一臺幾年前的舊筆記本電腦的虛擬系統運行環境，作為本次實踐的運行工具，僅供參考，
案例環境：Linux、Docker、MySQLCommunity8.0.31、InnoDB，
過早的MySQL版本不一定適用本章內容，僅圍繞 InnoDB 引擎的闡述，
一、索引
1.1 索引的管理
-- create 方式創建
create [unique] index {index_name} on {tab_name}({col_name}[(length)]);
-- alter表 方式創建
alter {tab_name} add [unique] index {index_name} on ({col_name}[(length)]);
-- 創建組合索引
create index {index_name} on ({col_name1}[(length)], {col_name2}[(length)], {col_name3}[(length)]);
-- unique：唯一索引
-- col_name：一列為單列索引；逗號隔開的多列為組合索引
-- length：欄位中前幾個字符有效，避免無限長度（通常能夠明顯區分值即可的長度；如：員工表的Email，@后面都一樣）
-- 查看表中的索引
show index from {tab_name};
-- 洗掉索引
drop index {index_name} on {tab_name};1.2 索引創建的場景
過多查詢的表，過少寫入的表，
資料量過大導致的查詢效率慢，
經常作為條件查詢的列，
批量的重復值，不適合創建索引；比如<業務狀態>列
值過少重復的列，適合創建索引；比如<usercode>、<email>列
1.3 理想的索引特征
- 盡量能夠覆寫常用欄位
- 欄位值區分度高
- 欄位長度小（合適的長度，不是越小越好，至少能足夠區分每個值）
- 相對低頻的寫入操作，以及高頻的查詢操作的表和欄位上建立索引
通過非聚集索引檢索記錄的時候，需要2次操作，先在非聚集索引中檢索出主鍵，然后再到聚集索引中檢索出主鍵對應的記錄，這個程序叫做回表，比聚集索引多了一次操作，
1.4 非主鍵索引
where全部為and時，無所謂位置，都會命中索引（當多個條件中有索引的時候，并且關系是and的時候，會自動匹配索引區分度高的）
where后面為 or 時，索引列 依影響資料范圍越精確 按序靠前寫，
1.5 索引的使用
使用原則：
- 按條件后面涉及到的列，創建出組合索引
- 越精確的條件，就排在條件的順序首位，最左匹配原則
-- 按現有資料，計算哪個列最精確；越精確的列，位置越靠前優先，
select sum(depno=28), sum(username like 'Sol%'), sum(position='dev') from tab_emp;
+---------------+---------------------------+---------------------+
| sum(depno=28) | sum(username like 'Sol%') | sum(position='dev') |
+---------------+---------------------------+---------------------+
| 366551 | 3 | 109 |
+---------------+---------------------------+---------------------+
-- 由此得出：username列的范圍最精確，應該放到where后的首位；不在組合索引的列放到最后，
-- 如下組合索引的創建方式：
create index {index_name} on {tab_name}(username,position,depno);
-- 如下組合索引的查詢方式：
select username,position,depno from tab_emp where username like 'Sol%' and position='dev' and depno=106 and age<27;1.5.1 使用索引查詢
這里準備兩張兩千萬相同表資料，測驗效果如下圖：
1.5.2 組合索引的使用
表創建的組合索引，如下圖：
兩千萬資料表，組合索引查詢效果，如下圖：
總結：組合索引所包含的列，盡量在where, order中寫全，非索引列或過少的組合索引列可能不會產生索引效果，
1.5.3 高性能分頁查詢
通常MySQL分頁時用到的limit，當limit值過大時，查詢效果會很慢，
當如 limit 9000000,10 時，需要先查詢出900萬資料，再拋掉900萬資料；這900萬的程序可否省略？
假如：每次查詢一頁時，把當前頁的最后一條資料的重要欄位都做記錄，并標識是第幾頁；當查詢它的下頁時，拿它的最后一條資料的重要欄位作為追加的查詢條件，如何呢...??
下圖示例：usercode 為主要的索引及排序欄位，上頁的最后一條作為追加條件，再往下取5條，效果有了顯著提升，(排序列重復資料呢?) 當然適用于類似code、time等這樣重復資料較少的列，
1.6 索引覆寫，避免回表查詢
當查詢的列中包含了非索引列，系統相當于掃描了兩遍資料，如果能只掃描了一遍，也提高了查詢效率，
回表查詢的程序：
- 先按已有索引查詢到資料，得出此資料的主鍵值
- 再按主鍵值，再次檢索出具體的資料，獲取其它列的值
查詢涉及到的列都為組合索引列時，包括：select、where、order、group等，索引覆寫(索引下推)，避免回表查詢，
避免使用*，以避免回表查詢；不常用的查詢列或text型別的列，盡量以單獨的擴展表存放，
通常串列資料需要的列并不多，查詢的時候可以考慮為索引列；通常詳細資訊時涵蓋的列多，可通過主鍵單獨查詢，
1.7 命中索引
1.7.1 無效索引
列型別轉換可能會導致索引無效；如：
- 字符轉數值，會導致索引無效
- 數值轉字符，不影響索引，
不建議型別的轉換，盡量按原型別查詢，
條件中的函式導致索引無效；索引列不能用在函式內，如：where abs(Id) > 200
條件中的運算式導致索引無效；如：where (Id + 1) > 200
避免單列索引與組合索引的重復列；在組合索引中的列，去除單列索引，
全模糊查詢導致索引無效；匹配開頭不會影響索引，如 'Sol%'；全模糊或'%Sol'時無效，
1.7.2 Explain
顯示執行程序，查看是否命中索引
mysql> explain select * from tab_emp where uname='Sol';
-- 可能用到的索引、實際用到的索引、掃描了的行數
+----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab_emp | range | idx_emp_uname | idx_emp_uname | 4 | const | 1 | Using index condition |
+----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+在通常情況下，能不能命中索引，取決于索引列的值重復程度；如果是極少重復的值，就很容易命中索引，如果類似于狀態或型別的值，重復程度很高，就很難命中索引，這是MySQL自動取舍的結果，
比如：沒有索引的列-電話號碼，有索引的列-部門，那么很難命中部門索引，因為MySQL認為[電話號碼]更精確；或者使用force強行命中，通常MySQL的自動取舍是最有效的，
1.8 查詢總結
避免使用*，以避免回表查詢，
不常用的查詢列或text型別的列，盡量以單獨的擴展表存放，
條件避免使用函式，
條件避免過多的or，建議使用in()/union代替，in中的資料不可以極端海量，至少個數小于1000比較穩妥，
避免子查詢，子查詢的結果集是臨時表不支持索引、或結果集過大、或重復掃描子表；以join代替子查詢，盡量以inner join代替最為妥當，
避免使用'%Sol%'查詢，或以'Sol%'代替，
二、表磁區
表磁區也就是把一張物理表的資料檔案分成若干個資料檔案存盤，使得單個資料檔案的量有限，有助于避免全表掃描資料，提升查詢性能，
那，跨區查詢的性能影響有多大，從整體看，表磁區還是帶來了不少的性能提升，
如果表中有主鍵列，磁區列必須是主鍵列之一，比如：又有自增主鍵，又想按年份磁區，那主鍵就是組合索引咯，(id+date)
2.1 磁區的種類
HASH：按演算法，平均分配到各磁區
-- 表創建 HASH 磁區12個
CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY HASH(MONTH(signed))
PARTITIONS 12;KEY：按演算法，無序不等的分配到各磁區
-- 表創建12個 KEY 磁區
CREATE TABLE clients_lk (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY LINEAR KEY(signed)
PARTITIONS 12;RANGE：按劃定的范圍將資料存放到符合的磁區
-- 按年份創建范圍磁區
CREATE TABLE tr (
id INT,
name VARCHAR(50),
purchased DATE
)
PARTITION BY RANGE(YEAR(purchased)) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000)
);LIST：按定義的一組包含值將資料存放到符合的磁區
-- LIST 分組包含方式
CREATE TABLE tt (
id INT,
data INT
)
PARTITION BY LIST(data) (
PARTITION p0 VALUES IN (5, 10, 15),
PARTITION p1 VALUES IN (6, 12, 18)
);2.2 磁區的管理
新增 HASH/KEY 磁區
-- 將原來的 12 個磁區合并為 8 個磁區
ALTER TABLE clients COALESCE PARTITION 4;
-- 在原有的基礎上增加 6 個磁區
ALTER TABLE clients ADD PARTITION PARTITIONS 6;新增 RANGE/LIST 磁區
-- RANGE 追加磁區
ALTER TABLE tr ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
-- LIST 追加新磁區（不可包含已存在的值）
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));變更 RANGE/LIST 磁區
-- RANGE 拆分原有磁區（重組磁區）
ALTER TABLE tr REORGANIZE PARTITION p0 INTO (
PARTITION n0 VALUES LESS THAN (1980),
PARTITION n1 VALUES LESS THAN (1990)
);
-- RANGE 合并相鄰磁區
ALTER TABLE tt REORGANIZE PARTITION s1,s2 INTO (
PARTITION s0 VALUES LESS THAN (1980)
);
-- LIST 重組原有磁區
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12)
);洗掉指定磁區
-- 丟掉指定磁區及其資料
ALTER TABLE {TABLE_NAME} DROP PARTITION p2,p3;
-- 洗掉指定磁區，保留資料
ALTER TABLE {TABLE_NAME} TRUNCATE PARTITION p2;
-- 洗掉表全部磁區，保留資料
ALTER TABLE {TABLE_NAME} REMOVE PARTITIONING;磁區詳細資訊
-- 查詢指定磁區的資料
SELECT * FROM tr PARTITION (p2);
-- 查詢各磁區詳細
SELECT * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tt';
-- 查看某個磁區的狀態
ALTER TABLE tr ANALYZE PARTITION p3;修復磁區
-- 檢查磁區是否損壞
ALTER TABLE tr CHECK PARTITION p1;
-- 修復磁區
ALTER TABLE tr REPAIR PARTITION p1, p2;
-- 優化磁區，整理磁區碎片
ALTER TABLE tr OPTIMIZE PARTITION p0, p1;
-- 當前磁區資料，重建磁區
ALTER TABLE tr REBUILD PARTITION p0, p1;三、查詢綜合測驗
2000萬相同資料、相同表結構，相同的查詢方式，測驗效果如下圖：(僅供參考)
資料量大了，查詢慢；加索引了，資料量越大，寫入越慢；
還是物理分表好呀~
四、SQL服務引數優化
僅列出了點官方認可的穩定性良好的可靠的引數，以 InnoDB 為主，
4.1 Connections
[mysqld]
# 保持在快取中的可用連接執行緒
# default = -1（無）
thread_cache_size = 16
# 最大的連接執行緒數(關系型資料庫)
# default = 151
max_connections = 1000
# 最大的連接執行緒數(檔案型/KV型)
# default = 100
#mysqlx_max_connections = 7004.2 緩沖區 Buffer
[mysqld]
# 緩沖區單位大小；default = 128M
innodb_buffer_pool_size = 128M
# 緩沖區總大小，記憶體的70%，單位大小的倍數
# default = 128M
innodb_buffer_pool_size = 6G
# 以上兩個引數的設定，MySQL會自動改變 innodb_buffer_pool_instances 的值4.3 Sort merge passes
[mysqld]
# 優化 order/group/distinct/join 的性能
# SHOW GLOBAL STATUS 中的 Sort_merge_passes 過多就增加設定
# default = 1K
max_sort_length = 8K
# default = 256K
sort_buffer_size = 2M
# 通常別太大，海量join時大
# default = 256K
#join_buffer_size = 128M4.4 I/O 執行緒數
[mysqld]
# 異步I/O子系統
# default = NO
innodb_use_native_aio = NO
# 讀資料執行緒數
# default = 4
innodb_read_io_threads = 32
# 寫入資料執行緒數
# default = 4
innodb_write_io_threads = 324.5 Capacity 容量
[mysqld]
# default = 200
innodb_io_capacity = 1000
# default = 2000
innodb_io_capacity_max = 2500
# 資料日志容量值越大，恢復資料越慢
# default = 100M
innodb_redo_log_capacity = 1G
# 資料重繪到磁盤的方式
# < 有些同學說用 O_DSYNC 方式，在寫入時，有很大提升，可官網說：
# < InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.
# < 也就是少部分系統可以穩定使用，或者已經過系統驗證確認，
# < 個人認為，默認值最可靠
# innodb_flush_method = fsync4.6 Open cache
[mysqld]
# default = 5000
open_files_limit = 10000
# 計算公式：MAX((open_files_limit-10-max_connections)/2,400)
# default = 4000
table_open_cache = 4495
# 超過16核的硬體，肯定要增加，以發揮出最大性能
# default = 16
table_open_cache_instances = 32五、寫入綜合測驗
測驗目的：
經過【四、SQL服務引數優化】的配置后，分別測驗空表狀態批量寫入200萬和500萬資料的耗時，
測驗場景：
一臺幾年前的破筆記本，創建的虛擬機4C8G，Docker + MySQL8.0.31，
桌面應用以36個執行緒寫入隨機資料，
批量寫入腳本：INSERT INTO TABLE ... VALUES (...),(...),(...) 的方式，INSERT 每次1000條，
表結構：聚集索引 + 兩列的非聚集索引 + 一組三列的組合索引；(參照 1.5.2)
+------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-------------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| usercode | varchar(32) | YES | MUL | NULL | |
| title | varchar(128) | YES | | NULL | |
| age | int | YES | MUL | NULL | |
| gender | char(1) | YES | | 男 | |
| phone | char(11) | YES | | NULL | |
| job | varchar(32) | YES | | NULL | |
| department | varchar(32) | YES | | NULL | |
| createtime | datetime | NO | PRI | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+測驗結果：
逐步追加MySQL服務引數配置+表磁區，最終有了成倍的性能提升；每次測驗后的日志記錄了優化的遞程序序；
如下圖：(日志不夠細，懂就行)
經過逐步優化：
??200萬資料寫入耗時從 9分4秒，提升到 5分50秒；(無表磁區)
??500萬資料寫入耗時從 41分33秒，提升到 6分50秒，(有表磁區)
鄙人拙見，有不妥望指出，萬分感謝，轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/542654.html
標籤：其他