MySQL索引及优化

索引

什么是索引

MySQL官方对索引的定义为：索引(Index) 是帮助MySQL高效获取数据的数据结构。

排好顺序的快速查找数据的结构。

数据本身之外，数据库维护着满足特定算法的数据结构，这种数据结构以某种方式指向数据，这样可以在这些数据结构上实现高级的查找算法，这种数据结构称为索引。

一般来说索引也很大，不可能全部存储在内存中，因此索引往往以文件的形式储存在磁盘中。我们平常所说的索引，如果没有特别指明，都是指B数(多路搜索树，不一定是二叉的)结构组织的索引。

索引的优缺点

优势：

1. 提高数据检索的效率，降低数据库的IO成本；
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

劣势：

1. 索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间；
2. 虽然索引大大提高了查询效率，但是降低了更新表的速度，如insert、update和delete操作。因为更新表时，MySQL不仅要保存数据，还要保存索引文件每次更新的索引列字段，并且在更新操作后，会更新相应字段索引的信息；
3. 索引只是提高查询效率的一个因素，如果你的MySQL有大量的数据表，就需要花时间研究建立最优秀的索引或优化查询语句。

索引分类

索引主要分为以下三类：

1. 单值索引：一个索引只包含单个列，一个表可以有多个单值索引；
2. 唯一索引：索引列的值必须唯一，但允许有空值，主键就是唯一索引；
3. 复合索引：一个索引包含多个列。

基本语法

创建索引

create [unique] index indexname on tablename(columnname(length));

alter table tablename add index indexname (columnname(length));

删除索引

drop index indexname on tablename;

查看索引

show index from tablename;

其他创建索引的方式

1.添加主键索引 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 

2.添加唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`) 

3.添加全文索引
ALTER TABLE `table_name` ADD FULLTEXT (`column`) 

4.添加普通索引
ALTER TABLE `table_name` ADD INDEX index_name (`column` ) 

5.添加组合索引 
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)

索引的结构

①BTREE索引；②Hash索引；③Full-Text索引；④R-Tree索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

建立索引与否的具体情况

需建立索引的情况

1. 主键自动建立唯一索引；
2. 频繁作为查询条件的字段；
3. 查询中与其他表关联的字段，外键关系建立索引；
4. 高并发下趋向创建组合索引；
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；
6. 查询中统计或分组字段。

不需要创建索引的情况

1. 表记录太少；（数据量太少MySQL自己就可以搞定了，300万）
2. 经常增删改的表；
3. 数据重复且平均分配的字段，如国籍、性别，不适合创建索引；
4. 频繁更新的字段不适合建立索引；
5. where条件里用不到的字段不创建索引。

B-Tree和B+Tree

减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以B-Tree和B+Tree就在这样伟大的时代背景下诞生了。目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

B-树

是一种多路搜索树（并不是二叉的）：

1. 定义任意非叶子结点最多只有M个儿子；且M>2；
2. 根结点的儿子数为[2, M]；
3. 除根结点以外的非叶子结点的儿子数为[M/2, M]；
4. 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5. 非叶子结点的关键字个数=指向儿子的指针个数-1；
6. 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7. 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8. 所有叶子结点位于同一层；
9. 每个k对应一个data。

如：（M=3）相当于一个2–3树，2–3树是一个这样的一棵树， 它的每个节点要么有2个孩子和1个数据元素，要么有3个孩子和2个数据元素，叶子节点没有孩子，并且有1个或2个数据元素。

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点。

B-树的特性：

1. 关键字集合分布在整颗树中；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束；
4. 其搜索性能等价于在关键字全集内做一次二分查找；
5. 自动层次控制；

往B树中依次插入：6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

插入或者删除元素都会导致节点发生裂变反应，有时候会非常麻烦，但正因为如此才让B树能够始终保持多路平衡，这也是B树自身的一个优势：自平衡。

B+树

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

1. 非叶子结点的子树指针与关键字个数相同；
2. 非叶子结点的子树指针P[i]，指向关键字值属于[K[i],K[i+1])的子树（B-树是开区间）；
3. 为所有叶子结点增加一个链指针；
4. 所有关键字都在叶子结点出现；
5. 内节点不存储data，只存储key

如：（M=3）

B+树的特征：

1. 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

B+树的优势：

1. B+树的中间节点没有卫星数据，同样大小的磁盘页可以容纳更多的节点元素，单一节点存储更多的元素，使得查询的IO次数更少；
2. 所有查询都要查找到叶子节点，查询性能稳定；
3. 所有叶子节点形成有序链表，便于范围查询。

SHOW GLOBAL STATUS like 'Innodb_page_size'可以看到每个节点(文件页)大小16K，一次IO读写也是16K。

bigInt 8B + 下一层节点指针 6B = 14B，一个节点可以存储16KB/14B=1170个元素，三层的B+树可以存储1170 × 1170 × 16 ≈ 20000000 数据。

聚簇索引

参考：https://www.cnblogs.com/jiawen010/p/11805241.html

聚簇索引(聚集索引)：叶结点包括了完整的数据记录。InnoDB是聚簇索引，MyISAM是非聚簇索引。

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

优点：

1. 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快；
2. 聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2. 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可新
3. 辅助索引(二级索引)访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

P.S. 为什么主键通常建议使用自增id?

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

辅助索引

在聚簇索引之上创建的索引称之为辅助索引(非聚簇索引)，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

MyISAM

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

MySQL如果使用MyISAM存储引擎，数据库的文件类型就包括.frm(存储表结构)，.MYD(存储数据)与.MYI(存储索引)。索引文件和数据文件是分离的：

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

InnoDB

MySQL如果使用InnoDB存储引擎，数据库的文件类型就包括.frm(存储表结构)，.ibd(索引+数据)。

表数据文件本身就是按照B+Tree组织的一个索引结构文件，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这就是问什么InnoDB表必须有主键，并且推荐使用整型的自增主键(保证永远是插入在叶节点的最后一个)。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

对比两种索引形式：

覆盖索引

回表查询：

先通过普通索引定位到主键值，再通过聚集索引定位到行记录，这就是所谓的回表查询。由于要先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

只需要在一棵索引树上就能获取SQL所需的所有列数据，即为覆盖索引，无需回表，速度更快。当explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

Using index condition表示需要回表。

复合索引

复合索引(联合索引)就是对多个字段同时建立的索引。

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)

复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

复合索引就是按照用户指定的顺序在树里多保存了几个字段。

非聚集索引叶节点的DATA只是有一个指针指向对应的数据块，因此如果使用非聚集索引查询，而查询列中包含了其它该索引没有覆盖的列，那么还要进行第二次回表查询，查询节点上对应的数据行的数据。通过建立复合索引(col1, col2)可以解决该问题，因为复合索引的列包括了col1和col2，不需要回表：

select col1, col2 from t1 where col1 = '213';

参考：

• https://www.zhihu.com/question/36996520/answer/93256153
• https://www.cnblogs.com/happyflyingpig/p/7662881.html

性能调优

MySQL常见瓶颈：

1. CPU：CPU饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候；
2. IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候；
3. 服务器硬件的性能瓶颈。

explain（执行计划），使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈。

explain的作用

通过explain+sql语句可以知道如下内容：

1. 表的读取顺序。（对应id）
2. 数据读取操作的操作类型。（对应select_type）
3. 哪些索引可以使用。（对应possible_keys）
4. 哪些索引被实际使用。（对应key）
5. 表之间的引用。（对应ref）
6. 每张表有多少行被优化器查询。（对应rows）

explain包含的信息

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，该字段通常与table字段搭配来分析。

1.id相同，执行顺序从上到下

id相同，执行顺序从上到下，搭配table列进行观察可知，执行顺序为t1->t3->t2。

2.id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高

如果是子查询id的序号会递增，id值越大执行优先级越高，搭配table列可知，执行顺序为t3->t1->t2。

3.id相同不同，同时存在
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b5GQSA1e-1586833703392)(http://picture.tjtulong.top/706569-20180621233603807-1370399356.png)]
id如果相同，可认为是同一组，执行顺序从上到下。在所有组中，id值越大执行优先级越高。所以执行顺序为t3->derived2(衍生表，也可以说临时表)->t2。

select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：

1. SIMPLE
   简单的select查询，查询中不包含子查询或union查询。
2. PRIMARY
   查询中若包含任何复杂的子部分，最外层查询为PRIMARY，也就是最后加载的就是PRIMARY。
3. SUBQUERY
   在select或where列表中包含了子查询，就为被标记为SUBQUERY。
4. DERIVED
   在from列表中包含的子查询会被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，将结果放在临时表中。
5. UNION
   若第二个select出现在union后，则被标记为UNION，若union包含在from子句的子查询中，外层select将被标记为DERIVED。
6. UNION RESULT
   从union表获取结果的select。

type

表示查询所使用的访问类型，type的值主要有八种，该值表示查询的sql语句好坏，从最好到最差依次为：system>const>eq_ref>ref>range>index>ALL。

system

表只有一行记录（等于系统表），是const的特例类型，平时不会出现，可以忽略不计。

const

表示通过一次索引就找到了结果，常出现于primary key或unique索引。因为只匹配一行数据，所以查询非常快。如将主键置于where条件中，MySQL就能将查询转换为一个常量。

eq_ref

唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描。

ref

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。

range

只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。

这种索引的范围扫描比全表扫描要好，因为索引的开始点和结束点都固定，不用扫描全索引。

index

全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。

ALL

全表扫描。

注：一般来说，需保证查询至少达到range级别，最好能达到ref。

possible_keys和key、key_len

possible_keys：显示可能应用在表中的索引，可能一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际中使用的索引，如为NULL，则表示未使用索引。若查询中使用了覆盖索引，则该索引和查询的select字段重叠。
简单理解：possible_keys表示理论上可能用到的索引，key表示实际中使用的索引。
key_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

ref

显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。

• tb_emp表为非唯一性索引扫描，实际使用的索引列为idx_name，由于tb_emp.name='rose’为一个常量，所以ref=const。
• tb_dept为唯一索引扫描，从sql语句可以看出，实际使用了PRIMARY主键索引，ref=db01.tb_emp.deptid表示关联了db01数据库中tb_emp表的deptid字段。

rows

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。

extra

Using filesort

Using filesort表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。

出现Using filesort就非常危险了，在数据量非常大的时候几乎“九死一生”。出现Using filesort尽快优化sql语句。

DROP TABLE IF EXISTS `tb_dept`;
CREATE TABLE `tb_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

deptname字段未建索引的情况：

为deptname字段创建索引后：

p.s. 若tb_dept表中还有别的列，那么依然是Using filesort，必须建立复合索引优化。

Using temporary

使用了临时表保存中间结果，常见于排序order by和分组查询group by。非常危险，“十死无生”，急需优化。

Using index

表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。

如果同时出现了Using where，表明索引被用来执行索引键值的查找。（where deptid=1）

如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

删除tb_emp表中name和deptid字段的单独索引，创建复合索引：

索引优化

准备：创建经典的tb_emp表

DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(11) NOT NULL,
  gender varchar(10) NOT NULL,
  email varchar(20),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Tom', '22','male','1@qq.com');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Mary', '21','female','2@qq.com');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Jack', '27','male','3@qq.com');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Rose', '23','female','4@qq.com');

全职匹配我最爱

创建索引：
create index idx_nameagegender on tb_emp(name,age,gender);

case1:

case2：

case3：

这三种情况都使用了索引，type=ref，ref=const。

最佳左前缀法则

定义：在创建了多列索引的情况下，查询从索引的最左前列开始且不能跳过索引中的列。

最佳左前缀法则就是说如果创建了多个索引，在使用索引时要按照创建索引的顺序来使用，不能缺少或跳过，当然如果只使用最左边的索引列，也就是第一个索引是可以的，通俗理解：“带头大哥不能死，中间兄弟不能断”。要点：“头不能掉”。

case4：

索引最左边没有用到，变为全表扫描。

不要在索引列上做任何操作

在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效从而转向全表扫描。

这里使用了函数计算，type=ALL，导致索引失效。

范围右边全失效

存储引擎不能使用索引中范围右边的列，也就是说范围右边的索引列会失效。

当使用范围时（age>22），type=range，key_len=86，ref=Null，与Case 1、Case2和Case3可知，使用了部分索引，但gender索引没用上（与Case 3对比），需要大量回表。

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

例如：mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

尽量使用覆盖索引

尽量使用覆盖索引（查询列和索引列尽量一致，通俗说就是对A、B列创建了索引，然后查询中也使用A、B列），减少select * 的使用。

select name,age,gender替代select * 使用覆盖索引（查询列与条件列对应），可看到Extra从Null变成了Using index，提高检索效率。

使用不等于（!=或<>）会使索引失效

结论：使用!=会使type=ALL，key=Null，导致全表扫描，并且索引失效。

is null 或 is not null也无法使用索引

使用is not null的时候，type=ALL全表扫描，key=Null索引失效。

like通配符以%开头会使索引失效

like的%位置不同，所产生的效果不一样，当%出现在左边的时候，type=ALL，key=Null（全表扫描，索引失效），当%出现在右边的时候，type=range，索引未失效。

但是在实际生产环境中，%仅出现在右边可能不能够解决我们的问题，所以解决%出现在左边索引失效的方法：使用覆盖索引。

字符串不加单引号导致索引失效

通过explain执行结果可以看出，字符串（name）不加单引号在查询的时候，导致索引失效（type=ref变成了type=ALL，并且key=Null），并全表扫描。

因为在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。

mysql> select * from tb_emp where CAST(name AS signed int) = 123;

也就是说，这条语句触发了上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

少用or，用or连接会使索引失效

一般性建议：

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引时，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好；
• 在选择组合索引时，尽量选择可以能够包含当前query中的where字句中更多字段的索引；
• 尽可能通过分析统计信息和调整query写法来达到选择合适索引的目的。

对比唯一索引与普通索引

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以建议尽量选择普通索引。

查询截取分析

分析过程：

1. 先观察，至少跑1天，查看生产的慢SQL情况；
2. 开启慢查询日志，设置阈值，比如超过5s就是慢SQL，并将其抓取；
3. explain + 慢SQL；
4. show profile查看SQL在MySQL服务器里面执行细节和生命周期情况；
5. SQL数据库服务器的参数调优。

小表驱动大表

in后面跟的是小表，exists后面跟的是大表。

对于exists：
select .....from table where exists(subquery);

可以理解为：将主查询的数据放入子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据是否得以保留。

ORDER BY优化

虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。

MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

order by满足两种情况会使用Using index：

1. order by语句使用索引最左前列；
2. 使用where子句与order by子句条件列组合满足索引最左前列。

尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。

如果order by的条件不在索引列上，就会产生using filesort，filesort有两种排序算法：双路排序和单路排序。
双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。
单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。但是当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算。

解决方式有：

• 增加sort_buffer_size参数的设置。
• 增大max_length_for_sort_data参数的设置。

提升order by速度的方式：

1. 在使用order by时，不要用select * ，只查询所需的字段。因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作。
2. 尝试提高sort_buffer_size。
3. 尝试提高max_length_for_sort_data。

慢查询日志

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要，一般不建议开启该参数，因为开启慢查询日志或多或少会带来一定的性能影响。

首先查看慢查询日志是否开启：show variables like '%slow_query_log%'

使用如下命令开启慢查询日志:set global slow_query_log = 1

设置long_query_time的值:set global long_query_time = 3

关闭当前连接，重新开一个新的连接，可以查看慢查询的阈值时间：show variables like '%long_query_time%'

select sleep(9)命令可以让查询睡眠9秒

查看慢查询sql的数目：show global status like '%Slow_queries%'

查看日志slowquery.log:

Show Profile

Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

开启功能：

向数据库中插入50w条记录，并进行查询：

select *from tb_emp_bigdata group by id%10 limit 150000;

通过show profiles查看结果：

使用show profile对sql语句进行诊断：

show profile cpu,block io for query Query_ID

日常开发需注意的结论：

• converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
• Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
• Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！
• locked

应用案例

在项目中，有一个动态表，里面有主题、用户id、视频信息、发布时间等字段，现要查询某一主题下最新发布的5条字段。

1. 如果不加索引：全表扫描ALL + Using filesort
2. 只对主题加索引，类似下图(字段名替换了)：

3. 对主题和发布时间加混合索引：

参考：https://www.cnblogs.com/songwenjie/p/9418397.html