MySQL索引原理
本文内容纲要：
-一、初识索引
-1.1为什么要有索引？
-1.2什么是索引？
-1.3你是否对索引存在误解？
-二、索引的原理
-2.1索引原理
-2.2磁盘IO与预读
-三、索引的数据结构
-四、MySQL索引管理
-4.1功能
-4.2MySQL常用的索引
-4.3各个索引应用场景
-4.4索引的两大类型hash与btree
-4.5创建/删除索引的语法
-4.6示例
-五、测试索引
-5.1数据准备
-5.2小结
-六、正确使用索引
-6.1索引未命中
-6.2其他注意事项
-七、联合索引和覆盖索引
-7.1联合索引
-7.2覆盖索引
-7.3合并索引
-八、查询优化神器-explain
-九、慢查询优化的基本步骤
-十、慢日志管理
目录
-
一、初识索引
- 1.1为什么要有索引？
- 1.2什么是索引？
- 1.3你是否对索引存在误解？
-
二、索引的原理
- 2.1索引原理
- 2.2磁盘IO与预读
-
三、索引的数据结构
-
四、MySQL索引管理
- 4.1功能
- 4.2MySQL常用的索引
- 4.3各个索引应用场景
- 4.4索引的两大类型hash与btree
- 4.5创建/删除索引的语法
- 4.6示例
-
五、测试索引
- 5.1数据准备
- 5.2小结
-
六、正确使用索引
- 6.1索引未命中
- 6.2其他注意事项
-
七、联合索引和覆盖索引
- 7.1联合索引
- 7.2覆盖索引
- 7.3合并索引
-
八、查询优化神器-explain
-
九、慢查询优化的基本步骤
-
十、慢日志管理
MySQL完整教程目录：https://www.cnblogs.com/nickchen121/p/14709373.html
一、初识索引
1.1为什么要有索引？
一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。
1.2什么是索引？
索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
1.3你是否对索引存在误解？
索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。
二、索引的原理
2.1索引原理
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
2.2磁盘IO与预读
前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2=4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17=9ms左右，听起来还挺不错的，但要知道一台500-MIPS（MillionInstructionsPerSecond）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
三、索引的数据结构
MySQL索引的数据结构-B+树介绍：https://www.cnblogs.com/nickchen121/p/11152523.html
四、MySQL索引管理
4.1功能
- 索引的功能就是加速查找
- mysql中的primarykey，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
4.2MySQL常用的索引
- 普通索引INDEX：加速查找
- 唯一索引：
主键索引PRIMARYKEY：加速查找+约束（不为空、不能重复）
唯一索引UNIQUE:加速查找+约束（不能重复）
- 联合索引：
PRIMARYKEY(id,name):联合主键索引
UNIQUE(id,name):联合唯一索引
INDEX(id,name):联合普通索引
4.3各个索引应用场景
举个例子来说，比如你在为某商场做一个会员卡的系统。
这个系统有一个会员表
有下列字段：
会员编号INT
会员姓名VARCHAR(10)
会员身份证号码VARCHAR(18)
会员电话VARCHAR(10)
会员住址VARCHAR(50)
会员备注信息TEXT
那么这个会员编号，作为主键，使用PRIMARY
会员姓名如果要建索引的话，那么就是普通的INDEX
会员身份证号码如果要建索引的话，那么可以选择UNIQUE（唯一的，不允许重复）
#除此之外还有全文索引，即FULLTEXT
会员备注信息，如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的INDEX也可以。
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。
#其他的如空间索引SPATIAL，了解即可，几乎不用
各个索引的应用场景
各个索引的应用场景
4.4索引的两大类型hash与btree
我们可以在创建上述索引的时候，为其指定索引类型，分两类：
- hash类型的索引：查询单条快，范围查询慢
- btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
不同的存储引擎支持的索引类型也不一样：
- InnoDB支持事务，支持行级别锁定，支持B-tree、Full-text等索引，不支持Hash索引；
- MyISAM不支持事务，支持表级别锁定，支持B-tree、Full-text等索引，不支持Hash索引；
- Memory不支持事务，支持表级别锁定，支持B-tree、Hash等索引，不支持Full-text索引；
- NDB支持事务，支持行级别锁定，支持Hash索引，不支持B-tree、Full-text等索引；
- Archive不支持事务，支持表级别锁定，不支持B-tree、Hash、Full-text等索引；
4.5创建/删除索引的语法
#方法一：创建表时
CREATETABLE表名(
字段名1数据类型[完整性约束条件…],
字段名2数据类型[完整性约束条件…],
[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY
[索引名](字段名[(长度)][ASC|DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名
ON表名(字段名[(长度)][ASC|DESC]);
#方法三：ALTERTABLE在已存在的表上创建索引
ALTERTABLE表名ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX
索引名(字段名[(长度)][ASC|DESC]);
#删除索引：DROPINDEX索引名ON表名字;
4.6示例
#方式一
createtablet1(
idint,
namechar,
ageint,
sexenum('male','female'),
uniquekeyuni_id(id),
indexix_name(name)#index没有key
);
createtablet1(
idint,
namechar,
ageint,
sexenum('male','female'),
uniquekeyuni_id(id),
index(name)#index没有key
);
#方式二
createindexix_ageont1(age);
#方式三
altertablet1addindexix_sex(sex);
altertablet1addindex(sex);
#查看
mysql>showcreatetablet1;
|t1|CREATETABLE`t1`(
`id`int(11)DEFAULTNULL,
`name`char(1)DEFAULTNULL,
`age`int(11)DEFAULTNULL,
`sex`enum('male','female')DEFAULTNULL,
UNIQUEKEY`uni_id`(`id`),
KEY`ix_name`(`name`),
KEY`ix_age`(`age`),
KEY`ix_sex`(`sex`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1
五、测试索引
5.1数据准备
#1.准备表
createtables1(
idint,
namevarchar(20),
genderchar(6),
emailvarchar(50)
);
#2.创建存储过程，实现批量插入记录
delimiter$$#声明存储过程的结束符号为$$
createprocedureauto_insert1()
BEGIN
declareiintdefault1;
while(i<3000000)do
insertintos1values(i,'eva','female',concat('eva',i,'@oldboy'));
seti=i+1;
endwhile;
END$$#$$结束
delimiter;#重新声明分号为结束符号
#3.查看存储过程
showcreateprocedureauto_insert1\G
#4.调用存储过程
callauto_insert1();
1、在没有索引的前提下测试查询速度
无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢
mysql>select*froms1whereid=333333333;
Emptyset(0.33sec)
2、在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢
3、在索引建立完毕后，以该字段为查询条件时，查询速度提升明显
注意：
- mysql先去索引表里根据b+树的搜索原理很快搜索到id等于333333333的记录不存在，IO大大降低，因而速度明显提升
- 我们可以去mysql的data目录下找到该表，可以看到占用的硬盘空间多了
- 需要注意，如下图
5.2小结
- 一定是为搜索条件的字段创建索引，比如
select\\\*froms1whereid=333;就需要为id加上索引 - 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快，比如
createindexidxons1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了。 - 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI
MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即BalanceTrue）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.
六、正确使用索引
6.1索引未命中
并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题：
1、范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!=、between...and...、like、大于号、小于号
不等于！=
between...and...
like
2、尽量选择区分度高的列作为索引,区分度的公式是count(distinctcol)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
先把表中的索引都删除，让我们专心研究区分度的问题：
mysql>descs1;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|id|int(11)|YES|MUL|NULL||
|name|varchar(20)|YES||NULL||
|gender|char(5)|YES||NULL||
|email|varchar(50)|YES|MUL|NULL||
+--------+-------------+------+-----+---------+-------+
rowsinset(0.00sec)
mysql>dropindexaons1;
QueryOK,0rowsaffected(0.20sec)
Records:0Duplicates:0Warnings:0
mysql>dropindexdons1;
QueryOK,0rowsaffected(0.18sec)
Records:0Duplicates:0Warnings:0
mysql>descs1;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|id|int(11)|YES||NULL||
|name|varchar(20)|YES||NULL||
|gender|char(5)|YES||NULL||
|email|varchar(50)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
rowsinset(0.00sec)
分析原因：
我们编写存储过程为表s1批量添加记录，name字段的值均为egon，也就是说name这个字段的区分度很低（gender字段也是一样的，我们稍后再搭理它）
回忆b+树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即左1<左2<左3<...
而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b+树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。本例中就是这种极端的情况，name字段所有的值均为'nick'
现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？
- 如果条件是name='xxxx',那么肯定是可以第一时间判断出'xxxx'是不在索引树中的（因为树中所有的值均为'nick’），所以查询速度很快
- 如果条件正好是name='nick',查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的IO次数没有多大区别，所以速度很慢
3、索引列不能在条件中参与计算，保持列“干净”，比如from_unixtime(create_time)=’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time=unix_timestamp(’2014-05-29’)
4、and/or
- and与or的逻辑
条件1and条件2：所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
条件1or条件2：只要有一个条件成立则最终结果就成立
4.and的工作原理
条件：a=10andb='xxx'andc>3andd=4
索引：制作联合索引(d,a,b,c)
工作原理：对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序
8.or的工作原理
条件：a=10orb='xxx'orc>3ord=4
索引：制作联合索引(d,a,b,c)
工作原理：对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d
在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），会依次往右找到一个区分度高的索引字段，加速查询。
经过分析，在条件为name='nick'andgender='male'andid>333andemail='xxx'的情况下，我们完全没必要为前三个条件的字段加索引，因为只能用上email字段的索引，前三个字段的索引反而会降低我们的查询效率
5、最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a=1andb=2andc>3andd=4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
6、其他情况
-使用函数
select*fromtb1wherereverse(email)='nick';
-类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select*fromtb1whereemail=999;
#排序条件为索引，则select字段必须也是索引字段，否则无法命中
-orderby
selectnamefroms1orderbyemaildesc;
当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
selectemailfroms1orderbyemaildesc;
特别的：如果对主键排序，则还是速度很快：
select*fromtb1orderbyniddesc;
-组合索引最左前缀
如果组合索引为：(name,email)
nameandemail--命中索引
name--命中索引
email--未命中索引
-count(1)或count(列)代替count(*)在mysql中没有差别了
-createindexxxxxontb(title(19))#text类型，必须制定长度
6.2其他注意事项
- 避免使用select*
- 使用count(*)
- 创建表时尽量使用char代替varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
七、联合索引和覆盖索引
7.1联合索引
联合索引是指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处仅在于有多个索引列，如下：
mysql>createtablet(
->aint,
->bint,
->primarykey(a),
->keyidx_a_b(a,b)
->);
QueryOK,0rowsaffected(0.11sec)
那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结果。从本质上来说，联合索引就是一棵B+树，不同的是联合索引的键值得数量不是1，而是>=2。接着来讨论两个整型列组成的联合索引，假定两个键值得名称分别为a、b如图：
可以看到这与我们之前看到的单个键的B+树并没有什么不同，键值都是排序的，通过叶子结点可以逻辑上顺序地读出所有数据，就上面的例子来说，即（1,1），（1,2），（2,1），（2,4），（3,1），（3,2），数据按（a,b）的顺序进行了存放。
因此，对于查询select*fromtablewherea=xxxandb=xxx,显然是可以使用(a,b)这个联合索引的，对于单个列a的查询select*fromtablewherea=xxx,也是可以使用（a,b）这个索引的。
但对于b列的查询select*fromtablewhereb=xxx,则不可以使用（a,b）索引，其实你不难发现原因，叶子节点上b的值为1、2、1、4、1、2显然不是排序的，因此对于b列的查询使用不到(a,b)索引
联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理，例如在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以帮我们避免多一次的排序操作，因为索引本身在叶子节点已经排序了，如下
#===========准备表==============
createtablebuy_log(
useridintunsignednotnull,
buy_datedate
);
insertintobuy_logvalues
(1,'2009-01-01'),
(2,'2009-01-01'),
(3,'2009-01-01'),
(1,'2009-02-01'),
(3,'2009-02-01'),
(1,'2009-03-01'),
(1,'2009-04-01');
altertablebuy_logaddkey(userid);
altertablebuy_logaddkey(userid,buy_date);
#===========验证==============
mysql>showcreatetablebuy_log;
|buy_log|CREATETABLE`buy_log`(
`userid`int(10)unsignedNOTNULL,
`buy_date`dateDEFAULTNULL,
KEY`userid`(`userid`),
KEY`userid_2`(`userid`,`buy_date`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8|
#可以看到possible_keys在这里有两个索引可以用，分别是单个索引userid与联合索引userid_2,但是优化器最终选择了使用的key是userid因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多
mysql>explainselect*frombuy_logwhereuserid=2;
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
|1|SIMPLE|buy_log|ref|userid,userid_2|userid|4|const|1||
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
rowinset(0.00sec)
#接着假定要取出userid为1的最近3次的购买记录，用的就是联合索引userid_2了，因为在这个索引中，在userid=1的情况下，buy_date都已经排序好了
mysql>explainselect*frombuy_logwhereuserid=1orderbybuy_datedesclimit3;
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
|1|SIMPLE|buy_log|ref|userid,userid_2|userid_2|4|const|4|Usingwhere;Usingindex|
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
rowinset(0.00sec)
#ps：如果extra的排序显示是Usingfilesort，则意味着在查出数据后需要二次排序(如下查询语句，没有先用whereuserid=3先定位范围，于是即便命中索引也没用，需要二次排序)
mysql>explainselect*frombuy_logorderbybuy_datedesclimit3;
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
|1|SIMPLE|buy_log|index|NULL|userid_2|8|NULL|7|Usingindex;Usingfilesort|
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
#对于联合索引（a,b）,下述语句可以直接使用该索引，无需二次排序
select...fromtablewherea=xxxorderbyb;
#然后对于联合索引(a,b,c)来首，下列语句同样可以直接通过索引得到结果
select...fromtablewherea=xxxorderbyb;
select...fromtablewherea=xxxandb=xxxorderbyc;
#但是对于联合索引(a,b,c)，下列语句不能通过索引直接得到结果，还需要自己执行一次filesort操作，因为索引（a，c)并未排序
select...fromtablewherea=xxxorderbyc;
7.2覆盖索引
InnoDB存储引擎支持覆盖索引（coveringindex，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
注意：覆盖索引技术最早是在InnoDBPlugin中完成并实现，这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0以下的，InnoDB存储引擎不支持覆盖索引特性。
对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primarykey1，priameykey2，...,key1，key2，...）。例如：
selectagefroms1whereid=123andname='nick';#id字段有索引，但是name字段没有索引,该sql命中了索引，但未覆盖，需要去聚集索引中再查找详细信息。
最牛逼的情况是，索引字段覆盖了所有，那全程通过索引来加速查询以及获取结果就ok了
mysql>descs1;
+--------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+-------------+------+-----+---------+-------+
|id|int(11)|NO||NULL||
|name|varchar(20)|YES||NULL||
|gender|char(6)|YES||NULL||
|email|varchar(50)|YES||NULL||
+--------+-------------+------+-----+---------+-------+
rowsinset(0.21sec)
mysql>explainselectnamefroms1whereid=1000;#没有任何索引
+--+-----------+-----+----------+----+-------------+----+-------+----+-------+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+----+-------+----+-------+--------+-----------+
|1|SIMPLE|s1|NULL|ALL|NULL|NULL|NULL|NULL|2688336|10.00|Usingwhere|
+--+-----------+-----+----------+----+-------------+----+-------+----+-------+--------+-----------+
rowinset,1warning(0.00sec)
mysql>createindexidx_idons1(id);#创建索引
QueryOK,0rowsaffected(4.16sec)
Records:0Duplicates:0Warnings:0
mysql>explainselectnamefroms1whereid=1000;#命中辅助索引，但是未覆盖索引，还需要从聚集索引中查找name
+--+-----------+-----+----------+----+-------------+------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+------+-------+-----+----+--------+-----+
|1|SIMPLE|s1|NULL|ref|idx_id|idx_id|4|const|1|100.00|NULL|
+--+-----------+-----+----------+----+-------------+------+-------+-----+----+--------+-----+
rowinset,1warning(0.08sec)
mysql>explainselectidfroms1whereid=1000;#在辅助索引中就找到了全部信息，Usingindex代表覆盖索引
+--+-----------+-----+----------+----+-------------+------+-------+-------+------+----------+-----+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+--+-----------+-----+----------+----+--------------------+-------+-------+------+----------+-----+
|1|SIMPLE|s1|NULL|ref|idx_id|idx_id|4|const|1|100.00|Usingindex|
+--+-----------+-----+----------+----+-------------+------+-------+-----+----+--------+-----------+
rowinset,1warning(0.03sec)
覆盖索引的另外一个好处是对某些统计问题而言的。基于上一小结创建的表buy_log，查询计划如下：
mysql>explainselectcount(*)frombuy_log;
+--+-----------+-------+-----+-------------+------+-------+----+----+-----------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+--+-----------+-------+-----+-------------+------+-------+----+----+-----------+
|1|SIMPLE|buy_log|index|NULL|userid|4|NULL|7|Usingindex|
+--+-----------+-------+-----+-------------+------+-------+----+----+-----------+
rowinset(0.00sec)
innodb存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器的选择如上key为userid辅助索引
对于（a,b）形式的联合索引，一般是不可以选择b中所谓的查询条件。但如果是统计操作，并且是覆盖索引，则优化器还是会选择使用该索引，如下：
#联合索引userid_2（userid,buy_date）,一般情况，我们按照buy_date是无法使用该索引的，但特殊情况下：查询语句是统计操作，且是覆盖索引，则按照buy_date当做查询条件时，也可以使用该联合索引
mysql>explainselectcount(*)frombuy_logwherebuy_date>='2011-01-01'andbuy_date<'2011-02-01';
+--+-----------+-------+-----+-------------+--------+-------+----+----+------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+--+-----------+-------+-----+-------------+--------+-------+----+----+------------------------+
|1|SIMPLE|buy_log|index|NULL|userid_2|8|NULL|7|Usingwhere;Usingindex|
+--+-----------+-------+-----+-------------+--------+-------+----+----+------------------------+
rowinset(0.00sec)
7.3合并索引
mysql>explainselectcount(email)fromindex_twhereid=1000000oremail='eva100000@oldboy';
+--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+
|1|SIMPLE|index_t|index_merge|PRIMARY,email,ind_id,ind_email|PRIMARY,email|4,51|NULL|2|Usingunion(PRIMARY,email);Usingwhere|
+--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+
rowinset(0.01sec)
八、查询优化神器-explain
MySQL性能分析之Explain：https://www.cnblogs.com/nickchen121/p/11155917.html
九、慢查询优化的基本步骤
- 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
- where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
- explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
- orderbylimit形式的sql语句让排序的表优先查
- 了解业务方使用场景
- 加索引时参照建索引的几大原则
- 观察结果，不符合预期继续从0分析
十、慢日志管理
慢日志
-执行时间>10
-未命中索引
-日志文件路径
配置：
-内存
showvariableslike'%query%';
showvariableslike'%queries%';
setglobal变量名=值
-配置文件
mysqld--defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf内容：
slow_query_log=ON
slow_query_log_file=D:/....
注意：修改配置文件之后，需要重启服务
MySQL日志管理
========================================================
错误日志:记录MySQL服务器启动、关闭及运行错误等信息
二进制日志:又称binlog日志，以二进制文件的方式记录数据库中除SELECT以外的操作
查询日志:记录查询的信息
慢查询日志:记录执行时间超过指定时间的操作
中继日志：备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放
通用日志：审计哪个账号、在哪个时段、做了哪些事件
事务日志或称redo日志：记录Innodb事务相关的如事务执行时间、检查点等
========================================================
一、bin-log
1.启用
#vim/etc/my.cnf
[mysqld]
log-bin[=dir\[filename]]
#servicemysqldrestart
2.暂停
//仅当前会话
SETSQL_LOG_BIN=0;
SETSQL_LOG_BIN=1;
3.查看
查看全部：
#mysqlbinlogmysql.000002
按时间：
#mysqlbinlogmysql.000002--start-datetime="2012-12-0510:02:56"
#mysqlbinlogmysql.000002--stop-datetime="2012-12-0511:02:54"
#mysqlbinlogmysql.000002--start-datetime="2012-12-0510:02:56"--stop-datetime="2012-12-0511:02:54"
按字节数：
#mysqlbinlogmysql.000002--start-position=260
#mysqlbinlogmysql.000002--stop-position=260
#mysqlbinlogmysql.000002--start-position=260--stop-position=930
4.截断bin-log（产生新的bin-log文件）
a.重启mysql服务器
b.#mysql-uroot-p123-e'flushlogs'
5.删除bin-log文件
#mysql-uroot-p123-e'resetmaster'
二、查询日志
启用通用查询日志
#vim/etc/my.cnf
[mysqld]
log[=dir\[filename]]
#servicemysqldrestart
三、慢查询日志
启用慢查询日志
#vim/etc/my.cnf
[mysqld]
log-slow-queries[=dir\[filename]]
long_query_time=n
#servicemysqldrestart
MySQL5.6:
slow-query-log=1
slow-query-log-file=slow.log
long_query_time=3单位为秒
查看慢查询日志
测试:BENCHMARK(count,expr)
SELECTBENCHMARK(50000000,2*3);
本文内容总结：一、初识索引，1.1为什么要有索引？，1.2什么是索引？，1.3你是否对索引存在误解？，二、索引的原理，2.1索引原理，2.2磁盘IO与预读，三、索引的数据结构，四、MySQL索引管理，4.1功能，4.2MySQL常用的索引，4.3各个索引应用场景，4.4索引的两大类型hash与btree，4.5创建/删除索引的语法，4.6示例，五、测试索引，5.1数据准备，5.2小结，六、正确使用索引，6.1索引未命中，6.2其他注意事项，七、联合索引和覆盖索引，7.1联合索引，7.2覆盖索引，7.3合并索引，八、查询优化神器-explain，九、慢查询优化的基本步骤，十、慢日志管理，
原文链接：https://www.cnblogs.com/nickchen121/p/11155947.html