《MySQL慢查询优化》之SQL语句及索引优化及mysql索引技巧分享[图]

一、《MySQL慢查询优化》之SQL语句及索引优化
1、慢查询优化方式
服务器硬件升级优化
Mysql服务器软件优化
数据库表结构优化
SQL语句及索引优化
本文重点关注于SQL语句及索引优化，关于其他优化方式以及索引原理等，请关注本人《MySQL慢查询优化》系列博文。优化我个人遵循的原则：积小胜为大胜，以空间换时间。-《论持久战》

2、数据源
工欲善其事必先利其器，为了测试与验证的方便，数据库可以直接采用MySQL官方提供的测试数据库employees，该数据库关系复杂度适中以及数据量较大，适合做SQL语句及索引优化分析，引用官方instruction：
Thedatabasecontainsabout300,000employeerecordswith2.8millionsalaryentries.Theexportdatais167MB,whichisnothuge,butheavyenoughtobenon-trivialfortesting.
数据库获取方式
数据库E-R关系图：
3、分析工具
采用explain指令直接模拟Mysql优化器执行SQL语句，查看SQL语句的执行计划。
示例：
explain命令执行结果包括若干参数：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra；重点关注type、possible_keys、key、key_len、extra这五个参数。
possible_keys：此次查询中可能会被选用的索引，注意这些索引不一定被查询使用到。
key：此次查询中真正使用到的索引。当为复合索引时，不确定是否被充分使用。
type：访问类型，表示MySQL在表中查找所需行的方式。常用的类型有：ALL,index,range,ref,eq_ref,const,system,NULL（性能从左到右逐步提升），其中：
key_len：表示索引中使用的字节数，用来计算索引是否被充分使用，不损失精确性的情况下，长度越短越好；
extra：
4、索引策略
索引策略是指创建使用索引所要遵循的规则，换句话说，违背了这些规则会导致索引失效或者查询效率降低。
测试数据表
showindexfromemployees;
策略1：尽量考虑覆盖索引
覆盖索引：SQL只需要通过遍历索引树就可以返回所需要查询的数据，而不必通过辅助索引查到主键值之后再去查询数据（回表操作）。回表操作的详细介绍可以参考本人《MySQL慢查询优化》系列博文之索引。
EG
EXPLAINSELECTemp_no,birth_date,genderFROMemployeesWHEREgender='M';
Usingindex：表示已经使用了覆盖索引。
策略2：遵循最左前缀匹配
联合索引命中必须遵循“最左前缀法则”。即SQL查询Where条件字段必须从索引的最左前列开始匹配，不能跳过索引中的列。联合索引又称复合索引，类似于书籍的目录，多级的目录结构中子目录依赖于父级目录存在，也是遵循“最左前缀法则”。
联合索引结构分析，示例
EXPLAINSELECT*FROMemployeesWHEREbirth_date='1963-06-01'ANDgender='F';
注：表存在多个索引时，即使Where条件满足最左前缀规则，SQL执行时也未必一定会命中联合索引，根据性能可能直接使用了主键索引。
EG：
EXPLAINSELECT*FROMemployeesWHEREemp_no=10010ANDbirth_date='1963-06-01'ANDgender='F';
PRIMARYKEY(`emp_no`)
策略3：范围查询字段放最后
联合索引定义时，尽量将范围查询字段放在最后（放在最后联合索引使用最充分，放在中间联合索引使用不充分）。使用联合索引时范围列（当前范围列索引生效）后面的索引列无法生效，同时索引最多用于一个范围列，如果查询条件中有多个范围列，也只能用到一个范围列索引。
EG1：
EXPLAINSELECTemp_no,birth_date,genderFROMemployeesWHEREemp_no>10015ANDgender='F';
只是使用到了主键索引PRIMARY(emp_no),联合索引未生效idx_empno_birthdate_gender(emp_no,birth_date,gender)；
删除idx_empno_birthdate_gender索引，新建联合索引idx_gender_birthdate_empno(gender,birth_date,emp_no)；
EG2：
EXPLAINSELECTemp_no,birth_date,genderFROMemployeesWHEREemp_no>10015ANDbirth_date=1953-09-02ANDgender='F';
策略4：不对索引字段进行逻辑操作
在索引字段上进行计算、函数、类型转换（自动\手动）都会导致索引失效。
EG：
CREATEINDEXidx_first_nameONemployees(first_name);
EXPLAINSELECT*FROMemployeesWHERELEFT(first_name,3)='Geo';
策略5：尽量全值匹配
全值匹配也就是精确匹配不使用like查询（模糊匹配），使用like会使查询效率降低。
策略6：Like查询，左侧尽量不要加%
like以%开头，当前列索引无效(当为联合索引时，当前列和后续列索引不生效，可能导致索引使用不充分)；当like前缀没有%，后缀有%时，索引有效。
EG1：
EXPLAINSELECT*FROMemployeesWHEREfirst_namelike'Geo%';
EG2：
EXPLAINSELECT*FROMemployeesWHEREfirst_namelike'%Geo%';
策略7：注意NULL/NOTNULL可能对索引有影响
在索引列上使用ISNULL或ISNOTNULL条件，可能对索引有所影响。
字段定义默认为NULL时，NULL索引生效，NOTNULL索引不生效；
字段定义明确为NOTNULL，不允许为空时，NULL/NOTNULL索引列，索引均失效；
EG1：
EXPLAINSELECT*FROMemployeesWHEREfirst_nameISNULL;
EG2：
EXPLAINSELECT*FROMemployeesWHEREfirst_nameISNOTNULL;
EG3：
EXPLAINSELECT*FROMemployeesWHEREfirst_nameISNOTNULL;
策略8：尽量减少使用不等于
不等于操作符是不会使用索引的。不等于操作符包括：not，<>，!=。
优化方法：数值型key<>0改为key>0orkey<0。
EG：
EXPLAINSELECT*FROMemployeesWHEREfirst_name!='Georgi';
策略9：字符类型务必加上引号
若varchar类型字段值不加单引号，可能会发生数据类型隐式转化，自动转换为int型，使索引无效。
EG：
EXPLAINSELECT*FROMemployeesWHEREfirst_name=1;
策略10：OR关键字前后尽量都为索引列
当OR左右查询字段只有一个是索引，会使该索引失效，只有当OR左右查询字段均为索引列时，这些索引才会生效。OR改UNION效率高。
EG1：
EXPLAINSELECT*FROMemployeesWHEREfirst_name='Georgi'ORemp_no=20001;
EG2：
EXPLAINSELECT*FROMemployeesWHEREfirst_name='Georgi'ORlast_name='Facello';
二、mysql索引技巧分享
什么是索引？

相信大家小时候学习汉字的时候都会查字典，想想你查字典的步骤，我们是通过汉字的首字母a～z一个一个在字典目录中查找，最终找到该字的页数。想想，如果没有目录会怎么样，读后感（https://www.yuananren.com/duhougan/）最差的结果是你有可能翻到字典的最后一页才找到你想要找的字。索引就相当于我们字典中的目录，可以极大的提高我们在数据库的查询效率。

常见的索引模型
①有序数组
如图
我们按照IDCard从小到大排列：
在我们想要根据IDCard查找某一条数据时，就可以通过二分法查找。
在我们想要根据IDCard查找IDCard在10到1000内的的数据时，就可以先通过二分法先查找10，然后向递增的方向遍历，找到IDCard为1000，再继续遍历，直到找到的IDCard大于1000时就完成了整个范围查询。我们仅仅从查询的角度来看有序数组已经很优秀了，但是我们都知道，有序数组在插入一条数据时是非常麻烦的，你需要将你插入位置后面的数据整体向后移一位，这是非常消耗性能的。
优点：查询效率很高，也很适合范围查询。
缺点：当新数据插入时会影响效率。
②哈希表
如图
学过hashmap的朋友应该都比较了解了，它的原理其实就是将IDCard通过哈希算法计算出一个特定的值，然后存储地址，这样在你找数据的时候直接可以通过IDCard去找了，当然也会有种情况，就是两个元素选中了相同的空间，我们通常会引出一个链表去存储。
我们可以看到hash表在等值查询的效率是很高的，但是由于hash表是无序的，所以在范围查询的时候只能遍历所有了，效率会很低。
感兴趣的朋友可以去看看lru，是如何解决范围查询的问题的，后续我也会和大家讲讲。
优点：等值查询效率高，插入效率高
缺点：不适合范围查询
③二叉树
如图
二叉树是比较经典的数据结构了，它的特点是每个节点的左儿子小于父节点，父节点又小于右儿子。
二叉树是有序的，查找的时间复杂度为O(logn)
二叉树可以说在插入查询方面都是比较优秀的，但是在数据的索引选择方面我们并不会选择二叉树，我们按照上图来讲：
假如我要访问IDCard5，那么我要先访问IDCard1，然后访问IDCard3，最后才能访问到IDCard5，我们总会访问了3个数据块，每一次访问都是一次磁盘寻址的过程，假设树高30，那么我们最差的情况下寻找一个数据要访问30此磁盘，这在效率上是不能忍受的。
④B+树
我们数据库innodb默认的索引引擎就是B+树。
B+树其实是就是一个N叉树，只在子节点上存储数据，并且子节点用链表维护，而且是有序的，在范围查询（链表更高效），等值查询，插入新数据上来说都是很高效的。并且作为N叉树，在树的每一层都可以存储很多数据，这样在数据库查询数据的时候最差也只需要几次磁盘寻址就可以了。
在mysql中有主键索引和非主键索引之分，主键索引上存储的是数据行信息，非主键索引上存储的是主键信息。
如下
主键为id，那么id这棵B+树上就会存储该行所有的信息字段，包括application，owner。
对于没有主键的表，innodb会给默认创建一个Rowid做主键。
唯一索引是application，那么在application这颗索引树上存储的信息就是id。
优点：
第一层只放索引信息，存放的索引信息更多。
树高更低，故磁盘寻址带来的损耗更小。
链表维护，范围查询效率更高。
⑤B-树
B-树和B+树的区别是每一个节点都会存储数据，叶子节点之间不用链表链接。
相比B+树来说做范围查询的效率会低一点，如果空间大小固定的话，第一层存放的索引信息更少（想想目录，我们都希望第一层是只用来存储目录信息的）。
什么是回表？
还按这个表举例：
来一条查询语句
select*fromuserwhereapplication='wechat';
我们来看看，针对与这张user表，上述的查询语句要经过哪些步骤：
①由于application是索引，所以先搜索application这颗索引树，找到application='wechat'这条数据，取得主键id
②通过取得的主键id，去主键id这颗B+树找到该条数据
③找到该条数据后，取得该数据行的值，并且返回
④结束
刚刚的第一步到第二步，其实就是一个回表操作，我们定义一下回表:
回表就是在普通索引树上取得主键信息，再返回到主键索引树去搜索，这就是回表操作。
覆盖索引
这条语句就可以用到覆盖索引这个特性了，我们再来看下步骤：
selectidfromuserwhereapplication='wechat';
这条语句就可以用到覆盖索引这个特性了，我们再来看下步骤：
①由于application是索引，所以先搜索application这颗索引树，找到application='wechat'这条数据，取得主键id
②mysql发现id就是select要查询的数据，并且application是唯一索引，于是直接返回
这就是覆盖索引的效果，可以减少我们的回表次数，甚至可以不用回表。
最左前缀原则
还是之前的表，索引变成了（application，owner）的联合索引，我们再来写个sql：
selectownerfromuserwhereapplicationlike"w%";
当你的查询条件是application以w开头的数据时，就可以用到最左前缀原则了。
可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左N个字符。
聪明的朋友已经发现了，最左前缀还有一层优化，比如（application，owner）这个联合索引中，我们用到了最左前缀，可以少维护一个application的单独索引，因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
索引下推
普通索引（application）
普通索引（owner）
select*fromuserwhereapplicationlike"w%"andowner="老王";
这个语句在搜索索引树的时候，只能用“w”，找到第一个满足条件的记录，然后判断其他条件是否满足。
在MySQL5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。
而MySQL5.6引入的索引下推优化（indexconditionpushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
也就是owner在联合索引中，判断owner是否等于'老王'，会直接过滤掉不等于'老王'的数据。
#结语
今天这篇文章内容确实够多的，聊了很多和数据库索引相关的知识，包括了什么是索引，索引模型，覆盖索引，前缀索引，索引下推，回表。
你可以看到，在满足语句需求的情况下，我们要利用数据库的特性尽量的减少资源的损耗。我们在使用数据库的时候，尤其是在设计表结构时，也要以减少资源消耗作为目标。