一刽锤、MySQL介紹

二、MySQL 安裝

1.安裝前注意事項

（1）確認SELinux 和 系統(tǒng)防火墻 iptables 需要關(guān)閉

SELinux

cat /etc/sysconfig/selinux

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:

#?enforcing - SELinux security policy is enforced.

#?permissive - SELinux prints warnings instead of enforcing.

#?disabled - SELinux is fully disabled.

SELINUX=disabled

# SELINUXTYPE= type of policy in use. Possible values are:

#?targeted - Only targeted network daemons are protected.

#?strict - Full SELinux protection. SELINUXTYPE=targeted

是關(guān)閉狀態(tài)

防火墻：

chkconfig --list|grep iptables iptables????????

0:off?1:off?2:off?3:off?4:off?5:off?6:off

chkconfig iptables off

（2）I/O調(diào)度系統(tǒng)

I/O調(diào)度模式默認是cfq的，建議用deadline模式（root用戶才有權(quán)限看）

cat /etc/grub.conf

在文件后面加：?elevator=deadline

（3）swap分區(qū)設(shè)置

swap分區(qū)簡介：

Swap分區(qū)在系統(tǒng)的物理內(nèi)存(這里應(yīng)該是運行內(nèi)存)不夠用的時候，把物理內(nèi)存中的一部分空間釋放出來呜达，以供當前運行的程序使用。那些被釋放的空間可能來自一些很長時間沒有什么操作的程序粟耻，這些被釋放的空間被臨時保存到Swap分區(qū)中查近，等到那些程序要運行時，再從Swap分區(qū)中恢復(fù)保存的數(shù)據(jù)到內(nèi)存中挤忙。

swappiness 至的范圍是0-100霜威，0代表最大限度使用物理內(nèi)存，然后才使用swap册烈，可能會導(dǎo)致系統(tǒng)內(nèi)存溢出戈泼，OMM錯誤；100代表積極使用swap分區(qū)赏僧，并及時把內(nèi)存數(shù)據(jù)搬到swap分區(qū)中（不建議）

一般不設(shè)置swap大猛，或者4G即可

配置swappiness：在? /etc/sysctl.conf 文件中加入? vm.swappiness = 60

（4）文件系統(tǒng)的選擇

建議使用xfs文件系統(tǒng)，相比ext4更方便管理淀零，支持動態(tài)擴容挽绩，刪除方便

（5）操作系統(tǒng)的限制

查看當前操作系統(tǒng)限制：ulimit -a

core file size????????? (blocks, -c) 0

data seg size?????????? (kbytes, -d) unlimited

scheduling priority???????????? (-e) 0

file size?????????????? (blocks, -f) unlimited

pending signals???????????????? (-i) 31381

max locked memory?????? (kbytes, -l) 64

max memory size???????? (kbytes, -m) unlimited

open files????????????????????? (-n) 65535

pipe size??????????? (512 bytes, -p) 8

POSIX message queues???? (bytes, -q) 819200

real-time priority????????????? (-r) 0

stack size????????????? (kbytes, -s) 10240

cpu time?????????????? (seconds, -t) unlimited

max user processes????????????? (-u) 4096

virtual memory????????? (kbytes, -v) unlimited

file locks????????????????????? (-x) unlimited

p24 - p40

三、MySQL體系結(jié)構(gòu)與存儲引擎

3.1 MySQL體系結(jié)構(gòu)包括兩層：

1.MySQL server層

（1）連接層

（2）SQL層

2.存儲引擎層—（區(qū)別于其他數(shù)據(jù)庫）

3.2 query cache

在生產(chǎn)中建議關(guān)閉驾中，因為他只緩存靜態(tài)數(shù)據(jù)信息唉堪；在數(shù)據(jù)倉庫可以考慮開啟。

兩個重要參數(shù)：

show variables like '%query_cache_size%';

| Variable_name??? | Value?? |

| query_cache_size | 1048576 |

show variables like '%query_cache_type%';

| Variable_name??? | Value |?

| query_cache_type | OFF?? |

MySQL壓力測試軟件 sysbench

下載地址：http://dev.mysql.com/downloads/benchmarks.html

3.3 存儲引擎

目前兩個主流存儲引擎是 InnoDB 和 MyISAM 兩種哀卫，MySQL 8.0將使用 InnoDB 存儲數(shù)據(jù)字典巨坊，目前默認也是 InnoDB

3.4 InnoDB 體系結(jié)構(gòu)

3.4.1

MySQL是單進程多線程的數(shù)據(jù)庫

包括下面三個部分

內(nèi)存結(jié)構(gòu) + 線程 + 磁盤文件

3.4.2 InnoDB 存儲結(jié)構(gòu)

主要分為：表空間、段此改、區(qū)趾撵、頁

（一）表空間：

（1）系統(tǒng)表空間

命名：ibdata1

安裝數(shù)據(jù)庫初始化時就會創(chuàng)建的，存儲所有數(shù)據(jù)信息和回滾段（undo）信息。

數(shù)據(jù)庫自動擴展占调，默認64MB

?show variables like '%auto%';

ibdata1 大小默認時1MB暂题，建議改為1GB

show variables like '%innodb_data%';?

| Variable_name???????? | Value??????????????????? |

| innodb_data_file_path | ibdata1:1024M:autoextend |

| innodb_data_home_dir? |????????????????????????? |

（2）獨立表空間

參數(shù)設(shè)置：innodb_file_oer_table=1

目前默認使用獨立表空間，即每個表都有自己的表空間文件究珊，不存放在系統(tǒng)表空間中薪者。

獨立表空間文件存儲對應(yīng)表的B+樹數(shù)據(jù)、索引和插入緩沖等信息剿涮，其余信息還存儲在默認表空間中言津。

回收表空間：alter table table_name engine=innodb; 或者 執(zhí)行：pt-online_schema_change

但是每個表都有.frm和.ibd文件兩個文件描述，如果表單增長過快就會出現(xiàn)性能問題取试。

（3）共享表空間

共享表空間不能在線回收表空間

回收方法：將全部InnoDB表的數(shù)據(jù)備份悬槽、刪除原表，然后把數(shù)據(jù)導(dǎo)回到與原表結(jié)構(gòu)一致的新表瞬浓。

統(tǒng)計分析類初婆、日志類系統(tǒng)不適合共享表空間

5.7版本后增加：

（4）臨時表空間（temporary tablespace）

show variables like '%temp%';

| Variable_name????????????? | Value???????????????? |

| avoid_temporal_upgrade???? | OFF?????????????????? |

| innodb_temp_data_file_path | ibtmp1:12M:autoextend |

| show_old_temporals???????? | OFF?????????????????? |

獨立表空間文件名：ibtmp1，默認大小12M

（5）通用表空間（general tablespace）

多個表放在同一個表空間中猿棉，根據(jù)活躍度來劃分表磅叛，存放在不同磁盤上，可以減少metadata的存儲開銷萨赁，但目前生產(chǎn)中使用較少

（二）段

表空間是由段組成弊琴，也可以把一個表理解為一個段。通常有數(shù)據(jù)段位迂，回滾段访雪，索引段等

一個段由：N個區(qū)和32個零散頁組成，段空間擴展是以區(qū)為單位進行擴展的

通常創(chuàng)建一個索引的同時會創(chuàng)建兩個段掂林，分別為非葉子節(jié)點和葉子節(jié)點段

一個表有四個段臣缀，是索引個數(shù)的2倍

（三）區(qū)

區(qū)是連續(xù)的頁組成，是物理上連續(xù)分配的一段空間泻帮，每個區(qū)固定大小是1M

（四）頁

InnoDB 的最小物理存儲分配單位是page精置，有數(shù)據(jù)頁，回滾頁等锣杂。一般情況下脂倦，一個區(qū)由64個連續(xù)頁組成，頁的默認大小是16KB

5.6版本開始可以調(diào)低page的大小元莫，如8KB赖阻，4KB；5.7版本開始可以調(diào)高page大小32KB踱蠢，64KB

一般情況下火欧，page頁會預(yù)留1/16空間用于更新數(shù)據(jù)棋电，真正使用的只有15/16

頁的結(jié)構(gòu)如圖，一個頁最少可以存兩行數(shù)據(jù)：虛擬最小行（infimum）和虛擬最大行（supremum）

（五）行

頁里面記錄行記錄的信息

InnoDB存儲引擎有兩種文件格式：

（1）Antelope?：包括compact 和 redundant 兩種行記錄格式

（2）Barracuda ：包括compressed 和 dynamic 兩種行記錄格式

5.7版本默認使用dynamic行記錄格式和Barracuda文件格式

show variables like '%row_format%';

| Variable_name???????????? | Value?? |?

| innodb_default_row_format | dynamic |

show variables like '%innodb_file_format%'????

| Variable_name??????????? | Value???? |

| innodb_file_format?????? | Barracuda |

| innodb_file_format_check | ON??????? |

| innodb_file_format_max?? | Barracuda |

3.4.3 內(nèi)存結(jié)構(gòu)

與Oracle類似苇侵，也分為SGA（系統(tǒng)全局區(qū)） 和 PGA（程序緩存區(qū)）

數(shù)據(jù)庫內(nèi)存分配參數(shù)查看：

show variables like '%buffer%';?

（一）SGA組成

1.innodb_buffer_pool

用來緩存InnoDB表的數(shù)據(jù)赶盔、索引、插入緩沖榆浓、數(shù)據(jù)字典等信息于未。

2.innodb_log_buffer

事務(wù)在內(nèi)存中的緩沖，即redo log buffer 的大小

3.Query Cache

高速查詢緩存陡鹃，在生產(chǎn)環(huán)境中建議關(guān)閉

4.key_buffer_size

只用于MyISAM存儲引擎表烘浦，緩存MyISAM存儲

引擎表的索引文件（區(qū)別于innodb_buffer_pool數(shù)據(jù)和索引都緩存）

5.innodb_additional_men_pool_size

用來保存數(shù)據(jù)字典信息和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池的大小，在5.7.4版本中被移除了

（二）PGA組成

1.sort_buffer_size

主要用于SQL語句在內(nèi)存中的臨時排序

2.join_buffer_size

表連接使用萍鲸，用于BKA谎倔。5.6版本之后開始支持

3.read_buffer_size

表順序掃面的緩存，只能應(yīng)用與MyISAM表存儲引擎

4.read_rnd_buffer_size

MySQL隨機讀緩沖區(qū)大小猿推，用于做mrr，mrr是5.6之后才有的特性

（三）特殊的

1.tmp_table_size

SQL語句在排序或分組時沒有用到索引捌肴，就會用臨時表空間

2.max_heap_table_size

管理heap蹬叭、memory存儲引擎表

show variables like '%heap%';

| Variable_name?????? | Value??? |

| max_heap_table_size | 16777216 |

show variables like '%tmp_table_size%';

| Variable_name? | Value??? |

| tmp_table_size | 16777216 |

一般生產(chǎn)環(huán)境把這兩個參數(shù)設(shè)置為一樣的，如果二者不一樣會按照其中小的值起作用

值太小會出現(xiàn)【converted heap to myisam】報錯

3.4.4 buffer 狀態(tài)及其鏈表結(jié)構(gòu)

page 是 InnoDB 磁盤 I/O 的最小單位状知，數(shù)據(jù)存放在page中秽五，對應(yīng)內(nèi)存中的一個個buffer，每個buffer分為三個狀態(tài)：

（1）free buffer ：未被使用

（2）clean buffer ：buffer數(shù)據(jù)與磁盤數(shù)據(jù)一致

（3）dirty buffer ：新寫入數(shù)據(jù)還未刷新到磁盤

InnoDB 是雙向鏈表結(jié)構(gòu)饥悴，由三種不同的buffer 狀態(tài)衍生三條鏈表：free list 坦喘、lru list 、flush list

3.4.5 各大刷新線程及其作用

1.master thread

后臺線程中的主線程西设，優(yōu)先級最高瓣铣。

內(nèi)部有四個循環(huán)：主循環(huán)loop、后臺循環(huán)background loop贷揽、刷新循環(huán)flush loop棠笑、暫停循環(huán)suspend loop

主循環(huán)包括每1s和每10s的操作：

每1秒操作：

（1）日志緩沖刷新到磁盤，即使這個事務(wù)還沒有提交

（2）刷新臟頁到磁盤

（3）執(zhí)行合并插入緩沖操作

（4）產(chǎn)生checkpoint

（5）清除無用的table cache

（6）如果當前沒有用戶活動禽绪，就切換到background loop

每10秒操作：

（1）日志緩沖刷新到磁盤蓖救，即使事務(wù)沒有提交

（2）執(zhí)行合并插入緩沖的操作

（3）刷新臟頁到磁盤

（4）刪除無用的undo頁

（5）產(chǎn)生checkpoint

以下是四大I/O線程

2.read thread

負責把日志緩沖中的內(nèi)容刷新到redo log 文件中

3.write thread

4.redo log thread

讀寫請求線程

5.change buffer thread

負責把插入緩沖（change buffer）中的內(nèi)容刷新到磁盤

6.page cleaner thread

負責臟頁刷新線程，5.7版本后可以增加多個

7.purge thread

負責刪除無用的undo頁

8.checkpoint線程

在redo log 發(fā)生切換時印屁，執(zhí)行checkpoint

3.4.6 內(nèi)存刷新機制

與Oracle類似循捺，日志先行策略署隘，即一條DML語句進入數(shù)據(jù)庫之后都會先寫日志推正，再寫數(shù)據(jù)文件

1.redo log

默認至少有兩個涂臣，磁盤上用ib_logfile(0~N)命名

順序?qū)懀h(huán)寫：第一個文件寫滿伴箩，寫第二個，直到寫道最后一個孝赫，又從第一個文件開始寫锣咒，寫滿日志文件會產(chǎn)生切換操作，并執(zhí)行checkpoint牺勾，觸發(fā)臟頁的刷新正罢。

2.binlog

DML操作既會寫redo log文件，也會寫binlog文件驻民，是數(shù)據(jù)庫的二進制文件翻具，主要用于備份恢復(fù)和主從賦值

只記錄改變信息，不記錄查詢

二者區(qū)別：

（1）記錄內(nèi)容不同：

binlog是邏輯日志回还，記錄所有數(shù)據(jù)的改變信息裆泳；redo log是物理日志，記錄的是所有InnoDB表數(shù)據(jù)的變化

（2）記錄內(nèi)容的時間不同

binlog記錄commit完畢的DML和DDL SQL語句柠硕；redo log 記錄事務(wù)發(fā)起之后的DML 和 DDL SQL

（3）文件使用方式不同

binlog不是循環(huán)使用工禾，在寫滿或?qū)嵗貑⒑髸a(chǎn)新的binlog文件；redo log是循環(huán)使用蝗柔，最后一個文件寫滿還會再寫第一個文件

（4）作用不同

binlog 可以做恢復(fù)數(shù)據(jù)使用闻葵，主從復(fù)制搭建；redo log 作為異常宕機或者介質(zhì)故障后的數(shù)據(jù)庫恢復(fù)使用

只要binlog寫入完成癣丧，那么主從復(fù)制環(huán)境就會正常完成事務(wù)

臟頁刷新的條件：

（1）重做日志ib_filelog 文件寫滿后槽畔，在切換過程中會執(zhí)行checkpoint，會觸發(fā)臟頁的刷新

（2）通過innodb_max_dirty_pages_pct 參數(shù)控制胁编，該參數(shù)是指在buffer pool中dirty page所占百分比厢钧，達到設(shè)置的值，就會觸發(fā)臟頁的刷新嬉橙。

show variables like '%innodb_max_dirty_pages_pct';?

| Variable_name????????????? | Value???? |

| innodb_max_dirty_pages_pct | 75.000000 |

默認是75%早直，生產(chǎn)環(huán)境建議25%~50%

（3）由innodb_adaptive_flushing參數(shù)控制，該參數(shù)影響每秒刷新臟頁的數(shù)目憎夷，替換了innodb_max_dirty_pages_pct 參數(shù)設(shè)置的值莽鸿，也會刷新一定數(shù)量的臟頁，默認開啟

show variables like 'innodb_adaptive_flushing';

| Variable_name??????????? | Value |

| innodb_adaptive_flushing | ON??? |

3.4.7 InnoDB 的三大特性

1.插入緩沖（change buffer）

先判斷插入的普通索引是都在緩沖池中拾给，如果在就直接插入祥得，不在就先放到change buffer中，然后進行change?buffer和普通索引的合并操作蒋得，可以將多個插入合并到一個操作中级及，提高普通索引是插入性能。

2.兩次寫（double write）

生成一個頁的副本额衙，如果實例宕機饮焦，頁壞了怕吴，先用副本把頁還原出來，再通過redo log進行恢復(fù)重做县踢。

3.自適應(yīng)哈希索引（adaptive hash index）

四转绷、數(shù)據(jù)庫文件

從數(shù)據(jù)庫層面劃分為：參數(shù)文件（my.cnf）錯誤日志（error log）、慢查詢?nèi)罩荆╯low log）硼啤、全量日志（general log）议经、二進制日志文件（binlog）、審計日志（audit log）谴返、中繼日志（relay log）煞肾、套接字文件（socket）、進程文件（pid）嗓袱、表結(jié)構(gòu)文件籍救。

從存儲引擎層面有：redo log 和 undo log 文件

4.1 參數(shù)文件

幾個重要參數(shù)：

1.innodb_buffer_pool

在主內(nèi)存中，用來緩存被訪問過的表和索引文件渠抹，使得常用數(shù)據(jù)可以直接在內(nèi)存中被處理蝙昙，從而提升處理速度。

建議在服務(wù)器只跑數(shù)據(jù)庫一個應(yīng)用的前提下梧却，該參數(shù)可設(shè)置為物理內(nèi)存的50% ~ 80%

2.innodb_buffer_pool_instance

默認值是1耸黑，MySQL5.6.6版本后可以調(diào)整為多個，表示innodb緩沖區(qū)可以劃分多個區(qū)域篮幢，可以提高并發(fā)性，避免高并發(fā)情況下为迈，出現(xiàn)內(nèi)存爭用問題三椿。設(shè)置完成后，每個緩沖區(qū)各自管理自己的數(shù)據(jù)葫辐，互不干涉搜锰。

注意：只有當innodb_buffer_pool 大于1G 時，生成的innodb_buffer_pool 多實例才生效耿战。

P56

4.2 參數(shù)類型

分為動態(tài)參數(shù)和靜態(tài)參數(shù)

1.動態(tài)參數(shù)

可以在線修改的參數(shù)蛋叼。

通過set global 或者 set session 兩個命令設(shè)置，global 代表全局剂陡，session是只針對當前會話狈涮。

2.靜態(tài)參數(shù)

無法在線修改的參數(shù)，只能通過改配置文件然后重啟數(shù)據(jù)庫才能使修改生效

4.3 錯誤日志文件（error log）

4.4 二進制日志文件（binary log）

show variables like '%binlog_format%';

| Variable_name | Value |

| binlog_format | ROW?? |

三種模式：

1.row：基于行變更情況記錄鸭栖，會記錄行變更前的樣子和行變更后的內(nèi)容歌馍，簡稱RBR，生產(chǎn)中建議使用row晕鹊。

2.statement：記錄的是一條完整的sql語句松却，

3.mixed：是5.1版本中row和statement的過渡暴浦，不建議使用

4.5 慢查詢?nèi)罩荆╯low log）

慢查詢?nèi)罩緯殉^參數(shù)long_query_time時間的所有SQL語句記錄進來。

推薦工具：percona-toolkit晓锻，

下載地址：https://www.percona.com/downloads/percona-toolkit/LATEST

通過以下命令生成慢SQL報告：

/usr/local/percona-toolkit-3.0.3/bin/pt-query-digest --since=24h /data/mysql/slow.log > query.log

4.6 全量日志（general log）

記錄MySQL數(shù)據(jù)庫所有操作：包括select和show歌焦，一般情況下不開啟，因為log會很大砚哆，個別情況可能臨時開啟独撇，用于故障檢測。

參數(shù)介紹：

show variables like '%log_output%';

| Variable_name | Value |

log_output??? | FILE? |

log_output：全局動態(tài)變量窟社，可取FILE TABLE NONE 三個值券勺，其中FILE可以方便按條件檢索，若指定NONE灿里，則即使開啟general_log 也不會記錄log关炼，若指定TABLE，則會在MySQL數(shù)據(jù)庫下創(chuàng)建一個general_log表匣吊。

該參數(shù)不僅影響general的存儲方式儒拂，也會影響slow log的存儲方式，建議使用FILE色鸳。

4.7 審計日志（audit log）

實時記錄網(wǎng)絡(luò)上數(shù)據(jù)庫活動社痛，對數(shù)據(jù)庫進行細粒度審計的合規(guī)性管理，對數(shù)據(jù)庫遭受到的風(fēng)險行為進行告警命雀，對攻擊進行阻斷蒜哀。

通過對用戶訪問數(shù)據(jù)庫行為的記錄、分析和匯報吏砂，用來幫助用戶事后生成合規(guī)報告撵儿、事故追溯更遠，同時加強內(nèi)外數(shù)據(jù)庫網(wǎng)絡(luò)行為記錄狐血，提高數(shù)據(jù)資產(chǎn)安全淀歇。

推薦審計插件：libaudit.plugin.so

下載地址：https://bintray.com/mcafee/mysql-audit-plugin/release/1.1.4-725#files

查看audit是否開啟

show variables like '%audit%';

Empty set (0.07 sec)

開啟audit

set global audit_json_file=1;

在MySQL目錄下會多出一個，mysql-audit.json 審計日志匈织。

4.8 中繼日志（relay log）

主從復(fù)制中浪默，從服務(wù)器上上的文件。從服務(wù)器I/O線程將主服務(wù)器的二進制日志讀取過來并記錄到從服務(wù)器本地文件（relay log）中缀匕，然后從服務(wù)器上的sql線程會讀取relay-log日志的內(nèi)容并應(yīng)用到從服務(wù)器上纳决。

4.9 Pid 文件

MySQL 是一個單線程，多進程模型的數(shù)據(jù)庫乡小，實例啟動完成后會將自己唯一的進程號記錄到自己的Pid文件中岳链。

pid文件存放在數(shù)據(jù)目錄下。命名規(guī)則是將主機名作為前綴劲件。

4.10 Socket 文件

mysql.sock 文件時服務(wù)器與本地客戶端進行通信的UNIX套接字文件掸哑。

4.11 表結(jié)構(gòu)文件

在MySQL 8.0之前约急，以.frm 結(jié)尾的文件為表結(jié)構(gòu)文件。

從MySQL8.0開始苗分，frm表的定義文件被消除掉厌蔽，把文件中的數(shù)據(jù)寫入了系統(tǒng)表空間中，通過innodb 存儲引擎摔癣，實現(xiàn)表的DDL語句操作的原子性（之前版本無法實現(xiàn)奴饮，如truncate無法回滾）

4.12 innodb 存儲引擎文件

兩種日志：redo + undo?

支持事務(wù)，支持MVCC多版本并發(fā)控制（undo+回滾段）

innodb_undo_tablespaces 參數(shù)代表undo tablespace 的個數(shù)择浊，默認0戴卜，一般最少2個琢岩，因為undo log的truncate是由purge協(xié)調(diào)線程發(fā)起担孔，為保證在線truncate 江锨，需要有一個undo log tablespace 能提供給用戶使用。

show variables like '%undo_tablespaces%';

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| innodb_undo_tablespaces | 0 |

+--------------------------+------------+

5.7版本后增加 innodb_max_undo_log_size 參數(shù)啄育，默認是1GB拌消，控制最大undo tablespace文件大小挑豌，超過該閾值，會觸發(fā)truncate undo logs浮毯，truncate后的undo logs 大小默認恢復(fù)為10MB泰鸡。

show variables like '%innodb_max_undo_log_size%';

+--------------------------+------------+

| Variable_name | Value |

+--------------------------+------------+

| innodb_max_undo_log_size | 1073741824 |

+--------------------------+------------+

5.7.5版本后盛龄，支持在線刪除無用undo logs 默認關(guān)閉的

show variables like '%innodb_undo_log_truncate%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_undo_log_truncate | OFF |

+--------------------------+-------+

5.7版本后還新增的參數(shù)：innodb_purge_rseg_truncate_frequency參數(shù)芳誓，用來控制回收undo log的頻率，默認128：表示purge undo 輪詢128次后匿值，進行一次undo 的truncate

show variables like '%innodb_purge_rseg_truncate_frequency%';

+--------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------+-------+

| innodb_purge_rseg_truncate_frequency | 128 |

+--------------------------------------+-------+

第五章 表

5.1 整形

常用int赂摆，最大可達42億钟些。

int(n)：n代表的顯示寬度政恍，不是多少位數(shù)达传。無論n是多少，都是占用4個字節(jié)

5.2 浮點型

包括：float（4字節(jié)）宗弯，double（8字節(jié)）搂妻，decimal（decimal（M,D）叽讳，如果M>D則為M+2，否則D+2字節(jié)）

常用的是decimal

四舍五入原則

5.3 時間類型

類型? ? ? ? ? ? ? ? ? ?大幸乩辍（字節(jié)）? ? ? 格式

DATE? ? ? ? ? ? ? ? ? 3? ? ? ? ? ? ? ? ? ? ? ? ?YYYY-MM-DD

TIME? ? ? ? ? ? ? ? ? ?3? ? ? ? ? ? ? ? ? ? ? ? ? HH:MM:SS

YEAR? ? ? ? ? ? ? ? ? 1? ? ? ? ? ? ? ? ? ? ? ? ? YYYY

DATETIME? ? ? ? ? ?8/5? ? ? ? ? ? ? ? ? ? ? YYYY-MM-DD HH:MM:SS

TIMESTAMP? ? ? ? ?4? ? ? ? ? ? ? ? ? ? ? ? ?YYYYMMDDHHMMSS

說明：DATETIME 在5.6版本前占用8個字節(jié)涤妒，之后占用5個字節(jié)，較常用

可以通過兩個函數(shù)轉(zhuǎn)換而來：unix_timestamp硅堆，from_unixtime

select unix_timestamp('2020-01-13 15:46:00');

+---------------------------------------+

| unix_timestamp('2020-01-13 15:46:00') |

+---------------------------------------+

| 1578901560 |

+---------------------------------------+

select from_unixtime(1578901560);

+---------------------------+

| from_unixtime(1578901560) |

+---------------------------+

| 2020-01-13 15:46:00 |

+---------------------------+

5.4 字符串類型

char? varchar??

blob? text：存大量文字或圖片的大數(shù)據(jù)類型贿讹，建議不要與業(yè)務(wù)表放在一起民褂，主要業(yè)務(wù)切忌出現(xiàn)

說明：存儲IP建議使用int，可以使用函數(shù)：inet_aton 和 inet_ntoa 進行轉(zhuǎn)換

select inet_aton('192.168.56.102');

+-----------------------------+

| inet_aton('192.168.56.102') |

+-----------------------------+

| 3232249958 |

+-----------------------------+

select inet_ntoa(3232249958);

+-----------------------+

| inet_ntoa(3232249958) |

+-----------------------+

| 192.168.56.102 |

+-----------------------+

5.5 字符集

常用字符集：GBK（占用2字節(jié)）面殖、Latin1(5.0,5.1中的默認字符集哭廉，目前不使用，占用1字節(jié))辽幌、UTF8（占用3字節(jié)）、UTF8mb4（是UTF8的超集埠通，占4字節(jié)）

保證以下三點統(tǒng)一就不會出現(xiàn)亂碼逛犹。

（1）連接終端的字符集：UTF8

（2）操作系統(tǒng)的字符集：UTF8

查看：

cat /etc/sysconfig/i18n

LANG="en_US.UTF-8"

SYSFONT="latarcyrheb-sun16"

（3）數(shù)據(jù)庫的字符集：UTF8（針對5.7版本建議使用：UTF8mb4）

查看

(root@localhost) [(none)]> \s;

或

show variables like '%char%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

+--------------------------+----------------------------------+

5.6 表碎片產(chǎn)生原因

刪除數(shù)據(jù)導(dǎo)致

5.7 碎片計算方式和整理過程

1.計算碎片

show table status like '%testclob%'\G;

*************************** 1. row ***************************

Name: testclob

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 61791

Avg_row_length: 42

Data_length: 2637824

Max_data_length: 0

Index_length: 0

Data_free: 4194304

Auto_increment: 65544

Create_time: 2019-12-26 15:48:01

Update_time: 2020-01-13 16:13:48

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

碎片大小 = 數(shù)據(jù)總大小 - 實際表空間文件大小

數(shù)據(jù)總大小 = data_length + index_length =?2637824 + 0 =?2637824

實際表空間文件大小 = rows * avg_row_length =?61791 *?42 =?2595222

碎片大小 = （2637824 -??2595222）/1024/1024 = 0.04M

2.清除碎片的兩種方法：

（1）alter table table_name engine = innodb;

優(yōu)點：整理全表數(shù)據(jù)虽画，整理好后數(shù)據(jù)連續(xù)性好码撰，全表掃描快，表空間文件變小朵栖，節(jié)約磁盤空間柴梆，清除碎片

缺點：需要先給整表加一個寫鎖，耗時較長门扇，不建議在業(yè)務(wù)高峰使用

（2）備份原表數(shù)據(jù)偿渡，然后刪掉溜宽，重新導(dǎo)入到新表中（與原表結(jié)構(gòu)一樣）--備份恢復(fù)會用到

推薦使用percona-toolkit 工具集，使用pt-query-digest 命令捕獲慢SQL留攒，查看慢查詢?nèi)罩尽?/p>

pt-online-schema-change：可以整理表結(jié)構(gòu)涡扼、收集碎片盟庞、給大表添加字段和索引，避免出現(xiàn)鎖表掃之讀寫操作票彪，5.7版本不需要使用這個命令，可以直接在線：online DDL

5.8 表統(tǒng)計信息

1.統(tǒng)計每個庫大性诤怠：

select table_schema,sum(data_length)/1024/1024/1024 as data_length,sum(index_length)/1024/1024/1024 as index_length,sum(data_length+index_length)/1024/1024/1024 as sum_data_index from information_schema.tables where table_schema?!='information_schema' and table_schema?!= 'mysql' group by table_schema;

+--------------------+----------------+----------------+----------------+

| table_schema | data_length | index_length | sum_data_index |

+--------------------+----------------+----------------+----------------+

| performance_schema | 0.000000000000 | 0.000000000000 | 0.000000000000 |

| sys | 0.000015258789 | 0.000000000000 | 0.000015258789 |

| test | 0.001876831055 | 0.000000000000 | 0.001876831055 |

| test1 | 0.000015258789 | 0.000000000000 | 0.000015258789 |

| test2 | 0.000015258789 | 0.000000000000 | 0.000015258789 |

| test_ljing | 0.002456665039 | 0.000000000000 | 0.002456665039 |

+--------------------+----------------+----------------+----------------+

5.9 統(tǒng)計信息的收集方法

1.遍歷information_schema.tables 表

select * from information_schema.tables where table_name = 'test_ljing'\G;

2.重啟MySQL實例

3.show table status like '%table_name%';

5.10 MySQL庫表常用命令總結(jié)

SHOW TABLE STATUS;? ? ?獲取表基礎(chǔ)信息

SHOW INDEX FROM TABLE_NAME;? 查看當前表下索引情況

SHOW FULL PROCESSLIST;? ? ? ?查看數(shù)據(jù)庫當前連接情況

第六章 索引

主要索引：B+tree 索引? 和? ?哈希索引

B+tree 是由：二叉樹?→ 平衡二叉樹?→ B-tree 演化來的

6.1 二叉樹結(jié)構(gòu)

每個節(jié)點至多有兩個子節(jié)點桶蝎，有左右序之分谅畅，次序不能顛倒毡泻。左子樹的值要比右子樹小，并且小于根鍵值

6.2 平衡二叉樹結(jié)構(gòu)

左右兩個子樹的高度差絕對值不超過1呻顽，保證插入后的整顆二叉樹是平衡的丹墨，通過左旋或者右旋使不平衡的樹變平衡

6.3 B-tree 結(jié)構(gòu)

也稱Btree，Btree和B+tree并不一樣昧碉。

最多子節(jié)點數(shù)變成4個揽惹，可以理解為四階的B樹結(jié)構(gòu)搪搏，樹中每個節(jié)點最多含有4個子節(jié)點，除根節(jié)點和葉子節(jié)點论颅，其他節(jié)點至少有2個子節(jié)點

特別說明：所有葉子節(jié)點都出現(xiàn)在同一層囱嫩，葉子節(jié)點不包括任何關(guān)鍵字信息。

6.4 B+tree

是Btree的變體今妄，也是一種多路搜索樹，

與Btree的區(qū)別：所有關(guān)鍵字信息都在葉子節(jié)點層犬性，并且包含這些關(guān)鍵字記錄的指針腾仅。葉子節(jié)點可以按照關(guān)鍵字的大小順序鏈接，他所有數(shù)據(jù)都保存在葉子節(jié)點中鹤耍。

特別說明：B+tree索引是雙向鏈表結(jié)構(gòu)惰蜜，而且用B+tree結(jié)構(gòu)做檢索會比B-tree快受神，訪問關(guān)鍵字的順序是連續(xù)的，不用再訪問上一個節(jié)點财著，而且葉子節(jié)點包含所有數(shù)據(jù)信息

6.4.1 聚集索引和普通索引

聚集索引是一種索引組織形式撑碴，其邏輯順序決定了表數(shù)據(jù)行的物理存儲順序醉拓。葉子節(jié)點存檔表中所有行數(shù)據(jù)記錄的信息。

我們在創(chuàng)建一張表的時候愤兵，要顯示地創(chuàng)建一個主鍵（聚集索引）排吴，如果不主動創(chuàng)建，那么Innodb會選擇第一個不包含null值的唯一索引作為主鍵屹堰，如果沒有唯一索引街氢，就會默認生成一個6個字節(jié)的roeid作為主鍵珊肃。

普通索引：在葉子節(jié)點并不包含所有行的數(shù)據(jù)記錄扣泊，只是會在葉子節(jié)點存有本身的鍵值和主鍵的值嘶摊。在檢索數(shù)據(jù)時叶堆，通過普通索引葉子節(jié)點上的主鍵來獲取想要查找的行的數(shù)據(jù)記錄斥杜。

語法：alter table table_name add index index_name(column);

或者：create index index_name on table_name(column)。

查詢執(zhí)行計劃：

explain select * from testclob WHERE NAME = 'll'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: testclob

partitions: NULL

type: ALL? ?(代表全表掃描)

possible_keys: NULL

key: NULL （代表沒有使用索引）

key_len: NULL

ref: NULL

rows: 45024（row越大忘渔，需要掃描的行數(shù)就越多）

filtered: 10.00（返回行數(shù)占讀取行數(shù)的百分比）

Extra: Using where

SQL優(yōu)化問題的正確思路：

（1）看表的數(shù)據(jù)類型設(shè)計是否合理畦粮，遵循數(shù)據(jù)類型越簡單越小原理

（2）表中碎片是否整理

（3）表的統(tǒng)計信息是否收集乖阵，統(tǒng)計信息準確瞪浸，執(zhí)行計劃才能幫助我們優(yōu)化SQL

（4）查看執(zhí)行計劃，檢查索引使用情況对蒲，沒有用到索引蹈矮，考慮創(chuàng)建

（5）創(chuàng)建前：看索引的選擇性；指：不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值诱渤，選擇性越高谈况，查詢效率越高

如：name列的選擇性：select distinct(name)/count(*) from testclob;

+--------------------------------+

| count(distinct(name))/count(*) |

+--------------------------------+

| 0.0001 |

+--------------------------------+

選擇性很低碑韵，試試創(chuàng)建索引：create index idx_name on testclob(name);

（6）創(chuàng)建索引后再查看下執(zhí)行計劃，對比兩次結(jié)果占卧，看是否有所提高

explain select * from testclob WHERE NAME = testclo 'll'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: testclob

partitions: NULL

type: ref（使用了索引）

possible_keys: ind_name（使用索引名稱）

key: ind_name

key_len: 63

ref: const

rows: 4096（需要掃描的行，較之前減少了辙纬，已經(jīng)不是全表掃描了）

filtered: 100.00（返回行數(shù)占讀取行數(shù)的百分比）

Extra: NULL

合理創(chuàng)建索引參考：

（1）經(jīng)常被查詢的列（where后面的列）

（2）經(jīng)常用于表連接的列

（3）經(jīng)常排序分組的列（order by 或group by 后面的列）

6.4.2 ICP贺拣、MRR捂蕴、BKA

（1）ICP（Index Condition Pushdown）是MySQL使用索引從表中檢索行數(shù)據(jù)的一種優(yōu)化方式。

通過optimizer_switch參數(shù)中的index_condition_pushdown選項控制涡匀，默認開啟

show variables like '%optimizer_switch%'\G;

*************************** 1. row ***************************

Variable_name: optimizer_switch

Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

當使用ICP優(yōu)化時陨瘩，執(zhí)行計劃的extra列會顯示using index condition

修改：set optimizer_switch="index_condition_pushdown=on|off";

（2）MRR（Multi-Range Read Optimization）

也是通過optimizer_switch參數(shù)中兩個重要參數(shù)控制的

默認的：mrr=on,mrr_cost_based=on

修改：set global optimizer_switch='mrr=on|off,mrr_cost_based=on|off';

原理：對于索引列存在重復(fù)值的情況：MRR把普通索引的葉子節(jié)點上找到的主鍵值的集合存儲到read_rnd_buffer中级乍，然后在該buffer中對主鍵值進行排序卡者，然后再利用排好序的主鍵值的集合去訪問表中的數(shù)據(jù)，這樣原來的隨機I/O就會變成順序I/O材诽，降低查詢中的I/O開銷

（3）BAK（Batched Key Access）

提高表join性能的算法恒傻，作用是在讀取被join表的記錄的時候使用順序I/O

通過optimizer_switch 參數(shù)中的batched_key_access=off 選項控制的盈厘，默認關(guān)閉

修改：必須保證MRR開啟的情況下才能開啟BKA

set global optimizer_switch = 'mrr=on,mrr_cost_based=off';

set global optimizer_switch = 'batched_key_access=on';

原理：對于多表join情況，當MySQL使用索引訪問第二個join表時外遇，使用join buffer 來手機第一個操作對象生成的相關(guān)列值契吉，BKA構(gòu)建好key后，批量傳給引擎層做索引查找菲语。key是通過MRR接口提交給引擎的山上，這樣MRR使得查詢更加高效。

6.4.3 主鍵索引和唯一索引

主鍵索引：聚集索引畔塔，只有一個主鍵鸯屿，由表的一個或多個字段組成把敢。

滿足條件：主鍵值唯一修赞；不包含null；保證該值是自增屬性（提高存取效率）

創(chuàng)建：alter table table_name add primary key(column);

唯一索引：不重復(fù)勾邦；允許null割择；可以多個

創(chuàng)建：alter table table_name add unique(column);

6.4.4 覆蓋索引

id是主鍵索引荔泳，name是普通索引蕉饼，普通索引包含主鍵的值昧港，

查詢：select id from testclob where name = 'll';

相當于（name,id）索引支子，即覆蓋索引

注意：使用覆蓋索引不能用select * 值朋，要列出所需的列

6.4.5 前綴索引

對于BLOB、TEXT或者很長的VARCHAR類型的列圈膏，可以為他們前幾個字符創(chuàng)建索引篙骡。叫前綴索引丈甸。

注意：前綴索引不能在order by 或 group by中使用睦擂，也不能做覆蓋索引

創(chuàng)建：alter table table_name add key(column_name(prefix_length));

6.4.6 聯(lián)合索引

也叫復(fù)合索引杖玲，即在表中的兩個或兩個以上的列上建立索引摆马。

注意：創(chuàng)建時把選擇性高的列放在前面，使用中必須滿足最左前綴原則

創(chuàng)建：create index ind_c1_c2 on t(c1,c2);

6.5 哈希索引

使用哈希算法述呐，把鍵值轉(zhuǎn)換成新的哈希值蕉毯。

注意：哈希值只能進行等值查詢代虾，不能進行排序，模糊查詢江掩，范圍查詢等

檢索時：不需要從根節(jié)點到葉子節(jié)點逐級查找乘瓤，只需一次哈希算法即可定位相應(yīng)位置馅扣，速度很快

6.6 索引總結(jié)

創(chuàng)建索引的四個不要：

1.選擇性低的字段不要創(chuàng)建

2.很少查詢的列不要創(chuàng)建

3.大數(shù)據(jù)類型字段不要創(chuàng)建

4.盡量避免使用null

使用不到索引的情況：

1.通過索引掃描行記錄超過全表30%，會變成全表掃描

2.聯(lián)合索引中第一個查詢條件不是最左索引列/最左前綴列

3.聯(lián)合索引第一個索引列使用范圍查詢拗军，只使用部分索引（范圍查詢包括：<发侵、=妆偏、<=、between and）

4.模糊查詢最左以通配符%開始

5.兩個單列索引叔锐，一個用于檢索愉烙，一個用于排序，之中情況下只能使用一個索引返顺，因為查詢中最多只能使用一個索引蔓肯，可以考慮建立聯(lián)合索引

6.查詢字段上面有索引蔗包，但是使用了函數(shù)運算

第七章 事務(wù)

7.1事務(wù)的特性

原子性（要么都做，要么都不做）

一致性（轉(zhuǎn)賬前后總金額一致）

隔離性（防止多個事務(wù)交叉執(zhí)行，造成數(shù)據(jù)不一致）

持久性（修改是永久的）

7.2 事務(wù)語句

show variables like '%autocommit%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

默認開啟自動提交旧噪，關(guān)閉：set autocommit=0脓匿；不建議關(guān)閉自動提交模式

隱式提交：DDL語句陪毡；再次輸入begin 或 start transaction

隱式回滾：退出會話、連接超時铁瞒、關(guān)機

7.3 truncate 和 delete

truncate：清空后自增序列從1開始

delete：清空后自增序列繼續(xù)之前的值開始

7.4 事務(wù)隔離級別

默認隔離級別是：REPEATABLE-READ

show variables like '%tx_isolation%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

（1）讀未提交（read uncommitted）RU--臟讀

（2）讀已提交（read committed）RC -----也叫不可重復(fù)讀慧耍，允許幻讀丐谋，是Oracle的默認隔離級別

（3）可重復(fù)讀（repeatable read）RR-------MySQL默認隔離級別号俐，避免臟讀，不可重復(fù)讀踪危，幻讀

（4）串行（serializable）---------------------讀：加表共享鎖；寫：加表排他鎖敛滋；會造成innodb并發(fā)能力下降兴革，大量超時和鎖競爭會發(fā)生杂曲，不建議生產(chǎn)環(huán)境使用

7.5 臟讀、不可重復(fù)讀咱揍、幻讀棚饵、可重復(fù)讀

7.5.1 臟讀

讀取其他事務(wù)未提交的數(shù)據(jù)

7.5.2 不可重復(fù)讀與幻讀

不可重復(fù)讀：讀取其他事務(wù)針對舊數(shù)據(jù)的修改記錄（常見update delete操作）

幻讀：讀取其他事務(wù)新增的數(shù)據(jù)（常見insert操作）

7.5.3 可重復(fù)讀

可以避免臟讀噪漾，幻讀，不可重復(fù)讀

用于對事務(wù)要求較高的數(shù)據(jù)庫系統(tǒng)题翰，如：電子交易類網(wǎng)站

第八章 鎖

不同存儲引擎支持不同鎖機制豹障，innodb支持行鎖焦匈，又是也會升級為表鎖，myisam 只支持表鎖坞笙。

表鎖：開銷小薛夜、加鎖快版述；不會出現(xiàn)思索；鎖粒度大晚伙，發(fā)生鎖沖突的概率高咆疗，并發(fā)度相對低

行鎖：開銷大、加鎖慢尝抖；會出現(xiàn)死鎖迅皇；鎖粒度小登颓，發(fā)生鎖沖突的概率低，并發(fā)度相對高

8.1 InnoDB 的鎖類型

讀鎖（共享鎖）咕痛、寫鎖（排他鎖）茉贡、意向鎖婉称、MDL鎖

8.1.1 讀鎖

簡稱S鎖构蹬，一個事務(wù)獲取一個行的讀鎖庄敛，其他事務(wù)也可以獲得該行對應(yīng)的讀鎖，但不能獲得寫鎖绷雏。

即：一個事務(wù)在讀取數(shù)據(jù)行時涎显，iqta事務(wù)也可以讀兴猩，但是不能對該數(shù)據(jù)行進行增刪改操作

包括兩種：

1）自動提交的select查詢語句倾芝，不加任何鎖

2）select... lock in share mode 在被讀取的行記錄或行記錄的范圍加上一個讀鎖箭跳，讓其他事務(wù)可以讀谱姓，但是如果想申請寫鎖刨晴，就會被阻塞

8.1.2 寫鎖

簡稱X鎖割捅，一個事務(wù)獲取了一個數(shù)據(jù)行的寫鎖，其他事務(wù)就不能再獲取該行的其他鎖嘹黔，寫鎖優(yōu)先級最高儡蔓。

DML操作都會對行記錄加寫鎖疼邀。

select for update 旁振，也會對讀取的行記錄加寫鎖

8.1.3 MDL 鎖

MySQL 5.5版本開始引入，全稱：meta data lock 吉嚣，用于保證表中元數(shù)據(jù)的信息蹬铺。

會話A中表開啟查詢事務(wù)后甜攀，會自動獲得一個MDL鎖，會話B就不剋有執(zhí)行任何DDL操作恒序。

8.1.4 意向鎖

是表級鎖歧胁。分為兩種：

1）意向共享鎖（IS）：在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖

2）意向排他鎖（IX）：在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖

作用類似于MDL鎖：防止在事務(wù)進行過程中，執(zhí)行DDL語句導(dǎo)致數(shù)據(jù)不一致

8.2 InnoDB 行鎖種類

在默認隔離級別為RR（repeatable read）了赌，且參數(shù)innodb_locks_unsafe_for_binlog=0 模式下勿她，行鎖有三種：

單個行記錄鎖（record lock）

間隙鎖（GAP Lock）

記錄鎖和間隙鎖的組合叫next-key lock

說明：普通索引默認就是：next-key lock 模式

8.2.1 單個行記錄的鎖

更新同一行數(shù)據(jù)會出現(xiàn)鎖等待的現(xiàn)象阵翎。

實驗發(fā)現(xiàn)：InnoDB的行鎖是加在索引項上面的郭卫，

當更新某個沒有索引的字段時（where score=60），會把所有行記錄都上鎖玻蝌，所以在其他會話更新其他行（where score=20）時俯树，也會報鎖超時的錯誤贰盗。

前提是：set autocommit = off;

查詢某個表上所有索引：

show index from testclob\G;

*************************** 1. row ***************************

Table: testclob

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 45024

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

刪除索引：drop index ind_name on testclob;

8.2.2 間隙鎖（Gap lock）

在RR隔離級別舵盈，為避免幻讀秽晚，引入Gap lock，但是他只鎖定行記錄數(shù)據(jù)范圍狸页，不包含記錄本身扯再，即不允許在此范圍內(nèi)插入任何數(shù)據(jù)

RC（read committed）隔離級別下熄阻，允許幻讀

8.2.3 Next-key Locks

是記錄鎖（Record Lock）和間隙鎖（Gap Lock）的組合秃殉，當InnoDB掃描索引記錄時，會先對選中的索引記錄加上記錄鎖（Record Lock）鳄袍，再對索引記錄兩邊的間隙加上間隙鎖（Gap Lock）瞎嬉。

例：

會話A：select? * from testclob where name = 'ljing' for update;

會話B：insert into test(name) values ('ljing');

會出現(xiàn)鎖超時。

8.3 鎖等待和死鎖

鎖等待：在事務(wù)過程中產(chǎn)生的鎖，其他事務(wù)要等待上一個事務(wù)釋放他的鎖搅幅，才能占用該資源茄唐。超過鎖等待時間就會報錯：等待超時。MySQL通過參數(shù) innodb_lock_wait_timeput 參數(shù)控制界牡，單位秒宿亡，默認100纳令。

show variables like '%innodb_lock_wait_timeout%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_lock_wait_timeout | 100 |

+--------------------------+-------+

死鎖：指兩個或兩個以上的進程在執(zhí)行過程中平绩，因爭奪資源造成的互相等待的現(xiàn)象捏雌，就是所謂的鎖資源請求產(chǎn)生回路現(xiàn)象，即死循環(huán)纬傲。常見報錯：Deadlock found when trying to get lock;try restarting transaction

查看死鎖：show engine innodb status;

避免死鎖的方法：

（1）如果不同程序會并發(fā)存取多個表叹括，或者涉及多行記錄時宵荒，盡量約定以相同順序訪問表

（2）業(yè)務(wù)中盡量采用小事務(wù)，避免使用大事務(wù)侠讯，及時提交或者回滾事務(wù)厢漩，可以減少死鎖產(chǎn)生的概率

（3）在同一個事務(wù)中袁翁，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率

（4）對于非常容易死鎖的業(yè)務(wù)柄驻，可以嘗試使用升級鎖粒度鸿脓，通過表鎖定減少死鎖概率

8.4 鎖問題的監(jiān)控

判斷事務(wù)中鎖問題情況：

show full processlist;

show engine innodb status;

另外還有三張比較重要的表：

information_schema 下的：innodb_trx涯曲、innodb_locks幻件、innodb_lock_waits