使用索引
准备工作:
drop table if EXISTS staffs;
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('admin',30,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('Tom',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_name_age_pos(name, age, pos);
SELECT * FROM staffs;
索引规范建议
- 按顺序全值匹配
- 最佳左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。 - 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
- mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
- is not null 也无法使用索引,但是is null是可以使用索引的
- like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
- 字符串不加单引号索引失效
- 少用or, 用它来连接时会索引失效
按顺序全值匹配
索引 index_staffs_name_age_pos 建立索引时 以 name , age ,pos 的顺序建立的,全值匹配表示按顺序匹配的。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 75 | const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.10 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 79 | const,const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
1 row in set (0.07 sec)
mysql>
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 141 | const,const,const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
1 row in set (0.12 sec)
最佳左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在,会自动优化。
表staffs 索引index_staffs_name_age_pos 索引 id 为主键
- 当使用覆盖索引的方式时,(select name, age, id from staffs where age=10 (后面没有其他没有索引的字段条件)),即使不是以 name 开头,也会使用 index_staffs_name_age_pos 索引。
mysql> explain select age, name, id from staffs where age=10;
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | staffs | index | NULL | index_staffs_name_age_pos | 141 | NULL | 4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
1 row in set (0.09 sec)
既 select 后的字段 有索引,where 后的字段也有索引,则无关执行顺序。
- 除开上述条件 才满足最左前缀法则。
mysql> EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.08 sec)
mysql>
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.09 sec)
不在索引列上做任何操作
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
mysql> EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 75 | const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.03 sec)
存储引擎不能使用索引中范围条件右边的列
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 75 | const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.10 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 79 | const,const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
1 row in set (0.07 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 141 | const,const,const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
1 row in set (0.12 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age > 25 AND pos = 'dev';
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | range | index_staffs_name_age_pos | index_staffs_name_age_pos | 79 | NULL | 1 | Using where |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
1 row in set (0.06 sec)
观察key_len 字段的值变化
尽量使用覆盖索引
尽量使用覆盖索引,索引列和查询列一致,减少select *。
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+--------------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 141 | const,const,const | 1 | Using where; Using index |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+--------------------------+
1 row in set (0.04 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 141 | const,const,const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
1 row in set (0.05 sec)
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 75 | const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.05 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME != 'July';
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_name_age_pos | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.12 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July';
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_name_age_pos | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.06 sec)
is not null 也无法使用索引,但是is null是可以使用索引的
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is null;
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 75 | const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.04 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is not null;
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_name_age_pos | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.04 sec)
like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
- like ‘%abc%’ type 类型会变成 all
- like ‘abc%’ type 类型为 range ,算是范围,可以使用索引
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like 'Jul%';
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | range | index_staffs_name_age_pos | index_staffs_name_age_pos | 75 | NULL | 1 | Using where |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
1 row in set (0.05 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%Jul';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like 'Ju%l';
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | range | index_staffs_name_age_pos | index_staffs_name_age_pos | 75 | NULL | 1 | Using where |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
1 row in set (0.09 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%Ju%l';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.06 sec)
那如何解决like '%字符串%'时索引不被使用问题呢?
使用覆盖索引
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%Jul%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.07 sec)
mysql> EXPLAIN SELECT name FROM staffs WHERE NAME like '%Jul%';
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | staffs | index | NULL | index_staffs_name_age_pos | 141 | NULL | 4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
1 row in set (0.16 sec)
字符串不加单引号索引失效
底层进行转换使索引失效,使用了函数造成索引失效
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = '1234';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | staffs | ref | index_staffs_name_age_pos | index_staffs_name_age_pos | 75 | const | 1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.02 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 1234;
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_name_age_pos | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.03 sec)
少用or, 用它来连接时会索引失效
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = '1234' or name = '2345';
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_name_age_pos | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.06 sec)
小测试总结
假设index(a,b,c)
Where语句 | 索引是否被使用 |
---|---|
where a = 3 | Y,使用到a |
where a = 3 and b = 5 | Y,使用到a,b |
where a = 3 and b = 5 and c = 4 | Y,使用到a,b,c |
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
where a = 3 and c = 5 | 使用到a, 但是c不可以,b中间断了 |
where a = 3 and b > 4 and c = 5 | 使用到a和b, c不能用在范围之后,b后断了 |
where a = 3 and b like 'kk%' and c = 4 | Y,使用到a,b,c |
where a = 3 and b like '%kk' and c = 4 | Y,只用到a |
where a = 3 and b like '%kk%' and c = 4 | Y,只用到a |
where a = 3 and b like 'k%kk%' and c = 4 | Y,使用到a,b,c |
索引建议
- 对于单键索引,尽量选择针对当前query过滤性更好的索引;
- 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。(避免索引过滤性好的索引失效)
- 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引;
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
单表查询优化
建表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');
SELECT * FROM article;
查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。
mysql> SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
| 3 | 1 |
+----+-----------+
1 row in set (0.08 sec)
查询当然可以查出来,下面用explain来分析下:
第一次explain
mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.04 sec)
很明显, type 是 ALL,即最坏的情况(全表扫描)。Extra 里还出现了 Using filesort (文件内排序),也是最坏的情况。
开始优化:
第一次新建索引
create index idx_article_ccv on article(category_id,comments,views);
第2次explain
mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | Using where; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
1 row in set (0.05 sec)
type 变成了 range, 这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
但是我们已经建立了索引,为啥没用呢?
这是因为按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时,因comments > 1 条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
第二次新建索引
# 先删除第一次的索引
DROP INDEX idx_article_ccv ON article;
create index idx_article_cv on article(category_id,views);
第3次explain
mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | article | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.06 sec)
可以看到, type 变为了 ref, Extra 中的 Using filesort 也消失了,结果非常理想。
所以说索引建的准很重要!!!
关联查询优化
建表语句:
CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
- 开始explain分析
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.09 sec)
type 有All
- 添加索引优化(右表)
ALTER TABLE `book` ADD INDEX Y ( `card`);
- 第2次explain
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+---------------------+------+-------------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 20 | |
| 1 | SIMPLE | book | ref | Y | Y | 4 | index_db.class.card | 1 | Using index |
+----+-------------+-------+------+---------------+------+---------+---------------------+------+-------------+
2 rows in set (0.09 sec)
可以看到第二行的 type 变为了 ref, rows 也变成了优化比较明显。
这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。
- 左边表新建索引
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
- 第3次explain
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | class | index | NULL | X | 4 | NULL | 20 | Using index |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
2 rows in set (0.09 sec)
看结果 row,并没有明显优化
优化建议
1、保证被驱动表的join字段已经被索引。
2、left join 时,选择小表作为驱动表,大表作为被驱动表。
3、inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
4、子查询尽量不要放在被驱动表,有可能使用不到索引。
子查询优化
用 in 还是 exists
优化原则:小表驱动大表
select * from A where id in (select id from B)
上面先遍历B表,再遍历A表,这里B表是驱动表;
当B表的数据集小于A表的数据集时,用in优于exists。
select * from A where exists (select 'a' from B where B.id = A.id)
上面先遍历A表,再遍历B表,这里A表是驱动表;
当A表的数据集小于B表的数据集时,用exists优于in。
上述A和B表的ID字段应建立索引。
order by关键字优化
ORDER BY子句,尽量使用Index方式排序, 避免使用FileSort方式排序
建表SQL
CREATE TABLE tblA(
id int primary key not null auto_increment,
age INT,
birth TIMESTAMP NOT NULL,
name varchar(200)
);
INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd');
INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def');
CREATE INDEX idx_A_ageBirth ON tblA(age,birth);
SELECT * FROM tblA;
MySQL支持二种方式的排序,FileSort和Index,Index效率高. 它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两情况,会使用Index方式排序:
- ORDER BY 语句使用索引最左前列
- 使用Where子句与Order BY子句条件列组合满足索引最左前列
where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。
尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀原则。
如果不在索引列上,filesort有两种算法: mysql就要启动双路排序和单路排序
双路排序和单路排序
双路排序
- MySQL 4.1之前是使用双路排序, 字面意思就是两次扫描磁盘,最终得到数据, 读取行指针和orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。
- 从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。
取一批数据,要对磁盘进行了两次扫描,众所周知,I\O是很耗时的,所以在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操作,反而得不偿失。
优化策略:
- 增大sort_buffer_size参数的设置
用于单路排序的内存大小 - 增大max_length_for_sort_data参数的设置
单次排序字段大小。(单次排序请求) - 去掉select 后面不需要的字段
select 后的多了,排序的时候也会带着一起,很占内存,所以去掉没有用的
分页查询的优化(limit)
参考这篇文章创建的emp表,并且表中批量插入了150万数据,这样能更好的看到优化的效果
mysql> explain select sql_no_cache * from emp order by deptno limit 10000,40;
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 1500209 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.09 sec)
上面出现了 Using filesort
那我们就给deptno这个字段加上索引吧。
mysql> create index index_emp_deptno on emp(deptno);
Query OK, 0 rows affected (7.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建索引后再试一下:
mysql> explain select sql_no_cache * from emp order by deptno limit 10000,40;
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 1500209 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.08 sec)
然而并没有用。
继续优化: 先利用覆盖索引把要取的数据行的主键取到,然后再用这个主键列与数据表做关联:(查询的数据量小了后)
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id;
+----+-------------+------------+--------+---------------+------------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------------------+---------+------+-------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 40 | |
| 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | a.id | 1 | |
| 2 | DERIVED | e | index | NULL | index_emp_deptno | 3 | NULL | 10040 | Using index |
+----+-------------+------------+--------+---------------+------------------+---------+------+-------+-------------+
3 rows in set (0.07 sec)
最后比较一下查询速度:
优化前:
mysql> select sql_no_cache * from emp order by deptno limit 10000,40;
+-------+-------+--------+----------+-----+------------+---------+--------+--------+
| id | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+--------+----------+-----+------------+---------+--------+--------+
| 98137 | 99138 | yMppGj | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98148 | 99149 | ppDVRA | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98150 | 99151 | KdnfNy | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98176 | 99177 | uCEqQf | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98183 | 99184 | jlFsYP | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98203 | 99204 | dNEHzA | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98221 | 99222 | cRfbPU | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98234 | 99235 | lkpXRq | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98305 | 99306 | vCzSNn | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98307 | 99308 | jwHXOd | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98334 | 99335 | lIGgNy | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98335 | 99336 | PRQDum | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98340 | 99341 | NLrABg | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98345 | 99346 | YdwWsb | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98365 | 99366 | IvehAg | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98371 | 99372 | qRmMVs | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98379 | 99380 | lQvFOe | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98406 | 99407 | pQjwIa | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98413 | 99414 | KwdbWE | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98427 | 99428 | xzhjCg | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98432 | 99433 | QMnczp | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98449 | 99450 | QFGnxz | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98461 | 99462 | RWjfZF | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98472 | 99473 | OpiXms | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98478 | 99479 | mYfIXP | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98481 | 99482 | Iaabfy | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98484 | 99485 | WiaCMc | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98487 | 99488 | YsUuqv | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98500 | 99501 | cbdjLc | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98505 | 99506 | AkwBvx | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98507 | 99508 | auWyCr | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98511 | 99512 | msciKb | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98516 | 99517 | WwuHey | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98523 | 99524 | XgPEBW | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98529 | 99530 | Kepoxz | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98559 | 99560 | VpPdXE | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98567 | 99568 | jnLRbG | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98586 | 99587 | huByLm | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98603 | 99604 | ftCEqP | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
| 98608 | 99609 | RnOgqk | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 |
+-------+-------+--------+----------+-----+------------+---------+--------+--------+
40 rows in set (1.52 sec)
优化后:
mysql> SELECT SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id;
+-------+-------+--------+----------+-----+------------+---------+--------+--------+-------+
| id | empno | ename | job | mgr | hiredate | sal | comm | deptno | id |
+-------+-------+--------+----------+-----+------------+---------+--------+--------+-------+
| 98137 | 99138 | yMppGj | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98137 |
| 98148 | 99149 | ppDVRA | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98148 |
| 98150 | 99151 | KdnfNy | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98150 |
| 98176 | 99177 | uCEqQf | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98176 |
| 98183 | 99184 | jlFsYP | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98183 |
| 98203 | 99204 | dNEHzA | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98203 |
| 98221 | 99222 | cRfbPU | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98221 |
| 98234 | 99235 | lkpXRq | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98234 |
| 98305 | 99306 | vCzSNn | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98305 |
| 98307 | 99308 | jwHXOd | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98307 |
| 98334 | 99335 | lIGgNy | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98334 |
| 98335 | 99336 | PRQDum | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98335 |
| 98340 | 99341 | NLrABg | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98340 |
| 98345 | 99346 | YdwWsb | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98345 |
| 98365 | 99366 | IvehAg | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98365 |
| 98371 | 99372 | qRmMVs | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98371 |
| 98379 | 99380 | lQvFOe | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98379 |
| 98406 | 99407 | pQjwIa | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98406 |
| 98413 | 99414 | KwdbWE | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98413 |
| 98427 | 99428 | xzhjCg | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98427 |
| 98432 | 99433 | QMnczp | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98432 |
| 98449 | 99450 | QFGnxz | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98449 |
| 98461 | 99462 | RWjfZF | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98461 |
| 98472 | 99473 | OpiXms | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98472 |
| 98478 | 99479 | mYfIXP | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98478 |
| 98481 | 99482 | Iaabfy | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98481 |
| 98484 | 99485 | WiaCMc | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98484 |
| 98487 | 99488 | YsUuqv | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98487 |
| 98500 | 99501 | cbdjLc | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98500 |
| 98505 | 99506 | AkwBvx | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98505 |
| 98507 | 99508 | auWyCr | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98507 |
| 98511 | 99512 | msciKb | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98511 |
| 98516 | 99517 | WwuHey | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98516 |
| 98523 | 99524 | XgPEBW | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98523 |
| 98529 | 99530 | Kepoxz | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98529 |
| 98559 | 99560 | VpPdXE | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98559 |
| 98567 | 99568 | jnLRbG | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98567 |
| 98586 | 99587 | huByLm | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98586 |
| 98603 | 99604 | ftCEqP | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98603 |
| 98608 | 99609 | RnOgqk | salesman | 1 | 2021-04-04 | 2000.00 | 400.00 | 100 | 98608 |
+-------+-------+--------+----------+-----+------------+---------+--------+--------+-------+
40 rows in set (0.13 sec)
优化结果很明显,查询时间缩短了10倍多。
实践证明:
order by 后的字段(XXX)有索引 , sql 中有 limit 时,
- 当 select id 或 XXX字段索引包含字段时 ,显示 using index
- 当 select 后的字段含有 order by 字段, 索引不包含的字段时,将显示 using filesort
GROUP BY关键字优化
- group by实质是先排序后进行分组,遵照索引建的最佳左前缀
- 当无法使用索引列,增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
- where高于having,能写在where限定的条件就不要去having限定了。
去重优化
尽量不要使用 distinct 关键字去重。
能用group by 代替的可以用group by。