国产伦精品一区二区三区视频金莲,欧美亚洲日日日日,国产一二懂色红豆在线精品

MyISAM和InnoDB

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

性能下降SQL慢的原因：

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

Mysql執(zhí)行順序

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

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)

可以簡單的理解為：排好序的快速查找數(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)上實(shí)現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引，下圖就是一種示例：

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

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

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

索引的優(yōu)劣

1.優(yōu)勢

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

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

2.劣勢

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

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

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

索引分類

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

創(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

有四種方式來添加數(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)建索引
單鍵/組合索引的選擇問題 who？（高并發(fā)下建議組合索引）
查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
查詢中統(tǒng)計(jì)或分組字段

哪些情況不要建索引

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

性能分析

explian重點(diǎn)

能干什么

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

id三種情況

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

select_type

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

type：：

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

從最好到最差依次是：

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

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

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

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

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

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

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

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

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

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

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

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

建立索引后

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

Extra

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

\G ：豎直顯示排序

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

Using temporary：使用了臨時(shí)表保存中間中間結(jié)果，MySQL在對查詢結(jié)果排序時(shí)使用臨時(shí)表。常見于order by排序和group by分組上表中建立了復(fù)合索引 col1_col2 但是直接通過col2進(jìn)行分組導(dǎo)致了mysql不得已只能進(jìn)行filesoft和建立臨時(shí)表
using index 表示相應(yīng)的select操作中使用了覆蓋索引，避免訪問了表的數(shù)據(jù)行，如果同時(shí)出現(xiàn)using where 表示索引被用來執(zhí)行索引鍵值的查找，沒有usingwhere表示索引用來讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作
using where 表示使用了 where過濾
using join buffer 私用了鏈接緩存
impossible buffer where子句的值總是false 不能用來獲取任何元組
select tables optimized away 在沒有g(shù)roup by子句的情況下，基于索引優(yōu)化min/max操作，或者對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)

可以看出雖然查詢出來了但是 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)并無多大區(qū)別還是全表查詢這是因?yàn)閭z表查詢左連接把左表必須全查詢這時(shí)候只有對右表建立索引才有用

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

對右表建立索引

 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í)，必須帶上頭索引，不能跳過中間索引直接使用后面的索引，使用后面的索引必須加上中間的索引（可以先使用后面的索引再使用中間的索引，但是不能直接使用后面的索引而跳過中間索引）（針對where）

可以從上圖看出跳過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)

可以從語句中看出跳過中間的索引后 key_len 不變證明第索引pos沒有被用到

2.不能對索引列進(jìn)行任何操作（計(jì)算，類型轉(zhuǎn)換等等）
3.存儲(chǔ)引擎不能使用索引中范圍條件右邊的列（索引列上少計(jì)算）
4.盡量使用覆蓋索引，即是只訪問索引的查詢減少select *的用法
5.少使用（！=，<>,<,>） is not null ,is null;
6.like以 '%'開頭會(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全沒使用

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自動(dòng)做了優(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自動(dòng)做了優(yōu)化對語句排序范圍c之后索引才會(huì)失效
where b=3 and c=4 and a=2	Y :mysql自動(dòng)做了優(yōu)化對語句排序
where c=5 and b=4 and a=3	Y :mysql自動(dòng)做了優(yōu)化對語句排序

假設(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í)也用了但是是用在排序，沒有統(tǒng)計(jì)到mysql中
where a=3 and b=4 order by c	使用了a和b，c其實(shí)也用了但是是用在排序，沒有統(tǒng)計(jì)到mysql中
where a=3 and b=4 order by d	使用了a和b, 這里跳過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)樘^了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，不會(huì)產(chǎn)生using filesort 因?yàn)樵趯，b排序前對b進(jìn)行了查詢，查詢時(shí)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	排序不一致升降機(jī)

exsites

 select a.* from A a where exists(select 1 from B b where a.id=b.id)
 以上查詢使用了exists語句,exists()會(huì)執(zhí)行A.length次,它并不緩存exists()結(jié)果集,因?yàn)閑xists()結(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ù)大時(shí)適合使用exists(),因?yàn)樗鼪]有那么遍歷操作,只需要再執(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次,可見B表數(shù)據(jù)越多,越適合exists()發(fā)揮效果.  再如:A表有10000條記錄,B表有100條記錄,那么exists()還是執(zhí)行10000次,還不如使用in()遍歷10000*100次,因?yàn)閕n()是在內(nèi)存里遍歷比較,而exists()需要查詢數(shù)據(jù)庫,我們都知道查詢數(shù)據(jù)庫所消耗的性能更高,而內(nèi)存比較很快.

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

show VARIABLES like '%slow_query_log%';

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

set global slow_query_log=0;

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

set global slow_query_log=1;

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

show VARIABLES like '%long_query_time%';

顯示超過多長時(shí)間即為慢查詢

set global long_quert_time=10;

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

show global status like '%Slow_queries%';

顯示一共有幾條慢查詢語句

[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, #上級編號(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ù)過程要多次使用；所以改變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ǔ)過程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ǔ)過程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)

隨便寫幾條插入語句‘

顯示查詢操作語句的速度

 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生命周期

 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)化查詢語句

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

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

此后你編寫的sql語句將會(huì)記錄到mysql庫里的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鎖

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

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

 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)無法操其他表但是可以操作加上鎖的表

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

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

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

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

可以通過以下語句顯示給記錄集加共享鎖或排他鎖。

 共享鎖（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ù)具有四個(gè)屬性：ACID

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

并發(fā)事務(wù)帶來的問題：

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

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

并發(fā)事務(wù)處理帶來的問題

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

事務(wù)隔離級別

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

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

隔離

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。

相關(guān)文章

mysql學(xué)習(xí)筆記之表的基本操作

centos編譯安裝mysql 5.6及安裝多個(gè)mysql實(shí)例詳解

mysql 5.7.11 winx64.zip安裝配置方法圖文教程

mysql 5.7.17 winx64.zip安裝配置方法圖文教程

CentOS安裝mysql5.7 及簡單配置教程詳解

MySQL 5.7 zip版本(zip版)安裝配置步驟詳解

MySQL5.6.31 winx64.zip 安裝配置教程詳解

MySQL注入繞開過濾的技巧總結(jié)

一次Mysql死鎖排查過程的全紀(jì)錄

Windows10 64位安裝MySQL5.6.35的圖文教程