一條查詢語句是怎么執(zhí)行的？
一條更新語句是怎么運行的?
什么是事務?什么是長事務?如何避免長事務?
如何啟動一個事務?
mysql的隔離性和隔離級別?
什么是臟讀，幻讀，不可重復讀?如何避免?
mysql的鎖類型?
什么是死鎖？msyql是如何解決死鎖的？
mysql存儲引擎的種類，以及他們的區(qū)別？使用場景？
如何查看表狀態(tài)，如表大小，行數(shù)，索引大小，行格式...
什么是mysql基準測試
mysql基準測試的類型，不同的類型有哪些測試工具
基準測試完成后需要看那些指標
如何設計一個基準測試？并得到正確的結果，并繪制圖形
如何使用sysbench對mysql進行壓測
1.連接器：
負責連接客戶端，客戶端連接服務器，服務器都會分配給客戶端一個線程，客戶端斷開時，服務器端不會銷毀該線程，會校驗用戶名和密碼。若正確，則連接成功，同時查詢該客戶端是否具有對哪張表的權限。
2.查詢緩存
執(zhí)行完成后，會將該select語句緩存起來，value就是該語句的查詢結果，當下一次一模一樣的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 然后關聯(lián)t2表，在查詢t2.name ='xxx'
選擇2:先查詢t2表的name然后關聯(lián)t1表，在查詢t1.name ='xxx'
優(yōu)化器包括重寫查詢，決定表的順序，選擇合適的索引.
5.執(zhí)行器
執(zhí)行該select語句，執(zhí)行之前，校驗該用戶是否擁有對該表的權限，如果有，則會從第一行查詢，如果有就返回，直到行末尾。
以上是對select語句執(zhí)行過程的解析
那么更新語句呢？
mysql> update T set c=c+1 where ID=2;
更新語句稍微有點不同？但是mysql的架構是不變的.
1.客戶端連接，連接器負責連接客戶端。
2.解析器，負責詞法語法解析
3.更新語句執(zhí)行;mysql更新語句時，寫入到redo log中，并更新內存數(shù)據(jù)，此時如果更新成功就返回，代表update更新成功了。等待mysql不那么忙的時候，在將臟數(shù)據(jù)刷新到磁盤上。也稱為WAL技術(預寫式日志技術)。
redo log和bing log的差異和不同
1.redo log是innodb 特有的，bing log是server特有的
2.redo log 是固定大小的一共有4G，一共4頁，每頁1G,環(huán)狀數(shù)據(jù)結構，追加寫的，當寫完后，會從頭開始。bing log追加寫，寫完后寫入下一頁。
3.redo log 是邏輯的 記錄數(shù)據(jù)頁做了什么修改，binglog是物理了，記錄了給id=2的這條數(shù)據(jù)c加1；
那么Mysql為什么會隨時隨地恢復數(shù)據(jù)呢？正因為有了redo log和bing log。
redo log和bing log通過兩階段提交，保證了數(shù)據(jù)的安全性。
下面分析一下上面update語句的執(zhí)行流程：
1.msyql用存儲引擎找id =2 這一行，如果內存中有該頁，將直接返回，如果沒有將從磁盤中加載到內存中，然后返回。
2。執(zhí)行器，拿到存儲引擎給的值，對c進行加1操作。同時調用存儲引擎API接口寫入這行數(shù)據(jù)。
3.引擎將這行數(shù)據(jù)寫入到redo log中，然后更新內存，此時redo log處于prepare階段；同時告訴執(zhí)行器，更新成功
3.執(zhí)行器，執(zhí)行這個操作，并且生成binglog日志，將binglog寫入磁盤
4.執(zhí)行器調用存儲引擎API接口，將redo log改成commit，運行這個sql語句。
假設原來c的字段值為0
如果先寫redo log 后寫bing log 會有什么問題忙？
1.寫完redo log 如果mysql 異常重啟，由于寫入了，redo log，mysql仍然講數(shù)據(jù)恢復回來，所以恢復c列為1。
由于沒有寫入bing log ，用Binglog日志恢復mysql，那么bing log日志是丟失這條更新語句的，那么恢復出來的值是0和原來的庫中的數(shù)據(jù)不同。
2.先寫bing log 后寫redo log
如果寫完bing log mysql異常重啟，由于沒有寫入redo log，mysql講原來的c列恢復為0。
但是由于寫入了bing log日志，用Bing log日志寫入了 這條更新語句，那么恢復出來的c列的值為1 ，那么與原來的庫不同。
總結:mysql 通過兩階段提交，保證了事務執(zhí)行的邏輯性，事務的邏輯狀態(tài)保持一致。
什么是事務？長事務？如何查詢長事務？長時間由于沒有提交的事務。
事務：是一組具有原子性的sql語句單元，要么全都執(zhí)行，要么要都不執(zhí)行。
事務的啟動方式有:
- begin....commit/rollback
- 設置mysql 的autocommit = 1 mysql會自動提交一個事務
- begin.....commit work and chain語法 mysql會自動提交一個事務 ，同時開啟下一個事務
如果設置autocommit = 0，mysql不會自動提交事務，會導致長事務的方式，建議將配置文件的autocommit設置為1，
mysql的隔離性:
- A原子性：整個事務的sql語句，要么要都提交，要么全都不提交
- C一致性：是指數(shù)據(jù)庫狀態(tài)的一致性，從一個狀態(tài)到另一個狀態(tài)的改變
- I隔離性：事務提交之前，修改的結果對其他事務不可見
- D持久性，事務語句提交后，持久到磁盤上，不會因為崩潰而導致數(shù)據(jù)丟失
mysql的隔離級別:
- 讀未提交:事務還沒提交，修改的值就會被其他事務讀取到，會導致臟讀
- 讀以提交:當前事務讀取以提交事務的數(shù)據(jù),即一個事務讀取兩次，查看到的結果是不一樣的。會導致 不可重復讀
- 可重復讀:事務開啟的時候，會創(chuàng)建相當于一個快照，事務創(chuàng)建的時候，此時數(shù)據(jù)的列值，就已經(jīng)確定了。 一個事務執(zhí)行過程中看到的數(shù)據(jù)，總是跟這個事務在啟動時看到的數(shù)據(jù)是一致的
- 串行化:顧名思義 串行讀取，讀會加讀鎖，寫會加寫鎖，整個sql語句的執(zhí)行是串行執(zhí)行。讀寫沖突時，后開啟的事務會阻塞，必須等到前面的事務提交之后，才會提交。
mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);
上面這兩個事務，不同的隔離級別，所讀取到v1 v2和v3的值是不一樣的
- 讀未提交級別下 v1 v2 v3都是2 。
- 讀已提交級別下 v1是1 ，v2是2，v3是2 由于b提交了事務，所有v2讀取到的是事務B提交過的數(shù)據(jù)。
- 可重復讀級別下 V1和V2都是1 ,V3是2.由于事務A啟動時就c得值以及確定了，所以v1和V2讀取的值是1。
- 串行化：V1,V2 都是1，V3是2，事務B將c的值由 1改成2會被阻塞，需要等待事務A提交之后，才會執(zhí)行。
mysql的鎖類型
分為排它鎖和共享鎖也叫寫鎖和讀鎖
寫鎖是排他的，會阻塞讀取select操作，按照鎖的粒度分為表鎖和行鎖，比如alter table會鎖住表，行鎖有可以分為樂觀鎖，和悲觀鎖，悲觀鎖 為行鎖，樂觀鎖為版本號MVCC實現(xiàn)。
讀鎖是共享的通過 select...for update 和....lock in share model實現(xiàn)，只能讀不能寫
死鎖？
死鎖時不同的事務，在同一個資源上相互競爭使用，并鎖定對方占用的資源，導致惡性循環(huán)的現(xiàn)象。
解決方式:
mysql實現(xiàn)了死鎖檢測和死鎖超時檢測
- 檢測死鎖的發(fā)生，返回一個錯誤
- 放棄鎖爭用。
- 持有最小的排他行級鎖的事務進行回滾。
mysql的存儲引擎
innodb 和 myisam 和NDB 和Memory等等。
將innodb和myisam 比較 區(qū)別如下：
myisam 只提供 壓縮，全文檢索，空間函數(shù)等特性功能，缺點：不支持行級鎖和事務，只支持表鎖，存在嚴重的性能問題。
myisam 將數(shù)據(jù)和索引分開兩個文件存儲,分成.myd和.myi存儲，
show table status like 'xxx'\G 顯示表的相關信息
如何選擇合適的存儲引擎？
如果需要用到innodb不支持某些特定的功能，在一般情況下，都應該選擇innodb.
- 事務: 如果必須要支持事務，innodb是非常不錯的選擇，不考慮事務那么myisam非常好，通常用于日志情況。
- 備份: 需要實時備份，innodb 也是非常不錯的選擇。
- 奔潰恢復: 如果需要在數(shù)據(jù)庫崩潰后，迅速恢復，innodb也非常不錯，myisam 損壞的幾率要比innodb高。
基準測試
基準測試：是針對mysql設計的一種壓力測試。基準測試的類型有集成式和單組件式。
集成測試針對整個應用，包括服務器，代碼，數(shù)據(jù)等，而非單個測試。
單組件式針對mysql測試，比較mysql下的schema 不同，mysql的性能不同。
針對某個問題進行測試，了解問題出現(xiàn)的場景
基準測試工具
集成測試工具
- ab
- httpload
- jmeter
組件式測試工具
- sysbench
基準測試的指標
- 吞吐量
測試單位時間,每秒的事務數(shù)量（TPS),以及在線事務處理的吞吐量(OLTP) - 并發(fā)性
用來表示有多少用戶同一時間訪問web站點，http協(xié)議是一個無狀態(tài)的，只是簡單的讀取瀏覽器上的信息，不等同于web服務器的并發(fā)性，web服務器的并發(fā)性，不等于數(shù)據(jù)庫的并發(fā)性。而web站點的并發(fā)性，實在任意時間有多少同時發(fā)生的并發(fā)請求。 - 響應時間
用于測試任務的所需的整體時間。
規(guī)劃和設計一個基準測試
1.明確相應的目標，提出問題
2.選擇合適的測試方案
3.獲得生產(chǎn)數(shù)據(jù)集的快照
4.獲得不同級別的查詢，如果是集成式記錄web服務器的Http請求。也可以打開mysql的查詢日志。
5.記錄測試數(shù)據(jù)，確定系統(tǒng)的配置，如何測量的，系統(tǒng)的預熱方案。
6.使用gnuplot或r繪制圖形。
使用sysbench對mysql進行測試
sysbench可以測量以下
1.CPU
sysbench --test=cpu --cpu-max-prime=20000 run
sysbench的cpu測試是在指定時間內，循環(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秒內線程總共計算了3125次event
Latency (ms):
min: 2.79 ###完成一次event最少時間
avg: 3.20 ###完成event 平均時間
max: 223.82 ###完成1此最大時間消耗
95th percentile: 3.19 ###95%的線程在3.19毫秒內完成
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 每秒從內存向磁盤同步的次數(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 這個 不是太會！?。?！雖然網(wǎng)上一大堆教程
mysql性能優(yōu)化:
1.性能定義：完成某件任務所需要的時間和度量。在myql中，數(shù)據(jù)庫執(zhí)行sql語句的時間可定義為響應時間，對性能的優(yōu)化，是在一定的負載下盡可能降低響應時間。
2.對響應時間的優(yōu)化：如何降低響應時間，就要明白為什么需要那么多時間。
3.性能優(yōu)化工具:pt-query-digest對mysql慢查詢日志進行分析
開啟慢查詢日志
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 | 慢查詢日志的存放地方
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 5.000000 | 當mysql的執(zhí)行時間超過5秒時，會被記錄到日志
+-----------------+----------+
使用pt-query-digest分析慢查詢日志(Profile)
//TODO
mysql的數(shù)據(jù)類型
如何選擇mysql選擇合適的數(shù)據(jù)類型：
1.更小的數(shù)據(jù)通常情況下，會更好，更小的數(shù)據(jù)類型占用更少的內存，磁盤和CPU緩存
2.盡量避免為NULL.為NULL會使mysql索引的列統(tǒng)計，和值比較更難。
3.盡可能簡單，一般用mysql內部基礎的類型來存儲，如：用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在存儲在磁盤和內存上可能不一樣。
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
)
枚舉列把一些不重復的列存儲到一些不重復的集合。Mysql將內部中將枚舉值保存為數(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ù)指定排序，導致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則設置這個列的值為當前時間。timestamp 默認為 not null.除了特殊的行為外，應該盡量使用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ù)字類型慢。使用隨機的字符串，因更加小心，隨機生成的值任意分布在很大的空間內，導致insert 和select時，更新很慢.
1.插入時被索引隨機的寫入到不同的位置，導致插入很慢。會導致頁分裂，磁盤隨機訪問。產(chǎn)生碎片。
2.select會變慢,邏輯上相鄰的行分布在磁盤和內存的不同地方。
不建議使用UUID，如果使用則需要移除"-",通常使用unhex函數(shù)轉換uuid為16位的字節(jié)數(shù)字。檢索式通過hex來格式化16進制。
mysql計數(shù)器
有一下情況，在實際應用中，很常見，如查詢一個用戶的朋友數(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;
預先在表中插入100行數(shù)據(jù)，隨機選擇一個slot進行更新，
update hit_counter set cnt = cnt +1 where slot = RANDOM()*100;
查詢統(tǒng)計結果這樣操作
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;
返回結果
day,slot,cnt
2020-10-01,1,1
2020-10-01,2,3
2020-10-01,4,1
如何加快alter table的速度
mysql的alter table的更新速度是個很大的問題，大部分的操作時，用新的結構創(chuàng)建一個空表，從舊表中查出所有數(shù)據(jù)插入新表。然后刪除舊表。一般情況下，alter table都會導致Mysql服務中斷。
以下三種優(yōu)化方式，會加快alter table的速度。
1.實現(xiàn)在一臺不提供服務的機器上執(zhí)行alter table操作。然后和主庫進行切換。
2.使用"拷貝",創(chuàng)建一張新的表，和原表無關，通過重命名和刪表操作交換兩張表。
3.修改表的默認值，或者刪除或增加主鍵自增。一般情況下可以直接修改.frm文件，然后替換，因為列的默認值和主鍵自增都會存在.frm文件中。
步驟如下
- 創(chuàng)建一個和原來表有相同結構的空表，并進行修改。
- 執(zhí)行flush tables with read lock.
- 交換.frm文件
- unlock tables;
mysql的索引
索引的基本概念:是存儲引擎用于快速查找記錄的一種數(shù)據(jù)結構。
工作方式：現(xiàn)根據(jù)索引值找到對應的值，然后根據(jù)匹配的所有記錄找到對應的數(shù)據(jù)行。
mysql的索引實在存儲引擎層實現(xiàn)。
索引的基本類型
B-Tree索引：
特點：所有的值都是按順序存放的。左節(jié)點指向子頁的指針，左子頁的值小于key，右子頁的值大于根節(jié)點的值;很適合查找范圍數(shù)據(jù)，全鍵值，鍵值范圍，鍵前綴查找;順序組織存儲.
查找方式：使用索引不需要全表掃描來獲取需要的數(shù)據(jù)，從索引的根節(jié)點開始搜索，根節(jié)點指向了子節(jié)點的指針，存儲引擎按照這些指針按照下層查找，比較節(jié)點頁的值和要查找的值進入到下層子節(jié)點。
適用查詢范圍:新建索引key(last-name,first-name,dob)
- 全值匹配 ：索引中的列進行全值匹配 如 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
自適應哈希索引：如果一個索引值，被頻繁的訪問的話，哈希索引將會在內存中基于B-Tree索引上在創(chuàng)建一個哈希索引。
使用場景：
存儲某個具體的url時，當url過長，如果使用B-Tree時，那么存儲的內容就會非常大。新建表如下
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ù)索引
全文索引
查找的是文本中的關鍵字，不是直接比較索引的值。
........
索引的優(yōu)點
- 減少了服務器需要掃描的的數(shù)量
- 避免排序和減少產(chǎn)生的臨時表
- 將隨機IO變成順序IO
高性能的索引
mysql無法自動解析某些函數(shù)，自然就無法使用索引
如select * from acturo where acturid + 1= 5;
前綴索引和索引的選擇性
有時候索引很長的字符串列，會讓索引變得又大又慢，一種是用hash索引，還有另一種就是索引列的部分字符。
什么是索引的選擇性：不重復的所有值和該行記錄的總數(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 查詢結果，然后在根據(jù)film_id查詢結果 ，然后將結果合并起來。
下面兩種：
1.OR條件的聯(lián)合
2.And條件的相交
情況優(yōu)化：
1.當服務器出現(xiàn)多個索引做and條件時，通常需要建立一個相關列的多列索引，而不是單個索引。
2.當服務器出現(xiàn)多個索引做or條件時，需要消耗大量CPU和內存資源，在緩存，排序和聯(lián)合操作上。
3.優(yōu)化器不會關心這些，將這些計算到成本里面，導致該執(zhí)行計劃還不如全表掃描。
在多列索引中，如何選擇合適的所有順序？
將選擇性最高的列放在最前列
在一個B-Tree索引中，索引的順序決定order by 的順序，group by和Distinct字句的順序。
如果不考慮排序和分組和范圍的條件，以及隨機Io,將選擇性最高的列放在最前列，那么大部分情況下是最優(yōu)的。
聚簇索引
概念：并不是一種單純的索引類型，而是一種數(shù)據(jù)存儲方式。
InnoDb實際上在同一個數(shù)據(jù)結構中保存了B-tree索引和數(shù)據(jù)行，聚簇索引的葉子節(jié)點，實際上是把相鄰的行和鍵值存儲在一起。聚簇索引的葉子節(jié)點實際上是數(shù)據(jù)行。
優(yōu)點:
- 把相關數(shù)據(jù)保存在一起。減少磁盤IO
- 數(shù)據(jù)訪問更快
- 使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點中的主鍵值
缺點：
- 基于主鍵插入時，如果主鍵被更新或者需要移動行的時候，可能面臨頁分裂。
- 更新的代價會更高，因為強制innodb將每個被更新的行移動到新的位置。
- 二級索引訪問兩次索引查找，可能需要查找兩次，而不是一次。
myisam和innodb索引和數(shù)據(jù)分布區(qū)別
myisam的主鍵索引和二級索引和數(shù)據(jù)分布上沒有太大的區(qū)別。主鍵索引和二級索引的葉子節(jié)點都存儲的是行指針
在innodb中聚簇索引就是表，二級索引的葉子節(jié)點存儲的不是“行指針”而是主鍵值。
好處：當出現(xiàn)行移動或者頁分裂時，帶來的二級索引的維護的工作。innodb在移動時，無需更新二級索引的指針。
壞處：二級索引會占據(jù)更多的空間。
InnoDb表順序插入行
使用UUID來作為聚簇索引會出現(xiàn)什么問題？它是的聚簇索引的插入變得完全隨機，使數(shù)據(jù)沒有聚集的特性。主鍵更長，空間也大。順序IO變成了隨機IO,新插入的值不一定比前面插入的值大，也可能比前面的更小。所以就會導致也分裂的情況。頁的最大填充因子是15/16,一旦超過下一條記錄就會被寫入到新的頁。
缺點：
- 寫入的頁可能已經(jīng)被或者沒有沒寫入到磁盤中，寫入到磁盤中的頁，也會被加載到緩存中，導致大量IO修改。
- 寫入亂序，導致大量的頁需要移動，導致頁分裂。
- 頁分裂導致數(shù)據(jù)碎片。
覆蓋索引
基本概念：如果一個索引包含所有需要查詢的字段的值，我們就稱為這個索引為覆蓋索引。
好處：
- 索引的條目小于數(shù)據(jù)行的大小，sql的響應時間更多的花在拷貝上，更容易放在內存。
- 索引的值是按照順序存放的，一次范圍查詢比隨機從磁盤讀取每一行數(shù)據(jù)的io要少的多。
- 二級主鍵能夠覆蓋查詢，避免對聚簇索引的二次查找。
使用覆蓋索引時，用Explain分析是Extra列，看到Using index的信息。
使用覆蓋索引主要的場景：
1.如果使用select * 操作，innodb無法使用覆蓋索引，
2.不能在索引中執(zhí)行Like操作。
通過延遲關聯(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ù)返回給服務器層以后，才能應用到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復合最左前綴。 如果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)化
如果一個查詢，匹配的結果有上百萬行，在排序會怎樣，通常我們一般會建立索引，通過覆蓋索引，然后排序，如一下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關鍵在于延遲查詢
select cols from profiles inner join(
select cols from profiles where x.sex = 'M' order by rating limit 100000,10
) as x
通過覆蓋索引查詢返回需要的主鍵，再根據(jù)這些主鍵獲得需要的行。
