MySQL索引优化

Posted on 2021-05-30 Edited on 2024-07-22 In 数据库 , MySQL Views: Word count in article: 10k Reading time ≈ 9 mins.

1、索引单表优化

执行以下SQL脚本，生成测试表。

CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
 
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

①假设需要查询category_id为1且comments大于1的情况下views最多的article_id，那么查询的sql语句如下：
1
SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
查询其执行计划，发现type是ALL，即最坏的情况；Extra里还出现了Using filesort，也是最坏的情况。所以优化是必须的。
②于是需要建立索引，先尝试在category_id、comments和views字段上建立联合索引，sql语句如下：
1
create index idx_article_ccv on article(category_id,comments,views);
之后再次查询执行计划，发现用到了该索引，并且type变成了range，这是可以忍受的；但是extra里还是使用Using filesort仍是无法接受的。

分析已经建立了索引，但却没有发挥作用：这是因为按照BTree联合索引的工作原理是先排序category_id，如果遇到相同的 category_id则再排序comments，如果遇到相同的comments则再排序views。而因comments>1条件是一个范围值(range)并且其处在联合索引的中间位置，所以MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。
③于是需要重新建立索引，先删除原来索引，并新建字段category_id和views的联合索引即可解决问题，sql语句如下：
1
2
DROP INDEX idx_article_ccv ON article;
create index idx_article_cv on article(category_id,views);
建立新索引后再次查询执行计划，发现type变为了ref，Extra中的Using filesort也消失了，说明还是比较理想的。这里的Extra出现了Using where，说明不能只从索引中获取数据而发生了回表查询。

2、索引两表优化

执行以下SQL脚本，生成测试表。

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`class_name` VARCHAR(10) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`student_name` VARCHAR(10) NOT NULL,
`class_id` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
 
INSERT INTO class(class_name) VALUES("一班");
INSERT INTO class(class_name) VALUES("二班");
INSERT INTO class(class_name) VALUES("三班");
INSERT INTO class(class_name) VALUES("四班");

INSERT INTO student(student_name,class_id) VALUES("a",1);
INSERT INTO student(student_name,class_id) VALUES("b",1);
INSERT INTO student(student_name,class_id) VALUES("c",1);
INSERT INTO student(student_name,class_id) VALUES("d",1);
INSERT INTO student(student_name,class_id) VALUES("e",1);
INSERT INTO student(student_name,class_id) VALUES("f",1);
INSERT INTO student(student_name,class_id) VALUES("g",2);
INSERT INTO student(student_name,class_id) VALUES("h",2);
INSERT INTO student(student_name,class_id) VALUES("i",3);
INSERT INTO student(student_name,class_id) VALUES("j",4);
INSERT INTO student(student_name,class_id) VALUES("k",4);

①假设需要列举所有班级对应的学生信息，则对应的sql语句如下：
1
SELECT * FROM class LEFT JOIN student ON class.id = student.class_id;
查询其执行计划，发现type都是是ALL，即最坏的情况，所以优化是必须的。
②尝试把索引加在student表的class_id字段，sql语句如下：
1
create index idx_class_id on student(class_id);
创建索引后重新查询执行计划，可以看到第二行的type变为了ref，rows也优化得比较明显。这是由左连接特性决定的，即LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是关键点，一定需要建立索引。同理如果是右连接则左表建立索引。

3、索引三表优化

执行以下SQL脚本，生成测试表。

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`class_name` VARCHAR(10) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`student_name` VARCHAR(10) NOT NULL,
`class_id` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `teacher` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`teacher_name` VARCHAR(10) NOT NULL,
`class_id` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
 
INSERT INTO class(class_name) VALUES("一班");
INSERT INTO class(class_name) VALUES("二班");
INSERT INTO class(class_name) VALUES("三班");
INSERT INTO class(class_name) VALUES("四班");

INSERT INTO student(student_name,class_id) VALUES("a",1);
INSERT INTO student(student_name,class_id) VALUES("b",1);
INSERT INTO student(student_name,class_id) VALUES("c",1);
INSERT INTO student(student_name,class_id) VALUES("d",1);
INSERT INTO student(student_name,class_id) VALUES("e",1);
INSERT INTO student(student_name,class_id) VALUES("f",1);
INSERT INTO student(student_name,class_id) VALUES("g",2);
INSERT INTO student(student_name,class_id) VALUES("h",2);
INSERT INTO student(student_name,class_id) VALUES("i",3);
INSERT INTO student(student_name,class_id) VALUES("j",4);
INSERT INTO student(student_name,class_id) VALUES("k",4);


INSERT INTO teacher(teacher_name,class_id) VALUES("aa",1);
INSERT INTO teacher(teacher_name,class_id) VALUES("bb",1);
INSERT INTO teacher(teacher_name,class_id) VALUES("cc",2);
INSERT INTO teacher(teacher_name,class_id) VALUES("dd",2);
INSERT INTO teacher(teacher_name,class_id) VALUES("ee",3);
INSERT INTO teacher(teacher_name,class_id) VALUES("ff",3);
INSERT INTO teacher(teacher_name,class_id) VALUES("gg",3);
INSERT INTO teacher(teacher_name,class_id) VALUES("hh",4);
INSERT INTO teacher(teacher_name,class_id) VALUES("ii",4);
INSERT INTO teacher(teacher_name,class_id) VALUES("jj",4);
INSERT INTO teacher(teacher_name,class_id) VALUES("kk",4);
INSERT INTO teacher(teacher_name,class_id) VALUES("ll",4);
INSERT INTO teacher(teacher_name,class_id) VALUES("mm",4);
INSERT INTO teacher(teacher_name,class_id) VALUES("nn",4);

①假设需要列举所有班级对应的学生信息以及老师信息，则对应的sql语句如下：
1
SELECT * FROM class LEFT JOIN student ON class.id = student.class_id LEFT JOIN teacher on student.class_id = teacher.class_id;
查询其执行计划，发现type都是是ALL并且使用到了连接缓存，即最坏的情况，所以优化是必须的。
②尝试把索引加在student表的class_id字段以及teacher表的class_id字段，sql语句如下：
1
2
create index idx_class_id on student(class_id);
create index idx_class_id on teacher(class_id);
创建索引后重新查询执行计划，可以看到后两行的type变为了ref，rows也优化得比较明显，原因也是由于左连接特性。

总结：
- 保证被驱动表的join字段已经被索引。
- left join时，选择小表作为驱动表，大表作为被驱动表。
- inner join时，mysql会自己帮我们把小结果集的表选为驱动表。

4、order by关键字优化

MySQL支持二种方式的排序，FileSort和Index。Index效率高，它指的是MySQL扫描索引本身完成排序；FileSort方式效率较低。

执行以下SQL脚本，生成测试表。

CREATE TABLE tblA(
  id int primary key not null auto_increment,
  age INT,
	birth TIMESTAMP NOT NULL
);
 
INSERT INTO tblA(age,birth) VALUES(22,NOW());
INSERT INTO tblA(age,birth) VALUES(23,NOW());
INSERT INTO tblA(age,birth) VALUES(24,NOW());
 
CREATE INDEX idx_A_ageBirth ON tblA(age,birth);

使用到索引并且不会出现文件内排序的order by情况：

1	explain select * from tblA where age > 20 order by age;

1	explain select * from tblA where age > 20 order by age,birth;

1	explain select * from tblA where age = 20 order by birth;

1	explain select * from tblA where birth > '2021-06-01 16:47:20' order by age;

使用到索引并且会出现文件内排序的order by情况：

1	explain select * from tblA where age > 20 order by birth;

1	explain select * from tblA where age > 20 order by birth,age;

1	explain select * from tblA order by birth;

1	explain select * from tblA where birth > '2021-06-01 16:47:20' order by birth;

1	explain select * from tblA order by age asc,birth desc;

总结：
- ORDER BY 语句使用索引最左前列会使用Index方式排序。
- 使用where子句(使用常量)与order by子句条件列组合满足索引最左前列会使用Index方式排序。
- 使用where子句(使用范围查询)与order by子句条件列组合会导致即使满足索引最左前列也会使用FileSort方式排序。
如果不在索引列上，filesort有两种算法：双路排序和单路排序。
- 双路排序：MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
  - 多路排序需要借助磁盘来进行排序。所以取数据，排好了取数据。两次io操作会比较慢。
- 单路排序：在mysql4.1之后，出现了第二种改进的算法，就是单路排序。即从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
  - 由于单路是后出的，总体而言好过双路。但可能会出现以下问题：假设在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
  - 所以需要进行优化，即修改缓冲区的参数配置或者优化sql语句。
    - 增大sort_buffer_size参数：用于单路排序的内存大小。
      - 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
    - 增大max_length_for_sort_data参数：单次排序字段大小。(单次排序请求)
      - 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。
    - 去掉select后面不需要的字段，因为select后字段多了，排序的时候也会带着一起，很占内存，所以去掉没有用的。
      - 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法即单路排序，否则用老算法多路排序。
      - 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

5、索引失效

执行以下SQL脚本，生成测试表。

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  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());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

①未符合最左前缀法则的查询索引会失效。即如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始(只要有索引的最左前列，顺序不同也可以使索引生效，因为MySQL中有查询优化器explain，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划，所以不论以何种顺序都可使用到联合索引)。
- 先看看符合最左前缀法则的查询索引生效。
  1
  EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
  1
  EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
  1
  EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
- 再看看不按照最左前缀法则的查询索引失效。
  1
  EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
②不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
1
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';
③范围查询后的索引全失效。即范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效，若是不同索引则不会失效)，这里的例子是联合索引中的pos字段失效。
1
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age > 25 AND pos = 'dev';
④like以通配符开头(‘%abc…’)则索引失效会变成全表扫描的操作(通配符放在后面则索引不会失效)。
1
2
EXPLAIN SELECT * FROM staffs WHERE NAME like '%July';
EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%';
而解决like ‘%字符串%’时索引不被使用的方法是使用覆盖索引，即select查询的字段要被联合索引覆盖(查询主键也可以，原因是mysql普通B+树索引的叶子结点存放了主键id)。
1
EXPLAIN SELECT id,name,age,pos FROM staffs WHERE NAME like '%July%';
⑤字符串不加单引号索引失效，原因是底层进行转换使索引失效，即相当于使用了函数造成索引失效。

⑥联合索引中不同字段使用or会失效。

1 2	EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' or age = 25; EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' or name = 'sas';

⑦or语句前后没有同时使用索引则会失效。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。(左右不是同一个索引)

drop index idx_staffs_nameAgePos on staffs;
create index idx_name on staffs(name);
create index idx_age on staffs(age);
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' or age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' or pos = 'ygf';