通过explain对mysql索引优化
来源:互联网 发布:time machine 恢复 mac 编辑:程序博客网 时间:2024/06/18 04:09
原文地址: http://www.cnblogs.com/zaric/archive/2012/09/28/2707248.html
-----------
今天优化了多条SQL语句。都是EXPLAIN的功劳,分析SQL子句的执行顺序和执行情况,一木了然,下来看具体分析:
[优化多表联合查询]
explain SELECT sql_no_cache pker.*,pk.* FROM ng_game_pk AS pk ,ng_game_pker AS pker where pker.pkid = pk.id and (pker.act_uid = 1 OR pker.def_uid = 1) AND pk.type <>4 GROUP BY pk.id limit 10;+----+-------------+-------+--------+----------------------------------+---------+---------+--------------------+--------+----------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+----------------------------------+---------+---------+--------------------+--------+----------------------------------------------+| 1 | SIMPLE | pker | ALL | pkid,act_def | NULL | NULL | NULL | 177543 | Using where; Using temporary; Using filesort || 1 | SIMPLE | pk | eq_ref | PRIMARY,type,idx_type_status_uid | PRIMARY | 4 | pwgbk8.7.pker.pkid | 1 | Using where |+----+-------------+-------+--------+----------------------------------+---------+---------+--------------------+--------+----------------------------------------------+
key:NULL,,,Extra:Using temporary,Using filesort都会造成语句很慢。type:ALL 全表扫描,没有比这个更糟糕的了。
下面作出改动:
explain SELECT sql_no_cache pker.*,pk.* FROM ng_game_pk AS pk ,ng_game_pker AS pker where pker.pkid = pk.id and (pker.act_uid = 1 OR pker.def_uid = 1) AND pk.type <>4 GROUP BY pker.pkid limit 10;+----+-------------+-------+--------+----------------------------------+---------+---------+--------------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+----------------------------------+---------+---------+--------------------+------+-------------+| 1 | SIMPLE | pker | index | pkid,act_def | pkid | 4 | NULL | 10 | Using where || 1 | SIMPLE | pk | eq_ref | PRIMARY,type,idx_type_status_uid | PRIMARY | 4 | pwgbk8.7.pker.pkid | 1 | Using where |+----+-------------+-------+--------+----------------------------------+---------+---------+--------------------+------+-------------+
比较下执行:
mysql> show profile for query 147;+----------------------+----------+| Status | Duration |+----------------------+----------+| Creating tmp table | 0.000372 || Copying to tmp table | 0.424248 || removing tmp table | 0.002125 |+----------------------+----------+
这里忽略了其他的执行。看到拷贝到临时表占用了大部分时间。这里需要解释下:Copying to tmp table是拷贝到内存,如果是Copying to tmp table on disk意味着内存空间不足,MySQL将会写临时表到磁盘。这个大小的配置参考tmp_table_size。
现分析下第二条SQL,,,子句id是一样的,那么执行顺序是从上至下,现查询pker表,索引为pkid,那么可以认为GROUP BY pker.pkid作用于上面的子句,rows为10,那么limit 10也是作用于此。
[优化索引、复合索引]
mysql> select count(*) from ng_game_pk WHERE status = 0 AND type = 4 AND uid = 1;+----------+| count(*) |+----------+| 0 |+----------+1 row in set (13.03 sec)
mysql> explain select count(*) from ng_game_pk WHERE status = 0 AND type = 4 AND uid = 1;
+----+-------------+------------+------+---------------+------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+-------+---------+-------------+
| 1 | SIMPLE | ng_game_pk | ref | type | type | 1 | const | 1729551 | Using where |
+----+-------------+------------+------+---------------+------+---------+-------+---------+-------------
这条语句用到了索引type,但是type取值范围很窄(1,2,3,4)其实这个索引没多大用处。
下面我们建立复合索引看效果如何,
mysql> alter table ng_game_pk add index idx_type_status_uid(type,status,uid);Query OK, 5831851 rows affected (1 min 43.20 sec)Records: 5831851 Duplicates: 0 Warnings: 0mysql> explain select count(*) from ng_game_pk WHERE status = 0 AND type = 4 AND uid = 1;+----+-------------+------------+------+--------------------------+---------------------+---------+-------------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+------+--------------------------+---------------------+---------+-------------------+------+-------------+| 1 | SIMPLE | ng_game_pk | ref | type,idx_type_status_uid | idx_type_status_uid | 6 | const,const,const | 1 | Using index |+----+-------------+------------+------+--------------------------+---------------------+---------+-------------------+------+-------------+1 row in set (0.11 sec)mysql> select count(*) from ng_game_pk WHERE status = 0 AND type = 4 AND uid = 1;+----------+| count(*) |+----------+| 0 |+----------+1 row in set (0.00 sec)
看来有很好的效果了。对SQL稍作修改,
mysql> select sql_no_cache count(*) from ng_game_pk WHERE type = 4 and status>0 AND uid = 1;+----------+| count(*) |+----------+| 2649 |+----------+1 row in set (0.40 sec)
性能又下来了,这是因为B-Tree算法的原因,存储引擎将不能优化任何在第一个条件范围右边的列。那么就是(type,status,,,)后面的索引失效。
那么调整下,,,
mysql> drop index idx_type_status_uid on ng_game_pk;mysql> alter table ng_game_pk add index idx_type_uid_status (type,uid,status);
结果性能又提升上来了。
---------------------------EOF--------------------------------
参考:
#《高性能MySql (第二版)》
#《构建高性能web站点》
# http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html
# http://www.perfgeeks.com/?p=460
- 通过explain对mysql索引优化
- mysql explain索引优化详解
- mysql优化实战(explain && 索引)
- MySQL索引及Explain及常见优化
- 优化mysql实验(explain;索引)+利用 index、explain和profile优化mysql数据库查询小结
- SQL优化:索引,explain
- SQL优化:索引,explain
- Explain优化索引
- MySQL EXPLAIN 查询语句索引分析---优化器原理
- MySQL性能优化---explain
- MySQL查询优化-explain
- MySQL查询优化-explain
- MySQL查询优化-explain
- mysql优化 explain index
- MySQL查询优化-explain
- MySQL查询优化-explain
- mysql优化 explain
- MySQL查询优化-explain
- Data Binding(数据绑定)用户指南
- iOS缓存方案
- mysql中的if与case-when
- Apache SCXML 状态机
- spring quartz学习总结: cluster的配置和示例
- 通过explain对mysql索引优化
- 【实践】ArtTemplate helper函数的使用
- Android编程开发总结
- 如何优雅的退出android应用
- AirPrint: 无交互的后台打印实现(Print without UI,iOS8+)
- 机器学习实战笔记——基于SVD的图像压缩
- 多个字符串逆置
- android窗口管理剖析
- 在Windows下C++获得程序路径