索引原理与慢查询优化
一 我们要搞明白的问题
让我们带着以下问题展开对索引的探索
1、为何索引叫key
2、索引是如何加速查询的，它的原理是啥？
索引模型/结构从二叉树-》平衡二叉树-》b树最后到b+树，每种树到底有什么问题最终演变成到了b+树
3、为何b+树不仅能够加速等值查询，还能加速范围查询
4、什么是聚集索引，什么是辅助索引
5、什么情况下叫覆盖了索引
6、什么情况下叫回表操作
7、什么是联合索引，最左前缀匹配原则
8、索引下推，查询优化
9、如何正确使用索引？
二 索引介绍
2.1 什么是索引
索引是存储引擎中一种数据结构，或者说数据的组织方式，又称之为键key，是存储引擎用于快速找到记录的一种数据结构。
为数据建立索引就好比是为书建目录，或者说是为字典创建音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
2.2 为何要用索引？
一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的、也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
2.3 如何正确看待索引？
# 先说结论：
索引不是越多越好，并且jason建议大家最好在提前创建好索引，而不是等火烧眉毛
# 详解如下
索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。
一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。
三 理解索引的储备知识
要了解索引的数据结构我来先来储备一些知识
1）储备知识1：机械磁盘一次IO的时间
机械磁盘一次io的时间 = 寻道时间 + 旋转延迟 + 传输时间
# 寻道时间
道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下
# 旋转延迟
旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；
# 传输时间
传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计
所以访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右
这9ms对于人来说可能非常短，但对于计算机来可是非常长的一段时间，长到什么程度呢？
一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。
2）储备知识2：磁盘的预读
# 考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化：
当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
3）储备知识3：索引原理精髓提炼
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
!!!!!!!!!!!!!!!!!!!!!!!!!!!!
本质都是：
通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
!!!!!!!!!!!!!!!!!!!!!!!!!!!!
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
四 索引分类
索引模型分为很多种类
#===========B+树索引（等值查询与范围查询都快）
二叉树->平衡二叉树->B树->B+树
#===========HASH索引(等值查询快，范围查询慢)
将数据打散再去查询
#===========FULLTEXT：全文索引 （只可以用在MyISAM引擎）
通过关键字的匹配来进行查询，类似于like的模糊匹配
like + %在文本比较少时是合适的
但是对于大量的文本数据检索会非常的慢
全文索引在大量的数据面前能比like快得多，但是准确度很低
百度在搜索文章的时候使用的就是全文索引，但更有可能是ES
不同的存储引擎支持的索引类型也不一样
-
InnoDB存储引擎
支持事务，支持行级别锁定，支持 B-tree（默认）、Full-text 等索引，不支持 Hash 索引； -
MyISAM存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； -
Memory存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
因为mysql默认的存储引擎是innodb，而innodb存储引擎的索引模型/结构是B+树，所以我们着重介绍B+树，那么大家最关注的问题来了：
B+树索引到底是如何加速查询的呢？
五 索引的数据结构
5.1 创建索引的两大步骤
为某个字段创建索引，即以某个字段的值为基础构建索引结构，那么如何构建呢？分为两大步骤
- 1、提取每行记录中该字段的值，以该值当作key，至于key对的value是什么？每种索引结构各不相同
- 2、然后以key值为基础构建索引结构
以后的查询条件中使用了该字段，则会命中索引结构
例
# 1、为user表的id字段创建索引，会以每条记录的id字段值为基础生成索引结构
create index 索引名 on user(id);
使用索引
select * from user where id = xxx;
# 2、为user表的name字段创建索引，会以每条记录的name字段值为基础生成索引结构
create index 索引名 on user(id);
使用索引
select * from user where name = xxx;
那么索引的结构到底长什么样子，让其能够加速查询呢？
innodb存储引擎默认的索引结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的
5.2 二叉查找树
有user表，我们以id字段值为基础创建索引
-
1、提取每一条记录的id值作为key值，value为本行完整记录，即
key value 10 (10,zs) 7 (7,ls) 13 (13,ls) 5 (5,ls) 8 (8,ls) 12 (12,ls) 17 (17,ls) -
2、以key值的大小为基础构建二叉树，如上图所示
二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。 顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。
如果我们需要查找id=12的用户信息
select * from user where id=12;
利用我们创建的二叉查找树索引，查找流程如下：
1、将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。
2、继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。
3、把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=1>2,name=xm。
利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。
5.3 平衡二叉树
基于5.1所示的二叉树，我们确实可以快速地找到数据。
但是，但是，但是让我们回到二叉查找树地特点上，只论二叉查找树，它的特点只是
任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。
所以，依据二叉查找树的特点，二叉树可以是这样构造的
这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。 导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。 为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。
平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度不能超过1。 下面是平衡二叉树和非平衡二叉树的对比：
由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。
那么是不是说基于平衡二叉树构建索引的结构就可以了呢？答案是否！
5.4 B树
那么直接用平衡二叉树这种数据结构来构建索引有什么问题？
1、首先，因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。
2、另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。 如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。
3、所以，如果我们单纯用平衡二叉树这种数据结构作为索引的数据结构，即每个磁盘块只放一个节点，每个节点中只存放一组键值对，此时如果数据量过大，二叉树的节点则会非常多，树的高度也随即变高，我们查找数据的也会进行很多次磁盘IO，查找数据的效率也会变得极低！
综上，如果我们能够在平衡二叉的树的基础上，把更多的节点放入一个磁盘块中，那么平衡二叉树的弊端也就解决了。即构建一个单节点可以存储多个键值对的平衡树，这就是B树。
B树（Balance Tree）即为平衡树的意思，下图即是一颗B树。
注意：
– 1、图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。– 2、图中的每个节点里面放入了多组键值对，一个节点也称为一页，一页即一个磁盘块，在mysql中数据读取的基本单位都是页，即一次io读取一个页的数据，所以我们这里叫做页更符合mysql中索引的底层数据结构。
从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。 基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。
假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：
- 1、先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。
- 2、将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。
- 3、将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。
注意：
- 1、B树的构造是有一些规定的，但这不是本文的关注点，有兴趣的同学可以令行了解。
- 2、B树也是平衡的，当增加或删除数据而导致B树不平衡时，也是需要进行节点调整的。
那么B树是否就是索引的最终结构了呢？答案是no，B树只擅长做等值查询，而对于范围查询（范围查询的本质就是n次等值查询），或者说排序操作，B树也帮不了我们
select * from user where id=3; -- 擅长
select * from user where id>3; -- 不擅长
上帝说要有光，于是有了B+树
5.5 B+树
B+树是对B树的进一步优化。让我们先来看下B+树的结构图：
根据上图我们来看下B+树和B树有什么不同。
1、B+树非叶子节点non-leaf node上是不存储数据的，仅存储键，而B树的非叶子节点中不仅存储键，也会存储数据。B+树之所以这么做的意义在于：树一个节点就是一个页，而数据库中页的大小是固定的，innodb存储引擎默认一页为16KB，所以在页大小固定的前提下，能往一个页中放入更多的节点，相应的树的阶数（节点的子节点树）就会更大，那么树的高度必然更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。
2、B+树的阶数是等于键的数量的，例如上图，我们的B+树中每个节点可以存储3个键，3层B+树存可以存储3*3*3=9个数据。所以如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。而一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO，真是屌炸天的设计。
3、因为B+树索引的所有数据均存储在叶子节点leaf node，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。
而且B+树中各个页之间也是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。
通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。
MyISAM中的B+树索引实现与innodb中的略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。
六 聚集索引于非聚集索引
6.1 什么是聚集索引，什么是非聚集索引
在上节介绍B+树索引的时候，我们提到了图中的索引其实是聚集索引的实现方式。那什么是聚集索引呢？什么是又是非聚集索引呢？
在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。这里我们主要介绍innodb存储引擎中的聚集索引和非聚集索引。
1、聚集索引（又称聚簇索引、主键索引，一张表必须有且只有一个）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键用的就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。
2、非聚集索引（又称非聚簇索引、辅助索引，一张表可以创建多个辅助索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。
明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。
6.2 利用聚集索引和非聚集索引查找数据
前面我们讲解B+树索引的时候并没有去说怎么在B+树中进行数据的查找，主要就是因为还没有引出聚集索引和非聚集索引的概念。下面我们通过讲解如何通过聚集索引以及非聚集索引查找数据表中数据的方式介绍一下B+树索引查找数据方法。
6.2.1 利用聚集索引查找数据
还是这张B+树索引图，现在我们应该知道这就是聚集索引，表中的数据存储在其中。现在假设我们要查找id>=18并且id<40的用户数据。对应的sql语句为
select * from user where id>=18 and id <40，
其中id为主键。具体的查找过程如下：
1、一般根节点都是常驻内存的，也就是说页1已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。从内存中读取到页1，要查找这个id>=18 and id <40或者范围值，我们首先需要找到id=18的键值。从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。
2、要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。
3、同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。将页8读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值18对应的数据。因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。
4、因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。那么查找到此终止。最终我们找到满足条件的所有数据为：(18,kl),(19,kl),(22,hj),(24,io),(25,vg),(29,jk),(31,jk),(33,rt),(34,ty),(35,yu),(37,rt),(39,rt)。总共12条记录。
下面看下具体的查找流程图：
[rml_read_more]：
6.2.2 利用非聚集索引查找数据
读者看到这张图的时候可能会蒙，这是啥东西啊？怎么都是数字。如果有这种感觉，请仔细看上图中红字的解释。什么？还看不懂？那我再来解释下吧。首先，这个非聚集索引表示的是用户幸运数字的索引（为什么是幸运数字？一时兴起想起来的），此时表结构是这样的。
| id | name | luckyNum |
|---|---|---|
| 1 | zs | 23 |
| 2 | ls | 7 |
在叶子节点中，不再存储所有的数据了，存储的是键和主键。对于叶子节点中的x-y，比如10-23。左边的10表示的是索引的键值，右边的23表示的其对应的主键值。如果我们要找到幸运数字为33的用户信息，对应的sql语句为
select * from user where luckNum=33;
查找的流程跟聚集索引一样，这里就不详细介绍了。我们最终会找到主键值47，因为我们select要的是*，即所有字段值，在辅助索引中并不存在，所以找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程，着
下面看下具体的查找流程图：
在MyISAM中，聚集索引和非聚集索引的叶子节点都会存储数据的文件地址。
6.2.3 覆盖索引与回表操作
命中了辅助索引，然后要找的字段值不存在与辅助索引的叶子节点上，则需要根据拿到的主键值再去聚集索引中查询一遍，然后再聚集索引的叶子节点找到你想要的内容，这就叫回表操作
例如
create index xxx on user(name);
下述语句，名中了辅助索引，但是select需要查询出的除了辅助索引叶子节点有的name字段值外还想要age字段的值，那么需要进行回表操作
select name,age from user where name="jason";
命中了某棵索引树，然后在其叶子节点就找到了你想要的值，即不需要回表操作，就是覆盖了索引
例1：
create index xxx on user(name);
下述语句，覆盖了索引
select name from user where name="jason";
例2：使用主键字段当作条件，百分百覆盖了索引，效率极高，推荐使用
如果id字段是主键，那么下述语句也覆盖了索引
select * from user where id=3;
一.索引介绍
1.什么是索引
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能。
2.索引类型介绍
1)BTREE:B+树索引
2)HASH：HASH索引
3)FULLTEXT：全文索引
4)RTREE：R树索引
图1·B+tree索引
图2·B*tree索引
3.索引管理
索引建立在表的列上(字段)的。
在where后面的列建立索引才会加快查询速度。
pages<---索引（属性）<----查数据。
- 1、索引分类：
主键索引
普通索引*****
唯一索引
- 2、添加索引：
| #创建索引 | |
|---|---|
| alter table test add index index_name(name); | |
| #创建索引 | |
| create index index_name on test(name); | |
| #查看索引 | |
| desc table; | |
| #查看索引 | |
| show index from table; | |
| #删除索引 | |
| alter table test drop key index_name; | |
| #添加主键索引（略） | |
| #添加唯一性索引 | |
| alter table student add unique key uni_xxx(xxx); | |
| #查看表中数据行数 | |
| select count(*) from city; | |
| #查看去重数据行数 | |
| select count(distinct name) from city; |
- 3、前缀索引和联合索引
前缀索引
根据字段的前N个字符建立索引
alter table test add index idx_name(name(10));
避免对大列建索引
如果有，就使用前缀索引
联合索引
多个字段建立一个索引
例：
where a.女生 and b.身高 and c.体重 and d.身材好
index(a,b,c)
特点：前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引
原则：把最常用来做为条件查询的列放在最前面
| #创建people表 | |
|---|---|
| create table people (id int,name varchar(20),age tinyint,money int ,gender enum('m','f')); | |
| #创建联合索引 | |
| alter table people add index idx_gam(gender,age,money); |
二.explain详解
explain命令使用方法
mysql> explain select name,countrycode from city where id=1;
explain命令应用
查询数据的方式
-
1.全表扫描1）在explain语句结果中type为ALL
2）什么时候出现全表扫描?
- 2.1 业务确实要获取所有数据
- 2.2 不走索引导致的全表扫描
- 2.2.1 没索引
- 2.2.2 索引创建有问题
- 2.2.3 语句有问题
生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描
- 2.索引扫描
2.1 常见的索引扫描类型:
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null
从上到下，性能从最差到最好，我们认为至少要达到range级别
index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
| mysql> alter table city add index idx_city(population); | |
|---|---|
| mysql> explain select * from city where population>30000000; |
ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。
| mysql> alter table city drop key idx_code; | |
|---|---|
| mysql> explain select * from city where countrycode='chn'; | |
| mysql> explain select * from city where countrycode in ('CHN','USA'); | |
| mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; |
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A
| join B | |
|---|---|
| on A.sid=B.sid |
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量
mysql> explain select * from city where id=1000;
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
mysql> explain select * from city where id=1000000000000000000000000000;
Extra（扩展）
Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer
如果出现Using filesort请检查order by ,group by ,distinct,join 条件列上没有索引
mysql> explain select * from city where countrycode='CHN' order by population;
当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现
| mysql> explain select * from city where population>30000000 order by population; | |
|---|---|
| mysql> select * from city where population=2870300 order by population; |
key_len: 越小越好
- 前缀索引去控制
rows: 越小越好
三.建立索引的原则（规范）
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的?
- 1、选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如:
学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
主键索引和唯一键索引，在查询中使用是效率最高的。
| select count(*) from world.city; | |
|---|---|
| select count(distinct countrycode) from world.city; | |
| select count(distinct countrycode,population ) from world.city; |
注意：如果重复值较多，可以考虑采用联合索引
- 2．为经常需要排序、分组和联合操作的字段建立索引
例如:
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作
-
3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
因此，为这样的字段建立索引，可以提高整个表的查询速度。
- 3.1 经常查询
- 3.2 列值的重复值少
注：如果经常作为条件的列，重复值特别多，可以建立联合索引
- 4．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
- 5．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。 - 6．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
重点关注：
- 1.没有查询条件，或者查询条件没有建立索引
| #全表扫描 | |
|---|---|
| select * from table; | |
| select * from tab where 1=1; |
在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。
1）对用户查看是非常痛苦的。
2）对服务器来讲毁灭性的。
3）SQL改写成以下语句：
| #情况1 | |
|---|---|
| #全表扫描 | |
| select * from table; | |
| #需要在price列上建立索引 | |
| selec * from tab order by price limit 10; | |
| #情况2 | |
| #name列没有索引 | |
| select * from table where name='zhangsan'; | |
| 1、换成有索引的列作为查询条件 | |
| 2、将name列建立索引 |
- 2.查询结果集是原表中的大部分数据，应该是25％以上
mysql> explain select * from city where population>3000 order by population;
1）如果业务允许，可以使用limit控制。
2）结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。
-
3.索引本身失效，统计数据不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
重建索引就可以解决 -
4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
| #例子 | |
|---|---|
| 错误的例子：select * from test where id-1=9; | |
| 正确的例子：select * from test where id=10; |
- 5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误
| mysql> create table test (id int ,name varchar(20),telnum varchar(10)); | |
|---|---|
| mysql> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112); | |
| mysql> explain select * from test where telnum=120; | |
| mysql> alter table test add index idx_tel(telnum); | |
| mysql> explain select * from test where telnum=120; | |
| mysql> explain select * from test where telnum=120; | |
| mysql> explain select * from test where telnum='120'; |
- 6． <> ，not in 不走索引
| mysql> select * from tab where telnum <> '1555555'; | |
|---|---|
| mysql> explain select * from tab where telnum <> '1555555'; |
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in尽量改成union
| EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119'); | |
|---|---|
| #改写成 | |
| EXPLAIN SELECT * FROM teltab WHERE telnum='110' | |
| UNION ALL | |
| SELECT * FROM teltab WHERE telnum='119' |
- 7．like "%_" 百分号在最前面不走
| #走range索引扫描 | |
|---|---|
| EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'; | |
| #不走索引 | |
| EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'; |
%linux%类的搜索需求，可以使用Elasticsearch -------> ELK
- 8.单独引用联合索引里非第一位置的索引列
| CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT); | |
|---|---|
| ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex); | |
| DESC t1 | |
| SHOW INDEX FROM t1 | |
| #走索引的情况测试 | |
| EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m'; | |
| #部分走索引 | |
| EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30; | |
| EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m'; | |
| #不走索引 | |
| EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20 | |
| EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m'; | |
| EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m'; |