深入淺出MySQL

<a href="#0-%e6%9c%ac%e6%96%87%e9%93%be%e6%8e%a5">本文連結</a>

<a href="#1-%e8%af%b4%e6%98%8e">說明</a>

<a href="#2-%e7%b4%a2%e5%bc%95%e7%9a%84%e8%ae%be%e8%ae%a1%e5%92%8c%e4%bd%bf%e7%94%a8">索引的設計和使用</a>

<a href="#1-%e8%ae%be%e8%ae%a1%e7%b4%a2%e5%bc%95%e5%8e%9f%e5%88%99">設計索引原則</a>

<a href="#2-%e5%b0%8f%e5%b8%b8%e8%af%86">小常識</a>

<a href="#3-btree%e7%b4%a2%e5%bc%95">btree索引</a>

<a href="#3-sql%e4%b8%ad%e7%9a%84%e5%ae%89%e5%85%a8%e9%97%ae%e9%a2%98">sql中的安全問題</a>

<a href="#4-%e5%b8%b8%e7%94%a8sql%e6%8a%80%e5%b7%a7">常用sql技巧</a>

<a href="#5-sql%e4%bc%98%e5%8c%96%e8%bf%87%e7%a8%8b">sql優化過程</a>

<a href="#1-%e4%ba%86%e8%a7%a3sql%e6%89%a7%e8%a1%8c%e9%a2%91%e7%8e%87">了解sql執行頻率</a>

<a href="#2-explain%e5%88%86%e6%9e%90">explain分析</a>

<a href="#3-%e6%9f%a5%e7%9c%8b%e7%b4%a2%e5%bc%95%e4%bd%bf%e7%94%a8%e6%83%85%e5%86%b5">檢視索引使用情況</a>

<a href="#4-%e5%ae%9a%e6%9c%9f%e5%88%86%e6%9e%90%e8%a1%a8%e5%92%8c%e6%a3%80%e6%9f%a5%e8%a1%a8">定期分析表和檢查表</a>

<a href="#5-%e5%ae%9a%e6%9c%9f%e4%bc%98%e5%8c%96%e8%a1%a8">定期優化表</a>

<a href="#6-%e5%b8%b8%e7%94%a8sql%e4%bc%98%e5%8c%96">常用sql優化</a>

<a href="#6-%e4%bc%98%e5%8c%96%e6%95%b0%e6%8d%ae%e5%ba%93%e5%af%b9%e8%b1%a1">優化資料庫對象</a>

<a href="#1-%e4%bc%98%e5%8c%96%e8%a1%a8%e7%9a%84%e6%95%b0%e6%8d%ae%e7%b1%bb%e5%9e%8b">優化表的資料類型</a>

<a href="#2-%e9%80%9a%e8%bf%87%e6%8b%86%e5%88%86%e8%a1%a8%e6%8f%90%e9%ab%98%e8%a1%a8%e7%9a%84%e8%ae%bf%e9%97%ae%e6%95%88%e7%8e%87">通過拆分表提高表的通路效率</a>

<a href="#3-%e4%bd%bf%e7%94%a8%e4%b8%ad%e9%97%b4%e8%a1%a8%e6%8f%90%e4%be%9b%e7%bb%9f%e8%ae%a1%e6%9f%a5%e8%af%a2%e9%80%9f%e5%ba%a6">使用中間表提供統計查詢速度</a>

<a href="#7-%e9%94%81%e9%97%ae%e9%a2%98">鎖問題</a>

<a href="#1-%e9%94%81">鎖</a>

<a href="#2-myisam%e8%a1%a8%e9%94%81">myisam表鎖</a>

<a href="#3-innodb%e9%94%81">innodb鎖</a>

本站:

<a href="http://blog.csdn.net/alex_my/article/details/72626925">深入淺出mysql</a>

個人部落格:

<a href="http://alex-my.xyz/books/database/%e6%b7%b1%e5%85%a5%e6%b5%85%e5%87%bamysql">深入淺出mysql</a>

之前先看了《mysql必知必會》一書，再來看這本。二者很多内容相同，是以本書隻節選了部分内容來看。

最适合索引的列是出現在<code>where/join/order by/group by/distinct</code>中的列。而不是出現在select中的列。

使用唯一索引。索引的基數越大，效果越好。比如存放身份号碼的列具有不同的值，很容易區分各行。而用來記錄性别的列，隻含有’m’和’f’，對這樣的列進行索引，沒多大用處。

使用短索引。如果索引的值很長，那麼查詢的速度會受到影響。

利用最左索引。假設建立了user_id, user_name, status(按該順序建立)複合索引， 實際上是建立了三個mysql可利用的索引。

隻要在查詢中指定了user_id的值，無論是否有user_name或者status，mysql都可以使用這個索引。但是，如果不包含user_id，隻包含了user_name或status，那麼，mysql不能利用這個索引。

限制索引的數目。并非索引越多越好。除了要占用額外的磁盤空間外，還會降低寫操作的性能。在修改表的時候，索引必須進行更新，索引越多，所花的時間也越多。

删除不再使用或者很少使用的索引。進而減少對表更新操作的影響。

系統自動建立primary key的索引。

系統自動建立unique key的索引。

myisam和innodb預設建立的都是btree索引。

當使用<code>&gt;,&lt;,&gt;=,&lt;=,between,!=,&lt;&gt;或者like 'pattern'(pattern不以通配符開始)</code>操作符時，都可以使用相關列上的索引。

sql注入攻擊

但如果沒有任何過濾，傳入的<code>user_name=admin'#</code>，就會出現大問題。

因為<code>#</code>會将後面的句子注釋。是以密碼驗證功能就丢失了。

又如傳入<code>user_name=admin' or 1=1</code>

防範

使用preparestatement，預編譯sql後，通過綁定參數來執行。

對特殊字元進行轉換

定義函數對輸入進行校驗。

使用rand()擷取随随即行

使用show [session | global] statusa來獲得伺服器狀态資訊。

session表示目前連接配接，如果直接寫show status, 則預設是session。

global表示從資料庫上次啟動至今的統計結果。

在結果中可以看下

通過以上資訊，就可以了解到是以更新為主還是查詢為主了。

假設有一張account表存在id(主鍵), phone字段,且未對phone建立索引。

通過id查找:

輸出:

我們可以看到，rows=1, 表示掃描了1行接得到了結果。

通過phone查找:

可以看到，掃描了48130行才得到想要的結果。

如果這個查詢很常用，就有必要對phone建立索引，特别是account表很龐大的時候，速度優勢很明顯。

explain顯示了mysql如何使用索引來處理select語句以及連接配接表。可以幫助選擇更好的引擎和寫出更佳的查詢語句。

select_type, select的類型，有以下幾種值:

simple: 表示簡單的select，沒有union和子查詢。

primary: 查詢中包含任何複雜的子查詢, 最外層被标記為primary。

<code>示例a</code>

subquery: 在<code>示例a</code>中, 子句就被标記為subquery。

derived: 在from清單中的子查詢被标記為derived。mysql會将這個子查詢的結果放到一個臨時表中，相當于該臨時表是從子查詢中派生出來的。

union: 若第二個select出現在union之後,就會被标記為union。

<code>示例b</code>

union result: 從union擷取結果被标記為union result。見<code>示例b</code>的結果。

subquery和union還可以被标記為dependent和uncacheable

dependent: 意味着子句依賴于外層發現的結果，見<code>示例a</code>。

uncacheable: 意味着select某些特性阻止結果緩存于一個item_cache中(<code>todo:需要更多的資料</code>)。

type, 表示在表中找到所需行的方式，也稱通路類型。

常見有<code>all, index, range, ref, eq_ref, const, system, null</code>, 從左到右，性能從最差到最好。

null: 執行時甚至不用通路表或使用索引，比如找出找出最小使用者id（主鍵）

const: 表中最多隻有一個比對行，用于primary key或者unique索引。因為隻比對一行，是以速度快。

system: 是const的特殊類型，為表中隻有一行的時候。

eq_ref: 簡單的說就是在多表連接配接中使用primary key和unique key做為關聯條件。

ref: 搜尋時使用的索引不是primary key或unique，但可以是複合索引的第一個值。

<code>以上都是很理想的索引使用情況</code>。

range: 用索引來檢索一定範圍的行, between, &lt;, &gt;。除此之外, in, or也會顯示range，但性能有差異。

index: 隻查找索引，不能包含非索引值。

all: 将周遊全表以查找比對的行。

possible_keys: 提示使用了哪些索引可以找到該行。

keys: 使用的索引。

key_len: 索引使用的長度。

ref: 哪些列或常量被用于查找索引列上的值。

子句中使用a.role_id來查找比對。

rows: 估算的找到所需的記錄所需要讀取的行數。

filtered: 顯示了通過條件過濾出的行數的百分比估計值。

extra: 比較重要的額外資訊。

handler_read_key: 這個值表示一個行被索引值讀的次數，很低的值表示增加的索引對性能改善不高，因為索引并不經常使用。

handler_read_rnd_next: 表示在資料檔案中讀下一行的請求書。如果值很高，表示進行了大量的掃描。通常說明索引不正确或寫入的查詢沒有利用索引。

按照搜尋出的資料，這個庫的索引情況并不理想。

分析表 analyze table account;

檢查表 check table account;

輸出: <code>1 client is using or hasn't closed the table properly</code>

修複該錯誤:

優化表 optimize table account, user, …

該指令可以将表中的空間碎片進行合并，并且可以消除由于删除或者更新造成的更新浪費。

如果已經删除了表中的一大部分，或者對含有可變長度行的表進行了很多更改，可以使用該指令進行優化。

該指令支隊myisam,bdb,innodb起作用。

優化insert語句

插入多行時,盡量使用多個值表的insert語句，減少用戶端與服務端的連結，關閉消耗。

使用insert delayed into替代insert into。僅限于isam和myisam表。需要插入的資料會在記憶體中排隊，直到有空閑。而用戶端會立即得到ok響應。好處是極高的插入速度，用戶端不需要等待太長的時間。壞處是如果沒有來得及插入資料，在記憶體隊列中的資料将會丢失，而且不能反悔自動遞增id。

批量插入時，可以增加bulk_insert_buffer_size變量值來提高速度，隻有myisam有效。這個參數是批量插入緩存大小，預設8m。

使用load data infile載入(未使用過)

優化group by語句

在包含了group by語句，會有一個預設的排序，如果沒有必要對結果進行排序，可以用order by null取消排序。

優化order by語句

當以下情況時,order by也可以借助索引來排序

索引滿足where和order by

order by的順序和索引順序相同

order by都是升序或者降序的

<code>todo 需要更多的資料</code>

優化嵌套查詢

使用join來替代子查詢，速度會快很多，尤其是對子查詢中的列建有索引的情況下，性能會更好。

mysql不需要再記憶體中建立臨時表來完成這個邏輯上需要兩個步驟的工作。

使用sql提示

use index: 添加希望mysql去參考的索引清單

ignore index: 忽略指定的索引

假設就這一個索引，忽略之後，mysql會使用全表掃描

force index: 強制使用指定索引

大利器procedure analyse()

用法 <code>select ... from ... where ... procedure analyse([max_elements,[max_memory]])</code>

max_elements 預設值256, 查找每一列不同值時所需關注的最大不同值的數量。還用這個值來檢查給予建議的值是否是enum。

max_memory 查找每一列所有不同值時可能配置設定的最大的記憶體數量。

示例 <code>select * from user procedure analyse(16, 256);</code>,注意看optimal_fieldtype中給予的建議。如果表中資料量小，要注意區分建議的局限性。

對于myisam類型的表:

* 垂直拆分: 把主鍵和一些列放在一張表，把主鍵和另一些列放在另一張表。特别在一張表中，有的列常用，而有些列不常用的時候，可以這麼拆。好處是使得資料行變小，查詢的時候會減少i/o次數。缺點是查詢所有資料的時候要join操作。

* 水準拆分: 把很大的表拆成好幾張，比如最近3個月的資料一張，3個月以前的表一張。

如果表很大，要在上面對一定範圍内的資料做查詢統計。可以把這部分資料導出到另一張一模一樣的表中，然後在做處理。

這邊省略了導出的時間。

在新表上處理，不會對應用産生負面影響。

可以在新表上增加索引，臨時字段等，提供性能。

表級鎖: 開銷小，加鎖快，不會出現死鎖，鎖定粒度大，引起沖突機率最高，并發度最低。

行級鎖：開銷大，加鎖慢，會出現思索，鎖定粒度最小，引起沖突機率最低，并發度最高。

頁面所: 會出現死鎖，介于表級鎖和行級鎖中間。

myisam支援表級鎖。

innodb支援表級鎖和行級鎖，預設采用行級鎖。

僅從鎖的角度來說:

表級鎖更适合以查詢為主，隻有少量按索引條件更新資料的應用。

行級鎖适合有大量按索引條件并發更新資料的應用。

可以通過<code>show status like 'table_locks_%';</code>來檢視表鎖定争奪。

如果table_locks_waited值比較高，說明存在着較嚴重的表級鎖競争。

加讀鎖: 不會阻塞其它使用者對表的讀操作，但是，會阻塞對該表的寫操作，包括加鎖的這個使用者。

加寫鎖: 隻有加鎖的這個使用者可以對表進行讀寫操作，其它使用者讀寫操作都會等待。

示例:

myisam會在select前給鎖設計的表添加讀鎖。在update,insert,delete時，加寫鎖。

myisam會一次獲得所需要的鎖，不會變更。比如獲得了讀鎖，則不能執行寫操作。

并發插入

concurrent_insert 用于控制并發插入行為。<code>show variables like 'concurrent_insert';</code>

當concurrent_insert=0的時候，不允許并發插入。

當concurrent_insert=1的時候，表中間沒有被删除的行，加讀鎖的時候，允許另一個使用者從表尾插入資料，這個也是預設設定。

當concurrent_insert=2的時候，無論表中間有沒有删除的行，都允許另一個使用者從表尾插入資料。

我這邊顯示的是concurrent_insert=auto, 行為和concurrent_insert=1一樣。

可以在空閑的時候使用optimize table來整理空間碎片，收回因删除記錄而産生的中間空洞。

鎖排程

myisam的讀寫是互斥的，串行的。

假設同時有讀和寫請求到來，寫會獲得鎖。

即使讀請求先在擷取鎖的等待隊列中，寫請求後到，寫會獲得鎖。

因為mysql認為寫操作比讀操作重要。

這也是myisam不适合有大量更新操作同時又有很多讀操作的原因。

大量的寫操作會造成讀操作難以獲得鎖。

可以使用一些設定來調節這些行為:

啟動時指定參數low-priority-updates，使myisam預設給予讀請求以優先的權利。

set low_priority_updates=1,使得該連接配接發出的寫操作優先級降低。

通過指定insert,update,delete語句的low_priority屬性,降低該語句的優先級。

給系統參數max_write_lock_count指定一定的值，當讀請求達到這個值後，就将寫請求的優先級降低。

<code>todo</code>