深入理解MySQL
MySQL邏輯框架
最上層服務并不是MySQL所獨有，大多基于網絡的客戶端/服務器的工具或服務都有類似的架構。如連接處理、授權認證、安全等
第二層架構是MySQL核心服務所在曾，包括查詢解析、分析、優化、緩存以及所有的內置函數（例如，日期、時間、數學和加密函數），所有跨存儲引擎的功能都在這一層實現存儲過程、觸發器、視圖等
第三層包含存儲引擎 ，負責MySQL中數據的存儲和提取。服務器通過API與存儲引擎進行通信。這些接口屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢過程透明。存儲引擎不會去解析SQL，不同存儲引擎之間也不會相互通信，只是響應上層服務器的請求
連接管理與安全性
每個客戶端連接都會在服務器進程中擁有一個線程，連接的查詢只會在這個單獨的線程中執行，該線程只能輪流在某個CPU核心或CPU中運行。服務器會緩存線程，不需為每一個新建的連接創建或銷毀線程
當客戶端（應用）連接到MySQL服務器時，需要對其認證，認證基于用戶名、原始主機信息和密碼。如果使用安全套接字的方式連接，還可以使用X.509證書認證。若客戶端連接成功，服務器會繼續驗證客戶端是否具有執行某個特定查詢的權限（如，是否允許客戶端對world數據庫的Country表執行SELECT語句）
優化與執行
MySQL會解析查詢，并創建內部數據結構（解析數），然后對其進行各種優化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等。用戶可以通過特殊的關鍵字提示優化器，影響其決策過程，也可請求優化器解析優化過程的各個因素，使用戶可以知道服務器是如何優化決策的，并提供一個參考基準，便于重構查詢和schema-修改
相關配置，使應用盡可能高效運行
優化器會請求存儲引擎提供容量或某個具體操作的開銷信息，以及表數據的統計信息等。例如，某些存儲引擎的某種索引，對一些特定的查詢有優化
對于SELECT語句，在解析查詢之前，服務器會先檢查查詢緩存，如果能在其中找到對應的查詢，服務器不必再執行查詢解析、優化和執行的某個過程，直接返回查詢緩存中的結果集
并發控制
MySQL在兩個層面的并發控制：服務器層和存儲引擎層
讀寫鎖
在處理并發讀或寫時，可以通過實現一個由兩種類型的鎖組成的鎖系統來解決問題，這兩種鎖通常被稱為共享鎖和排他鎖，也叫讀鎖和寫鎖
讀鎖使共享的（相互不阻塞），多個客戶在同一時間可以同時讀取同一資源，而互不干擾。寫鎖是排他的，即一個寫鎖會阻塞其他的寫鎖和讀鎖，能確保在給定的時間里，只有一個用戶能執行寫入，并防止其他用戶讀取正在寫入的同一資源
鎖粒度
一種提高共享資源并發性的方式是讓鎖定對象具有選擇性，盡管只鎖定需要修改的部分數據，而不是所有資源。更理想的方式是只對會修改的數據片進行精確的鎖定，鎖定的數據量越少，系統的并發程度越高，只要相互之間不發生沖突即可
鎖策略就是在鎖的開鎖和數據的安全性之間尋求平衡，平衡會影響性能。每種MySQL存儲引擎都可以實現自己的鎖策略和鎖粒度，將鎖粒度固定在某個級別，可以為某些特定的應用場景提供更好的性能，同時會失去對另外一些應用場景的良好支持
表鎖
MySQL中最基本的鎖策略，并且是開銷最小的策略。一個用戶在對表進行寫操作（插入、刪除、更新等）前，需要先獲得寫鎖，會阻塞其他用戶對該表的所有讀寫操作。沒有寫鎖時，其他讀取的用戶才能獲得讀鎖，讀鎖之間是不相互阻塞的
READ LOCAL表鎖支持某些類型的并發寫操作。寫鎖比讀鎖由更高的優先級，因此一個寫鎖請求可能會被插入到讀鎖隊列前面
行級鎖
可以最大程度支持并發處理，同時也帶來最大的鎖開銷。行級鎖存儲引擎層實現，MySQL服務器層沒有實現
事務
事務是一組原子性的SQL查詢，或者說一個獨立的工作單位，如果數據庫引擎能夠成功對數據庫應用該組查詢的全部語句，就執行該組查詢。事務內的語句要么全部執行成功，要么全部執行失敗
隔離級別（4種）
1、READ UNCOMMITTED（未提交讀）
事務種的修改，即使沒有提交，對其他事務也是可見的。事務可以提交未提交的數據，也被稱為臟讀，在實際應用中一般很少使用
2、READ COMITTED（提交讀）
大多數數據庫系統的默認隔離級別都是READ COMMITTED（MySQL不是）。一個事務從開始直到提交之前，所做的任何修改對其他事務都是不可見的，也叫不可重復度
3、REPEATABLE READ（可重復度）
解決了臟讀的問題，保證在同一個事務中多次讀取同樣記錄的結果是一致的，但還是無法解決另一個幻讀的問題。幻讀是指當某個事務在讀取某個范圍內的記錄時，另外一個事務又在該范圍內插入了新的記錄，當之前的事務再次讀取該范圍的記錄時，會產生幻行，可重復讀是MySQL的默認事務隔離級別
4、SERIALIZABLE（可串行化）
最高隔離級別，會在讀取的每一行數據都加鎖，可能導致大量的延時和鎖爭用的問題，實際應用中也很少用這個隔離級別
死鎖
指兩個或多個事務在同一資源上相互占用，并請求鎖定對方占用的資源，從而導致惡性循環的現象。當多個事務試圖以不同的順序鎖定資源時，可能會產生死鎖。多個事務同時鎖定同一個資源時，也會產生死鎖
鎖的行為和順序是和存儲引擎相關的，以同樣的順序執行語句，有些存儲引擎會產生死鎖，有些則不會。死鎖產生有雙重原因：有些是因為真正的數據沖突，這種情況一般難以避免，有些則是由于存儲引擎的實現方式導致的
對于事務型的系統，死鎖是無法避免的，所以應用程序在設計時必須考慮如何處理死鎖。大多數情況下只需要重寫執行因死鎖回滾的事務即可
事務日志
可以幫助提高事務的效率。使用事務日志，存儲引擎在修改表的數據時需要修改其內存拷貝，再把該修改行為記錄到持久在硬盤上的事務日志中，不用每次都將修改的數據本身持久到磁盤。事務采用追加的方式，寫日志的操作是磁盤上一小塊區域內的順序I/O。事務日志持久以后，內存中被修改的數據在后臺可以刷回到磁盤。大多存儲引擎是如此實現的，通常稱之為預寫式日志，修改數據需要寫兩次磁盤
若數據的修改已經記錄到事務日志并持久化，但數據本身沒有寫回磁盤，此時系統崩潰，存儲引擎在重啟時能夠自動恢復這部分修改的數據
MySQL中的事務
MySQL提供了兩種事務型的存儲引擎：InnoDB和NDB Cluster，另外還有一些第三方存儲引擎也支持事務，如XtraDB和PBXT
自動提交（AUTOCOMMIT）
默認采用自動提交模式，如果不是顯式地開始一個事務，則每個查詢都被當作一個事務執行提交操作。通過設置AUTOCOMMIT變量來啟動和禁用自動提交模式
1或ON表示啟用，0或OFF表示禁用
MySQL可通過執行SET TRANSACTION ISOLATION LEVEL 命名來設置隔離級別。新的隔離級別會在下一個事務開始時生效。可以在配置文件中設置整個數據庫的隔離級別，也可以只改變當前會話的隔離級別：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
在事務中混合使用存儲引擎
MySQL服務器層不管理事務，事務由下層存儲引擎實現
若在事務中混合使用了事務型和非事務型的表（如InnoDB和MYISAM表），在正常情況下提交不會有什么問題，但如果該事務需回滾，非事務型的表上的變更無法撤銷，會導致數據庫處于不一致狀態，這種情況難以修復，導致事務最終結果無法確定
隱式和顯式鎖定
InnoDB采用的是兩階段鎖定協議。在事務執行過程中，隨時可以執行鎖定，鎖只有執行COMMIT或ROLLBACK時才會釋放，并且所有鎖會在同一時刻被釋放。前描述的鎖定都是隱式鎖定，InnoDB會根據隔離級別在需要時自動加鎖
多版本并發控制
MVCC是行級鎖的一個變種，在大多數情況下避免了加鎖操作，因此開銷更低，大都實現了非阻塞的讀操作，寫操作也會鎖定必要的行
MVCC的實現是通過保存數據在某個時間點的快照來實現的。根據事務開始的時間不同，每個事務對同一張表，同一時刻看到的數據可能是不一樣的
通過InnoDB的簡化版行為說明MVCC是如何工作的
InnoDB的MVCC通過在每行記錄后面保存兩個隱式的列實現，一個保存了行的創建時間，一個保存行的過期時間，存儲的是系統版本號，沒開始一個新的事務，系統版本號都會自動遞增
SELECT
InnoDB會根據以下的兩個條件檢查每行記錄
1、InnoDB只查找版本早于當前事務版本的數據行，可以確保事務讀取的行，要么在事務開始前已經存在的，要么是事務自身插入或修改果
2、行的刪除版本要么未定義，要么大于當前事務版本號。確保事務讀取到的行在事務開始之前未被刪除
INSERT：InnoDB為新插入的每一行保存當前系統版本號作為行版本號
DELETE：InnoDB為刪除的每一行保存當前系統版本號作為行刪除標識
UPDATE：InnoDB為插入一行新記錄，保存當前系統版本號作為行版本號，同時保存當前系統版本號到原來的行作為行刪除標識
MySQL的存儲引擎
在文件系統中，MySQL將每個數據庫保存為數據目錄下的一個子目錄。創建表時，MySQL會在數據庫子目錄下創建一個和表同名的.frm文件保存表的定義。如創建一個名為MyTable的表，MySQL會在MyTable.frm文件中保存該表的定義
注：Windows大小寫不敏感 Unix敏感
可以使用SHOW TABLE STATUS命令顯示表的相關信息，例如對于mysql數據庫中的user表
Name：表名? ?
Engine：表的存儲引擎類型? ?
Row_format：行的格式? ?
Rows：表中的行數? ?
Avg_row_length：表數據的大小（以字節為單位）? ?
Max_data_length：表數據的最大容量，該值和存儲引擎有關? ??
Index_length：索引的大小（以字節為單位）
Data_free：對于MyISAM表，表示已分配但目前沒有使用的空間。這部分空間包括之前刪除的行，以及后續可以被INSERT利用到的空間
Auto_increment：下一個AUTO-INCREMENT的表? ?
Create_time：表的創建時間? ?
Update_time：表數據的最后修改時間
Check_time：使用CHECK TABLE命令或myisamchk工具最后一次檢查表的時間
Collation：表的默認字符集和字符排序規則
Cheoksum：如果啟用，保存的是整個表的實際校驗和
Create_options：創建表時指定的其他選項
Comment：包含一些其他的額外信息
InnoDB存儲引擎
MySQL默認事務引擎，用來處理大量的短期事務
概覽：InnoDB的數據存儲在表空間中，表空間是由InnoDB管理的一個黑盒子，由一系列的數據文件組成
InnoDB采用MVCC來支持高并發，并且實現了四個標準的隔離級別。默認級別是REPEATABLE READ（可重復讀），并且通過間隙鎖策略防止幻讀的出現。間隙鎖使得InnoDB不僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，以防止幻影行的插入
InnoDB表是基于聚簇索引建立的
MyISAM存儲引擎
提供了大量的特性，包括全文索引、壓縮、空間函數等，但MyISAM不支持事務和行級鎖，而且崩潰后無法安全恢復
存儲：將表存儲在兩個文件；數據文件和索引文件，分別以.MYD和.MYI為擴展名。MyISAM表可以包含動態或靜態行。MySQL根據表的定義決定采用何種行格式
MyISAM特性
加鎖和并發：MyISAM對整張表加鎖，不是針對行。讀取時會對需要讀到的所有表加共享鎖，寫入時對表加排他鎖
修復：對于MyISAM表，MySQL可以手工或自動執行檢查和修復操作。若MySQL服務器已經關閉，也可以通過myisamchk命令行工具進行檢查和修復操作
索引特性：對于MyISAM表，即使BLOB和TEXT等長字段，也可以基于其前500個字符創建索引。MyISAM也支持全文索引
創建MyISAM表的時候，如果指定了DELAY_KEY_WRITE選項，每次修改執行完成時，不會立刻將修改的索引數據寫入磁盤，會寫到內存中的鍵緩沖區，只有在清除緩沖區或關閉表時才將對應的索引塊寫入到磁盤，極大提升了寫入性能
MyISAM壓縮表
可使用myisampack對MyISAM表進行壓縮。壓縮表不能進行修改，可以極大減少磁盤空間占用，也可以減少磁盤I/O，從而提升查詢性能
MyISAM性能
MyISAM引擎設計簡單，數據從緊密格式存儲，有一些服務器級別的性能擴展限制，如對索引鍵緩沖區的Mutex鎖，MariaDB基于段的索引鍵緩沖區機制來避免該問題
MySQL內建的其他存儲引擎
Archive引擎：只支持INSERT和SELECT操作，會緩存所有寫并利用zlib對插入的行進行壓縮，所以比MyISAM表的磁盤I/O更少。每次SELECT查詢都需執行全表掃描，所以Archive表適合日志和數據采集類應用
Archive引擎支持行級鎖和專業的緩沖區，可以實現高并發的插入，在一個查詢開始直到返回表中存在的所有行數之前，Archive引擎會阻止其他的SELECT執行，以實現一致性讀
Blackhole引擎
沒有任何的存儲機制，會丟棄所有插入的數據，不做任何保存。服務器會記錄Blackhole表的日志，所以可以用復制數據到備庫，或簡單地記錄到日志
CSV引擎
可以將普通的CSV文件作為MySQL的表來處理，這種表不支持索引，該引擎可以在數據庫運行時拷入或拷出文件。還可以將Excel等電子表格軟件中的數據存儲為CSV文件，然后復制到MySQL數據目錄下，就能在MySQL中打開使用
Federated引擎
訪問其他MySQL服務器的一個代理，它會創建一個到遠程MySQL服務器的客戶端連接，并將查詢傳輸到遠程服務器執行，然后提取或發送需要的數據
Memory引擎
若需要快速地訪問數據，并且這些數據不會被修改，重啟以后丟失也沒有關系，則使用Memory表，比MyISAM表快一個數量級。Memory表的結構在重啟以后還會保留，但數據會丟失
Memory表在如下場景可以發揮好的作用：
1、用于查找或映射表，例如將郵編和州映射的表
2、用于緩存周期性聚合數據的結果
3、用于保存數據分析中產生的中間數據
Memory表支持Hash索引，因此查找操作非常快。Memory表是表級鎖，因此并發寫入的性能較低，不支持BLOB或TEXT類型的列，并且每行的長度是固定的，即使指定了VARCHAR列，實際存儲時也會轉換成CHAR，導致部分內存浪費
Merge引擎
由多個MyISAM表合并而來的虛擬表。如果將MySQL用于日志或數據倉庫類應用，該引擎可以發揮作用，引入分區功能后，該引擎已經被放棄
NDB集群引擎
作為SQL和NDB原生協議之間的接口，MySQL服務器、NDB集存儲引擎，NDB數據庫的組合稱為MySQL集群
選擇合適的引擎
考慮因素
事務：如果應用需要事務支持，選擇InnoDB；不需要事務，并且主要是SELECT和INSERT操作，選擇MyISAM
備份：若可以定期地關閉服務器來執行備份，那備份的因素可以忽略。如果需要在線熱備份，選擇InnoDB
崩潰恢復：數據量較大時選擇InnoDB引擎
日志型應用
利用MySQL內置的復制方案將數據復制一份到備庫，然后在備庫上執行比較消耗時間和CPU的查詢。主庫只用于高效的插入工作，備庫上執行的查詢也無須擔心影響到日志的插入性能
只讀或大部分情況下只讀的表
有些表的數據用于編制類目或分列清單，這種應用場景是典型的讀多寫少的業務，建議采用InnoDB
訂單選擇：支持事務是必要選項，還要考慮存儲引擎對外鍵的支持情況
CD—ROM應用：可以考慮使用MyISAM表或MyISAM壓縮表，這樣表之間可以隔離并且可以在不同介質上相互拷貝
大數據量：很多InnoDB數據庫數據量在3~5TB之間，或更大。這些系統運行需要合理選擇硬件，做好物理設計，并會服務器I/O瓶頸做好規劃，不能采用MyISAM
注：數據量增長至10TB以上，可能需建立數據倉庫
轉換表的引擎
ALTER TABLE
將表從一個引擎改為另一個引擎的辦法是使用ALTER TABLE：
mysql> ALTER TABLE mytable ENGINE = InnoDB
該語法適用任何存儲引擎，但需要執行很長時間。MySQL會按行將數據從原表復制到一張新的表中，在復制期間可能會消耗系統所有的I/O能力，同時原表會加上讀鎖。若轉換表的存儲引擎，將會失去和原引擎相關的所有特性
導出與導入
適用mysqldump工具將數據導出到文件，然后修改文件中CREATE TABLE語句的存儲引擎選項，注意同時修改表名，因為同一個數據庫中不能存在相同的表名，即使它們使用的是不同的存儲引擎
創建和查詢
綜合了第一種和第二種，不需要導出整個表的數據，先創建一個新的存儲引擎的表，然后利用INSERT...SELECT語法來導數據
若數據量很大，考慮做分批處理
Schema與數據類型優化
選擇優化的數據類型
更小的通常更好：一般情況下，盡量使用可以正確存儲數據的最小數據類型，因為它們占用更少的磁盤、內存和CPU，并且處理時需要的CPU周期更少，但也要確保沒有低估需要存儲的值的范圍，因為在schema中的多個地方增加數據類型的范圍是一個耗時的操作
簡單就好：操作通常需要更少的CPU周期，例如整型比字符操作代價更低，因為字符集的校對規則使字符比整型更加復雜
盡量避免NULL：如果查詢中包含可為NULL的列，對MySQL來說更難優化，因為可為NULL的列使得索引、索引統計和值比較都更復雜。可為NULL的列會使用更多的存儲空間，在MySQL里也需要特殊處理
整數類型
若存儲整數，使用：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別使用8，16，32，64位存儲空間，存儲的值的范圍：
-2^(N-1)~2^(N-1)-1，N是存儲空間的位數
整數類型有可選的UNSIGNED屬性，表示不允許負值，可以使正數的上限提高一倍，如TINYINT.UNSIGNED可以存儲的范圍是0~255，而TINYINT的存儲范圍是-128~127
有符號和無符號類型使用相同的存儲空間，并具有相同的性能，可以根據實際情況選擇合適的類型
實數類型
實數是帶有小數部分的數字，不只是為了存儲小數部分，也可以使用DECIMAL存儲比BIGINT還大的整數，MySQL既支持精確類型，也支持不精確類型
FLOAT和DOUBLE支持使用標準的符浮運算進行近似計算? ? DECIMAL類型用于存儲精確的小數
浮點和DECIMAL可以指定精度。對于DECIMAL列，可以指定小數點前后所允許的最大位數，會影響列的空間消耗，例如，DECIMAL（18，9）小數點兩邊將各存儲9個數字，一共使用9個字節，小數點前后各用4個字節，小數點占一個字節
浮點類型在存儲同樣范圍的值時，通常比DECIMAL使用更少的空間。FLOAT使用4個字節存儲，DOUBLE占用8個字節，和整數類型一樣，能選擇的只是存儲類型，MySQL使用DOUBLE作為內部浮點計算的類型
因為需額外的空間和計算開銷，所以應該盡量只在對小數進行精確計算時才使用DECIMAL，如存儲財務數據
字符串類型
VARCHAR：用于存儲可變長字符串，比定長類型更節省空間，需要使用1或2個額外字節記錄字符串長度，如果列的最大長度小于或等于255字節，則只使用1個字節，其他情況使用2個字節。假設采用latinl字符集，一個VARCHAR（10）的列需要11個字節的存儲空間，VARCHAR（1000）的列需要1002個字節，因為需要2個字節存儲長度信息
VARCHAR節省存儲空間，對性能有所幫助，由于行是變長的，在UPDATE時可能使行變得比原來更長，導致需要做額外的工作
如下情況適用VARCHAR：1、字符串的最大長度比平均長度大很多? ? 2、列的更新很少，所以碎片不是問題? ?3、使用了像UTF-8這樣復雜的字符集，每個字符都使用不同的字節數進行存儲
CHAR
是定長的，MySQL根據定義的字符串長度分配足夠的空間。當存儲CHAR值時，MySQL會刪除所有的末尾空格。CHAR值會根據需要采用空格進行填充以方便比較。CHAR適合短的字符串，如存儲密碼的MD5值，因為這是一個定長的值
先創建一張只有CHAR（10）字段的表并且往里面插入一些值
當檢查這些值時，發現string3末尾的空格被截斷
如果用VARCHAR（10）字段存儲相同的值，可以得到如下結果
更長的列會消耗更多的內存，因為MySQL會分配固定大小的內存卡來保存內布置，尤其是使用內存表進行排序或操作時會很糟糕，所以最好的策略是只分配真正需要的空間
BLOB和TEXT類型
為存儲很大數據而設計的字符串數據類型，分別采用二進制和字符方式存儲
MySQL把每個BLOB和TEXT值作一個獨立的對象處理。當BLOB和TEXT太大時，InnoDB會使用專門的“外部”存儲區域來進行存儲，此時每個值在行內需要1~4個字節存儲一個指針，然后在外部存儲區域存儲實際的值
BLOB和TEXT之間僅有的不同是BLOB類型存儲的是二進制數據，沒有字符集或排序規則，TEXT類型有字符集和排序規則
MySQL對BLOB和TEXT列進行排序與其他類型不同：只對每個列的最前max_sort_length字節而不是整個字符串做排序。如果只需排序前面一小部分字符，可以減小max_sort_length的配置或使用ORDER BY SUSTRING
使用枚舉（ENUM）代替字符串類型
枚舉列可以把一些不重復的字符串存儲成一個預定義的集合。MySQL在存儲枚舉時非常緊湊，會根據列表值的數量壓縮到一個或兩個字節中
例
這三行數據實際存儲為整數，不是字符串，可以通過數字上下文環境檢查看到這個雙重屬性
枚舉不好的地方是字符串列表是固定的，添加或刪除字符串必須使用ALTER TABLE，MySQL把每個枚舉值保存為整個，并且必須進行查找才能轉換為字符串，所以枚舉有開銷
轉換列為枚舉的好處：根據SHOW TABLE STATUS命令輸出結果中Data_length列的值，這兩列轉換為ENUM可以讓表的大小縮小1/3
日期和時間類型
MySQL能存儲的最小時間粒度為秒，MySQL也可以使用微秒級的粒度進行臨時運算，MySQL提供兩種相似的日期類型：DATETIME和TIMESTAMP
DATETIME
保存大范圍的值，從1001年到9999年，精度為秒，把日期和時間封裝到格式為YYYYMMDDHHMMSS的整數中，與時區無關，使用8個字符的存儲空間
默認情況下，MySQL以一種可排序的、無歧義的格式顯示DATETIME的值，例如“2008-01-16? 22：33：08”，這是ANSI標準定義的日期和時間表示方法
TIMESTAMP
保存了從1970年1月1日午夜以來的秒數，它和UNIX時間戳相同。TIMESTAMP只使用4個字節的存儲空間，范圍比DATETIME小得多，只能表示從1970年到2038年，MySQL提供了FROM_TIMESTAMP()函數把日期轉換為Unix時間戳
如果在多個時區存儲或訪問數據，TIMESTAMP和DATETIME的行為將很不一樣，前者提供的值與時區有關系，后者則保留文本表示的日期和時間
如果需要存儲比秒更小粒度的日期和時間值時，可以使用自己的存儲格式，使用BIGINT類型存儲微妙級別的時間戳，或使用DOUBLE存儲秒之后的小數部分
位數據類型
BIT
可以使用列在一列中存儲一個或多個true/false值。BIT(1)定義了一個包含單個位的字段，BIT(2)存儲2個位，依此類推。BIT列的最大長度是64個位。BIT行為因存儲引擎而已，MyISAM會打包所有BIT列，所以17個單獨的BIT列只需17個位的存儲，MyISAM只使用3個字節就能存儲這17個BIT列
MySQL把BIT當作字符串類型而不是數字類型。當檢索BIT（1）的值時，結果是一個包含二進制0或1的字符串，在數字上下文的場景中檢索時，結果將是位字符串轉換成的數字。例：如果存儲一個值b‘00111001’到BIT（8）的列并且檢索它，得到的內容是字符串碼為57的字符串
如果想在一個bit的存儲空間中存儲一個true/false值，另一個方法是創建一個可以為空的CHAR（0列，該列可以保存空值（NULL）或長度為0的字符串
SET
如果需要保存很多true/false值，可以考慮合并這些列到一個SET數據類型，在MySQL內部是以一系列打包的位的集合來表示的，有效的利用了存儲空間，并且MySQL有FIND_IN_SET()和FIELD()這樣的函數，方便在查詢中使用
缺點是改變列的定義的代價較高，需要ALTER TABLE，也無法在SET列上通過索引查找
選擇標識符
當選擇標識列的類型時，不僅要考慮存儲類型，還要考慮MySQL對這種類型怎么執行計算和比較。例如，MySQL在內部使用整數存儲ENUM和SET類型。然后再做比較操作時轉換為字符串
確定了一種類型，要確保在所有關聯表中都使用同樣的類型。類型之間需要精確匹配，包括像UNSIGNED這樣的屬性。在可以滿足值的范圍的需求，并且預留未來增長空間的前提下，應該選擇最小的數據類型
整數類型：通常是標識列最好的選擇，因為它們很快并且可以使用AUTO_INCREMENT
ENUM和SET類型：通常是一個糟糕的選擇，對某些只包含固定狀態或類型的靜態“定義表”來說可能沒問題，ENUM和SET列適合存儲固定信息，例如有序的狀態、產品類型、人的性別
字符串類型：應避免使用字符串類型作為標識列，它們消耗空間很大，通常比數字類型慢
MySQL schema設計中的陷阱
太多的列
MySQL的存儲引擎API工作時需要在服務器層和存儲引擎層之間通過行緩沖格式拷貝數據，然后在服務器層將緩沖內容解碼成各個列。行緩沖將編碼過的列轉換成行數據結構的操作代價是高昂的，MyISAM定長行結構實際與服務器層的行結構正好匹配，不需要轉換
太多的關聯
MySQl限制了每個關聯操作最多只能有61張表，但EVA數據庫需要許多的關聯，如果希望查詢執行得快速且并發性好，單個查詢最好在12個表以內做關聯
全能的枚舉
注意防止過度使用枚舉，例如
應該用整數作為外鍵關聯到字典表或查找表來查找具體值，在MySQL中，當需要在枚舉表中增加一個新的國家時就要做一次ALTER TABLE操作
變相的枚舉
枚舉列允許在列中存儲一組定義值中的單個值，集合列允許在列中存儲一組定義值中的一個或多個值，例如
如果真和假兩種情況不會同時出現，應該使用枚舉列代替集合列
范式和反范式
在范式化的數據庫中，每個事實數據會出現并且只出現一次，相反，在反范式化的數據庫中，信息的冗余的，可能會存儲在多個地方
范式的優點：
1、范式化的更新操作通常比反范式化要快
2、當數據較好地范式化時，就只有很快或沒有重復數據，所以只需要修改更少的數據
3、范式化的表通常更小，可以更好地放在內存里，執行操作會更快
4、少有多余的數據意味著檢索列表數據時更少需要DISTINCT或GROUP BY語句
范式的缺點：
1設計的schema的缺點是通常需要關聯。稍復雜一些的查詢語句在符合范式的schema上都可能需要至少一次關聯，也許更多
反范式的優點和缺點
反范式的schema因為所有數據都在一張表中，可以很好避免關聯。當數據比內存大時可能比關聯要快得多，避免了隨機I/O
緩存表和匯總表
緩存表表示存儲那些可以比較簡單地從schema其他表獲取（速度慢）數據的表。例如邏輯上冗余的數據
匯總表保存的是使用GROUP BY語句聚合數據的表，數據不是邏輯上冗余的
以網站為例，假設需要計算之前24小時內發送的消息數，以每小時匯總表為基礎，把前23個完整的小時的統計表中的計數全部加起來，最后再加上開始階段和結束階段不完整的小時內的計數，假設統計表叫作msg_per_hr并且這樣定義：
可以通過把下面三個語句的結果加起來，得到過去24小時發送消息的總數，使用LEFT（NOW(),14）來獲得當前的日期和時間最接近的小時
在使用緩存表和匯總表時，必須決定是實時維護數據還是定期重建，哪個更好依賴于應用程序，但是定期重建并不是節省資源，也可以保持表不會有很多碎片，以及有完全順序組織的索引
當重建匯總表和緩存表時，需要保證數據在操作時依然可用，通過使用“影子表”來實現，“影子表”指的是一張在真實表“背后”創建的表。當完成了建表操作后，通過一個原子的重命名操作切換影子表和原表。例如，如果需要重建my_summary則可以先創建my_summary_new，然后填充好數據，最后和真實表做切換
物化視圖
預先計算并且存儲在磁盤上的表，可以通過各種策略刷新和更新。MySQL并不原生支持物化視圖，使用開始工具Flexviews可以自己實現物化視圖，提供了很多功能使得可以更簡單地創建和維護物化視圖，由以下部分組成：
1、變更數據抓取功能，可以讀取服務器的二進制日志并且解析相關行的變更
2、一系列可以幫助創建和管理試圖的定義的存儲過程
3、一些可以應用變更到數據庫中的物化視圖的工具
Flexviews通過提取對源表的更改，可以增量地重新計算物化視圖的內容，不需要通過查詢原始數據來更新視圖。例如，如果創建了一張匯總表用于計算每個分組的行數，此后增加了一行數據到源表中，Flexviews給相應的組的行數加一
計數器表
可以緩存一個用戶的朋友數，文件下載次數等
假設有一個計數器表，只有一行數據，記錄網站的點擊次數
網站每次點擊都會導致對計數器進行更新
獲得更高的并發更新性能，可以將計數器保存在多行中，每次隨機選擇一行進行更新，需要對計數器表進行如下修改
然后預先在這張表增加100行數據，選擇一個隨機的槽進行更新
要獲得統計結構，需要使用下面這樣的聚合查詢
加快ALTER TABLE的操作速度
有兩種方式可以改變或刪除一個列的默認值，假如要修改電影的默認租賃期間，從三天改到五天，如下是慢方式
SHOW STATUS顯示這個語句做了1000次讀和1000次插入操作，拷貝整張表到新表
理論上，MySQL可以跳過創建新表的步驟，列的默認值實際上存在表的.frm文件中可以修改文件不改動表本身
另一種是通過ALTER COLUMN操作來改變列的默認值
此語句直接修改.frm文件
只修改.frm文件
下面這些操作可能不需要重建表
1、移除一個列的AUTO_INCREMENT屬性
2、增加、移除、更改ENUM和SET常量，如果移除的是已有行數據用到其值的常量，查詢將會返回一個空字串值
基本的技術是為想要的表結構創建一個新的.frm文件，然后用它替換掉已存在的那張表的.frm文件，如
1、創建一張有相同結構的空表，并進行所需要的修改（例如增加ENUM常量）
2、執行FLUSH TABLE WITH READ LOCK，將會關閉所有正在使用的表，并且禁止任何表被打開
3、交換.frm文件
4、執行UNLOCK TABLES來釋放第2步的讀鎖
例：給sakila film表的rating列增加一個常量，當前列如下：
增加一個PG—14的電影分級
接下來用操作系統的命名交換.frm文件
再回到MySQL命令行，可以解鎖表并且看到變更后的效果
最后需要刪除為完成這個操作而創建的輔助表
快速創建MyISAM索引
為了高效地載入數據到MyISAM表中，有一個常用技巧是先禁用索引，載入數據，然后重新啟用索引
當已經知道所有數據都是有效的并且沒有必要做唯一性檢查時可以如下操作：
1、用需要的表結構創建一張表，不包括索引
2、載入數據到表中以構建.MYD文件
3、按照需要的結構創建另外一張空表，要包含索引。創建需要的.frm個.MYI文件
4、獲取讀鎖并刷新表
5、重命名第二張表的.frm和.MYI文件，讓MySQL認為是第一張表的文件
6、釋放讀鎖
7、使用REPAIR TABLE來重建表的索引。該操作會通過排序來構建所有索引，包括唯一索引
需要使用的數據庫保持簡單原則
1、盡量避免過度設計，例如會導致極其復雜查詢的shcema設計，或有很多列的表設計
2、使用小而簡單的合適數據類型，應該盡可能避免使用NULL值
3、盡量使用相同的數據類型存儲相似或相關的值，尤其是要在關聯條件中使用的列
4、注意可變長字符串，其在臨時表和排序時可能導致悲觀的按最大長度分配內存
5、盡量使用整型定義標識列
6、避免使用MySQL已經遺棄的特性，例如指定浮點數的精度，或整數的顯示寬度
7、小心使用ENUM和SET
創建高性能的索引
索引（在MySQL也叫“鍵”）是存儲引擎用于快速找到記錄的一種數據結構，索引能夠輕易將查詢性能提高幾個數量級
索引基礎
在MySQL中，存儲引擎用類似的方法使用索引，先在索引中找到對應值，然后根據匹配的索引記錄找到對應的數據行，例如運行如下查詢
mysql> SELECT first _name FROM sakila.actor WHERE actor_id = 5;
如果在actor_id列上建有索引，則MySQL將使用該索引找到actor_id為5的行，MySQL先在索引上按值進行查找，然后返回所有包含該值的數據行
索引可以包含一個列或多個列的值，如果索引包含多個列，那列的順序也十分重要，因為MySQL只能高效地使用索引的最左前綴列
索引的類型
B—Tree索引
使用B—Tree數據結構來存儲數據，大多數MySQL引擎都支持這種索引。
存儲引擎以不同的方式使用B—Tree索引，性能不同，各有優勢，如MyISAM使用前綴壓縮技術使得索引更小，但InnoDB按照原數據格式進行存儲
B—Tree意味著所有的值都是按順序存儲的，并且每一個葉子頁到根的距離相同
B—Tree索引能夠加快訪問數據的速度，因為存儲引擎不再需要進行全表掃描來獲取需要的數據，從索引的根節點開始搜索
B—Tree對索引是順序存儲的，適合查找范圍數據，例如，在一個基于文本域的索引樹上，按字母順序傳遞連續的值進行查找是合適的
假設有如下數據表
對表中的每一行數據，索引中包含了last_name、first_name和dob列的值
索引對多個值進行排序的依據是CREATE TABLE語句中定義索引時列的順序。B—Tree索引適合全鍵值、鍵值范圍或鍵前綴查找，其中鍵前綴查找只適用于根據最左前綴的查找，前面所述索引對如下類型的查詢有效
全值匹配：指的是和所有列進行匹配，例如前面提到的索引可用于查找姓名為Cuba Allen 出生于1960-01-01的人
匹配最左前綴：索引可用于查找所有姓為Allen的人，即只使用索引的第一列
匹配列前綴：可以只匹配某一列的值的開頭部分，例如前面提到的索引可用于查找所有以j開頭的姓的人
匹配范圍值：例如前面提到的索引可用于查找姓在Allen和Barrymore之間的人
精確匹配某一列并范圍匹配到另外一列：前面提到的索引可用于查找所有姓為Allen，并且名字是字母K開頭的人
B-Tree通常可以支持“只訪問索引的查詢”，即查詢只需要訪問索引，無須訪問數據行
關于B—tree索引的限制
1、如果不是按照索引的最左列開始查找，則無法使用索引
2、不能跳過索引中的列
3、如果查詢中有個列的范圍查詢，其右邊所有列都無法使用索引優化查找
哈希索引
基于哈希表實現，只有精確匹配索引所有列的查詢才有效，對于每一行數據，存儲引擎都會對所有的索引列計算一個Hash Code，Hash Code是一個較小的值，并且不同鍵值的行計算出來的Hash Code也不一樣。Hash索引將所有的Hash Code存儲在索引中，同時在Hash表中保存指向每個數據行的指針
在MySQl中，只有Memory引擎顯式支持Hash索引
例如，假設有如下表
表中包含如下數據
假設索引使用假象的Hash函數f()，它返回下面的值
則Hash索引的數據結構如下
每個槽的編號是順序的，但數據行不是，如下查詢
mysql> SELECT lname FROM testhash WHERE fname = "Peter";
MySQL先計算‘Peter’的Hash值，并使用該值尋找對應的記錄指針，因為f('Peter')=8784，所以先在索引中查找8784，可以找到指向第3行的指針，最后一步比較第三行的值是否為‘Peter’，以確保就是要查找的行
Hash索引的限制：
1、Hash索引只包含Hash值和行指針，不存儲字段值，不能使用索引中的值來避免讀取行
2、Hash索引數據并不按照索引值順序存儲，無法用于排序
3、Hash索引也不支持部分索引到匹配查找，因為Hash索引始終是使用索引列的全部內容來計算Hash值的。例如，在數據列（A，B）上建立Hash索引，如果查詢只有數據到A，則無法使用該索引
４、Hash索引只支持等值比較查詢，包括=、IN()、<=>，不支持任何范圍查詢，例如WHERE price > 100
?5、當出現Hash沖突時，存儲引擎必須遍歷表中所有的行指針，逐行進行比較，直到找到所有符合條件的行
創建自定義Hash索引。如果存儲引擎不支持Hash索引，可以模擬InnoDB一樣創建Hash索引
思路：需要存儲大量URL，需要根據URL進行搜索查找，如下查詢
mysql> SELECT id FROM url WHERE url = "http:// www.mysql.com";
若刪除原來URL列上的索引，新增一個被索引的url-crc列，如下查詢
觸發器如何在插入和更新時維護url-crc列，首先創建如下表
然后創建觸發器，先臨時修改一下語句分隔符，可以在觸發器定義中使用分號：
驗證觸發器如何維護Hash索引
空間數據索引（R-Tree）
MyISAM表支持空間索引，可以用作地理數據存儲。和B-Tree索引不同，無須前綴查詢，空間索引會以所有維度來索引數據，查詢時，可以有效地使用任何維度來組合查詢。必須使用MySQL的GIS相關函數如MBRCONTAINS()來維護數據。MySQL的GIS支持并不完善，開源關系數據庫系統對GIS解決方案做得好的是PostgreSQl和PostGIS
全文索引
一種特殊類型的索引，查找的是文本中的關鍵詞，不直接比較索引中的值，需注意的細節如停用詞、詞干和復數、布爾搜索等
索引的優點
1、大大減少了服務器需要掃描的數據量? ? 2、可以幫助服務器避免排序和臨時表
3、可以將隨機I/O變為順序I/O
高性能的索引策略
獨立的列
如果查詢中的列不是獨立的，MySQL不會使用索引。“獨立的列”是指索引列不能是表達式的一部分，也不能是函數的參數
例如下面這個查詢無法使用actor_id列的索引
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id +1 = 5;
可以看出WHERE中的表達式等價于actor_id = 4 ，但MySQL無法自動解析這個方程式
前綴索引的和索引選擇性
索引的選擇性是指不重復的索引值（也稱為基數）和數據表的記錄總數（#T）的比值，范圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的行
對于BLOB、TEXT或很長的VARCHAR類型的列。必須使用前綴索引，因為MySQL不允許索引這些列的完整長度
為決定前綴的合適長度，需要找到最常見的列表，然后和最常見的前綴列表進行比較
從表中生成一個實例表，如圖
找到最常見的城市列表
查找最頻繁出現的城市前綴，從3個前綴字母開始
每個前綴都比原來的城市出現的次數更多，唯一前綴比唯一城市更少得多。增加前綴長度，直到這個前綴的選擇性接近完整列的選擇性
計算合適的前綴長度的另一個辦法是計算完整列的選擇性，并使前綴的選擇性接近完整列的選擇性
前綴索引是一種能使索引更小、更快的有效方法，但MySQL無法使用前綴索引做ORDER BY和GROUP BY，也無法使用前綴索引做覆蓋掃描
多列索引
在多個列上建立獨立的單列索引大部分情況下并不能提高MySQL的查詢性能。MySQL5.0和更新版本引入了一種叫“索引合并”的策略，一定程度上可以使用表上的多個單列索引來定位指定的行，查詢能夠同時使用這兩個單列索引進行掃描，并將結果進行合并。這種算法有三個變種：OR條件的聯合、AND條件的相交、以及組合前兩種情況的聯合及相交
如下的查詢是使用了兩個索引掃描的聯合，通過EXPLAIN中的Extra列可看出
索引合并策略有時是一種優化的結果，實際上更多時候說明了表上的索引建的很糟糕
1、出現服務器對多個索引做相交操作時，意味著需要一個包含所有相關列的多列索引，不是多個獨立的單列索引
2、當服務器需要對多個索引做聯合操作時，需要消耗大量CPU和內存資源在算的緩存、排序和合并操作上
3、優化器不會把這些計算到“查詢成本”中，只關心隨即頁面讀取
如果在EXPLAIN中看到有索引合并，應該檢查以下查詢和表結構，看是否已經是最優的，也可以通過參數optimizer-switch來關閉索引合并功能，也可以使用IGNORE INDEX提示讓優化器忽略掉某些索引
選擇合適的索引列順序
經驗法則：將選擇性最高的列放到索引最前端，但通常不如避免隨機IO和排序重要。當不需要考慮排序和分組時，將選擇性最高的列放在最前面，這時索引的作用是優化WHERE條件的查找
性能不只是依賴于所有索引列的選擇性（整體基數），也和查詢條件的具體值有關，可能需要根據運行效率最高的查詢來調整索引列的順序，讓這種情況下的索引選擇性更高
以下面的查詢為例:
SELECT *FROM payment WHERE staff-id = 2 AND customer-id = 584;
可以跑一些查詢來確定在這個表中值的分布情況，并確定那個列的選擇性更高
將索引列customer_id放到前面，因為對應條件值的customer_id數量更小
案例：在一個用戶分享購買商品和購買經驗的論壇上，這個特殊表上的查詢運行得非常慢
查詢看似沒有建立合適的索引，所以客戶端是否可以優化,EXPLAIN結果如下
這個案例的解決辦法是修改應該程序代碼，區分這類特殊用戶和組，禁止這類用戶和組執行這個查詢
聚簇索引
不是一種單獨的索引類型，是一種數據存儲方式，具體的細節依賴于其實現方式，但InnoDB的聚簇索引實際上在同一個結構中保存了
B-Tree索引和數據行
當表有聚簇索引時，它的數據行實際上存放在索引的葉子頁中。術語“聚簇”表示數據行和相鄰的鍵值緊湊地存儲在一塊，因為無法同時把數據行存放在兩個不同的地方，所以一個表只能有一個聚簇索引
存儲引擎負責實現索引，因此一個表只能有一個聚簇索引；存儲引擎負責實現索引，因此不是所有的存儲引擎都支持聚簇索引
如圖展示了聚簇索引中的記錄是如何存放的
聚集數據的優點：
1、可以把相關數據保存在一起，例如實現電子郵箱時，可以根據用戶ID聚集數據，只需要從磁盤讀取少數的數據頁就能獲取某個用戶的全部郵件
2、數據訪問更快，聚簇索引將索引和數據保存在同一個B-Tree中，因此聚簇索引中獲取數據通常比在非聚簇索引中查找要快
3、使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值
聚簇索引的缺點
1、最大限度提高了I/O密集型應用的性能
2、插入速度嚴重依賴于插入順序，按照主鍵的順序插入是加載數據到InnoDB表中速度最快的方式
3、更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動到新的位置
4、基于聚簇索引的表再插入新行，或主鍵被更新導致需行移動行的時候，可能“頁分裂”的問題。頁分裂是指當行的主鍵值要求必須將這一行插入到某個已滿的頁時，存儲引擎會將該頁分裂成兩個頁面來容納該行，會導致占用更多磁盤空間
5、聚簇索引可能會導致全表掃描變慢
覆蓋索引
如果查詢只需要掃描索引而無須回表，會帶來如下好處：
1、索引條目通常會遠小于數據行大小，所以如果只需讀取索引，那么MySQL會極大地減少數據訪問量
2、因為索引是按照列值順序存儲的，對于I/O密集型的范圍查詢會比隨機從磁盤讀取每一行數據的I/O要少得多
3、一些存儲引擎如MyISAM在內存中只緩慢索引，數據則依賴操作系統來緩存，因此要訪問數據需要一次系統調用，可能會導致嚴重的性能問題，尤其是那些系統調用占了數據訪問中最大開銷的場景
4、InnoDB的二級索引在葉子節點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢
MySQL只能使用B-Tree索引做覆蓋索引
例如：表sakila.inventory有一個多列索引（store_id、film_id），MySQL如果只需訪問這兩列，可以使用這個索引做覆蓋索引，如下
索引覆蓋查詢還有很多陷阱可能會導致無法實現優化。MySQL查詢優化會在執行查詢前判斷是否有一個索引能進行覆蓋
這里索引無法覆蓋查詢，有兩個原因：
1、沒有任何索引能夠覆蓋這個查詢，因為查詢從表中選擇了所有的列，沒有任何索引覆蓋了所有的列。理論上MySQL還有一個捷徑可利用：WHERE條件中的列是有索引可以覆蓋的，因此MySQL可以使用該索引找到對應的actor并檢查title是否匹配，過濾之后再讀取需要的數據行
2、MySQL不能在索引中執行LIKE操作，能在索引中做最左前綴的LIKE比較，因為該操作可以轉換為簡單的比較操作，但如果是通配符開頭的LIKE查詢，存儲引擎無法做比較匹配
有辦法解決以上兩種問題，需要重新查詢并巧妙地設計索引，先將索引擴展至覆蓋三個數據到（artist、title、prod_id），按如下方式重寫查詢
這種方式叫做延遲關聯，延遲了對列的訪問。在查詢第一階段MySQL可以使用覆蓋索引，在FROM子句的子查詢中找到匹配的prod_id，根據其值在外層查詢匹配獲取需要的所有列值
例：sakila.actor使用InnoDB存儲引擎，并在last_name字段有二級索引，該索引的列不包括主鍵actor_id，但也能夠用于對actor_id做覆蓋查詢
使用索引掃描來做排序
MySQL有兩種方式可以生成有序的結果：1、通過排序操作? ?2、按索引順序掃描
如果EXPLAIN出的type列的值為“index”，說明MySQL使用索引掃描來做排序
MySQL可以使用同一個索引既滿足排序，又用于查找行。只有當索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向都一樣時，MySQL才能使用索引來對結果做排序。如果查詢需要關聯多張表，則只有當ORDER BY子句引用的字段全部為第一個表時，才能使用索引做排序
例：sakila示例數據庫的表rental在列（rental_date、inventory_id、customer_id）上有名為rental_date的索引
MySQL可以使用rental_date索引為下面的查詢做排序，從EXPLAIN中可看到沒有出現文件排序操作
如下是不能使用索引做排序的查詢
使用了兩種不同的排序方向，但索引列都是正序排序的
...WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC ,customer_id ASC;
ORDER BY引用了一個不在索引中的列：
...WHERE rental_date = '2005-05-25'?RDER BY inventory_id,staff_id;
WHERE和ORDER BY中的列無法組合成索引的最左前綴
...WHERE rental_id = '2005=05-25'? ORDER BY customer_id;
在索引列的第一列上是范圍條件，所以MySQL無法使用索引的其余列，在inventory——id列上有多個等于條件，對于排序來說，這也是一種范圍查詢
...WHERE rental_date = '2005-05-25' AND inventory_id IN (1,2) ORDER BY customer_id;
壓縮（前綴壓縮）索引
MyISAM使用前綴壓縮來減少索引的大小，從而讓更多的索引放入內存中，一些情況下可以大幅度提升吸能。默認只壓縮字符串，通過參數設置也可以對整數做壓縮
MyISAM壓縮每個索引塊的方法：先完全保存索引塊中的第一個值，然后將其他值和第一個值進行比較得到相同前綴的字節和剩余的不同后綴部分。如第一個值是“perform”，第二個值是“performance”，那么第二個值的前綴壓縮后存儲的是類似“7，ance”這樣的形式
冗余和重復索引
重復索引是指在相同列上按相同的順序創建的相同類型的索引，應當避免創建重復索引
有時會不經意間創建重復索引，如下
MySQL唯一限制和主鍵限制都是通過索引實現的，因此上面的寫法實際上在相同的列上創建了三個重復的索引
冗余索引和重復索引有所不同，如果創建了索引（A，B），再創建索引（A）就是冗余索引，因為這只是前一個索引的前綴索引
冗余索引通常發生在為表添加新索引時。如，增加一個新的索引（A，B）而不是擴展已有的索引（A）
解決冗余索引和重復索引的方法很簡答，刪除這些索引即可，先找出這樣的索引，寫一些復雜訪問INFORMATION——SCHEMA是一系列可以安裝到服務器上的常用的存儲和視圖，還可以使用Percona Toolkit中的pt_duplicate_key_checker，該工具通過分析表結構來找出冗余和重復索引
未使用的索引
這樣的索引完成是累贅，建議考慮刪除。最簡單有效的辦法是在Percona Server或Maria DB中先打開userstates服務器變量（默認是關閉的），然后讓服務器正常運行一段時間，再通過查詢INFORMATION——SCHEMA.INDEX_STATISTICS能查詢到每個索引的使用頻率
索引和鎖
索引可以讓查詢鎖定更少的行，如果查詢從不訪問那些不需要的行，那么會鎖定更少的行，從兩個方面來說對性能都有好處。首先，雖然InnoDB的行鎖效率很高，內存使用也少，但鎖定行的時候仍會帶來額外的開銷，其次，鎖定超過需要的行會增加鎖爭用并減少并發性
InnoDB只有在訪問行的時候才會對其加鎖，而索引能夠減少InnoDB訪問的行數，從而減少鎖的數量，但只有要InnoDB在存儲引擎層能夠過濾掉所有不需要的行時才有效
索引案例學習
支持多種過濾條件
country列的選擇性通常不高，但可能很多查詢都會用到。sex列的選擇性肯定很低，但也會在很多查詢中用到，考慮到使用的頻率，建議在創建不同組合索引時將（sex,country)列作為前綴
這么做的理由有兩個：1、幾乎所有的查詢都會用到sex列? ? 2、索引中加上這一列并無壞處，即使查詢沒有使用sex列也可通過“訣竅”繞過
訣竅是指如果某個查詢不限制性別，可以通過在查詢條件中新增AND SEX IN（‘m’，‘f’）讓MySQL選擇該索引
案例顯示了一個基本原則：考慮表上所有的選項，當設計索引時，不要只為現有的查詢考慮需要哪些索引，還要考慮對查詢進行優化
避免多個范圍條件
假設有一個last_online列并希望通過下面的查詢顯示在過去幾周上線過的用戶
這個查詢有一個問題：它有兩個范圍條件，last_online和age列，MySQL可以使用last_online列索引或age列索引，但無法同時使用它們
優化排序
對于選擇性非常低的列，可以增加一些特殊的索引來做排序，例如，可以創建(sex,rating)索引用于下面的查詢
mysql> SELECT <cols> FROM profiles WHERE sex = 'M' ORDER? BY rating LIMIT 10;
同時使用了ORDER BY和LIMIT，沒有索引的話會很慢，即使有索引，如果用戶界面上需要翻頁，并且翻頁到比較靠后時查詢也可能非常慢
因為隨著偏移量的增加，MySQL需要花費大量的時間來掃描需要丟棄的數據，辦法之一是限制用戶能夠翻頁的數量，實際對用戶體驗影響不大
維護索引和表
維護表的三個主要目的：1、找到并修復損壞的表? 2、維護準確的索引統計信息? 3、減少碎片
找到并修復損壞的表
損壞的索引會導致查詢返回錯誤的結果和莫須有的主鍵沖突等問題，嚴重時還會導致數據庫的崩潰。可以嘗試運行CHECK TABLE來檢查是否發生了表損壞。CHECK TABLE通常能找出大多數的表和索引的錯誤
可以使用REPAIR TABLE命令來修復損壞的表，但同樣不是所有的存儲引擎都支持該命令，如果不支持，也可通過一個不做任何操作的ALTER操作來重建表，例如修改表的存儲引擎為當前的引擎，如下是一個針對InnoDB表的例子
mysql ALTER TABLE innodb_tbl ENGINE = INNODB;
更新索引統計信息
MySQL的查詢優化器會通過兩個API來了解存儲引擎值的分布信息，以決定如何使用索引。第一個API是records_in_range()，通過向存儲引擎傳入兩個邊界值獲取在這個范圍內大概有多少條記錄。對于某些存儲引擎，該接口返回精確值，例如MyISAM，對于另一些存儲引擎則是一個估算值，例如InnoDB。第二個API是info()，該接口返回各種類型的數據，包括索引的基數（每個鍵值有多少條記錄）
如果存儲引擎向優化器提供的掃描信息是不準確的數據，或執行計劃本身太復雜以致無法準確地獲取各個階段匹配的行數，那么優化器會使用索引統計信息來估算掃描行數
每種存儲引擎實現索引統計信息的方式不同，所以需要進行ANALYZE TABLE的頻率也因不同的引擎而不同，每次運行的成本也不同
1、Memory引擎根本不存儲索引統計信息
2、2、MyISAM將索引統計信息存儲在磁盤中，ANALY TABLE需要進行一次全索引掃描來計算索引基數。在整個過程中需要鎖表
3、InnoDB不在磁盤存儲索引統計信息，通過隨機的索引訪問進行評估并將其存儲在內存中
可以使用SHOW INDEX FROM命令來查看索引的基數，如
減少索引和數據的碎片
碎片化的索引可能會以很差或無序的方式存儲在磁盤上
有三種類型的數據碎片
1、行碎片，指的是數據行被存儲為多個地方的多個片段中。即使查詢只以索引中訪問一行記錄，行碎片也會導致性能下降
2、行間碎片：邏輯上順序的頁，或行在磁盤上不是順序存儲的。行間碎片對諸如全表掃描和聚簇索引掃描之類的操作有很大影響，因為這些操作原來能夠以磁盤上順序存儲的數據中效益
3、剩余空間碎片：指數據頁有大量的空余空間，會導致服務讀取大量不需要的數據，從而造成浪費
查詢性能優化
為什么查詢速度會慢？
查詢的生命周期大致可以按照順序來看：從客戶端到服務器，在服務器上進行解析，生成執行計劃，執行，并返回結果給客戶端。執行是最重要的階段，其中包括了大量為了檢索數據列存儲引擎的調用以及調用后的數據處理，包括順序、分組等
完成這些任務時，查詢需要在不同的地方花費時間，包括網絡、CPU計算，生成統計信息和執行計劃、鎖等待等操作，尤其是向底層存儲引擎檢索數據的調用操作，這些調用需要在內存操作、CPU操作和內存不足時導致的I/O操作上消耗時間。根據存儲引擎不同，可能還會產生大量的上下文切換以及系統調用
慢查詢基礎：優化數據訪問
大部分性能低下的查詢都可以通過減少訪問的數據量的方式進行優化，對于低效的查詢，通過下面兩個步驟分析有效
1、確認應用程序是否在檢索大量超過需要的數據
2、確認MySQL服務器層是否在分析大量超過需要的數據行
是否向數據庫請求了不需要的數據
有些查詢會請求超過實際需要的數據，多余的數據會被應用程序丟棄，給MySQl服務器帶來額外的負擔，并增加網絡開銷，也會消耗應用服務器的CPU和內存資源
MySQL是否在掃描額外的記錄
對于MySQL，最簡單的衡量查詢開銷的三個指標如下：
1、響應時間? 2、掃描的行數? ?3、返回的行數
響應時間：服務時間和排隊時間之和，服務時間是指數據庫出列這個查詢真正花了多長時間，排隊時間指的是因為等待某些資源而沒有真正執行查詢的時間，可能是等I/O操作完成，可能是等待行鎖等
掃描的行數和返回的行數
掃描的行數在一定程度上能夠說明該查詢找到需要的數據的效率高不高，理想情況下掃描的行數和返回的行數應該是相同的，但實際上相同的情況并不多，例如在做一個關聯查詢時，服務器需要掃描多行才能生成結果集中的一行。掃描的行數對返回的行數的比率通常很小，一般在1：1和10：1之間
掃描的行數和訪問類型
訪問類型速度從慢到快，掃描的行數從小到大依次是：從全表掃描到索引掃描、范圍掃描、唯一索引查詢、常熟引用
數據庫sakila中的一個查詢案例
mysql> SELECT * FROM sakila.film_actor WHERE film_id = 1;
這個查詢將返回10行數據，從EXPLAIN的結果可以看到，MySQL在索引idx_fk_film_id上使用了ref訪問類型來執行查詢
EXPLAIN的結果也顯示MySQL預估需要訪問10行數據
一般MySQL能夠使用如下三種應用WHERE條件，從好到壞依次是
1、在索引使用WHERE條件來過濾不匹配的記錄，在存儲引擎層完成
2、使用索引覆蓋掃描來返回記錄，直接從索引中過濾不需要的記錄并返回命令中的結果，在MySQL服務器上完成，無須再回表查詢記錄
3、從數據表中返回數據，然后過濾不滿足條件的記錄，在MySQL服務器層完成，MySQL需要先從數據表讀出記錄然后過濾
如果發現查詢需要掃描大量的數據但只返回少數的行，以下方法可優化：
1、使用索引覆蓋掃描。把所有需要用的列都放到索引中，存儲引擎無須回表獲得對應的行就可以返回結果
2、改變庫表結構，例如使用單獨的匯總表
3、重寫這個復雜的查詢，讓MySQL優化器能夠以更優化的方式執行這個查詢
重構查詢的方式
切分查詢
定期地清除大量數據時，如果用一個大的語句一次性完成的話，則可能需要一次鎖住很多數據、沾滿整個事務日志、耗盡系統資源，阻塞很多小但重要的查詢。將一個大的DELETE語句切分成多個較小的查詢可以盡可能小的影響MySQL性能，同時還可以減少MySQL復制的延遲。例如，需要每個月運行一次下面的查詢
那么可以用類似下面的辦法來完成同樣的工作
一次刪除一萬行數據一般來說是一個比較高效而且對服務器影響也最小的做法，如果每次刪除數據后，都暫停一會再做下一次刪除，也可以將服務器上原本一次性的壓力分散到一個很長的時間中，可以大大降低對服務器的影響，還可以大大減少刪除時鎖的持有時間
分解關聯查詢
可以對每一個表進行一次單表查詢，然后將結果在應用程序中進行關聯，例如：
可以分解成以下查詢代替
分解關聯查詢有如下優勢
1、讓緩存的效率更高，應用程序可以方便地緩存單筆查詢對應的結果對象
2、將查詢分解后，執行單個查詢可以減少鎖的競爭
3、在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴招
4、查詢本身效率也可能會有所提升
5、可以減少冗余記錄的查詢
6、相當于在應用中實現了Hash關聯，而不是使用MySQL的嵌套循環，提高效率
查詢執行的基礎
1、客戶端發送一條查詢給服務器
2、服務器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果，否則進入下一階段
3、服務器端進行SQL解析，預處理，再由優化器生成對應的執行計劃
4、MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢
5、將結果返回給客戶端
MySQL客戶端/服務器通信協議
在任何一個時刻，要么是由服務器向客戶端發送數據，要么是由客戶端向服務器發送數據，兩個動作不能同時發生，無法也無須將一個消息切成小塊獨立來發送
協議讓MySQL通信簡單快速，但無法進行流量控制
查詢狀態：有很多種方式能查看當前的狀態，最簡單的是使用SHOW FULL PROCESSLIST命令，在一個查詢的生命周期中，狀態會變化很多次
sleep:線程正在等待客戶端發送新的請求
Query：線程正在執行查詢或正在將結果發送給客戶端
Locked：在MySQL服務器層，該線程正在等待表鎖。在存儲引擎實現的鎖，例如InnoDB的行鎖，并不會體現在線程狀態中
Analyzing and statistics：線程正在收集存儲引擎的統計信息，并生成查詢的執行計劃
Copying to tmp table [on disk]：線程正在執行查詢，并且將其結果集都復制到一個臨時表中，這種狀態一般要么是再做GROUP BY操作，要么是文件排序操作，或是UNION操作。如果這個狀態后有“on disk”標記，表示MySQL正在將一個內存臨時表放到磁盤上
Sorting result：線程正在對結果集進行排序
Sending data：表示多種情況：線程可能在多個狀態之間發送數據，或在生成結果集，或在向客戶端返回數據
查詢緩存
如果當前的查詢恰好命中了查詢緩存，在返回查詢結果之前MySQl會檢查一次用戶權限，無須解析查詢SQL語句，因為在查詢緩存中已存放了當前查詢需要訪問的表信息
查詢優化處理
查詢的生命周期的下一步是將一個SQL轉換成一個執行計劃，MySQL再依照這個執行計劃和存儲引擎進行交互，包括多個子階段：解析SQL、預處理、優化SQL執行計劃
語法解析器和預處理
首先，MySQL通過關鍵字將SQL語句進行解析，并聲稱一棵對應的“解析樹”。MySQL解析器將使用MySQL語句規則驗證和解析查詢，預處理器則根據一些MySQL規則進一步檢查解析樹是否合法
查詢優化器
優化器的作用是找到最好 的執行計劃
可以通過查詢當前會話的Last_query_cost的值來得知MySQL計算的當前查詢的成本
這個結果表示MySQL的優化器認為大概需要做1040個數據頁的隨機查找才能完成上面的查詢
有以下原因會導致MySQL優化器選擇錯誤的執行計劃：
1、統計信息不準確，MySQL依賴存儲引擎提供的統計信息來評估成本，但有的存儲引擎提供的信息是準確的，有的偏差可能非常大。例如，InnoDB因為其MVCC的架構，并不能維護一個數據表的行數的精確統計信息
2、執行計劃中的成本估算不等同于實際執行的成本
3、MySQL從不考慮其他并發執行的查詢，可能會影響到當前查詢的速度
4、MySQL并不是任何時候都基于成本的優化。有時也會基于一些固定的規則，例如，如果保存再全文搜索的MATCH（）子句，則在存在全文索引的時候就是用全文索引
5、MySQL不會考慮不受其控制的操作的成本，例如執行存儲過程或用戶自定義函數的成本
6、優化器有時無法去估算所有可能的執行計劃，所以它可能錯過實際上最優的執行計劃
優化策略可以簡單地分為兩種：靜態優化和動態優化
靜態優化可以直接對解析樹進行分析，并完成優化。例如，優化器可以通過一些簡單的代數變換將WHERE條件轉換成另一種等價形式。靜態優化在第一次完成后就一直有效，即使使用不同的參數重復執行也不會發生變化
動態優化則和查詢的上下文有關，也可能和很多其他因素有關，例如，WHERE條件中的取值、索引中條目對應的數據行數等
數據和索引的統計信息
因為服務器層沒有任何統計信息。MySQl查詢優化器在生成查詢的執行計劃時，需要向存儲引擎獲取相應的統計信息。存儲引擎則提供給優化器對應的統計信息，包括：每個表或索引有多少個頁面，每個表的每個索引的基數是多少、數據行和索引長度、索引的分布信息等
MySQl如何執行關聯查詢
MySQL先在一個表中循環取出單條數據，然后再嵌套循環到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止，然后根據各個表匹配的行，返回查詢中需要的各個列
例
假設MySQL按查詢中表順序進行關聯操作，可以用下面的偽代碼表示MySQL將如何完成
執行計劃：
MySQL生成查詢的一棵指令數，然后通過存儲引擎執行完成這棵指令樹并返回結果，最終的執行計劃包含了重構查詢的全部信息。如果對某個查詢執行EXPLAIN EXTENDED后，再執行SHOW WARNINGS，可以看到重構出的查詢
任何多表查詢都可以使用一棵樹表示，可以按如圖執行一個四表的關聯操作
關聯查詢優化器
決定了多個表關聯時的順序。通常多表關聯的時候，可以有多種不同的關聯順序獲得相同的執行結果
下面的查詢通過不同順序的關聯最后都獲得相同的結果
排序優化
當不能使用索引生成排序結果的時候，MySQL需要自己進行排序，如果數量小，則在內存中進行，如果數據大，則需要使用磁盤，MySQL將這個過程統一稱為文件排序
單次傳輸排序
先讀取查詢所需要的所有列，然后根據給定列進行排序，最后直接返回排序結果。優點是只需要依次順序I/O讀取所有的數據，無須任何的隨機I/O；缺點是如果需要返回的列非常多、非常大，會額外占用大量的空間，這些列對排序操作本身是沒有任何作用的
查詢執行引擎
MySQL的查詢執行引擎則根據這個執行計劃來完成整個查詢，執行計劃是一個數據結構
MySQL只是簡單地根據執行計劃給出的指令逐步執行，在根據執行計劃逐步執行的過程中，有大量的操作需要調用需要調用存儲引擎實現的接口來完成
返回結果給客戶端
即使查詢不需要返回結果集給客戶端，MySQL仍會返回這個查詢的一些信息，如該查詢影響到的行數。如果查詢可以被緩存，MySQL在這個階段也會將結果存放到查詢緩存中
MySQL查詢優化器的局限性
關聯子查詢
例如：希望找到sakila數據庫中，演員Penelope Guiness (actor_id為11)參數過的所有影片信息，按如下方式查詢實現：
MySQL會將相關的外層表壓到子查詢中，認為這樣可以更高效率地查找到數據行，如下圖
子查詢根據film_id來關聯外部表film，因為需要film_id字段，所以MySQL認為無法先執行這個子查詢，通過EXPLAIN可以看到子查詢是一個相關子查詢
根據EXPLAIN的輸出可以看到，MySQL先選擇對file表進行全表掃描，然后根據返回的film_id逐個執行子查詢
如何使用好關聯子查詢
例：
一般會建議使用在外連接重寫該查詢
例如：
執行計劃基本上一樣，如下是一些微小的區別：
1、表film_actor的訪問類型是DEPENDENT SUBQUERY，另一個是SIMPLE
2、對film表，第二個查詢的Extra中沒有Using where
3、在第二個表film_actor的執行計劃的Extra列有Not exist
UNION的限制
若希望UNION的各個子句能根據LIMIT只取部分結果集，或希望能夠先排好序再合并結果集的話，需要在UNION的各個子句中分別使用這些子句。例如，將兩個子查詢結果聯合起來，然后再取前20條記錄，MySQL會將兩個表都存放到同一個臨時表中，然后再取出前20行記錄
索引合并優化
當WHERE子句中包含多個復雜條件時，MySQL能夠訪問單個表的多個索引以合并和交叉過濾的方式來定位需要查找的行
等值傳遞
例：有一個非常大的IN()列表，而MySQL優化器發現存在WHERE、ON或USING的子句，將這個列表的值和另一個表的某個列相關列，優化器會將IN()列表都復制應用到關聯的各個表中
并行執行
MySQL無法利用多核特性來并行執行查詢，很多其他的關系型數據庫能夠提供這一特性，MySQL不能
Hash關聯
MySQL所有的關聯都是嵌套循環，可以通過建立一個Hash索引來曲線地實現Hash關系
松散索引掃描
MySQL的索引掃描需要先定義一個起點和終點，即使需要的數據只是這段索引中很少數的記幾個，MySQL仍需掃描這段索引中的每一個條目
最大值和最小值優化
對于MIN()和MAX()查詢，MySQL的優化并不好，例如：
因為在first_name字段上并沒有索引，所有MySQL將會進行一次全表掃描。如果MySQL能進行主鍵掃描，當MySQL讀到第一次滿足條件的記錄時，就是需要的最小值，因為主鍵是嚴格按照actor_id字段的大小順序排列的
在同一個表上查詢和更新
MySQL不允許對同一張表同時查詢和更新，如下是一個無法運行的SQL
可以通過使用生成表的形式繞過上面的限制，因為MySQL只會把這個表當作一個臨時表來處理，實際上，這執行了兩個查詢：一個是子查詢中的SELECT語句，另一個是多表關聯UPDATE，只是關聯的表是一個臨時表，子查詢會在UPDATE語句打開表之前完成
下面的查詢將會正常執行
優化特定類型的查詢
優化COUNT()查詢
COUNT()是一個特殊的函數，兩個作用：1、可以統計某個列值的數量，也可以統計行數。在統計列值時要求列值是非空的（不統計NULL）如果在COUNT()的括號中制定了列或列的表達式，則統計的是這個表達式有值的結果樹
2、統計結果集的行數，當MySQL確認括號內的表達式不可能為空時，實際上在統計行數
簡單的優化
可以使用MyISAM在COUNT(*)全表非常快的特性，來加速一些特定條件的COUNT()的查詢，如下，使用標準數據庫world來快速查找所有ID大于5的城市
mysql> SELECT COUNT(*) FROM world.City WHERE ID > 5;
通過SHOW STATUS的結果可以看到該查詢需要掃描4097行數據。如果將條件反轉，先查找ID小于5的城市，然后用總城市數一減就能得到同樣的結果，可以將掃描的行數減少到5行以內
mysql> SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*)
? ? ? ? ?-> FROM world.City WHERE ID<=5;
這一可以大大減少需要掃描的行數，因為在查詢優化階段會將其中的子查詢直接當作一個常熟來處理，通過EXPLAIN驗證
使用近似值
有時某些業務場景并不要求完全精確的COUNT值，可以用近似值來代替。EXPLAIN出的優化器估算的行數是一個近似值，執行EXPLAIN不需要真正地去執行查詢，成本較低
優化關聯查詢
1、確保ON或USING子句中的列上有索引，在創建索引時要考慮到關聯的順序。當表A和表B用列C關聯時，如果優化器的關聯順序是B、A，那就不需要在B表的對應列上建立索引
２、確保任何的GROUP BY? 和ORDER BY中的表達式只涉及到一個表中的列，MySQL才有可能使用索引來優化這個過程
3、升級SQL時需要注意：關聯語法、運算符優先級等
優化子查詢
建議盡可能使用關聯查詢代替
優化GROUP BY 和DISTINCT
這兩種類型都可以使用索引來優化。在MySQL中，當無法使用索引時，GROUP BY使用兩種策略來完成：使用臨時表或文件排序來做分組
例如
使用actor.actor_id列分組的效率比使用film_actor.actor_id更好
優化GROUP BY WITH ROLLUP
通過EXPLAIN來觀察其執行計劃，注意分組是否通過文件排序或臨時表實現，然后再去掉WITH ROLLUP子句看執行計劃是否相同
優化LIMIT分頁
盡可能地使用索引覆蓋掃描，而不是查詢所有的列，然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大時，效率提升很大，如下查詢
mysql> SELECT film_id ,description FROM sakila-film ORDER BY title LIMIT 50,5;
若這個表非常大，查詢最好改成下面這樣子
這里的“延遲關聯”將大大提升查詢效率，讓MySQL掃描盡可能少的頁面，獲取需要訪問的記錄后再根據關聯列回到原表查詢需要的所有列
優化UNION查詢
除非確實需要服務器消除重復的行，否則就一定要使用UNION ALL，如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，會導致對整個臨時表的數據做唯一性檢查，代價高昂，即使有ALL關鍵字，MySQL仍會使用臨時表存儲結果
靜態查詢分析
Percona Toolkit 中的pt_query_advisor能夠解析查詢日志、分析查詢模式，然后給出所有可能存在潛在問題的查詢，并給出足夠詳細的建議
使用用戶自定義變量
一個用來存儲內容的臨時容，在連接MySQL的整個過程都存在。可以使用下面的SET和SELECT語句定義
然后可以在任何可以使用表達式的地方使用這些自定義變量：
mysql> SELECT? _.WHERE col <= @last_week;
以下場景不能使用自動逸變量
1、使用自定義變量的查詢，無法使用查詢緩存
2、不能在使用常量或標識符的地方使用自定義變量，例如表名、列名和LIMIT子句中
3、用戶自定義變量的生命期在一個連接中有效，所以不能用其做連接間的通信
4、如果使用連接池或持久化連接，自定義變量可能讓看起來毫無關系的代碼發生交互
5、在5.0之前的版本，是大小寫敏感的，所以要注意代碼在不同MySQL版本間的兼容問題
6、不能顯式地聲明自定義變量的類型
優化排名語句
可以給一個變量賦值的同時使用這個變量，例如
避免重復查詢剛剛更新的數據
例如：一個客戶希望能夠更高效的更新一條記錄的時間戳，同時希望查詢當前記錄中存放的時間戳是什么，可以用以下代碼實現
使用變量，可以按如下方式重寫查詢
統計更新和插入數量
當每次由于沖突導致更新時對變量@X自增一次，然后通過對表達式乘以0來讓其不影響要更新的內容
確定取值的順序
如下查詢
因為WHERE和SELECT是在查詢執行的不同階段被執行的，如果在查詢中再加入ORDER BY的話，結果可能會不同
OEDER BY引入了文件排序，WHERE條件是在文件排序操作之前取值的，所以這條查詢會返回表中的全部記錄。解決問題的辦法是讓變量的賦值和取值發生執行查詢的同一階段
用戶自定義變量的其他用處
1、查詢運行時計算總數和平均值
2、模擬GROUP語句中的函數FIRST()和LAST()
3、對大量數據做一些數據計算
4、計算一個大表的MD5散列值
5、編寫一個樣本處理的函數，當樣本中數值超過某個邊界值時將其變成0
6、模擬讀/寫游標
7、在SHOW語句的WHERE子句中加入變量值
總結
- 上一篇： 宠物之家网站大学生网页制作教程 学生HT
- 下一篇： Hexo个人博客NexT主题添加Loca