MySQL中SQL语句优化的示例分析
发布时间：2021-12-26 12:14:55 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍MySQL中SQL语句优化的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！ sql如下：sql强制用了into_time索引 # Time: 2017-02-14T11:35:01.594499+08:00 # User@Host: oms_readonly[oms_readonly] @ [10.44
|
这篇文章主要介绍MySQL中SQL语句优化的示例分析，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！ sql如下：sql强制用了into_time索引 # Time: 2017-02-14T11:35:01.594499+08:00 # User@Host: oms_readonly[oms_readonly] @ [10.44.xxx.xxx] Id: 41636892 # Query_time: 9.299612 Lock_time: 0.000124 Rows_sent: 20 Rows_examined: 2439330 SET timestamp=1487043301; 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; 查看表的相关状态： mysql> show table status like 'customers' G; *************************** 1. row *************************** Name: customers Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 2504609 Avg_row_length: 710 Data_length: 1780383744 Max_data_length: 0 Index_length: 1253048320 Data_free: 6291456 Auto_increment: 2546101 Create_time: 2017-01-07 01:59:34 Update_time: 2017-02-14 13:58:17 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) 表一共大约有250万行记录，查看下满足into_time<='2017-01-31 23:59:59'这个条件的有多少行 mysql> select count(*) from customers where `into_time`<='2017-01-31 23:59:59'; +----------+ | count(*) | +----------+ | 2439147 | +----------+ 1 row in set (0.95 sec) 显然into_time这个列的索引已经不合适了，查看下表上都有那些索引 Create Table: CREATE TABLE `customers` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), KEY `newdata` (`newdata`), KEY `cusname` (`cusname`), KEY `type` (`type`,`ownerid`), KEY `operator` (`operator`), KEY `into_time` (`into_time`), KEY `isarea` (`isarea`), KEY `linkcase` (`linkcase`), KEY `score` (`score`), FULLTEXT KEY `fdx_cusname` (`cusname_idx`) ) ENGINE=InnoDB AUTO_INCREMENT=2546101 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 可以看到score列有索引，如果能采用这个列的索引是个比较好的选择，去掉强制索引看下执行计划 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; +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+ | 1 | SIMPLE | customers | NULL | index | type,into_time,isarea,status,idx_isarea_renew_owner,type_status | score | 2 | NULL | 270 | 0.92 | Using where | +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 可以看到用了score索引，执行时间从最9秒多，到优化后的0.0几秒。 以上是“MySQL中SQL语句优化的示例分析”这篇文章的所有内容，感谢各位的阅读！ （编辑：临夏站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐