Chinaunix首页 | 论坛 | 博客
  • 博客访问: 474532
  • 博文数量: 99
  • 博客积分: 3621
  • 博客等级: 中校
  • 技术积分: 1089
  • 用 户 组: 普通用户
  • 注册时间: 2010-06-22 16:29
文章存档

2012年(21)

2011年(28)

2010年(50)

分类: Mysql/postgreSQL

2012-05-28 16:09:23


点击(此处)折叠或打开

  1. 一个SQL从13.45S优化到0.14S的过程
  2. #SQL原型+执行计划
  3. mysql> desc SELECT s.*,st.tag_name FROM fanwe_share as s
  4. INNER JOIN fanwe_share_category as sc ON sc.share_id = s.share_id
  5. INNER JOIN fanwe_share_tags as st ON st.share_id = s.share_id
  6. INNER JOIN fanwe_goods_category_tags as gct ON gct.cate_id = sc.cate_id
  7. WHERE gct.cate_id in (2,14,15,16,17,18) AND s.is_index=1 AND s.status=1
  8. GROUP BY s.share_id
  9. ORDER BY s.sort desc,s.share_id desc
  10.  limit 30;
  11. +----+-------------+-------+--------+-------------------------+----------+---------+---------------------+------+-----------------------------------------------------------+
  12. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  13. +----+-------------+-------+--------+-------------------------+----------+---------+---------------------+------+-----------------------------------------------------------+
  14. | 1 | SIMPLE | gct | range | cate_id | cate_id | 2 | NULL | 101 | Using where; Using index; Using temporary; Using filesort |
  15. | 1 | SIMPLE | sc | ref | share_id,cate_id | cate_id | 4 | shop520.gct.cate_id | 18 | Using where |
  16. | 1 | SIMPLE | s | eq_ref | PRIMARY,status,is_index | PRIMARY | 4 | shop520.sc.share_id | 1 | Using where |
  17. | 1 | SIMPLE | st | ref | share_id | share_id | 4 | shop520.sc.share_id | 19 | |
  18. +----+-------------+-------+--------+-------------------------+----------+---------+---------------------+------+-----------------------------------------------------------+
  19. 4 rows in set (0.00 sec)

  20. #查询时间:
  21. 30 rows in set (13.45 sec)

  22. #分析:
  23. 1:s.*,是否必须要全部字段,在正常情况下不允许写*,产生的问题有2个:1)获取不必要的数据,占用内存,网络字段,2)无法使用到覆盖索引。建议只取需要的字段
  24. 2:Using where; Using index; Using temporary; Using filesort,查询是从s开始的,group by,order by 字段也是s表,执行计划确用gct表做驱动表,导致分组,排序不能使用索引

  25. #修改,强制连接顺序如下,执行计划如下:
  26. mysql> desc sELECT sql_no_cache s.*,st.tag_name FROM fanwe_share as s
  27.     -> STRAIGHT_JOIN fanwe_share_category as sc ON sc.share_id = s.share_id
  28.     -> INNER JOIN fanwe_share_tags as st ON st.share_id = s.share_id
  29.     -> INNER JOIN fanwe_goods_category_tags as gct ON gct.cate_id = sc.cate_id
  30.     -> WHERE gct.cate_id in (2,14,15,16,17,18) AND s.is_index=1 AND s.status=1
  31.     -> GROUP BY s.share_id
  32.     -> ORDER BY s.share_id desc,s.sort desc limit 30;
  33. +----+-------------+-------+------+-------------------------+----------+---------+--------------------+------+----------------------------------------------+
  34. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  35. +----+-------------+-------+------+-------------------------+----------+---------+--------------------+------+----------------------------------------------+
  36. | 1 | SIMPLE | s | ref | PRIMARY,status,is_index | is_index | 2 | const | 381 | Using where; Using temporary; Using filesort |
  37. | 1 | SIMPLE | st | ref | share_id | share_id | 4 | shop520.s.share_id | 19 | |
  38. | 1 | SIMPLE | sc | ref | share_id,cate_id | share_id | 4 | shop520.s.share_id | 12 | |
  39. | 1 | SIMPLE | gct | ref | cate_id | cate_id | 2 | shop520.sc.cate_id | 13 | Using where; Using index |
  40. +----+-------------+-------+------+-------------------------+----------+---------+--------------------+------+----------------------------------------------+
  41. 4 rows in set (0.00 sec)

  42. #查询时间如下:
  43. 30 rows in set (1.61 sec)
  44. #分析:
  45. 1:已经由s表进行驱动,但是没有使用正确的索引去分组排序,所以产生了Using where; Using temporary; Using filesort,现在要把这个问题解决。
  46. 2:is_index就2个值,排序用的是share_id,新建索引需要把分组,排序字段包含进去

  47. #添加索引
  48. mysql> alter table fanwe_share add index idx_share_id_is_index2(share_id,sort,is_index);Query OK, 149462 rows affected (9.85 sec)Records: 149462 Duplicates: 0 Warnings: 0

  49. #查看执行计划:
  50. mysql> desc sELECT sql_no_cache s.*,st.tag_name FROM fanwe_share as s STRAIGHT_JOIN fanwe_share_category as sc ON sc.share_id = s.share_id INNER JOIN fanwe_share_tags as st ON st.share_id = s.share_id INNER JOIN fanwe_goods_category_tags as gct ON gct.cate_id = sc.cate_id WHERE gct.cate_id in (2,14,15,16,17,18) AND s.is_index=1 AND s.status=1 GROUP BY s.share_id ORDER BY s.share_id desc,s.sort desc limit 30;
  51. +----+-------------+-------+-------+------------------------------------------------+------------------------+---------+---------------------+------+------------------------------+
  52. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  53. +----+-------------+-------+-------+------------------------------------------------+------------------------+---------+---------------------+------+------------------------------+
  54. | 1 | SIMPLE | s | index | PRIMARY,status,is_index,idx_share_id_is_index2 | idx_share_id_is_index2 | 9 | NULL | 90 | Using where; Using temporary |
  55. | 1 | SIMPLE | st | ref | share_id | share_id | 4 | shop520.s.share_id | 19 | |
  56. | 1 | SIMPLE | sc | ref | share_id,cate_id | share_id | 4 | shop520.st.share_id | 12 | Using where |
  57. | 1 | SIMPLE | gct | ref | cate_id | cate_id | 2 | shop520.sc.cate_id | 13 | Using where; Using index |
  58. +----+-------------+-------+-------+------------------------------------------------+------------------------+---------+---------------------+------+------------------------------+
  59. 4 rows in set (0.00 sec)

  60. #额外的排序操作已经没有了。
  61. #执行时间:
  62. 30 rows in set (0.14 sec)

  63. #此库下表存储引擎是MYISAM,检查下最重要的系统参数
  64. mysql> show global variables like '%key_buffer_size%';
  65. +-----------------+----------+
  66. | Variable_name | Value |
  67. +-----------------+----------+
  68. | key_buffer_size | 16777216 |
  69. +-----------------+----------+
  70. 1 row in set (0.00 sec)

  71. #系统内存为1.6G,上面有跑PHP程序和数据库。
  72. #可以把key_buffer_size调到521M
  73. mysql> set global key_buffer_size=512*1024*1024;
  74. Query OK, 0 rows affected (0.05 sec)

阅读(3617) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~