【MySQL】——索引优化

温故-索引类型

聚集索引和非聚集索引
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、优化业务步骤