一條查詢語句是怎么執(zhí)行的？

一條更新語句是怎么運行的?

什么是事務(wù)?什么是長事務(wù)?如何避免長事務(wù)?

如何啟動一個事務(wù)?

mysql的隔離性和隔離級別?

什么是臟讀，幻讀，不可重復(fù)讀?如何避免?

mysql的鎖類型?

什么是死鎖？msyql是如何解決死鎖的？

mysql存儲引擎的種類，以及他們的區(qū)別？使用場景？

如何查看表狀態(tài)，如表大小，行數(shù)，索引大小，行格式...

什么是mysql基準測試

mysql基準測試的類型，不同的類型有哪些測試工具

基準測試完成后需要看那些指標

如何設(shè)計一個基準測試？并得到正確的結(jié)果，并繪制圖形

如何使用sysbench對mysql進行壓測

mysql基本框架.png

1.連接器：

負責連接客戶端，客戶端連接服務(wù)器，服務(wù)器都會分配給客戶端一個線程，客戶端斷開時，服務(wù)器端不會銷毀該線程，會校驗用戶名和密碼。若正確，則連接成功，同時查詢該客戶端是否具有對哪張表的權(quán)限。

2.查詢緩存

執(zhí)行完成后，會將該select語句緩存起來，value就是該語句的查詢結(jié)果，當下一次一模一樣的selec語句是，查詢緩存會直接緩存，不會走解析器和優(yōu)化器步驟，但是，不建議使用，查詢緩存的失效范圍很廣，一旦表中的數(shù)據(jù)由更新，緩存就會失效。

3.解析器

對sql語句進行解析，負責執(zhí)行語句的詞法解析和語法解析，對輸入的sql語句是否滿足語法。

4.優(yōu)化器

一個mysql語句，會有多個執(zhí)行步驟，效率也不會相同,選擇最優(yōu)的mysql。如

select * from t1 left join t2 on t1.id = t2.id where t1.name ='xxx' and t2.distance ='xxx'

選擇1:先查詢t1表的name 然后關(guān)聯(lián)t2表，在查詢t2.name ='xxx'
選擇2:先查詢t2表的name然后關(guān)聯(lián)t1表，在查詢t1.name ='xxx'
優(yōu)化器包括重寫查詢，決定表的順序，選擇合適的索引.

5.執(zhí)行器

執(zhí)行該select語句，執(zhí)行之前，校驗該用戶是否擁有對該表的權(quán)限，如果有，則會從第一行查詢，如果有就返回，直到行末尾。

以上是對select語句執(zhí)行過程的解析

那么更新語句呢？

mysql> update T set c=c+1 where ID=2;

更新語句稍微有點不同？但是mysql的架構(gòu)是不變的.
1.客戶端連接，連接器負責連接客戶端。
2.解析器，負責詞法語法解析
3.更新語句執(zhí)行;mysql更新語句時，寫入到redo log中，并更新內(nèi)存數(shù)據(jù)，此時如果更新成功就返回，代表update更新成功了。等待mysql不那么忙的時候，在將臟數(shù)據(jù)刷新到磁盤上。也稱為WAL技術(shù)(預(yù)寫式日志技術(shù))。

redo log和bing log的差異和不同

1.redo log是innodb 特有的，bing log是server特有的
2.redo log 是固定大小的一共有4G，一共4頁，每頁1G,環(huán)狀數(shù)據(jù)結(jié)構(gòu)，追加寫的，當寫完后，會從頭開始。bing log追加寫，寫完后寫入下一頁。
3.redo log 是邏輯的 記錄數(shù)據(jù)頁做了什么修改，binglog是物理了，記錄了給id=2的這條數(shù)據(jù)c加1；

那么Mysql為什么會隨時隨地恢復(fù)數(shù)據(jù)呢？正因為有了redo log和bing log。

redo log和bing log通過兩階段提交，保證了數(shù)據(jù)的安全性。
下面分析一下上面update語句的執(zhí)行流程：
1.msyql用存儲引擎找id =2 這一行，如果內(nèi)存中有該頁，將直接返回，如果沒有將從磁盤中加載到內(nèi)存中，然后返回。
2。執(zhí)行器，拿到存儲引擎給的值，對c進行加1操作。同時調(diào)用存儲引擎API接口寫入這行數(shù)據(jù)。
3.引擎將這行數(shù)據(jù)寫入到redo log中，然后更新內(nèi)存，此時redo log處于prepare階段；同時告訴執(zhí)行器，更新成功
3.執(zhí)行器，執(zhí)行這個操作，并且生成binglog日志，將binglog寫入磁盤
4.執(zhí)行器調(diào)用存儲引擎API接口，將redo log改成commit，運行這個sql語句。

update語句的更新流程.png

假設(shè)原來c的字段值為0
如果先寫redo log 后寫bing log 會有什么問題忙？
1.寫完redo log 如果mysql 異常重啟，由于寫入了，redo log，mysql仍然講數(shù)據(jù)恢復(fù)回來，所以恢復(fù)c列為1。
由于沒有寫入bing log ，用Binglog日志恢復(fù)mysql，那么bing log日志是丟失這條更新語句的，那么恢復(fù)出來的值是0和原來的庫中的數(shù)據(jù)不同。
2.先寫bing log 后寫redo log
如果寫完bing log mysql異常重啟，由于沒有寫入redo log，mysql講原來的c列恢復(fù)為0。
但是由于寫入了bing log日志，用Bing log日志寫入了 這條更新語句，那么恢復(fù)出來的c列的值為1 ，那么與原來的庫不同。

總結(jié):mysql 通過兩階段提交，保證了事務(wù)執(zhí)行的邏輯性，事務(wù)的邏輯狀態(tài)保持一致。

什么是事務(wù)？長事務(wù)？如何查詢長事務(wù)？長時間由于沒有提交的事務(wù)。
事務(wù)：是一組具有原子性的sql語句單元，要么全都執(zhí)行，要么要都不執(zhí)行。
事務(wù)的啟動方式有:

• begin....commit/rollback
• 設(shè)置mysql 的autocommit = 1 mysql會自動提交一個事務(wù)
• begin.....commit work and chain語法 mysql會自動提交一個事務(wù) ，同時開啟下一個事務(wù)

如果設(shè)置autocommit = 0，mysql不會自動提交事務(wù)，會導(dǎo)致長事務(wù)的方式，建議將配置文件的autocommit設(shè)置為1，

mysql的隔離性:

• A原子性：整個事務(wù)的sql語句，要么要都提交，要么全都不提交
• C一致性：是指數(shù)據(jù)庫狀態(tài)的一致性，從一個狀態(tài)到另一個狀態(tài)的改變
• I隔離性：事務(wù)提交之前，修改的結(jié)果對其他事務(wù)不可見
• D持久性，事務(wù)語句提交后，持久到磁盤上，不會因為崩潰而導(dǎo)致數(shù)據(jù)丟失

mysql的隔離級別:

• 讀未提交:事務(wù)還沒提交，修改的值就會被其他事務(wù)讀取到，會導(dǎo)致臟讀
• 讀以提交:當前事務(wù)讀取以提交事務(wù)的數(shù)據(jù),即一個事務(wù)讀取兩次，查看到的結(jié)果是不一樣的。會導(dǎo)致 不可重復(fù)讀
• 可重復(fù)讀:事務(wù)開啟的時候，會創(chuàng)建相當于一個快照，事務(wù)創(chuàng)建的時候，此時數(shù)據(jù)的列值，就已經(jīng)確定了。 一個事務(wù)執(zhí)行過程中看到的數(shù)據(jù)，總是跟這個事務(wù)在啟動時看到的數(shù)據(jù)是一致的
• 串行化:顧名思義 串行讀取，讀會加讀鎖，寫會加寫鎖，整個sql語句的執(zhí)行是串行執(zhí)行。讀寫沖突時，后開啟的事務(wù)會阻塞，必須等到前面的事務(wù)提交之后，才會提交。

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

image.png

上面這兩個事務(wù)，不同的隔離級別，所讀取到v1 v2和v3的值是不一樣的

• 讀未提交級別下 v1 v2 v3都是2 。
• 讀已提交級別下 v1是1 ，v2是2，v3是2 由于b提交了事務(wù)，所有v2讀取到的是事務(wù)B提交過的數(shù)據(jù)。
• 可重復(fù)讀級別下 V1和V2都是1 ,V3是2.由于事務(wù)A啟動時就c得值以及確定了，所以v1和V2讀取的值是1。
• 串行化：V1,V2 都是1，V3是2，事務(wù)B將c的值由 1改成2會被阻塞，需要等待事務(wù)A提交之后，才會執(zhí)行。

mysql的鎖類型

分為排它鎖和共享鎖也叫寫鎖和讀鎖
寫鎖是排他的，會阻塞讀取select操作，按照鎖的粒度分為表鎖和行鎖，比如alter table會鎖住表，行鎖有可以分為樂觀鎖，和悲觀鎖，悲觀鎖 為行鎖，樂觀鎖為版本號MVCC實現(xiàn)。
讀鎖是共享的通過 select...for update 和....lock in share model實現(xiàn)，只能讀不能寫

死鎖？

死鎖時不同的事務(wù)，在同一個資源上相互競爭使用，并鎖定對方占用的資源，導(dǎo)致惡性循環(huán)的現(xiàn)象。
解決方式:
mysql實現(xiàn)了死鎖檢測和死鎖超時檢測

• 檢測死鎖的發(fā)生，返回一個錯誤
• 放棄鎖爭用。
• 持有最小的排他行級鎖的事務(wù)進行回滾。

mysql的存儲引擎

innodb 和 myisam 和NDB 和Memory等等。
將innodb和myisam 比較 區(qū)別如下：
myisam 只提供 壓縮，全文檢索，空間函數(shù)等特性功能，缺點：不支持行級鎖和事務(wù)，只支持表鎖，存在嚴重的性能問題。
myisam 將數(shù)據(jù)和索引分開兩個文件存儲,分成.myd和.myi存儲，

show table status like 'xxx'\G 顯示表的相關(guān)信息

如何選擇合適的存儲引擎？

如果需要用到innodb不支持某些特定的功能，在一般情況下，都應(yīng)該選擇innodb.

• 事務(wù): 如果必須要支持事務(wù)，innodb是非常不錯的選擇，不考慮事務(wù)那么myisam非常好，通常用于日志情況。
• 備份: 需要實時備份，innodb 也是非常不錯的選擇。
• 奔潰恢復(fù): 如果需要在數(shù)據(jù)庫崩潰后，迅速恢復(fù)，innodb也非常不錯，myisam 損壞的幾率要比innodb高。

基準測試

基準測試：是針對mysql設(shè)計的一種壓力測試?；鶞蕼y試的類型有集成式和單組件式。
集成測試針對整個應(yīng)用，包括服務(wù)器，代碼，數(shù)據(jù)等，而非單個測試。
單組件式針對mysql測試，比較mysql下的schema 不同，mysql的性能不同。
針對某個問題進行測試，了解問題出現(xiàn)的場景

基準測試工具

集成測試工具

• ab
• httpload
• jmeter

組件式測試工具

• sysbench

基準測試的指標

• 吞吐量
  測試單位時間,每秒的事務(wù)數(shù)量（TPS),以及在線事務(wù)處理的吞吐量(OLTP)
• 并發(fā)性
  用來表示有多少用戶同一時間訪問web站點，http協(xié)議是一個無狀態(tài)的，只是簡單的讀取瀏覽器上的信息，不等同于web服務(wù)器的并發(fā)性，web服務(wù)器的并發(fā)性，不等于數(shù)據(jù)庫的并發(fā)性。而web站點的并發(fā)性，實在任意時間有多少同時發(fā)生的并發(fā)請求。
• 響應(yīng)時間
  用于測試任務(wù)的所需的整體時間。

規(guī)劃和設(shè)計一個基準測試
1.明確相應(yīng)的目標，提出問題
2.選擇合適的測試方案
3.獲得生產(chǎn)數(shù)據(jù)集的快照
4.獲得不同級別的查詢，如果是集成式記錄web服務(wù)器的Http請求。也可以打開mysql的查詢?nèi)罩尽?br> 5.記錄測試數(shù)據(jù)，確定系統(tǒng)的配置，如何測量的，系統(tǒng)的預(yù)熱方案。
6.使用gnuplot或r繪制圖形。

使用sysbench對mysql進行測試

sysbench可以測量以下
1.CPU

sysbench --test=cpu --cpu-max-prime=20000 run

sysbench的cpu測試是在指定時間內(nèi)，循環(huán)進行素數(shù)計算

--cpu-max-prime 素數(shù)生成的上線數(shù)量
--threads: 線程數(shù)
--time: 運行時長

CPU speed:
    events per second:   312.37  #所有線程每秒完成了312.37次event
General statistics:
    total time:                          10.0022s ##總共花費時間
    total number of events:              3125 ###10秒內(nèi)線程總共計算了3125次event
Latency (ms):
         min:                                    2.79 ###完成一次event最少時間
         avg:                                    3.20 ###完成event 平均時間
         max:                                  223.82 ###完成1此最大時間消耗
         95th percentile:                        3.19 ###95%的線程在3.19毫秒內(nèi)完成
         sum:                                 9997.49
Threads fairness:
    events (avg/stddev):           3125.0000/0.00
    execution time (avg/stddev):   9.9975/0.00 ###每個線程平均9.9975秒

2.磁盤IO
sysbench 的 file 測試需要也是 prepare、run 和 cleanup 三個階段。 prepare 是準備階段，產(chǎn)生需要的測試文件，run 是測試階段，cleanup 是清理測試產(chǎn)生的文件

//prepare階段
sysbench fileio --file-num=4  --file-total-size=2G prepare

運行完畢后，會在當前目錄下生成4個文件，大小一共2G

sysbench fileio --time=180 --events=100000000 --threads=1 --file-num=4 --file-total-size=2G --file-test-mode=rndrw run

時間180秒，線程數(shù) 1 個，隨機數(shù)請求 100000000 次，隨機讀性能

File operations:
    reads/s:                      1579.51 每秒讀次數(shù)
    writes/s:                     1053.01 每秒寫次數(shù)
    fsyncs/s:                     105.32 每秒從內(nèi)存向磁盤同步的次數(shù)
Throughput: ###吞吐量
    read, MiB/s:                  24.68
    written, MiB/s:               16.45
General statistics:
    total time:                          180.0590s
    total number of events:              492973
Latency (ms):
         min:                                    0.00
         avg:                                    0.36
         max:                                   86.23
         95th percentile:                        1.01
         sum:                               179444.55
Threads fairness:
    events (avg/stddev):           492973.0000/0.00
    execution time (avg/stddev):   179.4445/0.00

3.mysql壓測
//TODO 這個 不是太會?。。。‰m然網(wǎng)上一大堆教程

mysql性能優(yōu)化:

1.性能定義：完成某件任務(wù)所需要的時間和度量。在myql中，數(shù)據(jù)庫執(zhí)行sql語句的時間可定義為響應(yīng)時間，對性能的優(yōu)化，是在一定的負載下盡可能降低響應(yīng)時間。
2.對響應(yīng)時間的優(yōu)化：如何降低響應(yīng)時間，就要明白為什么需要那么多時間。
3.性能優(yōu)化工具:pt-query-digest對mysql慢查詢?nèi)罩具M行分析

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

mysql> show variables like '%slow%';
+---------------------------+-----------------------------------------+
| Variable_name             | Value                                   |
+---------------------------+-----------------------------------------+
| log_slow_admin_statements | OFF                                     |
| log_slow_slave_statements | OFF                                     |
| slow_launch_time          | 2                                       |
| slow_query_log            | ON                是否開啟慢查日志         |
| slow_query_log_file       | /var/lib/mysql/iZ8ykv3uxiy4adZ-slow.log | 慢查詢?nèi)罩镜拇娣诺胤?

mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 | 當mysql的執(zhí)行時間超過5秒時，會被記錄到日志
+-----------------+----------+

使用pt-query-digest分析慢查詢?nèi)罩?Profile)

//TODO

mysql的數(shù)據(jù)類型

如何選擇mysql選擇合適的數(shù)據(jù)類型：
1.更小的數(shù)據(jù)通常情況下，會更好，更小的數(shù)據(jù)類型占用更少的內(nèi)存，磁盤和CPU緩存
2.盡量避免為NULL.為NULL會使mysql索引的列統(tǒng)計，和值比較更難。
3.盡可能簡單，一般用mysql內(nèi)部基礎(chǔ)的類型來存儲，如：用dateTime或data等來存儲時間，而不是用字符串來存儲時間，用long來存儲ip，而不是vatchar來存儲ip

整數(shù)類型

分成整數(shù)和實數(shù)類型
整數(shù)類型
tinyint(1個字節(jié)),smallint(2個字節(jié)),mediumint(3個字節(jié)),int(4個字節(jié)) ,bigint(8個字節(jié))
一個字節(jié)=8位，所以對于的取值范圍為 -2^(n-1) ~ 2^(n-1) -1,其中n為字節(jié)。
其中tinyint 的取值范圍為-128 ~ 127,其中整數(shù)數(shù)據(jù)類型可分為，有符號位和無符號位，無符號位(unsigned) 表示沒有負數(shù)。如tinyint unsigned表示 0~ 255；有符號位tinyint表示 -128 ~ 127。
int(1)和int(10)有什么區(qū)別？
他不限制int的取值范圍，而是用于mysql交互時(mysql命令行客戶端)，用來顯示的字符個數(shù)。
實數(shù)類型
實數(shù)類型代表有小數(shù)部分的數(shù)字。
float,double,decimal
float和double支持標準的浮點數(shù)運算簡稱浮點，decimal支持更高精度的運算;其中float占4個字節(jié)，double占8個字節(jié)，decimal需要額外的存儲空間和計算,
可以指定小數(shù)點前后所允許最大的位數(shù)。但是可能會影響列空間消耗？如decimal(18,9)小數(shù)點兩邊各存儲9個數(shù)字，每四個字節(jié)存儲9個數(shù)字，小數(shù)點前面4個字節(jié)，小數(shù)點后面4個字節(jié)。小數(shù)點本身占一個字節(jié)。
建議只指定數(shù)據(jù)類型，不指定精度，精度的定義是非標準的???

字符串類型

分成char類型和varchar類型
char和varchar在存儲在磁盤和內(nèi)存上可能不一樣。
varchar:
用于存儲可變字符串，因為他使用必要的空間，比定長更節(jié)省空間，但無絕對，在一種情況下，當Row_format=Fixed的話(通過show table status like 'xxx'\G 來查看)，還是會定長存儲,即使你使用的是varchar。如果列的最大長度小于或者等于255時，需要1個長度來存儲字符串的長度，否則就需要2個長度來存儲。如varchar(1000)就需要1002個字節(jié)，2個字節(jié)存儲字符串長度。
因為行是變長的，所以在update操作可能會使原來的行變得比原來更長，如果沒有空間存儲的話，不同存儲引擎操作是不一樣的，myisam將不同的行拆成片段存儲，innodb需要頁分裂來存儲。
varchar使用場景:

• 列的更新很少，所以就沒有也分裂的場景
• 每個字符都是使用了不同的字節(jié)數(shù)來進行存儲
  在mysql5.0以上版本，mysql在存儲和檢索時會保留末尾空格。當字段過長時，innodb會將varchar變成Blob.

char
char類型是定長的，不容易產(chǎn)生碎片，存儲空間更有效率，存儲Y/N，char只需要一個字符。會刪除末尾空格。mysql的vachar字段的類型雖然最大長度是65535，但是并不是能存這么多數(shù)據(jù)，最大可以到65533（不允許非空字段的時候），當允許非空字段的時候只能到65532。
char(10)如果字段的值不夠10個字符時，char會采用末尾空格來進行填補。
varchar能存儲多少個漢字和數(shù)字呢？

• 4.0版本以下，varchar(100)，指的是100字節(jié)，如果存放UTF8漢字時，只能存33個（每個漢字3字節(jié)）
• 5.0版本以上，varchar(100)，指的是100字符，無論存放的是數(shù)字、字母還是UTF8漢字（每個漢字3字節(jié)），都可以存放100個

BloB和Text類型
存儲字符串數(shù)據(jù)類型，BloB存儲二進制方式存儲，Text采用字符方式存儲。
BloB類型有:tinyblob，smallblob,blob,mediumblob,longblob。沒有排序規(guī)則或字符集
text類型有:tinytext,smalltext,text,mediumtext,longtext。有排序規(guī)則和字符集

Enum枚舉

create table enum_text(
e enum('fish','apple','dog') not null
)

枚舉列把一些不重復(fù)的列存儲到一些不重復(fù)的集合。Mysql將內(nèi)部中將枚舉值保存為數(shù)字，在.frm中保存"數(shù)組-字符串"的映射.這三行實際存儲為整數(shù)，而不是字符串。
查詢實際值:

mysql> select e+0 from enum_text;
+-----+
| e+0 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

所以不建議用數(shù)字作為enum枚舉常量。field()函數(shù)指定排序，導(dǎo)致mysql無法使用索引來消除排序.
弊出:枚舉的列表值是固定的，所以添加和修改必須使用alter table語句。所以在添加枚舉的值時，通常會添加末尾。

日期和時間類型

DateTime和timestamp
DateTime:占8個字節(jié)，存儲的是1000~9999年精確度為秒，封裝格式為YYYYMMDDHHMMSS的整數(shù)中。
Timestamp:占4個字節(jié)，存儲的是從1970年1月1日以來的秒數(shù)，只能表示1970年到2038年。顯示的值也依賴于時區(qū)。
插入或更新Timestamp，如果沒有指定第一個timestamp的值，mysql則設(shè)置這個列的值為當前時間。timestamp 默認為 not null.除了特殊的行為外，應(yīng)該盡量使用timestamp，因為他比datatime空間更高。

位數(shù)據(jù)類型

bit和set
bit列在一列中存儲一個或多個true/false值，bit(1)定義一個單位的字段。最大長度是64個bit位。
set列保存很多個true/false值，缺點是：改變列的代價高，需要alter table.無法通過索引進行查找。

標識符

如何選擇表的標識列？

• 整數(shù)類型
  是最好的選擇，使用auto increment.
• enum和set
  非常不建議選擇，enum和set列只適合存儲固定信息。
• 字符串類型
  盡可能使用字符串類型，作為列的表示，因為它們很消耗空間，比數(shù)字類型慢。使用隨機的字符串，因更加小心，隨機生成的值任意分布在很大的空間內(nèi)，導(dǎo)致insert 和select時，更新很慢.
  1.插入時被索引隨機的寫入到不同的位置，導(dǎo)致插入很慢。會導(dǎo)致頁分裂，磁盤隨機訪問。產(chǎn)生碎片。
  2.select會變慢,邏輯上相鄰的行分布在磁盤和內(nèi)存的不同地方。
  不建議使用UUID，如果使用則需要移除"-",通常使用unhex函數(shù)轉(zhuǎn)換uuid為16位的字節(jié)數(shù)字。檢索式通過hex來格式化16進制。

mysql計數(shù)器

有一下情況，在實際應(yīng)用中，很常見，如查詢一個用戶的朋友數(shù)，文件下載的數(shù)量。

create table hit_counter(
  cnt int unsigned not null
)engine = innodb;

網(wǎng)站每次點擊都會更新;

update hit_counter set cnt = cnt +1;

但是會有一個問題！這條記錄上會有個全局的互斥鎖，會使的這條更新語句串行執(zhí)行！那么需要在增加一列，隨機選擇一列進行更新。

create table hit_counter(
 cnt int unsigned not null,
 slot tinyint unsigned not null primary key
) engine = innodb;

預(yù)先在表中插入100行數(shù)據(jù)，隨機選擇一個slot進行更新，

update hit_counter set cnt = cnt +1 where slot = RANDOM()*100;

查詢統(tǒng)計結(jié)果這樣操作

select count(cnt) from hit_counter;

如果希望每天一個開始一個新的計數(shù)器。

create table hit_counter(
day date not null,
cnt int unsigned not null,
slot tinyint unsigned not null 
primary key(day,slot)
)

同時使用on Duplicate key update 代替。

insert into daily_hit_counter(day,cnt,slot) values(CURRENT_DATE,RAND()*100,1) ON Duplicate key update cnt = cnt +1;

返回結(jié)果
day,slot,cnt
2020-10-01,1,1
2020-10-01,2,3
2020-10-01,4,1

如何加快alter table的速度

mysql的alter table的更新速度是個很大的問題，大部分的操作時，用新的結(jié)構(gòu)創(chuàng)建一個空表，從舊表中查出所有數(shù)據(jù)插入新表。然后刪除舊表。一般情況下，alter table都會導(dǎo)致Mysql服務(wù)中斷。
以下三種優(yōu)化方式，會加快alter table的速度。
1.實現(xiàn)在一臺不提供服務(wù)的機器上執(zhí)行alter table操作。然后和主庫進行切換。
2.使用"拷貝",創(chuàng)建一張新的表，和原表無關(guān)，通過重命名和刪表操作交換兩張表。
3.修改表的默認值，或者刪除或增加主鍵自增。一般情況下可以直接修改.frm文件，然后替換，因為列的默認值和主鍵自增都會存在.frm文件中。

步驟如下

• 創(chuàng)建一個和原來表有相同結(jié)構(gòu)的空表，并進行修改。
• 執(zhí)行flush tables with read lock.
• 交換.frm文件
• unlock tables;

mysql的索引

索引的基本概念:是存儲引擎用于快速查找記錄的一種數(shù)據(jù)結(jié)構(gòu)。
工作方式：現(xiàn)根據(jù)索引值找到對應(yīng)的值，然后根據(jù)匹配的所有記錄找到對應(yīng)的數(shù)據(jù)行。
mysql的索引實在存儲引擎層實現(xiàn)。

索引的基本類型

B-Tree索引：

B-Tree.png

• 全值匹配 ：索引中的列進行全值匹配 如 last-name = 'xxx' and first-name = 'xxx' and dob='xxx';
• 匹配最左前綴 ：last-name = 'xxx'
• 匹配范圍值: last-name >'Allen' and last-name <''Barry
• 只訪問索引的查詢：稱為覆蓋索引。select last-name,first-name,dob from xxx;
  弊端：
• 如果不是使用索引的最左列開始查找，無法使用索引。
• 不能跳過索引的某列。如 last-name = 'xxx' and dob= 'xxx'
• 如果查詢中有某個列范圍查詢，右邊的列都無法使用索引進行查找,如last-name = 'xxx' and first-name like 'j%' and dob = 'xxx';

哈希索引(只有memory引擎支持)

只有精確匹配的所有列的查詢才會有效，哈希索引將所有的哈希碼存儲在索引中，在哈希表中執(zhí)行每個數(shù)據(jù)行的指針。如果多個列的哈希值相同，索引會以鏈表的形式存放多個記錄到同一個哈希目中。
特點:

• 哈希索引只包含哈希值和行指針，不存儲字段值。
• 無法排序，不是按照索引值存儲的。
• 不支持部分索引列匹配查找：如在數(shù)據(jù)列(A,B)建立哈希索引，如果使用A列，那么就無無法使用該索引。
• 只支持等值查找，如：= in ! 不支持范圍查詢。price >100

自適應(yīng)哈希索引：如果一個索引值，被頻繁的訪問的話，哈希索引將會在內(nèi)存中基于B-Tree索引上在創(chuàng)建一個哈希索引。
使用場景：
存儲某個具體的url時，當url過長，如果使用B-Tree時，那么存儲的內(nèi)容就會非常大。新建表如下

create table personhash(
  id int unsigned not null auto_increment,
  url varchar(255) not null,
  url_crc int unsigned not null default 0,
  primary key(id)
)

url存儲具體的url，在url_crc上使用一個hash索引，用來專門存儲hash值，同時新建一個觸發(fā)器，在每次添加url時，自動算出哈希值。

create trigger p_crc_ins before insert on personhash for each row begin
set new.url_crc = crc32(new.url)
end;
//使用插入語句
insert into personhash(url) values('https://www.baidu.com');
//查詢時處理hash沖突
select * from personhash where url = 'https://www.baidu.com' and crc = CRC32("https://www.baidu.com")

不建議使用md5和sha函數(shù)，因為算出來會產(chǎn)生非常大的字符串，浪費空間。

空間數(shù)據(jù)索引

全文索引

查找的是文本中的關(guān)鍵字，不是直接比較索引的值。
........

索引的優(yōu)點

• 減少了服務(wù)器需要掃描的的數(shù)量
• 避免排序和減少產(chǎn)生的臨時表
• 將隨機IO變成順序IO

高性能的索引

mysql無法自動解析某些函數(shù)，自然就無法使用索引

如select * from acturo where acturid + 1= 5;

前綴索引和索引的選擇性

有時候索引很長的字符串列，會讓索引變得又大又慢，一種是用hash索引，還有另一種就是索引列的部分字符。
什么是索引的選擇性：不重復(fù)的所有值和該行記錄的總數(shù)(n)的比值，范圍為 1/n ~ n，之間，索引的選擇性越高，則查詢效率越高。
對于mysql前綴索引，varchar長度很高的類型的列，必須滿足前綴查詢，那么如果保證選擇性足夠高，又能保證索引的長度足夠長呢？
決定前綴的合適長度，需要找到最常見的值得列表，然后和最常見的前綴列表進行比較。如city字段

//找到出現(xiàn)次數(shù)最大的字段，
select count(*) as cnt,city from xxx group by city order by cnd desc limit 10; 
//使用left 截取，并計算 知道前綴的索引列，解決完整列的索引選擇性
select count(*)  as cnt,left (city,3) from xxx group by pref order by cnt desc limit 10;
//計算完整列的索引選擇性
select count(distinct city)/count(*) from xxx;
//添加索引
alter table xxx
add index index_city(city(7));

弊端：mysql無法利用前綴索引做order by 和group by操作無法使用前綴索引做覆蓋掃描。
好處:索引更小，更快。

多列索引

為每個列都創(chuàng)建獨立的索引，或者按照錯誤的順序創(chuàng)建多列索引。都是一種錯誤的方式。
索引合并：當一個表中有多個列，都有索引時，mysql會使用單列索引來定位指定的行，如下在file_actor 中有兩個字段,actor_id和film_id上都有索引。

select film_id,actor_id from film_actor where actor_id = 1 or film_id =1;

這條查詢語句，在mysql老版本中，會全表掃描！在新版本不會，會先根據(jù)actor_id 查詢結(jié)果，然后在根據(jù)film_id查詢結(jié)果 ，然后將結(jié)果合并起來。
下面兩種：
1.OR條件的聯(lián)合
2.And條件的相交

情況優(yōu)化：
1.當服務(wù)器出現(xiàn)多個索引做and條件時，通常需要建立一個相關(guān)列的多列索引，而不是單個索引。
2.當服務(wù)器出現(xiàn)多個索引做or條件時，需要消耗大量CPU和內(nèi)存資源，在緩存，排序和聯(lián)合操作上。
3.優(yōu)化器不會關(guān)心這些，將這些計算到成本里面，導(dǎo)致該執(zhí)行計劃還不如全表掃描。

在多列索引中，如何選擇合適的所有順序？

將選擇性最高的列放在最前列

在一個B-Tree索引中，索引的順序決定order by 的順序，group by和Distinct字句的順序。
如果不考慮排序和分組和范圍的條件，以及隨機Io,將選擇性最高的列放在最前列，那么大部分情況下是最優(yōu)的。

聚簇索引

概念：并不是一種單純的索引類型，而是一種數(shù)據(jù)存儲方式。
InnoDb實際上在同一個數(shù)據(jù)結(jié)構(gòu)中保存了B-tree索引和數(shù)據(jù)行，聚簇索引的葉子節(jié)點，實際上是把相鄰的行和鍵值存儲在一起。聚簇索引的葉子節(jié)點實際上是數(shù)據(jù)行。

聚簇索引.png

優(yōu)點:

1. 把相關(guān)數(shù)據(jù)保存在一起。減少磁盤IO
2. 數(shù)據(jù)訪問更快
3. 使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點中的主鍵值

缺點：

1. 基于主鍵插入時，如果主鍵被更新或者需要移動行的時候，可能面臨頁分裂。
2. 更新的代價會更高，因為強制innodb將每個被更新的行移動到新的位置。
3. 二級索引訪問兩次索引查找，可能需要查找兩次，而不是一次。

myisam和innodb索引和數(shù)據(jù)分布區(qū)別

myisam的主鍵索引和二級索引和數(shù)據(jù)分布上沒有太大的區(qū)別。主鍵索引和二級索引的葉子節(jié)點都存儲的是行指針
在innodb中聚簇索引就是表，二級索引的葉子節(jié)點存儲的不是“行指針”而是主鍵值。
好處：當出現(xiàn)行移動或者頁分裂時，帶來的二級索引的維護的工作。innodb在移動時，無需更新二級索引的指針。
壞處：二級索引會占據(jù)更多的空間。

InnoDb表順序插入行

使用UUID來作為聚簇索引會出現(xiàn)什么問題？它是的聚簇索引的插入變得完全隨機，使數(shù)據(jù)沒有聚集的特性。主鍵更長，空間也大。順序IO變成了隨機IO,新插入的值不一定比前面插入的值大，也可能比前面的更小。所以就會導(dǎo)致也分裂的情況。頁的最大填充因子是15/16,一旦超過下一條記錄就會被寫入到新的頁。
缺點：

1. 寫入的頁可能已經(jīng)被或者沒有沒寫入到磁盤中，寫入到磁盤中的頁，也會被加載到緩存中，導(dǎo)致大量IO修改。
2. 寫入亂序，導(dǎo)致大量的頁需要移動，導(dǎo)致頁分裂。
3. 頁分裂導(dǎo)致數(shù)據(jù)碎片。

覆蓋索引

基本概念：如果一個索引包含所有需要查詢的字段的值，我們就稱為這個索引為覆蓋索引。
好處：

1. 索引的條目小于數(shù)據(jù)行的大小，sql的響應(yīng)時間更多的花在拷貝上，更容易放在內(nèi)存。
2. 索引的值是按照順序存放的，一次范圍查詢比隨機從磁盤讀取每一行數(shù)據(jù)的io要少的多。
3. 二級主鍵能夠覆蓋查詢，避免對聚簇索引的二次查找。

使用覆蓋索引時，用Explain分析是Extra列，看到Using index的信息。
使用覆蓋索引主要的場景：
1.如果使用select * 操作，innodb無法使用覆蓋索引，
2.不能在索引中執(zhí)行Like操作。

通過延遲關(guān)聯(lián)，來優(yōu)化對列的訪問，到達使用覆蓋索引的目的

select * from products where actor = 'SEVEN' and title like '%APOLLOG%'；

建立索引key(actor,title,prod_id);
select * from products join (
  select prod_id from products where actor = 'SEVEN' and title like '%APOLLOG%'
) as t on (t.prod_id = products.prod_id);

使用索引掃描來排序

mysql兩種方式排序操作：
1.通過操作數(shù)據(jù)行排序操作，
2.按索引順序掃描
Explain出來的type列的值為index，說明使用了索引掃描來排序。
使用索引來排序 是由條件的：索引列的順序和order by字句的順序完全一致時。
order by 字句和查找型查詢的限制是一樣的，必須滿足索引的最左前綴但是，有些時候order 是可以不需要滿足的。就是前面的列為常量的時候。

key(rental_date,inventory_id,customer_id)
where rental_date = '2020-01-01' order by inventory_id desc;

第一列提供了常量條件，使用第二列進行排序，組合在一起，就滿足了最左前綴。

where rental_date>'2020-05-25' order by rental_date,inventory_id

上面這個也滿足索引的最左前綴的要求，order by的前兩列就是做做前綴。
下面的情況：不能使用索引做排序的查詢

//索引列都是正序排序的
where rental_date = '2020-01-01' order by inventory_id asc ,customer_id desc
//包含了一個不再索引的列
where rental_date = '2020-01-01' order by inventory_id ,staff_id
//不滿足索引的最左前綴
where rental_date = '2020-01-01' order by customer_id
//查詢在第一列是范圍查詢條件
where rental_date >'2020-01-01' order by inventory_id
//inventory_id in操作也是范圍查詢，對于排序來說不會走索引
where rental_date = '2020-01-01' and inventory_id in(1,2) order by customer_id

查詢所有的使用頻率，通過Information_schema.index_statistics 來查看索引的使用頻率。
索引和鎖：
使用索引可以較少行鎖之間的競爭，減少掃描的行數(shù)。減少行鎖的數(shù)量。Inndo只有在訪問行的時候才會對其加鎖，而索引減少innodb訪問的行數(shù)。如果索引無法過濾無效的行，那么innodb在檢索到數(shù)據(jù)返回給服務(wù)器層以后，才能應(yīng)用到Where字句,這時已經(jīng)無法避免行鎖。

select actor_id from actor where actor_id <5 and actor_id != 1 for update;
mysql雖然返回2,3,4但是 同時也鎖住了1. 這樣的原因是執(zhí)行計劃是索引范圍掃描

如果無法使用索引來查詢有效行數(shù)，那么mysql就會全表掃描并且鎖定所有的行，不管是否需要。

索引簡單案例

1.實現(xiàn)一個在線陌生人交友網(wǎng)站，用戶信息列有：國家，地區(qū)，城市，性別，眼睛等等。比如陌陌，
需求：根據(jù)性別和國家查詢用戶信息？索引該怎么建立呢？
根據(jù)以往經(jīng)驗，key(sex,country);作為前綴索引，如果我們直接根據(jù)國家查詢用戶信息，那么這個索引 就不能用了。
這里有個訣竅：用IN查詢，來避免，如何避免？新增and Sex In('m','f') 來讓mysql 選擇該索引，雖然不會過濾任何行，但是只有這樣才能讓mysql復(fù)合最左前綴。 如果In的列表太長，就不適用了。In的組合不能濫用，會以指數(shù)的形式增長。
接下來需要考慮常見的where 條件的組合，key(sex,conuntry,age) 上 根據(jù)性別，國家，年齡條件查詢。和key(sex,contry,region,city,age) 性別 國家，區(qū)域，城市，年齡，這樣的組合索引。盡可能重用索引，而不是建立大量的組合索引。
age放在最后面，因為age多半是范圍查詢，而范圍查詢會使索引失效。

避免多個范圍查詢

當mysql出現(xiàn)多個范圍查詢時，如根據(jù)上面的案列，查詢過去幾個周上線過得用戶。

where last_online >DATE_SUB(NOW(),INTERVAL_7_DAY)
and age betwen 18 and 25;

范圍條件用Explain分析是type的值為range.
范圍查詢值和查詢列表值？？？？如何區(qū)分？？？？從值的范圍和多個等于條件來區(qū)分，而多個等值查詢用In()代替范圍,mysql可以使用索引

id:1
select_type:simple
table:actor
type:range

排序優(yōu)化

如果一個查詢，匹配的結(jié)果有上百萬行，在排序會怎樣，通常我們一般會建立索引，通過覆蓋索引，然后排序，如一下sql

key(sex,cols);
select  cols from profiles where sex = 'm' order by cols limit 10;

但是如果需要翻頁怎么辦呢？

select cols from profiles where sex = 'm' order by cols limit 100000,10;

這樣查詢的效率起始還是特別慢的，尤其是翻到比較后面的頁時，不管如何建立索引，Mysq總會掃描大量的數(shù)據(jù)然后丟棄掉。

優(yōu)化這種sql關(guān)鍵在于延遲查詢

select cols from profiles inner join(
select cols from profiles where x.sex = 'M' order by rating limit 100000,10
) as x 

通過覆蓋索引查詢返回需要的主鍵，再根據(jù)這些主鍵獲得需要的行。