宜宾网站建设公司

MySQL中SQL语句优化的示例分析

这篇文章主要介绍MySQL中SQL语句优化的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

创新互联主营广灵网站建设的网络公司,主营网站建设方案,重庆APP软件开发,广灵h5成都小程序开发搭建,广灵网站营销推广欢迎广灵等地区企业咨询

sql如下：sql强制用了into_time索引

  1. # Time: 2017-02-14T11:35:01.594499+08:00

  2. # User@Host: oms_readonly[oms_readonly] @ [10.44.xxx.xxx] Id: 41636892

  3. # Query_time: 9.299612 Lock_time: 0.000124 Rows_sent: 20 Rows_examined: 2439330

  4. SET timestamp=1487043301;

  5. select * from customers force index(`into_time`) where `type` = 1 AND `status` < 7 AND `isarea` = 8 AND `into_time`<='2017-01-31 23:59:59' order by score desc limit 0,20;

查看表的相关状态：

  1. mysql> show table status like 'customers' \G;

  2. *************************** 1. row ***************************

  3.            Name: customers

  4.          Engine: InnoDB

  5.         Version: 10

  6.      Row_format: Dynamic

  7.            Rows: 2504609

  8.  Avg_row_length: 710

  9.     Data_length: 1780383744

  10. Max_data_length: 0

  11.    Index_length: 1253048320

  12.       Data_free: 6291456

  13.  Auto_increment: 2546101

  14.     Create_time: 2017-01-07 01:59:34

  15.     Update_time: 2017-02-14 13:58:17

  16.      Check_time: NULL

  17.       Collation: utf8_general_ci

  18.        Checksum: NULL

  19.  Create_options:

  20.         Comment:

  21. 1 row in set (0.00 sec)

表一共大约有250万行记录，查看下满足into_time<='2017-01-31 23:59:59'这个条件的有多少行

  1. mysql> select count(*) from customers where `into_time`<='2017-01-31 23:59:59';

  2. +----------+

  3. | count(*) |

  4. +----------+

  5. | 2439147 |

  6. +----------+

  7. 1 row in set (0.95 sec)


显然into_time这个列的索引已经不合适了，查看下表上都有那些索引

  1. Create Table: CREATE TABLE `customers` (

  2.   `id` int(11) NOT NULL AUTO_INCREMENT,

  3.   PRIMARY KEY (`id`),

  4.   KEY `newdata` (`newdata`),

  5.   KEY `cusname` (`cusname`),

  6.   KEY `type` (`type`,`ownerid`),

  7.   KEY `operator` (`operator`),

  8.   KEY `into_time` (`into_time`),

  9.   KEY `isarea` (`isarea`),

  10.   KEY `linkcase` (`linkcase`),

  11.   KEY `score` (`score`),

  12.   FULLTEXT KEY `fdx_cusname` (`cusname_idx`)

  13. ) ENGINE=InnoDB AUTO_INCREMENT=2546101 DEFAULT CHARSET=utf8

  14. 1 row in set (0.00 sec)

可以看到score列有索引，如果能采用这个列的索引是个比较好的选择，去掉强制索引看下执行计划



    1. mysql> explain select * from customers where `type` = 1 AND `status` < 7 AND `isarea` = 8 AND `into_time`<='2017-01-31 23:59:59' order by score desc limit 0,20;

    2. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+

    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    4. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+

    5. | 1 | SIMPLE | customers | NULL | index | type,into_time,isarea,status,idx_isarea_renew_owner,type_status | score | 2 | NULL | 270 | 0.92 | Using where |

    6. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+

    7. 1 row in set, 1 warning (0.00 sec)

可以看到用了score索引，执行时间从最9秒多，到优化后的0.0几秒。

以上是“MySQL中SQL语句优化的示例分析”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注创新互联行业资讯频道！


当前文章：MySQL中SQL语句优化的示例分析
本文地址：http://ybzwz.com/article/pehdpe.html

其他资讯