MySQL的id關(guān)聯(lián)和索引使用的實(shí)際優(yōu)化案例
昨晚收到客服MM電話,一用戶反饋數(shù)據(jù)庫響應(yīng)非常慢,手機(jī)收到load異常報警,登上主機(jī)后發(fā)現(xiàn)大量sql執(zhí)行非常慢,有的執(zhí)行時間超過了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í)行計劃:
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í)行計劃,由于tran_id是表的主鍵,所以查詢根據(jù)主鍵降序順序掃描,這樣就可以不用排序,
然后在過濾條件price>2.00的記錄,看上去這個執(zhí)行計劃貌似非常好,如果查詢掃描到了滿足條件的10條記錄,就會停止掃描;
但是這里有個問題,如果表中有大量的記錄是不符合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的,所以這個時候不能在根據(jù)主鍵順序掃描,在過濾記錄;
那么是否需要在price建立一個索引:
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的分布相對還是比較集中的,如果在price建立索引,mysql也有可能認(rèn)為由于需要回表的記錄過多,
同時需要額外的排序,而不選擇在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)
可以看到優(yōu)化器雖然注意到了我們新加的索引,但是最終還是選擇了primary來掃描;
所以這個時候我們加上去的索引沒有產(chǎn)生效果,數(shù)據(jù)庫負(fù)載依然很高,如果強(qiáng)制走price上的索引,效果會這樣:
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)制走索引,時間已經(jīng)明顯下降了,但是還是有些慢,能不能在快一點(diǎn)?其實(shí)我們需要掃描的記錄只有10條,但查詢在取得這10條記錄的時候需要掃描大量無效的記錄
怎么降低這個數(shù)據(jù):其實(shí)只要改寫一下sql就可以,我們先從索引中得到滿足條件的10個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í)行時間已經(jīng)不在秒級別了,和客戶電話溝通后,很愿意這樣改寫sql。
—這里看到是order by tran_id是要額外排序的,索引也可以這樣來建立消除排序(tran_id,price)這樣可以消除排序,同時可以利用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)大量等待情況
同時該表的insert,也有等待的現(xiàn)象;
可以看到這個表結(jié)構(gòu)設(shè)計是有些問題的,咨詢了客戶后,可以改為下面結(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作為表的主鍵,這樣對插入的性能提升是很好的,同時也降低了表主鍵的大?。?/li>
- 將session_data由text改為了varchar(200),咨詢了客戶后,這個字段可以不用大字段存儲,同時有text改為了varchar,就可以冗余到索引中;
- 由于查詢可以使用覆蓋索引來完成,所以將查詢的3個字段冗余到索引中,查詢通過索引完成,不用回表
- MySQL如何基于Explain關(guān)鍵字優(yōu)化索引功能
- MySQL中or、in、union與索引優(yōu)化詳析
- MySQL中索引優(yōu)化distinct語句及distinct的多字段操作
- 探究MySQL優(yōu)化器對索引和JOIN順序的選擇
- Mysql limit 優(yōu)化,百萬至千萬級快速分頁 復(fù)合索引的引用并應(yīng)用于輕量級框架
- Mysql limit 優(yōu)化,百萬至千萬級快速分頁 復(fù)合索引的引用并應(yīng)用于輕量級框架
- MySQL 8 新特性之Invisible Indexes
- MySQL8.0?索引優(yōu)化invisible?index詳情
相關(guān)文章
SQL?Server實(shí)現(xiàn)全文搜索查詢詳解
這篇文章介紹了SQL?Server的全文搜索功能,文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-04-04mysql數(shù)據(jù)庫基礎(chǔ)知識點(diǎn)與操作小結(jié)
這篇文章主要介紹了mysql數(shù)據(jù)庫基礎(chǔ)知識點(diǎn)與操作,總結(jié)分析了mysql數(shù)據(jù)庫修改數(shù)據(jù)表、增刪改查及數(shù)據(jù)庫函數(shù)基本功能,需要的朋友可以參考下2020-01-01Windows?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ì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-09-0930個mysql千萬級大數(shù)據(jù)SQL查詢優(yōu)化技巧詳解
本文總結(jié)了30個mysql千萬級大數(shù)據(jù)SQL查詢優(yōu)化技巧,特別適合大數(shù)據(jù)里的MYSQL使用2018-03-03MySQL自動停機(jī)的問題處理實(shí)戰(zhàn)記錄
這篇文章主要給大家介紹了關(guān)于MySQL自動停機(jī)的問題處理,文中通過示例代碼介紹的非常詳細(xì),對大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2020-05-0521條MySQL優(yōu)化建議(經(jīng)驗(yàn)總結(jié))
這篇文章主要介紹了21條MySQL優(yōu)化建議,均來自個人的實(shí)戰(zhàn)經(jīng)驗(yàn)總結(jié),需要的朋友可以參考下2014-07-07MySQL授權(quán)命令grant的使用方法小結(jié)
這篇文章主要介紹了MySQL授權(quán)命令grant的使用方法,本文實(shí)例,運(yùn)行于?MySQL?5.0?及以上版本,介紹了MySQL?賦予用戶權(quán)限命令的簡單格式,本文給大家介紹的非常詳細(xì),需要的朋友參考下吧2021-12-12