MySQL千萬(wàn)級(jí)數(shù)據(jù)表的優(yōu)化實(shí)戰(zhàn)記錄
前言
這里先說(shuō)明一下,網(wǎng)上很多人說(shuō)阿里規(guī)定500w數(shù)據(jù)就要分庫(kù)分表。實(shí)際上,這個(gè)500w并不是定義死的,而是與MySQL的配置以及機(jī)器的硬件有關(guān)。MySQL為了提升性能,會(huì)將表的索引裝載到內(nèi)存中。但是當(dāng)表的數(shù)據(jù)到達(dá)一定的量的時(shí)候,會(huì)導(dǎo)致內(nèi)存無(wú)法存儲(chǔ)這些索引,無(wú)法存儲(chǔ)索引,就只能進(jìn)行磁盤IO,從而導(dǎo)致性能下降。
實(shí)戰(zhàn)調(diào)優(yōu)
我這里有張表,數(shù)據(jù)有1000w,目前只有一個(gè)主鍵索引
CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `uname` varchar(20) DEFAULT NULL COMMENT '賬號(hào)', `pwd` varchar(20) DEFAULT NULL COMMENT '密碼', `addr` varchar(80) DEFAULT NULL COMMENT '地址', `tel` varchar(20) DEFAULT NULL COMMENT '電話', `regtime` char(30) DEFAULT NULL COMMENT '注冊(cè)時(shí)間', `age` int(11) DEFAULT NULL COMMENT '年齡', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10000003 DEFAULT CHARSET=utf8;
查詢所有大概16s??芍^是相當(dāng)慢了。通常我們一個(gè)后臺(tái)系統(tǒng),比如這個(gè)是一個(gè)電商平臺(tái),這個(gè)是用戶表。后臺(tái)管理系統(tǒng),一般會(huì)查詢這些用戶信息,做一些操作,比如后臺(tái)直接新增用戶啊,或者刪除用戶啊這些操作。
所以這里就誕生了兩個(gè)需求,一個(gè)是查詢count,一個(gè)是分頁(yè)查詢
我們分別來(lái)測(cè)試一下count用的時(shí)間和分頁(yè)查詢所用的時(shí)間
select * from user limit 1, 10 //幾乎不用時(shí) select * from user limit 1000000, 10 //0.35s select * from user limit 5000000, 10 //1.7s select * from user limit 9000000, 10 //2.8s select count(1) from user //1.7s
從上面查詢所用時(shí)間可以看出來(lái),如果是分頁(yè)查詢的話,查詢的數(shù)據(jù)越往后用時(shí)是越長(zhǎng)的,查詢count也需要1.7s。這顯然是不符合我們的要求的。所以,這里我們就需要優(yōu)化。首先我們這里進(jìn)行索引優(yōu)化試試
首先看一下這是只有主鍵索引的執(zhí)行計(jì)劃:
alter table `user` add INDEX `sindex` (`uname`,`pwd`,`addr`,`tel`,`regtime`,`age`)
看上面的執(zhí)行計(jì)劃,雖然type是從all->index,走了sindex索引,但是實(shí)際上查詢速度并沒(méi)有發(fā)生改變。
其實(shí),創(chuàng)建聯(lián)合索引,是為了有條件查詢的時(shí)候速度更快,而不是全表查詢
select * from user where uname='6.445329111484186' //3.5s(無(wú)聯(lián)合索引) select * from user where uname='6.445329111484186' //0.003s(有聯(lián)合索引)
所以這就是有聯(lián)合索引和無(wú)索引的差距
這里基本上可以證明,加了索引和不加索引,進(jìn)行全表查詢的時(shí)候,效率就是會(huì)很慢
既然索引這個(gè)結(jié)果已經(jīng)不好使了,那就只能找其他方案了。根據(jù)我之前mysql面試?yán)锩嬷v的,count我們可以單獨(dú)存儲(chǔ)到一個(gè)表里面
CREATE TABLE `attribute` ( `id` int(11) NOT NULL, `formname` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '表名', `formcount` int(11) NOT NULL COMMENT '表總數(shù)據(jù)', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
這里說(shuō)一下,這種表一般不會(huì)查所有,只會(huì)查詢一條,所以建表的時(shí)候,可以建成hash
select formcount from attribute where formname='user' //幾乎不用時(shí)
count就進(jìn)行優(yōu)化完了。如果上面有選擇條件的話,就可以建立索引,通過(guò)走索引篩選的形式來(lái)查詢,這樣就可以不用讀這個(gè)count了。
那么,count是沒(méi)問(wèn)題了,分頁(yè)查詢優(yōu)化要如何優(yōu)化呢?這里可以使用子查詢來(lái)優(yōu)化
select * from user where id>=(select id from user limit 9000000,1) limit 10 //1.7s
其實(shí)子查詢這種寫法,判斷id,其實(shí)就是通過(guò)覆蓋索引來(lái)查詢。效率會(huì)大大增加。不過(guò)我這里測(cè)試是1.7s,以前在公司優(yōu)化這方面的時(shí)候,比這個(gè)查詢時(shí)間要低,大家也可以自己生成數(shù)據(jù)自己測(cè)試
但是如果說(shuō)數(shù)據(jù)量太大了,我還是建議走es或者進(jìn)行一些默認(rèn)選擇,count可以單獨(dú)列出來(lái)
至此,一個(gè)千萬(wàn)級(jí)的數(shù)據(jù)分頁(yè)查詢的優(yōu)化就完成了。
總結(jié)
到此這篇關(guān)于MySQL千萬(wàn)級(jí)數(shù)據(jù)表優(yōu)化的文章就介紹到這了,更多相關(guān)MySQL千萬(wàn)級(jí)數(shù)據(jù)表優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- 詳細(xì)聊聊MySQL中慢SQL優(yōu)化的方向
- 淺談MySQL之select優(yōu)化方案
- Mysql縱表轉(zhuǎn)換為橫表的方法及優(yōu)化教程
- MySql子查詢IN的執(zhí)行和優(yōu)化的實(shí)現(xiàn)
- 帶你快速搞定Mysql優(yōu)化
- mysql 數(shù)據(jù)插入優(yōu)化方法之concurrent_insert
- mysql優(yōu)化之query_cache_limit參數(shù)說(shuō)明
- MySQL優(yōu)化之如何寫出高質(zhì)量sql語(yǔ)句
- mysql查詢優(yōu)化之100萬(wàn)條數(shù)據(jù)的一張表優(yōu)化方案
- MYSQL 的10大經(jīng)典優(yōu)化案例場(chǎng)景實(shí)戰(zhàn)
相關(guān)文章
MySQL的InnoDB存儲(chǔ)引擎的數(shù)據(jù)頁(yè)結(jié)構(gòu)詳解
這篇文章主要為大家詳細(xì)介紹了MySQL的InnoDB存儲(chǔ)引擎的數(shù)據(jù)頁(yè)結(jié)構(gòu),,文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下,希望能夠給你帶來(lái)幫助2022-03-03mysql5.7 新增的json字段類型用法實(shí)例分析
這篇文章主要介紹了mysql5.7 新增的json字段類型用法,結(jié)合實(shí)例形式分析了mysql5.7 新增的json字段類型具體功能、使用方法及操作注意事項(xiàng),需要的朋友可以參考下2020-02-02簡(jiǎn)單聊一聊SQL中的union和union?all
在寫SQL的時(shí)候,偶爾會(huì)用到兩個(gè)表的數(shù)據(jù)結(jié)合在一起返回的,就需要用到UNION 和 UNION ALL,這篇文章主要給大家介紹了關(guān)于SQL中union和union?all的相關(guān)資料,需要的朋友可以參考下2023-02-02Navicat無(wú)法連接MySQL報(bào)錯(cuò)1251的解決方案
這篇文章主要為大家詳細(xì)介紹了Navicat無(wú)法連接MySQL報(bào)錯(cuò)1251的解決方案,文中解決方法介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2023-12-12MySQL數(shù)據(jù)庫(kù)中遇到no?database?selected問(wèn)題解決辦法
這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫(kù)中遇到no?database?selected問(wèn)題的解決辦法,這是MySQL數(shù)據(jù)庫(kù)的錯(cuò)誤提示,意思是沒(méi)有選擇數(shù)據(jù)庫(kù),在使用MySQL命令行操作時(shí)需要先選擇要操作的數(shù)據(jù)庫(kù),否則就會(huì)出現(xiàn)這個(gè)錯(cuò)誤,需要的朋友可以參考下2024-03-03mysql中g(shù)eneral_log日志知識(shí)點(diǎn)介紹
這篇文章主要介紹了mysql中g(shù)eneral_log日志知識(shí)點(diǎn)的介紹以及其他相關(guān)內(nèi)容,以后興趣的朋友們學(xué)習(xí)下。2019-08-08mysql 5.7.13 安裝配置方法圖文教程(win10 64位)
這篇文章主要為大家分享了win10 64位下mysql 5.7.13 安裝配置方法圖文教程,感興趣的朋友可以參考一下2017-02-02MySQL5.7安裝過(guò)程并重置root密碼的方法(shell 腳本)
由于 MySQL 5.7 版本的 root 密碼是首次啟動(dòng)時(shí)隨機(jī)生成的,并且還要求必須修改后才能使用。下面小編給大家分享使用shell 腳本完成安裝和設(shè)置新的 root 密碼的方法,一起看看吧2016-12-12