添加索引优化器更高效率地执行语句
假设我们有两个数据表t1和t2,每个有1000行,包含的值从1到1000。下面的查询查找出两个表中值相同的数据行:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql> select t1.i1, t2.i2 from t1, t2 where t1.i1 = t2.i2;
+------+------+
| i1 | i2 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
两个表都没有索引的时候,explain产生下面的结果:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql> explain select t1.i1, t2.i2 from t1, t2 where t1.i1 = t2.i2\g
*************************** 1. row ***************************
id: 1
select_type: simple
table: t1
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 1000
extra:
*************************** 2. row ***************************
table: t2
extra: using whe
类型列中的all表明要进行检查所有数据行的全表扫描。可能键列中的null表明没有找到用于提高查询速度的备选索引(键、键长度和参考列都是null也是因为缺少合适的索引)。using where表明使用where子句中的信息来识别合格的数据行。 这段信息告诉我们,优化器没有为提高执行查询的效率找到任何有用的信息:
它将对t1表进行全表扫描。
对于t1中的每一行,它将执行t2的全表扫描,使用where子句中的信息识别出合格的行。
行数值显示了优化器估计的每个阶段查询需要检查的行数。t1的估计值是1000,因为1000可以完成全表扫描。相似地,t2的估计值也是1000,但是这个值是对于t1的每一行的。换句话说,优化器所估计的处理该查询所需要检查的数据行组合的数量是1000×1000,也就是一百万。这会造成很大的浪费 ,因为实际上只有1000个组合符合where子句的条件。
为了使这个查询的效率更高,给其中一个联结列添加索引 并重新执行explain语句:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql> alter table t2 add index (i2);
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 10
extra: using where; using index
我们可以看到性能提高了。t1的输出没有改变(表明还是需要进行全表扫描),但是优化器处理t2的方式就有所不同了:
类型从all改变为ref,意味着可以使用参考值(来自t1的值)来执行索引查找,定位t2中合格的数据行。
参考值在参考(ref)字段中给出了:sampdb.t1.i1。
数值从1000降低到了10,显示出优化器相信对于t1中的每一行,它只需要检查t2中的10行(这是一个悲观的估计值。实际上,在t2中只有一行与 t1中数据行匹配。我们在后面会看到如何帮助优化器改善这个估计值)。数据行组合的全部估计值使1000×10=10000。它比前面的没有索引的时候估 计出来的一百万好多了。
对t1进行索引有价值吗?实际上,对于这个特定的联结操作,扫描一张表是必要的,因此没有必要对t1建立索引。如果你想看到效果,可以索引t1.i1并再次运行explain:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql> alter table t1 add index (i1);
type: index
possible_keys: i1
key: i1
extra: using index
上面的输出与前面的explain的输出相似,但是添加索引对t1的输出有一些改变。类型从null改成了index,附加(extra)从空的改成了 using index。这些改变表明,尽管对索引的值仍然需要执行全表扫描,但是优化器还是可以直接从索引文件中读取值,根据不需要使用数据文件。你可以从 myisam表中看到这类结果,在这种情况下,优化器知道自己只询问索引文件就能够得到所有需要的信息。对于innodb 和bdb表也有这样的结果,在这种情况下优化器可以单独使用索引中的信息而不用搜索数据行。
我们可以运行analyze table使优化器进一步优化估计值。这会引起服务器生成键值的静态分布。分析上面的表并再次运行explain得到了更好的估计值:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql> analyze table t1, t2;
rows: 1
extra: using where; using inde
在这种情况下,优化器估计在t2中与t1的每个值匹配的数据行只有一个。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql > explain select a . id , a . title , b . title from jos_content a , jos_categories b where a . catid = b . id ;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
| 1 | simple | a | all | null | null | null | null | 46585 | |
| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | using where |
2 rows in set ( 0.00 sec )
这个是我们经常使用的一种查询方式,对b表的联接类型使用了eq_ref,索引使用了primary,但是对于a表,却没有使用任何索引,这可能不是我们想要的。
查看以上sql语句,我们可能会想到,有必要给a.catid加个索引了。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql > alter table jos_content add index idx_catid ( ` catid ` ) ;
query ok , 46585 rows affected ( 0.75 sec )
records : 46585 duplicates : 0 warnings : 0
| 1 | simple | a | all | idx_catid | null | null | null | 46585 | |
这样表a便使用了idx_catid索引。下面我们做一次三个表的联合查询
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql > explain select a . id , a . title , b . title from jos_content a , jos_categories b , jos_sections c where a . catid = b . id and a . sectionid = c . id ;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
| 1 | simple | c | index | primary | primary | 4 | null | 2 | using index |
| 1 | simple | a | all | idx_catid | null | null | null | 46585 | using where ; using join buffer |
| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | using where |
3 rows in set ( 0.00 sec )
这里显示了mysql先将c表读入查询,并使用primary索引,然后联合a表进行查询,这时候type显示的是all,可以用的索引有idx_catid,但是实际没有用。
原因非常明显,因为使用的连接条件是a.sectionid=c.id,所以我们给a.sectionid加个索引先。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql > alter table jos_content add index idx_section ( ` sectionid ` ) ;
query ok , 46585 rows affected ( 0.89 sec )
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
| 1 | simple | c | index | primary | primary | 4 | null | 2 | using index |
| 1 | simple | a | ref | idx_catid , idx_section | idx_section | 4 | joomla_test . c . id | 23293 | using where |
| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | using where |
这时候显示结果告诉我们,效果很明显,在连接a表时type变成了ref,索引使用了idx_section,如果我们注意看后两列,对a表的查询结果后一次明显少了一半左右,而且没有用到join buffer。
这个表读入的顺序是mysql优化器帮我们做的,可以得知,用记录数少的表做为基础表进行联合,将会得到更高的效率。
对于上面的语句,我们换一种写法
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5ichR3cf52bjl2LcNXZnFWbp9CXt92YuUWelRXauYjM5cmbvlXZkVHavw1LcpDc0RHaiojIsJye.png)
mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
| 1 | simple | a | all | null | null | null | null | 46585 | |
| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |
| 1 | simple | c | eq_ref | primary | primary | 4 | joomla_test . a . sectionid | 1 | using index |
mysql读入表的顺序被改变了,这意味着,如果我们用left join来做连接查询,mysql会按sql语句中表出现的顺序读入,还有一个有变化的地方是联接b和c的type都变成了eq_ref,前边我们说过, 这样说明mysql可以找到唯一的行,这个效率是比ref要高的。