1000万行数据的测试表
sysbench oltp_common --mysql-socket=/home/mingjie.gmj/databases/data/mydata5470/tmp/mysql.sock --mysql-user=root --mysql-db=idxdb --db-driver=mysql --tables=4 --table-size=10000000 --threads=128 prepare
alter table sbtest1 add (t1 int default 0);
update sbtest1 set t1=FLOOR(RAND() * 1000000);
alter table sbtest1 add (t2 int default 0);
update sbtest1 set t2=FLOOR(RAND() * 1000000);
alter table sbtest1 add (qad varchar(32) default '');
update sbtest1 set qad=MD5(RAND());
alter table sbtest1 add index (k,t1,t2,qad);
alter table sbtest1 add index (k,t2,qad);
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`t1` int(11) DEFAULT '0',
`t2` int(11) DEFAULT '0',
`qad` varchar(32) DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`),
KEY `k` (`k`,`t1`,`t2`,`qad`),
KEY `k_2` (`k`,`t2`,`qad`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4
CREATE TABLE `sbtest2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_2` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4
CREATE TABLE `sbtest3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_3` (`k`),
KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4
alter table sbtest4 add column (t1 int not null default 0);
alter table sbtest4 add index (k,t1);
update sbtest4 set t1=FLOOR(RAND() * 1000000);
alter table sbtest4 add column (qad varchar(32) not null default '');
alter table sbtest4 add index (k,t1,qad);
update sbtest4 set qad=MD5(RAND());
CREATE TABLE `sbtest4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`t1` int(11) NOT NULL DEFAULT '0',
`qad` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_4` (`k`),
KEY `k` (`k`,`t1`),
KEY `k_2` (`k`,`t1`,`qad`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表
mysql> explain select max(k) from sbtest1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Select tables optimized away
mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。
mysql> explain select * from (select * from sbtest1 where id=10000) sb\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select '10000' AS `id`,'5032908' AS `k`,'16518908930-89069434581-00330047360-80153545765-52786063008-17719135532-97186968876-24800122573-76373447456-17506809060' AS `c`,'42734141361-53867137877-50446368300-00647311019-92369579010' AS `pad`,'755900' AS `t1`,'103761' AS `t2`,'b13976c059dafb164fbd0543fbc5754d' AS `qad` from `idxdb`.`sbtest1` where 1
(用到唯一索引的连接,性能很好)
primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了,简单的 select 查询不会出现这种 type。
explain select * from sbtest1 a left join sbtest2 b on a.id=b.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9565215
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: idxdb.a.id
rows: 1
filtered: 100.00
Extra: NULL
相比 eq_ref
,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。
简单查询出多行
mysql> explain select * from sbtest1 where k=4983851\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: ref
possible_keys: k_1,k
key: k_1
key_len: 4
ref: const
rows: 94
filtered: 100.00
Extra: NULL
一行能连多行
explain select * from sbtest1 a left join sbtest2 b on a.id=b.k\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9565215
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: k_2
key: k_2
key_len: 4
ref: idxdb.a.id
rows: 5
filtered: 100.00
Extra: NULL
表示使用了索引合并的优化方法,耗费大量CPU资源
mysql> explain select * from sbtest3 where k=100000 or c='asd'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest3
partitions: NULL
type: index_merge
possible_keys: k_3,c
key: k_3,c
key_len: 4,480
ref: NULL
rows: 2
filtered: 100.00
Extra: Using union(k_3,c); Using where
范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
mysql> explain select * from sbtest1 where id>5000000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 4782607
filtered: 100.00
Extra: Using where
和ALL一样,不同就是mysql只需扫描索引树,这通常比ALL快一些。
mysql> explain select count(*) from sbtest1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: index
possible_keys: NULL
key: k_1
key_len: 4
ref: NULL
rows: 9565215
filtered: 100.00
Extra: Using index
key_len计算规则如下:
索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`t1` int(11) DEFAULT '0',
`t2` int(11) DEFAULT '0',
`qad` varchar(32) DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`),
KEY `k` (`k`,`t1`,`t2`,`qad`)
)
explain select * from sbtest1 force index(k)
where k=4983851 and t1=654542 and t2=670862 and qad='b6616ae504d039e04c54882ddcfc28ff';
+------+---------+-------------------------+------+----------+-------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------------+------+----------+-------+
| k | 145 | const,const,const,const | 1 | 100.00 | NULL |
+------+---------+-------------------------+------+----------+-------+
intNotNull 4(not null)
intNotNull int 9 = 4 + 4 + 1(非空标示)
intNotNull int int 14 = 9 + 4 + 1 (非空标示)
intNotNull int int vchar(32) 145 = 14 + 32*4(mb4要*4) + 2(变长+2定长不加)+ 1(非空标示)
CREATE TABLE `sbtest4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`t1` int(11) NOT NULL DEFAULT '0',
`qad` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_4` (`k`),
KEY `k` (`k`,`t1`),
KEY `k_2` (`k`,`t1`,`qad`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4
explain select * from sbtest4 force index(k_2)
where k=4983851 and t1=654542 and qad='b6616ae504d039e04c54882ddcfc28ff';
+------+---------+-------------------+------+----------+-------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------+------+----------+-------+
| k_2 | 138 | const,const,const | 1 | 100.00 | NULL |
+------+---------+-------------------+------+----------+-------+
138 = 4 + 4 + 130(mb4需要乘4然后加上2变长:32*4+2)
Using index
:这发生在对表的请求列都是同一索引的部分的时候,返回的列数据只使用了索引中的信息,而没有再去访问表中的行记录。是性能高的表现。
Using where
:mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据,再按 where 条件进行检查,符合就留下,不符合就丢弃。
Using temporary
:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引来优化。
Using filesort
:mysql 会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
组合索引k:k, t1, t2, qad
KEY `k` (`k`,`t1`,`t2`,`qad`)
mysql> explain select * from sbtest1 force index(k) where k in (4983851,4983852) and t1=654542\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 9
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index condition
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1 in(654542,654543) and t2=670862\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 14
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index condition
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1 = 654542 and t2 in (670862, 670863)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 14
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index condition
mysql> explain select * from sbtest1 force index(k) where k in (4983851,4983851) and t1 = 654542 and t2 in (670862, 670863)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 14
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index condition
explain select * from sbtest1 force index(k) where k=4983851 and t1 = 654542 and t2 =670862 order by qad\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: ref
possible_keys: k
key: k
key_len: 14
ref: const,const,const
rows: 1
filtered: 100.00
Extra: Using index condition
key_len14三个字段都走了
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1 = 654542 order by t2,qad\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: ref
possible_keys: k
key: k
key_len: 9
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index condition
key_len9两个字段都走了,排序两列
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1 in(654542,654543) and t2=670862 order by qad\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 14
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index condition; Using filesort
把in列从索引中踢掉
alter table sbtest1 add index (k,t2,qad);
再进行测试,完美解决
mysql> explain select * from sbtest1 force index(k_2) where k=4983851 and t1 in(654542,654543) and t2=670862 order by qad\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: ref
possible_keys: k_2
key: k_2
key_len: 9
ref: const,const
rows: 1
filtered: 20.00
Extra: Using index condition; Using where
组合索引k:k, t1, t2, qad
KEY `k` (`k`,`t1`,`t2`,`qad`)
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1>654542 and t2=670862\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 9
ref: NULL
rows: 31
filtered: 10.00
Extra: Using index condition
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1=654542 and t2>670862\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 14
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1=654542 and t2>670862 order by t2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 14
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`t1` int(11) NOT NULL,
`t2` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `k` (`k`,`t1`,`t2`,`pad`)
)
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1=654542 and t2>670862 order by t2,pad\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 12
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1=654542 and t2>670862 order by qad\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 14
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition; Using filesort
mysql> explain select * from sbtest1 force index(k) where k=4983851 and t1=654542 order by t2,qad\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: ref
possible_keys: k
key: k
key_len: 9
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index condition
-- s1的(`k`,`t1`,`t2`,`qad`)只能走k
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(k)
on s2.id=s1.t1
where s2.k=5012066 and s1.k in (5002170,5002170) and s1.t2=738425;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: s2
partitions: NULL
type: ref
possible_keys: PRIMARY,k_2
key: k_2
key_len: 4
ref: const
rows: 111
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s1
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 4
ref: NULL
rows: 106
filtered: 0.05
Extra: Using index condition; Using where; Using join buffer (Block Nested Loop)
-- s1的(`k`,`t1`,`t2`,`qad`)能走k和t1
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(k)
on s2.id=s1.t1
where s2.k=5012066 and s1.k = 5002170 and s1.t2 in (738425,738425);
+------+---------+-------------------+------+----------+-----------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------+------+----------+-----------------------+
| k_2 | 4 | const | 111 | 100.00 | NULL |
| k | 9 | const,idxdb.s2.id | 1 | 20.00 | Using index condition |
+------+---------+-------------------+------+----------+-----------------------+
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`),
KEY `idx_sbtest1_c_pad` (`c`,`pad`)
)
explain select * from sbtest1
where c like '68487932199-96439406143-93774651418-41631865787-96406072701-20604855487%'
and pad>='22195207048-70116052123-74140395089-76317954521-98694025897';
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | sbtest1 | NULL | range | idx_sbtest1_c_pad | idx_sbtest1_c_pad | 180 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
可以改写为inner join或逗号连接加where
mysql> explain select * from sbtest1 where id in ( select max(id) from sbtest1 where k=4983851 group by t1 )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: sbtest1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9565215
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: sbtest1
partitions: NULL
type: ref
possible_keys: k_1,k,k_2
key: k
key_len: 4
ref: const
rows: 94
filtered: 100.00
Extra: Using where; Using index
-- sbtest1:
-- KEY `k` (`k`,`t1`,`t2`,`qad`),
-- KEY `t1` (`t1`,`t2`,`k`)
-- KEY `t1_2` (`t1`,`k`,`t2`)
-- (`k`,`t1`,`t2`,`qad`)走`k`和`t1`覆盖`t2`
explain select max(t2) from sbtest1 force index(k)
where k=4983851 and t1=654542 group by t2;
-+------+---------+-------------+------+----------+--------------------------+
| key | key_len | ref | rows | filtered | Extra |
-+------+---------+-------------+------+----------+--------------------------+
| k | 9 | const,const | 1 | 100.00 | Using where; Using index |
-+------+---------+-------------+------+----------+--------------------------+ref
-- (`t1`,`t2`,`k`)走`t1`覆盖`t2`
explain select max(t2) from sbtest1 force index(t1)
where k=4983851 and t1=654542 group by t2;
+------+---------+-------+------+----------+--------------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------+------+----------+--------------------------+
| t1 | 5 | const | 18 | 0.28 | Using where; Using index |
+------+---------+-------+------+----------+--------------------------+ref
-- (`t1`,`k`,`t2`)走`t1`和`k`覆盖`t2`
explain select max(t2) from sbtest1 force index(t1_2)
where k=4983851 and t1=654542 group by t2;
+------+---------+-------------+------+----------+--------------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------+------+----------+--------------------------+
| t1_2 | 9 | const,const | 1 | 100.00 | Using where; Using index |
+------+---------+-------------+------+----------+--------------------------+ref
(leftjoin在数据量相同的情况下会优化为join)
结论:
-- s2: KEY `k_2` (`k`)
-- s1:
-- KEY `k` (`k`,`t1`,`t2`,`qad`),
-- KEY `t1` (`t1`,`t2`,`k`)
-- KEY `t1_2` (`t1`,`k`,`t2`)
-- s1的(`k`,`t1`,`t2`,`qad`)走`k`和`t1`
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(k)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170;
+------+---------+-------------------+------+
| key | key_len | ref | rows |
+------+---------+-------------------+------+
| k_2 | 4 | const | 111 |
| k | 9 | const,idxdb.s2.id | 1 |
+------+---------+-------------------+------+
-- s1的(`k`,`t1`,`t2`,`qad`)走`k`和`t1`和`t2`
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(k)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170 and s1.t2=738425;
+------+---------+-------------------------+------+
| key | key_len | ref | rows |
+------+---------+-------------------------+------+
| k_2 | 4 | const | 111 |
| k | 14 | const,idxdb.s2.id,const | 1 |
+------+---------+-------------------------+------+
-- s1的(`t1`,`t2`,`k`)只能走`t1`
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(t1)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170;
+------+---------+-------------+------+
| key | key_len | ref | rows |
+------+---------+-------------+------+
| k_2 | 4 | const | 111 |
| t1 | 5 | idxdb.s2.id | 9 |
+------+---------+-------------+------+
-- s1的(`t1`,`t2`,`k`)能走`t1`,`t2`,`k`
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(t1)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170 and s1.t2=738425;
+------+---------+-------------------------+------+
| key | key_len | ref | rows |
+------+---------+-------------------------+------+
| k_2 | 4 | const | 111 |
| t1 | 14 | idxdb.s2.id,const,const | 1 |
+------+---------+-------------------------+------+
-- s1的(`t1`,`k`,`t2`)可以用`t1`,`k`
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(t1_2)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170;
+------+---------+-------------------+------+
| key | key_len | ref | rows |
+------+---------+-------------------+------+
| k_2 | 4 | const | 111 |
| t1_2 | 9 | idxdb.s2.id,const | 1 |
+------+---------+-------------------+------+
-- s1的(`t1`,`k`,`t2`)可以用`t1`,`k`,`t2`
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(t1_2)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170 and s1.t2=738425;
+------+---------+-------------------------+------+
| key | key_len | ref | rows |
+------+---------+-------------------------+------+
| k_2 | 4 | const | 111 |
| t1_2 | 14 | idxdb.s2.id,const,const | 1 |
+------+---------+-------------------------+------+
小结
测试过程
Order by(后表)列
-- s2: KEY `k_2` (`k`)
-- s1:
-- KEY `k` (`k`,`t1`,`t2`,`qad`),
-- KEY `t1` (`t1`,`t2`,`k`)
-- KEY `t1_2` (`t1`,`k`,`t2`)
-- s1的(`k`,`t1`,`t2`,`qad`)走`k`和`t1`
-- order by 能用k排序,因为有k等值
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(k)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170
order by s1.k;
+------+---------+-------------------+------+----------+-------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------+------+----------+-------+
| k_2 | 4 | const | 111 | 100.00 | NULL |
| k | 9 | const,idxdb.s2.id | 1 | 100.00 | NULL |
+------+---------+-------------------+------+----------+-------+
-- s1的(`k`,`t1`,`t2`,`qad`)走`k`和`t1`
-- order by 不能用t1排序
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(k)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170
order by s1.t1;
+------+---------+-------------------+------+----------+---------------------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------+------+----------+---------------------------------+
| k_2 | 4 | const | 111 | 100.00 | Using temporary; Using filesort |
| k | 9 | const,idxdb.s2.id | 1 | 100.00 | NULL |
+------+---------+-------------------+------+----------+---------------------------------+
-- s1的(`k`,`t1`,`t2`,`qad`)走`k`和`t1`和`t2`
-- order by 能用t2排序因为t2有单值
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(k)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170 and s1.t2=738425
order by s1.t2;
+------+---------+-------------------------+------+----------+-------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------------+------+----------+-------+
| k_2 | 4 | const | 111 | 100.00 | NULL |
| k | 14 | const,idxdb.s2.id,const | 1 | 100.00 | NULL |
+------+---------+-------------------------+------+----------+-------+
-- s1的(`k`,`t1`,`t2`,`qad`)走`k`和`t1`
-- order by t2不能走索引因为t2没有单值条件
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(k)
on s2.id=s1.t1
where s2.k=5012066 and s1.k=5002170
order by s1.t2;
+------+---------+-------------------+------+----------+---------------------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------+------+----------+---------------------------------+
| k_2 | 4 | const | 111 | 100.00 | Using temporary; Using filesort |
| k | 9 | const,idxdb.s2.id | 1 | 100.00 | NULL |
+------+---------+-------------------+------+----------+---------------------------------+
-- s1的(`t1`,`t2`,`k`)只走t1不排序
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(t1)
on s2.id=s1.t1
where s2.k=5012066 and s1.t2=738425
order by s1.t1;
+------+---------+-------------+------+----------+---------------------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------+------+----------+---------------------------------+
| k_2 | 4 | const | 111 | 100.00 | Using temporary; Using filesort |
| t1 | 5 | idxdb.s2.id | 9 | 0.53 | Using index condition |
+------+---------+-------------+------+----------+---------------------------------+
-- s1的(`t1`,`t2`,`k`)走`t1`和`t2`不排序
explain
select * from sbtest2 s2
straight_join sbtest1 s1 force index(t1)
on s2.id=s1.t1
where s2.k=5012066 and s1.t2=738425
order by s1.t1;
+------+---------+-------------------+------+----------+---------------------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------+------+----------+---------------------------------+
| k_2 | 4 | const | 111 | 100.00 | Using temporary; Using filesort |
| t1 | 10 | idxdb.s2.id,const | 1 | 100.00 | NULL |
+------+---------+-------------------+------+----------+---------------------------------+
Order by(前表)列
-- s2: KEY `k_2` (`k`)
-- s1:
-- KEY `k` (`k`,`t1`,`t2`,`qad`),
-- KEY `t1` (`t1`,`t2`,`k`)
-- KEY `t1_2` (`t1`,`k`,`t2`)
-- s1的(`t1`,`t2`,`k`)不走
explain
select * from sbtest1 s1 force index(t1)
straight_join sbtest2 s2
on s1.t1=s2.k
order by s1.t2;
+------+---------+-------------+---------+----------+-----------------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------+---------+----------+-----------------------------+
| NULL | NULL | NULL | 9565215 | 100.00 | Using where; Using filesort |
| k_2 | 4 | idxdb.s1.t1 | 5 | 100.00 | NULL |
+------+---------+-------------+---------+----------+-----------------------------+
-- s1的(`t1`,`t2`,`k`)走t1和t2可以避免排序!
explain
select * from sbtest1 s1 force index(t1)
straight_join sbtest2 s2
on s1.t2=s2.k
order by s1.t1;
+------+---------+-------------+---------+----------+-------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------+---------+----------+-------------+
| t1 | 14 | NULL | 9565215 | 100.00 | Using where |
| k_2 | 4 | idxdb.s1.t2 | 5 | 100.00 | NULL |
+------+---------+-------------+---------+----------+-------------+
-- s1的KEY `t1_3` (`t1`)
explain
select * from sbtest1 s1 force index(t1_3)
straight_join sbtest2 s2
on s1.t2=s2.k
order by s1.t1;
+------+---------+-------------+---------+----------+-------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------+---------+----------+-------------+
| t1_3 | 5 | NULL | 9565215 | 100.00 | Using where |
| k_2 | 4 | idxdb.s1.t2 | 5 | 100.00 | NULL |
+------+---------+-------------+---------+----------+-------------+
-- s1的(`t1`,`t2`,`k`)
explain
select * from sbtest1 s1 force index(t1)
straight_join sbtest2 s2
on s1.t1=s2.k
order by s1.t1;
+------+---------+-------------+---------+----------+-------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------+---------+----------+-------------+
| t1 | 14 | NULL | 9565215 | 100.00 | Using where |
| k_2 | 4 | idxdb.s1.t1 | 5 | 100.00 | NULL |
+------+---------+-------------+---------+----------+-------------+
-- (`k`,`t1`,`t2`,`qad`),
-- order by s1.t1 可以索引排序:先用索引正常排序,完了在连接
-- order by s1.t2 不可以排序
-- order by s1.k 可以索引排序:按连接键直接排序!
-- 对于驱动表来说,连接键没什么用,只需要关注索引覆盖where的情况
explain
select * from sbtest1 s1 force index(k)
straight_join sbtest2 s2
on s1.t2=s2.k
where s1.k=5012066
order by s1.t1;
+------+---------+-------------+------+----------+-----------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------+------+----------+-----------------------+
| k | 4 | const | 116 | 100.00 | Using index condition |
| k_2 | 4 | idxdb.s1.t2 | 5 | 100.00 | NULL |
+------+---------+-------------+------+----------+-----------------------+
几句话总结:
keylen的长度不算排序列!
sysbench oltp_common --mysql-socket=/home/mingjie.gmj/databases/data/mydata5470/tmp/mysql.sock --mysql-user=root --mysql-db=idxdb --db-driver=mysql --tables=4 --table-size=10000000 --threads=128 prepare
alter table sbtest1 add (t1 int not null);
update sbtest1 set t1=FLOOR(RAND() * 1000000);
alter table sbtest1 add (t2 int not null);
update sbtest1 set t2=FLOOR(RAND() * 1000000);
alter table sbtest1 add index(k,t1,t2,pad);
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`t1` int(11) NOT NULL,
`t2` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `k_1` (`k`),
KEY `k` (`k`,`t1`,`t2`,`pad`)
);
-- KEY `k` (`k`,`t1`,`t2`,`pad`)
explain select * from sbtest1 force index(k) where k =4983851 and t1=998800 and t2=145174 order by k;
--ref
+------+---------+-------------------+------+----------+-------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------+------+----------+-------+
| k | 12 | const,const,const | 1 | 100.00 | NULL |
+------+---------+-------------------+------+----------+-------+
-- KEY `k` (`k`,`t1`,`t2`,`pad`)
explain select * from sbtest1 force index(k) where k =4983851 and t1=998800 and t2=145174 order by t1;
+------+---------+-------------------+------+----------+-------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------------+------+----------+-------+
| k | 12 | const,const,const | 1 | 100.00 | NULL |
+------+---------+-------------------+------+----------+-------+
-- KEY `k` (`k`,`t1`,`t2`,`pad`)
explain select * from sbtest1 force index(k) where k =4983851 order by t2;
+------+---------+-------+------+----------+---------------------------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------+------+----------+---------------------------------------+
| k | 4 | const | 94 | 100.00 | Using index condition; Using filesort |
+------+---------+-------+------+----------+---------------------------------------+
-- KEY `k` (`k`,`t1`,`t2`,`pad`)
explain select * from sbtest1 force index(k) where t1=998800 order by k;
+------+---------+------+---------+----------+-------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+------+---------+----------+-------------+
| k | 252 | NULL | 9857202 | 10.00 | Using where |
+------+---------+------+---------+----------+-------------+
-- KEY `k` (`k`,`t1`,`t2`,`pad`)
explain select * from sbtest1 force index(k) where k =4983851 and t1=998800 order by k;
+---------------+------+---------+-------------+------+----------+-------+
| possible_keys | key | key_len | ref | rows | filtered | Extra |
+---------------+------+---------+-------------+------+----------+-------+
| k | k | 8 | const,const | 1 | 100.00 | NULL |
+---------------+------+---------+-------------+------+----------+-------+
-- KEY `k` (`k`,`t1`,`t2`,`pad`)
explain select * from sbtest1 force index(k) where k =4983851 and t2=145174 order by t1;
+------+---------+-------+------+----------+-----------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------+------+----------+-----------------------+
| k | 4 | const | 94 | 10.00 | Using index condition |
+------+---------+-------+------+----------+-----------------------+
-- KEY `k` (`k`,`t1`,`t2`,`pad`)
explain select * from sbtest1 force index(k) where k =4983851 and t1=998800 order by t2;
+------+---------+-------------+------+----------+-----------------------+
| key | key_len | ref | rows | filtered | Extra |
+------+---------+-------------+------+----------+-----------------------+
| k | 8 | const,const | 1 | 100.00 | Using index condition |
+------+---------+-------------+------+----------+-----------------------+