MySQL进阶_查询优化和索引优化

第一节、索引失效案例

可以从以下维度对数据库进行优化：

• 索引失效、没有充分利用到索引–索引建立
• 关联查询太多JOIN (设计缺陷或不得已的需求)–SQL优化
• 服务器调优及各个参数设置(缓冲、线程数等）–调整my.cnf
• 数据过多–分库分表

虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。

• 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
• 逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

其实，用不用索引，最终都是优化器说了算。优化器是基于cost开销（通过JSON格式可以看到开销数据）的，它不是基于规则，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

EXPLAIN可以输出四种格式： 传统格式 ， JSON格式 ， TREE格式 以及可视化输出 。用户可以根据需要选择适用于自己的格式。

1.1 数据准备

创建两个表，通过函数和存储过程填充数据。

CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

1.2 全值匹配我最爱

// 查询语句
SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classid=4 AND NAME= 'abcd';

// 分别建立不同的索引
CREATE INDEX idx_age ON student (age) ; // 索引1
CREATE INDEX idx_age_classid oN student (age,classid); // 索引2
CREATE INDEX idx_age_classid_name ON student (age,classid,NAME); // 索引3
1
2
3
4
5
6
7

当数据量非常大时，发现使用索引3的执行时间最短。也就是说，尽量将WHERE后的字段都建立索引（如果有多个，建立联合索引）。

1.3 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';
1

针对于上面的查询语句，虽然已经创建了索引idx_age_classid_name，但是WHERE后没有使用age，所以无法使用此索引。这一点可以从索引数据结构的角度来解释，创建索引idx_age_classid_name时，B+树首先根据age来排序，如果age相同，再根据classid，如果classid相同，再根据name。而WHERE后没有使用age，所以无法从此B+树获取结果，从而无法使用索引。

1.4 主键插入顺序

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 。

1.5 计算、函数、类型转换(自动或手动)导致索引失效

• student表的字段stuno上设置有索引（计算）

CREATE INDEX idx_sno ON student(stuno);
1

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
1

• student表的字段name上设置有索引（函数）

CREATE INDEX idx_name ON student(NAME);
1

EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';
1

• 类型转换导致索引失效（类型转换）

CREATE INDEX idx_name ON student(NAME);
1

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
1
2
3
4

因为name的类型为varchar，name=123会发生类型转换

1.6 范围条件右边的列索引失效

• 1

create index idx_age_classid_name on student(age,classid,name);
1

// 此时使用到联合索引的age和id，name失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
1
2

使用到age和id，这两个字段的类型均为INT，所以key_len为4 + 1（NULL） + 4 + 1(NULL)

• 2

create index idx_age_name_classid on student(age,name,classid);
1

// 此时可以使用到age + name + id
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20 ;
1
2

开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后（创建的联合索引中，务必把范围涉及到的字段写在最后）

1.7 不等于(!= 或者<>)索引失效

不等于的时候又要去一个一个找，所以使用不上索引。

1.8 is null可以使用索引，is not null无法使用索引

这个道理和1.7一样

最好在设计数据表时就将字段设置为NOT NULL约束，比如可以将INT类型的字段，默认值设置为0；将字符类型的字段默认值设置为空字符串（‘’）

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

1. 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会起作用。只有“%"不在第一个位置，索引才会起作用。

1.10 OR 前后存在非索引的列，索引失效

1. 在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。
2. 因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

1.11 总结

1. 对于单列索引，尽量选择针对当前query过滤性更好的索引
2. 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
3. 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
4. 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
   总之，书写SQL语句时，尽量避免造成索引失效的情况。

第二节、关联查询优化 & 子查询优化

公司里暂时用不到，以后用到再学习

第三节、 order by 优化

3.1 排序优化

1. 在MysQL中，支持两种排序方式，分别是FileSort和Index排序。Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
2. FileSort排序则一般在内存中进行排序，占用CPU 较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

3.2 优化建议

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

3.3 实际举例

场景：查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
1

优化思路：

为了去掉filesort我们可以把索引建成：

// 索引1
CREATE INDEX idx_age_name ON student(age,NAME);
// 索引2
CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);
1
2
3
4

在explain结果中可以看到，使用索引1时，Extra中没有filesort；使用索引2时，Extra中有filesort，但是使用索引2的速度却高于使用索引1。

所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的stuno<101000这个条件，如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。
当【范围条件】和【group by或者order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

3.4 filesort算法

1. 双路排序
   首先从磁盘中只读取order by后面用到的排序字段，在sort_buffer中排好序；然后从磁盘中读取其他字段，得到结果；
   取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的

2. 单路排序
   从磁盘读取查询需要的 所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取sort_buffer容量大小，再排…从而多次I/O。

3.5 filesort调优

1. 尝试提高 sort_buffer_size
2. 尝试提高 max_length_for_sort_data
   MySQL会将要排序的字段长度和这个值做对比。如果待排序字段大于此值，使用双路排序；反之使用单路排序。
3. Order by 时select * 是一个大忌。最好只Query需要的字段。

第四节、 group by 优化

1. group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
2. group by 先排序再分组，遵照索引的最佳左前缀法则
3. 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
4. where效率高于having，能写在where限定的条件就不要写在having中了
5. 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
6. 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

第五节、 覆盖索引

5.1 什么是覆盖索引？

1. 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
2. 非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

5.2 实例

CREATE INDEX idx_age_name ON student (age,NAME);
1

EXPLAIN SELECT * FROM student WHERE age <> 20;
1

如上图所示，没有使用索引，符合以前说的索引失效情形；

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;
1

当查询条件改变之后，使用了索引。
这是因为情形1，优化器认为使用索引的成本(二级索引运算+回表)>全表扫描的成本，所以不使用索引；而情形2中，使用覆盖索引，不需要回表操作，此时优化器认为使用索引的成本更低。

5.3 覆盖索引的好处

1. 在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作提升了查询效率。
2. 由于覆盖索引是按键值的顺序存储的，对于I0密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

5.4 覆盖索引的坏处

索引字段的维护 总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

第六节、 索引下推（ICP）

6.1 索引下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

6.2 实例

准备工作：

CREATE TABLE `people` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `zipcode` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `lastname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zip_last_first` (`zipcode`,`lastname`,`firstname`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

INSERT INTO `people` VALUES 
('1', '000001', '三', '张', '北京市'), 
('2', '000002', '四', '李', '南京市'), 
('3', '000003', '五', '王', '上海市'), 
('4', '000001', '六', '赵', '天津市');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查询语句为：

EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';
1
2
3
4

结果为：

1. 在Extra中有“using index condition”,表示使用了索引下推。
2. 索引zip_last_first中包含了字段zipcode,lastname,firstname,当查询时，可以直接使用到zipcode字段，假设经过zipcode过滤后的数据有10000条，就意味着要回表再去查这10000条数据。但是此时发现在索引中包括lastname，所以直接在存储引擎中对lastname进行过滤，假设过滤后的数据只剩下10条，此时再去回表查的成本就大大降低了。

6.3 ICP的使用条件

第七节、 其他查询优化策略

7.1 COUNT(*)与COUNT(具体字段)效率

问：在 MySQL 中统计数据表的行数，可以使用三种方式： SELECT COUNT(*) 、 SELECT COUNT(1) 和SELECT COUNT(具体字段) ，使用这三者之间的查询效率是怎样的？

7.2 关于SELECT(*)

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：

1. MySQL 在解析的过程中，会通过 查询数据字典 将"*"按序转换成所有列名，这会大大的耗费资源和时间。
2. 无法使用 覆盖索引

7.3 主键的设计

还有一些主键的设计原则，以淘宝为例，后续如果会用到再去看视频。