【MySQL】——索引优化

偏执的太偏执、 2022-05-15 22:06 290阅读 0赞

# 温故-索引类型 #

聚集索引和非聚集索引  
MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。  
Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。  
聚集索引不是一种单独的索引类型，而是一种存储数据的方式  
**1、主键索引**  
数据库引擎为InnoDB的时候，主键为聚集索引，为MyISAM的时候，主键为非聚集索引。在表中聚集索引最多只能有一个  
**2、唯一索引**  
当表某列被指定为unique约束时  
**3、单列索引**  
单列索引应用到在where子句中出现的列，以及join子句中出现的列  
**4、多列索引**  
在多个字段共同建立一个索引  
**5、全文索引FullText**  
MyISAM：支持 FULLTEXT类型的全文索引  
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

# 索引使用场景 #

## 创建索引的情况 ##

主键自动建立唯一索引；  
频繁作为查询条件的字段 应该创建索引；  
查询中与其它表关联的字段，外键关系建立索引；  
频繁更新的字段不适合创建索引（因为每次更新都需要更新索引）；  
where条件里用不到的字段不创建索引；  
单键/组合索引的选择问题（在高并发下倾向创建组合索引）；  
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；  
查询中统计或者分组字段；

## 不要创建索引的情况 ##

表的记录太少  
经常修改的表  
数据重复且分布平均的表字段，例如性别字段

## 注意事项 ##

表中的索引并不是建立的越多越好，因为索引需要顺序，这样在表中进行编辑操作的时候，负担比较重，一般建议表中的索引不要超过5个为宜。  
在使用explain分析的时候，索引的几种使用类型，从高到低依次排序：

> system>const>eq\_ref>ref>range>index>all

一般来说，保证查询至少达到range级别，最好达到ref。

# 索引失效 #

在使用过程可能设置了索引，但是索引并没有发挥效果的情况，称为这种情况为索引失效。一般通过explain进行分析，即可判断索引是否有效。通常使用以下方式尽可能避免索引失效的问题。  
1、在组合索引，尽可能按照索引的字段顺序的进行查询，避免越过某个字段导致索引失效  
2、不要再索引列进行函数操作  
3、组合索引中一旦出现范围检索的条件，那么它之后的索引全部失效  
4、尽可能就是使用索引查询的列和查询的列一致  
5、尽可能不要使用不等于！=或者<> ,以及is null ，is not null  
6、模糊字段like，尽量不在使用通配符开头 like以通配符开头（’%abc…’，可以通过覆盖索引，来避免，但是如果查询列不在索引范围内，将导致索引全部失效，也可以考虑全文索引来优化  
7、字符串一定要加单引号  
8、少用or字段，可以使用union all来替代  
9、使用in 或者not in同样容易操作全表扫描，能使用between 就不要使用in  
**题外话：覆盖索引**  
索引支持高效查找行，mysql也能使用索引来接收列的数据。这样不用读取行数据，当发起一个被索引覆盖的查询，explain解释器的extra列看到 using index。  
\#满足条件：\#  
\# select 查询的字段必须 有索引全覆盖  
select last\_name，first\_name 其中 last\_name 和first\_name 必须都有索引  
推荐一个优化总结口诀

全值匹配我最爱，最左前缀要遵守；
    带头大哥不能死，中间兄弟不能断；
    索引列上少计算，范围之后全失效；
    LIKE百分写最右，覆盖索引不写星；
    不能空值还有OR，索引失效要少用；
    VAR引号不可丢，SQL高级也不难；

# 筛选待优化的SQL以及优化流程 #

一般都会对线上环境的sql进行筛选，针对性的再进行优化。小编常用的筛选方案有如下内容：  
1、利用客户端数据连接池工具Druid的可视化页面，判断执行次数比较多，而且比较慢的SQL  
2、开启MySQL的慢查询日志，通过自定义慢查询的时间，查询符合条件的慢查询针对性优化。  
**优化流程**  
1、确定慢查询的语句  
2、判断表的数据量  
3、explain分析语句执行情况，判断理论上命中索引，实际命中索引，以及额外的信息extra  
4、优化业务步骤