快捷導(dǎo)航

MySQL的id關(guān)聯(lián)和索引使用的實(shí)際優(yōu)化案例

更新時(shí)間：2015年05月08日 09:28:52 作者：羅龍九

這篇文章主要介紹了MySQL的id關(guān)聯(lián)實(shí)際優(yōu)化案例,關(guān)聯(lián)和索引一直是MySQL常見的可優(yōu)化大塊兒,需要的朋友可以參考下

昨晚收到客服MM電話，一用戶反饋數(shù)據(jù)庫響應(yīng)非常慢，手機(jī)收到load異常報(bào)警，登上主機(jī)后發(fā)現(xiàn)大量sql執(zhí)行非常慢，有的執(zhí)行時(shí)間超過了10s
優(yōu)化點(diǎn)一：

SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10;

表結(jié)構(gòu)為：

CREATE TABLE `game_shares_buy_list` (
`tran_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`………..'
PRIMARY KEY (`tran_id`),
KEY `ind_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3144200 DEFAULT CHARSET=utf8;

執(zhí)行計(jì)劃：

root@127.0.0.1 : sitevipdb 09:10:22> explain SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10;
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
| 1 | SIMPLE | game_shares_buy_list | index | NULL | PRIMARY | 4 | NULL | 10 | Using where |
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
1 row in set (0.00 sec)

分析該sql的執(zhí)行計(jì)劃，由于tran_id是表的主鍵，所以查詢根據(jù)主鍵降序順序掃描，這樣就可以不用排序，
然后在過濾條件price>2.00的記錄，看上去這個(gè)執(zhí)行計(jì)劃貌似非常好，如果查詢掃描到了滿足條件的10條記錄，就會(huì)停止掃描；
但是這里有個(gè)問題，如果表中有大量的記錄是不符合2.00的，意味查詢就需要掃描非常多的記錄，才能找到符合條件的10條：

root@127.0.0.1 : sitevipdb 09:17:23> select price,count(*) as cnt from `game_shares_buy_list` group by price order by cnt desc limit 10;
+——-+——-+
| price | cnt |
+——-+——-+
| 1.75 | 39101 |
| 1.68 | 38477 |
| 1.71 | 34869 |
| 1.66 | 34849 |
| 1.72 | 34718 |
| 1.70 | 33996 |
| 1.76 | 32527 |
| 1.69 | 27189 |
| 1.61 | 25694 |
| 1.25 | 25450 |

可以看到表中有大量的記錄不是2.00的，所以這個(gè)時(shí)候不能在根據(jù)主鍵順序掃描，在過濾記錄；
那么是否需要在price建立一個(gè)索引：

root@127.0.0.1 : sitevipdb 09:09:01> select count(*) from `game_shares_buy_list` where price>'2′;
+———-+
| count(*) |
+———-+
| 4087 |
+———-+
root@127.0.0.1 : sitevipdb 09:17:31> select count(*) from `game_shares_buy_list` ;
+———-+
| count(*) |
+———-+
| 1572100 |

從上面price的數(shù)據(jù)分布可以看出，price的分布相對(duì)還是比較集中的，如果在price建立索引，mysql也有可能認(rèn)為由于需要回表的記錄過多，
同時(shí)需要額外的排序，而不選擇在price上的索引：

root@127.0.0.1 : sitevipdb 09:24:53> alter table game_shares_buy_list add index ind_game_shares_buy_list_price(price);
Query OK, 0 rows affected (5.79 sec)

20155892702556.jpg (965×121)

可以看到優(yōu)化器雖然注意到了我們新加的索引，但是最終還是選擇了primary來掃描；
所以這個(gè)時(shí)候我們加上去的索引沒有產(chǎn)生效果，數(shù)據(jù)庫負(fù)載依然很高，如果強(qiáng)制走price上的索引，效果會(huì)這樣：

root@127.0.0.1 : sitevipdb 09:35:38> SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10;
。。。。。
10 rows in set (7.06 sec)

root@127.0.0.1 : sitevipdb 09:36:00> SELECT * FROM `sitevipdb`.`game_shares_buy_list` force index(ind_game_shares_buy_list_price) WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10;
。。。。
10 rows in set (1.01 sec)

可以看到如果強(qiáng)制走索引，時(shí)間已經(jīng)明顯下降了，但是還是有些慢，能不能在快一點(diǎn)？其實(shí)我們需要掃描的記錄只有10條，但查詢?cè)谌〉眠@10條記錄的時(shí)候需要掃描大量無效的記錄

20155892746760.jpg (960×224)

怎么降低這個(gè)數(shù)據(jù)：其實(shí)只要改寫一下sql就可以，我們先從索引中得到滿足條件的10個(gè)id，在回表進(jìn)行關(guān)聯(lián)：

root@127.0.0.1 : sitevipdb 09:44:45> select * from game_shares_buy_list t1,
-> ( SELECT tran_id FROM sitevipdb.game_shares_buy_list WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10) t2
-> where t1.tran_id=t2.tran_id;
10 rows in set (0.00 sec)

可以看到執(zhí)行時(shí)間已經(jīng)不在秒級(jí)別了，和客戶電話溝通后，很愿意這樣改寫sql。

—這里看到是order by tran_id是要額外排序的，索引也可以這樣來建立消除排序(tran_id,price)這樣可以消除排序，同時(shí)可以利用order by desc/asc +limit M,N的優(yōu)化。

優(yōu)化點(diǎn)二：

CREATE TABLE `game_session` (
`session_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int(10) unsigned NOT NULL DEFAULT '0′,
`client_ip` varchar(16) DEFAULT NULL,
`session_data` text,
…………………….
PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查詢?yōu)閟elect `session_data`, `session_expires` from `game_session` where session_id='xxx'出現(xiàn)大量等待情況
同時(shí)該表的insert，也有等待的現(xiàn)象；
可以看到這個(gè)表結(jié)構(gòu)設(shè)計(jì)是有些問題的，咨詢了客戶后，可以改為下面結(jié)構(gòu)：

CREATE TABLE `game_session` (
id int auto_increment,
`session_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int(10) unsigned NOT NULL DEFAULT '0′,
`client_ip` varchar(16) DEFAULT NULL,
`session_data` varchar(200),
PRIMARY KEY (id),
key ind_session_id(session_id,session_data, session_expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

小結(jié)：

新增自增主鍵id作為表的主鍵，這樣對(duì)插入的性能提升是很好的，同時(shí)也降低了表主鍵的大小；
將session_data由text改為了varchar(200),咨詢了客戶后，這個(gè)字段可以不用大字段存儲(chǔ)，同時(shí)有text改為了varchar，就可以冗余到索引中；
由于查詢可以使用覆蓋索引來完成，所以將查詢的3個(gè)字段冗余到索引中，查詢通過索引完成，不用回表

您可能感興趣的文章:

MySQL

相關(guān)文章

這篇文章介紹了SQL?Server的全文搜索功能，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
2023-04-04

詳解MySQL8.0原子DDL語法

這篇文章主要介紹了詳解MySQL8.0原子DDL語法的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下

2021-03-03

mysql數(shù)據(jù)庫基礎(chǔ)知識(shí)點(diǎn)與操作小結(jié)

這篇文章主要介紹了mysql數(shù)據(jù)庫基礎(chǔ)知識(shí)點(diǎn)與操作,總結(jié)分析了mysql數(shù)據(jù)庫修改數(shù)據(jù)表、增刪改查及數(shù)據(jù)庫函數(shù)基本功能,需要的朋友可以參考下

2020-01-01

Windows?Server?2019?MySQL數(shù)據(jù)庫的安裝與配置理論+遠(yuǎn)程連接篇

mysql是一款關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由MySQL?AB公司開發(fā)，目前屬于Oracle旗下產(chǎn)品，MySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一。MySQL也是一款開源的SQL數(shù)據(jù)庫管理系統(tǒng)，是眾多小型網(wǎng)站作為網(wǎng)站數(shù)據(jù)庫的首選數(shù)據(jù)庫

2023-05-05

深入分析mysql為什么不推薦使用uuid或者雪花id作為主鍵

這篇文章主要介紹了深入分析mysql為什么不推薦使用uuid或者雪花id作為主鍵,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

2020-09-09

30個(gè)mysql千萬級(jí)大數(shù)據(jù)SQL查詢優(yōu)化技巧詳解

本文總結(jié)了30個(gè)mysql千萬級(jí)大數(shù)據(jù)SQL查詢優(yōu)化技巧,特別適合大數(shù)據(jù)里的MYSQL使用

2018-03-03

MySQL自動(dòng)停機(jī)的問題處理實(shí)戰(zhàn)記錄

這篇文章主要給大家介紹了關(guān)于MySQL自動(dòng)停機(jī)的問題處理，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧

2020-05-05