超全MySQL學(xué)習(xí)筆記

更新時(shí)間：2021年12月25日 16:20:52 作者：進(jìn)擊地小白

本文詳細(xì)介紹了MySQL索引優(yōu)化、鎖和事物等學(xué)習(xí)記錄，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

MyISAM和InnoDB

對(duì)比	MyISAM	InnoDB
主外鍵	不支持	支持
事務(wù)	不支持	支持
行表鎖	表鎖，操作時(shí)即使操作一條記錄也會(huì)鎖住一整張表，不適合高并發(fā)的操作	行鎖，操作時(shí)只鎖住某一行，不會(huì)影響到其他行，適合高并發(fā)
緩存	只緩存索引，不緩存其他數(shù)據(jù)	緩存索引和真實(shí)數(shù)據(jù)，對(duì)內(nèi)存要求較高，而且內(nèi)存大小對(duì)性能有影響
表空間	小	大
關(guān)注點(diǎn)	性能	事務(wù)
默認(rèn)安裝	Y	Y

性能下降SQL慢的原因：

查詢語(yǔ)句寫的差
索引失效
關(guān)聯(lián)查詢太多join （設(shè)計(jì)缺陷或不得已的需求）
服務(wù)器調(diào)優(yōu)及各個(gè)參數(shù)設(shè)置(緩沖，線程參數(shù))

Mysql執(zhí)行順序

手寫
機(jī)讀先從from開(kāi)始

SQLJoin

a表

 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表

 +----+------+--------+
 | 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不支持全連接

使用以下方式可以實(shí)現(xiàn)全連接

 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的獨(dú)有和b的獨(dú)有

 mysql> select * from tbl_dept a left join tbl_emp b on  a.id=b.deptId where b.id is null
     -> union
     -> select * from tbl_dept a right join tbl_emp b on  a.id=b.deptId where a.id is null;
 +------+----------+--------+------+------+--------+
 | id   | deptName | locAdd | id   | name | deptId |
 +------+----------+--------+------+------+--------+
 |    5 | FD       | 15     | NULL | NULL |   NULL |
 | NULL | NULL     | NULL   |    8 | s9   |     51 |
 +------+----------+--------+------+------+--------+
 2 rows in set (0.01 sec)

索引

索引的定義：

索引是幫助SQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，索引的本質(zhì)：數(shù)據(jù)結(jié)構(gòu)

可以簡(jiǎn)單的理解為：排好序的快速查找數(shù)據(jù)結(jié)構(gòu)

在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式（引用）指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引，下圖就是一種示例：

一般來(lái)說(shuō)索引也很大，因此索引往往以索引文件的方式存儲(chǔ)在磁盤上

我們平常所說(shuō)的索引，如果沒(méi)有特別指明，一般都是指B樹(shù)（多路搜索樹(shù)，不一定是二叉的）結(jié)構(gòu)組織的索引，

其中聚集索引，次要索引，復(fù)合索引，前綴索引，唯一索引默認(rèn)都是使用B+樹(shù)索引，統(tǒng)稱索引，當(dāng)然除了B+樹(shù)這種類型的索引之外，還有哈希索引。

索引的優(yōu)劣

1.優(yōu)勢(shì)

類似大學(xué)圖書(shū)館圖書(shū)編號(hào)建索引，提高了數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的IO成本

通過(guò)索引對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗

2.劣勢(shì)

實(shí)際上索引也是一張表，該表保存了主鍵與存在索引的字段，并指向?qū)嶓w表的記錄，所以索引列也是占用空間的

雖然索引大大提高了查詢速度，但是會(huì)降低更新表的速度，比如 update，insert，delete操作，因?yàn)楦卤頃r(shí)，MySQL不僅要數(shù)據(jù)也要保存索引文件每次更新添加了索引的字段，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息

索引只是提高效率的一個(gè)因素，在一個(gè)大數(shù)據(jù)量的表上，需要建立最為優(yōu)秀的索引或者寫優(yōu)秀的查詢語(yǔ)句，而不是加了索引就能提高效率

索引分類

單值索引
唯一索引
復(fù)合索引
基本語(yǔ)法:

創(chuàng)建

create [unique] index indexName on mytable(cloumnname(length));

alter mytable add [unique] index [indexName] on (columnname(length));

刪除

drop index [indexName] on mytable

查看

show index from table_name\G

有四種方式來(lái)添加數(shù)據(jù)表的索引

mysql索引結(jié)構(gòu)

BTree索引
Hash索引
full-text全文索引
R-Tree

那些情況建索引

主鍵自動(dòng)建立唯一索引
頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
查詢中與其他表相關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
頻繁更新的字段不適合創(chuàng)建索引，因?yàn)槊看胃虏粏螁胃铝擞涗涍€更新了索引
where條件里用不到的字段不要?jiǎng)?chuàng)建索引
單鍵/組合索引的選擇問(wèn)題 who？（高并發(fā)下建議組合索引）
查詢中排序的字段，排序字段若通過(guò)索引去訪問(wèn)將大大提高排序速度
查詢中統(tǒng)計(jì)或分組字段

哪些情況不要建索引

表記錄少
經(jīng)常操作dml語(yǔ)句的表
數(shù)據(jù)重復(fù)且平均分布的表字段，因此只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引，注意，如果某個(gè)數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒(méi)有太大的實(shí)際效果

性能分析

explian重點(diǎn)

能干什么

表的讀取順序
數(shù)據(jù)讀取操作的操作類型
哪些索引可以被使用
哪些索引被實(shí)際使用
表之間的引用
每張表有多少行被優(yōu)化器查詢

id三種情況

id 相同，執(zhí)行順序由上至下
id不同，如果是子查詢，id序號(hào)遞增，id越大優(yōu)先級(jí)越高
id相同不同，同時(shí)存在

select_type

SIMPLE 簡(jiǎn)單查詢
PRIMARY 主查詢（最外層的查詢）
SUBQUERY 子查詢
DERIUED 某個(gè)查詢的子查詢的臨時(shí)表
UNION 聯(lián)合查詢
UNION RESULT 聯(lián)合查詢結(jié)果

type：：

type顯示的是訪問(wèn)類型排列，是較為重要的一個(gè)指標(biāo)

從最好到最差依次是：

system > const > eq_ref> ref > range > index > ALL;

一般來(lái)說(shuō)，得保證查詢至少達(dá)到range級(jí)別，最好ref

----------------------------------------------type類型-------------------------------------------------------

system:表只有一行記錄（等于系統(tǒng)表）這是const類型的特列一般不會(huì)出現(xiàn)，可忽略不計(jì)
const：表示通過(guò)索引一次就查詢到了，const用來(lái)比較primary key或者unique索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快，如將主鍵置于where列表中，Mysql就能將該查詢轉(zhuǎn)換為一個(gè)常量
eq_ref：唯一性索引掃描，表中只有一條記錄與之匹配，常用于主鍵或唯一索引掃描（兩個(gè)表是多對(duì)一或者一對(duì)一的關(guān)系，被連接的表是一的情況下，他的查詢是eq_ref）
ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行，本質(zhì)上也是一種索引訪問(wèn)，它返回匹配某個(gè)單獨(dú)值的所有行，然而他可能會(huì)找到多個(gè)復(fù)合條件的行，屬于查找和掃描的結(jié)合體
range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行，key列顯示使用了哪個(gè)索引，一般where語(yǔ)句里出現(xiàn)了betweent，<,>,in等的查詢，這種范圍掃描索引比全表掃描好
index：index與ALL的區(qū)別，index只遍歷索引樹(shù)，索引文件通常比數(shù)據(jù)文件小
ALL：全表掃描

----------------------------------------------type類型-------------------------------------------------------

possible_keys：顯示可能應(yīng)用的的索引（理論上）
key：實(shí)際使用的索引，查詢中若使用了覆蓋索引，則該索引僅僅出現(xiàn)在key中
key_len：表示索引中使用的字節(jié)數(shù)，在不損失精度的情況下越短越好，kenlen顯示的值為索引字段的最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，kenlen是根據(jù)表定義計(jì)算而得，而不是通過(guò)表內(nèi)檢索出的

key_len長(zhǎng)度：13是因?yàn)閏har(4)*utf8(3)+允許為null(1)=13

ref：顯示索引的哪一列被使用了，如果可能的話是一個(gè)常數(shù)，哪些列或常量被用于查找索引列上的值

rows：根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致計(jì)算出找到所需的記錄所需要讀取的行數(shù)

沒(méi)建立索引時(shí)查詢t1 t2表 t1表對(duì)應(yīng)t2表的id t2表 col1的值要為'ac'

對(duì)于Id這個(gè)字段t1表對(duì)t2表相當(dāng)于一對(duì)多

t1表的type為 eq_ref代表唯一性索引掃描，表中只有一條記錄與之匹配，t2表對(duì)應(yīng)t1的這個(gè)id對(duì)應(yīng)的col值只有一個(gè)，根據(jù)t2表的主鍵id索引查詢，t1表讀取了一行，t2表讀取了640行

建立索引后

t1讀取一行，t2讀取142行，ref非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行，返回t2對(duì)應(yīng)id的col所有行，而t1對(duì)應(yīng)id的col只有一行，所以type為eq_ref

Extra

包含不適合在其他列展現(xiàn)但十分重要的信息

\G ：豎直顯示排序

Using filesort：說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取，mysql中無(wú)法利用索引完成排序的操作稱為文件排序未被方框框住的圖建立了復(fù)合索引，但是直接使用col3進(jìn)行排序?qū)е驴罩袠情w，mysql不得已只能進(jìn)行filesoft

Using temporary：使用了臨時(shí)表保存中間中間結(jié)果，MySQL在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)于order by排序和group by分組上表中建立了復(fù)合索引 col1_col2 但是直接通過(guò)col2進(jìn)行分組導(dǎo)致了mysql不得已只能進(jìn)行filesoft和建立臨時(shí)表
using index 表示相應(yīng)的select操作中使用了覆蓋索引，避免訪問(wèn)了表的數(shù)據(jù)行，如果同時(shí)出現(xiàn)using where 表示索引被用來(lái)執(zhí)行索引鍵值的查找，沒(méi)有usingwhere表示索引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作
using where 表示使用了 where過(guò)濾
using join buffer 私用了鏈接緩存
impossible buffer where子句的值總是false 不能用來(lái)獲取任何元組
select tables optimized away 在沒(méi)有g(shù)roup by子句的情況下，基于索引優(yōu)化min/max操作，或者對(duì)myisam存儲(chǔ)引擎執(zhí)行count(*)操作，不必等到執(zhí)行操作進(jìn)行，查詢執(zhí)行計(jì)劃生成的階段即完成優(yōu)化
distinct 優(yōu)化distinct操作，在找到第一匹配的元組后立即停止查找同樣值的操作

案例

索引優(yōu)化

單表優(yōu)化

 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 li
 imit 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)

可以看出雖然查詢出來(lái)了但是 type是all，Extra里面出現(xiàn)了using filesort證明查詢效率很低

需要優(yōu)化

建立索引

create index idx_article_ccv on article(category_id,comments,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       | 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)化了一點(diǎn)

但是 extra 仍然有 using filesort 證明索引優(yōu)化并不成功

所以我們刪除索引

drop index idx_article_ccv on article;

建立新的索引，排除掉range

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)
這時(shí)候會(huì)發(fā)現(xiàn) 優(yōu)化成功 type 變?yōu)榱藃ef extra變?yōu)榱?using where

在這次實(shí)驗(yàn)中我又加入了一次試驗(yàn) 發(fā)現(xiàn)當(dāng)建立索引時(shí)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)

這時(shí)候會(huì)發(fā)現(xiàn) 優(yōu)化成功 type 變?yōu)榱藃ef extra變?yōu)榱?using where

在這次實(shí)驗(yàn)中我又加入了一次試驗(yàn) 發(fā)現(xiàn)當(dāng)建立索引時(shí)comments放在最后也是可行的

這里發(fā)現(xiàn)了 type仍然是ref，extra也是usingwhere，而只是把索引建立的位置換了一換，把范圍查詢的字段挪到了最后!!!!

雙表優(yōu)化

 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)

會(huì)發(fā)現(xiàn)并無(wú)多大區(qū)別還是全表查詢這是因?yàn)閭z表查詢左連接把左表必須全查詢這時(shí)候只有對(duì)右表建立索引才有用

相反的右鏈接必須對(duì)左表建立索引才有用

對(duì)右表建立索引

 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)

會(huì)發(fā)現(xiàn) 右表只查詢了一次。。type為ref

三表優(yōu)化

 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)));

先刪除所有索引

 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)

建立索引

 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)

會(huì)發(fā)現(xiàn)索引建立的非常成功。。但是left join 最左表必須全部查詢 建立索引

 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é)果仍然一樣

建立表

 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'入職時(shí)間'
 )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ù)合索引時(shí)，必須帶上頭索引，不能跳過(guò)中間索引直接使用后面的索引，使用后面的索引必須加上中間的索引（可以先使用后面的索引再使用中間的索引，但是不能直接使用后面的索引而跳過(guò)中間索引）（針對(duì)where）

可以從上圖看出跳過(guò)name的都用不了索引

 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)

可以從語(yǔ)句中看出跳過(guò)中間的索引后 key_len 不變證明第索引pos沒(méi)有被用到

2.不能對(duì)索引列進(jìn)行任何操作（計(jì)算，類型轉(zhuǎn)換等等）
3.存儲(chǔ)引擎不能使用索引中范圍條件右邊的列（索引列上少計(jì)算）
4.盡量使用覆蓋索引，即是只訪問(wèn)索引的查詢減少select *的用法
5.少使用（！=，<>,<,>） is not null ,is null;
6.like以 '%'開(kāi)頭會(huì)導(dǎo)致索引失效（使用覆蓋索引課避免索引失效）覆蓋索引：（建立的索引與查詢的字段順序數(shù)量盡量一致）
7.字符串不加單引號(hào)會(huì)導(dǎo)致索引失效（mysql會(huì)將字符串類型強(qiáng)制轉(zhuǎn)換導(dǎo)致索引失效）
8.少用or，用它連接會(huì)失效

索引案例

假設(shè)index（a，b，c）

Y代表索引全部使用了 N全沒(méi)使用

where語(yǔ)句	索引是否被使用
where a=3 and c=5	(中間b斷掉了)使用了a 沒(méi)使用c
where a=3 and b=4 andc=5	Y
where a=3 and c=5 and b=4	Y這里mysql自動(dòng)做了優(yōu)化對(duì)語(yǔ)句排序
where a=3 and b>4 and c=5	a,b被使用
where a=3 and b like 'k%' and c=5	Y like后面常量開(kāi)頭索引全用
where b=3 and c=4	N
where a=3 and c>5 and b=4	Y:mysql自動(dòng)做了優(yōu)化對(duì)語(yǔ)句排序范圍c之后索引才會(huì)失效
where b=3 and c=4 and a=2	Y :mysql自動(dòng)做了優(yōu)化對(duì)語(yǔ)句排序
where c=5 and b=4 and a=3	Y :mysql自動(dòng)做了優(yōu)化對(duì)語(yǔ)句排序

假設(shè)index（a，b，c, d）

 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其實(shí)也用了但是是用在排序，沒(méi)有統(tǒng)計(jì)到mysql中
where a=3 and b=4 order by c	使用了a和b，c其實(shí)也用了但是是用在排序，沒(méi)有統(tǒng)計(jì)到mysql中
where a=3 and b=4 order by d	使用了a和b, 這里跳過(guò)c 會(huì)導(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，會(huì)產(chǎn)生using filesort，因?yàn)樘^(guò)了b對(duì)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，不會(huì)產(chǎn)生using filesort 因?yàn)樵趯?duì)c，b排序前對(duì)b進(jìn)行了查詢，查詢時(shí)b已經(jīng)確定了（常量），這樣就沒(méi)有跳過(guò)b對(duì)c進(jìn)行排序了，而是相當(dāng)于直接對(duì)c排序相當(dāng)于第三格的查詢語(yǔ)句

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	排序不一致升降機(jī)

exsites

 select a.* from A a where exists(select 1 from B b where a.id=b.id)
 以上查詢使用了exists語(yǔ)句,exists()會(huì)執(zhí)行A.length次,它并不緩存exists()結(jié)果集,因?yàn)閑xists()結(jié)果集的內(nèi)容并不重要,重要的是結(jié)果集中是否有記錄,如果有則返回true,沒(méi)有則返回false. 它的查詢過(guò)程類似于以下過(guò)程
 ?
 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ù)大時(shí)適合使用exists(),因?yàn)樗鼪](méi)有那么遍歷操作,只需要再執(zhí)行一次查詢就行.  如:A表有10000條記錄,B表有1000000條記錄,那么exists()會(huì)執(zhí)行10000次去判斷A表中的id是否與B表中的id相等.  如:A表有10000條記錄,B表有100000000條記錄,那么exists()還是執(zhí)行10000次,因?yàn)樗粓?zhí)行A.length次,可見(jiàn)B表數(shù)據(jù)越多,越適合exists()發(fā)揮效果.  再如:A表有10000條記錄,B表有100條記錄,那么exists()還是執(zhí)行10000次,還不如使用in()遍歷10000*100次,因?yàn)閕n()是在內(nèi)存里遍歷比較,而exists()需要查詢數(shù)據(jù)庫(kù),我們都知道查詢數(shù)據(jù)庫(kù)所消耗的性能更高,而內(nèi)存比較很快.

Mysql慢查詢?nèi)罩久?/h3>

show VARIABLES like '%slow_query_log%';

顯示是否開(kāi)啟mysql慢查詢?nèi)罩?/p>

set global slow_query_log=0;

關(guān)閉mysql慢查詢?nèi)罩?/p>

set global slow_query_log=1;

開(kāi)啟mysql慢查詢?nèi)罩?/p>

show VARIABLES like '%long_query_time%';

顯示超過(guò)多長(zhǎng)時(shí)間即為慢查詢

set global long_quert_time=10;

修改慢查詢時(shí)間為10秒，當(dāng)查詢語(yǔ)句時(shí)間超過(guò)10秒即為慢查詢

show global status like '%Slow_queries%';

顯示一共有幾條慢查詢語(yǔ)句

[root@iZ0jlh1zn42cgftmrf6p6sZ data]# cat mysql-slow.log

linux查詢慢sql

函數(shù)操作批量插入數(shù)據(jù)

 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, #編號(hào)
    enname VARCHAR(20) NOT NULL DEFAULT '', #名字
    job VARCHAR(9) NOT NULL DEFAULT '', #工作
    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #上級(jí)編號(hào)
    hiredate DATE NOT NULL, #入職時(shí)間
    sal DECIMAL(7,2) NOT NULL, #薪水
    comm DECIMAL(7,2) NOT NULL, #紅利
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 #部門編號(hào)
 )ENGINE=INNODB DEFAULT CHARSET=GBK;

 show variables like 'log_bin_trust_function_creators';
 set global log_bin_trust_function_creators=1;

創(chuàng)建函數(shù)：隨機(jī)產(chǎn)生部門編號(hào) 隨機(jī)產(chǎn)生字符串

DELIMITER $$是因?yàn)閟ql都是；進(jìn)行結(jié)尾但是創(chuàng)建函數(shù)過(guò)程要多次使用；所以改變sql執(zhí)行結(jié)束的條件為輸入$$，相當(dāng)于代替了分號(hào)' ；'

//定義函數(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 $$
//定義存儲(chǔ)過(guò)程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 $$
//定義存儲(chǔ)過(guò)程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 $$
//開(kāi)始插入數(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)

隨便寫幾條插入語(yǔ)句‘

顯示查詢操作語(yǔ)句的速度

 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)

顯示查詢過(guò)程 sql生命周期

 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)以上這四個(gè) 中的任何一個(gè)就需要優(yōu)化查詢語(yǔ)句

全局查詢?nèi)罩?/h3>

  set global general_log=1;
 set global log_output='TABLE';

此后你編寫的sql語(yǔ)句將會(huì)記錄到mysql庫(kù)里的general_log表，可以用下面的命令查看

 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鎖

讀鎖（共享鎖）：針對(duì)同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響
寫鎖（排它鎖）：當(dāng)前寫操作沒(méi)有完成時(shí)，它會(huì)阻斷其他寫鎖和讀鎖
行鎖：偏向InnoDB引擎，開(kāi)銷大，加鎖慢，會(huì)出現(xiàn)死鎖：鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)量高
表鎖：偏向myisam引擎，開(kāi)銷小，加鎖快；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低

在下面進(jìn)行表鎖的測(cè)試

 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;

鎖命令

 lock table mylock read,book write;## 讀鎖鎖mylock  寫鎖鎖book
 show open tables;  ##顯示哪些表被加鎖了
 unlock tables;##取消鎖

表鎖：讀鎖

 ##添加讀鎖后不可修改
 mysql> lock table mylock  read;##1
 Query 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; ##1
 ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
 ##改不了當(dāng)前讀鎖鎖住的表
 ##讀不了其他表
 mysql> select * from book;##1
 ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

為了區(qū)分兩個(gè)命令把1當(dāng)作原有的mysql命令終端上的操作，2當(dāng)作新建的mysql終端

新建一個(gè)mysql終端命令操作

 ##新建一個(gè)mysql終端命令操作
 mysql> update mylock set name='a3' where id=1;  ##2

發(fā)現(xiàn)會(huì)出現(xiàn)阻塞操作

在原有的mysql命令終端上取消鎖

 unlock tables;##1
 Query OK, 1 row affected (2 min 1.46 sec)  ##2
 Rows matched: 1  Changed: 1  Warnings: 0   ##2

會(huì)發(fā)現(xiàn)阻塞了兩分鐘多

總結(jié) ：當(dāng)讀鎖鎖表mylock之后：1.查詢操作：當(dāng)前client（終端命令操作1）可以進(jìn)行查詢表mylock

其他client（終端命令操作2）也可以查詢表mylock 2.DML操作（增刪改）當(dāng)前client會(huì)失效報(bào)錯(cuò) ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated 其他client進(jìn)行DML操作會(huì)讓mysql陷入阻塞狀態(tài)直到當(dāng)前session釋放鎖

表鎖：寫鎖

 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

會(huì)發(fā)現(xiàn)無(wú)法操其他表但是可以操作加上鎖的表

再開(kāi)啟一個(gè)新的客戶端測(cè)試被鎖住的表

 mysql> select * from mylock;
 ?
 5 rows in set (2 min 30.92 sec)

發(fā)現(xiàn)新的客戶端上操作（增刪改查）被寫鎖鎖住的表會(huì)陷入阻塞狀態(tài)

作

分析表鎖定

 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 實(shí)現(xiàn)了以下兩種類型的行鎖。

共享鎖（S）：又稱為讀鎖，簡(jiǎn)稱S鎖，共享鎖就是多個(gè)事務(wù)對(duì)于同一數(shù)據(jù)可以共享一把鎖，都能訪問(wèn)到數(shù)據(jù)，但是只能讀不能修改。
排他鎖（X）：又稱為寫鎖，簡(jiǎn)稱X鎖，排他鎖就是不能與其他鎖并存，如一個(gè)事務(wù)獲取了一個(gè)數(shù)據(jù)行的排他鎖，其他事務(wù)就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務(wù)是可以對(duì)數(shù)據(jù)就行讀取和修改。

對(duì)于UPDATE、DELETE和INSERT語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X)；

對(duì)于普通SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖；

可以通過(guò)以下語(yǔ)句顯示給記錄集加共享鎖或排他鎖。

 共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
 ?
 排他鎖（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

由于行鎖支持事務(wù)，在此復(fù)習(xí)一下

事務(wù)

事務(wù)是一組由SQL語(yǔ)句組成的邏輯處理單元，事務(wù)具有四個(gè)屬性：ACID

原子性（Atomicity）：事務(wù)是一個(gè)原子操作單元，其對(duì)數(shù)據(jù)的操作要么全部執(zhí)行，要么全不執(zhí)行。
一致性（Consistent）：在事務(wù)開(kāi)始和完成時(shí)，數(shù)據(jù)都必須保持一致?tīng)顟B(tài)。這意味著所有相關(guān)的數(shù)據(jù)都必須應(yīng)用于事務(wù)的修改，以保持?jǐn)?shù)據(jù)的完整性；事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹(shù)索引或雙向鏈表）也都必須是正確的。
隔離性（Isolation）：數(shù)據(jù)庫(kù)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的"獨(dú)立"環(huán)境執(zhí)行。這意味著事務(wù)處理過(guò)程的中間狀態(tài)對(duì)外部都是不可見(jiàn)的，反之亦然。
持久性（Durable）：事務(wù)完成后，它對(duì)數(shù)據(jù)的操作是永久性的，哪怕出現(xiàn)系統(tǒng)故障也能維持

并發(fā)事務(wù)帶來(lái)的問(wèn)題：

更新丟失，臟讀，不可重復(fù)讀，幻讀

ACID屬性	含義
原子性（Atomicity）	事務(wù)是一個(gè)原子操作單元，其對(duì)數(shù)據(jù)的修改，要么全部成功，要么全部失敗。
一致性（Consistent）	在事務(wù)開(kāi)始和完成時(shí)，數(shù)據(jù)都必須保持一致?tīng)顟B(tài)。
隔離性（Isolation）	數(shù)據(jù)庫(kù)系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的 “獨(dú)立” 環(huán)境下運(yùn)行。
持久性（Durable）	事務(wù)完成之后，對(duì)于數(shù)據(jù)的修改是永久的。

并發(fā)事務(wù)處理帶來(lái)的問(wèn)題

問(wèn)題	含義
丟失更新（Lost Update）	當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，最初的事務(wù)修改的值，會(huì)被后面的事務(wù)修改的值覆蓋。
臟讀（Dirty Reads）	當(dāng)一個(gè)事務(wù)正在訪問(wèn)數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒(méi)有提交到數(shù)據(jù)庫(kù)中，這時(shí)，另外一個(gè)事務(wù)也訪問(wèn)這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。
不可重復(fù)讀（Non-Repeatable Reads）	一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間，再次讀取以前讀過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)和以前讀出的數(shù)據(jù)不一致。
幻讀（Phantom Reads）	一個(gè)事務(wù)按照相同的查詢條件重新讀取以前查詢過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)。

事務(wù)隔離級(jí)別

為了解決上述提到的事務(wù)并發(fā)問(wèn)題，數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決這個(gè)問(wèn)題。數(shù)據(jù)庫(kù)的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)也就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使用事務(wù)在一定程度上“串行化” 進(jìn)行，這顯然與“并發(fā)” 是矛盾的。

數(shù)據(jù)庫(kù)的隔離級(jí)別有4個(gè)，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個(gè)級(jí)別可以逐個(gè)解決臟寫、臟讀、不可重復(fù)讀、幻讀這幾類問(wèn)題。

隔離級(jí)別	丟失更新	臟讀	不可重復(fù)讀	幻讀
Read uncommitted	×	√	√	√
Read committed	×	×	√	√
Repeatable read（默認(rèn)）	×	×	×	√
Serializable	×	×	×	×

備注： √ 代表可能出現(xiàn) ， × 代表不會(huì)出現(xiàn) 。

Mysql 的數(shù)據(jù)庫(kù)的默認(rèn)隔離級(jí)別為 Repeatable read ，查看方式：

 show variables like 'tx_isolation';

行鎖測(cè)試建表，案例準(zhǔn)備工作

 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);

行鎖測(cè)試

還是開(kāi)倆個(gè)終端測(cè)試，關(guān)閉事自動(dòng)事務(wù)提交，因?yàn)樽詣?dòng)事務(wù)提交會(huì)自動(dòng)加鎖釋放鎖；

 mysql> set autocommit=0;

 mysql> set autocommit=0;

會(huì)發(fā)現(xiàn)查詢無(wú)影響

對(duì)左邊進(jìn)行更新操作

 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

對(duì)左邊進(jìn)行更新操作

對(duì)右邊進(jìn)行更新操作后停止操作

 mysql> update test_innodb_lock set name='340' where id=3;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

會(huì)發(fā)現(xiàn)進(jìn)行阻塞了直到鎖釋放或者提交事務(wù)（commit）為止

對(duì)于innodb引擎來(lái)說(shuō)，對(duì)某一行數(shù)據(jù)進(jìn)行DML（增刪改）操作會(huì)對(duì)操作的那行添加排它鎖

別的事務(wù)就不能執(zhí)行這行語(yǔ)句了，但是可以操作其他行的數(shù)據(jù)

無(wú)索引行鎖會(huì)升級(jí)成表鎖：如果不通過(guò)索引條件檢索數(shù)據(jù)，那么innodb會(huì)對(duì)表中所有記錄加鎖，實(shí)際效果和表鎖一樣

記住進(jìn)行操作時(shí)使用索引：innodb引擎索引失效時(shí)時(shí)行鎖會(huì)升級(jí)為表鎖

 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沒(méi)有加單引號(hào) 索引失效

 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)了對(duì)其他行操作也陷入了阻塞狀態(tài)，這是沒(méi)加索引導(dǎo)致的行鎖升級(jí)為表鎖

本來(lái)只對(duì)一行數(shù)據(jù)加鎖但是由于忘記給name字段加單引號(hào)導(dǎo)致索引失效給全表都加上了鎖；

間隙鎖

當(dāng)我們使用范圍條件而不是想等條件進(jìn)行檢索數(shù)據(jù)，并請(qǐng)求共享或排它鎖，在那個(gè)范圍條件中有不存在的記錄，叫做間隙，innodb也會(huì)對(duì)這個(gè)間隙進(jìn)行加鎖，這種鎖機(jī)制就叫做間隙鎖

 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    |
 +------+------+------+
 沒(méi)有id為2的數(shù)據(jù)

行鎖征用情況查看

 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)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度
 ?
 Innodb_row_lock_time_avg:每次等待所花平均時(shí)長(zhǎng)
 ?
 Innodb_row_lock_time_max:從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次所花的時(shí)間
 ?
 Innodb_row_lock_waits: 系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)

行鎖總結(jié)

InnoDB存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面帶來(lái)了性能損耗可能比表鎖會(huì)更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)由于MyISAM的表鎖的。當(dāng)系統(tǒng)并發(fā)量較高的時(shí)候，InnoDB的整體性能和MyISAM相比就會(huì)有比較明顯的優(yōu)勢(shì)。

但是，InnoDB的行級(jí)鎖同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候，可能會(huì)讓InnoDB的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會(huì)更差。

優(yōu)化建議：

盡可能讓所有數(shù)據(jù)檢索都能通過(guò)索引來(lái)完成，避免無(wú)索引行鎖升級(jí)為表鎖。
合理設(shè)計(jì)索引，盡量縮小鎖的范圍
盡可能減少索引條件，及索引范圍，避免間隙鎖
盡量控制事務(wù)大小，減少鎖定資源量和時(shí)間長(zhǎng)度
盡可使用低級(jí)別事務(wù)隔離（但是需要業(yè)務(wù)層面滿足需求）

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章:

国产无遮挡裸体免费直播视频,久久精品国产蜜臀av,动漫在线视频一区二区,欧亚日韩一区二区三区,久艹在线 免费视频,国产精品美女网站免费,正在播放 97超级视频在线观看,斗破苍穹年番在线观看免费,51最新乱码中文字幕