背景介绍
CREATE TABLE `video_info_100W` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`customer_id` int(11) DEFAULT NULL COMMENT '客户ID',
`transfer_status` tinyint(1) NOT NULL COMMENT ,
`domain_id` int(11) DEFAULT NULL COMMENT '域名ID',
`upload_time` int(11) DEFAULT NULL COMMENT '上传时间戳',
`create_user` varchar(255) NOT NULL COMMENT '创建者',
`create_time` int(11) DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `zj` (`create_user`,`transfer_status`,`domain_id`,`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2048561 DEFAULT CHARSET=utf8;
SELECT
*
FROM
video_info_100W
WHERE
create_user IN ('1', '2')
AND transfer_status IN (1, 2, 3)
AND domain_id IN (1, 2, 3)
ORDER BY
id DESC
LIMIT 0, 150;
mysql> explain select * from video_info_100W where create_user in ('1','2') and transfer_status in(1,2,3) and domain_id in (1,2,3) order by id desc limit 0 ,150;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | video_info_100W | index | zj | PRIMARY | 4 | NULL | 150 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
在possible_keys中的zj索引，最终并没有被选中
排查
初步怀疑是in搞的鬼
参考材料：http://myrock.github.io/2014/09/24/in-and-range/
- 得知参数eq_range_index_dive_limit，以及mysql5.6,5.7版本的默认值分别为20，100。（我们当前使用的5.6版本）
- index dive 与 index statistics的区别，前者速度慢但估算的行数准确，后者速度快但估算的行数不准。在数据分布均匀的情况下，后者计算的数据相对准确，但大部分情况下数据分布都是不均匀的。
官方手册中说明：
The eq_range_index_dive_limit system variable enables you to configure the number of values at which the optimizer switches from one row estimation strategy to the other. To disable use of statistics and always use index dives, set eq_range_index_dive_limit to 0. To permit use of index dives for comparisons of up to N equality ranges, set eq_range_index_dive_limit to N + 1.
eq_range_index_dive_limit is available as of MySQL 5.6.5. Before 5.6.5, the optimizer uses index dives, which is equivalent to eq_range_index_dive_limit=0.
limit=0 -->只使用dive
0<N<limit -->使用dive
limit<=N -->使用statistics
通过optimize trace查看
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "zj",
"ranges": [
"1 <= create_user <= 1 AND 1 <= transfer_status <= 1 AND 1 <= domain_id <= 1",
"1 <= create_user <= 1 AND 1 <= transfer_status <= 1 AND 2 <= domain_id <= 2",
"1 <= create_user <= 1 AND 1 <= transfer_status <= 1 AND 3 <= domain_id <= 3",
"1 <= create_user <= 1 AND 2 <= transfer_status <= 2 AND 1 <= domain_id <= 1",
"1 <= create_user <= 1 AND 2 <= transfer_status <= 2 AND 2 <= domain_id <= 2",
"1 <= create_user <= 1 AND 2 <= transfer_status <= 2 AND 3 <= domain_id <= 3",
"1 <= create_user <= 1 AND 3 <= transfer_status <= 3 AND 1 <= domain_id <= 1",
"1 <= create_user <= 1 AND 3 <= transfer_status <= 3 AND 2 <= domain_id <= 2",
"1 <= create_user <= 1 AND 3 <= transfer_status <= 3 AND 3 <= domain_id <= 3",
"2 <= create_user <= 2 AND 1 <= transfer_status <= 1 AND 1 <= domain_id <= 1",
"2 <= create_user <= 2 AND 1 <= transfer_status <= 1 AND 2 <= domain_id <= 2",
"2 <= create_user <= 2 AND 1 <= transfer_status <= 1 AND 3 <= domain_id <= 3",
"2 <= create_user <= 2 AND 2 <= transfer_status <= 2 AND 1 <= domain_id <= 1",
"2 <= create_user <= 2 AND 2 <= transfer_status <= 2 AND 2 <= domain_id <= 2",
"2 <= create_user <= 2 AND 2 <= transfer_status <= 2 AND 3 <= domain_id <= 3",
"2 <= create_user <= 2 AND 3 <= transfer_status <= 3 AND 1 <= domain_id <= 1",
"2 <= create_user <= 2 AND 3 <= transfer_status <= 3 AND 2 <= domain_id <= 2",
"2 <= create_user <= 2 AND 3 <= transfer_status <= 3 AND 3 <= domain_id <= 3"
],
"index_dives_for_eq_ranges": false,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 17357490,
"cost": 2.08e7,
"chosen": false,
"cause": "cost"
}
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`video_info_100W`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 1.93e6,
"cost": 457274,
"chosen": true
}
]
},
"cost_for_plan": 457274,
"rows_for_plan": 1.93e6,
"chosen": true
}
]
很明显可以看到index zj cost=2.08e7 > table scan cost=457274，这是因为eq_range_index_dive_limit=10，而ranges中有323=18>10，使用了statitics的方式估算行数，cost也就跟着不准。
解决方案
- 调大eq_range_index_dive_limit的值，这个需要根据业务场景提前判断组合的最大值
- 强制使用index zj，应该尽量不使用这种特殊的sql写法（例如索引如果删除了，执行时会报错），将优化的工作还是交给mysql来做
SELECT * FROM video_info_100W force index(zj) WHERE create_user IN ('1', '2') AND transfer_status IN (1, 2, 3) AND domain_id IN (1, 2, 3) ORDER BY id DESC LIMIT 0, 150; - 中断ranges组合（这个是我自己取的名字~~）
创建字典表
CREATE TABLE `dict` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(255) NOT NULL,
`value` int(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
改写sql
SELECT
*
FROM
video_info_100W FORCE INDEX (zj)
WHERE
create_user IN ('1', '2')
AND transfer_status IN (
SELECT
VALUE
FROM
dict
WHERE
CODE = 1
)
AND domain_id IN (1, 2)
ORDER BY
id DESC
LIMIT 0,150;
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "zj",
"ranges": [
"1 <= create_user <= 1",
"2 <= create_user <= 2"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 2,
"cost": 4.41,
"chosen": true
}
]
mysql> explain select * from video_info_100W where create_user in ('1','2') and transfer_status in(select value from dict where code=1) and domain_id in (1,2) order by id desc limit 0 ,150;
+----+-------------+-----------------+-------+---------------+------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | video_info_100W | range | zj | zj | 767 | NULL | 2 | Using index condition; Using filesort |
| 1 | SIMPLE | dict | ALL | NULL | NULL | NULL | NULL | 12 | Using where; FirstMatch(video_info_100W) |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+------------------------------------------+
FAQ
- create_user能不能也通过变为子查询的方式进行优化？--不行
- create_user是可变参数，不像transfer_status是基本不变的一些值
- 如果create_user改为in select 这种方式将不会使用range方式，而变为ALL类型，效率最低
- domain_id如果数量很多，会不会有影响？--不会
zj(create_user,transfer_status,domain_id,create_time) ，组合索引左匹配原则，在transfer_status字段已经中断了ranges组合方式，不会再增加组合，会直接命中index zj
业务场景优化
- 主账号登录时查询该客户下所有用户的视频信息，涉及customer_id
1. alter table video_info add index idx_customerId(customer_id)
2. select * from video_info where customer_id = xxx;
- 子账号登录时，只能查询该用户下的视频信息，涉及create_user
1. alter table video_info add index idx_createUser(create_user)
2. select * from video_info where create_user = xxx;
- 查询可选条件：transfer_status（单选）, domain_id（单选）,create_time（范围）
1. alter table video_info add index idx_transferStatus(transfer_status)
2. alter table video_info add index idx_domainId(domain_id)
3. alter table video_info add index idx_createTime(create_time)
1. select * from video_info where create_user = xxx and transfer_status = xxx;
2. select * from video_info where create_user = xxx and domain_id = xxx;
3. select * from video_info where create_user = xxx and create_time >= xxx and create_time <= xxx;
4. select * from video_info where create_user = xxx and transfer_status = xxx and create_time >= xxx;
组合查询条件，若存在符合where条件中的数据，则会使用create_user索引过滤出集合，再从集合中查找符合条件的数据，如果补步过滤出的数据量太大，查询性能也是很差，这时候就得考虑使用组合索引。
若where条件中的某一项可以快速的定位数据量，则会使用该项的索引。例如sql4，如果db中的transfer_status没有=xxx的记录，那么就会使用idx_transferStatus。
3242
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
