这里举例说明如何查看MySQL的SQL执行计划，并根据执行计划创建索引。
mysql> explain select count(*) from orders o , customer c where o.o_c_id=c.c_id and c_last='BARBARBAR' and c_first='YaWRXwdLu3Sq1'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+----------+----------+-----------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+----------+----------+-----------------------------------------------------------------+ | 1 | SIMPLE | c | NULL | index | NULL | idx_customer | 137 | NULL | 277208 | 1.00 | Using where; Using index | | 1 | SIMPLE | o | NULL | index | NULL | idx_orders | 12 | NULL | 24346812 | 10.00 | Using where; Using index; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+----------+----------+-----------------------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
使用show index from orders;和show index from customer;可以查看这些索引的定义。
列名 说明
id 执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type 显示本行是简单或复杂select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY（DERIVED、UNION、UNION RESUlT）
table 访问引用哪个表（引用某个查询，如“derived3”）
type 数据访问/读取操作类型（ALL、index、range、ref、eq_ref、const/system、NULL）
possible_keys 揭示哪一些索引可能有利于高效的查找
key 显示mysql决定采用哪个索引来优化查询
key_len 显示mysql在索引里使用的字节数
ref 显示了之前的表在key列记录的索引中查找值所用的列或常量
rows 为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
filtered 显示过滤后的百分比
Extra 额外信息，如using index、filesort等
创建一个索引后的执行计划改变了：
mysql> create index cu_dx1 on customer(c_last,c_first); Query OK, 0 rows affected (1.71 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select count(*) from orders o , customer c where o.o_c_id=c.c_id and c_last='BARBARBAR' and c_first='YaWRXwdLu3Sq1' -> ; +----+-------------+-------+------------+-------+---------------+------------+---------+-------------+----------+----------+-----------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------------+---------+-------------+----------+----------+-----------------------------------------------------------------+ | 1 | SIMPLE | c | NULL | ref | cu_dx1 | cu_dx1 | 134 | const,const | 1 | 100.00 | Using index | | 1 | SIMPLE | o | NULL | index | NULL | idx_orders | 12 | NULL | 24216188 | 10.00 | Using where; Using index; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+-------+---------------+------------+---------+-------------+----------+----------+-----------------------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
当我们把orders表上对应的index改成invisible后，我们看到执行计划走了全表扫描。
03:09:41pm> alter table orders alter index idx_orders invisible; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 03:11:02pm> explain select count(*) from orders o , customer c where o.o_c_id=c.c_id and c_last='BARBARBAR' and c_first='YaWRXwdLu3Sq1'; +----+-------------+-------+------------+------+---------------+--------+---------+-------------+----------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+-------------+----------+----------+----------------------------------------------------+ | 1 | SIMPLE | c | NULL | ref | cu_dx1 | cu_dx1 | 134 | const,const | 1 | 100.00 | Using index | | 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 24216188 | 10.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+--------+---------+-------------+----------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
