直接贴代码吧mysql> explain partitions select smw_title from smw_ids_1 where smw_namespace=1180 and smw_title = 'S' or smw_title = 'AS' or smw_title = 'D' or smw_title = 'B' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: smw_ids_1
partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33
type: range
possible_keys: smw_title
key: smw_title
key_len: 52
ref: NULL
rows: 132
Extra: Using where
1 row in set (0.00 sec)mysql> explain select smw_title from smw_ids where smw_namespace=1180 and smw_title = 'S' or smw_title = 'AS' or smw_title = 'D' or smw_title = 'B';+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | smw_ids | range | smw_title | smw_title | 52 | NULL | 16 | Using where |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.01 sec)mysql> show index from smw_ids_1;
+-----------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| smw_ids_1 | 0 | PRIMARY | 1 | smw_id | A | 1153313 | NULL | NULL | | BTREE | |
| smw_ids_1 | 0 | PRIMARY | 2 | smw_namespace | A | 1153313 | NULL | NULL | | BTREE | |
| smw_ids_1 | 1 | smw_title | 1 | smw_title | A | 1153313 | 50 | NULL | | BTREE | |
| smw_ids_1 | 1 | smw_sortkey | 1 | smw_sortkey | A | 1153313 | 50 | NULL | | BTREE | |
+-----------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.02 sec)
问题2个
1:为什么这个查询要扫描所有的分区?我觉得它可以先通过smw_namespace=1180的分区,分区P1,然后再通过TITLE索引。而不是直接通过索引来查找2:在未分区的表里,扫描16行就能得到结果,分区表里却要扫描132行记录,这是什么原因?3:《MYSQL内幕--INNODB存储引擎》里面说,对于分区,每多扫描一个分区,磁盘IO就会多出来一倍。像上面这样的查询,该怎么样优化?
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: smw_ids_1
partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33
type: range
possible_keys: smw_title
key: smw_title
key_len: 52
ref: NULL
rows: 132
Extra: Using where
1 row in set (0.00 sec)mysql> explain select smw_title from smw_ids where smw_namespace=1180 and smw_title = 'S' or smw_title = 'AS' or smw_title = 'D' or smw_title = 'B';+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | smw_ids | range | smw_title | smw_title | 52 | NULL | 16 | Using where |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.01 sec)mysql> show index from smw_ids_1;
+-----------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| smw_ids_1 | 0 | PRIMARY | 1 | smw_id | A | 1153313 | NULL | NULL | | BTREE | |
| smw_ids_1 | 0 | PRIMARY | 2 | smw_namespace | A | 1153313 | NULL | NULL | | BTREE | |
| smw_ids_1 | 1 | smw_title | 1 | smw_title | A | 1153313 | 50 | NULL | | BTREE | |
| smw_ids_1 | 1 | smw_sortkey | 1 | smw_sortkey | A | 1153313 | 50 | NULL | | BTREE | |
+-----------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.02 sec)
问题2个
1:为什么这个查询要扫描所有的分区?我觉得它可以先通过smw_namespace=1180的分区,分区P1,然后再通过TITLE索引。而不是直接通过索引来查找2:在未分区的表里,扫描16行就能得到结果,分区表里却要扫描132行记录,这是什么原因?3:《MYSQL内幕--INNODB存储引擎》里面说,对于分区,每多扫描一个分区,磁盘IO就会多出来一倍。像上面这样的查询,该怎么样优化?
from smw_ids_1
where smw_namespace=1180
and smw_title = 'S'
or smw_title = 'AS'
or smw_title = 'D'
or smw_title = 'B'这sql语句本身逻辑就有问题吧,哪有and 和or一起使用的
from smw_ids_1
where smw_namespace=1180
and (smw_title = 'S'
or smw_title = 'AS'
or smw_title = 'D'
or smw_title = 'B')
mysql> explain partitions select smw_title from smw_ids_1 where smw_namespace=1180 and (smw_title = 'S' or smw_title = 'AS' or smw_title = 'D' or smw_title = 'B');
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | smw_ids_1 | p1 | range | smw_title | smw_title | 52 | NULL | 4 | Using where |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)这就对了。。1,2楼眼神很犀利啊。