MySQL索引及優(yōu)化

防偽碼：路曼曼其修遠(yuǎn)兮，吾將上下而求索。

一、MySQL 性能優(yōu)化之-影響性能的因素

1. 商業(yè)需求的影響

不合理需求造成資源投入產(chǎn)出比過(guò)低，這里我們就用一個(gè)看上去很簡(jiǎn)單的功能來(lái)分析一下。

需求：一個(gè)論壇帖子總量的統(tǒng)計(jì)，附加要求：實(shí)時(shí)更新

從功能上來(lái)看非常容易實(shí)現(xiàn)，執(zhí)行一條 SELECT COUNT(*) from 表名 的 Query 就可以得到結(jié)

果。但是，如果我們采用不是 MyISAM 存儲(chǔ)引擎，而是使用的 Innodb 的存儲(chǔ)引擎，那么大

家可以試想一下，如果存放帖子的表中已經(jīng)有上千萬(wàn)的帖子的時(shí)候，執(zhí)行這條 Query 語(yǔ)句

需要多少成本？恐怕再好的硬件設(shè)備，恐怕都不可能在 10 秒之內(nèi)完成一次查詢(xún)吧

注：沒(méi)有 where 的 count(*)使用 MyISAM 要比 InnoDB 快得多。因?yàn)?MyISAM 內(nèi)置了一個(gè)計(jì)

數(shù)器，count(*)時(shí)它直接從計(jì)數(shù)器中讀，而 InnoDB 必須掃描全表。所以在 InnoDB 上執(zhí)行

count(*)時(shí)一般要伴隨 where，且 where 中要包含主鍵以外的索引列。

既然這樣查詢(xún)不行，那我們是不是該專(zhuān)門(mén)為這個(gè)功能建一個(gè)表，就只有一個(gè)字段，一條記錄，

就存放這個(gè)統(tǒng)計(jì)量，每次有新的帖子產(chǎn)生的時(shí)候，都將這個(gè)值增加 1，這樣我們每次都只需

要查詢(xún)這個(gè)表就可以得到結(jié)果了，這個(gè)效率肯定能夠滿(mǎn)足要求了。確實(shí)，查詢(xún)效率肯定能夠

滿(mǎn)足要求，可是如果帖子產(chǎn)生很快，在高峰時(shí)期可能每秒就有幾十甚至上百個(gè)帖子新增操作

的時(shí)候，恐怕這個(gè)統(tǒng)計(jì)表又要成為大家的噩夢(mèng)了。要么因?yàn)椴l(fā)的問(wèn)題造成統(tǒng)計(jì)結(jié)果的不準(zhǔn)

確，要么因?yàn)殒i資源爭(zhēng)用嚴(yán)重造成整體性能的大幅度下降。

其實(shí)這里問(wèn)題的焦點(diǎn)不應(yīng)該是實(shí)現(xiàn)這個(gè)功能的技術(shù)細(xì)節(jié)，而是在于這個(gè)功能的附加要求“實(shí)

時(shí)更新”上面。當(dāng)一個(gè)論壇的帖子數(shù)量很大了之后，到底有多少人會(huì)關(guān)注這個(gè)統(tǒng)計(jì)數(shù)據(jù)是否

是實(shí)時(shí)變化的？有多少人在乎這個(gè)數(shù)據(jù)在短時(shí)間內(nèi)的不精確性？恐怕不會(huì)有人會(huì)盯著這個(gè)

統(tǒng)計(jì)數(shù)字并追究當(dāng)自己發(fā)了一個(gè)帖子然后回頭刷新頁(yè)面發(fā)現(xiàn)這個(gè)統(tǒng)計(jì)數(shù)字沒(méi)有加 1 吧？所

以只要去掉了這個(gè)“實(shí)時(shí)更新”的附加條件，就可以非常容易的實(shí)現(xiàn)這個(gè)功能了。就像之前

所提到的那樣，通過(guò)創(chuàng)建一個(gè)統(tǒng)計(jì)表，然后通過(guò)一個(gè)定時(shí)任務(wù)每隔一定時(shí)間段去更新一次里

面的統(tǒng)計(jì)值，這樣既可以解決統(tǒng)計(jì)值查詢(xún)的效率問(wèn)題，又可以保證不影響新發(fā)貼的效率，一

舉兩得。

2.系統(tǒng)架構(gòu)及實(shí)現(xiàn)的影響

所有數(shù)據(jù)都是適合在數(shù)據(jù)庫(kù)中存放的嗎？數(shù)據(jù)庫(kù)為我們提供了太多的功能，反而讓很多并不

是太了解數(shù)據(jù)庫(kù)的人錯(cuò)誤的使用了數(shù)據(jù)庫(kù)的很多并不是太擅長(zhǎng)或者對(duì)性能影響很大的功能，

最后卻全部怪罪到數(shù)據(jù)庫(kù)身上。

實(shí)際上，以下幾類(lèi)數(shù)據(jù)都是不適合在數(shù)據(jù)庫(kù)中存放的：

1） 二進(jìn)制多媒體數(shù)據(jù)

這種數(shù)據(jù)主要包括圖片，音頻、視頻和其他一些相關(guān)的二進(jìn)制文件。將二進(jìn)制多媒體數(shù)據(jù)存

放在數(shù)據(jù)庫(kù)中，一個(gè)問(wèn)題是數(shù)據(jù)庫(kù)空間資源耗用非常嚴(yán)重，另一個(gè)問(wèn)題是這些數(shù)據(jù)的存儲(chǔ)很

消耗數(shù)據(jù)庫(kù)主機(jī)的 CPU 資源。這些數(shù)據(jù)的處理本不是數(shù)據(jù)庫(kù)的優(yōu)勢(shì)，如果我們硬要將他們

塞入數(shù)據(jù)庫(kù)，肯定會(huì)造成數(shù)據(jù)庫(kù)的處理資源消耗嚴(yán)重。

2）超大文本數(shù)據(jù)

對(duì)于 5.0.3 之前的 MySQL 版本，VARCHAR 類(lèi)型的數(shù)據(jù)最長(zhǎng)只能存放 255 個(gè)字節(jié)，如果需

要存儲(chǔ)更長(zhǎng)的文本數(shù)據(jù)到一個(gè)字段，我們就必須使用 TEXT 類(lèi)型（最大可存放 64KB）的字

段，甚至是更大的 LONGTEXT 類(lèi)型（最大 4GB）。而 TEXT 類(lèi)型數(shù)據(jù)的處理性能要遠(yuǎn)比 VARCHAR

類(lèi)型數(shù)據(jù)的處理性能低下很多。從 5.0.3 版本開(kāi)始，VARCHAR 類(lèi)型的最大長(zhǎng)度被調(diào)整到 64KB

了，所以，超大文本數(shù)據(jù)存放在數(shù)據(jù)庫(kù)中不僅會(huì)帶來(lái)性能低下的問(wèn)題，還會(huì)帶來(lái)空間占用的

浪費(fèi)問(wèn)題。

是否合理的利用了應(yīng)用層 Cache 機(jī)制？

對(duì)于 Web 應(yīng)用，活躍數(shù)據(jù)的數(shù)據(jù)量總是不會(huì)特別的大，有些活躍數(shù)據(jù)更是很少變化。對(duì)于

未經(jīng)允許不得轉(zhuǎn)載傳播--陳英宏

博客地址：hongge.blog.51cto.com

這類(lèi)數(shù)據(jù)，我們是否有必要每次需要的時(shí)候都到數(shù)據(jù)庫(kù)中去查詢(xún)呢？如果我們能夠?qū)⒆兓?/span>

對(duì)較少的部分活躍數(shù)據(jù)通過(guò)應(yīng)用層的 Cache 機(jī)制 Cache 到內(nèi)存中，對(duì)性能的提升肯定是成

數(shù)量級(jí)的，而且由于是活躍數(shù)據(jù)，對(duì)系統(tǒng)整體的性能影響也會(huì)很大。

3.查詢(xún)語(yǔ)句對(duì)性能的影響

SQL 語(yǔ)句的優(yōu)劣是對(duì)性能有影響的，每個(gè) SQL 語(yǔ)句在優(yōu)化之前和優(yōu)化之后的性能差異也是

各不相同。

在數(shù)據(jù)庫(kù)管理軟件中，最大的性能瓶頸就是在于磁盤(pán) IO，也就是數(shù)據(jù)的存取操作上面。而

對(duì)于同一份數(shù)據(jù)，當(dāng)我們以不同方式去尋找其中的某一點(diǎn)內(nèi)容的時(shí)候，所需要讀取的數(shù)據(jù)量

可能會(huì)有天壤之別，所消耗的資源也自然是區(qū)別很大。

功能完全相同的兩條 SQL 的在性能方面的差異。

我們?cè)趫?zhí)行 sql 語(yǔ)句時(shí)可以用 explain 來(lái)查看執(zhí)行計(jì)劃：

mysql> explain

-> select stuid,stuname,cardid from tb1 where stuid between 3000 and 5000

-> order by stuid desc

-> limit 20 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tb1

partitions: NULL

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: NULL

rows: 3678

filtered: 100.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

還可以打開(kāi) mysql 的 profiling 功能，來(lái)查看 sql 的實(shí)際執(zhí)行計(jì)劃

mysql> set profiling=1;

mysql> select stuid,stuname,cardid from tb1 where stuid between 3000 and 5000 order by stuid

desc limit 5\G

mysql>show profile;

通過(guò)執(zhí)行“SHOW PROFILE” 命令獲取當(dāng)前系統(tǒng)中保存的多個(gè) Query 的 profile 的概要信息。;

mysql> show profile CPU,BLOCK IO for query 1;

4.數(shù)據(jù)庫(kù) Schema 設(shè)計(jì)對(duì)性能的影響

5.硬件選擇對(duì)性能的影響

首先，數(shù)據(jù)庫(kù)主機(jī)是存取數(shù)據(jù)的地方，所以數(shù)據(jù)庫(kù)主機(jī)的 IO 性能肯定是需要最優(yōu)先考慮的

一個(gè)因素，這一點(diǎn)不管是什么類(lèi)型的數(shù)據(jù)庫(kù)應(yīng)用都是適用的。在主機(jī)中決定 IO 性能部件主

要由磁盤(pán)和內(nèi)存所決定，當(dāng)然也包括各種與 IO 相關(guān)的板卡。

其次，由于數(shù)據(jù)庫(kù)主機(jī)和普通的應(yīng)用程序服務(wù)器相比，資源要相對(duì)集中很多，單臺(tái)主機(jī)上所

需要進(jìn)行的計(jì)算量自然也就比較多，所以數(shù)據(jù)庫(kù)主機(jī)的 CPU 處理能力也不能忽視。

最后，由于數(shù)據(jù)庫(kù)負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)，與各應(yīng)用程序的交互中傳遞的數(shù)據(jù)量比其他各類(lèi)服務(wù)器

都要多，所以數(shù)據(jù)庫(kù)主機(jī)的網(wǎng)絡(luò)設(shè)備的性能也可能會(huì)成為系統(tǒng)的瓶頸。

所以，數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的優(yōu)化，實(shí)際上是一個(gè)需要多方面配合，多方面優(yōu)化的才能產(chǎn)生根本

性改善的事情。簡(jiǎn)單來(lái)說(shuō)，可以通過(guò)下面三句話(huà)來(lái)簡(jiǎn)單的概括數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的性能優(yōu)化：

商業(yè)需求合理化，系統(tǒng)架構(gòu)最優(yōu)化，邏輯實(shí)現(xiàn)精簡(jiǎn)化，硬件設(shè)施理性化。

二、MySQL 性能優(yōu)化之-索引

關(guān)于 MySQL索引的好處，如果正確合理設(shè)計(jì)并且使用索引的 MySQL 是一輛蘭博基尼的話(huà)，

那么沒(méi)有設(shè)計(jì)和使用索引的 MySQL 就是一個(gè)人力三輪車(chē)。對(duì)于沒(méi)有索引的表，單表查詢(xún)可

能幾十萬(wàn)數(shù)據(jù)就是瓶頸，而通常大型網(wǎng)站單日就可能會(huì)產(chǎn)生幾十萬(wàn)甚至幾百萬(wàn)的數(shù)據(jù)，沒(méi)有

索引查詢(xún)會(huì)變的非常緩慢。

做一個(gè)簡(jiǎn)單測(cè)試，假如我們創(chuàng)建了一個(gè) tb1 表，向表中插入 20000 行數(shù)據(jù)，表的創(chuàng)建和數(shù)據(jù)

插入用如下腳本實(shí)現(xiàn)

[root@localhost ~]# cat mysql3.sh

#!/bin/bash

HOSTNAME="localhost"

PORT="3306"

USERNAME="root"

PASSWORD="123.abc"

DBNAME="test1"

TABLENAME="tb1"

#create database

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "drop database if exists

${DBNAME}"

create_db_sql="create database if not exists ${DBNAME}"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

#create table

create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname

varchar(20)  not  null,stusex  char(1)  not  null,cardid  varchar(20)  not  null,birthday

datetime,entertime datetime,address varchar(100) default null)"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e

"${create_table_sql}"

#insert data to table

i=1

while [ $i -le 20000 ]

do

insert_sql="insert  into  ${TABLENAME}  values

($i,'zhangsan','1','1234567890123456','1999-10-10','2016-9-3','zhongguo  beijingshi

changpinqu')"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e

"${insert_sql}"

let i++

done

#select data

select_sql="select count(*) from ${TABLENAME}"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e

"${select_sql}"

再手動(dòng)插入一行數(shù)據(jù)，如

mysql> insert into tb1 values

(20001,'admin','0','123456789009877','2000-1-1','2016-9-1','wwwwwwwwwwwww');

下面開(kāi)始測(cè)試，查詢(xún) stuname=’admin’的記錄

情況 1：stuname 列上沒(méi)有創(chuàng)建索引的情況

情況 2：stuname 列上創(chuàng)建索引的情況再查詢(xún)

在查找 stuname="admin"的記錄時(shí)，如果在 stuname 上已經(jīng)建立了索引，MySQL 無(wú)須任何

掃描全表，即準(zhǔn)確可找到該記錄。相反，MySQL 會(huì)掃描所有記錄。

所以在數(shù)據(jù)庫(kù)表中，對(duì)字段建立索引可以大大提高查詢(xún)速度。

索引是在存儲(chǔ)引擎中實(shí)現(xiàn)的，而不是在服務(wù)器層中實(shí)現(xiàn)的。所以，每種存儲(chǔ)引擎的索引都不一定完全相同，

并不是所有的存儲(chǔ)引擎都支持所有的索引類(lèi)型。

索引概述：

什么是索引？

索引（Index）是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，這是一種比較官方的說(shuō)法。它的存

在形式是文件。索引能夠幫助我們快速定位數(shù)據(jù)。 更通俗的說(shuō)，數(shù)據(jù)庫(kù)索引好比是一本書(shū)

前面的目錄，能加快數(shù)據(jù)庫(kù)的查詢(xún)速度。

索引的數(shù)據(jù)結(jié)構(gòu)

這里主介紹 B-tree 索引的結(jié)構(gòu)

如上圖，是一顆 b+樹(shù)，這里只說(shuō)一些重點(diǎn)，淺藍(lán)色的塊我們稱(chēng)之為一個(gè)磁盤(pán)塊，可以看到

每個(gè)磁盤(pán)塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），如磁盤(pán)塊 1 包含數(shù)據(jù)項(xiàng) 17

和 35，包含指針 P1、P2、P3，P1 表示小于 17 的磁盤(pán)塊，P2 表示在 17 和 35 之間的磁盤(pán)塊，

P3 表示大于 35 的磁盤(pán)塊。真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即 3、5、9、10、13、15、28、29、

36、60、75、79、90、99。非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)

項(xiàng)和指針，如 17、35 并不真實(shí)存在于數(shù)據(jù)表中。

b+樹(shù)的查找過(guò)程

如圖所示，如果要查找數(shù)據(jù)項(xiàng) 29，那么首先會(huì)把磁盤(pán)塊 1 由磁盤(pán)加載到內(nèi)存，此時(shí)發(fā)生一

次 IO，在內(nèi)存中用二分查找確定 29 在 17 和 35 之間，鎖定磁盤(pán)塊 1 的 P2 指針，內(nèi)存時(shí)間

因?yàn)榉浅６蹋ㄏ啾却疟P(pán)的 IO）可以忽略不計(jì)，通過(guò)磁盤(pán)塊 1 的 P2 指針的磁盤(pán)地址把磁盤(pán)塊

3 由磁盤(pán)加載到內(nèi)存，發(fā)生第二次 IO，29 在 26 和 30 之間，鎖定磁盤(pán)塊 3 的 P2 指針，通過(guò)

指針加載磁盤(pán)塊 8 到內(nèi)存，發(fā)生第三次 IO，同時(shí)內(nèi)存中做二分查找找到 29，結(jié)束查詢(xún)，總

計(jì)三次 IO。真實(shí)的情況是，3 層的 b+樹(shù)可以表示上百萬(wàn)的數(shù)據(jù)，如果上百萬(wàn)的數(shù)據(jù)查找只

需要三次 IO，性能提高將是巨大的，如果沒(méi)有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次 IO，那么總

共需要百萬(wàn)次的 IO，顯然成本非常非常高。

為什么使用索引？

索引可以讓 mysql 高效運(yùn)行，可以大大提高 mysql 的查詢(xún)（包括排序，分組）效率；數(shù)據(jù)約

束（唯一索引的建立）。

索引給我?guī)?lái)什么好處？

提高查詢(xún)效率，快速定位數(shù)據(jù)

使用索引產(chǎn)生的代價(jià)？

1、索引本身是以文件的形式存放在硬盤(pán)，需要的時(shí)候才加載至內(nèi)存，所以添加索引會(huì)增加

磁盤(pán)的開(kāi)銷(xiāo)；

2、寫(xiě)數(shù)據(jù)：需要更新索引，對(duì)數(shù)據(jù)庫(kù)是個(gè)很大的開(kāi)銷(xiāo)，降低表更新、添加和刪除的速度

不建議使用索引的情況：

a) 表記錄較少

b) 索引的選擇性較低，所謂索引的選擇性，是指不重復(fù)的索引值與表記錄數(shù)的比值，取值

范圍（0-1）。選擇性越高，索引的價(jià)值越大。

索引的類(lèi)型？

索引包括單列索引和組合索引

單列索引，即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引，但這不是組合索引。組

合索引，即一個(gè)索包含多個(gè)列。

1、 普通索引

這是最基本的索引，它沒(méi)有任何限制

CREATE INDEX indexName ON tablename(column1[,column2,……])

2、 唯一索引

它與前面的普通索引類(lèi)似，不同的就是索引列的值必須唯一，但允許空值，空值是指 null。

如果是組合索引，組合列的值必須唯一

CREATE UNIQUE INDEX indexName ON tablename(column1[,column2,……])

主鍵索引：一種特殊的唯一索引，不允許有空值，一般在建表的時(shí)候同時(shí)建立主鍵索引

CREATE TABLE tablename(ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY

KEY(ID) );

3、組合索引

為了進(jìn)一步提升 MySQL 的效率，就要考慮建立組合索引

例如：創(chuàng)建一個(gè)表，包含如下字段

CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50)

NOT NULL, age INT NOT NULL );

將 username, city, age 建到一個(gè)索引里

CREATE INDEX username_city_age ON mytable(username,city,age);

如果分別在 usernname，city，age 上建立單列索引，讓該表有 3 個(gè)單列索引，查詢(xún)時(shí)和上

述的組合索引效率也會(huì)大不一樣，遠(yuǎn)遠(yuǎn)低于組合索引。雖然此時(shí)有了三個(gè)索引，但 MySQL

只能用到其中那個(gè)它認(rèn)為似乎是最有效率的單列索引。

建立這樣的組合索引，其實(shí)是相當(dāng)于分別建立了下面三組組合索引：

usernname,city,age usernname,city usernname 為什么沒(méi)有 city，age 這樣的組合索引呢？

這是因?yàn)?MySQL 組合索引“最左前綴”的結(jié)果。簡(jiǎn)單的理解就是只從最左面的開(kāi)始組合。并

不是只要包含這三列的查詢(xún)都會(huì)用到該組合索引，下面的幾個(gè) SQL 就會(huì)用到這個(gè)組合索引：

SELECT * FROM mytable WHREE username="admin" AND city="鄭州"

SELECT * FROM mytable WHREE username="admin"

而下面幾個(gè)則不會(huì)用到：

SELECT * FROM mytable WHREE age=20 AND city="鄭州"

SELECT * FROM mytable WHREE city="鄭州"

如果對(duì)多列進(jìn)行索引(組合索引)，列的順序非常重要，MySQL 僅能對(duì)索引最左邊的前綴進(jìn)行

有效的查找。

例如：

假設(shè)存在組合索引 index1(c1,c2)，查詢(xún)語(yǔ)句 select * from t1 where c1=1 and c2=2 能夠使用該

索引。查詢(xún)語(yǔ)句 select * from t1 where c1=1 也能夠使用該索引。但是，查詢(xún)語(yǔ)句 select * from

t1 where c2=2 不能夠使用該索引，因?yàn)闆](méi)有組合索引的引導(dǎo)列，即要想使用 c2 列進(jìn)行查找，

必需出現(xiàn) c1 等于某值。因此我們?cè)趧?chuàng)建組合索引時(shí)應(yīng)該將最常用作限制條件的列放在最左

邊，依次遞減。

4、 全文索引

只用于 MyISAM 表 對(duì)文本域進(jìn)行索引。字段類(lèi)型包括 char、varchar、text

不過(guò)切記對(duì)于大容量的數(shù)據(jù)表，生成全文索引是一個(gè)非常消耗時(shí)間非常消耗硬盤(pán)空間的做法。

CREATE FULLTEXT INDEX indexname ON tablename(column)

查看索引

mysql> show index from tablename;

mysql> show keys from tablename;

建立索引的時(shí)機(jī)

到這里我們已經(jīng)學(xué)會(huì)了建立索引，那么我們需要在什么情況下建立索引呢？

一般來(lái)說(shuō)，在 WHERE 和 JOIN 子句中出現(xiàn)的列需要建立索引，例如：

代碼如下:在 username 上創(chuàng)建索引

SELECT * FROM mytable WHREE username="admin" AND city="鄭州"

代碼如下:

SELECT t.Name FROM mytable1 t LEFT JOIN mytable2 m ON t.username=m.username;

此時(shí)就需要對(duì)兩個(gè)表的 userame 上建立索引。

使用索引的注意事項(xiàng)

剛才提到只有某些時(shí)候的 LIKE 才需建立索引。因?yàn)樵谝酝ㄅ浞?和_開(kāi)頭作查詢(xún)時(shí)，MySQL

不會(huì)使用索引。例如下句會(huì)使用索引：

SELECT * FROM mytable WHERE username like'admin%'

而下句就不會(huì)使用：

SELECT * FROM mytable WHEREt Name like'%admin'

不要在列上進(jìn)行運(yùn)算,例如

select * from users where YEAR(adddate)<2007;

將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致索引失效而進(jìn)行全表掃描，因此我們可以改成

select * from users where adddate<‘2007-01-01';

總結(jié)：

選擇索引列：

a.性能優(yōu)化過(guò)程中，選擇在哪個(gè)列上創(chuàng)建索引是最重要的步驟之一。可以考慮使用索引

的主要有 兩種類(lèi)型的列：在 where 子句中出現(xiàn)的列，在 join 子句中出現(xiàn)的列。

b.考慮列中值的分布，索引的列的基數(shù)越大，索引的效果越好。

c.使用短索引，如果對(duì)字符串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴長(zhǎng)度，可節(jié)省大量索引空間，

提升查詢(xún)速度。

未經(jīng)允許不得轉(zhuǎn)載傳播--陳英宏

博客地址：hongge.blog.51cto.com

例如：CREATE INDEX username_city_age ON mytable(username（10）,city,age);

建表時(shí)，usernname 長(zhǎng)度為 16，這里用 10。這是因?yàn)橐话闱闆r下名字的長(zhǎng)度不會(huì)超過(guò) 10，

這樣會(huì)加速索引查詢(xún)速度，還會(huì)減少索引文件的大小，提高 INSERT 的更新速度。

d.利用最左前綴

e.不要過(guò)度索引，只保持所需的索引。每個(gè)額外的索引都要占用額外的磁盤(pán)空間，并降低寫(xiě)

操作的性能。 在修改表的內(nèi)容時(shí)，索引必須進(jìn)行更新，有時(shí)可能需要重構(gòu)，因此，索引越

多，所花的時(shí)間越長(zhǎng)。

MySQL 只對(duì)一下操作符才使用索引：<,<=,=,>,>=,between,in,

以及某些時(shí)候的 like(不以通配符%或_開(kāi)頭的情形)。

以下就是有關(guān) Mysql 索引的相關(guān)理論介紹，下面我們來(lái)學(xué)習(xí)如何慢查詢(xún)分析、優(yōu)化索引和配

置

三、mysql 性能優(yōu)化-慢查詢(xún)分析、優(yōu)化索引和配置

基本思路：

1）性能瓶頸定位

Show 命令

慢查詢(xún)?nèi)罩?/span>

explain 分析查詢(xún)

profiling 分析查詢(xún)

2）索引及查詢(xún)優(yōu)化

3）配置優(yōu)化

MySQL 數(shù)據(jù)庫(kù)是常見(jiàn)的兩個(gè)瓶頸是 CPU 和 I/O 的瓶頸，CPU 在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝

入內(nèi)存或從磁盤(pán)上讀取數(shù)據(jù)時(shí)候。磁盤(pán) I/O 瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候，如

果應(yīng)用分布在網(wǎng)絡(luò)上，那么查詢(xún)量相當(dāng)大的時(shí)候那么平瓶頸就會(huì)出現(xiàn)在網(wǎng)絡(luò)上，我們可以用

mpstat, iostat, sar 和 vmstat 來(lái)查看系統(tǒng)的性能狀態(tài)。

除了服務(wù)器硬件的性能瓶頸，對(duì)于 MySQL 系統(tǒng)本身，我們可以使用工具來(lái)優(yōu)化數(shù)據(jù)庫(kù)的性

能，通常有三種：使用索引，使用 EXPLAIN 分析查詢(xún)以及調(diào)整 MySQL 的內(nèi)部配置。

1 、 查詢(xún)與索引優(yōu)化分析

在優(yōu)化 MySQL 時(shí)，通常需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行分析，常見(jiàn)的分析手段有慢查詢(xún)?nèi)罩荆珽XPLAIN 分

析查詢(xún)，profiling 分析以及 show 命令查詢(xún)系統(tǒng)狀態(tài)及系統(tǒng)變量，通過(guò)定位分析性能的瓶頸，才

能更好的優(yōu)化數(shù)據(jù)庫(kù)系統(tǒng)的性能。

性能瓶頸定位

show 命令

可以通過(guò) show 命令查看 MySQL 狀態(tài)及變量，找到系統(tǒng)的瓶頸：

查看 MySQL 服務(wù)器配置信息 mysql> show variables;

查看 MySQL 服務(wù)器運(yùn)行的各種狀態(tài)值 mysql> show global status; 

# mysqladmin variables -u username -ppassword——顯示系統(tǒng)變量

# mysqladmin extended-status -u username -ppassword——顯示狀態(tài)信息

比較全的 show 命令的使用可參考：mysql>help show

或 http://dev.mysql.com/doc/refman/5.7/en/show.html

慢查詢(xún)?nèi)罩?/span>

慢查詢(xún)?nèi)罩鹃_(kāi)啟：

在配置文件 my.cnf 中在 [mysqld] 一行下面加入 3 個(gè)配置參數(shù)，并重啟 mysql 服務(wù)

slow_query_log = 1 //0 關(guān)閉 1 開(kāi)啟

slow_query_log_file = /usr/local/mysql/data/slow-query.log //慢查詢(xún)?nèi)罩敬娣诺攸c(diǎn)

未經(jīng)允許不得轉(zhuǎn)載傳播--陳英宏

博客地址：hongge.blog.51cto.com

long_query_time = 1 //表示查詢(xún)超過(guò) 1 秒才記錄

在 my.cnf 中添加 log-queries-not-using-indexes 參數(shù)，表示向慢查詢(xún)?nèi)罩局杏涗浵聸](méi)有使用索

引的查詢(xún)。

慢查詢(xún)?nèi)罩鹃_(kāi)啟方法二：

我們也可以通過(guò)命令行設(shè)置變量來(lái)即時(shí)啟動(dòng)慢日志查詢(xún)

mysql> set global slow_query_log = on;

mysql> set long_query_time = 0.01;

mysql> set global slow_query_log_file = "/usr/local/mysql/data/slow-query.log";

查看慢查詢(xún)的設(shè)置信息

mysql> show variables like '%slow_query_log%';

mysql> show variables like '%long_query_time%';

我們可以通過(guò)打開(kāi) log 文件查看得知哪些 SQL 執(zhí)行效率低下

[root@localhost data]# cat slow-query.log

# Time: 2016-09-06T14:17:12.582189Z

# User@Host: root[root] @ localhost [] Id: 3

# Query_time: 0.008316 Lock_time: 0.000304 Rows_sent: 1 Rows_examined: 20002

SET timestamp=1473171432;

select * from test1.tb1 where stusex='0'; //沒(méi)有使用索引的 query

# Time: 2016-09-06T15:54:42.648291Z

# User@Host: root[root] @ localhost [] Id: 14

# Query_time: 0.017319 Lock_time: 0.000612 Rows_sent: 1 Rows_examined: 20001

SET timestamp=1473177282;

select * from test1.tb1 where entertime <> '2016-9-3'; //慢查詢(xún) query

從日志中，可以發(fā)現(xiàn)查詢(xún)時(shí)間超過(guò) 0.01 秒的 SQL，而小于 0.01 秒的沒(méi)有出現(xiàn)在此日志中。

如果慢查詢(xún)?nèi)罩局杏涗泝?nèi)容很多，可以使用 mysqldumpslow 工具（MySQL 客戶(hù)端安裝自帶）

來(lái)對(duì)慢查詢(xún)?nèi)罩具M(jìn)行分類(lèi)匯總。mysqldumpslow 對(duì)日志文件進(jìn)行了分類(lèi)匯總，顯示匯總后摘

要結(jié)果。

有關(guān) mysqldumpslow 命令的用法可以參考其幫助：#mysqldumpslow --help

[root@localhost data]# mysqldumpslow -a -s at -r slow-query.log

Reading mysql slow query log from slow-query.log

Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts

Time: 2016-09-06T15:54:42.648291Z

# User@Host: root[root] @ localhost [] Id: 14

# Query_time: 0.017319 Lock_time: 0.000612 Rows_sent: 1 Rows_examined: 20001

SET timestamp=1473177282;

select * from test1.tb1 where entertime <> '2016-9-3'

上面顯示結(jié)果中就是一條慢查詢(xún)，如何優(yōu)化呢？

一是在 entertime 列上創(chuàng)建索引優(yōu)化查詢(xún)

mysql> create index index_entertime on test1.tb1(entertime);

二是優(yōu)化這個(gè) sql 查詢(xún)語(yǔ)句

mysql> select * from test1.tb1 where entertime < '2016-9-3' or entertime > '2016-9-3';

從下圖可以看查詢(xún) 0.00 秒

使用 mysqldumpslow 命令可以非常明確的得到各種我們需要的查詢(xún)語(yǔ)句，對(duì) MySQL 查詢(xún)語(yǔ)

句的監(jiān)控、分析、優(yōu)化是 MySQL 優(yōu)化非常重要的一步。開(kāi)啟慢查詢(xún)?nèi)罩竞?，由于日志記?/span>

操作，在一定程度上會(huì)占用 CPU 資源影響 mysql 的性能，但是可以階段性開(kāi)啟來(lái)定位性能

瓶頸。

explain 分析查詢(xún)

使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行 SQL 查詢(xún)語(yǔ)句，從而知道 MySQL 是如何處理你的

SQL 語(yǔ)句的。這可以幫你分析你的查詢(xún)語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。通過(guò) explain 命令可以得到:

mysql> explain select * from test1.tb1 where stuname='admin'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tb1

partitions: NULL

type: ALL // 全表掃描

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 19986

filtered: 10.00

Extra: Using where //使用 where 過(guò)濾數(shù)據(jù)

1 row in set, 1 warning (0.00 sec)

EXPLAIN 字段：

Table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

type：這是最重要的字段之一，顯示查詢(xún)使用了何種類(lèi)型。從最好到最差的連接類(lèi)型為 system、

const、eq_reg、ref、range、index 和 ALL

possible_keys：顯示可能應(yīng)用在這張表中的索引。如果為空，沒(méi)有可能的索引。

key：實(shí)際使用的索引。如果為 NULL，則沒(méi)有使用索引。

key_len：使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好

ref：顯示索引的哪一列被使用了，如果可能的話(huà)，是一個(gè)常數(shù)

rows：MySQL 認(rèn)為必須檢索的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)

Extra：關(guān)于 MYSQL 如何解析查詢(xún)的額外信息

從上面的 explain 模擬優(yōu)化器執(zhí)行 sql 語(yǔ)句來(lái)看是沒(méi)有使用索引查詢(xún)的，而是全表掃描

優(yōu)化方法：在 stuname 列上創(chuàng)建索引

mysql> create index index_stuname on test1.tb1(stuname);

再次執(zhí)行 explain

mysql> explain select * from test1.tb1 where stuname='admin'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tb1

partitions: NULL

type: ref

possible_keys: index_stuname

key: index_stuname

key_len: 62

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

顯示結(jié)果說(shuō)明該查詢(xún)語(yǔ)句使用了 index_stuname 索引查詢(xún)數(shù)據(jù)而非全表掃描。

profiling 分析查詢(xún)

通過(guò)慢日志查詢(xún)可以知道哪些 SQL 語(yǔ)句執(zhí)行效率低下，通過(guò) explain 我們可以得知 SQL 語(yǔ)句的

具體執(zhí)行情況，索引使用等，還可以結(jié)合 show 命令查看執(zhí)行狀態(tài)。如果覺(jué)得 explain 的信息不

夠詳細(xì)，可以同通過(guò) profiling 命令得到更準(zhǔn)確的 SQL 執(zhí)行消耗系統(tǒng)資源的信息。

profiling 默認(rèn)是關(guān)閉的。可以通過(guò)以下語(yǔ)句查看

mysql> show variables like '%profiling%'; //off 表示未開(kāi)啟

或

mysql> select @@profiling; //0 表示未開(kāi)啟

打開(kāi) profiling 功能： mysql>set profiling=1; 執(zhí)行需要測(cè)試的 sql 語(yǔ)句：

mysql> select @@profiling;

+---------------------+

| @@profiling |

+---------------------+

| 1 |

+----------------------+

執(zhí)行要測(cè)試的 sql 語(yǔ)句

mysql> select * from test1.tb1 where stuname='admin' and entertime='2016-9-1';

mysql> show profiles\G; //可以得到被執(zhí)行的 SQL 語(yǔ)句的時(shí)間和 ID

*************************** 1. row ***************************

Query_ID: 1

Duration: 0.00012650

Query: select @@profiling

*************************** 2. row ***************************

Query_ID: 2

Duration: 0.00121725

Query: select * from test1.tb1 where stuname='admin' and entertime='2016-9-1'

mysql> show profile for query 2; //得到對(duì)應(yīng) SQL 語(yǔ)句執(zhí)行的詳細(xì)信息

+----------------------+-------------------------+

| Status | Duration |

+----------------------+-------------------------+

| starting | 0.000230 |

| checking permissions | 0.000013 |

| Opening tables | 0.000030 |

| init | 0.000087 |

| System lock | 0.000018 |

| optimizing | 0.000128 |

| statistics | 0.000378 |

| preparing | 0.000026 |

| executing | 0.000005 |

| Sending data | 0.000187 |

| end | 0.000013 |

| query end | 0.000011 |

| closing tables | 0.000010 |

| freeing items | 0.000061 |

| cleaning up | 0.000021 |

+----------------------+-------------------------+

status:是 profile 里的狀態(tài)，duration：是 status 狀態(tài)下的耗時(shí)。因此我們關(guān)注的就是那個(gè)狀

態(tài)最耗時(shí)，這些狀態(tài)中那些可以?xún)?yōu)化。

當(dāng)然也可以查看更多的信息如 CPU 等等

SHOW PROFILE [type [, type] ... ] [FOR QUERY n]

type:

ALL:顯示所有的開(kāi)銷(xiāo)信息

BLOCK IO:顯示塊 IO 相關(guān)開(kāi)銷(xiāo)

CPU:顯示用戶(hù) CPU 時(shí)間、系統(tǒng) CPU 時(shí)間

IPC:顯示發(fā)送和接收相關(guān)開(kāi)銷(xiāo)信息

PAGE FAULTS:顯示頁(yè)面錯(cuò)誤相關(guān)開(kāi)銷(xiāo)信息

SWAPS:顯示交換次數(shù)相關(guān)開(kāi)銷(xiāo)的信息

測(cè)試完成之以后，記得要關(guān)閉調(diào)試功能，以免影響數(shù)據(jù)庫(kù)的正常使用：

mysql> set profiling=0;

2、配置優(yōu)化

Mysql 參數(shù)優(yōu)化對(duì)于不同的網(wǎng)站，及其在線量，訪問(wèn)量，帖子數(shù)量，網(wǎng)絡(luò)情況，以及機(jī)器硬

件配置都有關(guān)系，優(yōu)化不可能一次性完成，需要不斷的觀察以及調(diào)試，才有可能得到最佳效

果。

下面列出了對(duì)性能優(yōu)化影響較大的主要變量，主要分為連接請(qǐng)求的變量和緩沖區(qū)變量

1） 連接請(qǐng)求的變量：

1. max_connections

MySQL 的最大連接數(shù)，如果服務(wù)器的并發(fā)連接請(qǐng)求量比較大，建議調(diào)高此值，以增加并行

連接數(shù)量，當(dāng)然這建立在機(jī)器能支撐的情況下，因?yàn)槿绻B接數(shù)越多， MySQL 會(huì)為每個(gè)連

接提供連接緩沖區(qū)，就會(huì)開(kāi)銷(xiāo)越多的內(nèi)存，所以要適當(dāng)調(diào)整該值，不能盲目提高設(shè)值。

數(shù)值過(guò)小會(huì)經(jīng)常出現(xiàn) ERROR 1040: Too many connections 錯(cuò)誤，可以過(guò) mysql> show status like

'connections';通配符查看當(dāng)前狀態(tài)的連接數(shù)量(試圖連接到MySQL(不管是否連接成功)的連接

數(shù))，以定奪該值的大小。

mysql>show variables like ‘max_connections’ 最大連接數(shù)

mysql>show status like ‘max_used_connections’ 響應(yīng)的連接數(shù)

max_used_connections / max_connections * 100% （理想值≈ 85%）

如果 max_used_connections 跟 max_connections 相同那么就是 max_connections 設(shè)置過(guò)低或

者超過(guò)服務(wù)器負(fù)載上限了，低于 10%則設(shè)置過(guò)大。

如何設(shè)置 max_connections?

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內(nèi)容，如設(shè)置最大連接數(shù)為 1024

max_connections = 1024

重啟 mysql 服務(wù)

2.back_log

MySQL 能暫存的連接數(shù)量。當(dāng)主要 MySQL 線程在一個(gè)很短時(shí)間內(nèi)得到非常多的連接請(qǐng)求，

它就會(huì)起作用。如果 MySQL 的連接數(shù)據(jù)達(dá)到 max_connections 時(shí)，新來(lái)的請(qǐng)求將會(huì)被存在堆

棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即 back_log，如果等待連接的數(shù)量超過(guò)

back_log，將不被授予連接資源。

back_log 值指出在 MySQL 暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)有多少個(gè)請(qǐng)求可以被存在堆

棧中。只有如果期望在一個(gè)短時(shí)間內(nèi)有很多連接，你需要增加它。

當(dāng)觀察你主機(jī)進(jìn)程列表（mysql> show full processlist），發(fā)現(xiàn)大量

xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接

進(jìn)程時(shí)，就要加大 back_log 的值了或加大 max_connections 的值。

通過(guò) mysql> show variables like 'back_log';查看 back_log 的設(shè)置

如何設(shè)置 back_log?

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內(nèi)容，如設(shè)置最大連接數(shù)為 1024

back_log = 數(shù)值

重啟 mysql 服務(wù)

3. wait_timeout 和 interactive_timeout

wait_timeout -- 指的是 MySQL在關(guān)閉一個(gè)非交互的連接之前所要等待的秒數(shù)

interactive_time -- 指的是 mysql 在關(guān)閉一個(gè)交互的連接之前所要等待的秒數(shù)，比如我們?cè)诮K

端上進(jìn)入 mysql 管理，使用的即使交互的連接，這時(shí)候，如果沒(méi)有操作的時(shí)間超過(guò)了

interactive_time 設(shè)置的時(shí)間就會(huì)自動(dòng)斷開(kāi)。默認(rèn)數(shù)值是 28800，可調(diào)優(yōu)為 7200。

對(duì)性能的影響：

wait_timeout：

（1）如果設(shè)置大小，那么連接關(guān)閉的很快，從而使一些持久的連接不起作用

（2）如果設(shè)置太大，容易造成連接打開(kāi)時(shí)間過(guò)長(zhǎng)，在 show processlist 時(shí)，能看到太多的 sleep

狀態(tài)的連接，從而造成 too many connections 錯(cuò)誤

（3）一般希望 wait_timeout 盡可能地低

interactive_timeout 的設(shè)置將要對(duì)你的 web application 沒(méi)有多大的影響

查看 wait_timeout 和 interactive_timeout

mysql> show variables like '%wait_tmeout%';

mysql> show variables like '%interactive_timeout%';

如何設(shè)置 wait_timeout 和 interactive_timeout ?

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內(nèi)容

wait_timeout=100

interactive_timeout=100

重啟 MySQL Server 進(jìn)入后，查看設(shè)置已經(jīng)生效。

2）綬沖區(qū)變量

全局緩沖：

4.key_buffer_size

key_buffer_size 指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通

過(guò)檢查狀態(tài)值 Key_read_requests 和 Key_reads，可以知道 key_buffer_size 設(shè)置是否合理。比

例 key_reads / key_read_requests 應(yīng)該盡可能的低，至少是 1:100，1:1000 更好（上述狀態(tài)值

可以使用 SHOW STATUS LIKE ‘key_read%’獲得）。

一共有 6 個(gè)索引讀取請(qǐng)求，有 3 個(gè)請(qǐng)求在內(nèi)存中沒(méi)有找到直接從硬盤(pán)讀取索引，計(jì)算索引

未命中緩存的概率：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =50%

key_buffer_size 只對(duì) MyISAM 表起作用。即使你不使用 MyISAM 表，但是內(nèi)部的臨時(shí)磁盤(pán)表

是 MyISAM 表，也要使用該值。可以使用檢查狀態(tài)值 created_tmp_disk_tables 得知詳情。

如何調(diào)整 key_buffer_size

默認(rèn)配置數(shù)值是 8388608(8M)，主機(jī)有 4GB 內(nèi)存，可以調(diào)優(yōu)值為 268435456(256MB)

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內(nèi)容

key_buffer_size=268435456 或 key_buffer_size=256M

重啟 MySQL Server 進(jìn)入后，查看設(shè)置已經(jīng)生效。

5. query_cache_size(查詢(xún)緩存簡(jiǎn)稱(chēng) QC)

使用查詢(xún)緩沖，MySQL 將查詢(xún)結(jié)果存放在緩沖區(qū)中，今后對(duì)于同樣的 SELECT 語(yǔ)句（區(qū)分大小

寫(xiě)），將直接從緩沖區(qū)中讀取結(jié)果。

一個(gè) SQL 查詢(xún)?nèi)绻?select 開(kāi)頭，那么 MySQL 服務(wù)器將嘗試對(duì)其使用查詢(xún)緩存。

注：兩個(gè) SQL 語(yǔ)句，只要相差哪怕是一個(gè)字符（例如大小寫(xiě)不一樣；多一個(gè)空格等），那么這兩

個(gè) SQL 將使用不同的一個(gè) CACHE。

通過(guò)檢查狀態(tài)值’Qcache%’，可以知道 query_cache_size 設(shè)置是否合理（上述狀態(tài)值可以使用

SHOW STATUS LIKE ‘Qcache%’獲得）。

Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個(gè)數(shù)。如果該值顯示較大，則說(shuō)明 Query Cache 中

的內(nèi)存碎片較多了，F(xiàn)LUSH QUERY CACHE 會(huì)對(duì)緩存中的碎片進(jìn)行整理，從而得到一個(gè)

空閑塊。

注：當(dāng)一個(gè)表被更新之后，和它相關(guān)的 cache blocks 將被 free。但是這個(gè) block 依然可能

存在隊(duì)列中，除非是在隊(duì)列的尾部。可以用 FLUSH QUERY CACHE 語(yǔ)句來(lái)清空 free blocks

Qcache_free_memory：Query Cache 中目前剩余的內(nèi)存大小。通過(guò)這個(gè)參數(shù)我們可以較為

準(zhǔn)確的觀察出當(dāng)前系統(tǒng)中的 Query Cache 內(nèi)存大小是否足夠，是需要增加還是過(guò)多了。

Qcache_hits：表示有多少次命中緩存。我們主要可以通過(guò)該值來(lái)驗(yàn)證我們的查詢(xún)緩存的效

果。數(shù)字越大，緩存效果越理想。

Qcache_inserts：表示多少次未命中然后插入，意思是新來(lái)的 SQL 請(qǐng)求在緩存中未找到，不

得不執(zhí)行查詢(xún)處理，執(zhí)行查詢(xún)處理后把結(jié)果 insert 到查詢(xún)緩存中。這樣的情況的次數(shù)越多，

表示查詢(xún)緩存應(yīng)用到的比較少，效果也就不理想。當(dāng)然系統(tǒng)剛啟動(dòng)后，查詢(xún)緩存是空的，這

很正常。

Qcache_lowmem_prunes：多少條 Query 因?yàn)閮?nèi)存不足而被清除出 Query Cache。通過(guò)

“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結(jié)合，能夠更清楚的了解到我們系

統(tǒng)中 Query Cache 的內(nèi)存大小是否真的足夠，是否非常頻繁的出現(xiàn)因?yàn)閮?nèi)存不足而有 Query

被換出。這個(gè)數(shù)字最好長(zhǎng)時(shí)間來(lái)看；如果這個(gè)數(shù)字在不斷增長(zhǎng)，就表示可能碎片非常嚴(yán)重，

或者內(nèi)存很少。（上面的 free_blocks 和 free_memory 可以告訴您屬于哪種情況）

Qcache_not_cached：不適合進(jìn)行緩存的查詢(xún)的數(shù)量，通常是由于這些查詢(xún)不是 SELECT

語(yǔ)句或者用了 now()之類(lèi)的函數(shù)。

Qcache_queries_in_cache：當(dāng)前 Query Cache 中 cache 的 Query 數(shù)量；

Qcache_total_blocks：當(dāng)前 Query Cache 中的 block 數(shù)量；。

我們?cè)俨樵?xún)一下服務(wù)器關(guān)于 query_cache 的配置：

上圖可以看出 query_cache_type 為 off 表示不緩存任何查詢(xún)

各字段的解釋?zhuān)?/span>

query_cache_limit：超過(guò)此大小的查詢(xún)將不緩存

query_cache_min_res_unit：緩存塊的最小大小 ，query_cache_min_res_unit 的配置是一柄”

雙刃劍”，默認(rèn)是 4KB，設(shè)置值大對(duì)大數(shù)據(jù)查詢(xún)有好處，但如果你的查詢(xún)都是小數(shù)據(jù)查詢(xún)，

就容易造成內(nèi)存碎片和浪費(fèi)。

query_cache_size：查詢(xún)緩存大小 (注：QC 存儲(chǔ)的最小單位是 1024 byte，所以如果你設(shè)定了

一個(gè)不是 1024 的倍數(shù)的值，這個(gè)值會(huì)被四舍五入到最接近當(dāng)前值的等于 1024 的倍數(shù)的值。)

query_cache_type：緩存類(lèi)型，決定緩存什么樣的查詢(xún)，注意這個(gè)值不能隨便設(shè)置，必須設(shè)

置為數(shù)字，可選項(xiàng)目以及說(shuō)明如下：

如果設(shè)置為 0，那么可以說(shuō)，你的緩存根本就沒(méi)有用，相當(dāng)于禁用了。

如果設(shè)置為 1，將會(huì)緩存所有的結(jié)果，除非你的 select 語(yǔ)句使用 SQL_NO_CACHE 禁用了查詢(xún)

緩存。

如果設(shè)置為 2，則只緩存在 select 語(yǔ)句中通過(guò) SQL_CACHE 指定需要緩存的查詢(xún)。

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=256M

query_cache_type=1

保存文件，重新啟動(dòng) MYSQL 服務(wù)，然后通過(guò)如下查詢(xún)來(lái)驗(yàn)證是否真正開(kāi)啟了：

query_cache_wlock_invalidate：當(dāng)有其他客戶(hù)端正在對(duì) MyISAM 表進(jìn)行寫(xiě)操作時(shí)，如果查詢(xún)

在 query cache 中，是否返回 cache 結(jié)果還是等寫(xiě)操作完成再讀表獲取結(jié)果。

查詢(xún)緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查詢(xún)緩存碎片率超過(guò) 20%，可以用 FLUSH QUERY CACHE 整理緩存碎片，或者試試減小

query_cache_min_res_unit，如果你的查詢(xún)都是小數(shù)據(jù)量的話(huà)。

查詢(xún)緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查詢(xún)緩存利用率在 25%以下的話(huà)說(shuō)明 query_cache_size 設(shè)置的過(guò)大，可適當(dāng)減??；查詢(xún)緩存

利用率在80％以上而且Qcache_lowmem_prunes > 50的話(huà)說(shuō)明 query_cache_size可能有點(diǎn)小，

要不就是碎片太多。

查詢(xún)緩存命中率 = Qcache_hits/(Qcache_hits +Qcache_inserts) * 100%

Query Cache 的限制

a) 所有子查詢(xún)中的外部查詢(xún) SQL 不能被 Cache；

b) 在 Procedure，F(xiàn)unction 以及 Trigger 中的 Query 不能被 Cache；

c) 包含其他很多每次執(zhí)行可能得到不一樣結(jié)果的函數(shù)的 Query 不能被 Cache。

鑒于上面的這些限制，在使用 Query Cache 的過(guò)程中，建議通過(guò)精確設(shè)置的方式來(lái)使用，僅

僅讓合適的表的數(shù)據(jù)可以進(jìn)入 Query Cache，僅僅讓某些 Query 的查詢(xún)結(jié)果被 Cache。

如何設(shè)置 query_cache_size？

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內(nèi)容

query_cache_size=256M

query_cache_type=1

重啟 MySQL Server 進(jìn)入后，查看設(shè)置已經(jīng)生效。

6. max_connect_errors 是一個(gè) MySQL 中與安全有關(guān)的計(jì)數(shù)器值，它負(fù)責(zé)阻止過(guò)多嘗試失敗

的客戶(hù)端以防止暴力破解密碼的情況, 當(dāng)超過(guò)指定次數(shù)，MYSQL 服務(wù)器將禁止 host 的連接

請(qǐng)求，直到 mysql 服務(wù)器重啟或通過(guò) flush hosts 命令清空此 host 的相關(guān)信息。

max_connect_errors 的值與性能并無(wú)太大關(guān)系。

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內(nèi)容

max_connect_errors=20

重啟 MySQL Server 進(jìn)入后，查看設(shè)置已經(jīng)生效。

7. sort_buffer_size

每個(gè)需要進(jìn)行排序的線程分配該大小的一個(gè)緩沖區(qū)。增加這值加速 ORDER BY 或 GROUP BY

操作。

Sort_Buffer_Size 是一個(gè) connection 級(jí)參數(shù)，在每個(gè) connection（session）第一次需要使用這

個(gè) buffer 的時(shí)候，一次性分配設(shè)置的內(nèi)存。

Sort_Buffer_Size 并不是越大越好，由于是 connection 級(jí)的參數(shù)，過(guò)大的設(shè)置+高并發(fā)可能會(huì)

耗盡系統(tǒng)內(nèi)存資源。例如：500 個(gè)連接將會(huì)消耗 500*sort_buffer_size(2M)=1G 內(nèi)存

例如設(shè)置 sort_buffer_size

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內(nèi)容

sort_buffer_size = 2M

重啟 MySQL Server 進(jìn)入后，查看設(shè)置已經(jīng)生效。

8. max_allowed_packet = 32M

MySQL 根據(jù)配置文件會(huì)限制 Server 接受的數(shù)據(jù)包大小。有時(shí)候大的插入和更新會(huì)受

max_allowed_packet 參數(shù)限制，導(dǎo)致寫(xiě)入或者更新失敗。最大值是 1GB，必須設(shè)置 1024 的

倍數(shù)。

9．join_buffer_size = 2M

用于表間關(guān)聯(lián)緩存的大小，和 sort_buffer_size 一樣，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接

獨(dú)享。

10. thread_cache_size = 300

服務(wù)器線程緩存，這個(gè)值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開(kāi)連接時(shí),那么客

戶(hù)端的線程將被放到緩存中以響應(yīng)下一個(gè)客戶(hù)而不是銷(xiāo)毀(前提是緩存數(shù)未達(dá)上限),如果線

程重新被請(qǐng)求，那么請(qǐng)求將從緩存中讀取,如果緩存中是空的或者是新的請(qǐng)求，那么這個(gè)線

程將被重新創(chuàng)建,如果有很多新的線程，增加這個(gè)值可以改善系統(tǒng)性能.通過(guò)比

較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個(gè)變量的作用。設(shè)置規(guī)則如下：

1GB 內(nèi)存配置為 8，2GB 配 置為 16，3GB 配置為 32，4GB 或更高內(nèi)存，可配置更大。服務(wù)

器處理此客戶(hù)的線程將會(huì)緩存起來(lái)以響應(yīng)下一個(gè)客戶(hù)而不是銷(xiāo)毀(前提是緩存數(shù)未達(dá)上限)

試圖連接到 MySQL(不管是否連接成功)的連接數(shù)

Threads_cached :代表當(dāng)前此時(shí)此刻線程緩存中有多少空閑線程。

Threads_connected :代表當(dāng)前已建立連接的數(shù)量，因?yàn)橐粋€(gè)連接就需要一個(gè)線程，所以也可

以看成當(dāng)前被使用的線程數(shù)。

Threads_created :代表從最近一次服務(wù)啟動(dòng)，已創(chuàng)建線程的數(shù)量，如果發(fā)現(xiàn) Threads_created

值過(guò)大的話(huà)，表明 MySQL 服務(wù)器一直在創(chuàng)建線程，這也是比較耗資源，可以適當(dāng)增加配置

文件中 thread_cache_size 值。

Threads_running :代表當(dāng)前激活的（非睡眠狀態(tài)）線程數(shù)。并不是代表正在使用的線程數(shù)，

有時(shí)候連接已建立，但是連接處于 sleep 狀態(tài)。

3）配置 InnoDB 的幾個(gè)變量

11. innodb_buffer_pool_size

對(duì)于InnoDB 表來(lái)說(shuō)，innodb_buffer_pool_size 的作用就相當(dāng)于 key_buffer_size 對(duì)于 MyISAM

表的作用一樣。InnoDB 使用該參數(shù)指定大小的內(nèi)存來(lái)緩沖數(shù)據(jù)和索引。對(duì)于單獨(dú)的 MySQL

數(shù)據(jù)庫(kù) 服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的 80%。根據(jù) MySQL 手冊(cè)，對(duì)于 2G 內(nèi)存

的機(jī)器，推薦值是 1G（50%）。 如果你的數(shù)據(jù)量不大，并且不會(huì)暴增，那么無(wú)需

把 innodb_buffer_pool_size 設(shè)置的太大了。

mysql> show variables like 'innodb_buffer_pool_size';

設(shè)置 innodb_buffer_pool_size

修改/etc/my.cnf 文件，在[mysqld]下面添加如下內(nèi)容

innodb_buffer_pool_size = 2048M

重啟 MySQL Server 進(jìn)入后，查看設(shè)置已經(jīng)生效。

12. innodb_flush_log_at_trx_commit

主要控制了innodb將log buffer中的數(shù)據(jù)寫(xiě)入日志文件并flush磁盤(pán)的時(shí)間點(diǎn)，取值分別為0、

1、2 三個(gè)。0，表示當(dāng)事務(wù)提交時(shí)，不做日志寫(xiě)入操作，而是每秒鐘將 log buffer 中的數(shù)據(jù)

寫(xiě)入日志文件并 flush 磁盤(pán)一次；1，則在每秒鐘或是每次事物的提交都會(huì)引起日志文件寫(xiě)入、

flush 磁盤(pán)的操作，確保了事務(wù)的 ACID；設(shè)置為 2，每次事務(wù)提交引起寫(xiě)入日志文件的動(dòng)作，

但每秒鐘完成一次 flush 磁盤(pán)操作。

實(shí)際測(cè)試發(fā)現(xiàn)，該值對(duì)插入數(shù)據(jù)的速度影響非常大，設(shè)置為 2 時(shí)插入 10000 條記錄只需要 2

秒，設(shè)置為 0 時(shí)只需要 1 秒，而設(shè)置為 1 時(shí)則需要 229 秒。因此，MySQL 手冊(cè)也建議盡量

將插入操作合并成一個(gè)事務(wù)，這樣可以大幅提高速度。

根據(jù) MySQL 手冊(cè)，在允許丟失最近部分事務(wù)的危險(xiǎn)的前提下，可以把該值設(shè)為 0 或 2。

13.innodb_thread_concurrency = 0

此參數(shù)用來(lái)設(shè)置 innodb 線程的并發(fā)數(shù)量，默認(rèn)值為 0 表示不限制，若要設(shè)置則與服務(wù)器的

CPU 核數(shù)相同或是 cpu 的核數(shù)的 2 倍，建議用默認(rèn)設(shè)置，一般為 8.

14. innodb_log_buffer_size

此參數(shù)確定些日志文件所用的內(nèi)存大小，以 M 為單位。緩沖區(qū)更大能提高性能，對(duì)于較大

的事務(wù)，可以增大緩存大小。

innodb_log_buffer_size=32M

15. innodb_log_file_size = 50M

此參數(shù)確定數(shù)據(jù)日志文件的大小，以 M 為單位，更大的設(shè)置可以提高性能.

16. innodb_log_files_in_group = 3

為提高性能，MySQL 可以以循環(huán)方式將日志文件寫(xiě)到多個(gè)文件。推薦設(shè)置為 3

17.read_buffer_size = 1M

MySql 讀入緩沖區(qū)大小。對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配一個(gè)讀入緩沖區(qū)，MySql 會(huì)為它分

配一段內(nèi)存緩沖區(qū)。如果對(duì)表的順序掃描請(qǐng)求非常頻繁，并且你認(rèn)為頻繁掃描進(jìn)行得太慢，

可以通過(guò)增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。和 sort_buffer_size 一樣，該參數(shù)

對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享。

18.read_rnd_buffer_size = 16M

MySql 的隨機(jī)讀（查詢(xún)操作）緩沖區(qū)大小。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)，

將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢(xún)時(shí)，MySql 會(huì)首先掃描一遍該緩沖，以避免磁盤(pán)搜

索，提高查詢(xún)速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但 MySql 會(huì)為每個(gè)客戶(hù)連接

發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開(kāi)銷(xiāo)過(guò)大。

注：順序讀是指根據(jù)索引的葉節(jié)點(diǎn)數(shù)據(jù)就能順序地讀取所需要的行數(shù)據(jù)。隨機(jī)讀是指一般需

要根據(jù)輔助索引葉節(jié)點(diǎn)中的主鍵尋找實(shí)際行數(shù)據(jù)，而輔助索引和主鍵所在的數(shù)據(jù)段不同，因

此訪問(wèn)方式是隨機(jī)的。

19.bulk_insert_buffer_size = 64M

批量插入數(shù)據(jù)緩存大小，可以有效提高插入效率，默認(rèn)為 8M

20.binary log

log-bin=/usr/local/mysql/data/mysql-bin

binlog_cache_size = 2M //為每個(gè) session 分配的內(nèi)存，在事務(wù)過(guò)程中用來(lái)存儲(chǔ)二進(jìn)制日志

的緩存, 提高記錄 bin-log 的效率。沒(méi)有什么大事務(wù)，dml 也不是很頻繁的情況下可以設(shè)置小

一點(diǎn)，如果事務(wù)大而且多，dml 操作也頻繁，則可以適當(dāng)?shù)恼{(diào)大一點(diǎn)。前者建議是--1M，后

者建議是：即 2--4M

max_binlog_cache_size = 8M //表示的是 binlog 能夠使用的最大 cache 內(nèi)存大小

max_binlog_size = 512M //指定 binlog 日志文件的大小，如果當(dāng)前的日志大小達(dá)到

max_binlog_size，還會(huì)自動(dòng)創(chuàng)建新的二進(jìn)制日志。你不能將該變量設(shè)置為大于 1GB 或小于

4096 字節(jié)。 默認(rèn)值是 1GB。在導(dǎo)入大容量的 sql 文件時(shí)，建議關(guān)閉 sql_log_bin，否則硬盤(pán)

扛不住，而且建議定期做刪除。

expire_logs_days = 7 //定義了 mysql 清除過(guò)期日志的時(shí)間。

二進(jìn)制日志自動(dòng)刪除的天數(shù)。默認(rèn)值為 0,表示“沒(méi)有自動(dòng)刪除”。

mysqladmin flush-logs 也可以重新開(kāi)始新的 binary log

在優(yōu)化之前執(zhí)行 mysqlslap 工具進(jìn)行測(cè)試

[root@localhost  ~]#mysqlslap  --defaults-file=/etc/my.cnf  --concurrency=10  --iterations=1

--create-schema='test1'  --query='select  *  from  test1.tb1'  --engine=innodb

--number-of-queries=2000 -uroot -p123456 –verbose

顯示結(jié)果：

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 13.837 seconds

Minimum number of seconds to run all queries: 13.837 seconds

Maximum number of seconds to run all queries: 13.837 seconds

Number of clients running queries: 10

Average number of queries per client: 200

優(yōu)化之后執(zhí)行 mysqlslap 工具進(jìn)行測(cè)試

[root@localhost  ~]#mysqlslap  --defaults-file=/etc/my.cnf  --concurrency=10  --iterations=1

--create-schema='test1'  --query='select  *  from  test1.tb1'  --engine=innodb

--number-of-queries=2000 -uroot -p123456 –verbose

顯示結(jié)果：

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 4.199 seconds

Minimum number of seconds to run all queries: 4.199 seconds

Maximum number of seconds to run all queries: 4.199 seconds

Number of clients running queries: 10

Average number of queries per client: 200

相關(guān)優(yōu)化參數(shù)總結(jié)：

[mysqld]

slow_query_log = 1

slow_query_log_file = /usr/local/mysql/data/slow-query.log

long_query_time = 1

log-queries-not-using-indexes

max_connections = 1024

back_log = 128

wait_timeout = 60

interactive_timeout = 7200

key_buffer_size=256M

query_cache_size = 256M

query_cache_type=1

query_cache_limit=50M

max_connect_errors=20

sort_buffer_size = 2M

max_allowed_packet=32M

join_buffer_size=2M

thread_cache_size=200

innodb_buffer_pool_size = 2048M

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size=32M

innodb_log_file_size=128M

innodb_log_files_in_group=3

log-bin=mysql-bin

binlog_cache_size=2M

max_binlog_cache_size=8M

max_binlog_size=512M

expire_logs_days=7

read_buffer_size=1M

read_rnd_buffer_size=16M

bulk_insert_buffer_size=64M

log-error = /usr/local/mysql/data/mysqld.err

謝謝觀看，真心的希望能幫到您！

本文出自 “一盞燭光” 博客，謝絕轉(zhuǎn)載！