mysql优化
干扰优化器选择索引
准备
create table tb1(
id int not null auto_increment primary key,
code int not null,
name varchar(10) not null default '',
`type` varchar(1),
unique key uni_code (code,`type`)
) engine=innodb;
CREATE TABLE `x1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`x` bigint(20) DEFAULT NULL,
`fk` varchar(10) NOT NULL DEFAULT '' COMMENT 'fk',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_x1` (`x`)
) ENGINE=InnoDB;
index hint
- reference: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
- 关键字：
USE INDEX、IGNORE INDEX、FORCE INDEX
FORCE INDEX 强制使用指定索引
SELECT * FROM TB1 force index(uni_code) where code > 100 and code < 1000 order by id;
IGNORE INDEX 忽略指定索引（MYSQL不一定会使用按提示来做决策）
SELECT * FROM TB1 ignore index(uni_code) where code > 0 and code < 1000 order by id;
USE INDEX 使用指定索引（MYSQL不一定会使用按提示来做决策）
SELECT * FROM TB1 use index(uni_code) where code > 0 and code < 1000 order by id;
join order hint
- JOIN_FIXED_ORDER
固定顺序，顾名思义。join顺序完全按照编写sql的顺序去执行。括弧不能丢。使用时写JOIN_FIXED_ORDER
()，类似STRAIGHT_JOIN
explain select /*+ JOIN_FIXED_ORDER()*/ * from tb1 JOIN x1 on tb1.code = x1.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------+
| 1 | SIMPLE | tb1 | <null> | ALL | uni_code | <null> | <null> | <null> | 4 | 100.0 | <null> |
| 1 | SIMPLE | x1 | <null> | eq_ref | PRIMARY | PRIMARY | 4 | jay.tb1.code | 1 | 100.0 | <null> |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------+
explain select /*+ JOIN_FIXED_ORDER()*/ * from x1 JOIN tb1 on tb1.code = x1.id;
+----+-------------+-------+------------+------+---------------+----------+---------+-----------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------+------+----------+--------+
| 1 | SIMPLE | x1 | <null> | ALL | PRIMARY | <null> | <null> | <null> | 3 | 100.0 | <null> |
| 1 | SIMPLE | tb1 | <null> | ref | uni_code | uni_code | 4 | jay.x1.id | 1 | 100.0 | <null> |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------+------+----------+--------+
-
JOIN_ORDER JOIN顺序只影响括弧里的表。但MYSQL不一定会使用按提示来做决策。 举个例子：如驱动表的查询类型为CONST时就不会按指定的顺序来走
-
JOIN_PREFIX 指定第一个表（但优化器并不一定按这个顺序执行。举个例子，在后面加个where tb1.code=1）
-
JOIN_SUFFIX 指定最后一个表（但优化器并不一定按这个顺序执行。）
explain select /*+ JOIN_ORDER(x1, tb1)*/ * from tb1 JOIN x1 on tb1.code = x1.id;
-- 按预期顺序执行
+----+-------------+-------+------------+------+---------------+----------+---------+-----------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------+------+----------+--------+
| 1 | SIMPLE | x1 | <null> | ALL | PRIMARY | <null> | <null> | <null> | 3 | 100.0 | <null> |
| 1 | SIMPLE | tb1 | <null> | ref | uni_code | uni_code | 4 | jay.x1.id | 1 | 100.0 | <null> |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------+------+----------+--------+
2 rows in set
Time: 0.018s
-- 未按预期顺序执行
explain select /*+ JOIN_ORDER(tb1,x1)*/ * from tb1 JOIN x1 on tb1.code = x1.id where tb1.code= 1;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+--------+
| 1 | SIMPLE | x1 | <null> | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | <null> |
| 1 | SIMPLE | tb1 | <null> | ref | uni_code | uni_code | 4 | const | 1 | 100.0 | <null> |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+--------+
-- 按预期顺序执行，多了一个where
explain select /*+ JOIN_PREFIX(x1)*/ * from tb1 JOIN x1 on tb1.code = x1.id;
+----+-------------+-------+------------+------+---------------+----------+---------+-----------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------+------+----------+--------+
| 1 | SIMPLE | x1 | <null> | ALL | PRIMARY | <null> | <null> | <null> | 3 | 100.0 | <null> |
| 1 | SIMPLE | tb1 | <null> | ref | uni_code | uni_code | 4 | jay.x1.id | 1 | 100.0 | <null> |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------+------+----------+--------+
2 rows in set
Time: 0.013s
-- 按预期顺序执行
explain select /*+ JOIN_SUFFIX(x1)*/ * from tb1 JOIN x1 on tb1.code = x1.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------+
| 1 | SIMPLE | tb1 | <null> | ALL | uni_code | <null> | <null> | <null> | 4 | 100.0 | <null> |
| 1 | SIMPLE | x1 | <null> | eq_ref | PRIMARY | PRIMARY | 4 | jay.tb1.code | 1 | 100.0 | <null> |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------+
特殊玩法
空运算
场景说明：对于一些希望走uni_code索引的场景，缺走到了主键索引。比如下面这条sql完全有可能因为根据code扫描的数据量过大，优化器发现需要根据id排序，直接选择根据id扫描
select * from tb1 where code between 1000 and 100000 order by id+0;
特殊join
STRAIGHT_JOIN是和JOIN_FIXED_ORDER类似
explain select * from x1 STRAIGHT_JOIN tb1 on tb1.code = x1.id where tb1.code=1;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+--------+
| 1 | SIMPLE | x1 | <null> | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | <null> |
| 1 | SIMPLE | tb1 | <null> | ref | uni_code | uni_code | 4 | const | 1 | 100.0 | <null> |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+--------+
explain select * from tb1 STRAIGHT_JOIN x1 on tb1.code = x1.id where tb1.code=1;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+--------+
| 1 | SIMPLE | tb1 | <null> | ref | uni_code | uni_code | 4 | const | 1 | 100.0 | <null> |
| 1 | SIMPLE | x1 | <null> | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | <null> |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+--------+
隐式转换
☆不要搞隐式转换就对了
in查询
基于PK查询时，隐式转换没有问题（mysql版本5.7、5.8），但二级索引会转换错误导致全表扫描
-- 基于ID查询
explain select * from x1 where id in ("1","2","3",'4','5');
+----+-------------+-------+------------+-------+---------------+---------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------+------+----------+-------------+
| 1 | SIMPLE | x1 | <null> | range | PRIMARY | PRIMARY | 4 | <null> | 5 | 100.0 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------+------+----------+-------------+
-- 基于二级索引
explain select * from x1 where x in ("1","2","3",'4','5');
+----+-------------+-------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| 1 | SIMPLE | x1 | <null> | ALL | uni_x1 | <null> | <null> | <null> | 15 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
join查询
两个表关联时关联字段的类型不一致（用二级索引测试）
子查询 hit
- 祸害人间的东西。hint个啥。不用。
- 子查询在执行explain语句查看执行计划时也要真正去执行查询。危险动作
来源：5.7文档
参考
| Hint Name | Description | Applicable Scopes |
|---|---|---|
| BKA, NO_BKA | Affects Batched Key Access join processing | Query block, table |
| BNL, NO_BNL | Affects Block Nested-Loop join processing | Query block, table |
| MAX_EXECUTION_TIME | Limits statement execution time | Global |
| MRR, NO_MRR | Affects Multi-Range Read optimization | Table, index |
| NO_ICP | Affects Index Condition Pushdown optimization | Table, index |
| NO_RANGE_OPTIMIZATION | Affects range optimization | Table, index |
| QB_NAME | Assigns name to query block | Query block |
| SEMIJOIN, NO_SEMIJOIN | Affects semijoin strategies | Query block |
| SUBQUERY | Affects materialization, IN-to-EXISTS subquery stratgies | Query block |
来源：5.8文档
| Hint Name | Description | Applicable Scopes |
|---|---|---|
| BKA, NO_BKA | Affects Batched Key Access join processing | Query block, table |
| BNL, NO_BNL | Affects Block Nested-Loop join processing | Query block, table |
| HASH_JOIN, NO_HASH_JOIN | Affects Hash Join optimization | Query block, table |
| INDEX_MERGE, NO_INDEX_MERGE | Affects Index Merge optimization | Table, index |
| JOIN_FIXED_ORDER | Use table order specified in FROM clause for join order | Query block |
| JOIN_ORDER | Use table order specified in hint for join order | Query block |
| JOIN_PREFIX | Use table order specified in hint for first tables of join order | Query block |
| JOIN_SUFFIX | Use table order specified in hint for last tables of join order | Query block |
| MAX_EXECUTION_TIME | Limits statement execution time | Global |
| MERGE, NO_MERGE | Affects derived table/view merging into outer query block | Table |
| MRR, NO_MRR | Affects Multi-Range Read optimization | Table, index |
| NO_ICP | Affects Index Condition Pushdown optimization | Table, index |
| NO_RANGE_OPTIMIZATION | Affects range optimization | Table, index |
| QB_NAME | Assigns name to query block | Query block |
| RESOURCE_GROUP | Set resource group during statement execution | Global |
| SEMIJOIN, NO_SEMIJOIN | Affects semijoin strategies; beginning with MySQL 8.0.17, this also applies to antijoins | Query block |
| SKIP_SCAN, NO_SKIP_SCAN | Affects Skip Scan optimization | Table, index |
| SET_VAR | Set variable during statement execution | Global |
| SUBQUERY | Affects materialization, IN-to-EXISTS subquery stratgies | Query block |
https://www.slideshare.net/oysteing/using-optimizer-hints-to-improve-mysql-query-performance
http://jorgenloland.blogspot.com/2011/10/optimizer-tracing-query-execution-plan.html
登博:https://www.slideshare.net/frogd/presentations
最后修改于 2019-05-08