Mysql索引数据结构详解及性能调优

以前学习了不少东西，都忘了不少，最近就想着总结一下，就想到想写博客文章来总结下自己这些年学习的东西，记录下各方面技术学习情况。

如果觉得好看，请给个赞

• 你有一个思想，我有一个思想，我们交换后，一个人就有两个思想
• If you can NOT explain it simply, you do NOT understand it well enough

简单介绍下这篇文章的流程

1. 为什么使用索引

A：那还用说，肯定在某些方面有特长呗，比如你知道的【它很快，非常快】


我也很赞同这个答案，但说的不够具体，你得说明它为啥这么快

如果问你选择索引的原因就是一个【快】字，面试也就不会出那么多幺蛾子了。你有没有问过你自己

1. 索引在所有场景下都是快的吗？
2. 知道它很快，何为快？怎样度量？

索引 （翻译官方文档）是帮助 MySQL 高效获取数据的 排好序 的数据结构
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

2. 索引数据结构详解

在创建索引时，通常采用的数据结构有：Hash、二叉树、红黑树、B 树以及 B + 树 可以在线查看数据结构的网页 Data Structure

二叉树:

定义规则为左边节点值比根节点小，右边节点值比根节点大，并且左右子节点都是排序树

要是索引采取这种结构，数值递增那种，就要满足右边节点值比根节点大，导致检索数据会导致查了 6 遍磁盘

红黑树：

（在 jdk8 之后，用数组 + 链表 + 红黑树来实现 hashmap，当碰撞的元素个数大于 8 时 & 总容量大于 64，会有红黑树的引入。）红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化成链表的情况，在数据插入的时候同时调整整个树，使其节点尽量均匀分布，保证平衡性，目的在于降低树的高度，提高查询效率。（右边的树的高度不会大于左边树的高度超过 1，大于等于 1 级后会自动平衡，自己可在数据结构上插入试试）

特点：

1. 节点是红色或者黑色
2. 根节点是黑色
3. 每个叶子的节点都是黑色的空节点（NULL）
4. 每个红色节点的两个子节点都是黑色的
5. 从任意节点到其每个叶子的所有路径都包含相同的黑色节点

• 优点：解决二叉搜索树的极端情况的退化问题。
• 缺点：检索时间依旧与树的高度有关，当数据量很大时，树的高度就会很高，检索的次数就会比较多，检索的时间会比较久，效率低。

从前面分析情况来看，减少磁盘 IO 的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以 B -Tree 就在这样伟大的时代背景下诞生了

B-Tree

基于以上进行扩容，每个横向的节点变多了意味的存放的数据变多了，整个树的高度也变小了，减少磁盘 io 的搜索速度

特点
1. 叶节点具有相同的深度，叶节点的指针为空
2. 所有索引元素不重复
3. 节点中的数据索引从左到右递增排列

缺点：可以看到存放的数据类似 key+value 的数据 要是 InnoDB 的话 data 可能存放的是除了索引外的字段
页节点 mysql 默认推荐的是 16k 大小 (show global status like ‘Innodb_page_size’;)，假如大节点的每个节点的 data 存的数据比较大，那么意味着横向能存储的索引就会变很少，大节点的能存储的索引变少意味着整颗树的高度受到限制

B+Tree

(B-Tree 变种 MySql 默认使用索引结构)
1. 非叶子节点不存储 data，只存储索引(冗余)，可以放更多的索引
2. 叶子节点包含所有索引字段
3. 叶子节点用指针连接，提高区间访问的性能（快速定位范围查询，例如查询大于 20，第一次 io 从根节点查询三次定位到 20，然后通过后面的指针查询大于 20 的数据，就不用再从根节点的重新再查询，提高性能，叶子节点开始结束节点也是用指针连接串起来的）

Hash

前面说的 mysql 默认索引结构是 B +Tree，还有一种索引结构是 Hash
如果是 hash 的话是通过 hash（值）运算然后在磁盘中快速查找对应的磁盘文件指针从而找到行数据
hash 索引查数据是很方便也快的，但是不支持范围性查找 例如 >= < between and 也不支持排序
Hash 索引适合等值查询，不适合范围查询

总结

为什么 mysql 索引结构默认使用 B +Tree

为什么 mysql 索引结构默认使用 B +Tree，而不是 Hash，二叉树，红黑树？
B-tree：因为 B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低；
Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高。
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高。
红黑树：树的高度随着数据量增加而增加，IO 代价高。

索引是如何支持千万级表的快速查询

索引可以把它想象跟旁边的指针的成对存在的（指针是指向下一个节点的磁盘位置（占用 6 字节））

索引假设字段为数字类型 Bigint 8b+ 指针默认占用空间 6b = 14b（索引跟旁边的指针的成对存在的大小总和）
大节点能存放 16kb 数据 那么最多能存放 16kb * 1024/ 14= 1170 个索引


假设叶子节点每个元素存放 1kb 大小，总共 16kb，那么叶子节点能存放 16 个元素，那么 高度 h = 3 的 B +Tree 叶子节点能存放的元素为
1170*1170*16 = 2100w 多（2000 多 w 的数据只需访问磁盘 io 三次）

3. 聚集索引与非聚集索引

聚集（clustered）索引，也叫聚簇索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

注：第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们 SQL 里面用的表里的列，其中 id 是主键，建立了聚集索引。

数据行的物理顺序与列值的顺序相同，如果我们查询 id 比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

InnoDB 索引实现(聚集索引）

（可以看到 InnoDB 的索引跟表数据全部放在一起 .frm 存的是表结构的定义文件 .ibd 存取索引跟表数据）

InnoDB 表数据文件本身就是按 B +Tree 组织的一个索引结构文件
聚集索引 - 叶节点包含了完整的数据记录

为什么 InnoDB 表必须有主键，并且推荐使用整型的自增主键？
（如果没有主键，mysql 会优先查找有唯一数据的字段当主键，没有的话会在后台新增 row_id 来标识主键）
结合 B +Tree 的特点，B+Tree 组织是一个索引结构文件，所以需要一个主键元素来充当组织数据的角色，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。如果是 uid 的话，会进行字符串比较大小，相比之下肯定是数字比较大小会比较快。

非聚集（unclustered）索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，它结构顺序与实际存放顺序不一定一致。

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

MyISAM 索引文件和数据文件是分离的(非聚集)

frm 存的是表结构的定义文件，MYD 存取的所有数据行，MYI 存取的表的索引字段

例如查找 col1 = 18 的数据 先从 MYI 中找到索引所在磁盘文件指针然后定位到 MYD 找到 col1 = 18 那行的数据

为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

主键索引

非主键索引

查找 Alice 找到主键 18 再去主键索引查找整行的数据

（非主键索引 与 单值索引的 null 不会存储在非叶节点，会放在叶子节点最左边上）

例如在叶子节点最左边上开辟一个空间存放

联合索引 多个字段联合起来（索引最左前缀原理—> 索引优化重点）

联合索引也是排好序的数据结构 例如（a,b,c）依次从左到右排序 也就是先排好 a，再 b，再到 c

对于有些索引字段为空的情况也是排在最左边

例如 a 相等，b 为空，c 相等

4.mysql 执行计划 Explain 使用

Explain 工具介绍

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返 回执行计划的信息，而不是执行这条 SQL
注意: 如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

示例表

DROP TABLE IF EXISTS `singer`; 
CREATE TABLE `singer`(`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `singer` (`id`,`name`,`update_time`) VALUES (1,'周杰伦', NULL),(2,'林俊杰', NULL),(3,'五月天', NULL);
explain select * from singer;

DROP TABLE IF EXISTS `song`;
CREATE TABLE`song`(`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO`song`(`id`,`name`)VALUES(3,'晴天'),(1,'曹操'),(2,'突然好想你');

DROP TABLE IF EXISTS`singer_song`;
CREATE TABLE `singer_song`(`id` int(11) NOT NULL,
`singer_id` int(11) NOT NULL,
`song_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_singer_song_id` (`singer_id`,`song_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `singer_song`(`id`,`singer_id`,`song_id`)VALUES(1,1,3), (2,2,1),(3,3,2);

explain 变种

explain extended: 会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通 过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表 进行连接的行数(前一个表指 explain 中的 id 值比当前表 id 值小的表)。explain extended select * from song where id = 2; 
show warnings;
注：两个要结合一起使用

Explain 列信息

各列含义如下：

• id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
• select_type: SELECT 查询的类型.
• table: 查询的是哪个表
• partitions: 匹配的分区
• type: join 类型
• possible_keys: 此次查询中可能选用的索引
• key: 此次查询中确切使用到的索引.
• key_len: 这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
• ref: 哪个字段或常数与 key 一起被使用
• rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
• filtered: 表示此查询条件所过滤的数据的百分比
• extra: 额外的信息

explain 中每个列的信息

1.id id 列的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的顺序是按 select 出现的 顺序增长的。id 列越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行。

2.select_type 表示了查询的类型, 它的常用取值有:

• （1)SIMPLE, 简单查询表示此查询不包含 UNION 查询或子查询
• mysql> explain select * from song where id = 2;
• （2)PRIMARY, 表示此查询是最外层的查询
• （3)UNION, 表示此查询是 UNION 的第二或随后的查询
• （4)UNION RESULT, UNION 的结果
• （5)SUBQUERY, 子查询中的第一个 SELECT
• （6)DERIVED: 包含在 from 子句中的子查询。MySQL 会将结果存放在一个临时表中, 也称为 派生表
• 
用这个例子来了解 primary、subquery 和 derived 类型
 mysql> set session optimizer_switch=’derived_merge=off’; #关闭 mysql5.7 新特性对衍生表的合并优化 
explain select (select 1 from singer where id = 1) from (select * from song where id = 1) der;

  mysql> set session optimizer_switch='derived_merge=on'; #还原默认配置

3.table
这一列表示 explain 的一行正在访问哪个表。当 from 子句中有子查询时，table 列是 <derivenN> 格式，表示当前查询依赖 id=N 的查 询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为 <union1,2>，1 和 2 表示参与 union 的 select 行 id。

4.partitions
官方定义为 The matching partitions（匹配的分区），该字段应该是看 table 所在的分区吧（应该是在分库分表用到的不晓得理解错误没）。值为 NULL 表示表未被分区。

5. type

这一列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL 一般来说，得保证查询达到 range 级别，最好达到 ref
NULL:mysql 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如: 在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

explain select min(id) from song;

const, system :mysql 能对查询的某部分进行优化并将其转化成一个常量(可以看 show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取 1 次，速度比较快。system 是 const 的特例，表里只有一条元组匹配时为 system

set session optimizer_switch='derived_merge=off'; 
explain extended select * from (select * from song where id = 1) tmp;
show warnings; 

eq_ref:primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

explain select * from singer_song left join song on singer_song.song_id = song.id;

ref: 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要 和某个值相比较，可能会找到多个符合条件的行。

1. 简单 select 查询，name 是普通索引(非唯一索引)

explain select * from song where name = '晴天';

2. 关联表查询，idx_singer_song_id 是 singer_id 和 song_id 的联合索引，这里使用到了 singer_song 的左边前缀 singer_id 部分。

 explain select singer_id from singer left join singer_song on singer.id = singer_song.singer_id;

range: 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

explain select * from singer where id > 1;

index: 扫描全表索引，这通常比 ALL 快一些。

explain select * from song;

ALL: 即全表扫描，意味着 mysql 需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

explain select * from singer;

6.possible_keys 
这一列显示查询可能使用哪些索引来查找。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中 数据不多，mysql 认为索引对此查询帮助不大，选择了全表查询。如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可 以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

7. key 这一列显示 mysql 实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制 mysql 使用或忽视 possible_keys 列中的索 引，在查询中使用 force index、ignore index。

8. key_len 这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些 列。举例来说，singer_song 的联合索引 idx_singer_song_id 由 singer_id 和 song_id 两个 int 列组成，并且每个 int 是 4 字节。通过结果中的 key_len= 4 可推断出查询使用了第一个列:singer_id 列来执行索引查找。

explain select * from singer_song where singer_id = 2;

key_len 计算规则如下:
字符串 char(n):n 字节长度 varchar(n):2 字节存储字符串长度，如果是 utf-8，则长度 3n +2
数值类型 tinyint:1 字节 smallint:2 字节 int:4 字节 bigint:8 字节
时间类型 date:3 字节 timestamp:4 字节 datetime:8 字节
如果字段允许为 NULL，需要 1 字节记录是否为 NULL
索引最大长度是 768 字节，当字符串过长时，mysql 会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

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

10. rows 这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数。

11. Extra 
这一列展示的是额外信息。常见的重要值如下:
(1)Using index: 使用覆盖索引(查询的字段是设置索引的字段)

explain select singer_id from singer_song where singer_id = 1;

(2)Using where: 使用 where 语句来处理结果，查询的列未被索引覆盖

explain select * from singer where name = '周杰伦';

(3)Using index condition: 查询的列不完全被索引覆盖，where 条件中是一个前导列的范围;

explain select * from singer_song where singer_id > 1; 优化策略: 尽量让查询的列被索引覆盖

问题来了: 为什么使用覆盖索引

根据上面的 sql 来看 先去 id_singer_song_id 索引树搜索 找出 singer_id > 1 的数据 因为 id_singer_song_id 索引树并没有存储 singer_song 的全部数据，因此需要根据在 id_singer_song_id 索引树上查询到的主键 id 的值再到 id 索引树搜索一次。

直接在 id_singer_song_id 索引树中就能查询到 id 的值，不用再去 id 索引树中查找其他的数据，避免了回表。

（4)Using temporary mysql 需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

1. singer.name 没有索引，此时创建了张临时表来 distinct
explain select distinct name from singer;

2.song.name 建立了 idx_name 索引，此时查询时 extra 是 using index, 没有用临时表 

(5)Using filesort: 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

（1）singer.name 未创建索引，会浏览 singer 整个表，保存排序关键字 name 和对应的 id，然后排序 name 并检索行记录

explain select * from singer order by name;

（2）song.name 建立了 idx_name 索引, 此时查询时 extra 是 using index

(6)Select tables optimized away: 使用某些聚合函数 (比如 max、min) 来访问存在索引的某个字段

explain select min(id) from song;

12. filtered
使用 explain extended 时会出现这个列，5.7 之后的版本默认就有这个字段，不需要使用 explain extended 了。这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

5.mysql 索引优化

建表

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('sisi',22,'admin',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('hsisi', 24,'test',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('huangsisi',23,'dev',NOW());

1. 全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'sisi'; 
可以看到在索引里使用的字节数 = 3*24 + 2 = 74 通过这个值可以算出具体使用了索引中的 name。

EXPLAIN SELECT * FROM employees WHERE name= 'sisi' AND age = 22;
可以看到在索引里使用的字节数 = 3*24 + 2 +4 = 78 通过这个值可以算出具体使用了索引中的 name,age。

EXPLAIN SELECT * FROM employees WHERE name= 'sisi' AND age = 22 AND position ='admin';

2. 最左前缀法则

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='admin'; 
EXPLAIN SELECT * FROM employees WHERE position = 'admin';
EXPLAIN SELECT * FROM employees WHERE name = 'sisi';

以上只有最后一个走索引，其它两个没有

3. 不在索引列上做任何操作 (计算、函数、(自动 or 手动) 类型转换)，会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE name = 'sisi';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'sisi';

4. 存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'sisi' AND age = 22 AND position ='dev';
EXPLAIN SELECT * FROM employees WHERE name= 'sisi' AND age > 22 AND position ='dev';

可以看到下面的查询语句只用到前面两个索引列

5. 尽量使用覆盖索引(只访问索引的查询(索引列包含查询列))，减少 select * 语句

6.mysql 在使用不等于 (!= 或者 <>) 的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM employees WHERE name != 'sisi';

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

EXPLAIN SELECT * FROM employees WHERE name is null

8.like 以通配符开头(‘$abc…’)mysql 索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%si'

EXPLAIN SELECT * FROM employees WHERE name like 'si%'

解决 like’% 字符串 %’ 索引不被使用的方法
（1）使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%si%';

（2）借助搜索引擎

9. 字符串不加单引号索引失效

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

原因 mysql 优化器自动将数字强制转换为字符串类型 (自动 or 手动)类型转换)，导致索引失效而转向全表扫描

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

11. 范围查询优化

给年龄添加单值索引 
ALTER TABLE`employees`ADD INDEX`idx_age`(`age`) USING BTREE;
explain select * from employees where age >=1 and age <=30;

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

explain select * from employees where age >=23 and age <=30;

还原最初索引状态 
 ALTER TABLE `employees` DROP INDEX `idx_age`;

索引使用总结:

like KK% 相当于 = 常量，%KK 和 %KK% 相当于范围

6. 仿照千万级别数据进行优化实战

制造千万级别数据

参考:
MySQL 快速插入【一亿】测试数据（临时表方式）

按照上面做法其实是不对的，在创表了时候给了索引，边插入边维护索引，开销太大了。

正确做法应该是先删除索引，再插入，再新增索引

建表
建立测试表 t_user
CREATE TABLE `t_user` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
 `name` varchar(24) NOT NULL DEFAULT ''COMMENT' 姓名 ',
 `age` int(11) NOT NULL COMMENT '年龄',
 `create_time` datetime NOT NULL COMMENT '创建时间',
 `position` varchar(20) NOT NULL DEFAULT ''COMMENT' 职位 ',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表' ;

创建临时表
CREATE TABLE `tmp_table` (`id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

生成数据
用 python 生成【一千万】记录的数据文件（这个确实稍微花点时间）python -c "for i in range(1, 1+10000000): print(i)" > base.txt

将生成的文件导入到临时表 tmp_table 中
load data infile 'E:/base.txt' replace into table tmp_table;

导入数据时有可能会报错，原因是 mysql 默认没有开 secure_file_priv（这个参数用来限制数据导入和导出操作的效果，例如执行 LOAD DATA、SELECT … INTO OUTFILE 语句和 LOAD_FILE()函数。这些操作需要用户具有 FILE 权限。）解决办法：在 mysql 的配置文件中（my.ini 或者 my.conf）中添加 secure_file_priv = 文件所在的路径 ,  然后重启 mysql 解决。添加自己文件放置的路径即可。可以用 show variables like '%secure%'; 先看一下配置：mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set, 1 warning (0.00 sec)

说明：secure_file_prive=null   限制 mysqld 不允许导入导出
secure_file_priv=/var/lib/mysql-files/   限制 mysqld 的导入导出只能发生在 /var/lib/mysql-files/ 目录下
secure_file_priv=' '     不对 mysqld 的导入导出做限制

重启 MySQL

以临时表为基础数据，插入数据到 t_user 中

INSERT INTO t_user SELECT
id,
CONCAT('user_name_', id),
FLOOR(RAND() * 50 ),
NOW(),
''
FROM
    tmp_table

新增索引
ALTER TABLE`t_user`ADD INDEX `idx_name_age_position` (`name`,`age`,`position`) USING BTREE;

更新创建时间字段和职位让插入的数据更加随机：以下 sql 更新挺久的，也可以不用更新，只是想让数据看起来比较随机而已，大家看情况
UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year);
UPDATE t_user SET position = 'manager' WHERE id<=1000000;
UPDATE t_user SET position = 'dev' WHERE id>1000000;

到此，千万数据插入结束。

Mysql 如何选择合适的索引


 EXPLAIN select * from t_user where name > 'a';

如果用 name 索引需要遍历 name 字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历 name 字段的联合索引树就能拿到所有结果

 EXPLAIN select name, age, position from t_user where name > 'a'; 

EXPLAIN select * from t_user where name > 'zzz' ;

对于上面这两种 name>’a’ 和 name>’zzz’ 的执行结果，mysql 最终是否选择走索引或者一张表涉及多个索引，mysql 最终如何选择索引，我们可以用 trace 工具来一查究竟，开启 trace 工具会影响 mysql 性能，所以只能临时分析 sql 使用，用完之后立即关闭

 set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启 trace
 select * from t_user where name > 'a' ;
 SELECT * FROM information_schema.OPTIMIZER_TRACE;

把查询的结果 trace 字段结果拿出来分析重点搜索下 rows_estimation   —预估表的访问成本 

• table_scan —全表扫描情况
• “rows”: 9750918, ‐‐扫描行数
• “cost”: 1.98e6 ‐‐查询成本

再搜索下  analyzing_range_alternatives ‐‐分析各个索引使用成本

"ranges": ["a < name" ‐‐索引使用范围] /* ranges */, 
  "index_dives_for_eq_ranges": true, 
  "rowid_ordered": false, ‐‐使用该索引获取的记录是否按照主键排序 
  "using_mrr": true, 
  "index_only": false, ‐‐是否使用覆盖索引 
  "rows": 4875459, ‐‐索引扫描行数 
  "cost": 5.27e6, ‐‐索引使用成本 
 "chosen": false, ‐‐是否选择该索引 
  "cause": "cost" 
  } 

结论: 全表扫描的成本低于索引扫描，所以 mysql 最终选择全表扫描

同理 查看 trace 字段可知索引扫描的成本低于全表扫描，所以 mysql 最终选择索引扫描 
select * from t_user where name > 'zzz'
SELECT * FROM information_schema.OPTIMIZER_TRACE;

set session optimizer_trace="enabled=off"; ‐‐关闭 trace 

常见 sql 深入优化

1.Order by 与 Group by 优化

CASE1: EXPLAIN SELECT * FROM t_user WHERE name = 'user_name_2999009' AND position = 'dev' ORDER BY age;

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

CASE2:  EXPLAIN SELECT * FROM t_user WHERE name = 'user_name_2999009'  ORDER BY position;

从 explain 的执行结果来看:key_len=74，查询使用了 name 索引，由于用了 position 进行排序，跳过了 age，出现了 Using filesort。

CASE3:  EXPLAIN SELECT * FROM t_user WHERE name = 'user_name_2999009'  ORDER BY age,position;

CASE4:  EXPLAIN SELECT * FROM t_user WHERE name = 'user_name_2999009'  ORDER BY position,age;

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

CASE5:  EXPLAIN SELECT * FROM t_user WHERE name = 'user_name_2999009' AND age =20   ORDER BY position,age;

从 explain 的执行结果来看:key_len=78，查询使用了 name,age 索引 ORDER BY 即使位置颠倒了执行器也会进行优化

CASE6: EXPLAIN SELECT * FROM t_user WHERE name >'u'  ORDER BY name;

可以用覆盖索引优化

 EXPLAIN SELECT name, age, position FROM t_user WHERE     name >'u'  ORDER BY name;

Using filesort 文件排序原理详解

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 比查询字段的总长度小，那么使用双路排序模式。

单路排序模式
set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启 trace
SELECT * FROM t_user WHERE name = 'user_name_2999009'  ORDER BY position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

    {
      "join_execution": { ‐‐Sql 执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`t_user`",
                "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": 1,  ‐‐预计扫描行数
              "examined_rows": 1, ‐‐参数排序的行
              "number_of_tmp_files": 0, ‐‐使用临时文件的个数，这个值如果为 0 代表全部使用的 sort_buffer 内存排序，否则使用
磁盘文件排序
              "sort_buffer_size": 262016, ‐‐排序缓存的大小
              "sort_mode": "<sort_key, packed_additional_fields>" ‐‐排序方式，这里用的单路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */

双路排序模式
set max_length_for_sort_data = 10; ‐‐t_user 表所有字段长度总和肯定大于 10 字节 
SELECT * FROM t_user WHERE name = 'user_name_2999009'  ORDER BY position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

恢复默认值
set max_length_for_sort_data = 1024; 
set session optimizer_trace="enabled=off"; ‐‐关闭 trace 

先看单路排序的详细过程:

1. 从索引 name 找到第一个满足 name =‘user_name_2999009’条件的主键 id
2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
3. 从索引 name 找到下一个满足 name =‘zhuge’条件的主键 id
4. 重复步骤 2、3 直到不满足 name =‘user_name_2999009’
5. 对 sort_buffer 中的数据按照字段 position 进行排序
6. 返回结果给客户端

我们再看下双路排序的详细过程:

1. 从索引 name 找到第一个满足 name =‘user_name_2999009’的主键 id
2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
2. 从索引 name 取下一个满足 name =‘user_name_2999009’记录的主键 id
4. 重复 3、4 直到不满足 name =‘user_name_2999009’
3. 对 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 很多参数设置都是做过优化的，不要轻易调整。

Order by 与 Group by 优化总结

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 实现
SELECT * FROM t_user LIMIT 1000000,10;
表示从表 t_user 中取出从 1000001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 1000010 条记录，然后抛弃前 1000000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

根据非主键字段排序的分页查询 
EXPLAIN select * from t_user order by name limit 900000,5;

发现并没有使用 name 字段的索引 (key 字段对应的值为 null)，具体原因: 扫描整个索引并查找到没索引的行(可能要遍历多个索引树) 的成本比扫描全表的成本更高，所以优化器放弃使用索引。知道不走索引的原因，那么怎么优化呢? 其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下 :

select * from t_user e inner join (select id from t_user order by name limit 900000,5) ed on e.id = ed.id;

需要的结果与原 SQL 一致，执行时间减少了不少，我们再对比优化前后 sql 的执行计划:

原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

当然还有其它策略例如覆盖索引:
select name, age, position from t_user order by name limit 900000,5

像在正常情况下不可能说查询会用覆盖索引的，像有时候会加上日期等日期去查询，这时就不应该在查询上用时间函数去查询了，这时应该在时间字段上加个索引然后用范围查询

EXPLAIN SELECT id, `name`, age, position, create_time FROM t_user WHERE create_time BETWEEN '2020-06-30 00:00:00' AND '2020-06-30 23:59:59' ORDER BY create_time;

Join 关联查询优化

再创建一个测试表 t_user2
CREATE TABLE `t_user2` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
 `name` varchar(24) NOT NULL DEFAULT ''COMMENT' 姓名 ',
 `age` int(11) NOT NULL COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT ''COMMENT' 职位 ',
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表 2' ;

从 t_user 表拉点数据
INSERT INTO t_user2 SELECT id,name,age,position FROM t_user LIMIT 1000;

mysql 的表关联常见有两种算法
Nested-Loop Join 算法
Block Nested-Loop Join 算法

1、嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表 (称为驱动表) 中读取行，在这行数据中取到关联字段，根据关联字段在另一张表 (被驱动 表) 里取出满足条件的行，然后取出两张表的结果合集。

EXPLAIN select * from t_user t1 inner join t_user2 t2 on t1.name = t2.name;

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

上面 sql 的大致流程如下:

1. 从表 t2 中读取一行数据;
2. 从第 1 步的数据中，取出关联字段 name，到表 t1 中查找;
3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端;
2. 重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描 1000 行)，然后遍历这每行数据中字段 name 的值，根据 t2 表中 name 的值索引扫描 t1 表 中的对应行(扫描 1000 次 t1 表的索引，1 次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了 1000 行)。因此整个过程扫描了 2000 行。如果被驱动表的关联字段没索引，使用 NLJ 算法性能会比较低(下面有详细解释)，mysql 会选择 Block Nested-Loop Join 算法。

2、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

EXPLAIN select * from t_user t1 inner join t_user2 t2 on t1.position = t2.position;

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

上面 sql 的大致流程如下:

1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比 3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为 10000000(表 t1 的数据总量) + 1000(表 t2 的数据总量) = 10001000。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 1000 次判断，所以内存中的判断次数是 1000 * 10000000。(判断完一次释放一次内存)

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢?
如果上面第二条 sql 使用 Nested-Loop Join
（1. 从表 t2 中读取一行数据;
2. 从第 1 步的数据中，取出关联字段 name(这时已经没有索引了，所以会拿所有行的数据 10000000)，到表 t1 中查找;
3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端;

1. 重复上面 3 步。）那么扫描行数为 1000 * 10000000

这个是磁盘扫描，很显然，用 BNL 磁盘扫描次数少很多，相比于磁盘扫描，BNL 的内存计算会快得多。因此 MySQL 对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL 算法性能更高

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

straight_join 解释:straight_join 功能同 join 类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。例如: select * from t_user2 t2 straight_join t_user t1 on t1.name = t2.name 代表制定 mysql 选着 t2 表作为驱动表。straight_join 只适用于 inner join，并不适用于 left join，right join。(因为 left join，right join 已经代表指 定了表的执行顺序) 
尽可能让优化器去判断，因为大部分情况下 mysql 优化器是比人要聪明的。使用 straight_join 一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

in 和 exsits 优化

原则: 小表驱动大表，即小的数据集驱动大的数据集 
in: 当 B 表的数据集小于 A 表的数据集时，in 优于 exists 
select * from t_user where id in(select id from t_user2)

#等价于:
for(select id from t_user2){select * from t_user where t_user.id = t_user2.id} 

exists: 当 A 表的数据集小于 B 表的数据集时，exists 优于 in 将主查询 A 的数据，放到子查询 B 中做条件验证，根据验证结果 (true 或 false) 来决定主查询的数据是否保留 

select * from t_user2 t2 where exists (select 1 from t_user t1 where t1.id = t2.id)

1、EXISTS (subquery)只返回 TRUE 或 FALSE, 因此子查询中的 SELECT * 也可以用 SELECT 1 替换, 官方说法是实际执行时会忽略 SELECT 清单, 因此没有区别
2、EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比 
3、EXISTS 子查询往往也可以用 JOIN 来代替，何种最优需要具体问题具体分析 

count(*)查询优化

EXPLAIN select count(1) from t_user;
EXPLAIN select count(id) from t_user;  
EXPLAIN select count(name) from t_user;
EXPLAIN select count(*) from t_user;

先不执行，先简单分析下哪个会比较快
select count(name) from t_user 比  select count(id) from t_user 快
辅助索引会比较快，因为二级索引相对主键索引存储数据更少（二级索引存储的是主键，而主键索引存储的整行数据），检索性能应该更高，先去查询非主键索引扫描到一条就加 1，但是不会计算 null 情况(相当于扫描 name 的索引树)
 
 select count(1) from t_user 比 select count(name) from t_user 快 
因为 count(1) 底层优化器自动选择走二级索引，但是它没有拿 name 的数据，它只是在结果集中记录 + 1 而已，而 count(name) 会拿 name 字段进行转码之类的操作

 select count(*) from t_user 比 select count(id) from t_user 快 
 count(*) 底层优化器自动选择走二级索引，但几乎跟 count(name)一样 
 count(*)  不会拿所有字段，只会拿第一个索引字段而已（mysql 5.7 后）select count(id) from t_user 最慢

结论: count(1)> count(name) == count(*)>  count(id)

然而四个 sql 的执行计划都是一样，说明这四个 sql 执行效率应该差不多 (mysql5.7 后优化了, 原来要走主键索引的终究走辅助索引。。。) , 区别在于根据某个字段 count 不会统计字段为 null 值的数据行 为什么 mysql 最终选择辅助索引而不是主键聚集索引? 因为二级索引相对主键索引存储数据更少（二级索引存储的是主键，而主键索引存储的整行数据），检索性能应该更高

所以（mysql 5.7 后推荐使用 count(*)）

count(*)查询常见优化方法

1、查询 mysql 自己维护的总行数 对于 myisam 存储引擎的表做不带 where 条件的 count 查询性能是很高的，因为 myisam 存储引擎的表的总行数会被 mysql 存储在磁盘上，查询不需要计算 
EXPLAIN select count(*) from test_myisam;

对于 innodb 存储引擎的表 mysql 不会存储表的总记录行数（事务），查询 count 需要实时计算

2、show table status 如果只需要知道表总行数的估计值可以用如下 sql 查询，性能很高 

show table status LIKE 't_user'

3、增加计数表 
新增一个专门计数的表插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作 

7. 总结

遵照索引创建顺序的最左前缀法则，不在索引列上做任何操作 (计算、函数、(自动 or 手动) 类型转换)，尽量使用覆盖索引，可以用 join 查询替代子查询，因为 mysql 子查询需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕再撤销这些临时表，因此查询数据量比较大，影响比较大。如果表中的字段比较多可以分解成多表，当一个表的数据量很大时，会使用频率低的字段的存在而变慢。当然优化可以从硬件出发，也可以对 mysql 服务的参数进行优化例如修改 my.cof 或 my.ini 文件的配置参数 查询缓存大小等，这部分的内容需要全面的知识才能进行该类的优化

参考
感谢前辈们总结的精华，自己所写的好多都参考了以下资料

• mysql5.7 从入门到精通
• 图灵学院诸葛老师 mysql 索引优化