EXPLAIN sql优化方法（1） 添加索引

添加索引优化器更高效率地执行语句 

假设我们有两个数据表t1和t2，每个有1000行，包含的值从1到1000。下面的查询查找出两个表中值相同的数据行：

Java代码  

  1. mysql> SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;  
  2. +------+------+  
  3. | i1 | i2 |  
  4. +------+------+  
  5. | 1 | 1 |  
  6. | 2 | 2 |  
  7. | 3 | 3 |  
  8. | 4 | 4 |  
  9. | 5 | 5 |  

两个表都没有索引的时候，EXPLAIN产生下面的结果：

Java代码  

  1. mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G  
  2. *************************** 1. row ***************************  
  3. id: 1  
  4. select_type: SIMPLE  
  5. table: t1  
  6. type: ALL  
  7. possible_keys: NULL  
  8. key: NULL  
  9. key_len: NULL  
  10. ref: NULL  
  11. rows: 1000  
  12. Extra:  
  13. *************************** 2. row ***************************  
  14. id: 1  
  15. select_type: SIMPLE  
  16. table: t2  
  17. type: ALL  
  18. possible_keys: NULL  
  19. key: NULL  
  20. key_len: NULL  
  21. ref: NULL  
  22. rows: 1000  
  23. Extra: Using whe  

 类型列中的ALL表明要进行检查所有数据行的全表扫描。可能键列中的NULL表明没有找到用于提高查询速度的备选索引（键、键长度和参考列都是NULL也是因为缺少合适的索引）。Using where表明使用WHERE子句中的信息来识别合格的数据行。 这段信息告诉我们，优化器没有为提高执行查询的效率找到任何有用的信息：
它将对t1表进行全表扫描。

对于t1中的每一行，它将执行t2的全表扫描，使用WHERE子句中的信息识别出合格的行。
行数值显示了优化器估计的每个阶段查询需要检查的行数。T1的估计值是1000，因为1000可以完成全表扫描。相似地，t2的估计值也是1000，但是这个值是对于t1的每一行的。换句话说，优化器所估计的处理该查询所需要检查的数据行组合的数量是1000×1000，也就是一百万。这会造成很大的浪费 ，因为实际上只有1000个组合符合WHERE子句的条件。

 

为了使这个查询的效率更高，给其中一个联结列添加索引 并重新执行EXPLAIN语句：

Java代码  

  1. mysql> ALTER TABLE t2 ADD INDEX (i2);  
  2. mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G  
  3. *************************** 1. row ***************************  
  4. id: 1  
  5. select_type: SIMPLE  
  6. table: t1  
  7. type: ALL  
  8. possible_keys: NULL  
  9. key: NULL  
  10. key_len: NULL  
  11. ref: NULL  
  12. rows: 1000  
  13. Extra:  
  14. *************************** 2. row ***************************  
  15. id: 1  
  16. select_type: SIMPLE  
  17. table: t2  
  18. type: ref  
  19. possible_keys: i2  
  20. key: i2  
  21. key_len: 5  
  22. ref: sampdb.t1.i1  
  23. rows: 10  
  24. 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：

Java代码  

  1. mysql> ALTER TABLE t1 ADD INDEX (i1);  
  2. mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G  
  3. *************************** 1. row ***************************  
  4. id: 1  
  5. select_type: SIMPLE  
  6. table: t1  
  7. type: index  
  8. possible_keys: i1  
  9. key: i1  
  10. key_len: 5  
  11. ref: NULL  
  12. rows: 1000  
  13. Extra: Using index  
  14. *************************** 2. row ***************************  
  15. id: 1  
  16. select_type: SIMPLE  
  17. table: t2  
  18. type: ref  
  19. possible_keys: i2  
  20. key: i2  
  21. key_len: 5  
  22. ref: sampdb.t1.i1  
  23. rows: 10  
  24. Extra: Using where; Using index  

上面的输出与前面的EXPLAIN的输出相似，但是添加索引对t1的输出有一些改变。类型从NULL改成了index，附加（Extra）从空的改成了 Using index。这些改变表明，尽管对索引的值仍然需要执行全表扫描，但是优化器还是可以直接从索引文件中读取值，根据不需要使用数据文件。你可以从 MyISAM表中看到这类结果，在这种情况下，优化器知道自己只询问索引文件就能够得到所有需要的信息。对于InnoDB 和BDB表也有这样的结果，在这种情况下优化器可以单独使用索引中的信息而不用搜索数据行。

我们可以运行ANALYZE TABLE使优化器进一步优化估计值。这会引起服务器生成键值的静态分布。分析上面的表并再次运行EXPLAIN得到了更好的估计值：

Java代码  

  1. mysql> ANALYZE TABLE t1, t2;  
  2. mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G  
  3. *************************** 1. row ***************************  
  4. id: 1  
  5. select_type: SIMPLE  
  6. table: t1  
  7. type: index  
  8. possible_keys: i1  
  9. key: i1  
  10. key_len: 5  
  11. ref: NULL  
  12. rows: 1000  
  13. Extra: Using index  
  14. *************************** 2. row ***************************  
  15. id: 1  
  16. select_type: SIMPLE  
  17. table: t2  
  18. type: ref  
  19. possible_keys: i2  
  20. key: i2  
  21. key_len: 5  
  22. ref: sampdb.t1.i1  
  23. rows: 1  
  24. Extra: Using where; Using inde  

在这种情况下，优化器估计在t2中与t1的每个值匹配的数据行只有一个。

 

Java代码  

  1. mysql > explain select A . id , A . title , B . title from jos_content A , jos_categories B where A . catid = B . id ;  
  2. +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+  
  3. | id | select_type | table | type    | possible_keys | key      | key_len | ref                  | rows   | Extra        |  
  4. +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+  
  5. |  1 | SIMPLE       | A      | ALL     | NULL           | NULL     | NULL     | NULL                 | 46585 |             |  
  6. |  1 | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . catid |      1 | Using where |  
  7. +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+  
  8. 2 rows in set ( 0.00 sec )  

这个是我们经常使用的一种查询方式，对B表的联接类型使用了eq_ref，索引使用了PRIMARY，但是对于A表，却没有使用任何索引，这可能不是我们想要的。
查看以上SQL语句，我们可能会想到，有必要给A.catid加个索引了。

Java代码  

  1. mysql > alter table jos_content add index idx_catid ( ` catid ` ) ;  
  2. Query OK , 46585 rows affected ( 0.75 sec )  
  3. Records : 46585   Duplicates : 0   Warnings : 0  
  4.    
  5. mysql > explain select A . id , A . title , B . title from jos_content A , jos_categories B where A . catid = B . id ;  
  6. +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+  
  7. | id | select_type | table | type    | possible_keys | key      | key_len | ref                  | rows   | Extra        |  
  8. +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+  
  9. |  1 | SIMPLE       | A      | ALL     | idx_catid      | NULL     | NULL     | NULL                 | 46585 |             |  
  10. |  1 | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . catid |      1 | Using where |  
  11. +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+  
  12. 2 rows in set ( 0.00 sec )  

这样表A便使用了idx_catid索引。下面我们做一次三个表的联合查询

Java代码  

  1. 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 ;  
  2. +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+  
  3. | id | select_type | table | type    | possible_keys | key      | key_len | ref                  | rows   | Extra                           |  
  4. +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+  
  5. |  1 | SIMPLE       | C      | index   | PRIMARY        | PRIMARY | 4        | NULL                 |      2 | Using index                     |  
  6. |  1 | SIMPLE       | A      | ALL     | idx_catid      | NULL     | NULL     | NULL                 | 46585 | Using where ; Using join buffer |  
  7. |  1 | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . catid |      1 | Using where                     |  
  8. +----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+  
  9. 3 rows in set ( 0.00 sec )  

 这里显示了Mysql先将C表读入查询，并使用PRIMARY索引，然后联合A表进行查询，这时候type显示的是ALL，可以用的索引有idx_catid，但是实际没有用。
原因非常明显，因为使用的连接条件是A.sectionid=C.id，所以我们给A.sectionid加个索引先。

Java代码  

  1. mysql > alter table jos_content add index idx_section ( ` sectionid ` ) ;  
  2. Query OK , 46585 rows affected ( 0.89 sec )  
  3. Records : 46585   Duplicates : 0   Warnings : 0  
  4.    
  5. 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 ;  
  6. +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+  
  7. | id | select_type | table | type    | possible_keys          | key          | key_len | ref                  | rows   | Extra        |  
  8. +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+  
  9. |  1 | SIMPLE       | C      | index   | PRIMARY                | PRIMARY      | 4        | NULL                 |      2 | Using index |  
  10. |  1 | SIMPLE       | A      | ref     | idx_catid , idx_section | idx_section | 4        | joomla_test . C . id     | 23293 | Using where |  
  11. |  1 | SIMPLE       | B      | eq_ref | PRIMARY                | PRIMARY      | 4        | joomla_test . A . catid |      1 | Using where |  
  12. +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+  
  13. 3 rows in set ( 0.00 sec )  

 这时候显示结果告诉我们，效果很明显，在连接A表时type变成了ref，索引使用了idx_section，如果我们注意看后两列，对A表的查询结果后一次明显少了一半左右，而且没有用到join buffer。
这个表读入的顺序是Mysql优化器帮我们做的，可以得知，用记录数少的表做为基础表进行联合，将会得到更高的效率。

对于上面的语句，我们换一种写法

Java代码  

  1. 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 ;  
  2. +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+  
  3. | id | select_type | table | type    | possible_keys | key      | key_len | ref                      | rows   | Extra        |  
  4. +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+  
  5. |  1 | SIMPLE       | A      | ALL     | NULL           | NULL     | NULL     | NULL                     | 46585 |             |  
  6. |  1 | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . catid      |      1 |             |  
  7. |  1 | SIMPLE       | C      | eq_ref | PRIMARY        | PRIMARY | 4        | joomla_test . A . sectionid |      1 | Using index |  
  8. +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+  
  9. 3 rows in set ( 0.00 sec )  

 Mysql读入表的顺序被改变了，这意味着，如果我们用left join来做连接查询，Mysql会按SQL语句中表出现的顺序读入，还有一个有变化的地方是联接B和C的type都变成了eq_ref，前边我们说过， 这样说明Mysql可以找到唯一的行，这个效率是比ref要高的。

 

时间： 2024-07-30 05:28:27

EXPLAIN sql优化方法（1） 添加索引的相关文章

EXPLAIN sql优化方法（2） Using temporary ; Using filesort

优化GROUP BY语句 默认情况下,MySQL对所有GROUP BY col1,col2...的字段进行排序.这与在查询中指定ORDER BY col1,col2...类似.因此,如果显式包括一个包含相同的列的ORDER BY子句,则对MySQL的实际执行性能没有什么影响. 如果查询包括GROUP BY 但用户想要避免排序结果的消耗,则可以指定ORDER By NULL禁止排序,例如: Java代码   explain select id, sum(moneys) from sales2 gr

EXPLAIN sql优化方法（3）DERIVED

派生表和视图的性能 从MySQL 4.1开始,它已经支持派生表.联机视图或者基本的FROM从句的子查询. 这些特性之间彼此相关,但是它们之间的性能比较如何呢? MySQL 5.0 中的派生表似乎和视图实现的方式不同,尽管我从合并的代码基数来看觉得在查询优化上应该是一样的. 派生表仍然以临时表的方式显式地处理,而且还是没有索引的临时表(因此最好不要像在例子中那样连接2个派生表) 需要考虑的另一方面是,派生表需要被显式处理,尽管只是执行 EXPLAIN 语句.因此如果在 FROM 字句中的 SELE

千万级的mysql数据库与sql优化方法

对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使

浅谈SQL优化方法

一. 什么情况下需要优化SQL? 1.单个查询执行时间非常长 2.许多查询执行时间过长,但是有一个代表性的可以调整(使用绑定变量) 二.什么情况下会触发SQL慢? 1.数据库版本的升级改变了行为或有bug 2.统计信息不正确 3.数据变化 4.应用变化 三.什么地方会出错呢? 1.索引:没被创建.建了但无效.建了但没走索引 2.表连接的顺序错了 3.表连接的类型错了 4.谓词没有推进,视图没有合并 5.查询转化不当(COST) 6.其它问题 查看本栏目更多精彩内容:http://www.bian

不懂业务的SQL优化方法

看着如下长长的SQL,对于其要实现的功能一无所知,开发人员需要根据领导的需要调整该SQL并导出产生的数据,但每次运行都要5分钟左右,对对于开发,调试和领导确认都不能接受,开发人员直接将此SQL转给我优化,由于此SQL并不写入应用且手工执行频率很高,于是最直接的方法就是先并行加快sql的执行速度 WITH T_MAST AS(  SELECT          T3.BASE_YW         ,T2.USER_ID         ,T2.USER_NM            ,T2.BRN

总结SQL执行进展优化方法_MsSql

在本文中,小编将与大家重点探讨SQL执行进展优化的方法. 聚集索引扫描 SELECT * FROM C_SY_Ownedstorm 聚集索引扫描比表扫描快 聚集索引扫描:发生于聚集表,也相当于全表扫描操作,但在针对聚集列的条件等操作时,效率会较好. 表扫描 SELECT * FROM #temp 表扫描:发生于堆表,并且没有可用的索引时,会发生表扫描,表示整个表扫描一次. 测试SQL CREATE TABLE t1(c1 INT, c2 VARCHAR (8000)); GO DECLARE @

智能运维：美团SQL Advisor的自动化SQL优化实现

介绍 在数据库运维过程中,优化 SQL 是 DBA 团队的日常任务.例行 SQL 优化,不仅可以提升程序性能,还能够降低线上故障的概率. 目前常用的 SQL 优化方式包括但不限于:业务层优化.SQL逻辑优化.索引优化等.其中索引优化通常通过调整索引或新增索引从而达到 SQL 优化的目的.索引优化往往可以在短时间内产生非常巨大的效果.如果能够将索引优化转化成工具化.标准化的流程,减少人工介入的工作量,无疑会大大提高DBA的工作效率. SQLAdvisor 是由美团点评公司北京DBA团队开发维护的

MySql sql优化之order by desc/asc limit M

Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景,其优化原理也非常的简单,就是利用索引的有序性,优化器沿着索引的顺序扫描,在扫描到符合条件的M行数据后,停止扫描:看起来非常的简单,但是我经常看到很多性能较差的sql没有利用这个优化规律,下面将结合一些实际的案例来分析说明: 案例一: 一条sql执行非常的慢,执行时间为: root@test 02:00:44   SELECT * FROM test_order_desc WHERE  END_TI

踩坑CBO，解决那些坑爹的SQL优化问题

本文根据DBAplus社群第93期线上分享整理而成.   讲师介绍  丁俊 新炬网络首席性能优化专家 SQL审核产品经理   DBAplus社群联合发起人,<剑破冰山-Oracle开发艺术>副主编. Oracle ACEA,ITPUB开发版资深版主,十年电信行业从业经验.   本次分享大纲: CBO优化器存在哪些坑 CBO优化器坑的解决之道 加强SQL审核,将性能问题扼杀于襁褓之中 分享现场FAQ   CBO( Cost Based Optimizer)优化器是目前Oracle广泛使用的优化器