导图社区 Mysql索引优化
- 82
- 9
- 1
- 举报
Mysql索引优化
本图帮助大家全面分析了MySQL索引，并提出优化方式。MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的关系数据库管理系统应用软件之一。
编辑于2021-04-22 09:29:08- 相似推荐
- 大纲
MySql索引优化分析
性能下降 SQL 慢执行时间长等待时间长
查询语句问题
索引失效
单值
复合索引
关联查询太多 join （设计曲线或不得已的需求）
服务器调优各个参数的额设置（缓冲、线程数等）
常见通用的 Join 查询
SQL 执行顺序
SQL 语法格式
语法过程
SQL 解析过过程
执行过程
总结
SQL 解析过程
JOIN 图
图解
建表SQL
```sql create database `oemp`; use `oemp`; #部门表 create table `tb_dept` ( `id` int(11) not null auto_increment, `name` varchar(30) default null, `storey` varchar(40) default null, primary key(`id`) ) engine = innodb auto_increment=1 default charset=utf8; #员工表 create table `tb_emp` ( `id` int(11) not null auto_increment, `name` varchar(30) default null, `dept_id` int(11) default null, primary key(`id`), key `idx_dept_id`(`dept_id`) #, constraint `fk_dept_id` foregign key(`dept_id`) references `tb_dept` (`id`) ) engine = innodb auto_increment=1 default charset=utf8; #部门数据 insert into `tb_dept`(`id`, `name`, `storey`) values('1', 'RD', '11'); insert into `tb_dept`(`id`, `name`, `storey`) values('2', 'HR', '12'); insert into `tb_dept`(`id`, `name`, `storey`) values('3', 'MK', '13'); insert into `tb_dept`(`id`, `name`, `storey`) values('4', 'MIS', '14'); insert into `tb_dept`(`id`, `name`, `storey`) values('5', 'FD', '15'); #员工数据 insert into `tb_emp`(`name`, `dept_id`) values('z3', 1); insert into `tb_emp`(`name`, `dept_id`) values('z4', 1); insert into `tb_emp`(`name`, `dept_id`) values('z5', 1); insert into `tb_emp`(`name`, `dept_id`) values('w5', 2); insert into `tb_emp`(`name`, `dept_id`) values('w6', 2); insert into `tb_emp`(`name`, `dept_id`) values('s7', 3); insert into `tb_emp`(`name`, `dept_id`) values('s8', 4); insert into `tb_emp`(`name`, `dept_id`) values('s9', 51); ```
7种 JOIN
``` #关联查询 select * from `tb_dept`; select * from `tb_emp`; #内连接查询 select * from `tb_emp` a inner join `tb_dept` b on a.dept_id = b.id; #左连接查询 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id; #右连接查询 select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id; #左差集查询 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id where b.id is null; #右差集查询 select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id where a.id is null; #全连接查询 （全集）(MySQL不支持 full outer join 语法) # oracle: select * from `tb_emp` a full outer join `tb_dept` b on a.dept_id = b.id; # union 拼接且去重 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id union select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id; #A/B独有的数据（差集） select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id where b.id is null union select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id where a.id is null; ```
1. left join
``` #左连接查询 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id; ```
2. right join
``` #右连接查询 select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id; ```
3. 交集 （inner join）
``` #关联查询 select * from `tb_dept`; select * from `tb_emp`; #内连接查询 select * from `tb_emp` a inner join `tb_dept` b on a.dept_id = b.id; ```
4. 左差集
``` #左差集查询 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id where b.id is null; ```
5. 右差集
``` #右差集查询 select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id where a.id is null; ```
6. 全集
``` #全连接查询 （全集）(MySQL不支持 full outer join 语法) # oracle: select * from `tb_emp` a full outer join `tb_dept` b on a.dept_id = b.id; # union 拼接且去重 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id union select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id; ```
7. 差集
``` #A/B独有的数据（差集） select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id where b.id is null union select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id where a.id is null; ```
索引简介
是什么
MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构
* 索引的目的在于提高查询效率，类比字典。 * 如果要查询 “MySQL” 这个单词，我们肯定是需要定位到 M 字母，然后从下往上查找到 y, 字母，再找到剩下的sql. * 如果没有索引，那么你可能需要 a---z, 如果我想找到 Java 开头的单词？ 或者 Oracle 开头的单词呢？ * 所以索引再数据库系统中是一种非常重要的一部分
索引理解：“用于排序和快速查找的数据结构”。
详解（重要）
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）的数据，这样就可以再这些数据结构上实现高级查找算法。这种数据结构，就是索引。
二叉树索引
左边是数据表，一共有两列7条记录， 最左边是数据记录的物理地址。
为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个执行对应数据记录物理地址的指针，这样可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
结论
数据本身之外，数据库还维护这一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。这种数据结构就是索引。
一般来锁索引本身也很大，不可能全部存在内存中，因此索引往往以索引文件的形式存储在磁盘上
我们平常所说的索引，如果没有特别指明， 都是B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希(hash index)等。
优势
1. 类似大学图书馆目录索引，提高数据检索的效率，降低数据的IO成本。
2. 通过索引列对数据库进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势
1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
3. 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句
MySQL 索引分类
单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引
索引列的值必须唯一，但允许有空值
复合索引
即一个索引包含多个列
基本语法
创建
create [unique] index indexName on mytable(columnname(length));
alter mytable add [unique] index [indexName] on (clumnname(length))
删除
drop index [indexname] on mytable;
查看
show index form table_name\G
使用 alter 命令
索引创建的4种方式
1. alter table tb_name add primary key (column_list): 添加一个主键，这意味着索引必须是唯一的且不能为null
2. alter table tb_name add unique index_name(column_list): 这条语句创建索引的值必须是唯一的（除了 NULL 外， NULL 可能会出现多次）
3. alter table tb_name add index index_name (column_list): 添加普通索引，索引值可出现多次
4. alter table tb_name add fulltext index_name(column_list): 该语句指定了索引为 full text， 用于全文索引
建议：一张表索引不要超过5个
MySQL 索引结构
BTree 索引
检索原理
B+树
【初始化介绍】：一颗B+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）如磁盘块1包含数据项 17 和 35 , 包含指针 P1, P2, P3P1 表示小雨 17 的磁盘块， P2 表示在17 和 35 之间的磁盘块， P3 表示大于 35 的磁盘块真实的数据存在与叶子节点 即3，5，9，10，13，15，28，29，36，60，75，79，90，99。非叶子节点只不存储真实的数据，只存储指引搜索的数据项， 如 17，35 并不是真实存在与数据表中
【查找过程】：如果要查找数据项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， 显然成本非常的高
Hash 索引
full-text 全文索引
R-Tree 索引
哪些情况下需要创建索引？
1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3.查询中与其他关联的字段，外键关系建立索引
4.频繁更新的字段不是创建索引
因为每次更新不单单是更新了记录还会更新索引
5.where 条件中用不到的字段不创建索引
6.单键/组合索引的选择， who? （高并发下倾向于创建组合索引）
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
8.查询中统计或分组字段
哪些情况下不需要创建索引？
1. 表记录太少
2. 经常增删改的字段
提高了查询速度，同时会降低更新表的速度，如对表进行 insert , update , delete 因为表更新时， MySQL 不仅仅要保存数据，还要保存索引文件。
3. 数据重复，且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含多个重复的内容，为它建立索引就没有太大的实际效果。
1. 假如一个表由10万记录，由一个字段A只有 T 和 F 两种值，且每个值的分布概率大约为 50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
2. 索引的选择性是指缩影列中不同值的数目与表中记录数相比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个缩影的选择性就是 1980/2000=0.99， 一个索引的选择性越接近于1，这个索引的效率就越高
性能分析
MySQL Query Optimizer
1. MySQL 中有专门负责优化 select 语句的优化器模块， 主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划（他认为最优的数据检索方式， 但不见得是DBA 认为最优的， 这部分最耗费时间）
2. 当客户端向 MySQL 请求一条 Query ，命令解析模块完成请求分类， 区别是select 并转发给 MySQL Query Optimizer 时，MySQL Query Optimizer 首先会对整条Query 进行优化，处理调一些常量表达式的预算，直接换算成常量值。并对Query 中查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等，然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定 Query 执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行几乎，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析， 然后再得出最后的执行计划
Myql 常见性能瓶颈
CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO：磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top, free, iostat 和 vmstat 来查看系统的性能状态
Explain
什么是执行计划
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。
官网介绍
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
执行计划能帮我们完成什么事情？
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
怎么使用执行计划？
Explain + SQL 语句
执行计划的包含信息
包含信息
名词解释
id
select 查询的序列号，包含一组数字， 表示查询中执行 select 子句或操作表的顺序
三种情况
```sql use oemp; #测试表1 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `other_column` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; #测试表2 CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `other_column` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; #测试表3 CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `other_column` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; #id 相同 explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t3.other_column = ''; #id 不同 explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = '')); #id 相同和不同同时存在 explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 where s1.id = t2.id; ```
id 相同，执行顺序由上而下
运行结果
id 不同，如果是子查询，id 的序号会递增， id 值越大优先级越高， 越先被执行
运行结果
id 相同不同，同时存在
id 如果相同，可以认为是一组的，从上往下执行；在所有组中，id 值越大，优先级越高，越先执行衍生 = DERIVED
运行结果
derived_merge是MySQL 5.7引入的，其会试图将Derived Table（派生表，from后面的子查询），视图引用，公用表表达式（Common table expressions）与外层查询进行合并。
MySQL 5.7中不再兼容的实现方式，可以通过调整optimizer_switch来加以规避set optimizer_switch='derived_merge=off';
select_type
有那些
1. simple
2. primary
3. subquery
4. derived
5. union
6. union result
查询的类型，主要是用于区别普通查询，联合查询，子查询等复杂的查询
1. simple
简单的 select 查询，查询中不包含子查询或者union
2. primary
查询中若包含任何复杂的子部分，最外层查询则被标记为
3. subquery
在 select 或 where 列表中包含子查询
4. derived
在 from 列表中包含的子查询被标记为 derived (衍生) MySQL 会递归执行这些子查询，把结果放在临时表中。
5. union
若第二个 select 出现 union 之后，则被标记为 union , 若 union 包含在 from 子句查询中，外层 select 将被标记为 derived
6. union result
从 union 表获取结果的 select
table
这行数据是关于哪张表的
type
类型
all
index
range
ref
eq_ref
const, system
NULL
访问类型排列
type 显示的是访问类型， 是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > range > index > ALL
一般来说得保证查询至少达到 range 级别， 最好能达到 ref
显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>range>index>ALL
sytem > const > eq_ref > ref > fulltext > ref_or_null > index_merge >> unique_subquery > index_subquery > range > index > ALL
sytem
表只有一行记录（等于系统表），这是 const 类型的特列， 平时不会出现，这个也可以忽略不计
const
``` explain select * from (select * from t1 where id =1) d1; ```
表示通过索引一次就找到了， const 用于比较 primary key 或者 unique 索引。 因为只匹配一行数据，所以很快如将主键置于where 列表中， MySQL 就能将该查询转换为一个常量
示例
eq_ref
``` explain select * from t1, t2 where t1.id = t2.id; ```
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
示例
ref
```' #员工表添加年龄列 alter table tb_emp add column `age` int(11) default null after `name`; #添加复合索引 create index idx_emp_name_age on tb_emp(`name`, `age`); explain select * from tb_emp where `name` = 'z3'; ```
非唯一性索引扫描， 返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独的行，然而，它可能会找到多个符合个条件的行，所以它应该属于查找和扫描的混合体
示例
rang
``` explain select * from t1 where id between 1 and 3; explain select * from t1 where id in (1, 2, 3); ```
只检索给定范围内的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是你在 where 语句中出现了 between、<、>、in 等的查询 这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某个点，而结束于另一个点，不用全表扫描
示例
index
``` explain select id from t1; ```
Full Index Scan ， index 于 ALL的却别 ，index 类型只遍历索引树， 这通常比 ALL 快， 因为索引文件通常比数据文件小。（也就是说虽然 all 和 index 都是读全表，但是index 是从索引中读取的， 而 all 是从硬盘中读取的 ）
示例
all
``` explain select * from t1; ```
Full Table Scan 将遍历全表找到匹配的行
示例
备注：一般来说，得以保证查询至少达到 rang 级别， 最好能达到 ref。
possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key
实际使用的索引，如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在KEY列表中
``` explain select col1, col2 from t1; create index idx_col1_col2 on t1(col1, col2); explain select col1, col2 from t1; ```
示例1
示例2
key_len
``` desc t1; explain select * from t1 where col1 = 'ab'; explain select * from t1 where col1 = 'ab' and col2 = 'bc'; ```
表示索引中使用的字节数，可通过该列计算查询中的使用的索引的长度，在不损失精确性的情况下，长度越短越好
key_len 显示的只为索引字段的最大可能长度， 并非实际使用长度。即 key_len e是更具表定义计算而得，不是通过表内检索出的。
示例
总结：条件越多，付出的代价越大，key_len 的长度也就越大，建议在一定条件的情况下，key_len 越短，效率越高。
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值
示例
由，key_len 可知，t2 表的 idx_col1_col2 被从充分使用了, co1 匹配了 t1 表的 col1, t2 表的 col1 匹配了一个常量即 ‘ac’
查询中与其他表关联的字段，外键关系建立索引。（实际开发中不推荐）
rows
根据表统计信息及索引选用情况， 大致估算出找到所需的记录所需读取的行数
filtered
Extra
包含不适合其他列中显示但十分重要的额外信息
id, select_type, table, type , possible_keys, key, key_len, ref, rows, Extra
1. Using filesort
说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取mysql 中无法利用索引完成的排序叫做 “文件排序”
示例
2. Using temporary
使用了临时表保存中间结果， MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by 。
示例
3. Using index
表示相应的 select 操作使用了覆盖索引 (Covering Index), 避免了访问表的数据行，效率不错～如果同时出现 using where , 表示索引被用来执行索引键值的查找；如果没有同时出现 using where , 表明索引引用来读取数据而非执行查找动作。
示例
覆盖索引 （Covering Index）
覆盖索引 （Covering Index）, 一说为索引覆盖
理解方式一：就是 select 的数据列只用从索引中就能取得，不必读取数据行， MySQL 可以利用你索引返回 select 列表的字段， 而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖
理解方式二：索引是高效找到的行的一个方法， 但是一般数据库也能使用索引找到一个列的数据， 因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据， 那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
注意
如果要使用覆盖索引，一定要注意 select 列表汇总只取出需要的列，不可 select *
因为如果将所有字段一起做索引将会导致索引文件过大，查询性能下降。
4. Using where
表明使用了 where 过滤
5. using join buffer
使用了链接缓存
6. impossible where
where 子句的值总是 false , 不能用来获取任何元组
示例
7. select tbale optimized away
在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUT（*） 操作不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8. distinct
优化 distinct 操作 在找到第一匹配的元祖后立即停止找相同值的动作。
热身 CASE
案例一
问题
案例分析
第一行 （执行顺序4）：id 列为1 ， 表示 union 的第一个 select , select_type 的 primary 表表示该查询为外层查询， table 列被标记为 <derived3>, 表示查询结果来自一个衍生表，其中 derived3 中的 3 代表查询衍生自第三个 select 查询， 即 id 为 3 的 select [select d1.name ... ]
第二行（执行顺序为2）：id 为 3 ，是整个查询中第三个 select 的一部分， 因查询包含在from 中， 所以为derived 。 【select id, name from where other_column = ''】
第三行（执行顺序为3）：select 列表中的子查询 select_type 为 subquery , 为整个查询中的第二个 select . [select id from t3]
第四行（执行顺序为1）：select_type 为 union ， 说明第四个 select 是 unin 里的第二个 select , 最先执行 【select name ,id from t2】
第五行（执行顺序为5）：代表 union 的临时表中读取行的阶段， table 列的 <union , 1, 4> 表示用第一个 和第四个 select 结果进行union 操作 。 【两个结果 union 操作】
索引优化
索引分析
一表
建SQL
``` create table if not exists `article` ( `id` int(11) auto_increment not null, `author_id` int(11) not null, `category_id` int(11) not null, `views` int(11) not null, `comments` int(11) not null, `title` varchar(255) not null, `content` text not null, primary key(id) ); insert into `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) values (1, 1, 1, 1, '1', '1'), (2, 2, 2, 2, '2', '2'), (3, 3, 3, 3, '3', '3'), (4, 4, 4, 4, '4', '4'); select * from `article`; ```
案例
``` #查询 category_id 为 1 且 comments 大于 1 的情况下， views 最多的 article_id explain select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1; #结论：很显然，type 是 ALL， 即最坏的情况。 Extra 里还出现了 Using filesort 也是最坏的情况，必须优化。 #查询表的索引 show index from article; #1.1 开始优化：（创建索引 + 删除索引） #alter table `article` and index idx_article_ccv(`category_id`, `comments`, `views`); create index idx_article_cvv on `article`(`category_id`, `comments`, `views`); drop index idx_article_cvv on `article`; #1.2 优化后执行 explain explain select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1; explain select id, author_id from article where category_id = 1 and comments = 3 order by views desc limit 1; #结论 #type 变成了 range, 这是可以忍受的。但是 extra 里使用 Using filesort 依然无法接受的。 #但是我们已经建立了索引，为撒没有什么用呢？ #这是因为按照 BTree 的缩影工作原理， #先排序 category_id #如果遇到相同的 category_id 则再排序 comments, 如果遇到相同的 comments 则再排序 views. #当 comments 字段是在联合索引里处于中间位置时， #因 comments > 1 条件是一个范围值 （所谓 range）, #MySQL 无法利用索引再对后面的 views 部分进行检索， 即 range 类型查询字段后面的索引无效。 #1.3 删除第一次创建的索引 drop index idx_article_cvv on `article`; #1.4 新创建索引 #alter table `article` and index idx_article_cv(`category_id`, `views`); create index idx_article_cv on `article`(`category_id`, `views`); #1.4 重新执行 explain explain select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1; #结论： #可以看到 type 变成了 ref ， Extra 中的 Using filesort 也消失了，结果非常的理想。 drop index idx_article_cv on article; ```
两表
建表SQL
``` create table if not exists `class` ( `id` int(11) auto_increment not null, `card` int(11) not null, primary key(`id`) ); ```
案例
``` use oemp; #图书分类 create table if not exists `class` ( `id` int(11) auto_increment not null, `card` int(11) not null, primary key(`id`) ); #图书 create table if not exists `book` ( `id` int(11) auto_increment not null, `card` int(11) not null, primary key(`id`) ); delete from `book`; delete from `class`; #图书分类 insert into `class`(`id`, `card`) values(1, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(2, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(3, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(4, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(5, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(6, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(7, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(8, floor(1 + (rand() * 20))); #图书 insert into `book`(`id`, `card`) values(1, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(2, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(3, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(4, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(5, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(6, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(7, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(8, floor(1 + (rand() * 20))); select * from `class`; select * from `book`; #下面的 expain 分析 explain select * from class left join book on class.card = book.card; #结论 type 有 ALL #添加索引优化 alter table `book` add index idx_book_c(`card`); #第二次 explain explain select * from class left join book on class.card = book.card; #可以看到第二行的type 变成了 ref, row 也变成了优化比较明显。 #这是由做左链接的特征决定的。 left join 条件用于确定如何从右表搜索行，左边一定有。 #右边是关键点一点要建立索引 #删除旧索引 + 新建 + 第三次 explain drop index idx_book_c on `book`; drop index idx_class_c on `class`; alter table `class` add index idx_class_c(`card`); explain select * from class left join book on class.card = book.card; #然后来看一个右链接查询： #优势比较明显，这是因为 RIGHT JOIN 条件用于确定如何从左表搜索行，右边一定都有， 所以左边我是我们的关键点，一定需要建立索引。 explain select * from class right join book on class.card = book.card; drop index idx_class_c on `class`; alter table `book` add index idx_class_c(`card`); #右链接基本无变化 explain select * from class right join book on class.card = book.card; show index from `book`; show index from `class`; ```
总结：左链接，索引建立到右表，右链接，索引建立到左表
三表
建表SQL
案例(包含结论)
``` use oemp; #分类 create table if not exists `class` ( `id` int(11) auto_increment not null, `card` int(11) not null, primary key(`id`) ); #图书 create table if not exists `book` ( `id` int(11) auto_increment not null, `card` int(11) not null, primary key(`id`) ); #手机 create table if not exists `phone` ( `id` int(11) auto_increment not null, `card` int(11) not null, primary key(`id`) ); delete from `book`; delete from `class`; delete from `phone`; #图书分类 insert into `class`(`id`, `card`) values(1, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(2, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(3, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(4, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(5, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(6, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(7, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(8, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(9, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(10, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(11, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(12, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(13, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(14, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(15, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(16, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(17, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(18, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(19, floor(1 + (rand() * 20))); insert into `class`(`id`, `card`) values(20, floor(1 + (rand() * 20))); #图书 insert into `book`(`id`, `card`) values(1, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(2, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(3, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(4, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(5, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(6, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(7, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(8, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(9, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(10, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(11, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(12, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(13, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(14, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(15, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(16, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(17, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(18, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(19, floor(1 + (rand() * 20))); insert into `book`(`id`, `card`) values(20, floor(1 + (rand() * 20))); #手机 insert into `phone`(`id`, `card`) values(1, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(2, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(3, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(4, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(5, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(6, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(7, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(8, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(9, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(10, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(11, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(12, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(13, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(14, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(15, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(16, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(17, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(18, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(19, floor(1 + (rand() * 20))); insert into `phone`(`id`, `card`) values(20, floor(1 + (rand() * 20))); select * from `class`; select * from `book`; select * from `phone`; show index from `class`; show index from `book`; show index from `phone`; #三表关联查询 explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card; #创建索引 alter table `phone` add index idx_phone_c (`card`); alter table `book` add index idx_book_c (`card`); explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card; #后第二行的 type 都是 ref 且总 rows 优化很好，效果不错。因此索引最好设置在需要进场查询的字段中。 #【结论】 #join 语句的优化 # 1. 尽可能的减少 join 语句中的 NestedLoop 的循环总次数：”永远用小结果集驱动大的结果集“。alter # 2. 优先优化 NestedLoop 的内层循环； # 3. 保证 Join 语句中被驱动表上的 Join 条件字段已经被索引。 # 4. 当无法保证被驱动表的join 条件字段被索引且内存资源充足的前提下， 大家不要吝啬 JoinBuffer 的设置。 ```
索引失效（应该避免）
建表SQL
``` use oemp; create table staffs ( id int primary key auto_increment, name varchar(24) not null default '' comment '姓名', age int not null default 0 comment '年龄', pos varchar(20) not null default '' comment '职位', add_time timestamp not null default current_timestamp comment '入职时间' ) charset utf8 comment '员工记录表'; insert into staffs(name, age, pos, add_time) values ('z3', 22, 'manager', now()); insert into staffs(name, age, pos, add_time) values ('July', 23, 'dev', now()); insert into staffs(name, age, pos, add_time) values ('2000', 23, 'dev', now()); select * from staffs; alter table staffs add index idx_staffs_nap(name, age, pos); ```
案例（索引失效）
1. 全值匹配
2. 最佳左前缀法则
如果索引了多列，需要准守最左前缀法则，指的是查询从索引的最左前列开始并且 不跳过索引中的列。
索引失效
explain select * from staffs where age = 25 and pos = 'dev';explain select * from staffs where pos = 'dev';
索引正常
explain select * from staffs where name = 'July';explain select * from staffs where name = 'July' and age = 25;explain select * from staffs where name = 'July' and age = 25 and pos = 'dev';
explain select * from staffs where name = 'July' and pos = 'dev1';
MySQL 5.6 以上版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP开启时的执行计划含有 Using index condition 标示 ，表示优化器使用了ICP对数据访问进行优化。
ICP（index condition pushdown）是MySQL利用索引（二级索引）元组和筛字段在索引中的WHERE条件从表中提取数据记录的一种优化操作。ICP的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的WHERE条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从Server层下推到Storage Engine层。Storage Engine使用索引过过滤不相关的数据，仅返回符合Index Condition条件的数据给Server层。也是说数据过滤尽可能在Storage Engine层进行，而不是返回所有数据给Server层，然后后再根据WHERE条件进行过滤。
图示1
图示2
官方解释：https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
3. 不在索引列上左任何操作 （计算、函数、（自动 or 手动）类型转换）， 会导致索引失效而转向全表扫描
示例
4. 存储引擎不能使用索引中范围条件右边的列
示例
在 5.6 +， ICP特征可以使用到 Using index condition
5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 select *
示例
6. mysql 在使用不等于 （!= 或者 <>）的时候无法使用索引会导致全表扫描
示例
7. is null, is not null 也无法使用索引
示例
8. like 以通配符开头 （'%abc ...'）mysql 索引失效会变成全表扫描的操作
问题：解决 like '% 字符串 %' 索引不被使用的方法 ？？
建表SQL
#like 关键字 '%%'create table `tb_user` (`id` int(11) not null auto_increment,`name` varchar(20) default null,`age` int(11) default null,`email` varchar(20) default null,primary key(id)) engine = innodb auto_increment=1 default charset = utf8;select * from `tb_user`;#drop table `tb_user`;insert into tb_user(name, age, email) values ('1aa1', 21, 'b@163.com');insert into tb_user(name, age, email) values ('2aa2', 222, 'a@163.com');insert into tb_user(name, age, email) values ('3aa3', 256, 'c@163.com');insert into tb_user(name, age, email) values ('4aa4', 21, 'd@163.com');
完整 SQL（含优化结论）
``` #like 关键字 '%%' create table `tb_user` ( `id` int(11) not null auto_increment, `name` varchar(20) default null, `age` int(11) default null, `email` varchar(20) default null, primary key(id) ) engine = innodb auto_increment=1 default charset = utf8; select * from `tb_user`; #drop table `tb_user`; insert into tb_user(name, age, email) values ('1aa1', 21, 'b@163.com'); insert into tb_user(name, age, email) values ('2aa2', 222, 'a@163.com'); insert into tb_user(name, age, email) values ('3aa3', 256, 'c@163.com'); insert into tb_user(name, age, email) values ('4aa4', 21, 'd@163.com'); #before index explain select name, age from tb_user where name like '%aa%'; explain select id from tb_user where name like '%aa%'; explain select name from tb_user where name like '%aa%'; explain select age from tb_user where name like '%aa%'; explain select id, name from tb_user where name like '%aa%'; explain select id, name, age from tb_user where name like '%aa%'; explain select name, age from tb_user where name like '%aa%'; #create index create index idx_user_na on tb_user(name, age); explain select id, name, age, email from tb_user where name like '%aa%'; ```
覆盖索引
示例：
9. 字符串不加单引号索引失效
示例
10. 少用 or, 用它来连接时会索引失效
示例
11. 小总结
总结
总结2
面试题讲解
SQL 题目
``` #【建表语句】 use oemp; create table test03 ( id int primary key not null auto_increment, c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10), c5 varchar(10) ); insert into test03 (c1, c2, c3, c4, c5) values ('a1', 'a2', 'a3', 'a4', 'a5'); insert into test03 (c1, c2, c3, c4, c5) values ('b1', 'b2', 'b3', 'b4', 'b5'); insert into test03 (c1, c2, c3, c4, c5) values ('c1', 'c2', 'c3', 'c4', 'c5'); insert into test03 (c1, c2, c3, c4, c5) values ('d1', 'd2', 'd3', 'd4', 'd5'); insert into test03 (c1, c2, c3, c4, c5) values ('e1', 'e2', 'e3', 'e4', 'e5'); select * from test03; ```
解题和分析
``` #【建表语句】 use oemp; create table test03 ( id int primary key not null auto_increment, c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10), c5 varchar(10) ); insert into test03 (c1, c2, c3, c4, c5) values ('a1', 'a2', 'a3', 'a4', 'a5'); insert into test03 (c1, c2, c3, c4, c5) values ('b1', 'b2', 'b3', 'b4', 'b5'); insert into test03 (c1, c2, c3, c4, c5) values ('c1', 'c2', 'c3', 'c4', 'c5'); insert into test03 (c1, c2, c3, c4, c5) values ('d1', 'd2', 'd3', 'd4', 'd5'); insert into test03 (c1, c2, c3, c4, c5) values ('e1', 'e2', 'e3', 'e4', 'e5'); select * from test03; #索引建立 create index idx_test03_c1234 on test03(c1, c2, c3, c4); show index from test03; #问题：我们创建了复合索引 idx_test03_c1234, 根据一下 SQL 分析下索引使用情况？ explain select * from test03 where c1 = 'a1'; explain select * from test03 where c1 = 'a1' and c2 = 'a2'; explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3'; explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4'; #1) Yes explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4'; #2) Yes explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3'; #3) Yes(5.6+ Using index condition) explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4'; #4) Yes(5.6+ Using index condition) explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3'; #5)Yes(5.6+ Using index condition) explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3; #c3 的作用在于排序而不是查找 #6）Yes(5.6+ Using index condition) explain select * from test03 where c1='c1' and c2='c2' order by c3; #7) Yes(5.6+ Using index condition) explain select * from test03 where c1='c1' and c2='c2' order by c4; #8) Yes(5.6+ Using index condition, Using where) explain select * from test03 where c1='a1' and c5='a5' order by c2, c3; #9) NO (Using index condition; Using where; Using filesort) explain select * from test03 where c1='a1' and c5='a5' order by c3, c2; #10) Yes(5.6+ Using index condition) explain select * from test03 where c1='a1' and c2='a2' order by c2, c3; #11) Yes(Using index condition; Using where) explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2, c3; #12) Yes(5.6+ Using index condition) explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3, c2; #本例有常量c2的情况，和#9对比 #13) No (Using index condition; Using where; Using filesort) explain select * from test03 where c1='a1' and c5='a5' order by c3, c2; #14) Yes(Using where; Using index) explain select c2, c3 from test03 where c1='a1' and c4='a4' group by c2, c3; #15) NO (Using where; Using index; Using temporary; Using filesort) explain select c2, c3 from test03 where c1='a1' and c4='a4' group by c3, c2; ```
定值、范围还是排序，一般order by是给一个范围
group by 基本上都是需要排序的， 会有临时表产生
一般性建议
对于单键索引，尽量选择针对当前 query 过滤更好的索引
在选择索引的时候，当前 query 中过滤性最好的字段顺序
在选择组合索引的时候，尽量选择可以能够包含当前query 中的where 子句更多字段的索引
尽可能通过分析统计信息和调整 query 的写法来达到选择适合索引的目的
总结
全值匹配我最爱，最左匹配前缀要准守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE 百分写最右， 覆盖索引不写星
小总结
分析过程
1. 观察，至少一天， 看看生产慢SQL的情况
2. 开启慢查询日志， 设置阈值，比如超过5秒钟就是是慢SQL ， 并将他们抓取出来。
3. explain + 慢SQL分析
4. show profile
5. 运维经理 + DBA，进行 SQL数据库服务器的参数调优。
总结
1. 慢查询开启并捕获
2. explain + 慢 SQL 分析
3. show profile 查询SQL服务器里面的执行细节和生命周期情况
4. SQL 数据库服务器的参数调优