MySQL千萬級(jí)數(shù)據(jù)表的優(yōu)化實(shí)戰(zhàn)記錄

前言

這里先說明一下，網(wǎng)上很多人說阿里規(guī)定500w數(shù)據(jù)就要分庫分表。實(shí)際上，這個(gè)500w并不是定義死的，而是與MySQL的配置以及機(jī)器的硬件有關(guān)。MySQL為了提升性能，會(huì)將表的索引裝載到內(nèi)存中。但是當(dāng)表的數(shù)據(jù)到達(dá)一定的量的時(shí)候，會(huì)導(dǎo)致內(nèi)存無法存儲(chǔ)這些索引，無法存儲(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è)是分頁查詢

我們分別來測(cè)試一下count用的時(shí)間和分頁查詢所用的時(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í)間可以看出來，如果是分頁查詢的話，查詢的數(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í)際上查詢速度并沒有發(fā)生改變。

其實(shí)，創(chuàng)建聯(lián)合索引，是為了有條件查詢的時(shí)候速度更快，而不是全表查詢

select * from user where uname='6.445329111484186' //3.5s(無聯(lián)合索引)
select * from user where uname='6.445329111484186' //0.003s(有聯(lián)合索引)

所以這就是有聯(lián)合索引和無索引的差距

這里基本上可以證明，加了索引和不加索引，進(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;

這里說一下，這種表一般不會(huì)查所有，只會(huì)查詢一條，所以建表的時(shí)候，可以建成hash

select formcount from attribute where formname='user' //幾乎不用時(shí)

count就進(jìn)行優(yōu)化完了。如果上面有選擇條件的話，就可以建立索引，通過走索引篩選的形式來查詢，這樣就可以不用讀這個(gè)count了。

那么，count是沒問題了，分頁查詢優(yōu)化要如何優(yōu)化呢？這里可以使用子查詢來優(yōu)化

select * from user where
id>=(select id from user limit 9000000,1) limit 10 //1.7s

其實(shí)子查詢這種寫法，判斷id，其實(shí)就是通過覆蓋索引來查詢。效率會(huì)大大增加。不過我這里測(cè)試是1.7s，以前在公司優(yōu)化這方面的時(shí)候，比這個(gè)查詢時(shí)間要低，大家也可以自己生成數(shù)據(jù)自己測(cè)試

但是如果說數(shù)據(jù)量太大了，我還是建議走es或者進(jìn)行一些默認(rèn)選擇，count可以單獨(dú)列出來

至此，一個(gè)千萬級(jí)的數(shù)據(jù)分頁查詢的優(yōu)化就完成了。

總結(jié)

到此這篇關(guān)于MySQL千萬級(jí)數(shù)據(jù)表優(yōu)化的文章就介紹到這了,更多相關(guān)MySQL千萬級(jí)數(shù)據(jù)表優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！