优化特定类型的查询
测试数据
CREATE TABLE forums
(
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
uid BIGINT DEFAULT '0' NOT NULL
COMMENT '发帖人ID',
title VARCHAR(100) DEFAULT '' NOT NULL
COMMENT '标题',
content MEDIUMTEXT NULL
COMMENT '内容',
create_ts BIGINT DEFAULT '0' NOT NULL
COMMENT '创建时间'
)COMMENT '帖子' ENGINE = InnoDB CHARSET = utf8mb4;
调用存储过程插入1000000万条数据
DELIMITER //
CREATE PROCEDURE insert_test_data_forums(IN loops INT)
BEGIN
DECLARE v1 INT;
set v1 = loops;
WHILE v1 > 0 DO
INSERT INTO forums(uid, title, content, create_ts)
VALUES (floor(rand() * 1000), 'test标题标题标题标题标题', 'test内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容', unix_timestamp() + floor(3600 * 24 * rand()));
set v1 = v1 -1;
END WHILE;
END;
//
DELIMITER ;
sql> CALL insert_test_data_forums(1000000)
[2018-03-10 13:03:50] 1 row affected in 3m 16s 82ms
一、count 查询
mysql> explain select count(*) from forums;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | forums | NULL | index | NULL | PRIMARY | 8 | NULL | 890283 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> select count(*) from forums;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (1.25 sec)
mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------+
| 1 | 0.01062300 | explain select count(*) from forums |
| 2 | 1.24545500 | select count(*) from forums |
+----------+------------+-------------------------------------+
2 rows in set, 1 warning (0.01 sec)
添加 uid 索引后重新执行count查询
mysql> alter table forums add index k_uid(uid);
Query OK, 0 rows affected (2.61 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show profiles;
+----------+------------+-----------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------+
| 1 | 0.01062300 | explain select count(*) from forums |
| 2 | 1.24545500 | select count(*) from forums |
| 3 | 2.60981400 | alter table forums add index k_uid(uid) |
+----------+------------+-----------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> explain select count(*) from forums;
+----+-------------+--------+------------+-------+---------------+-------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | forums | NULL | index | NULL | k_uid | 8 | NULL | 890283 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+-------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> select count(*) from forums;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.15 sec)
mysql> show profiles;
+----------+------------+-----------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------+
| 1 | 0.01062300 | explain select count(*) from forums |
| 2 | 1.24545500 | select count(*) from forums |
| 3 | 2.60981400 | alter table forums add index k_uid(uid) |
| 4 | 0.00021000 | explain select count(*) from forums |
| 5 | 0.15110800 | select count(*) from forums |
+----------+------------+-----------------------------------------+
5 rows in set, 1 warning (0.00 sec)
对比之后可以看到添加uid索引后，count查询 mysql优化器选择的是uid索引 使用force index强制使用索引，可以发现使用k_uid索引的效率要比使用主键索引要快的多。此部分原因需要学习mysql B+树结构才能明白。
mysql> select count(*) from forums force index(primary);
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (1.23 sec)
mysql> select count(*) from forums force index(k_uid);
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.15 sec)
再次插入400万条数据 对比同样的count查询的性能
mysql> call test.insert_test_data_forums(4000000);
Query OK, 1 row affected (31 min 57.31 sec)
上面插入的时间很慢，估计如果先删除索引k_uid 然后重建索引 会快很多
mysql> explain select count(*) from forums;
+----+-------------+--------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | forums | NULL | index | NULL | k_uid | 8 | NULL | 4451950 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> select count(*) from forums;
+----------+
| count(*) |
+----------+
| 5000000 |
+----------+
1 row in set (3.38 sec)
可以发现性能降低了20倍
二、limit查询
limit查询的问题：limit查询偏移量很大的时候，mysql会扫面很多行记录。
解决办法：通常有两种方式来解决limit查询的问题，第一种是使用索引覆盖扫描获取指定行再返回原表关联查询。另一种方法就是记录上一次偏移量的位置
mysql> explain select * from forums limit 300000,1;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | forums | NULL | ALL | NULL | NULL | NULL | NULL | 4451950 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from forums limit 300000,1;
1 row in set (0.90 sec)
可以看到次数mysql走的是全表扫描，用子查询延迟关联让mysql扫描更少的页面，再返回原表进行关联查询，大大提高性能。
mysql> explain select * from forums where id in (select id from forums limit 300000,1);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
mysql> explain select * from forums f inner join (select id from forums limit 300000,1) a on f.id = a.id ;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 300001 | 100.00 | NULL |
| 1 | PRIMARY | f | NULL | eq_ref | PRIMARY | PRIMARY | 8 | a.id | 1 | 100.00 | NULL |
| 2 | DERIVED | forums | NULL | index | NULL | k_uid | 8 | NULL | 4451950 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)
mysql> select * from forums f inner join (select id from forums limit 300000,1) a on f.id = a.id ;
1 row in set (0.07 sec)
三、Group By 和 disctinct
mysql处理group by 和 distinct 当无法使用索引的时候，使用临时表或者文件排序的方式来处理。
mysql> show create table actor;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| actor | CREATE TABLE `actor` (
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table film_actor;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| film_actor | CREATE TABLE `film_actor` (
`actor_id` smallint(5) unsigned NOT NULL,
`film_id` smallint(5) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`,`film_id`),
KEY `idx_fk_film_id` (`film_id`),
CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select first_name, last_name from actor inner join film_actor using(actor_id) group by first_name, last_name;
+----+-------------+------------+------------+------+---------------+---------+---------+-----------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-----------------------+------+----------+---------------------------------+
| 1 | SIMPLE | actor | NULL | ALL | PRIMARY | NULL | NULL | NULL | 200 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | film_actor | NULL | ref | PRIMARY | PRIMARY | 2 | sakila.actor.actor_id | 27 | 100.00 | Using index |
+----+-------------+------------+------------+------+---------------+---------+---------+-----------------------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> explain select first_name, last_name from actor inner join film_actor using(actor_id) group by actor_id;;
+----+-------------+------------+------------+-------+-----------------------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------------------+---------+---------+-----------------------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | index | PRIMARY,idx_actor_last_name | PRIMARY | 2 | NULL | 200 | 100.00 | NULL |
| 1 | SIMPLE | film_actor | NULL | ref | PRIMARY | PRIMARY | 2 | sakila.actor.actor_id | 27 | 100.00 | Using index |
+----+-------------+------------+------------+-------+-----------------------------+---------+---------+-----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
可以看到使用actor_id作为分组字段要比使用first_name,last_name作为分组字段的效率好的多
注意事项：使用group_by分组聚集select查询的字段一定要是分组的字段，不然查询的结果是不定的。很可惜没有模拟出来，不过此类查询可以通过设置mysql的sql_mode变量中的ONLY_FULL_GROUP_BY进行限制。
四、优化union查询
mysql针对union查询经常使用临时表的方式处理。针对union查询，注意两点：可以将where,limit,order by 字句下推到union大的各个子查询中，以便优化器可以充分优化；另外如果不需要消除重复的行，一定要使用union all 避免mysql给临时表加上distinct选项做唯一性检测。
