myisam和innodb
| 對比 | myisam | innodb |
|---|---|---|
| 主外鍵 | 不支持 | 支持 |
| 事務(wù) | 不支持 | 支持 |
| 行表鎖 | 表鎖，操作時即使操作一條記錄也會鎖住一整張表，不適合高并發(fā)的操作 | 行鎖，操作時只鎖住某一行，不會影響到其他行，適合高并發(fā) |
| 緩存 | 只緩存索引，不緩存其他數(shù)據(jù) | 緩存索引和真實數(shù)據(jù)，對內(nèi)存要求較高，而且內(nèi)存大小對性能有影響 |
| 表空間 | 小 | 大 |
| 關(guān)注點 | 性能 | 事務(wù) |
| 默認(rèn)安裝 | y | y |
性能下降sql慢的原因：
- 查詢語句寫的差
- 索引失效
- 關(guān)聯(lián)查詢太多join （設(shè)計缺陷或不得已的需求）
- 服務(wù)器調(diào)優(yōu)及各個參數(shù)設(shè)置(緩沖，線程參數(shù))
mysql執(zhí)行順序
- 手寫
- 機讀先從from開始
sqljoin
a表
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> select * from tbl_dept;+----+----------+--------+| id | deptname | locadd | +----+----------+--------+| 1 | rd | 11 || 2 | hr | 12 || 3 | mk | 13 || 4 | mis | 14 || 5 | fd | 15 |+----+----------+--------+5 rows in set (0.00 sec) |
b表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
+----+------+--------+ | id | name | deptid | +----+------+--------+ | 1 | z3 | 1 | | 2 | z4 | 1 | | 3 | z5 | 1 | | 4 | w5 | 2 | | 5 | w6 | 2 | | 6 | s7 | 3 | | 7 | s8 | 4 | | 8 | s9 | 51 | +----+------+--------+8 rows in set (0.00 sec) |
mysql不支持全連接
使用以下方式可以實現(xiàn)全連接
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> select * from tbl_dept a right join tbl_emp b on a.id=b.deptid -> union -> select * from tbl_dept a left join tbl_emp b on a.id=b.deptid;+------+----------+--------+------+------+--------+| id | deptname | locadd | id | name | deptid |+------+----------+--------+------+------+--------+| 1 | rd | 11 | 1 | z3 | 1 || 1 | rd | 11 | 2 | z4 | 1 || 1 | rd | 11 | 3 | z5 | 1 || 2 | hr | 12 | 4 | w5 | 2 || 2 | hr | 12 | 5 | w6 | 2 || 3 | mk | 13 | 6 | s7 | 3 || 4 | mis | 14 | 7 | s8 | 4 || null | null | null | 8 | s9 | 51 || 5 | fd | 15 | null | null | null |+------+----------+--------+------+------+--------+9 rows in set (0.00 sec) |
a的獨有和b的獨有
mysql>select*fromtbl_dept aleftjointbl_emp bona.id=b.deptidwhereb.idisnull->union->select*fromtbl_dept arightjointbl_emp bona.id=b.deptidwherea.idisnull;+------+----------+--------+------+------+--------+| id | deptname | locadd | id |name| deptid |+------+----------+--------+------+------+--------+| 5 | fd | 15 |null|null|null||null|null|null| 8 | s9 | 51 |+------+----------+--------+------+------+--------+2rowsinset(0.01 sec)
索引
索引的定義：
索引是幫助sql高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，索引的本質(zhì)：數(shù)據(jù)結(jié)構(gòu)
可以簡單的理解為：排好序的快速查找數(shù)據(jù)結(jié)構(gòu)
在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式（引用）指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引，下圖就是一種示例：
一般來說索引也很大，因此索引往往以索引文件的方式存儲在磁盤上
我們平常所說的索引，如果沒有特別指明，一般都是指b樹（多路搜索樹，不一定是二叉的）結(jié)構(gòu)組織的索引，
其中聚集索引，次要索引，復(fù)合索引，前綴索引，唯一索引默認(rèn)都是使用b+樹索引，統(tǒng)稱索引，當(dāng)然除了b+樹這種類型的索引之外，還有哈希索引。
索引的優(yōu)劣
1.優(yōu)勢
類似大學(xué)圖書館圖書編號建索引，提高了數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的io成本
通過索引對數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了cpu的消耗
2.劣勢
實際上索引也是一張表，該表保存了主鍵與存在索引的字段，并指向?qū)嶓w表的記錄，所以索引列也是占用空間的
雖然索引大大提高了查詢速度，但是會降低更新表的速度，比如 update，insert，delete操作，因為更新表時，mysql不僅要數(shù)據(jù)也要保存索引文件每次更新添加了索引的字段，都會調(diào)整因為更新所帶來的鍵值變化后的索引信息
索引只是提高效率的一個因素，在一個大數(shù)據(jù)量的表上，需要建立最為優(yōu)秀的索引或者寫優(yōu)秀的查詢語句，而不是加了索引就能提高效率
索引分類
- 單值索引
- 唯一索引
- 復(fù)合索引
- 基本語法:
創(chuàng)建
|
1
|
create [unique] index indexname on mytable(cloumnname(length)); |
|
1
|
alter mytable add [unique] index [indexname] on (columnname(length)); |
刪除
|
1
|
drop index [indexname] on mytable |
查看
|
1
|
show index from table_name\g |
有四種方式來添加數(shù)據(jù)表的索引
mysql索引結(jié)構(gòu)
- btree索引
- hash索引
- full-text全文索引
- r-tree
那些情況建索引
- 主鍵自動建立唯一索引
- 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
- 查詢中與其他表相關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
- 頻繁更新的字段不適合創(chuàng)建索引，因為每次更新不單單更新了記錄還更新了索引
- where條件里用不到的字段不要創(chuàng)建索引
- 單鍵/組合索引的選擇問題 who？（高并發(fā)下建議組合索引）
- 查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
- 查詢中統(tǒng)計或分組字段
哪些情況不要建索引
- 表記錄少
- 經(jīng)常操作dml語句的表
- 數(shù)據(jù)重復(fù)且平均分布的表字段，因此只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引，注意，如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實際效果
性能分析
explian重點
能干什么
- 表的讀取順序
- 數(shù)據(jù)讀取操作的操作類型
- 哪些索引可以被使用
- 哪些索引被實際使用
- 表之間的引用
- 每張表有多少行被優(yōu)化器查詢
id三種情況
- id 相同，執(zhí)行順序由上至下
- id不同，如果是子查詢，id序號遞增，id越大優(yōu)先級越高
- id相同不同 ，同時存在
select_type
- simple 簡單查詢
- primary 主查詢 （最外層的查詢）
- subquery 子查詢
- deriued 某個查詢的子查詢的臨時表
- union 聯(lián)合查詢
- union result 聯(lián)合查詢結(jié)果
type：：
type顯示的是訪問類型排列，是較為重要的一個指標(biāo)
從最好到最差依次是：
system > const > eq_ref> ref > range > index > all;
一般來說，得保證查詢至少達(dá)到range級別，最好ref
----------------------------------------------type類型-------------------------------------------------------
- system:表只有一行記錄（等于系統(tǒng)表） 這是const類型的特列 一般不會出現(xiàn)，可忽略不計
- const：表示通過索引一次就查詢到了，const用來比較primary key或者unique索引。因為只匹配一行數(shù)據(jù)，所以很快，如將主鍵置于where列表中，mysql就能將該查詢轉(zhuǎn)換為一個常量
- eq_ref：唯一性索引掃描，表中只有一條記錄與之匹配，常用于主鍵或唯一索引掃描（兩個表是多對一或者一對一的關(guān)系，被連接的表是一的情況下，他的查詢是eq_ref）
- ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，本質(zhì)上也是一種索引訪問，它返回匹配某個單獨值的所有行，然而他可能會找到多個復(fù)合條件的行，屬于查找和掃描的結(jié)合體
- range：只檢索給定范圍的行，使用一個索引來選擇行，key列顯示使用了哪個索引，一般where語句里出現(xiàn)了betweent，<,>,in等的查詢，這種范圍掃描索引比全表掃描好
- index：index與all的區(qū)別，index只遍歷索引樹，索引文件通常比數(shù)據(jù)文件小
- all：全表掃描
----------------------------------------------type類型-------------------------------------------------------
- possible_keys：顯示可能應(yīng)用的的索引（理論上）
- key：實際使用的索引，查詢中若使用了覆蓋索引，則該索引僅僅出現(xiàn)在key中
- key_len：表示索引中使用的字節(jié)數(shù)，在不損失精度的情況下越短越好，kenlen顯示的值為索引字段的最大可能長度，并非實際使用長度，kenlen是根據(jù)表定義計算而得，而不是通過表內(nèi)檢索出的
key_len長度：13是因為char(4)*utf8(3)+允許為null(1)=13
- ref：顯示索引的哪一列被使用了，如果可能的話是一個常數(shù)，哪些列或常量被用于查找索引列上的值
- rows：根據(jù)表統(tǒng)計信息及索引選用情況，大致計算出找到所需的記錄所需要讀取的行數(shù)
沒建立索引時查詢t1 t2表 t1表對應(yīng)t2表的id t2表 col1的值要為'ac'
對于id這個字段t1表對t2表相當(dāng)于 一對多
t1表的type為 eq_ref代表唯一性索引掃描，表中只有一條記錄與之匹配，t2表對應(yīng)t1的這個id對應(yīng)的col值只有一個，根據(jù)t2表的主鍵id索引查詢，t1表讀取了一行，t2表讀取了640行
建立索引后
t1讀取一行，t2讀取142行，ref非唯一性索引掃描，返回匹配某個單獨值的所有行，返回t2對應(yīng)id的col所有行，而t1對應(yīng)id的col只有一行，所以type為eq_ref
extra
包含不適合在其他列展現(xiàn)但十分重要的信息
\g ：豎直顯示排序
- using filesort：說明mysql會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取，mysql中無法利用索引完成排序的操作稱為文件排序未被方框框住的圖建立了復(fù)合索引，但是直接使用col3進(jìn)行排序?qū)е驴罩袠情w，mysql不得已只能進(jìn)行filesoft
- using temporary：使用了臨時表保存中間中間結(jié)果，mysql在對查詢結(jié)果排序時使用臨時表。常見于order by排序和group by分組上表中建立了復(fù)合索引 col1_col2 但是直接通過col2進(jìn)行分組導(dǎo)致了mysql不得已只能進(jìn)行filesoft和建立臨時表
- using index 表示相應(yīng)的select操作中使用了覆蓋索引，避免訪問了表的數(shù)據(jù)行，如果同時出現(xiàn)using where 表示索引被用來執(zhí)行索引鍵值的查找，沒有usingwhere表示索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作
- using where 表示使用了 where過濾
- using join buffer 私用了鏈接緩存
- impossible buffer where子句的值總是false 不能用來獲取任何元組
- select tables optimized away 在沒有g(shù)roup by子句的情況下，基于索引優(yōu)化min/max操作，或者對myisam存儲引擎執(zhí)行count(*)操作，不必等到執(zhí)行操作進(jìn)行，查詢執(zhí)行計劃生成的階段即完成優(yōu)化
- distinct 優(yōu)化distinct操作，在找到第一匹配的元組后立即停止查找同樣值的操作
案例
索引優(yōu)化
單表優(yōu)化
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )values(1,1,1,1,'1','1'),(2,2,2,2,'2','2'),(1,1,3,3,'3','3');?select * from article;mysql> select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;+----+-----------+| id | author_id |+----+-----------+| 3 | 1 |+----+-----------+1 row in set (0.00 sec)?mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc liimit 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, 1 warning (0.00 sec) |
可以看出雖然查詢出來了 但是 type是all，extra里面出現(xiàn)了using filesort證明查詢效率很低
需要優(yōu)化
建立索引
|
1
|
create index idx_article_ccv on article(category_id,comments,views); |
查詢
|
1
2
3
4
5
6
7
|
mysql> explain select author_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 | inx_article_ccv | inx_article_ccv | 8 | null | 1 | 100.00 | using index condition; using filesort |+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+1 row in set, 1 warning (0.00 sec) |
這里發(fā)現(xiàn)type 變?yōu)榱?range 查詢?nèi)碜優(yōu)榱?范圍查詢 優(yōu)化了一點
但是 extra 仍然 有 using filesort 證明 索引優(yōu)化并不成功
所以我們刪除索引
|
1
|
drop index idx_article_ccv on article; |
建立新的索引，排除掉range
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
create index idx_article_cv on article(category_id,views); mysql> explain select author_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, 1 warning (0.00 sec)這時候會發(fā)現(xiàn) 優(yōu)化成功 type 變?yōu)榱藃ef extra變?yōu)榱?using where在這次實驗中我又加入了一次試驗 發(fā)現(xiàn)當(dāng)建立索引時comments放在最后也是可行的 mysql> create index idx_article_cvc on article(category_id,views,comments); query ok, 0 rows affected (0.02 sec) records: 0 duplicates: 0 warnings: 0 ? mysql> explain select author_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_cvc | idx_article_cvc | 4 | const | 2 | 33.33 | using where | +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) |
這時候會發(fā)現(xiàn) 優(yōu)化成功 type 變?yōu)榱藃ef extra變?yōu)榱?using where
在這次實驗中我又加入了一次試驗 發(fā)現(xiàn)當(dāng)建立索引時comments放在最后也是可行的
這里發(fā)現(xiàn)了 type仍然是ref，extra也是usingwhere，而只是把索引建立的位置換了一換，把范圍查詢的字段挪到了最后!!!!
雙表優(yōu)化
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
create table if not exists `class`(`id` int(10) unsigned not null primary key auto_increment,`card` int (10) unsigned not null);create table if not exists `book`(`bookid` int(10) unsigned not null primary key auto_increment,`card` int (10) unsigned not null);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)));?mysql> create index y on book(card); explain select * from book left join class on book.card=class.card;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+| 1 | simple | book | null | index | null | y | 4 | null | 20 | 100.00 | using index || 1 | simple | class | null | all | null | null | null | null | 20 | 100.00 | using where; using join buffer (block nested loop) |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+2 rows in set, 1 warning (0.00 sec) |
會發(fā)現(xiàn)并無多大區(qū)別 還是全表查詢 這是因為倆表查詢左連接把左表必須全查詢 這時候只有對右表建立索引才有用
相反的右鏈接必須對左表建立索引才有用
對右表建立索引
|
1
2
3
4
5
6
7
8
9
|
create index y on class;explain select * from book left join class on book.card=class.card;+----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |+----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+| 1 | simple | book | null | index | null | y | 4 | null | 20 | 100.00 | using index || 1 | simple | class | null | ref | y | y | 4 | db01.book.card | 1 | 100.00 | using index |+----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec) |
會發(fā)現(xiàn) 右表只查詢了一次。。type為ref
三表優(yōu)化
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
create table if not exists `phone`(`phoneid` int(10) unsigned not null primary key auto_increment,`card` int (10) unsigned not null)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))); |
先刪除所有索引
|
1
2
3
4
5
6
7
8
9
10
11
|
drop index y on book;drop index y on class;explain select * from class left join book on class.card=book.card left 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.00 | null || 1 | simple | book | null | all | null | null | null | null | 20 | 100.00 | using where; using join buffer (block nested loop) || 1 | simple | phone | null | all | null | null | null | null | 20 | 100.00 | using where; using join buffer (block nested loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+3 rows in set, 1 warning (0.00 sec) |
建立索引
|
1
2
3
4
5
6
7
8
9
10
11
12
|
create index y on book(card);?create index z on phone(card);explain select * from class left join book on class.card=book.card left 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.00 | null || 1 | simple | book | null | ref | y | y | 4 | db01.class.card | 1 | 100.00 | using index || 1 | simple | phone | null | ref | z | z | 4 | db01.book.card | 1 | 100.00 | using index |+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+3 rows in set, 1 warning (0.00 sec) |
會發(fā)現(xiàn)索引建立的非常成功。。 但是left join 最左表必須全部查詢 建立索引
|
1
2
3
4
5
6
7
8
9
10
|
create index x on class(card);explain select * from class left join book on class.card=book.card left 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 | index | null | x | 4 | null | 20 | 100.00 | using index || 1 | simple | book | null | ref | y | y | 4 | db01.class.card | 1 | 100.00 | using index || 1 | simple | phone | null | ref | z | z | 4 | db01.book.card | 1 | 100.00 | using index |+----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+3 rows in set, 1 warning (0.00 sec) |
結(jié)果仍然一樣
建立表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
create table staffs( id int primary key auto_increment, `name` varchar(24)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 index_staffs_nameagepos(`name`,`age`,`pos`); |
索引口訣
- 1.帶頭大哥不能死，中間兄弟不能斷:當(dāng)建立復(fù)合索引時，必須帶上頭索引，不能跳過中間索引直接使用后面的索引，使用后面的索引必須加上中間的索引（可以先使用后面的索引再使用中間的索引，但是不能直接使用后面的索引而跳過中間索引）（針對where）
可以從上圖看出 跳過name的都用不了索引
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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 | index_staffs_nameagepos | index_staffs_nameagepos | 74 | const | 1 | 100.00 | null |+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)?mysql> 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 | index_staffs_nameagepos | index_staffs_nameagepos | 74 | const | 1 | 33.33 | using index condition |+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec) |
可以從語句中看出跳過中間的索引后 key_len 不變 證明第索引pos沒有被用到
- 2.不能對索引列進(jìn)行任何操作（計算，類型轉(zhuǎn)換 等等）
- 3.存儲引擎不能使用索引中范圍條件右邊的列（索引列上少計算）
- 4.盡量使用覆蓋索引，即是只訪問索引的查詢減少select *的用法
- 5.少使用（！=，<>,<,>） is not null ,is null;
- 6.like以 '%'開頭會導(dǎo)致索引失效（使用覆蓋索引課避免索引失效）覆蓋索引：（建立的索引與查詢的字段順序數(shù)量盡量一致）
- 7.字符串不加單引號會導(dǎo)致索引失效（mysql會將字符串類型強制轉(zhuǎn)換 導(dǎo)致索引失效）
- 8.少用or，用它連接會失效
索引案例
假設(shè)index（a，b，c）
y代表索引全部使用了 n全沒使用
| where語句 | 索引是否被使用 |
|---|---|
| where a=3 and c=5 | (中間b斷掉了)使用了a 沒使用c |
| where a=3 and b=4 andc=5 | y |
| where a=3 and c=5 and b=4 | y這里mysql自動做了優(yōu)化對語句排序 |
| where a=3 and b>4 and c=5 | a,b被使用 |
| where a=3 and b like 'k%' and c=5 | y like后面常量開頭索引全用 |
| where b=3 and c=4 | n |
| where a=3 and c>5 and b=4 | y:mysql自動做了優(yōu)化對語句排序 范圍c之后索引才會失效 |
| where b=3 and c=4 and a=2 | y :mysql自動做了優(yōu)化對語句排序 |
| where c=5 and b=4 and a=3 | y :mysql自動做了優(yōu)化對語句排序 |
假設(shè)index（a，b，c, d）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
create table test03(id int primary key not null auto_increment,a int(10),b int(10),c int(10),d int(10),?insert into test03(a,b,c,d) values (3,4,5,6);insert into test03(a,b,c,d) values (3,4,5,6);insert into test03(a,b,c,d) values (3,4,5,6);insert into test03(a,b,c,d) values (3,4,5,6);?create index idx_test03_abcd on test03(a,b,c,d); |
###
| where a=3 and b>4 and c=5 | 使用了a和b ，b后面的索引全失效 |
|---|---|
| where a=3 and b=4 and d=6 order by c | 使用了a和b，c其實也用了但是是用在排序，沒有統(tǒng)計到mysql中 |
| where a=3 and b=4 order by c | 使用了a和b，c其實也用了但是是用在排序，沒有統(tǒng)計到mysql中 |
| where a=3 and b=4 order by d | 使用了a和b, 這里跳過c 會導(dǎo)致using filesort |
| where a=3 and d=6 order by b ,c | 使用了a, 排序用到了b,c索引 |
| where a=3 and d=6 order by c ,b | 使用了 a，會產(chǎn)生using filesort，因為跳過了b對c進(jìn)行排序 |
| where a=3 and b=4 order by b ,c | y 全使用 |
| where a=3 and b=4 and d&##61;6 order by c , b | 使用了a，b，不會產(chǎn)生using filesort 因為在對c，b排序前對b進(jìn)行了查詢，查詢時b已經(jīng)確定了（常量），這樣就沒有跳過b對c進(jìn)行排序了，而是相當(dāng)于直接對c排序 相當(dāng)于第三格的查詢語句 |
group by 更嚴(yán)重group by先分組再排序 把order by換為 group by 甚至?xí)a(chǎn)生using temporary，與order by差不多，但是更嚴(yán)重 而且與group by產(chǎn)生的效果差不多就不做演示了
order by 索引優(yōu)化
| orderby 條件 | extra |
|---|---|
| where a>4 order by a | using where using index |
| where a>4 order by a,b | using where using index |
| where a>4 order by b | using where, using index ,using filesort（order by 后面帶頭大哥不在） |
| where a>4 order by b,a | using where, using index ,using filesort（order by 后面順序） |
| where a=const order by b,c | 如果where使用索引的最左前綴定義為常量，則order by能使用索引 |
| where a=const and b=const order by c | where使用索引的最左前綴定義為常量，則order by能使用索引 |
| where a=const and b>3 order by b c | using where using index |
| order by a asc, b desc ,c desc | 排序不一致 升降機 |
exsites
|
1
2
3
4
5
6
7
|
select a.* from a a where exists(select 1 from b b where a.id=b.id)以上查詢使用了exists語句,exists()會執(zhí)行a.length次,它并不緩存exists()結(jié)果集,因為exists()結(jié)果集的內(nèi)容并不重要,重要的是結(jié)果集中是否有記錄,如果有則返回true,沒有則返回false. 它的查詢過程類似于以下過程?list resultset=[]; array a=(select * from a)for(int i=0;i<a.length;i++) { if(exists(a[i].id) { //執(zhí)行select 1 from b b where b.id=a.id是否有記錄返回 resultset.add(a[i]); } } return resultset;?當(dāng)b表比a表數(shù)據(jù)大時適合使用exists(),因為它沒有那么遍歷操作,只需要再執(zhí)行一次查詢就行. 如:a表有10000條記錄,b表有1000000條記錄,那么exists()會執(zhí)行10000次去判斷a表中的id是否與b表中的id相等. 如:a表有10000條記錄,b表有100000000條記錄,那么exists()還是執(zhí)行10000次,因為它只執(zhí)行a.length次,可見b表數(shù)據(jù)越多,越適合exists()發(fā)揮效果. 再如:a表有10000條記錄,b表有100條記錄,那么exists()還是執(zhí)行10000次,還不如使用in()遍歷10000*100次,因為in()是在內(nèi)存里遍歷比較,而exists()需要查詢數(shù)據(jù)庫,我們都知道查詢數(shù)據(jù)庫所消耗的性能更高,而內(nèi)存比較很快. |
mysql慢查詢?nèi)罩久?/h3>
1
show variables like '%slow_query_log%';
|
1
|
show variables like '%slow_query_log%'; |
顯示是否開啟mysql慢查詢?nèi)罩?/p>
|
1
|
set global slow_query_log=0; |
關(guān)閉mysql慢查詢?nèi)罩?/p>
|
1
|
set global slow_query_log=1; |
開啟mysql慢查詢?nèi)罩?/p>
|
1
|
show variables like '%long_query_time%'; |
顯示超過多長時間即為 慢查詢
|
1
|
set global long_quert_time=10; |
修改慢查詢時間為10秒，當(dāng)查詢語句時間超過10秒即為慢查詢
|
1
|
show global status like '%slow_queries%'; |
顯示一共有幾條慢查詢語句
|
1
|
[root@iz0jlh1zn42cgftmrf6p6sz data]# cat mysql-slow.log |
linux查詢慢sql
函數(shù)操作批量插入數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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, #編號 enname 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; |
|
1
2
|
show variables like 'log_bin_trust_function_creators';set global log_bin_trust_function_creators=1; |
創(chuàng)建函數(shù)：隨機產(chǎn)生部門編號 隨機產(chǎn)生字符串
delimiter $$是因為sql都是；進(jìn)行結(jié)尾但是創(chuàng)建函數(shù)過程要多次使用；所以改變sql執(zhí)行結(jié)束的條件為輸入$$，相當(dāng)于代替了分號' ；'
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
//定義函數(shù)1 delimiter $$ create function rand_string(n int) returns varchar(255) begin declare chars_set varchar(100) default 'abcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyz'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do set return_str = concat(return_str,substring(chars_set,floor(1 + rand()*52),1)); set i = i + 1; end while; return return_str; end $$//定義函數(shù)2 delimiter $$ create function rand_num() returns int(5) begin declare i int default 0; set i = floor(100 + rand()*10); return i; end $$//定義存儲過程1 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, enname, 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 $$//定義存儲過程2 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 $$//開始插入數(shù)據(jù) delimiter ; call insert_dept(100,10); call insert_emp(100001,500000);show profile分析sql mysql> show variables like 'profiling'; +---------------+-------+ | variable_name | value | +---------------+-------+ | profiling | off | +---------------+-------+ 1 row in set (0.00 sec) ? mysql> set profiling=on; query ok, 0 rows affected, 1 warning (0.00 sec) ? mysql> show variables like 'profiling'; +---------------+-------+ | variable_name | value | +---------------+-------+ | profiling | on | +---------------+-------+ 1 row in set (0.01 sec)———————————————— |
隨便寫幾條插入語句‘
顯示查詢操作語句的速度
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> show profiles;+----------+------------+----------------------------------------------------------------+| query_id | duration | query |+----------+------------+----------------------------------------------------------------+| 1 | 0.00125325 | show variables like 'profiling' || 2 | 0.00018850 | select * from dept || 3 | 0.00016825 | select * from tb1_emp e inner join tbl_dept d on e.deptid=d.id || 4 | 0.00023900 | show tables || 5 | 0.00031125 | select * from tbl_emp e inner join tbl_dept d on e.deptid=d.id || 6 | 0.00024775 | select * from tbl_emp e inner join tbl_dept d on e.deptid=d.id || 7 | 0.00023725 | select * from tbl_emp e inner join tbl_dept d on e.deptid=d.id || 8 | 0.00023825 | select * from tbl_emp e left join tbl_dept d on e.deptid=d.id || 9 | 0.35058075 | select * from emp group by id%10 limit 15000 || 10 | 0.35542250 | select * from emp group by id%10 limit 15000 || 11 | 0.00024550 | select * from tbl_emp e left join tbl_dept d on e.deptid=d.id || 12 | 0.36441850 | select * from emp group by id%20 order by 5 |+----------+------------+----------------------------------------------------------------+12 rows in set, 1 warning (0.00 sec) |
顯示查詢過程 sql生命周期
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
mysql> show profile cpu,block io for query 3;+----------------------+----------+----------+------------+--------------+---------------+| status | duration | cpu_user | cpu_system | block_ops_in | block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting | 0.000062 | 0.000040 | 0.000021 | 0 | 0 || checking permissions | 0.000004 | 0.000003 | 0.000001 | 0 | 0 || checking permissions | 0.000015 | 0.000006 | 0.000003 | 0 | 0 || opening tables | 0.000059 | 0.000039 | 0.000020 | 0 | 0 || query end | 0.000004 | 0.000002 | 0.000001 | 0 | 0 || closing tables | 0.000002 | 0.000001 | 0.000000 | 0 | 0 || freeing items | 0.000014 | 0.000010 | 0.000005 | 0 | 0 || cleaning up | 0.000009 | 0.000006 | 0.000003 | 0 | 0 |+----------------------+----------+----------+------------+--------------+---------------+8 rows in set, 1 warning (0.00 sec)?mysql> show profile cpu,block io for query 12;+----------------------+----------+----------+------------+--------------+---------------+| status | duration | cpu_user | cpu_system | block_ops_in | block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting | 0.000063 | 0.000042 | 0.000021 | 0 | 0 || checking permissions | 0.000006 | 0.000003 | 0.000002 | 0 | 0 || opening tables | 0.000013 | 0.000009 | 0.000004 | 0 | 0 || init | 0.000028 | 0.000017 | 0.000008 | 0 | 0 || system lock | 0.000007 | 0.000004 | 0.000002 | 0 | 0 || optimizing | 0.000004 | 0.000002 | 0.000002 | 0 | 0 || statistics | 0.000014 | 0.000010 | 0.000004 | 0 | 0 || preparing | 0.000008 | 0.000005 | 0.000003 | 0 | 0 || creating tmp table | 0.000028 | 0.000018 | 0.000009 | 0 | 0 || sorting result | 0.000003 | 0.000002 | 0.000001 | 0 | 0 || executing | 0.000002 | 0.000002 | 0.000001 | 0 | 0 || sending data | 0.364132 | 0.360529 | 0.002426 | 0 | 0 || creating sort index | 0.000053 | 0.000034 | 0.000017 | 0 | 0 || end | 0.000004 | 0.000002 | 0.000002 | 0 | 0 || query end | 0.000007 | 0.000005 | 0.000002 | 0 | 0 || removing tmp table | 0.000005 | 0.000003 | 0.000002 | 0 | 0 || query end | 0.000003 | 0.000002 | 0.000001 | 0 | 0 || closing tables | 0.000006 | 0.000004 | 0.000002 | 0 | 0 || freeing items | 0.000023 | 0.000016 | 0.000007 | 0 | 0 || cleaning up | 0.000012 | 0.000007 | 0.000004 | 0 | 0 |+----------------------+----------+----------+------------+--------------+---------------+20 rows in set, 1 warning (0.00 sec) |
如果出現(xiàn)以上這四個 中的任何一個就需要 優(yōu)化查詢語句
全局查詢?nèi)罩?/h3>
1
2
set global general_log=1;
set global log_output='table';
|
1
2
|
set global general_log=1;set global log_output='table'; |
此后你編寫的sql語句將會記錄到mysql庫里的general_log表，可以用下面的命令查看
|
1
2
3
4
5
6
7
8
|
select * from mysql.general_log;mysql> select * from mysql.general_log;+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+| event_time | user_host | thread_id | server_id | command_type | argument |+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+| 2021-12-06 11:53:53.457242 | root[root] @ localhost [] | 68 | 1 | query | select * from mysql.general_log |+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+1 row in set (0.00 sec) |
mysql鎖
- 讀鎖（共享鎖）：針對同一份數(shù)據(jù)，多個讀操作可以同時進(jìn)行而不會互相影響
- 寫鎖（排它鎖）：當(dāng)前寫操作沒有完成時，它會阻斷其他寫鎖和讀鎖
- 行鎖：偏向innodb引擎，開銷大，加鎖慢，會出現(xiàn)死鎖：鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)量高
- 表鎖：偏向myisam引擎，開銷小，加鎖快；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低
在下面進(jìn)行表鎖的測試
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
use big_data;?create table mylock (id int not null primary key auto_increment,name varchar(20) default '') 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; |
鎖命令
|
1
2
3
|
lock table mylock read,book write;## 讀鎖鎖mylock 寫鎖鎖bookshow open tables; ##顯示哪些表被加鎖了unlock tables;##取消鎖 |
表鎖：讀鎖
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
##添加讀鎖后不可修改mysql> lock table mylock read;##1query ok, 0 rows affected (0.00 sec)?mysql> select * from mylock;##1+----+------+| id | name |+----+------+| 1 | a || 2 | b || 3 | c || 4 | d || 5 | e |+----+------+5 rows in set (0.00 sec)?mysql> update mylock set name='a2' where id=1; ##1error 1099 (hy000): table 'mylock' was locked with a read lock and can't be updated##改不了當(dāng)前讀鎖鎖住的表##讀不了其他表mysql> select * from book;##1error 1100 (hy000): table 'book' was not locked with lock tables |
為了區(qū)分兩個命令 把1當(dāng)作原有的mysql命令終端上的操作，2當(dāng)作新建的mysql終端
新建一個mysql終端命令操作
|
1
2
|
##新建一個mysql終端命令操作mysql> update mylock set name='a3' where id=1; ##2 |
發(fā)現(xiàn)會出現(xiàn)阻塞操作
在原有的mysql命令終端上取消鎖
|
1
2
3
|
unlock tables;##1query ok, 1 row affected (2 min 1.46 sec) ##2rows matched: 1 changed: 1 warnings: 0 ##2 |
會發(fā)現(xiàn)阻塞了兩分鐘多
總結(jié) ：當(dāng)讀鎖鎖表mylock之后：1.查詢操作：當(dāng)前client（終端命令操作1）可以進(jìn)行查詢表mylock
其他client（終端命令操作2）也可以查詢表mylock 2.dml操作（增刪改）當(dāng)前client會失效報錯 error 1099 (hy000): table 'mylock' was locked with a read lock and can't be updated 其他client進(jìn)行dml操作會讓mysql陷入阻塞狀態(tài)直到當(dāng)前session釋放鎖
表鎖：寫鎖
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> lock table mylock write; query ok, 0 rows affected (0.00 sec)給當(dāng)前session mylock表加上寫鎖 mysql> update mylock set name='a4'where id=1 ; query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0 ? mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a4 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ mysql> select * from book; error 1100 (hy000): table 'book' was not locked with lock tables |
會發(fā)現(xiàn)無法操其他表但是可以操作加上鎖的表
再開啟一個新的客戶端測試被鎖住的表
|
1
2
3
|
mysql> select * from mylock;?5 rows in set (2 min 30.92 sec) |
發(fā)現(xiàn)新的客戶端上操作（增刪改查）被寫鎖鎖住的表會陷入阻塞狀態(tài)
作
分析表鎖定
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show status like 'table%';+----------------------------+-------+| variable_name | value |+----------------------------+-------+| table_locks_immediate | 194 || table_locks_waited | 0 || table_open_cache_hits | 18 || table_open_cache_misses | 2 || table_open_cache_overflows | 0 |+----------------------------+-------+5 rows in set (0.00 sec) |
行鎖
innodb 的行鎖模式
innodb 實現(xiàn)了以下兩種類型的行鎖。
- 共享鎖（s）：又稱為讀鎖，簡稱s鎖，共享鎖就是多個事務(wù)對于同一數(shù)據(jù)可以共享一把鎖，都能訪問到數(shù)據(jù)，但是只能讀不能修改。
- 排他鎖（x）：又稱為寫鎖，簡稱x鎖，排他鎖就是不能與其他鎖并存，如一個事務(wù)獲取了一個數(shù)據(jù)行的排他鎖，其他事務(wù)就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務(wù)是可以對數(shù)據(jù)就行讀取和修改。
對于update、delete和insert語句，innodb會自動給涉及數(shù)據(jù)集加排他鎖（x)；
對于普通select語句，innodb不會加任何鎖；
可以通過以下語句顯示給記錄集加共享鎖或排他鎖 。
|
1
2
3
|
共享鎖（s）：select * from table_name where ... lock in share mode?排他鎖（x) ：select * from table_name where ... for update |
由于行鎖支持事務(wù)，在此復(fù)習(xí)一下
事務(wù)
事務(wù)是一組由sql語句組成的邏輯處理單元，事務(wù)具有四個屬性：acid
- 原子性（atomicity）：事務(wù)是一個原子操作單元，其對數(shù)據(jù)的操作要么全部執(zhí)行，要么全不執(zhí)行。
- 一致性（consistent）：在事務(wù)開始和完成時，數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)都必須應(yīng)用于事務(wù)的修改，以保持?jǐn)?shù)據(jù)的完整性；事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如b樹索引或雙向鏈表）也都必須是正確的。
- 隔離性（isolation）：數(shù)據(jù)庫提供一定的隔離機制，保證事務(wù)在不受外部并發(fā)操作影響的"獨立"環(huán)境執(zhí)行。這意味著事務(wù)處理過程的中間狀態(tài)對外部都是不可見的，反之亦然。
- 持久性（durable）：事務(wù)完成后，它對數(shù)據(jù)的操作是永久性的，哪怕出現(xiàn)系統(tǒng)故障也能維持
并發(fā)事務(wù)帶來的問題：
更新丟失，臟讀，不可重復(fù)讀，幻讀
| acid屬性 | 含義 |
|---|---|
| 原子性（atomicity） | 事務(wù)是一個原子操作單元，其對數(shù)據(jù)的修改，要么全部成功，要么全部失敗。 |
| 一致性（consistent） | 在事務(wù)開始和完成時，數(shù)據(jù)都必須保持一致狀態(tài)。 |
| 隔離性（isolation） | 數(shù)據(jù)庫系統(tǒng)提供一定的隔離機制，保證事務(wù)在不受外部并發(fā)操作影響的 “獨立” 環(huán)境下運行。 |
| 持久性（durable） | 事務(wù)完成之后，對于數(shù)據(jù)的修改是永久的。 |
并發(fā)事務(wù)處理帶來的問題
| 問題 | 含義 |
|---|---|
| 丟失更新（lost update） | 當(dāng)兩個或多個事務(wù)選擇同一行，最初的事務(wù)修改的值，會被后面的事務(wù)修改的值覆蓋。 |
| 臟讀（dirty reads） | 當(dāng)一個事務(wù)正在訪問數(shù)據(jù)，并且對數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫中，這時，另外一個事務(wù)也訪問這個數(shù)據(jù)，然后使用了這個數(shù)據(jù)。 |
| 不可重復(fù)讀（non-repeatable reads） | 一個事務(wù)在讀取某些數(shù)據(jù)后的某個時間，再次讀取以前讀過的數(shù)據(jù)，卻發(fā)現(xiàn)和以前讀出的數(shù)據(jù)不一致。 |
| 幻讀（phantom reads） | 一個事務(wù)按照相同的查詢條件重新讀取以前查詢過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)。 |
事務(wù)隔離級別
為了解決上述提到的事務(wù)并發(fā)問題，數(shù)據(jù)庫提供一定的事務(wù)隔離機制來解決這個問題。數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價也就越大，因為事務(wù)隔離實質(zhì)上就是使用事務(wù)在一定程度上“串行化” 進(jìn)行，這顯然與“并發(fā)” 是矛盾的。
數(shù)據(jù)庫的隔離級別有4個，由低到高依次為read uncommitted、read committed、repeatable read、serializable，這四個級別可以逐個解決臟寫、臟讀、不可重復(fù)讀、幻讀這幾類問題。
| 隔離級別 | 丟失更新 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
|---|---|---|---|---|
| read uncommitted | × | √ | √ | √ |
| read committed | × | × | √ | √ |
| repeatable read（默認(rèn)） | × | × | × | √ |
| serializable | × | × | × | × |
備注 ： √ 代表可能出現(xiàn) ， × 代表不會出現(xiàn) 。
mysql 的數(shù)據(jù)庫的默認(rèn)隔離級別為 repeatable read ， 查看方式：
|
1
|
show variables like 'tx_isolation'; |
行鎖測試建表， 案例準(zhǔn)備工作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
create table test_innodb_lock( id int(11), name varchar(16), sex varchar(1))engine = innodb default charset=utf8;?insert into test_innodb_lock values(1,'100','1');insert into test_innodb_lock values(3,'3','1');insert into test_innodb_lock values(4,'400','0');insert into test_innodb_lock values(5,'500','1');insert into test_innodb_lock values(6,'600','0');insert into test_innodb_lock values(7,'700','0');insert into test_innodb_lock values(8,'800','1');insert into test_innodb_lock values(9,'900','1');insert into test_innodb_lock values(1,'200','0');?create index idx_test_innodb_lock_id on test_innodb_lock(id);create index idx_test_innodb_lock_name on test_innodb_lock(name); |
行鎖測試
還是開倆個終端測試，關(guān)閉事自動事務(wù)提交，因為自動事務(wù)提交會自動加鎖釋放鎖；
|
1
2
3
|
mysql> set autocommit=0;mysql> set autocommit=0; |
會發(fā)現(xiàn)查詢無影響
對左邊進(jìn)行更新操作
|
1
2
3
|
mysql> update test_innodb_lock set name='100' where id=3;query ok, 0 rows affected (0.00 sec)rows matched: 1 changed: 0 warnings: 0 |
對左邊進(jìn)行更新操作
對右邊進(jìn)行更新操作后停止操作
|
1
2
|
mysql> update test_innodb_lock set name='340' where id=3;error 1205 (hy000): lock wait timeout exceeded; try restarting transaction |
會發(fā)現(xiàn)進(jìn)行阻塞了 直到鎖釋放或者提交事務(wù)（commit）為止
對于innodb引擎來說，對某一行數(shù)據(jù)進(jìn)行dml（增刪改）操作會對操作的那行添加排它鎖
別的事務(wù)就不能執(zhí)行這行語句了，但是可以操作其他行的數(shù)據(jù)
無索引行鎖會升級成表鎖：如果不通過索引條件檢索數(shù)據(jù)，那么innodb會對表中所有記錄加鎖，實際效果和表鎖一樣
記住進(jìn)行操作時使用索引：innodb引擎索引失效時時行鎖會升級為表鎖
|
1
2
3
|
mysql> update test_innodb_lock set sex='2' where name=400;query ok, 0 rows affected (0.00 sec)rows matched: 2 changed: 0 warnings: 0 |
注意這里name沒有加單引號 索引失效
|
1
2
3
|
mysql> update test_innodb_lock set sex='3' where id=3;query ok, 1 row affected (23.20 sec)rows matched: 1 changed: 1 warnings: 0 |
發(fā)現(xiàn)了對其他行操作也陷入了阻塞狀態(tài)，這是沒加索引導(dǎo)致的行鎖升級為表鎖
本來只對一行數(shù)據(jù)加鎖 但是由于忘記給name字段加單引號導(dǎo)致索引失效給全表都加上了鎖；
間隙鎖
當(dāng)我們使用范圍條件而不是想等條件進(jìn)行檢索數(shù)據(jù)，并請求共享或排它鎖，在那個范圍條件中有不存在的記錄，叫做間隙，innodb也會對這個間隙進(jìn)行加鎖，這種鎖機制就叫做間隙鎖
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> select * from test_innodb_lock;+------+------+------+| id | name | sex |+------+------+------+| 1 | 100 | 2 || 3 | 100 | 3 || 4 | 400 | 0 || 5 | 500 | 1 || 6 | 600 | 0 || 7 | 700 | 3 || 8 | 800 | 1 || 9 | 900 | 2 || 1 | 200 | 0 |+------+------+------+沒有id為2的數(shù)據(jù) |
行鎖征用情況查看
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> show status like 'innodb_row_lock%';+-------------------------------+--------+| variable_name | value |+-------------------------------+--------+| innodb_row_lock_current_waits | 0 || innodb_row_lock_time | 284387 || innodb_row_lock_time_avg | 21875 || innodb_row_lock_time_max | 51003 || innodb_row_lock_waits | 13 |+-------------------------------+--------+5 rows in set (0.00 sec)innodb_row_lock_current_waits: 當(dāng)前正在等待鎖定的數(shù)量?innodb_row_lock_time: 從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度?innodb_row_lock_time_avg:每次等待所花平均時長?innodb_row_lock_time_max:從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花的時間?innodb_row_lock_waits: 系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù) |
行鎖總結(jié)
innodb存儲引擎由于實現(xiàn)了行級鎖定，雖然在鎖定機制的實現(xiàn)方面帶來了性能損耗可能比表鎖會更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)由于myisam的表鎖的。當(dāng)系統(tǒng)并發(fā)量較高的時候，innodb的整體性能和myisam相比就會有比較明顯的優(yōu)勢。
但是，innodb的行級鎖同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r候，可能會讓innodb的整體性能表現(xiàn)不僅不能比myisam高，甚至可能會更差。
優(yōu)化建議：
- 盡可能讓所有數(shù)據(jù)檢索都能通過索引來完成，避免無索引行鎖升級為表鎖。
- 合理設(shè)計索引，盡量縮小鎖的范圍
- 盡可能減少索引條件，及索引范圍，避免間隙鎖
- 盡量控制事務(wù)大小，減少鎖定資源量和時間長度
- 盡可使用低級別事務(wù)隔離（但是需要業(yè)務(wù)層面滿足需求）
以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持服務(wù)器之家。
原文鏈接：https://blog.csdn.net/m0_60264772/article/details/121778471
