mysql不等于（MySQL索引优化）

Mysql索引

一、索引类型

1.1、索引介绍

索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下：

从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引从应用层次划分：普通索引、唯一索引、主键索引、复合索引从索引键值类型划分：主键索引、辅助索引（二级索引）从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

1.2、索引基础回顾

1、普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

创建普通索引的方法如下：

CREATE INDEX <索引的名字> ON tablename (字段名);ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

查看索引的方法：

show index from tableName;

删除索引的方法：

drop index <索引名字> on <表名>;

查看建表语句

show create table users;show index from users;

2、唯一索引

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。

创建唯一索引的方法如下：

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

3、主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

创建主键索引的方法如下：

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );ALTER TABLE tablename ADD PRIMARY KEY (字段名);

4、复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。

创建组合索引的方法如下：

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：

何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

5、全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

创建全文索引的方法如下：

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from user where match(name) against('aaa');

全文索引使用注意事项：

全文索引必须在字符串、文本字段上建立。全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

select * from userwhere match(name) against('a*' in boolean mode);

二、索引原理

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。

索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：二分查找法、Hash和B+Tree。

2.1二分查找法

二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。

首先定位left和right两个指针计算(left+right)/2判断除2后索引位置值与目标值的大小比对索引位置值大于目标值就-1，right移动；如果小于目标值就+1，left移动

举个例子，下面的有序数组有17 个值，查找的目标值是7，过程如下：

第一次查找

第二次查找

第三次查找

第四次查找

2.2Hash结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。其结构如下所示：

从上面结构可以看出，Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了。Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。

InnoDB提供的自适应哈希索引功能强大，接下来重点描述下InnoDB 自适应哈希索引。【用在Buffer Pool的地方】

InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。

InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于B+Tree。

自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。

show engine innodb status G;show variables like '%innodb_adaptive%';

2.3B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

B-Tree结构

索引值和data数据分布在整棵树结构中每个节点可以存放多个索引值及对应的data数据树节点中的多个索引值从左到右升序排列

B-Tree的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。 适合做等值查询（MongoDB）

B+Tree结构

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值叶子节点包含了所有的索引值和data数据叶子节点用指针连接，提高区间的访问性能

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。【适合做范围查询和全表扫描，Mysql默认结构】

为啥使用B+Tree

为啥MySQL使用B+Tree而不使用二叉树或者红黑树或者B-Tree呢？

1、二叉树的弊端，在插入数据的时候比如id是自增那么就会形成单边树，在进行查找的时候会产生很多磁盘io效率比较低。

2、红黑树，会自动平衡二叉树，可以有效解决二叉树的单边树问题，但是对于Mysqllaishuo存储数据可能上千万那么，红黑树因为每个节点只能有两个子节点，也会产生比较多的子节点导致在查找的时候增加磁盘io

3、B-Tree树，每个节点都有固定的大小mysql默认是16k，这样子就可以解决红黑树导致的子节点层级过多的问题，但是B-Tree有另外一个问题，就是每个节点都有所有的数据，如果节点对应的数据过多的话每个节点就不能有太多的横向节点，就会导致跟红黑树一样的问题节点的层级比较多，增加磁盘IO。

4、B+Tree树，Mysql在B-Tree的基础上优化的一种形态的数据结构，B+Tree是非叶子节点都包含数据，这样子非叶子节点就在规定的大小（16k）中包含尽可能多的索引值，就可以减少树节点的层级，有效的减少磁盘IO，所有的数据都在子节点上，并且子节点之间相互指向【方便范围查找】，因此子节点直接可以直接快速的访问。【MySQL通常只有三层就可以解决】

MySQL中有主键索引和非主键索引（辅助索引），主键索引data中存储了所有的数据，非主键索引只存储了当前的列和主键索引。

2.4聚簇索引和辅助索引

1、聚簇索引（聚集索引）

聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

InnoDB的表要求必须要有聚簇索引：

如果表定义了主键，则主键索引就是聚簇索引如果表没有定义主键，则第一个非空unique列作为聚簇索引否则InnoDB会从建一个隐藏的row-id作为聚簇索引

2、辅助索引

InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。【非主键索引的索引就是辅助索引，只存储当前的索引列和主键列】

3、非聚簇索引

与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构。

简单理解，就是索引结构和数据结构分开存储，需要先通过索引找到地址值在通过地址值去查找数据，这样子就增加了磁盘IO。

4、对比

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。

主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。

2.5常见面试题

1、为啥InnoDB表必须要有主键

因为InnoDB表使用的存储引擎是B+Tree而B+Tree必须要有一个自增的主键来构成索引结构。如果不自己建主键的话，Mysql内部会自动生成唯一的列作为主键。

2、为啥要使用自增的整型？

假如使用uuid作为uuid作为主键进行存储的话，没办法进行快速的比较进行查找，并且uuid存储的空间比较大。自增的话方便快速存储。

3、为什么非主键索引结构叶子节点存储的是主键值？

为了数据一致性和节省存储空间。【完整性的数据只需要维护一份就行，就在主键索引中维护即可】

三、索引分析

3.1Explain介绍

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。例如： EXPLAIN SELECT * from user WHERE id < 3;

1、select_type属性

表示查询的类型。常用的值如下：

SIMPLE ： 表示查询语句不包含子查询或unionPRIMARY：表示此查询是最外层的查询【先执行】UNION：表示此查询是UNION的第二个或后续的查询DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果UNION RESULT：UNION的结果SUBQUERY：SELECT子查询语句DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

2、type属性

表示存储引擎查询数据时采用的方式。比较重要，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

NULL：表示不用访问表，速度最快。system、const：使用主键索引或者唯一索引进行等值常量查询，效率比较高，如果查询的条数为一条的话就是system。eq_ref：使用主键索引或者唯一索引进行多表关联时候on之后的条件。表示前面表的每一个记录，都只能匹配后面表的一行结果。（不可能是简单查询）ref：表相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。（可以是简单查询）【explain select * from film where name = 'film1';】range：表示使用索引范围查询。使用>、>=、<、<=、in等等。index：扫描全表索引，这通常比ALL快一些。ALL：全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化

建议：在平时的sql中至少要把sql优化成range级别的才算可以。

NULL> const> eq_ref> ref> range> index>ALL

3、possible_keys

表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。

4、key

表示查询时真正使用到的索引，显示的是索引名称。

5、ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

6、rows

MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。

7、key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

key_len的计算规则如下：

字符串类型

字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4char(n)：n*字符集长度varchar(n)：n * 字符集长度 + 2字节

数值类型

TINYINT：1个字节SMALLINT：2个字节MEDIUMINT：3个字节INT、FLOAT：4个字节BIGINT、DOUBLE：8个字节

时间类型

DATE：3个字节TIMESTAMP：4个字节DATETIME：8个字节

字段属性

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

比如一个users表中name 类型为varchar(50),那么使用到name索引值的话key_len = 53 【50长度 + 2可变长 + 1(Default NULL)】

8、Extra

Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

Using index：使用覆盖索引，不需要回表。【性能最高】Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；【性能其次】Using where：使用 where 语句来处理结果，查询的列未被索引覆盖，需要回表。Using temprorary：使用到了内存表，一般出现于去重、分组等操作。【distinct操作】Using filesort：查询的结果需要在内存中进行排序，效率很低，需要进行优化。Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段。【explain select min(id) from user;】·

3.2回表查询和覆盖索引

回表查询：在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

覆盖索引：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。

3.3最左前缀原则

复合辅助索引存储结构

筛选的话会根据创建索引的顺序诸葛对索引进行筛选，类似于A(B(C)) 只有找到A匹配之后在去匹配B，B匹配之后再去匹配C。不能跨级匹配，比如A匹配之后没有匹配B而直接匹配C不会的。

结论：按照索引创建的顺序，从左向右匹配直到遇到范围查询 > < between 索引失效。

案例数据准备：

CREATE TABLE `employees` ( `id` INT (11) NOT NULL AUTO_INCREMENT, `name` VARCHAR (24) NOT NULL DEFAULT '' COMMENT '姓名', `age` INT (11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`, `age`, `position`) USING BTREE) ENGINE = INNODB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8 COMMENT = '员工记录表';INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',23,'dev',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

案例分析

查询语句一

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

使用了组合索引，key_len是74计算：24*3 + 2 (24个字符，3是urf8编码，2是可变长度 【如果默认值为null的话需要在加1】)

查询语句二

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

使用了索引，key_len = 140计算：24*3+2 + 4 + 20*3+2 = 140（int默认为4字节）

查询语句三

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';

不会使用索引，复合索引存储，是匹配第一个字段【name】，在匹配第二个字段【age】，在匹配第三个字段【position】

查询语句四

EXPLAIN SELECT * FROM employees WHERE age > 22 and name = 'LiLei' AND position ='manager';

此时生效的索引只有name和age，position没有生效。原因，在做范围查找的时候第三个索引就没办法进行精确匹配了。

注意：

1、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。【EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';】 2、在复合索引中使用非最后一个索引的列进行范围查找就会导致后边的索引列失效，就是查询语句四。

3.4字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = 1000;

3.5Like查询

MySQL在使用like模糊查询时，索引能不能起作用？ select * from employees where name like '%o%'; //不起作用

select * from employees where name like 'o%'; //起作用

select * from employees where name like '%o'; //不起作用

3.6 NULL查询

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

EXPLAIN SELECT * FROM employees WHERE name is null

建议在给一些索引筛选列设置默认值为空字符串。

3.7!=或者<>

mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。

EXPLAIN SELECT * FROM employees WHERE name = 1000;

3.8or和in关键字

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

3.9.范围查询优化

给employees表添加索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;

查询语句

explain select * from employees where age >=1 and age <=2000;

没有使用索引，原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引优化方法：可以讲大的范围拆分成多个小范围。

explain select * from employees where age >=1001 and age <=2000;

3.10总结

四、索引优化工具Trace

4.1思考与分析

思考：根据3的employees表，索引为idex_name_age_position 思考下边的SQL是否走索引?

SQL1：explain select * from employees where name > 'a';

SQL2：explain select name,age,position from employees where name > 'a';

SQL3：explain select * from employees where name > 'zzz';

结果：SQL1不走索引，SQL2走索引，SQL3走索引。

4.2Trace工具使用

对于4.1的问题分析，SQL1为啥不走索引？而SQL2和SQL3都走索引？

我们开启trace工具对MySQL执行优化器进行查看，注意 平时生产上不能开启，影响性能。

set session optimizer_trace = 'enabled=on',end_markers_in_json=on; -- 开启trace

运行SQL1和trace

select * from employees where name > 'a';select * from information_schema.OPTIMIZER_TRACE;

查看结果二TRACE

{ "steps": [ { "join_preparation": { ‐‐第一阶段：SQL准备阶段 … }, { "join_optimization": { "steps": [ { "condition_processing": { ‐‐第二阶段：SQL优化阶段 … }, { "table_dependencies": [ -- 表依赖详情 … ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ -- 预估表的访问成本 { "table": "`employees`", "range_analysis": { "table_scan": { "rows": 3, ‐‐扫描行数 "cost": 3.7 --查询成本（没有单位数值越大成本越高） } /* table_scan */, "potential_range_indices": [‐‐查询可能使用的索引 { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "idx_name_age_position", --辅助索引 "usable": true, "key_parts": [ "name", "age", "position", "id" ] /* key_parts */ } ] /* potential_range_indices */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "analyzing_range_alternatives": { --分析各个索引使用成本 "range_scan_alternatives": [ { "index": "idx_name_age_position", "ranges": [ "a < name" ‐‐索引使用范围 ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, ‐‐使用该索引获取的记录是否按照主键排序 "using_mrr": false, "index_only": false, ‐‐是否使用覆盖索引 "rows": 3, ‐‐索引扫描行数 "cost": 4.61, ‐‐索引使用成本 "chosen": false, ‐‐是否选择该索引 "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`employees`", "best_access_path": { ‐‐最优访问路径 "considered_access_paths": [‐‐最终选择的访问路径 { "access_type": "scan",--访问类型：为scan，全表扫描 "rows": 3, "cost": 1.6, "chosen": true ‐‐确定选择 } ] /* considered_access_paths */ } /* best_access_path */, "cost_for_plan": 1.6, "rows_for_plan": 3, "chosen": true } ] }, }

查看的时候，如果做索引优化只需要查看rows_estimation 、analyzing_range_alternatives 、considered_execution_plans 这些个属性即可。

结论：

1、通过trace工具可以分析，SQL1在使用索引的情况下首先需要查询索引树，还需要回表扫描主键索引树。二个扫描的成本>全表扫描的成本，因此MySQL没有选择使用索引。

2、SQL2只扫描辅助索引树就可以得到结果，因此会走索引。

3、SQL3扫描了一行查看到没有关联的数据因此直接返回的结果为空索引会走索引树。

五、排序优化

5.1排序

MySQL查询支持index和filesort两种方式的排序，index就是根据主键进行排序，物理排序效率比较高；filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。

5.2排序案例

基于employees表进行查看

case1：

分析：利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

case2：

分析：从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了

age，出现了Using filesort。

case3：

分析：查找只用到索引name，age和position用于排序，无Using filesort。

case4：

分析：和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

case5：

分析：与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

case6：

分析：虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

case7：

分析：对于排序来说，多个相等条件也是范围查询

case8：

5.3 filesort文件排序

介绍

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,packed_additional_fields >

双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次去主键索引取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

参数控制：MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 单路排序模式；如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 双路排序模式。

案例

示例验证下各种排序方式：

查看下这条sql对应trace结果如下(只展示排序部分)：

‐‐开启traceset session optimizer_trace="enabled=on",end_markers_in_json=on; select * from employees where name = 'zhuge' order by position;select * from information_schema.OPTIMIZER_TRACE;trace排序部分结果："join_execution": { ‐‐Sql执行阶段 "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`employees`", "field": "position" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { ‐‐文件排序信息 "rows": 10000, ‐‐预计扫描行数 "examined_rows": 10000, ‐‐参数排序的行 "number_of_tmp_files": 3, ‐‐使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序 "sort_buffer_size": 262056, ‐‐排序缓存的大小 "sort_mode": "<sort_key, packed_additional_fields>" ‐‐排序方式，这里用的单路排序 } /* filesort_summary */ } ] /* steps */ }

详细执行流程：

1. 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id

2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中

3. 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id

4. 重复步骤 2、3 直到不满足 name = ‘zhuge’

5. 对 sort_buffer 中的数据按照字段 position 进行排序

6. 返回结果给客户端

双路排序

set max_length_for_sort_data = 10; ‐‐employees表所有字段长度总和肯定大于10字节 select * from employees where name = 'zhuge' order by position; select * from information_schema.OPTIMIZER_TRACE;trace排序部分结果："join_execution": { "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`employees`", "field": "position" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "rows": 10000, "examined_rows": 10000, "number_of_tmp_files": 2, "sort_buffer_size": 262136, "sort_mode": "<sort_key, rowid>" ‐‐排序方式，这里用的双路排序 } /* filesort_summary */ } ] /* steps */ } /* join_execution */ set session optimizer_trace="enabled=off"; ‐‐关闭trace

详细执行流程：

1. 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id

2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中

3. 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id

4. 重复 2、3 直到不满足 name = ‘zhuge’

5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序

6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

总结

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

如果 MySQL 排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配

置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。

如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器

优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。

所以，MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

注意：如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

5.4总结

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

2、order by满足两种情况会使用Using index。

1) order by语句使用索引最左前列。

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

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

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group

by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

六、SQL优化实战

6.1分页查询优化

很多时候我们业务系统实现分页功能可能会用如下sql实现select * from employees limit 10000,10;

性能分析：表示从表employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

1、优化技巧1

根据自增且连续的主键排序的分页查询。

优化前select * from employees limit 90000,5;优化后select * from employees where id > 90000 limit 5;

弊端：这种优化方式只适用于表中id是连续自增的一旦出现有数据删除那么就会导致结果不一致

2、优化技巧2

根据非主键字段排序的分页查询

优化前select * from employees ORDER BY name limit 90000,5;优化后select * from employees e inner join (select id from employees order by name limit 90000,5) edon e.id = ed.id;

优点：内查询是走主键索引的，inner join关联主键的效率非常高。而原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

6.2Join关联查询优化

1、准备

create table t1 (id int not null auto_increment,a int default null,b int default null,PRIMARY KEY (`id`),INDEX `idx_a` (a) USING BTREE ) engine = innodb charset=utf8;create table t2 like t1;往t1表插入1万行记录，往t2表插入100行记录

2、mysql的表关联常见有两种算法

Nested-Loop Join 算法Block Nested-Loop Join 算法

嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

explain select * from t1 inner join t2 on t1.a = t2.a

从执行计划中可以看到这些信息：

驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

sql的大致流程

1. 从表 t2 中读取一行数据；

2. 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；

3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；

4. 重复上面 3 步。

总结：整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，mysql会选择Block Nested-Loop Join算法。

基于块的嵌套循环连接 Block Nested-Loop Join( BNL )算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

explain select * from t1 inner join t2 on t1.b = t2.b

Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

sql的大致流程如下：

1. 把 t2 的所有数据放入到 join_buffer 【内存】中

2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比

3. 返回满足 join 条件的数据整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) =10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是100 * 10000= 100 万次。

思考：被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。

很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。

因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高

3、对于关联SQL的优化

关联字段加索引，让mysql做join操作时尽量选择NLJ算法。小标驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间。

straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql选着 t2 表作为驱动表。

straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

6.3in和exists优化

原则：小表驱动大表，即小的数据集驱动大的数据集

in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)#等价于： for(select id from B){ //先执行 select * from A where A.id = B.id //在执行 }

exists ：当A表的数据集小于B表的数据集时，exists优于in

将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id) #等价于: for(select * from A){//先执行 select * from B where B.id = A.id } #A表与B表的ID字段应建立索引

1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别

2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比

3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

6.4count(*)查询优化

# 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间

set global query_cache_size=0;

set global query_cache_type=0;

思考：下边查询语句哪个更高？

select count(1) from employees;

select count(id) from employees;

select count(name) from employees;

select count(*) from employees;

结论：在MySQL5.7之前count(1) > count(name) >= count(*) > count(id)，但是在MySQL5.7以后版本四个sql的执行计划一样，说明这四个sql执行效率应该差不多，区别在于根据某个字段count不会统计字段为null值的数据行。最终使用的都是辅助索引。

为什么mysql最终选择辅助索引而不是主键聚集索引？

因为二级索引相对主键索引存储数据更少，检索性能应该更高。

常见优化方法

1、查询mysql自己维护的总行数

对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算。【因为MyISAM引擎不支持事务，因此不会出现数据不一致情况，索引MySIAM会在内存中维护一份总数量值】

2、show table status语句

如果只需要知道表总行数可以使用 show table status like '表名'

3、将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性。

4、增加计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作。

6.5查看SQL执行时长工具

查看是否打开

show variables like 'profiling'

打开

set profiling=1

使用

show profiles;

七、MySQL参数优化

优化选择：

优化成本：硬件升级>系统配置>表结构设计>SQL语句及索引。优化效果：硬件升级<系统配置<表结构设计<SQL语句及索引。

7.1系统参数配置

MySQL会在内存中保存一定的数据，通过LRU算法将不常访问的数据保存在硬盘文件中。尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。扩大innodb_buffer_pool_size，能够全然从内存中读取数据。最大限度降低磁盘操作。

确定innodb_buffer_pool_size 足够大的方法：

show global status like 'innodb_buffer_pool_pages_%';+----------------------------------+-------+| Variable_name | Value |+----------------------------------+-------+| Innodb_buffer_pool_pages_data | 267 || Innodb_buffer_pool_pages_dirty | 0 || Innodb_buffer_pool_pages_flushed | 36 || Innodb_buffer_pool_pages_free | 7925 | free代表能够使用的空间| Innodb_buffer_pool_pages_misc | 0 || Innodb_buffer_pool_pages_total | 8192 |+----------------------------------+-------+在/etc/my.cnf文件中修改innodb_buffer_pool_size = 750M 【innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。（只安装了mysql可以）】重启mysql服务器

如果是专用的MySQL Server可以禁用SWAP【不建议，禁用就不会从磁盘上读取文件了】

#查看swapcat /proc/swaps Filename Type Size Used Priority/dev/sda2 partition 1048572 0 -1#关闭所有交换设备和文件.swapoff -a

7.2数据预热

预热脚本

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb, ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache FROM ( SELECT engine,table_schema db,table_name tb, index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index)ndxcollist FROM ( SELECT B.engine,A.table_schema,A.table_name, A.index_name,A.column_name,A.seq_in_index FROM information_schema.statistics A INNER JOIN ( SELECT engine,table_schema,table_name FROM information_schema.tables WHERE engine='InnoDB' ) B USING (table_schema,table_name) WHERE B.table_schema NOT IN ('information_schema','mysql') ORDER BY table_schema,table_name,index_name,seq_in_index ) A GROUP BY table_schema,table_name,index_name ) AAORDER BY db,tb;

将该脚本保存为：loadtomem.sql

执行命令：mysql -uroot -proot -AN < /root/loadtomem.sql > /root/loadtomem.sql

在需要数据预热时，比如重启数据库：mysql -uroot < /root/loadtomem.sql > /dev/null 2>&1

7.3降低磁盘写入次数

增大redolog，减少落盘次数 ：innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size通用查询日志、慢查询日志可以不开 ，bin-log开：生产中不开通用查询日志，遇到性能问题开慢查询日志写redolog策略 innodb_flush_log_at_trx_commit设置为0或2：如果不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者事务都非常小，都能够用 0或者 2 来减少磁盘操作。