索引原理与慢查询优化

一介绍

为何要有索引？

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

什么是索引？

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

索引的利弊权衡

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。

二索引的原理

1 索引原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

2 磁盘IO与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

三索引的数据结构

前面讲了索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。

如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的容量/ 数据项的数量，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2.索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

四聚集索引与辅助索引

在数据库中，B+树的高度一般都在2_{4层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO，2}4次的IO意味着查询时间只需要0.02~0.04秒。

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

1 聚集索引

innodb存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分，同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接

如果未定义主键，mysql取第一个唯一索引(unique)而且只含有非空列(not null)作为主键，innodb使用它作为聚簇索引。

如果没有这样的列，innodb就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚簇索引。在多少情况下，查询优化器倾向于采用聚簇索引。因为聚簇索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快的访问针对范围的值得查询。

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

2 辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据(如果包含庞大的记录重复对硬盘是种浪费)。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。如下图

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

五 mysql索引管理

1 功能

1.索引的功能就是加速查找
2.mysql中的primary kei , unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束功能

2 mysql常用的索引

普通索引index：加速查找

唯一索引：
	-主键索引primary key:加速查找+约束(不为空、不能重复)
	-唯一索引unique:加速查找+约束(不能重复)
	
联合索引：
	-primary key(id,name):联合主键索引
	-unique(id,name):联合唯一索引
	-index(id,name):联合普通索引

3 索引的两大类型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 创建/删除索引的语法

#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;

六测试索引

1 准备

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

2 在没有索引的前提下测试查询速度

#无索引：innodb会自动建立一个索引，每个索引的大小为6个字节且是隐藏的，而一般使用Int类型字节为4个，
#那么相比之下索引越大则磁盘块的索引数量越小，(横向变短则纵向变长)那么树的高度越高，则IO更多。
mysql> SELECT * from s1 where id=999999999;
Empty set (1.00 sec)
#但该隐藏索引只提供了B+树的架构，由于不是实际表中的索引，无法以索引细分范围。
#定义了一个不存在的id那么会访问每一个磁盘块，得到一个总的IO

3 建立Index索引

在表中已经存在大量数据的情况下，为某个字段建立索引，那么建立速度很慢。

mysql> create index a on s1(id);
Query OK, 0 rows affected (3.15 sec)

4 建立索引后再次查询

mysql> SELECT * from s1 where id=999999999;
Empty set (0.00 sec)    #提速效果明显

5 总结

#1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

七正确使用索引

1 索引未命中

并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题

（1）范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like。

大于号>、小于号<

SELECT count(*) from s1 where id=1000;
OK, Time: 0.000000s

1 row in set (0.00 sec) 明确指定要找1000这个Id,在索引树中可以快速找到

SELECT count(*) from s1 where id>1000;
OK, Time: 0.660000s 
同上，也会利用索引树，但是没有明确指定需要的值，而只是一个范围，
那么会在这个范围内所有的id相对应的磁盘块都访问一遍，那整体下来IO时间就会过长

SELECT count(*) from s1 where id>1000 and id<2000;
OK, Time: 0.000000s
当然，如果访问的范围不大，遇到的IO次数不多，则能很快输出

不等于！=

SELECT count(*) from s1 where id!=1000;
OK, Time: 0.688000s
不等于1000也是一个很大的范围了，遇到IO的次数也过多了，就是除了id=1000的磁盘块没有访问遇到IO以外，
其他的都遇到了IO

Between .... and

SELECT count(*) from s1 where id BETWEEN 1 and 3000000;
OK, Time: 0.741000s
范围过大，遇到IO次数过多，查询速度慢

SELECT count(*) from s1 where id BETWEEN 1 and 10;
OK, Time: 0.000000s
范围过小，遇到IO次数过少，查询速度快

SELECT count(*) from s1 where email='xxxx';
OK, Time: 0.980000s
没有给email设定为索引，所以访问了所有的磁盘块

CREATE INDEX d on s1(email);
OK, Time: 3.623000s

SELECT count(*) from s1 where email='xxxx';
OK, Time: 0.000000s
给email加索引后，查询速度加快

SELECT count(*) from s1 where email like 'xxxx%';
OK, Time: 0.002000s
%放在最后，查询速度很快

SELECT count(*) from s1 where email like '%xxxx';
OK, Time: 1.050000s
%放在前面，查询速度很慢

匹配的先后问题，先有xxxx再去匹配能迅速确定范围进行类似查找
%放在前面，会将每个数据项对比一遍，即访问了所有的磁盘块。

2 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

#先删除已存在索引再进行分析
drop index a on s1;

drop index d on s1;

=======================
SELECT count(*) from s1 where name='xxx';
OK, Time: 0.943000s
不为索引查询速度变慢

CREATE INDEX b on s1(name);
OK, Time: 3.539000s

SELECT count(*) from s1 where name='xxx';
OK, Time: 0.000000s
成为索引查询速度变快

SELECT count(*) from s1 where name='egon';
OK, Time: 0.753000s
成为索引查询速度还是很慢。

原因：
在name这个字段下的所有记录均为egon，也就是说name这个字段的区分度很低。
回忆B+树的结构，查询速度与树的高度成反比，要想将树的高低控制很低，第一：某一层数据项均是按照从左到右，从小到大的关系横向排开，在通过二分法细分范围，而对于区分度低的字段，无法找到大小关系，因为值都相等，那么这种情况下B+树的会因为没有分支而高度不断增加，由于都是相等没有一个分支，B+树几乎成了一根棍就是线性关系，此时再查询其实就是在遍历每一个数据项。第二：name的数据类型的大小过大，会造成数据页中的数据项变少，那么直接影响了树的高度将变高。

注意：
name='xxxx'，那么肯定第一时间判断出‘xxxx’是不在索引树内的(树中索引的值均为'egon')

3 =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

4 索引列不能参与计算，保持列“干净”

DROP index b on s1;

CREATE index a on s1(id);

SELECT COUNT(*) from where id=1000;
OK, Time: 0.000000s
id成为了索引能迅速查出

SELECT COUNT(*) from where id*3=3000;
OK, Time: 0.740000s
id参与了计算，无法拿到一个明确的值去索引树去找，
相当于带这个式子去了每一数据页中然后每对比一个数据项就进行一个计算再比对。

SELECT COUNT(*) from s1 where id=3000/3;
OK, Time: 0.000000s
这样是计算完了，id带着明确的值去索引树去找

5 and/or

#1、and与or的逻辑
    条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
    条件1 or 条件2:只要有一个条件成立则最终结果就成立

#2、and的工作原理
    条件：
        a = 10 and b = 'xxx' and c > 3 and d =4
    索引：
        制作联合索引(d,a,b,c)
    工作原理:
        对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快            				 速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

#3、or的工作原理
    条件：
        a = 10 or b = 'xxx' or c > 3 or d =4
    索引：
        制作联合索引(d,a,b,c)
        
    工作原理:
        对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

CREATE index b on s1(name);
OK, Time: 2.890000s
#为name字段添加索引

SELECT COUNT(*) FROM s1 WHERE name='xxx';
OK, Time: 0.000000s
#name='xxx'很快就能从索引树中排除出去

select COUNT(*) from s1 where name='xxx' and gender='male';
OK, Time: 0.000000s
#gender是非索引字段，但是name='xxx'不成立，则根本不会管gender的条件，
#且运行速度很快

在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），会依次往右找到一个区分度高的索引字段，加速查询
select COUNT(*) from s1 where name='egon' and gender='male';
OK, Time: 12.604000s

CREATE INDEX c ON s1(gender);
#字段index设为索引

select COUNT(*) from s1 where name='egon' and gender='male';
OK, Time: 2.052000s
#速度稍快一点

select COUNT(*) from s1 where name='egon' and gender='xxx';
OK, Time: 0.000000s

select COUNT(*) from s1 where name='egon' and gender='male' and id=3;
OK, Time: 0.000000s
#指定id=3有区分了，迅速查到

select COUNT(*) from s1 where name='egon' and gender='male' and id>3;
OK, Time: 11.835000s
#id>3有区分，但是范围过大没有明确的数据项

select COUNT(*) from s1 where name='egon' and gender='xxx' and id>3;
OK, Time: 0.000000s
#gender='xxx'直接判断没有此数据项，也不会管后面的条件了

经过分析，在条件为name='egon' and gender='male' and id>333 and email='xxx'的情况下，我们完全没必要为前三个条件的字段加索引，因为只能用上email字段的索引，前三个字段的索引反而会降低我们的查询效率。

6 最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

CREATE INDEX xxx on s1(id,name,gender,email);
#把id范围比较放在最前面，查询速度很慢(找到每个ID号再去匹配后面的条件，每一次都判断一下gender='male'不存在然后结束)那么相对id后面的字段都没有用到索引(跟着ID做重复的判断)。
SELECT COUNT(*) from s1 where name='egon' and gender='male' and id>333 and email='xxx';
OK, Time: 3.993000s
#匹配顺序按照建立组合索引时从左到右的顺序，where后接的顺序可以乱序

CREATE INDEX yyy on s1(name,gender,email,id);
##把id范围比较放在最后面，查询速度很快(匹配好前面的条件直接判定email='xxx'不存在，就结束)
SELECT COUNT(*) from s1 where name='egon' and gender='male' and id>333 and email='xxx';
OK, Time: 0.005000s

2 其他注意事项

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时  varchar 代替 char
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

八覆盖索引与联合索引

1 联合索引

联合索引时指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列，如下

mysql> create table t(
    -> a int,
    -> b int,
    -> primary key(a),
    -> key idx_a_b(a,b)
    -> );
Query OK, 0 rows affected (0.11 sec)

那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结果。从本质上来说，联合索引就是一棵B+树，不同的是联合索引的键值得数量不是1，而是>=2。接着来讨论两个整型列组成的联合索引，假定两个键值得名称分别为a、b如图

可以看到这与我们之前看到的单个键的B+树并没有什么不同，键值都是排序的，通过叶子结点可以逻辑上顺序地读出所有数据，就上面的例子来说，即（1,1），（1,2），（2,1），（2,4），（3,1），（3,2），数据按（a,b）的顺序进行了存放。

因此，对于查询select * from table where a=xxx and b=xxx, 显然是可以使用(a,b) 这个联合索引的，对于单个列a的查询select * from table where a=xxx,也是可以使用（a,b）这个索引的。

但对于b列的查询select * from table where b=xxx,则不可以使用（a,b）索引，其实你不难发现原因，叶子节点上b的值为1、2、1、4、1、2显然不是排序的，因此对于b列的查询使用不到(a,b) 索引

联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理

2 回表查询

先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。
select id,age,name from user where age = 10;
在辅助索引中找到了id且age已确定，但还需要查找name，于是需要再通过聚簇索引找到name

3 索引覆盖

只需要在辅助索引的索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
例如：select id,age from user where age = 10;
age已确定，直接找到主键id即可。

4 如何实现覆盖索引

常见的方法是：将被查询的字段，建立到联合索引里去。
1、如实现：select id,age from user where age = 10;
explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B+树即可查询到相应的结果，这样就实现了覆盖索引

2、实现：select id,age,name from user where age = 10;
explain分析：age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询

为了实现索引覆盖，需要建组合索引idx_age_name(age,name)

drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);

explain分析：此时字段age和name是组合索引idx_age_name，查询的字段id、age、name的值都在索引树上，只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖。

九慢查询优化的基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析

posted @ 2020-10-07 18:23 artherwan 阅读(120) 评论(0) 编辑收藏举报

刷新页面返回顶部

artherwan