MySQL結構介紹
MySQL邏輯分層
MyISAM和InnoDB
性能下降分析
- 查詢語句寫的不好
- 索引失效
- 關聯太多join（設計缺陷或不得已的需求）
- 伺服器調優及各個參數設定（緩沖、線程數等）
SQL執行順序
索引優化分析
SQL JOINS回顧
- select * from A inner join B on A.key=B.key;
MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制 - select X from A left join B on A.key=B.key;
MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制 - select * from A right join B on A.key=B.key;
MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制 - select * from A left join B on A.key=B.key where B.key is null;
MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制 - select * from A right join B on A.key=B.key where A.key is null;
MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制 - select * from A full outer join B on A.key=B.key;
MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制 - select * from A full outer join B on A.key=B.key where A.key is null or B.key is null;
MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制 MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制
mysql> select * from employee e inner join department d on e.did=d.did;
+----+------+-----+-----+-----+--------+
| id | name | age | did | did | dname |
+----+------+-----+-----+-----+--------+
| 1 | 張三 | 20 | 1 | 1 | 網絡部 |
| 2 | 李四 | 22 | 1 | 1 | 網絡部 |
| 3 | 王五 | 20 | 2 | 2 | 媒體部 |
| 5 | 錢七 | 20 | 1 | 1 | 網絡部 |
+----+------+-----+-----+-----+--------+
4 rows in set
mysql> select * from employee e left join department d on e.did=d.did;
+----+------+-----+-----+------+--------+
| id | name | age | did | did | dname |
+----+------+-----+-----+------+--------+
| 1 | 張三 | 20 | 1 | 1 | 網絡部 |
| 2 | 李四 | 22 | 1 | 1 | 網絡部 |
| 5 | 錢七 | 20 | 1 | 1 | 網絡部 |
| 3 | 王五 | 20 | 2 | 2 | 媒體部 |
| 4 | 趙六 | 20 | 4 | NULL | NULL |
+----+------+-----+-----+------+--------+
5 rows in set
mysql> select * from employee e right join department d on e.did=d.did;
+------+------+------+------+-----+--------+
| id | name | age | did | did | dname |
+------+------+------+------+-----+--------+
| 1 | 張三 | 20 | 1 | 1 | 網絡部 |
| 2 | 李四 | 22 | 1 | 1 | 網絡部 |
| 3 | 王五 | 20 | 2 | 2 | 媒體部 |
| 5 | 錢七 | 20 | 1 | 1 | 網絡部 |
| NULL | NULL | NULL | NULL | 3 | 研發部 |
+------+------+------+------+-----+--------+
5 rows in set
mysql> select * from employee e left join department d on e.did=d.did where d.did is null;
+----+------+-----+-----+------+-------+
| id | name | age | did | did | dname |
+----+------+-----+-----+------+-------+
| 4 | 趙六 | 20 | 4 | NULL | NULL |
+----+------+-----+-----+------+-------+
1 row in set
mysql> select * from employee e right join department d on e.did=d.did where e.did is null;
+------+------+------+------+-----+--------+
| id | name | age | did | did | dname |
+------+------+------+------+-----+--------+
| NULL | NULL | NULL | NULL | 3 | 研發部 |
+------+------+------+------+-----+--------+
1 row in set
mysql> select * from employee e left join department d on e.did=d.did where d.did is null
-> union
-> select * from employee e right join department d on e.did=d.did where e.did is null;
+------+------+------+------+------+--------+
| id | name | age | did | did | dname |
+------+------+------+------+------+--------+
| 4 | 趙六 | 20 | 4 | NULL | NULL |
| NULL | NULL | NULL | NULL | 3 | 研發部 |
+------+------+------+------+------+--------+
2 rows in set
mysql> select * from employee e left join department d on e.did=d.did
-> union
-> select * from employee e right join department d on e.did=d.did;
+------+------+------+------+------+--------+
| id | name | age | did | did | dname |
+------+------+------+------+------+--------+
| 1 | 張三 | 20 | 1 | 1 | 網絡部 |
| 2 | 李四 | 22 | 1 | 1 | 網絡部 |
| 5 | 錢七 | 20 | 1 | 1 | 網絡部 |
| 3 | 王五 | 20 | 2 | 2 | 媒體部 |
| 4 | 趙六 | 20 | 4 | NULL | NULL |
| NULL | NULL | NULL | NULL | 3 | 研發部 |
+------+------+------+------+------+--------+
6 rows in set
索引
是什麼
排好序的快速查找資料結構，往往以索引檔案的形式存儲在磁盤上，索引如果沒有特别說明，都是指B樹
幹什麼
排序和查找
優勢
- 類似圖書館建書目索引，提高資料檢索效率，降低資料庫的IO成本
- 通過索引列對資料進行排序，降低資料排序的成本，降低了CPU消耗
劣勢
- 實際上索引也是一張表，該表儲存了主鍵與索引字段，并指向實體表，也是要占用空間的
- MySQL儲存資料時，還要儲存一下索引檔案每次更新添加了索引列的字段，都會調整因為更新帶來的鍵值變化後的索引資訊
- 索引隻是提高效率的一個因素，如果MySQL有大資料量的表，需要花費時間建立更優秀的索引，或優化查詢
MySQL索引分類
- 單值索引：一個索引隻包含單個列，一個表可以有多個單列索引
- 唯一索引：索引列的值必須唯一，但允許有空值
- 複合索引：一個索引包含多個列
基本文法
# 建立
create [unique] index indexName on mytable(columnname(length));
alter table mytable add [unique|fulltext] index [indexName] on (columnname(length));
alter table mytable add primary key (columnname(length));
# 删除
drop index [indexName] on mytable;
# 檢視
show index from table_name\G
BTree索引檢索原理
一棵b+樹，淺藍色的塊稱為一個磁盤塊，每個磁盤塊包含幾個資料項（深藍色所示）和指針（黃色所示），如磁盤塊1包含資料項17和35，也包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示介于17和35之間的磁盤塊，P3表示大于35的磁盤塊；真實資料存在于葉子節點，即3、5、9、10、13、15、28、29、36、60、75、79、90、99，非葉子節點不存儲真實的資料，隻存儲指引搜尋方向的資料項，如17、35并不真實存在于資料表中。
何時建立索引
- 主鍵自動建立唯一索引
- 頻繁作為查詢條件的字段應作為索引
- 查詢中與其他表關聯的字段，外鍵關系建立索引
- 頻繁更新的字段不适合建立索引
- where條件用不到的字段不建立索引
- 單鍵/組合索引選擇？高并發下傾向建立組合索引
- 查詢中排序的字段，建立索引後将大大提高排序速度
- 查詢中統計或分組字段
何時不建索引
- 表記錄太少
- 經常增删改的表
- 資料重複且分布均勻的字段
性能分析
- MySQL Query Optimizer
- 常見瓶頸：CPU飽和、IO容量、伺服器硬體性能瓶頸（top、free、iostat和vmstat檢視系統性能狀态）
Explain
是什麼
使用explain關鍵字可以模拟優化器執行SQL查詢語句，就能知道MySQL是如何處理SQL語句的，進而分析查詢語句或表結構的性能瓶頸
能幹什麼
- 表的讀取順序
- 資料讀取操作的操作類型
- 哪些索引可以使用
- 哪些索引被實際使用
- 表之間的引用
- 每張表多少行被優化器查詢
用法
Explain + SQL語句
mysql> explain select * from department;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | department | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set
字段詳解
- id：查詢的序列号，包含一組數字，表示查詢中執行select字句或操作表的順序，相當于Java中的線程優先級。
- id相同，執行順序由上至下；
- id不同，如果是子查詢，id的序号會遞增，id值越大優先級越高；
- 序列存在相同也存在不同，則先按優先級執行，相同優先級的按順序由上至下。
- select_type：查詢類型。
- simple，簡單的select查詢，不包含子查詢或者union；
- primary，查詢中若包含任何複雜的字部份，最外層則标記為primary；
- subquery，在select或where清單中包含了的子查詢；
- derived，在from清單中包含的子查詢被标記為derived（衍生），MySQL會遞歸執行這些子查詢，把結果放在臨時表中；
- union，若第二個select出現在union之後，則被标記為union，若union包含在from子句的子查詢中，外層select将被标記為derived；
- union result，從union表擷取結果的select。
- table：這個資料是哪張表的。
- type：通路類型最好到最差依次是 system>const>eq_ref>ref>range>index>All
- system，表隻有一行記錄（等于系統表），這是const類型的特例，平時不會出現；
- const，表示通過索引一次就找到了，const用于比較primary key或unique索引。因為隻比較一行資料，是以很快，如将主鍵置于where清單中，MySQL就能将該查詢轉換為一個常量
- eq_ref，唯一性索引掃描，對于每個索引鍵，表中隻有一條記錄與之比對，常見于主鍵或唯一索引掃描
- range，非唯一性索引掃描，傳回比對某個單獨值的所有行，本質上也是一種索引通路，它傳回所有比對某個單獨值的行，然而，它可能會找到多個符合條件的行，是以它應該屬于查找和掃描的混合體
- range，隻檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引，一般就是在你where語句中出現了between、<、>、in的查詢，這種範圍索引掃描比全表掃描要好，因為它隻掃描部分索引，而不是全索引掃描
- index，Full Index Scan，index與All的差別為index類型隻周遊索引樹，雖然都是全表掃描，但是因為索引檔案比資料檔案小，是以通常比All快
- All，Full Table Scan，周遊全表找到比對的行
- passible_keys：顯示可能應用在這張表的索引，若存在多個索引則被列出，但實際使用中不一定使用
- key：實際使用到的索引，查詢中若使用了覆寫索引，則該索引僅出現在key清單中
- 覆寫索引：查詢的字段和索引建的字段一一符合（個數和順序），是以從索引上取資料就行了，不用全表掃描
- key_len：表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度，長度越短越好
- key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表内檢索出的
- ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用于查找索引列上的值
- rows：根據表統計資訊及索引選用情況，大緻估算出找到所需的記錄所需要讀取的行數
- Extra
- Using filesort：說明MySQL會對資料使用一個外部的索引排序，而不是按照表内的索引順序進行讀取，MySQL中無法利用索引完成的排序操作稱為 “檔案排序”
- Using temporary：使用了臨時表儲存中間結果，MySQL在對查詢結果排序時使用臨時表。常見于order by和group by
- Using index：表示相應的select操作中使用了覆寫索引，避免通路了表的資料行，效率不錯
- using where：表明索引被用來執行索引鍵值的查找，如果沒有同時出現using where，表明索引用來讀取資料而非執行查找動作
- using join buffer：使用了連結緩存
- impossible where：where字句的值等于false，不能用來擷取任何元組
- select tables optimized away：在沒有group by字句的情況下，基于索引優化min/max操作或者對于M有ISAM存儲引擎優化 count(*) 操作，不必等到執行階段在進行計算，查詢執行計劃生成的階段即完成優化
- distinct：優化distinct操作，在找到第一比對的元組後即停止找同樣值的動作
索引優化
單表
建表
create table if not exists article(
id int(10) unsigned not null primary key auto_increment,
author_id int(10) unsigned not null,
category_id int(10) unsigned not null,
views int(10) unsigned not null,
comments int(10) unsigned not null,
title varbinary(255) not null,
content text not null
);
insert into article values(1,1,1,1,1,'1','1'),(2,2,2,2,2,'2','2'),(3,1,1,3,3,'3','3');
select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set
查詢category_id為1且comments大于1的情況下，views最多的article_id。
select id from article where category_id=1 and comments>1 order by views desc limit 1;
+----+
| id |
+----+
| 3 |
+----+
explain select id from article where category_id=1 and comments>1 order by views desc limit 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set
第一次優化：
alert table article add index idx_article_ccv(category_id,comments,views);
create index idx_article_ccv on article(category_id,comments,views);
explain select id from article where category_id=1 and comments>1 order by views desc limit 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------+
| 1 | SIMPLE | article | NULL | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | 100 | Using where; Using index; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------------+
1 row in set
結論：
- type變成了range，但仍有using filesort問題
- 按照BTree原理，先排序category_id，遇到相同的category_id則排序comments，遇到相同的comments再排序views
- 當comments字段在聯合索引的中間位置且comments>1是一個範圍值，是以MySQL無法利用索引再對後面的views部分進行檢索，即range類型插叙字段後面的索引無效
第二次優化：
drop index idx_article_ccv on article;
create index idx_article_cv on article(category_id,views);
explain select id from article where category_id=1 and comments>1 order by views desc limit 1;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | 33.33 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set
結論：
- 由結果可以看到，type變為了ref，也沒有using filesort，結果理想
雙表
建表并插入資料
create table class(
id int(10) unsigned not null auto_increment,
card int(10) unsigned not null,
primary key(id)
);
create table book(
bookid int(10) unsigned not null auto_increment,
card int(10) unsigned not null,
primary key(bookid)
);
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set
建立在左連接配接的左表
create index X on class(card);
explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | index | NULL | X | 4 | NULL | 20 | 100 | Using index |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set
drop index X on class;
create index Y on book(card);
explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------------+------+---------------+------+---------+---------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+---------------------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100 | NULL |
| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | mysql_advanced.class.card | 1 | 100 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+---------------------------+------+----------+-------------+
2 rows in set
結論：對于左連接配接的左表，無論是否建索引，都需要全表掃描，但右表則不同；由此延伸：對于由連接配接，應在左表建立索引。
三表
在雙表的基礎上增加一個phone表
create table phone(
phoneid int(10) unsigned not null auto_increment,
card int(10) unsigned not null,
primary key(phoneid)
)engine=innodb;
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
explain select * from class inner join book on class.card=book.card inner join phone on book.card=phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set
優化：
create index Y on book(card);
create index Z on phone(card);
explain select * from class inner join book on class.card=book.card inner join phone on book.card=phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+---------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+---------------------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100 | NULL |
| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | mysql_advanced.class.card | 1 | 100 | Using index |
| 1 | SIMPLE | phone | NULL | ref | Z | Z | 4 | mysql_advanced.class.card | 1 | 100 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+---------------------------+------+----------+-------------+
3 rows in set
結論：後兩行的type都是ref且總rows優化很好，是以索引最好設定在需要經常查詢的字段中。
Join語句的優化
- 盡可能減少Join語句中的NestedLoop的循環總次數：“永遠用小結果集驅動大的結果集”
- 優先優化NestedLoop的内層循環
- 保證Join語句中被驅動表上Join條件字段已經被索引
- 當無法保證被驅動表的Join條件字段被索引且記憶體資源充足的前提下，不要太吝惜JoinBuffer的設定
口訣
全值比對我最愛，最左字首要遵守，
帶頭大哥不能死，中間兄弟不能斷，
索引列上少計算，範圍之後全失效，
Like百分寫最右，索引覆寫不屑星，
不等空值還有or，索引失效要少用，
VAR引号不可丢，SQL進階也不難。
索引失效
測試用例
create table staffs(
id int primary key auto_increment,
name varchar(20) not null default '' comment '姓名',
age int not null default 0 comment '年齡',
pos varchar(20) not null default '' comment '職位',
add_time timestamp not null default current_timestamp comment '入職時間'
)charset utf8 comment '員工記錄表';
insert into staffs(name,age,pos,add_time)values('z3',22,'manager',NOW());
insert into staffs(name,age,pos,add_time)values('July',23,'dev',NOW());
insert into staffs(name,age,pos,add_time)values('2000',23,'dev',NOW());
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
CREATE TABLE `employee` (
`id` int(4) NOT NULL,
`name` varchar(36) NOT NULL DEFAULT '0',
`age` int(2) NOT NULL DEFAULT '0',
`did` int(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into employee(id,name,age,did)values(6,'1aa1',20,2),(7,'2aa2',20,2),(8,'3aa3',20,2),(9,'4aa4',20,2);
create index idx_employee_nameAge on employee(name,age,did);
常見情況
- 最佳左字首法則（如果索引了多列，查詢從索引的最左前列開始并且不跳過索引中的列，即第一個索引列必須出現）
explain select * from staffs where name='July'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62 | const | 1 | 100 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set explain select * from staffs where name='July' and age=25; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 66 | const,const | 1 | 100 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ 1 row in set explain select * from staffs where name='July' and age=25 and pos='dev'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 128 | const,const,const | 1 | 100 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set explain select * from staffs where age=25 and pos='dev'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set - 跳過了索引中間列，導緻隻有部分索引列生效
explain select * from staffs where name='July' and pos='dev'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62 | const | 1 | 33.33 | Using index condition | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ 1 row in set - 在索引列上做任何操作（計算、函數、類型轉換），會導緻索引失效而轉向全表掃描
explain select * from staffs where name='July'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62 | const | 1 | 100 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set explain select * from staffs where left(name,4)='July'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set - 存儲引擎不能使用索引中範圍條件右邊的列，即範圍列後面的索引全失效
explain select * from staffs where name='July' and age=25 and pos='dev'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 128 | const,const,const | 1 | 100 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set explain select * from staffs where name='July' and age>25 and pos='dev'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 66 | NULL | 1 | 33.33 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set - 盡量使用覆寫索引（隻通路索引的查詢（索引列和查詢列一緻）），減少select *
explain select * from staffs where name='July' and age=25 and pos='dev'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 128 | const,const,const | 1 | 100 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set explain select name,age,pos from staffs where name='July' and age=25 and pos='dev'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 128 | const,const,const | 1 | 100 | Using index | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ 1 row in set - mysql在使用 != 或 <> 的時候無法使用索引會導緻全表掃描
explain select * from staffs where name='July'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62 | const | 1 | 100 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set explain select * from staffs where name!='July'; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set explain select * from staffs where name<>'July'; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set - is null, is not null 也無法使用索引
explain select * from staffs where name is null; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set explain select * from staffs where name is not null; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set - like 以通配符開頭（’%abc…’) 會讓索引失效導緻全表掃描
explain select * from staffs where name like 'Ju%'; +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62 | NULL | 1 | 100 | Using index condition | +----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set explain select * from staffs where name like '%ly'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set - 非不得已like查詢左右都要加 % ，使用覆寫索引
show index from employee; +----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employee | 0 | PRIMARY | 1 | id | A | 9 | NULL | NULL | | BTREE | | | | employee | 1 | idx_employee_nameAge | 1 | name | A | 9 | NULL | NULL | | BTREE | | | | employee | 1 | idx_employee_nameAge | 2 | age | A | 9 | NULL | NULL | | BTREE | | | +----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set explain select * from employee where name like'%aa%'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employee | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set explain select id,name,age from employee where name like'%aa%'; +----+-------------+----------+------------+-------+---------------+----------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+----------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | employee | NULL | index | NULL | idx_employee_nameAge | 114 | NULL | 9 | 11.11 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+----------------------+---------+------+------+----------+--------------------------+ 1 row in set explain select id,name from employee where name like'%aa%'; +----+-------------+----------+------------+-------+---------------+----------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+----------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | employee | NULL | index | NULL | idx_employee_nameAge | 114 | NULL | 9 | 11.11 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+----------------------+---------+------+------+----------+--------------------------+ 1 row in set - 字元串不加單引号導緻索引失效
select * from staffs where name=2000; +----+------+-----+-----+---------------------+ | id | name | age | pos | add_time | +----+------+-----+-----+---------------------+ | 3 | 2000 | 23 | dev | 2021-04-21 12:04:03 | +----+------+-----+-----+---------------------+ 1 row in set select * from staffs where name='2000'; +----+------+-----+-----+---------------------+ | id | name | age | pos | add_time | +----+------+-----+-----+---------------------+ | 3 | 2000 | 23 | dev | 2021-04-21 12:04:03 | +----+------+-----+-----+---------------------+ 1 row in set explain select * from staffs where name=2000; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set explain select * from staffs where name='2000'; +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62 | const | 1 | 100 | NULL | +----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set - 用 or 會導緻索引失效
mysql> explain select * from staffs where name='July' or name='z3'; +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where | +----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set
面試題
建表
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5)values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5)values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5)values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5)values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5)values('e1','e2','e3','e4','e5');
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
情況1
explain select * from test03 where c4='a4' and c3='a3' and c2='a2' and c1='a1';
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 44 | const,const,const,const | 1 | 100 | NULL |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
1 row in set
分析：MySQL的優化器幫我們對SQL語句進行了重排
情況2
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | range | idx_test03_c1234 | idx_test03_c1234 | 33 | NULL | 1 | 20 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set
分析：c1和c2用到了查找，c3隻用到了排序，c4在範圍之後，是以沒有用到索引
情況3
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | range | idx_test03_c1234 | idx_test03_c1234 | 44 | NULL | 1 | 100 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set
分析：由于MySQL優化器的原因，c1、c2、c3和c4都用到了索引，c4索引用于排序
情況4
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 22 | const,const | 1 | 20 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
1 row in set
分析：c1、c2都用到了索引，但是和c4有斷層，是以c4沒用到，但c3也算是用到了排序，隻是沒顯示出來
情況5
explain select * from test03 where c1='a1' and c2='a2' order by c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 22 | const,const | 1 | 100 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
1 row in set
分析：通過與情況4對比，結果一樣，因為c3都排序了，是以用不上c4，這和範圍索引情況不一樣
情況6
explain select * from test03 where c1='a1' and c2='a2' order by c4;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 22 | const,const | 1 | 100 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
1 row in set
分析：出現問題（using filesort），跳過c3直接從c4開始排序了，是以出現了内排序（性能下降）
情況7
explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 11 | const | 1 | 20 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
1 row in set
分析：隻用到了c1的索引查找，之後的c2、c3都排序去了，無filesort
情況8
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 11 | const | 1 | 20 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set
分析：隻用了c1的索引，但沒有按照我們的索引順序（1234），32颠倒，出現了filesort
情況9
explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 22 | const,const | 1 | 100 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
1 row in set
分析：查找和排序都按照順序來
情況10
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 22 | const,const | 1 | 20 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
1 row in set
分析：用了c1和c2兩個索引，c2、c3用于排序，無filesort
情況11
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 22 | const,const | 1 | 20 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
1 row in set
分析：沒有出現filesort，因為排序字段c2已經是一個常量了，排不排序都已經沒關系了
情況12
explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 11 | const | 1 | 20 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set
分析：隻用到了一個c1的索引
情況13
explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
| 1 | SIMPLE | test03 | NULL | ref | idx_test03_c1234 | idx_test03_c1234 | 11 | const | 1 | 20 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
1 row in set
分析：隻用到了一個索引且出現了filesort和臨時表
一般性建議：
- 對于單鍵索引，盡量選擇針對目前查詢過濾性更好的索引
- 在選擇組合索引的時候，目前查詢中過濾性最好的字段在索引字段順序中，位置越靠前越好
- 在選擇組合索引的時候，盡量選擇可以能夠包含目前查詢中的where字句中更多字段的索引
- 盡可能通過分析統計資訊和調整查詢的寫法來達到選擇何時索引的目的
小總結：
查詢截取分析
查詢優化
永遠小表驅動大表
select * from A where id in(select id from B);
相當于：
for select id from B
for select * from A where A.id=B.id
結論：當B表資料集小于A表資料集的時候，用in優于exists
select * from A where exists (select 1 from B where B.id=A.id);
等價于
for select * from A
for select * from B where B.id=A.id（這條語句相當于一個判斷條件true / false，主查詢會根據這個結果決定是否将資料結果進行保留）
結論：當A表的資料集小于B表的資料集時，用exists優于in
注意：A表與B表的ID字段應建立索引
order by關鍵字優化
- order by子句，盡量是喲個index方式排序，避免使用filesort方式排序
- 盡可能在索引上完成排序操作，遵照索引建的最佳左字首
- 如果不在索引列上，filesort有兩種算法
- 雙路排序：MySQL4.1之前使用的是雙路排序，進行兩次掃描磁盤，獲得資料；讀取行指針和order by列，對他們進行排序，然後掃描已經排好序的清單，按照清單中的值重新從清單中的值重新從清單中讀取對應的資料輸出；從磁盤取排序字段，在buffer進行排序，再從磁盤取其他字段
- 單路排序：從磁盤讀取查詢所需要的所有列，按照order by列再buffer對他們進行排序，然後掃描排序後的清單進行輸出，他的效率更快一些，避免了第二次讀取資料。并且把随機IO變成了順序IO，但是他會使用更多的空間，因為他把每一行都儲存在記憶體中了
- 結論：由于單路是後出的，總體而言好過雙路；但是如果每次取出的資料總大小超過了sort_buffer的容量，導緻每次隻能取sort_buffer容量大小的資料，進行排序（建立tmp檔案，多路合并），排完再取sort_buffer容量大小的資料，再排…進而多次IO
- 優化政策
- 增大sort_buffer_size參數的設定
- 增大max_length_for_sort_data參數的設定
- 使用order by時盡量不用 select *
- 小結
MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制
group by關鍵字優化
- group by實質上是先排序後分組，遵照索引建的最佳左字首
- 當無法使用索引列，增大max_length_for_sort_data參數的設定 + 增大sort_buffer_size參數的設定
- where高于having，能寫再where限定的條件不要用having限定
慢查詢日志
是什麼
MySQL慢查詢日志是MySQL提供的一種日志記錄，用來記錄MySQL中相應時間超過門檻值的語句，具體指運作時間超過long_query_time值（預設10秒）的SQL，會被記錄到慢查詢日志中；如果不需要性能調優不建議開啟，有一定的性能影響
開啟
# 檢視是否開啟
show variables like '%slow_query_log%';
+---------------------+---------------------------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | D:\app\mysql-5.7.32\data\DESKTOP-019FVI6-slow.log |
+---------------------+---------------------------------------------------+
2 rows in set
# 開啟
set global slow_query_log=1; (重新開機MySQL後失效）
+---------------------+---------------------------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | D:\app\mysql-5.7.32\data\DESKTOP-019FVI6-slow.log |
+---------------------+---------------------------------------------------+
2 rows in set
# 修改配置檔案後可永久生效
# 檢視并設定門檻值
show global variables like '%long_query_time%';
set global long_query_time=3;
後續檢視及分析
# 模拟
select sleep(4);
+----------+
| sleep(4) |
+----------+
| 0 |
+----------+
1 row in set
# 檢視慢SQL條數
show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set
檢視具體慢SQL
日志分析工具mysqldumpslow
批量資料腳本
- 建表
create table dept(
id int unsigned primary key auto_increment,
deptno mediumint unsigned not null default 0,
dname varchar(20) not null default "",
loc varchar(13) not null default ""
)engine=innodb default charset=gbk;
create table emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
ename varchar(20) not null default "",
job varchar(9) not null default "",
mgr mediumint unsigned not null default 0,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno mediumint unsigned not null default 0
)engine=innodb default charset=gbk;
- 設定參數log_bin_trust_function_creators
建立函數如果報錯：This is has none of DETERMINISTIC…
由于開啟過慢查詢日志，因為我們開啟了bin-log，我們就必須為我們的function指定一個參數
show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set
set global log_bin_trust_function_creators=1;
注意：這是臨時的設定，mysqld重新開機後，參數會消失，永久設定可以在my.ini[mysqld]加上log_bin_trust_function_creators=1
- 建立函數，保證每條資料都不同
随機字元串
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
随機部門編号
delimiter $$
create function rand_num( ) returns int(5)
begin
declare i int default 0;
set i = floor(100+rand()*10);
return i;
end $$
- 建立存儲過程
建立往emp表中插入資料的存儲過程
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit=0;
repeat
set i=i+1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$
建立往dept表中插入資料的存儲過程
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit=0;
repeat
set i=i+1;
insert into dept(deptno,dname,loc)values((start+i),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$
- 調用存儲過程
dept
delimiter ;
call insert_dept(100,10);
emp
call insert_emp(100001,500000);
Show Profile
是什麼
MySQL提供可以用來分析目前會話中語句hi行的資源消耗情況，可用于SQL的調優測量，預設情況下，參數處于關閉狀态，并儲存最近15次的運作結果
官網
http://dev.mysql.com/doc/refman/5.7/en/show-profile.html
分析步驟
- 目前MySQL版本是否支援
show variables like 'profiling';
- 開啟功能，預設是關閉
set profiling=on;
- 運作SQL
set @@GLOBAL.sql_mode='';
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;
- 檢視結果，show profiles;
show profiles;
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.0003065 | select * from emp group by id%10 limit 150000 |
| 2 | 0.00011375 | set @@GLOBAL.sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| 3 | 8.025E-5 | set @@GLOBAL.sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| 4 | 0.0001055 | set @@GLOBAL.sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| 5 | 0.00019925 | select * from emp group by id%10 limit 150000 |
| 6 | 0.0002245 | set @@GLOBAL.sql_mode='' |
| 7 | 0.00011975 | set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' |
| 8 | 0.53956 | select * from emp group by id%10 limit 150000 |
| 9 | 0.60186025 | select * from emp group by id%20 order by 5 |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set
- 診斷SQL，show profile cpu,block io for query 上一步前面的問題SQL數字号碼;
參數說明：
- ALL – 顯示所有的開銷資訊
- BLOCK IO – 顯示塊IO相關開銷
- CONTEXT SWITCHES --上下文切換相關開銷
- CPU – 顯示CPU相關開銷資訊
- IPC – 顯示發送和接收相關開銷資訊
- MEMORY – 顯示記憶體相關開銷資訊
- PAGE FAULTS – 顯示頁面錯誤相關開銷資訊
- SOURCE – 顯示和Source_function，Source_file，Source_line相關的開銷資訊
- SWAPS – 顯示交換次數相關開銷的資訊
show profile cpu,block io for query 9;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000126 | 0 | 0 | NULL | NULL |
| checking permissions | 9E-6 | 0 | 0 | NULL | NULL |
| Opening tables | 2.7E-5 | 0 | 0 | NULL | NULL |
| init | 3E-5 | 0 | 0 | NULL | NULL |
| System lock | 9E-6 | 0 | 0 | NULL | NULL |
| optimizing | 4E-6 | 0 | 0 | NULL | NULL |
| statistics | 2.2E-5 | 0 | 0 | NULL | NULL |
| preparing | 9E-6 | 0 | 0 | NULL | NULL |
| Creating tmp table | 3.9E-5 | 0 | 0 | NULL | NULL |
| Sorting result | 3E-6 | 0 | 0 | NULL | NULL |
| executing | 1E-6 | 0 | 0 | NULL | NULL |
| Sending data | 0.601433 | 0.5625 | 0 | NULL | NULL |
| Creating sort index | 5.6E-5 | 0 | 0 | NULL | NULL |
| end | 3E-6 | 0 | 0 | NULL | NULL |
| query end | 7E-6 | 0 | 0 | NULL | NULL |
| removing tmp table | 6E-6 | 0 | 0 | NULL | NULL |
| query end | 2E-6 | 0 | 0 | NULL | NULL |
| closing tables | 6E-6 | 0 | 0 | NULL | NULL |
| freeing items | 5.5E-5 | 0 | 0 | NULL | NULL |
| cleaning up | 1.5E-5 | 0 | 0 | NULL | NULL |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set
- 日常開發需要注意
- converting HEAP to MyISAM – 查詢結果太大，記憶體都不夠用了往磁盤上搬了。
- Creating tmp table建立臨時表 – 拷貝資料到臨時表用完再删除
- Copying to tmp table on disk – 把記憶體中臨時表複制到磁盤，危險!!!
- locked
全局查詢日志
注意：永遠不要在生産環境中啟用這個功能！！！
- 啟用配置
set global general_log=1;
set global log_output='table';
以後的記錄都會記錄在表 mysql.general_log 中
select * from mysql.general_log;
MySQL鎖機制
概述
定義
鎖是計算機協調多個程序或線程并發通路某一資源的機制。在資料庫中，除傳統的計算資源〈如CPU、RAM、I/O等)的争用以外，資料也是一種供許多使用者共享的資源。如何保證資料并發通路的一緻性、有效性是所有資料庫必須解決的一個問題，鎖沖突也是影響資料庫并發通路性能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。
分類
- 對資料操作的類型分：讀鎖（共享鎖，多個讀操作可以同時進行）、寫鎖（排他鎖，寫操作完成前阻斷其他讀寫操作）
- 對資料操作的粒度分：表鎖、行鎖
三鎖
表鎖（偏讀）
特點：偏向MyISAM引擎，開銷小，加鎖快，無死鎖，粒度大，發生鎖沖突的機率最高，并發度最低
建表
create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
select * from mylock;
手動加解鎖并檢視
# 手動增加表鎖
lock table 表名字 read | write,表名字2 read | write,....
# 檢視表上過的鎖
show open tables;
lock table mylock read,book write;
show open tables;
+--------------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------------+------------------------------------------------------+--------+-------------+
| ...... | ...... | ... | ... |
| mysql_advanced | mylock | 1 | 0 |
| ...... | ...... | ... | ... |
| mysql_advanced | book | 1 | 0 |
| ...... | ...... | ... | ... |
+--------------------------+------------------------------------------------------+--------+-------------+
113 rows in set
unlock tables;
show open tables;
+--------------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------------+------------------------------------------------------+--------+-------------+
| ...... | ...... | ... | ... |
| mysql_advanced | mylock | 0 | 0 |
| ...... | ...... | ... | ... |
| mysql_advanced | book | 0 | 0 |
| ...... | ...... | ... | ... |
+--------------------------+------------------------------------------------------+--------+-------------+
113 rows in set
讀鎖測試
session-1
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set
mysql> update mylock set name='a1' where id=1;
1099 - Table 'mylock' was locked with a READ lock and can't be updated
mysql> select * from book;
1100 - Table 'book' was not locked with LOCK TABLES
mysql>
session-2
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 2 | 1 |
| 15 | 1 |
| 4 | 3 |
| 8 | 4 |
| 3 | 6 |
| 9 | 6 |
| 16 | 6 |
| 1 | 7 |
| 6 | 7 |
| 13 | 7 |
| 17 | 7 |
| 20 | 9 |
| 14 | 12 |
| 11 | 14 |
| 12 | 14 |
| 18 | 16 |
| 7 | 17 |
| 10 | 18 |
| 19 | 19 |
| 5 | 20 |
+--------+------+
20 rows in set
結論：如果session-1對一個表加了讀鎖，那麼session-1就不能讀寫該表了，但是session-2對所有表的讀不影響。
session-2
update mylock set name='a3' where id=1;
session-1
unlock tables;
Query OK, 0 rows affected
session-2
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
結論：如果session-2寫了加讀鎖的表，則被會阻塞，隻有讀鎖表被解鎖以後，session-2的寫操作還能繼續進行。
寫鎖測試
session-1
lock table mylock write;
Query OK, 0 rows affected
select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a3 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set
update mylock set name='a4' where id=1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
select * from book;
1100 - Table 'book' was not locked with LOCK TABLES
session-2
select * from mylock;
session-1
unlock tables;
Query OK, 0 rows affected
session-2
+----+------+
| id | name |
+----+------+
| 1 | a4 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set
結論：對于加了寫鎖的表，session-1可以對其進行修改，但不能操作其他的表，對于其他session，既不能讀也不能寫該表。
表鎖分析：
show status like 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 193 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 4 |
| Table_open_cache_misses | 2 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
5 rows in set
# Table_locks_immediate：産生表級鎖定的次數，表示可以立即獲得鎖的查詢次數
# Table_locks_waited：出現表級鎖定争用而發生等待的次數（不能立即擷取鎖的次數，每等待一次鎖值加1），此值高則說明存在着較嚴重的表級鎖争用情況
# 此外，MyISAM的讀寫鎖排程是寫優先，這也是MyISAM不适合做寫為主表的引擎，因為寫鎖後，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，進而造成永遠阻塞
行鎖（偏寫）
特點：偏向InnoDB存儲引擎，開銷大，加鎖慢，會出現死鎖，鎖定粒度小，發生沖突的機率低，并發度也最高，行級鎖也是InnoDB和MyISAM存儲引擎的不同。
事務的隔離級别
建表
create table test_innodb_lock(
a int(11),
b varchar(16)
)engine=innodb;
insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');
create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);
select * from test_innodb_lock;
session-1
set autocommit=0;
Query OK, 0 rows affected
update test_innodb_lock set b='4001' where a=4;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_innodb_lock;
+---+------+
| a | b |
+---+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4001 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+---+------+
9 rows in set
session-2
set autocommit=0;
Query OK, 0 rows affected
select * from test_innodb_lock where a=4;
+---+------+
| a | b |
+---+------+
| 4 | 4003 |
+---+------+
1 row in set
session-1
commit;
Query OK, 0 rows affected
session-2
commit;
Query OK, 0 rows affected
mysql> select * from test_innodb_lock where a=4;
+---+------+
| a | b |
+---+------+
| 4 | 4001 |
+---+------+
1 row in set
結論：不可重複讀，未送出時隻有session-1才能讀到更改，兩個session都送出後session-2才能讀取到變化的資料
session-1
update test_innodb_lock set b='4002' where a=4;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_innodb_lock where a=4;
+---+------+
| a | b |
+---+------+
| 4 | 4002 |
+---+------+
1 row in set
session-2
update test_innodb_lock set b='4003' where a=4;
# 阻塞
session-1
commit;
Query OK, 0 rows affected
session-2
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
session-1
select * from test_innodb_lock where a=4;
+---+------+
| a | b |
+---+------+
| 4 | 4002 |
+---+------+
1 row in set
session-2
select * from test_innodb_lock where a=4;
+---+------+
| a | b |
+---+------+
| 4 | 4003 |
+---+------+
1 row in set
mysql> commit;
Query OK, 0 rows affected
session-1
commit;
Query OK, 0 rows affected
select * from test_innodb_lock where a=4;
+---+------+
| a | b |
+---+------+
| 4 | 4003 |
+---+------+
1 row in set
結論：接觸阻塞後，後面的更新才能正常
sesseion-1
update test_innodb_lock set b='4000' where a=4;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
commit;
Query OK, 0 rows affected
session-2
update test_innodb_lock set b='9001' where a=1;
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
commit;
Query OK, 0 rows affected
select * from test_innodb_lock;
+---+------+
| a | b |
+---+------+
| 1 | 9001 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | 9001 |
+---+------+
9 rows in set
session-1
select * from test_innodb_lock;
+---+------+
| a | b |
+---+------+
| 1 | 9001 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | 9001 |
+---+------+
9 rows in set
結論：不同行的資料不互相影響
索引失效導緻行鎖變表鎖
session-1
# varchar字段不使用單引号導緻索引失效
update test_innodb_lock set a=40 where b=4000;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
select * from test_innodb_lock;
+----+------+
| a | b |
+----+------+
| 1 | 9001 |
| 3 | 3 |
| 40 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | 9001 |
+----+------+
9 rows in set
session-2
update test_innodb_lock set b='9001' where a=9;
# 阻塞
session-1
commit;
session-2
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
結論：當session-1的更新操作導緻索引失效時，session-2即使更新不同的行，也會被阻塞，session-1送出以後session-2恢複正常，即索引失效導緻了行鎖變表鎖
間隙鎖
session-1
update test_innodb_lock set b='1111' where a>1 and a<6;
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
session-2
insert into test_innodb_lock(a,b)values(2,'2000');
# 阻塞
session-1
commit;
Query OK, 0 rows affected
session-2
Query OK, 1 row affected
結論：當我們使用範圍條件而不是相等條件檢索資料，并請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖，對于鍵值在索引範圍内但不存在的記錄，叫做 “間隙（GAP）”，InnoDB也會對這個 “間隙” 加鎖，這種鎖機制就是 “間隙鎖（Next-Key鎖）”。
鎖定一行
session-1
begin;
Query OK, 0 rows affected
select * from test_innodb_lock where a=9 for update;
+---+------+
| a | b |
+---+------+
| 9 | 9001 |
+---+------+
1 row in set
session-2
update test_innodb_lock set b='200000' where a=9;
# 阻塞
session-1
commit;
session-2
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
結論：select xxx for update 鎖定某一行以後，其他的操作會被阻塞，知道鎖定行的會話送出commit;
行鎖分析
show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 116128 |
| Innodb_row_lock_time_avg | 19354 |
| Innodb_row_lock_time_max | 50436 |
| Innodb_row_lock_waits | 6 |
+-------------------------------+--------+
5 rows in set
- Innodb_row_lock_current_waits:目前正在等待鎖定的數量;
- Innodb_row_lock_time:從系統啟動到現在鎖定總時間長度;
- Innodb_row_lock_time_avg:每次等待所花平均時間;
- Innodb_row_lock_time_max:從系統啟動到現在等待最常的一次所花的時間;
- Innodb_row_lock_waits:系統啟動後到現在總共等待的次數;
優化建議
- 盡可能讓所有資料檢索都通過索引來完成，避免無索引行鎖更新為表鎖。
- 合理設計索引，盡量縮小鎖的範圍
- 盡可能較少檢索條件，避免間隙鎖
- 盡量控制事務大小，減少鎖定資源量和時間長度
- 盡可能低級别事務隔離
頁鎖
開銷和加鎖時間介于表鎖和行鎖之間，會出現死鎖，鎖定粒度介于表鎖和行鎖之間，并發度一般。
主從複制
基本原理
- slave會從master讀取binlog來進行資料同步
- 步驟
- master将改變記錄到二進制日志(binary log)。這些記錄過程叫做二進制日志事件，binary log events
- slave将master的binary log events拷貝到它的中繼日志(relay log)
- slave重做中繼日志中的事件，将改變應用到自己的資料庫中。MySQL複制是異步的且串行化的
MySQL調優MySQL結構介紹索引優化分析查詢截取分析MySQL鎖機制主從複制
基本準則
- 每個slave隻有一個master
- 每個slave隻能有一個唯一的伺服器ID
- 每個master可以有多個slave
複制的最大問題
延時
一主一從常見配置
- MySQL版本一緻
- 主從都配置在[mysqld]節點下
- 主機修改my.ini配置檔案
- 主伺服器唯一ID（server-id=1）
- 啟用二進制檔案（log-bin=自己本地的路徑/mysqlbin）
- 啟用錯誤日志（log-err=自己本地的路徑/mysqlerr）
- 根目錄（basedir=“自己本地路徑”）
- 臨時目錄（tmpdir=“自己本地路徑”）
- 資料目錄（datadir=“自己本地路徑/Data/”）
- read-only=0（主機讀寫都可以）
- 設定不要複制的資料庫（binlog-ignore-db=mysql）
- 設定需要複制的資料庫（binlog-do-db=需要複制的資料庫名字）
- 從機修改my.cnf配置檔案
- 從伺服器唯一ID（server-id=X）
- 啟用二進制日志
- 主機 + 從機重新開機MySQL
- 主機從機都關閉防火牆
- 在Windows主機上建立賬戶并授權slave
- grant replication slave on *.* to ‘zhangsan’@‘從機資料庫IP’ identified by ‘123456’;
- flush privileges;
- 檢視master狀态：show master status; 記錄下File和Position的值
- 在Linux從機上配置需要複制的主機
- change master to master_host=‘主機IP’,master_user=‘zhangsan’,master_password=‘123456’,master_log_file=‘File名字’,master_log_pos=Position數字;
- 啟動從伺服器複制功能：start slave;
- show slave status\G：如果 Slave_IO_Running 和 Slave_SQL_Running 都為 Yes，則說明主從配置成功
- 主機建立庫、建立表、insert記錄，從機複制
- 停止主從複制功能：stop slave;