分分鐘解決 MySQL 查詢(xún)速度慢與性能差
作者：唐立勇
一、什么影響了數(shù)據(jù)庫(kù)查詢(xún)速度
1.1 影響數(shù)據(jù)庫(kù)查詢(xún)速度的四個(gè)因素
1.2 風(fēng)險(xiǎn)分析
QPS：?QueriesPerSecond意思是“每秒查詢(xún)率”，是一臺(tái)服務(wù)器每秒能夠相應(yīng)的查詢(xún)次數(shù)，是對(duì)一個(gè)特定的查詢(xún)服務(wù)器在規(guī)定時(shí)間內(nèi)所處理流量多少的衡量標(biāo)準(zhǔn)。
TPS：?是?TransactionsPerSecond的縮寫(xiě)，也就是事務(wù)數(shù)/秒。它是軟件測(cè)試結(jié)果的測(cè)量單位?？蛻魴C(jī)在發(fā)送請(qǐng)求時(shí)開(kāi)始計(jì)時(shí)，收到服務(wù)器響應(yīng)后結(jié)束計(jì)時(shí)，以此來(lái)計(jì)算使用的時(shí)間和完成的事務(wù)個(gè)數(shù)。
Tips：?最好不要在主庫(kù)上數(shù)據(jù)庫(kù)備份，大型活動(dòng)前取消這樣的計(jì)劃。
效率低下的?sql：超高的?QPS與?TPS。
大量的并發(fā)：數(shù)據(jù)連接數(shù)被占滿（?max_connection默認(rèn)?100，一般把連接數(shù)設(shè)置得大一些）。 并發(fā)量:同一時(shí)刻數(shù)據(jù)庫(kù)服務(wù)器處理的請(qǐng)求數(shù)量
超高的?CPU使用率：?CPU資源耗盡出現(xiàn)宕機(jī)。
磁盤(pán)?IO：磁盤(pán)?IO性能突然下降、大量消耗磁盤(pán)性能的計(jì)劃任務(wù)。解決：更快磁盤(pán)設(shè)備、調(diào)整計(jì)劃任務(wù)、做好磁盤(pán)維護(hù)。
1.3 網(wǎng)卡流量：如何避免無(wú)法連接數(shù)據(jù)庫(kù)的情況
減少?gòu)姆?wù)器的數(shù)量（從服務(wù)器會(huì)從主服務(wù)器復(fù)制日志）
進(jìn)行分級(jí)緩存（避免前端大量緩存失效）
避免使用?select*?進(jìn)行查詢(xún)
分離業(yè)務(wù)網(wǎng)絡(luò)和服務(wù)器網(wǎng)絡(luò)
1.4 大表帶來(lái)的問(wèn)題（?重要）
1.4.1 大表的特點(diǎn)
記錄行數(shù)巨大，單表超千萬(wàn)
表數(shù)據(jù)文件巨大，超過(guò)?10個(gè)?G
1.4.2 大表的危害
1.慢查詢(xún)：很難在短時(shí)間內(nèi)過(guò)濾出需要的數(shù)據(jù)?查詢(xún)字區(qū)分度低 -> 要在大數(shù)據(jù)量的表中篩選出來(lái)其中一部分?jǐn)?shù)據(jù)會(huì)產(chǎn)生大量的磁盤(pán)?io?-> 降低磁盤(pán)效率
2.對(duì)?DDL影響：
建立索引需要很長(zhǎng)時(shí)間：
MySQL-v<5.5?建立索引會(huì)鎖表
MySQL-v>=5.5?建立索引會(huì)造成主從延遲（?mysql建立索引，先在組上執(zhí)行，再在庫(kù)上執(zhí)行）
修改表結(jié)構(gòu)需要長(zhǎng)時(shí)間的鎖表：會(huì)造成長(zhǎng)時(shí)間的主從延遲('480秒延遲')
1.4.3 如何處理數(shù)據(jù)庫(kù)上的大表
分庫(kù)分表把一張大表分成多個(gè)小表
難點(diǎn)：
分表主鍵的選擇
分表后跨分區(qū)數(shù)據(jù)的查詢(xún)和統(tǒng)計(jì)
1.5 大事務(wù)帶來(lái)的問(wèn)題（?重要*）*
1.5.1 什么是事務(wù)
1.5.2事務(wù)的?ACID屬性
1、原子性（?atomicity)：全部成功，全部回滾失敗。銀行存取款。
2、一致性（consistent)：銀行轉(zhuǎn)賬的總金額不變。
3、隔離性（isolation)：
隔離性等級(jí)：
未提交讀(?READ UNCOMMITED)?臟讀,兩個(gè)事務(wù)之間互相可見(jiàn)；
已提交讀(?READ COMMITED)符合隔離性的基本概念,一個(gè)事務(wù)進(jìn)行時(shí)，其它已提交的事物對(duì)于該事務(wù)是可見(jiàn)的，即可以獲取其它事務(wù)提交的數(shù)據(jù)。
可重復(fù)讀(?REPEATABLE READ)??InnoDB的默認(rèn)隔離等級(jí)。事務(wù)進(jìn)行時(shí)，其它所有事務(wù)對(duì)其不可見(jiàn)，即多次執(zhí)行讀，得到的結(jié)果是一樣的！
可串行化（?SERIALIZABLE） 在讀取的每一行數(shù)據(jù)上都加鎖，會(huì)造成大量的鎖超時(shí)和鎖征用，嚴(yán)格數(shù)據(jù)一致性且沒(méi)有并發(fā)是可使用。
查看系統(tǒng)的事務(wù)隔離級(jí)別：?show variables like'%iso%';
開(kāi)啟一個(gè)新事務(wù)：?begin;
提交一個(gè)事務(wù)：?commit;
修改事物的隔離級(jí)別：?setsession tx_isolation='read-committed';
4、持久性(?DURABILITY)：從數(shù)據(jù)庫(kù)的角度的持久性，磁盤(pán)損壞就不行了
redolog機(jī)制保證事務(wù)更新的一致性和持久性
1.5.3 大事務(wù)
運(yùn)行時(shí)間長(zhǎng)，操作數(shù)據(jù)比較多的事務(wù)；
風(fēng)險(xiǎn)：鎖定數(shù)據(jù)太多，回滾時(shí)間長(zhǎng)，執(zhí)行時(shí)間長(zhǎng)。
鎖定太多數(shù)據(jù)，造成大量阻塞和鎖超時(shí)；
回滾時(shí)所需時(shí)間比較長(zhǎng)，且數(shù)據(jù)仍然會(huì)處于鎖定；
如果執(zhí)行時(shí)間長(zhǎng)，將造成主從延遲，因?yàn)橹挥挟?dāng)主服務(wù)器全部執(zhí)行完寫(xiě)入日志時(shí)，從服務(wù)器才會(huì)開(kāi)始進(jìn)行同步，造成延遲。
解決思路：
避免一次處理太多數(shù)據(jù)，可以分批次處理；
移出不必要的?SELECT操作，保證事務(wù)中只有必要的寫(xiě)操作。
二、什么影響了MySQL性能（?非常重要)
2.1 影響性能的幾個(gè)方面
服務(wù)器硬件。
服務(wù)器系統(tǒng)（系統(tǒng)參數(shù)優(yōu)化）。
存儲(chǔ)引擎。?MyISAM：?不支持事務(wù)，表級(jí)鎖。?InnoDB:?支持事務(wù)，支持行級(jí)鎖，事務(wù)?ACID。
數(shù)據(jù)庫(kù)參數(shù)配置。
數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)和SQL語(yǔ)句。（重點(diǎn)優(yōu)化）
2.2 MySQL體系結(jié)構(gòu)
分三層：客戶端->服務(wù)層->存儲(chǔ)引擎
MySQL是?插件式的存儲(chǔ)引擎，其中存儲(chǔ)引擎分很多種。只要實(shí)現(xiàn)符合mysql存儲(chǔ)引擎的接口，可以開(kāi)發(fā)自己的存儲(chǔ)引擎!
所有跨存儲(chǔ)引擎的功能都是在服務(wù)層實(shí)現(xiàn)的。
MySQL的存儲(chǔ)引擎是針對(duì)表的，不是針對(duì)庫(kù)的。也就是說(shuō)在一個(gè)數(shù)據(jù)庫(kù)中可以使用不同的存儲(chǔ)引擎。但是不建議這樣做。
2.3 InnoDB存儲(chǔ)引擎
MySQL5.5及之后版本默認(rèn)的存儲(chǔ)引擎：?InnoDB。
2.3.1 InnoDB使用表空間進(jìn)行數(shù)據(jù)存儲(chǔ)。
show variables like'innodb_file_per_table
如果innodbfileper_table 為 ON 將建立獨(dú)立的表空間，文件為tablename.ibd；
如果innodbfileper_table 為 OFF 將數(shù)據(jù)存儲(chǔ)到系統(tǒng)的共享表空間，文件為ibdataX（X為從1開(kāi)始的整數(shù)）；
.frm?：是服務(wù)器層面產(chǎn)生的文件，類(lèi)似服務(wù)器層的數(shù)據(jù)字典，記錄表結(jié)構(gòu)。
2.3.2 (MySQL5.5默認(rèn))系統(tǒng)表空間與(?MySQL5.6及以后默認(rèn))獨(dú)立表空間
1.1 系統(tǒng)表空間無(wú)法簡(jiǎn)單的收縮文件大小，造成空間浪費(fèi)，并會(huì)產(chǎn)生大量的磁盤(pán)碎片。
1.2 獨(dú)立表空間可以通過(guò)?optimeze table?收縮系統(tǒng)文件，不需要重啟服務(wù)器也不會(huì)影響對(duì)表的正常訪問(wèn)。
2.1 如果對(duì)多個(gè)表進(jìn)行刷新時(shí)，實(shí)際上是順序進(jìn)行的，會(huì)產(chǎn)生IO瓶頸。
2.2 獨(dú)立表空間可以同時(shí)向多個(gè)文件刷新數(shù)據(jù)。
強(qiáng)烈建立對(duì)Innodb 使用獨(dú)立表空間，優(yōu)化什么的更方便，可控。
2.3.3 系統(tǒng)表空間的表轉(zhuǎn)移到獨(dú)立表空間中的方法
1、使用mysqldump 導(dǎo)出所有數(shù)據(jù)庫(kù)數(shù)據(jù)（存儲(chǔ)過(guò)程、觸發(fā)器、計(jì)劃任務(wù)一起都要導(dǎo)出 ）可以在從服務(wù)器上操作。
2、停止MYsql 服務(wù)器，修改參數(shù)（my.cnf加入innodbfileper_table），并刪除Inoodb相關(guān)文件（可以重建Data目錄）。
3、重啟MYSQL，并重建Innodb系統(tǒng)表空間。
4、 重新導(dǎo)入數(shù)據(jù)。
或者?Altertable?同樣可以的轉(zhuǎn)移，但是無(wú)法回收系統(tǒng)表空間中占用的空間。
2.4 InnoDB存儲(chǔ)引擎的特性
2.4.1 特性一：事務(wù)性存儲(chǔ)引擎及兩個(gè)特殊日志類(lèi)型：Redo Log 和 Undo Log
Innodb?是一種事務(wù)性存儲(chǔ)引擎。
完全支持事務(wù)的?ACID特性。
支持事務(wù)所需要的兩個(gè)特殊日志類(lèi)型：?RedoLog?和?UndoLog
Redo Log：?實(shí)現(xiàn)事務(wù)的持久性(已提交的事務(wù))。?Undo Log：?未提交的事務(wù)，獨(dú)立于表空間，需要隨機(jī)訪問(wèn)，可以存儲(chǔ)在高性能io設(shè)備上。
Undo日志記錄某數(shù)據(jù)被修改前的值，可以用來(lái)在事務(wù)失敗時(shí)進(jìn)行?rollback；?Redo日志記錄某數(shù)據(jù)塊被修改后的值，可以用來(lái)恢復(fù)未寫(xiě)入?data file的已成功事務(wù)更新的數(shù)據(jù)。
2.4.2 特性二：支持行級(jí)鎖
InnoDB支持行級(jí)鎖。
行級(jí)鎖可以最大程度地支持并發(fā)。
行級(jí)鎖是由存儲(chǔ)引擎層實(shí)現(xiàn)的。
2.5 什么是鎖
2.5.1 鎖
2.5.2 鎖類(lèi)型
2.5.3 鎖的粒度
MySQL的事務(wù)支持不是綁定在MySQL服務(wù)器本身，?而是與存儲(chǔ)引擎相關(guān)
將table_name加表級(jí)鎖命令：?locktable table_name write;??寫(xiě)鎖會(huì)阻塞其它用戶對(duì)該表的‘讀寫(xiě)’操作，直到寫(xiě)鎖被釋放：?unlock tables；
鎖的開(kāi)銷(xiāo)越大，粒度越小，并發(fā)度越高。
表級(jí)鎖通常是在服務(wù)器層實(shí)現(xiàn)的。
行級(jí)鎖是存儲(chǔ)引擎層實(shí)現(xiàn)的。innodb的鎖機(jī)制，服務(wù)器層是不知道的
2.5.4 阻塞和死鎖
（1）阻塞是由于資源不足引起的排隊(duì)等待現(xiàn)象。 （2）死鎖是由于兩個(gè)對(duì)象在擁有一份資源的情況下申請(qǐng)另一份資源，而另一份資源恰好又是這兩對(duì)象正持有的，導(dǎo)致兩對(duì)象無(wú)法完成操作，且所持資源無(wú)法釋放。
2.6 如何選擇正確的存儲(chǔ)引擎
參考條件：
事務(wù)
備份(?Innobd免費(fèi)在線備份)
崩潰恢復(fù)
存儲(chǔ)引擎的特有特性
總結(jié):?Innodb?大法好。
注意:?盡量別使用混合存儲(chǔ)引擎，比如回滾會(huì)出問(wèn)題在線熱備問(wèn)題。
2.7 配置參數(shù)
2.7.1 內(nèi)存配置相關(guān)參數(shù)
確定可以使用的內(nèi)存上限。
內(nèi)存的使用上限不能超過(guò)物理內(nèi)存，否則容易造成內(nèi)存溢出；（對(duì)于32位操作系統(tǒng)，MySQL只能試用3G以下的內(nèi)存。）
確定MySQL的?每個(gè)連接?單獨(dú)?使用的內(nèi)存。
sort_buffer_size #定義了每個(gè)線程排序緩存區(qū)的大小，MySQL在有查詢(xún)、需要做排序操作時(shí)才會(huì)為每個(gè)緩沖區(qū)分配內(nèi)存（直接分配該參數(shù)的全部?jī)?nèi)存）； join_buffer_size #定義了每個(gè)線程所使用的連接緩沖區(qū)的大小，如果一個(gè)查詢(xún)關(guān)聯(lián)了多張表，MySQL會(huì)為每張表分配一個(gè)連接緩沖，導(dǎo)致一個(gè)查詢(xún)產(chǎn)生了多個(gè)連接緩沖； read_buffer_size #定義了當(dāng)對(duì)一張MyISAM進(jìn)行全表掃描時(shí)所分配讀緩沖池大小，MySQL有查詢(xún)需要時(shí)會(huì)為其分配內(nèi)存，其必須是4k的倍數(shù)； read_rnd_buffer_size #索引緩沖區(qū)大小，MySQL有查詢(xún)需要時(shí)會(huì)為其分配內(nèi)存，只會(huì)分配需要的大小。
注意：?以上四個(gè)參數(shù)是為一個(gè)線程分配的，如果有100個(gè)連接，那么需要×100。
MySQL數(shù)據(jù)庫(kù)實(shí)例：
?、費(fèi)ySQL是?單進(jìn)程多線程（而oracle是多進(jìn)程），也就是說(shuō)?MySQL實(shí)例在系統(tǒng)上表現(xiàn)就是一個(gè)服務(wù)進(jìn)程，即進(jìn)程；
?、贛ySQL實(shí)例是線程和內(nèi)存組成，實(shí)例才是真正用于操作數(shù)據(jù)庫(kù)文件的；
一般情況下一個(gè)實(shí)例操作一個(gè)或多個(gè)數(shù)據(jù)庫(kù)；集群情況下多個(gè)實(shí)例操作一個(gè)或多個(gè)數(shù)據(jù)庫(kù)。
如何為緩存池分配內(nèi)存：
Innodb_buffer_pool_size，定義了Innodb所使用緩存池的大小，對(duì)其性能十分重要，必須足夠大，但是過(guò)大時(shí)，使得Innodb 關(guān)閉時(shí)候需要更多時(shí)間把臟頁(yè)從緩沖池中刷新到磁盤(pán)中；
總內(nèi)存-（每個(gè)線程所需要的內(nèi)存*連接數(shù)）-系統(tǒng)保留內(nèi)存
key_buffer_size，定義了MyISAM所使用的緩存池的大小，由于數(shù)據(jù)是依賴(lài)存儲(chǔ)操作系統(tǒng)緩存的，所以要為操作系統(tǒng)預(yù)留更大的內(nèi)存空間；
select?sum(index_length)?from?information_schema.talbes?where?e``ngine=``'myisam'
注意：?即使開(kāi)發(fā)使用的表全部是Innodb表，也要為MyISAM預(yù)留內(nèi)存，因?yàn)镸ySQL系統(tǒng)使用的表仍然是MyISAM表。
max_connections?控制允許的最大連接數(shù)， 一般2000更大。
不要使用外鍵約束保證數(shù)據(jù)的完整性。
2.8 性能優(yōu)化順序
從上到下：
