EXPLAIN sql优化方法（1） 添加索引
2023-09-14 08:59:47 时间
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 ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using whe
mysql ALTER TABLE t2 ADD INDEX (i2); 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 *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 10 Extra: Using where; Using index
mysql ALTER TABLE t1 ADD INDEX (i1); 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: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 10 Extra: Using where; Using index
mysql ANALYZE TABLE t1, t2; 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: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 1 Extra: Using where; Using inde
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 )
mysql alter table jos_content add index idx_catid ( ` catid ` ) ; Query OK , 46585 rows affected ( 0.75 sec ) Records : 46585 Duplicates : 0 Warnings : 0 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 | idx_catid | 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 )
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加个索引先。
mysql alter table jos_content add index idx_section ( ` sectionid ` ) ; Query OK , 46585 rows affected ( 0.89 sec ) Records : 46585 Duplicates : 0 Warnings : 0 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 | 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 | +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+ 3 rows in set ( 0.00 sec )
这时候显示结果告诉我们，效果很明显，在连接A表时type变成了ref，索引使用了idx_section，如果我们注意看后两列，对A表的查询结果后一次明显少了一半左右，而且没有用到join buffer。
这个表读入的顺序是Mysql优化器帮我们做的，可以得知，用记录数少的表做为基础表进行联合，将会得到更高的效率。
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 | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+ 3 rows in set ( 0.00 sec )
Mysql读入表的顺序被改变了，这意味着，如果我们用left join来做连接查询，Mysql会按SQL语句中表出现的顺序读入，还有一个有变化的地方是联接B和C的type都变成了eq_ref，前边我们说过， 这样说明Mysql可以找到唯一的行，这个效率是比ref要高的。
类型列中的ALL表明要进行检查所有数据行的全表扫描。可能键列中的NULL表明没有找到用于提高查询速度的备选索引（键、键长度和参考列都是NULL也是因为缺少合适的索引）。Using where表明使用WHERE子句中的信息来识别合格的数据行。 这段信息告诉我们，优化器没有为提高执行查询的效率找到任何有用的信息：
它将对t1表进行全表扫描。
对于t1中的每一行，它将执行t2的全表扫描，使用WHERE子句中的信息识别出合格的行。
行数值显示了优化器估计的每个阶段查询需要检查的行数。T1的估计值是1000，因为1000可以完成全表扫描。相似地，t2的估计值也是1000，但是这个值是对于t1的每一行的。换句话说，优化器所估计的处理该查询所需要检查的数据行组合的数量是1000×1000，也就是一百万。这会造成很大的浪费 ，因为实际上只有1000个组合符合WHERE子句的条件。
为了使这个查询的效率更高，给其中一个联结列添加索引 并重新执行EXPLAIN语句：
mysql ALTER TABLE t2 ADD INDEX (i2); 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 *************************** id: 1 select_type: SIMPLE table: t2 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：
mysql ALTER TABLE t1 ADD INDEX (i1); 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: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 10 Extra: Using where; Using index
上面的输出与前面的EXPLAIN的输出相似，但是添加索引对t1的输出有一些改变。类型从NULL改成了index，附加（Extra）从空的改成了 Using index。这些改变表明，尽管对索引的值仍然需要执行全表扫描，但是优化器还是可以直接从索引文件中读取值，根据不需要使用数据文件。你可以从 MyISAM表中看到这类结果，在这种情况下，优化器知道自己只询问索引文件就能够得到所有需要的信息。对于InnoDB 和BDB表也有这样的结果，在这种情况下优化器可以单独使用索引中的信息而不用搜索数据行。
我们可以运行ANALYZE TABLE使优化器进一步优化估计值。这会引起服务器生成键值的静态分布。分析上面的表并再次运行EXPLAIN得到了更好的估计值：
mysql ANALYZE TABLE t1, t2; 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: index possible_keys: i1 key: i1 key_len: 5 ref: NULL rows: 1000 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: i2 key: i2 key_len: 5 ref: sampdb.t1.i1 rows: 1 Extra: Using where; Using inde
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加个索引了。
mysql alter table jos_content add index idx_catid ( ` catid ` ) ; Query OK , 46585 rows affected ( 0.75 sec ) Records : 46585 Duplicates : 0 Warnings : 0 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 | idx_catid | 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 )
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加个索引先。
mysql alter table jos_content add index idx_section ( ` sectionid ` ) ; Query OK , 46585 rows affected ( 0.89 sec ) Records : 46585 Duplicates : 0 Warnings : 0 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 | 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 | +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+ 3 rows in set ( 0.00 sec )
这时候显示结果告诉我们，效果很明显，在连接A表时type变成了ref，索引使用了idx_section，如果我们注意看后两列，对A表的查询结果后一次明显少了一半左右，而且没有用到join buffer。
这个表读入的顺序是Mysql优化器帮我们做的，可以得知，用记录数少的表做为基础表进行联合，将会得到更高的效率。
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 | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+ 3 rows in set ( 0.00 sec )
Mysql读入表的顺序被改变了，这意味着，如果我们用left join来做连接查询，Mysql会按SQL语句中表出现的顺序读入，还有一个有变化的地方是联接B和C的type都变成了eq_ref，前边我们说过， 这样说明Mysql可以找到唯一的行，这个效率是比ref要高的。
相关文章
- 常见SQL面试题_sql索引面试题
- 修复Domino数据库视图索引的两种方法
- 手写一个Promise Class版本及Promise的api使用方法
- Mysql查看当前行号的函数使用方法（mysql行号函数）
- Oracle数据库中修改索引的方法（oracle修改索引）
- 基于Oracle表的索引建立方法研究（oracle表建立索引）
- 文件Linux快速导入SQL文件的方法（linux导入sql）
- 称查看Oracle数据库中索引名称的方法（oracle查看索引名）
- MySQL索引引擎：优化数据库查询的新方法（mysql索引引擎）
- MySQL教程：拼接SQL语句的方法（mysql拼接sql语句）
- 深入剖析：MySQL 辅助索引的作用与优化方法（mysql辅助索引）
- 利用Oracle SQL设计高效报表生成方法（oracle报表sql）
- MySQL索引的作用及优化方法（mysql索引作用）
- Oracle多条SQL的执行方法及优化建议（oracle多条sql）
- MSSQL数据库中索引优化的清除方法（mssql 索引清除）
- 最佳的MS SQL索引使用方法（mssql索引使用方法）
- MySQL数据库的SQL导入方法简介（sql导入mysql数据库）
- MySQL数据库简单导出SQL脚本方法（mysql导出sql脚本）
- MySQL中主键索引的作用及使用方法（mysql中主键索引）
- mysql索引删除问题的解决方法（mysql不能删除索引）
- MySQL索引问题无法创建索引的解决方法（mysql不能创建索引）
- Oracle中实现字段拼接的方法（oracle中拼接字段）
- Oracle中SQL语句的运用方法（oracle中sql用法）
- Oracle数据库中添加一般索引的方法（oracle一般索引添加）
- 用SQL脚本读取Excel中的sheet数量及名称的方法代码