這篇文章主要介紹MySQL中SQL語句優(yōu)化的示例分析，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

創(chuàng)新互聯(lián)主營廣靈網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,重慶APP軟件開發(fā),廣靈h5成都小程序開發(fā)搭建,廣靈網(wǎng)站營銷推廣歡迎廣靈等地區(qū)企業(yè)咨詢

sql如下：sql強(qiáng)制用了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;

查看表的相關(guān)狀態(tài)：

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'這個(gè)條件的有多少行

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這個(gè)列的索引已經(jīng)不合適了，查看下表上都有那些索引

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列有索引，如果能采用這個(gè)列的索引是個(gè)比較好的選擇，去掉強(qiáng)制索引看下執(zhí)行計(jì)劃

1. 
   
   

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索引，執(zhí)行時(shí)間從最9秒多，到優(yōu)化后的0.0幾秒。

以上是“MySQL中SQL語句優(yōu)化的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

文章名稱：MySQL中SQL語句優(yōu)化的示例分析
網(wǎng)頁路徑：http://vcdvsql.cn/article34/pehdpe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、網(wǎng)站內(nèi)鏈、建站公司、域名注冊(cè)、全網(wǎng)營銷推廣、App設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)