六乡摹、優(yōu)化手段
主要以查詢優(yōu)化采转、索引使用和表結(jié)構(gòu)設(shè)計方面進行講解趟卸。
6.1 查詢優(yōu)化
1) 避免 SELECT *，需要什么數(shù)據(jù)锄列，就查詢對應(yīng)的字段惯悠。
2) 小表驅(qū)動大表邻邮，即小的數(shù)據(jù)集驅(qū)動大的數(shù)據(jù)集克婶。如：以 A筒严，B 兩表為例情萤，兩表通過 id 字段進行關(guān)聯(lián)鸭蛙。
當 B 表的數(shù)據(jù)集小于 A 表時筋岛，用in優(yōu)化 exist娶视；使用in睁宰，兩表執(zhí)行順序是先查 B 表肪获，再查 A 表select * from Awhereidin(select id from B)當 A 表的數(shù)據(jù)集小于 B 表時柒傻，用 exist 優(yōu)化in孝赫；使用 exists红符，兩表執(zhí)行順序是先查 A 表青柄，再查 B 表select * from Awhereexists (select 1 from BwhereB.id = A.id)
3) 一些情況下预侯，可以使用連接代替子查詢致开，因為使用 join雌桑，MySQL 不會在內(nèi)存中創(chuàng)建臨時表。
4) 適當添加冗余字段校坑，減少表關(guān)聯(lián)拣技。
5) 合理使用索引（下文介紹）耍目。如：為排序膏斤、分組字段建立索引邪驮，避免 filesort 的出現(xiàn)莫辨。
6.2 索引使用
6.2.1 適合使用索引的場景
1) 主鍵自動創(chuàng)建唯一索引
2) 頻繁作為查詢條件的字段
3) 查詢中與其他表關(guān)聯(lián)的字段
4) 查詢中排序的字段
5) 查詢中統(tǒng)計或分組字段
6.2.2 不適合使用索引的場景
1) 頻繁更新的字段
2) where 條件中用不到的字段
3) 表記錄太少
4) 經(jīng)常增刪改的表
5) 字段的值的差異性不大或重復性高
6.2.3 索引創(chuàng)建和使用原則
1) 單表查詢：哪個列作查詢條件，就在該列創(chuàng)建索引
2) 多表查詢：left join 時沮榜，索引添加到右表關(guān)聯(lián)字段；right join 時蟆融，索引添加到左表關(guān)聯(lián)字段
3) 不要對索引列進行任何操作（計算、函數(shù)型酥、類型轉(zhuǎn)換）
4) 索引列中不要使用 !=山憨，<> 非等于
5) 索引列不要為空弥喉，且不要使用 is null 或 is not null 判斷
6) 索引字段是字符串類型郁竟，查詢條件的值要加''單引號,避免底層類型自動轉(zhuǎn)換
違背上述原則可能會導致索引失效由境，具體情況需要使用 explain 命令進行查看
6.2.4 索引失效情況
除了違背索引創(chuàng)建和使用原則外，如下情況也會導致索引失效：
1) 模糊查詢時藻肄，以 % 開頭
2) 使用 or 時蔑舞，如：字段1（非索引）or 字段2（索引）會導致索引失效嘹屯。
3) 使用復合索引時攻询，不使用第一個索引列州弟。
index(a,b,c) 钧栖，以字段 a,b,c 作為復合索引為例：
語句索引是否生效
where a = 1是婆翔，字段 a 索引生效
where a = 1 and b = 2是拯杠，字段 a 和 b 索引生效
where a = 1 and b = 2 and c = 3是啃奴，全部生效
where b = 2 或 where c = 3否
where a = 1 and c = 3字段 a 生效潭陪，字段 c 失效
where a = 1 and b > 2 and c = 3字段 a最蕾，b 生效依溯，字段 c 失效
where a = 1 and b like 'xxx%' and c = 3字段 a瘟则，b 生效黎炉，字段 c 失效
6.3 數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計
6.3.1 選擇合適的數(shù)據(jù)類型
1) 使用可以存下數(shù)據(jù)最小的數(shù)據(jù)類型
2) 使用簡單的數(shù)據(jù)類型醋拧。int 要比 varchar 類型在mysql處理簡單
3) 盡量使用 tinyint淀弹、smallint、mediumint 作為整數(shù)類型而非 int
4) 盡可能使用 not null 定義字段庆械，因為 null 占用4字節(jié)空間
5) 盡量少用 text 類型,非用不可時最好考慮分表
6) 盡量使用 timestamp 而非 datetime
7) 單表不要有太多字段，建議在 20 以內(nèi)
6.3.2 表的拆分
當數(shù)據(jù)庫中的數(shù)據(jù)非常大時干奢，查詢優(yōu)化方案也不能解決查詢速度慢的問題時，我們可以考慮拆分表忿峻，讓每張表的數(shù)據(jù)量變小辕羽，從而提高查詢效率逛尚。
1) 垂直拆分：將表中多個列分開放到不同的表中。例如用戶表中一些字段經(jīng)常被訪問刁愿，將這些字段放在一張表中绰寞，另外一些不常用的字段放在另一張表中铣口。
插入數(shù)據(jù)時滤钱，使用事務(wù)確保兩張表的數(shù)據(jù)一致性脑题。
2) 水平拆分：按照行進行拆分件缸。例如用戶表中叔遂，使用用戶ID他炊，對用戶ID取10的余數(shù)已艰，將用戶數(shù)據(jù)均勻的分配到0~9的10個用戶表中痊末。查找時也按照這個規(guī)則查詢數(shù)據(jù)哩掺。
6.3.3 讀寫分離
一般情況下對數(shù)據(jù)庫而言都是“讀多寫少”凿叠。換言之嚼吞，數(shù)據(jù)庫的壓力多數(shù)是因為大量的讀取數(shù)據(jù)的操作造成的盒件。我們可以采用數(shù)據(jù)庫集群的方案誊薄，使用一個庫作為主庫履恩，負責寫入數(shù)據(jù)呢蔫；其他庫為從庫切心，負責讀取數(shù)據(jù)。這樣可以緩解對數(shù)據(jù)庫的訪問壓力绽昏。
七、服務(wù)器參數(shù)調(diào)優(yōu)
7.1 內(nèi)存相關(guān)
sort_buffer_size 排序緩沖區(qū)內(nèi)存大小
join_buffer_size 使用連接緩沖區(qū)大小
read_buffer_size 全表掃描時分配的緩沖區(qū)大小
7.2 IO 相關(guān)
Innodb_log_file_size 事務(wù)日志大小
Innodb_log_files_in_group 事務(wù)日志個數(shù)
Innodb_log_buffer_size 事務(wù)日志緩沖區(qū)大小
Innodb_flush_log_at_trx_commit 事務(wù)日志刷新策略 全谤，其值如下：
0：每秒進行一次 log 寫入 cache，并 flush log 到磁盤
1：在每次事務(wù)提交執(zhí)行 log 寫入 cache认然，并 flush log 到磁盤
2：每次事務(wù)提交补憾，執(zhí)行 log 數(shù)據(jù)寫到 cache卷员，每秒執(zhí)行一次 flush log 到磁盤
7.3 安全相關(guān)
expire_logs_days 指定自動清理 binlog 的天數(shù)
max_allowed_packet 控制 MySQL 可以接收的包的大小
skip_name_resolve 禁用 DNS 查找
read_only 禁止非 super 權(quán)限用戶寫權(quán)限
skip_slave_start 級你用 slave 自動恢復
7.4 其他
max_connections 控制允許的最大連接數(shù)
tmp_table_size 臨時表大小
max_heap_table_size 最大內(nèi)存表大小
筆者并沒有使用這些參數(shù)對 MySQL 服務(wù)器進行調(diào)優(yōu)，具體詳情介紹和性能效果請參考文章末尾的資料或另行百度毕骡。
八削饵、硬件選購和參數(shù)優(yōu)化
硬件的性能直接決定 MySQL 數(shù)據(jù)庫的性能未巫。硬件的性能瓶頸窿撬，直接決定 MySQL 數(shù)據(jù)庫的運行數(shù)據(jù)和效率叙凡。
作為軟件開發(fā)程序員劈伴，我們主要關(guān)注軟件方面的優(yōu)化內(nèi)容狭姨，以下硬件方面的優(yōu)化作為了解即可
8.1 內(nèi)存相關(guān)
內(nèi)存的 IO 比硬盤的速度快很多宰啦，可以增加系統(tǒng)的緩沖區(qū)容量饼拍，使數(shù)據(jù)在內(nèi)存停留的時間更長赡模，以減少磁盤的 IO
8.2 磁盤 I/O 相關(guān)
1) 使用 SSD 或 PCle SSD 設(shè)備师抄，至少獲得數(shù)百倍甚至萬倍的 IOPS 提升
2) 購置陣列卡同時配備 CACHE 及 BBU 模塊漓柑，可以明顯提升 IOPS
3) 盡可能選用 RAID-10叨吮，而非 RAID-5
8.3 配置 CUP 相關(guān)
在服務(wù)器的 BIOS 設(shè)置中辆布，調(diào)整如下配置：
1) 選擇 Performance Per Watt Optimized（DAPC）模式茶鉴，發(fā)揮 CPU 最大性能
2) 關(guān)閉 C1E 和 C States 等選項锋玲，提升 CPU 效率
3) Memory Frequency（內(nèi)存頻率）選擇 Maximum Performance
作者：moonlightL
鏈接：https://juejin.im/post/59d83f1651882545eb54fc7e
來源：掘金
著作權(quán)歸作者所有涵叮。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)伞插，非商業(yè)轉(zhuǎn)載請注明出處。
