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