前言
這里先說明一下，網上很多人說阿里規定500w數據就要分庫分表。實際上，這個500w并不是定義死的，而是與MySQL的配置以及機器的硬件有關。MySQL為了提升性能，會將表的索引裝載到內存中。但是當表的數據到達一定的量的時候，會導致內存無法存儲這些索引，無法存儲索引，就只能進行磁盤IO，從而導致性能下降。
實戰調優
我這里有張表，數據有1000w，目前只有一個主鍵索引
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`uname` varchar(20) DEFAULT NULL COMMENT '賬號',
`pwd` varchar(20) DEFAULT NULL COMMENT '密碼',
`addr` varchar(80) DEFAULT NULL COMMENT '地址',
`tel` varchar(20) DEFAULT NULL COMMENT '電話',
`regtime` char(30) DEFAULT NULL COMMENT '注冊時間',
`age` int(11) DEFAULT NULL COMMENT '年齡',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000003 DEFAULT CHARSET=utf8;
查詢所有大概16s。可謂是相當慢了。通常我們一個后臺系統，比如這個是一個電商平臺，這個是用戶表。后臺管理系統，一般會查詢這些用戶信息，做一些操作，比如后臺直接新增用戶啊，或者刪除用戶啊這些操作。
所以這里就誕生了兩個需求，一個是查詢count，一個是分頁查詢
我們分別來測試一下count用的時間和分頁查詢所用的時間
select * from user limit 1, 10 //幾乎不用時
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
從上面查詢所用時間可以看出來，如果是分頁查詢的話，查詢的數據越往后用時是越長的，查詢count也需要1.7s。這顯然是不符合我們的要求的。所以，這里我們就需要優化。首先我們這里進行索引優化試試
首先看一下這是只有主鍵索引的執行計劃：
alter table `user` add INDEX `sindex` (`uname`,`pwd`,`addr`,`tel`,`regtime`,`age`)
看上面的執行計劃，雖然type是從all->index,走了sindex索引，但是實際上查詢速度并沒有發生改變。
其實，創建聯合索引，是為了有條件查詢的時候速度更快，而不是全表查詢
select * from user where uname='6.445329111484186' //3.5s(無聯合索引)
select * from user where uname='6.445329111484186' //0.003s(有聯合索引)
所以這就是有聯合索引和無索引的差距
這里基本上可以證明，加了索引和不加索引，進行全表查詢的時候，效率就是會很慢
既然索引這個結果已經不好使了，那就只能找其他方案了。根據我之前mysql面試里面講的，count我們可以單獨存儲到一個表里面
CREATE TABLE `attribute` (
`id` int(11) NOT NULL,
`formname` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '表名',
`formcount` int(11) NOT NULL COMMENT '表總數據',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
這里說一下，這種表一般不會查所有，只會查詢一條，所以建表的時候，可以建成hash
select formcount from attribute where formname='user' //幾乎不用時
count就進行優化完了。如果上面有選擇條件的話，就可以建立索引，通過走索引篩選的形式來查詢，這樣就可以不用讀這個count了。
那么，count是沒問題了，分頁查詢優化要如何優化呢？這里可以使用子查詢來優化
select * from user where
id>=(select id from user limit 9000000,1) limit 10 //1.7s
其實子查詢這種寫法，判斷id，其實就是通過覆蓋索引來查詢。效率會大大增加。不過我這里測試是1.7s，以前在公司優化這方面的時候，比這個查詢時間要低，大家也可以自己生成數據自己測試
但是如果說數據量太大了，我還是建議走es或者進行一些默認選擇，count可以單獨列出來
至此，一個千萬級的數據分頁查詢的優化就完成了。
總結
到此這篇關于MySQL千萬級數據表優化的文章就介紹到這了,更多相關MySQL千萬級數據表優化內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- 詳細聊聊MySQL中慢SQL優化的方向
- 淺談MySQL之select優化方案
- Mysql縱表轉換為橫表的方法及優化教程
- MySql子查詢IN的執行和優化的實現
- 帶你快速搞定Mysql優化
- mysql 數據插入優化方法之concurrent_insert
- mysql優化之query_cache_limit參數說明
- MySQL優化之如何寫出高質量sql語句
- mysql查詢優化之100萬條數據的一張表優化方案
- MYSQL 的10大經典優化案例場景實戰
