你们要的索引原理和MySQL优化
阅读本文约“15分钟”
适读人群：Java中级
先看看本文的“索引”先，再阅读理解
正文如下
原题《浅谈索引原理和mysql优化》
攝影師：Adam Borkowski，連結：Pexels
申明：本文所讲的内容都是基于innodb存储引擎
什么是索引？
在学习索引过程中，我们总会举书籍的目录这一个例子来做为索引的参考理解。基本上每一本书都会有目录，而书籍目录会占据书的篇幅，难道作者是为了凑字数来赚稿费么（突然回想起了大学毕业论文为了凑字数的各种骚操作了...）其实目录存在的意义是因为它能够帮我们快速地定位到想要查找的东西的位置。同样的，转换成计算机的术语，索引的概念就是拿空间来换时间，索引的本质就是帮助MySql高效获取数据的数据结构。在关系型数据库里，索引最终都要进行一个落盘操作，存储在硬盘中。
索引的分类
数据结构角度
1.B+Tree索引
在说到B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树即由这些树逐步优化而来
二叉树
如图所示，这是一棵二叉树。我们都明白通过树来遍历可以加速定位到要查找的东西，而如果是一个递增的序列，这棵二叉树是呈现一个逐渐深入的状态。索引的存在是空间换时间，那么这颗树的存在耗费了空间，又没有减少查找的时间，所以在这个基础上进行优化，便有了AVLTree。
平衡二叉树（AVLTree）
一棵AVL树有如下必要条件：
条件一：它必须是二叉查找树。
条件二：每个节点的左子树和右子树的高度差至多为1。
这次我们可以看到，原先查找6的节点，二叉树需要查找6次，而平衡二叉树只需要查找3次，缩短了查找的时间。
但是平衡二叉树就一定完美了吗？为什么平衡二叉树基本上很少被使用呢？
通过这个例子我们可以发现一个现象：树的高度决定了查询的次数。
当数据量巨大的情况下，平衡二叉树的深度一样会很深，因为它需要维护平衡二叉树的定义。他要会造成查询次数的增加。
从上面的内容我们可以知道，在关系型数据库里，索引最终都是存储在硬盘中的。而比较数据的时候需要将磁盘里的数据加载到内存进行操作，每一次内存和磁盘的交互都是一次IO操作。平衡二叉树的节点只记录了它的数据和子节点，每一次内存读取只能获取到很少的信息，这无疑是效率不高的。
二叉树的节点内容：
二叉树节点内容
B-Tree（多叉平衡查找树）
这里设置B-Tree的路数（degree）为3，一个节点关键字的最多个数为degree-1。
通过B-Tree可以看到，内存一次加载的目标数据可以更多了。那么为什么还会有B+树呢？
B+Tree
B+Tree是在B-Tree的基础上衍生过来的。
B tree的两个明显特点
1.树内存储数据
2.叶子节点上无链表
B+ tree的两个明显特点
1.数据只出现在叶子节点
2.所有叶子节点增加了一个链指针
数据只B+Tree的叶子节点中，而B-Tree是所有数据都可能存在任何一个节点中。如图所示，所以假如需要获取到6的数据，在B-Tree中，只需要IO 2次就可以获取到数据，而在B+Tree中，需要IO 4次才可以获取到数据。因为数据存储在叶子节点中！可能你会觉得疑惑，为什么衍生出来的B+Tree反而效率更低，那么它存在的意义又是什么呢？
我们可以发现，在B+Tree在每个叶子节点上加上了一个链指针。我们在开发过程中，经常会有SELECT * FROM Data;这个语句，在这种情况下，B+Tree只需要遍历链表就可以了，而B-Tree还需要遍历树。数据库索引采用B+ tree的主要原因是B Tree在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。
2.Hash索引
innodb的hash索引学名是自适应哈希索引（Adaptive Hash Index，又称为AHI），为什么这么说呢？因为它是内部的、自主判断的，我们无法自己手动创建。
物理存储角度
聚集索引和非聚集索引
这里引用MySQL官方文档的一句话：
clustered index
The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.
翻译成中文：
聚集索引
InnoDB术语表示主键索引。InnoDB表存储是根据主键列的值组织的，以加快涉及主键列的查询和排序。为了获得最佳性能，请根据对性能要求最严格的查询仔细选择主键列。因为修改聚集索引的列是一项昂贵的操作，所以请选择很少更新或永远不会更新的主列。
在InnoDB中，聚集索引表示索引和主键列的值存在同一个索引文件中。并且InnoDB只有一个聚集索引。而非聚簇索引的叶子节点只存储主键值。
在这个概念上引申出覆盖索引和回表。
覆盖索引指的就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列被所使用的索引覆盖。
回表值的是select的数据列无法从索引中直接取得，得通过获得索引里的主键，再去主键索引里寻找一次数据列返回。
假设一张表里有三个字段，id(主键)，name和age，数据库只设置了’name‘的索引。（innodb会自动创建主键索引）
在以下的SQL语句中
select * from data where id=1;#索引覆盖
select id,name from data where name=“张三”;#索引覆盖
select * from data where name=“张三”;#回表
索引的逻辑角度
（1）主键索引：主键索引是特殊的唯一索引，不允许有空值
（2）唯一索引：故名思议，允许有空值
（3）单列索引：对一个字段建立索引
（4）联合索引（复合索引）：对多个字段同时建立的索引。
索引的选择
1.索引一定好吗？什么时候需要建立索引？
其实数据库大多数时候的业务都是查询，建立太多索引反而可能导致数据写入/更新/删除速度变慢，所以如果写入修改的场景远远大过于查询的场景，是不建议建立索引的。如果写入修改查询的场景都很多，就需要根据业务来进行分表分库，读写分离(主从复制)了（–题外话）。技术的存在都是为了填补空白或者解决痛点，不然没有存在的意义。索引的存在就是为了解决数据量大查询缓慢的痛点。
2.怎么判断哪些SQL出问题，需要创建索引？
可以通过开启MySQL慢查询功能来定位。使用命令 show variables like ‘%slow_query_log%’;
1mysql> show variables like ‘%slow_query_log%’;
2±--------------------±-----------------------------------------+
3| Variable_name | Value |
4±--------------------±-----------------------------------------+
5| slow_query_log | OFF |
6| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
7±--------------------±-----------------------------------------+
82 rows in set (0.00 sec)
9mysql> set global slow_query_log=1;
10Query OK, 0 rows affected (0.00 sec)
11mysql> show variables like ‘%slow_query_log%’;
12±--------------------±-----------------------------------------+
13| Variable_name | Value |
14±--------------------±-----------------------------------------+
15| slow_query_log | ON |
16| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
17±--------------------±-----------------------------------------+
182 rows in set (0.00 sec)
19#使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
修改my.cnf文件：
1[mysqld]
2#打开慢查询日志
3slow_query_log = 1
4#慢查询日志存储路径（linux环境）
5slow_query_log_file = /usr/local/mysql/data/localhost-slow.log
6#SQL执行时间大于3秒，则记录日志（默认10s）
7long_query_time = 3
修改后重启mysql服务器
这时候找到查询缓慢的语句了，就需要立即建立索引了吗？非也。还是要根据业务场景来判断。如果业务场景是查询一条数据，那么可以在sql语句最后加上’limit 1’，这样MySQL在查询到一条符合条件的数据后就会停止搜索，直接返回，而不会继续查询所有符合条件的数据。
索引设计原则
(1)索引并非越多越好，大量的索引不仅占用磁盘空间，而且还会影响insert,delete,update等语句的性能
(2)避免对经常更新的表做更多的索引，并且索引中的列尽可能少；对经常用于查询的字段创建索引，避免添加不必要的索引
(3)数据量少的表尽量不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果
(4)在条件表达式中经常用到不同值较多的列上创建索引，在不同值很少的列上不要建立索引。比如性别字段只有“男”“女”俩个值，就无需建立索引。如果建立了索引不但不会提升效率，反而严重减低数据的更新速度
(5)在频繁进行排序或者分组的列上建立索引，如果排序的列有多个，可以在这些列上建立联合索引。
联合索引
看完以上的索引内容后，来细讲联合索引。说到联合索引一定离不开最左匹配原则。
联合索引是有顺序性的，(a,b),(b,a)是两种不同的索引。以联合索引（a,b）为例，在建立联合索引（a,b）的时候，相当于创建了（a),(a,b)两个索引。
1a=1 #匹配索引
2a=1 and b=2 #匹配索引
3b=2 and a=1 #匹配索引
4b=2 #不匹配索引
5a=1 and b=2 #匹配索引
注意：
1b=2 and a=1
也是可以匹配索引的，这里主要是靠MySQL优化器进行优化（下一节讲解MySQL语句的执行过程会说到）
最左匹配的原理
对（a,b）字段建立索引，如下图
这里可以看到，这里是先按a字段来进行排序，a相等的情况下，再按b字段进行排序。
所以a字段是全局有序的状态，而b字段是全局无序的。b字段的有序要建立在a值确定的基础上！
了解完最左匹配原则，直接上实战。
联合索引实战
目前已经有联合索引
1create index idx_name_ph(name,phoneNum,age) on user;
以下SQL语句，怎么使用联合索引？
1select * from user where name = "张三" and phoneNum > 13000000000 and age = 18;#用到name和phoneNum
2select * from user where name = "张三" and age = 18;#用到name
3select * from user where phoneNum = 13000000000 and age = 18;#没有用到索引
4select * from user where phoneNum = 13000000000 and name = "张三";#用到name和phoneNum
MySQL语句的执行过程
1.客户端发送一条查询个服务器
2.服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3.服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
5.将结果返回给客户端
——《高性能MySQL（第三版）第六章：查询性能优化》