一、mysql性能下降的原因及對(duì)策

• 數(shù)據(jù)過(guò)多–>分庫(kù)分表
• 關(guān)聯(lián)了太多的表，太多join–>Sql優(yōu)化
• 沒(méi)有充分利用到索引–>利用索引
• 服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)設(shè)置–>調(diào)整my.cnf
  以下是7種join圖示：
  

二、索引優(yōu)化

什么是索引
MySQL官方對(duì)索引的定義為：

索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)。

索引的目的在于提高查詢(xún)效率，可以類(lèi)比字典。

如果要查“mysql”這個(gè)單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql。如果沒(méi)有索引，那么你可能需要a----z，如果我想找到Java開(kāi)頭的單詞呢？或者Oracle開(kāi)頭的單詞呢？

可以簡(jiǎn)單理解成：排序好的快速查找數(shù)據(jù)結(jié)構(gòu)，如下圖示：

結(jié)論：數(shù)據(jù)本身之外，數(shù)據(jù)庫(kù)還維護(hù)著一個(gè)滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。而且，一般來(lái)說(shuō)索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤(pán)上

索引的優(yōu)勢(shì)
類(lèi)似大學(xué)圖書(shū)館建書(shū)目索引，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的IO成本。
通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗。

索引的劣勢(shì)

1.雖然索引大大提高了查詢(xún)速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息
2.實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的。

mysql索引結(jié)構(gòu)(B+樹(shù))
先看如果使用B樹(shù)的索引結(jié)構(gòu)

再看使用 B+樹(shù)建立索引的結(jié)構(gòu)

B樹(shù)和B+樹(shù)的對(duì)比

聚簇索引與非聚簇索引

聚簇索引并不是一種單獨(dú)的索引類(lèi)型，而是一種數(shù)據(jù)存儲(chǔ)方式。術(shù)語(yǔ)‘聚簇’表示數(shù)據(jù)行和相鄰的鍵值聚簇的存儲(chǔ)在一起。
如下圖，左側(cè)的索引就是聚簇索引，因?yàn)閿?shù)據(jù)行在磁盤(pán)的排列和索引排序保持一致。

聚簇索引的好處：

按照聚簇索引排列順序，查詢(xún)顯示一定范圍數(shù)據(jù)的時(shí)候，由于數(shù)據(jù)都是緊密相連，數(shù)據(jù)庫(kù)不不用從多個(gè)數(shù)據(jù)塊中提取數(shù)據(jù)，所以節(jié)省了大量的io操作。

聚簇索引的限制：

1.對(duì)于mysql數(shù)據(jù)庫(kù)目前只有innodb數(shù)據(jù)引擎支持聚簇索引，而Myisam并不支持聚簇索引。
2.由于數(shù)據(jù)物理存儲(chǔ)排序方式只能有一種，所以每個(gè)Mysql的表只能有一個(gè)聚簇索引。一般情況下就是該表的主鍵。
3.為了充分利用聚簇索引的聚簇的特性，所以innodb表的主鍵列盡量選用有序的順序id，而不建議用無(wú)序的id，比如uuid這種。

三、mysql索引分類(lèi)

單值索引：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引

語(yǔ)法：

隨表一起建索引： CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name) ); 單獨(dú)建單值索引： CREATE INDEX idx_customer_name ON customer(customer_name); 刪除索引： DROP INDEX idx_customer_name on customer;

唯一索引：索引列的值必須唯一，但允許有空值
語(yǔ)法：

隨表一起建索引： CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_no) ); 單獨(dú)建唯一索引： CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 刪除索引： DROP INDEX idx_customer_no on customer ;

主鍵索引：設(shè)定為主鍵后數(shù)據(jù)庫(kù)會(huì)自動(dòng)建立索引，innodb為聚簇索引
語(yǔ)法：

隨表一起建索引： CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id) ); CREATE TABLE customer2 (id INT(10) UNSIGNED ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id) );單獨(dú)建主鍵索引： ALTER TABLE customer add PRIMARY KEY customer(customer_no); 刪除建主鍵索引： ALTER TABLE customer drop PRIMARY KEY ; 修改建主鍵索引： 必須先刪除掉(drop)原索引，再新建(add)索引

復(fù)合索引：即一個(gè)索引包含多個(gè)列
語(yǔ)法：

隨表一起建索引： CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_name),KEY (customer_no,customer_name) ); 單獨(dú)建索引： CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 刪除索引： DROP INDEX idx_no_name on customer ;

基本語(yǔ)法：

創(chuàng)建：

CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))

刪除:

DROP INDEX [indexName] ON mytable;

查看:

SHOW INDEX FROM table_name

使用ALTER命令:

有四種方式來(lái)添加數(shù)據(jù)表的索引： ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語(yǔ)句添加一個(gè)主鍵，這意味著索引值必須是唯一的，且不能為NULL。ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語(yǔ)句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會(huì)出現(xiàn)多次）。ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出現(xiàn)多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語(yǔ)句指定了索引為 FULLTEXT ，用于全文索引。

四、哪些情況需要?jiǎng)?chuàng)建索引

• 主鍵自動(dòng)建立唯一索引
• 頻繁作為查詢(xún)條件的字段應(yīng)該創(chuàng)建索引
• 查詢(xún)中與其它表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
• 單鍵/組合索引的選擇問(wèn)題， 組合索引性?xún)r(jià)比更高
• 查詢(xún)中排序的字段，排序字段若通過(guò)索引去訪問(wèn)將大大提高排序速度
• 查詢(xún)中統(tǒng)計(jì)或者分組字段

五、哪些情況不要?jiǎng)?chuàng)建索引

• 表記錄太少
• 經(jīng)常增刪改的表或者字段
• Where條件里用不到的字段不創(chuàng)建索引
  為什么：

雖然提高了查詢(xún)速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。
因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件

• 過(guò)濾性不好的不適合建索引

六、性能分析（explain：執(zhí)行計(jì)劃）

是什么
使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢(xún)語(yǔ)句，從而知道MySQL是
如何處理你的SQL語(yǔ)句的。分析查詢(xún)語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。

能干嘛（主要是查看以下信息）

1.表的讀取順序
2.哪些索引可以使用
3.數(shù)據(jù)讀取操作的操作類(lèi)型
4.哪些索引被實(shí)際使用
5.表之間的引用
6.每張表有多少行被物理查詢(xún)

怎么使用

Explain + SQL語(yǔ)句

各字段解釋
參考

總結(jié)

以上是生活随笔為你收集整理的Mysql优化分析的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。