特色栏目： python 批处理 net编程 Javascript Php Asp Css Html5 Android seo centos

当前位置 : 主页 > 操作系统 > centos >

CentOS MySQL索引

来源：互联网收集：自由互联发布时间：2022-09-02

索引及执行计划什么是索引类似书的目录索引就好比一本书的目录，它能让你更快的找到自己想要的内容。让获取的数据更有目的性，从而提高数据库检索数据的性能。注意：给指

索引及执行计划

什么是索引

类似书的目录

索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
让获取的数据更有目的性，从而提高数据库检索数据的性能。

注意：给指定的字段，排序，添加'目录'功能，索引并不是越多越好，也不是每个字段都必须加索引

MySQL中索引的类型

)BTREE:B+树索引
HASH：HASH索引
FULLTEXT：全文索引
RTREE：R树索引

Btree算法

Btree

三路Btree

根节点
枝节点
叶子结点

select * from tb1 where id=15; 精确查询：3次IO select * from tb1 where id>10 and id<30; 范围查询：6次

B+tree

select * from tb1 where id=15; 精确查询：3次 select * from tb1 where id>10 and id<30; 范围查询：4次 # B+tree和Btree比 1.优化了范围查询 2.在叶子节点上添加了相邻的指针

B*tree(了解即可)

索引管理

索引建立在表的列上(字段)的。在where后面的列建立索引才会加快查询速度。

索引分类

主键索引
- 联合索引
唯一索引
- 前缀索引
- 联合索引
普通索引
- 前缀索引
- 联合索引

如何创建索引

## 创建索引 alter table score add index idx_sno(sno); ## 查看索引 show index from 表名; show index from stu; desc:查看索引 PRI:主键索引 UNI:唯一键索引 MUL:普通索引 ## 删除索引 alter table score drop index idx_sno; alter table score drop key idx_sno; ## 添加主键索引 mysql> create table test(id int,name varchar(10) not null); Query OK, 0 rows affected (0.13 sec) mysql> desc test; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table test add primary key(id); ## 删除主键索引 mysql> alter table test drop primary key; ## 添加唯一键索引 mysql> alter table test add unique key uni_name(name); # 添加唯一键要求：该字段的数据不能有重复的 mysql> alter table linux50.teacher add unique key uni_tanme(tname); ERROR 1062 (23000): Duplicate entry '徐亮伟' for key 'uni_tanme' ### 判断是否可以在name字段上创建唯一键 1.先统计该字段总共有多少行 mysql> select count(tname)from linux50.teacher; +--------------+ | count(tname) | +--------------+ | 6 | +--------------+ 1 row in set (0.00 sec) 2.再统计，去重后，该字段有多少行 mysql> select count(distinct(tname)) from linux50.teacher; +------------------------+ | count(distinct(tname)) | +------------------------+ | 3 | +------------------------+ 1 row in set (0.00 sec) # 如果两个数值一样，则可以创建唯一键索引，如果两个数值不一样，则无法创建唯一键索引（有重复值） ## 删除唯一键索引 alter table test drop index 索引名; alter table test drop index uni_name;

前缀索引

给某一字段数据内容特别长的列，创建前缀索引

## 普通前缀索引的创建 mysql> alter table city add index idx_district(district(4)); # 优先按前四个字符排序 ## 唯一索引前缀索引创建 alter table student2 add unique key uni_name(name(3)); # 原则 1.避免对大列（数据长的列）建索引 2.如果有，就使用前缀索引

联合索引

将多个字段，做成一个索引

索引的执行计划

## explain(执行计划) 检测一个SQL语句的执行效率将explain加入在要执行的SQL语句之前即可 mysql> explain select * from score; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | score | ALL | NULL | NULL | NULL | NULL | 9 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> desc select * from score; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | score | ALL | NULL | NULL | NULL | NULL | 9 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ ## 查看SQL语句的执行效率，主要看type列分类: - 全表扫描 - ALL:效率最低,且最好不要出现的类型 - 索引扫描 - index:全索引扫描 - range:范围查询 - ref:非唯一键或者唯一键的前缀索引的精确查询 - eq_ref:连表查询的时候 - system、const:主键索引精确查询 - NULL:无法查出数据时,效率最高 ## 全表扫描 ALL:企业中绝对不会出现 1.业务确实要获取所有数据 2.不走索引导致的全表扫描 - 没索引 - 索引创建有问题 - 语句有问题 - 索引损坏 ## 索引扫描 # index:全索引扫描,创建索引的列,全部数据都查询出来了 mysql> explain select sno from score; # range:范围查询,一般来说,一条SQL语句,只要达到range级别,就OK mysql> explain select sno from score where sno>2; # ref:唯一索引的前缀扫描或者非唯一索引扫描(精确查询) mysql> explain select sno from score where sno=2; # eq_ref:连表查询,传统连接,join on explain select city.countrycode,country.name as 国家,city.name as 城市,city.population as 城市人口数 from city join country on city.country.countrycode=country.code and city.population<100; # const、system:主键精确查询 mysql[world]> explain select * from city where id=10; # null:不进行表的扫描,没有这样的数据 explain select * from city where id>100000000000000000000000000000000000000000000000; ### 范围查询优化 mysql[world]> explain select * from city where countrycode='CHN' or countrycode='USA'; mysql[world]> explain select * from city where countrycode in ('CHN','USA'); ### 联合查询优化，or精确范围 explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

索引建立的原则

索引的创建，是为了提示查询的效率，需要知道在哪些字段上创建索引

索引不是每一个字段都要创建，也不是越多越好

根据用户的喜好，被查询的越多的字段，要创建索引

1.索引首要选择，唯一键索引

判断是否可以创建唯一键索引
- select count(需要创建唯一键索引的字段) from 表;
- select count(distinct(需要创建唯一键索引的字段)) from 表;

2.其次，如果无法创建唯一索引，重复值比较多，创建联合索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3.为经常作为查询条件的字段，创建普通索引

经常查询
增加查询的效率

4.尽量能使用前缀索引，就用前缀索引

减少创建索引的排序时间
增加查询的效率

5.索引的数量不是越多越好

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6.删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

不走索引的原因

数据库查询效率低，导致网站加载速度慢，如何解决？

在企业中，开启MySQL的慢日志（慢查询日志）

查看，哪一条SQL语句执行的速度慢，取出SQL语句，使用explain查看该SQL语句是否走索引

没有走索引

1.全表扫描，使用select *查询没有加条件

## 使用limit来优化 mysql> explain select * from world.city where limit 60;

2.查询结果集是原表中的大部分数据，应该是25％以上

mysql> explain select * from world.city where population>50 limit 60;

3.条件本身做运算

## 不走索引 mysql> explain select * from city where id-1=9; # 不能使用字段做计算

4.隐式转换导致索引失效

5.不等于或者 not in

使用不等于或者not in会影响到结果集 ## 优化方案： - 使用limit来优化 - 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit 、or或in尽量改成union

6.使用like模糊查询%在前面

mysql> explain select * from city where countrycode like '%HN'; ## 优化方案： - 尽量不要使用%在前面的SQL语句，尽量将%放在后面 - 那就不要使用MySQL，使用搜索引擎式数据库：elasticsearch

7.使用联合索引查数据时

不走索引，要根据联合索引创建的顺序来查询数据创建顺序：ABC 查询顺序：ABC AB AC BC

8.索引损坏或失效

删了重建

上一篇：【K8S专栏】Kubernetes集群管理
下一篇：没有了