mysql 常見問題

事務級別

 SQL標準定義了4類隔離級別，包括了一些具體規則，用來限定事務內外的哪些改變是可見的，哪些是不可見的。低級別的隔離級一般支持更高的并發處理，并擁有更低的系統開銷。
Read Uncommitted（讀取未提交內容）

       在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。本隔離級別很少用于實際應用，因為它的性能也不比其他級別好多少。讀取未提交的數據，也被稱之為臟讀（Dirty Read）。
Read Committed（讀取提交內容）

       這是大多數數據庫系統的默認隔離級別（但不是MySQL默認的）。它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。這種隔離級別 也支持所謂的不可重復讀（Nonrepeatable Read），因為同一事務的其他實例在該實例處理其間可能會有新的commit，所以同一select可能返回不同結果。
Repeatable Read（可重讀）

       這是MySQL的默認事務隔離級別，它確保同一事務的多個實例在并發讀取數據時，會看到同樣的數據行。不過理論上，這會導致另一個棘手的問題：幻讀 （Phantom Read）。簡單的說，幻讀指當用戶讀取某一范圍的數據行時，另一個事務又在該范圍內插入了新行，當用戶再讀取該范圍的數據行時，會發現有新的“幻影” 行。InnoDB和Falcon存儲引擎通過多版本并發控制（MVCC，Multiversion Concurrency Control）機制解決了該問題。

Serializable（可串行化） 
       這是最高的隔離級別，它通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題。簡言之，它是在每個讀的數據行上加上共享鎖。在這個級別，可能導致大量的超時現象和鎖競爭。

         這四種隔離級別采取不同的鎖類型來實現，若讀取的是同一個數據的話，就容易發生問題。例如：

         臟讀(Drity Read)：某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由于某些原因，前一個RollBack了操作，則后一個事務所讀取的數據就會是不正確的。

         不可重復讀(Non-repeatable read):在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的數據。

         幻讀(Phantom Read):在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾列(Row)數據，而另一個事務卻在此時插入了新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的。

         在MySQL中，實現了這四種隔離級別，分別有可能產生問題如下所示：

索引無效：

索引并不是時時都會生效的，比如以下幾種情況，將導致索引失效：

    1.如果條件中有or，即使其中有條件帶索引也不會使用(這也是為什么盡量少用or的原因)

注意：要想使用or，又想讓索引生效，只能將or條件中的每個列都加上索引

　　2.對于多列索引，不是使用的第一部分，則不會使用索引

　　3.like查詢是以%開頭

    4.如果列類型是字符串，那一定要在條件中將數據使用引號引用起來,否則不使用索引

　5.如果mysql估計使用全表掃描要比使用索引快,則不使用索引

此外，查看索引的使用情況
show status like ‘Handler_read%';
大家可以注意：
handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數
handler_read_rnd_next:這個值越高，說明查詢低效

索引失效的情形總結如下：

• 請求表上的數據行超出表總記錄數30%，變成全表掃描

• 謂詞上的索引列上存在NULL值

• 謂詞上的索引列條件使用函數

• 謂詞上的索引列條件進行了相關運算

• 謂詞上的索引列條件上使用了<>，NOT IN操作符

• 復合索引中，第一個索引列使用范圍查詢--只能用到部份或無法使用索引

• 復合索引中，第一個查詢條件不是最左索引列

• 模糊查詢條件列最左以通配符%開始

• 內存表（HEAP表）使用HASH索引時，使用范圍檢索或者ORDER BY

• 表關聯字段類型不一樣（包括某些長度不一樣，但像varchar(10)與char(10)則可以，MYSQL經過內部優化處理）

索引類型（按用途非嚴格劃分）

• 普通索引，這是最基本的索引，無任何限制

• 唯一索引，與普通索引類似，索引列值必須唯一，允許NULL值

• 全文索引，基于詞干方式創建索引，多用于BLOB數據類型

• 單列索引，僅基于一列創建的索引

• 多列索引，基于多列創建的索引，列順序非常重要

• 空間索引，用作地理數據存儲

• 主鍵索引，是一種特殊的唯一索引，不允許有NULL值，通常在建表時創建。

索引的優缺點

       索引的優點

• 大大減少了服務器需要掃描的數據量

• 可以幫助服務器避免排序或減少使用臨時表排序

• 索引可以隨機I/O變為順序I/O

       索引的缺點

• 需要占用磁盤空間，因此冗余低效的索引將占用大量的磁盤空間

• 降低DML性能，對于數據的任意增刪改都需要調整對應的索引，甚至出現索引分裂

• 索引會產生相應的碎片，產生維護開銷

復合索引：

聯合索引又叫復合索引。對于復合索引：Mysql從左到右的使用索引中的字段，一個查詢可以只使用索引中的一部份，但只能是最左側部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3種組合進行查找，但不支持 b,c進行查找 .當最左側字段是常量引用時，索引就十分有效。

    兩個或更多個列上的索引被稱作復合索引。

    利用索引中的附加列，您可以縮小搜索的范圍，但使用一個具有兩列的索引 不同于使用兩個單獨的索引。復合索引的結構與電話簿類似，人名由姓和名構成，電話簿首先按姓氏對進行排序，然后按名字對有相同姓氏的人進行排序。如果您知 道姓，電話簿將非常有用；如果您知道姓和名，電話簿則更為有用，但如果您只知道名不姓，電話簿將沒有用處。

    所以說創建復合索引時，應該仔細考慮列的順序。對索引中的所有列執行搜索或僅對前幾列執行搜索時，復合索引非常有用；僅對后面的任意列執行搜索時，復合索引則沒有用處。

    如：建立 姓名、年齡、性別的復合索引。

    create table test（

    a int,

    b int,

    c int,

    KEY a（a,b,c）

    ）；

    優： select * from test where a=10 and b>50

    差： select * from test where a50

    優： select * from test order by a

    差： select * from test order by b

    差： select * from test order by c

    優： select * from test where a=10 order by a

    優： select * from test where a=10 order by b

    差： select * from test where a=10 order by c

    優： select * from test where a>10 order by a

    差： select * from test where a>10 order by b

    差： select * from test where a>10 order by c

    優： select * from test where a=10 and b=10 order by a

    優： select * from test where a=10 and b=10 order by b

    優： select * from test where a=10 and b=10 order by c

    優： select * from test where a=10 and b=10 order by a

    優： select * from test where a=10 and b>10 order by b

    差： select * from test where a=10 and b>10 order by c

    索引原則

    1.索引越少越好

    原因：主要在修改數據時，第個索引都要進行更新，降低寫速度。

    2.最窄的字段放在鍵的左邊

    3.避免file sort排序，臨時表和表掃描。

B 樹，索引

B+/-Tree原理

B-Tree介紹

B-Tree是一種多路搜索樹（并不是二叉的）：
       1.定義任意非葉子結點最多只有M個兒子；且M>2；
       2.根結點的兒子數為[2, M]；
       3.除根結點以外的非葉子結點的兒子數為[M/2, M]；
       4.每個結點存放至少M/2-1（取上整）和至多M-1個關鍵字；（至少2個關鍵字）
       5.非葉子結點的關鍵字個數=指向兒子的指針個數-1；
       6.非葉子結點的關鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
       7.非葉子結點的指針：P[1], P[2], …, P[M]；其中P[1]指向關鍵字小于K[1]的子樹，P[M]指向關鍵字大于K[M-1]的子樹，其它P[i]指向關鍵字屬于(K[i-1], K[i])的子樹；
       8.所有葉子結點位于同一層；
       如：（M=3）

B-樹的特性：
       1.關鍵字集合分布在整顆樹中；
       2.任何一個關鍵字出現且只出現在一個結點中；
       3.搜索有可能在非葉子結點結束；
       4.其搜索性能等價于在關鍵字全集內做一次二分查找；
       5.自動層次控制；

B-樹的搜索，從根結點開始，對結點內的關鍵字（有序）序列進行二分查找，如果命中則結束，否則進入查詢關鍵字所屬范圍的兒子結點；重復，直到所對應的兒子指針為空，或已經是葉子結點；

B+Tree介紹

B+樹是B-樹的變體，也是一種多路搜索樹：

       1.其定義基本與B-樹同，除了：

       2.非葉子結點的子樹指針與關鍵字個數相同；

       3.非葉子結點的子樹指針P[i]，指向關鍵字值屬于[K[i], K[i+1])的子樹（B-樹是開區間）；

       5.為所有葉子結點增加一個鏈指針；

       6.所有關鍵字都在葉子結點出現；

       如：（M=3）

B+的搜索與B-樹也基本相同，區別是B+樹只有達到葉子結點才命中（B-樹可以在非葉子結點命中），其性能也等價于在關鍵字全集做一次二分查找；

       B+的特性：

       1.所有關鍵字都出現在葉子結點的鏈表中（稠密索引），且鏈表中的關鍵字恰好是有序的；

       2.不可能在非葉子結點命中；

       3.非葉子結點相當于是葉子結點的索引（稀疏索引），葉子結點相當于是存儲（關鍵字）數據的數據層；

       4.更適合文件索引系統；

mysql中的索引

MySQL中普遍使用B+Tree做索引，但在實現上又根據聚簇索引和非聚簇索引而不同。

聚簇索引

所謂聚簇索引，就是指主索引文件和數據文件為同一份文件，聚簇索引主要用在Innodb存儲引擎中。在該索引實現方式中B+Tree的葉子節點上的data就是數據本身，key為主鍵，如果是一般索引的話，data便會指向對應的主索引，如下圖所示：

在B+Tree的每個葉子節點增加一個指向相鄰葉子節點的指針，就形成了帶有順序訪問指針的B+Tree。做這個優化的目的是為了提高區間訪問的性能，例如圖4中如果要查詢key為從18到49的所有數據記錄，當找到18后，只需順著節點和指針順序遍歷就可以一次性訪問到所有數據節點，極大提到了區間查詢效率。

非聚簇索

非聚簇索引就是指B+Tree的葉子節點上的data，并不是數據本身，而是數據存放的地址。主索引和輔助索引沒啥區別，只是主索引中的key一定得是唯一的。主要用在MyISAM存儲引擎中，如下圖：

非聚簇索引比聚簇索引多了一次讀取數據的IO操作，所以查找性能上會差。

Myisam索引與InnoDB索引相比較

• MyisAM支持全文索引（FULLTEXT）、壓縮索引，InnoDB不支持；

• InnoDB支持事務，MyisAM不支持；

• MyisAM順序儲存數據，索引葉子節點保存對應數據行地址，輔助索引很主鍵索引相差無幾；InnoDB主鍵節點同時保存數據行，其他輔助索引保存的是主鍵索引的值；

• MyisAM鍵值分離，索引載入內存（key_buffer_size），數據緩存依賴操作系統；InnoDB鍵值一起保存，索引與數據一起載入InnoDB緩沖池；MyisAM主鍵（唯一）索引按升序來存儲存儲，InnoDB則不一定

• MyisAM索引的基數值（Cardinality，show index 命令可以看見）是精確的，InnoDB則是估計值。這里涉及到信息統計的知識，MyisAM統計信息是保存磁盤中，在alter表或Analyze table操作更新此信息，而InnoDB則是在表第一次打開的時候估計值保存在緩存區內；

• MyisAM處理字符串索引時用增量保存的方式，如第一個索引是‘preform’，第二個是‘preformence’，則第二個保存是‘7，ance’，這個明顯的好處是縮短索引，但是缺陷就是不支持倒序提取索引，必須順序遍歷獲取索引

為什么選用B+/-Tree

一般來說，索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上。這樣的話，索引查找過程中就要產生磁盤I/O消耗，相對于內存存取，I/O存取的消耗要高幾個數量級，所以評價一個數據結構作為索引的優劣最重要的指標就是在查找過程中磁盤I/O操作次數的漸進復雜度。換句話說，索引的結構組織要盡量減少查找過程中磁盤I/O的存取次數。

簡單點說說內存讀取，內存是由一系列的存儲單元組成的，每個存儲單元存儲固定大小的數據，且有一個唯一地址。當需要讀內存時，將地址信號放到地址總線上傳給內存，內存解析信號并定位到存儲單元，然后把該存儲單元上的數據放到數據總線上，回傳。

寫內存時，系統將要寫入的數據和單元地址分別放到數據總線和地址總線上，內存讀取兩個總線的內容，做相應的寫操作。

內存存取效率，跟次數有關，先讀取A數據還是后讀取A數據不會影響存取效率。而磁盤存取就不一樣了，磁盤I/O涉及機械操作。磁盤是由大小相同且同軸的圓形盤片組成，磁盤可以轉動(各個磁盤須同時轉動)。磁盤的一側有磁頭支架，磁頭支架固定了一組磁頭，每個磁頭負責存取一個磁盤的內容。磁頭不動，磁盤轉動，但磁臂可以前后動，用于讀取不同磁道上的數據。磁道就是以盤片為中心劃分出來的一系列同心環(如圖標紅那圈)。磁道又劃分為一個個小段，叫扇區，是磁盤的最小存儲單元。

磁盤讀取時，系統將數據邏輯地址傳給磁盤，磁盤的控制電路會解析出物理地址，即哪個磁道哪個扇區。于是磁頭需要前后移動到對應的磁道，消耗的時間叫尋道時間，然后磁盤旋轉將對應的扇區轉到磁頭下，消耗的時間叫旋轉時間。所以，適當的操作順序和數據存放可以減少尋道時間和旋轉時間。
為了盡量減少I/O操作，磁盤讀取每次都會預讀，大小通常為頁的整數倍。即使只需要讀取一個字節，磁盤也會讀取一頁的數據(通常為4K)放入內存，內存與磁盤以頁為單位交換數據。因為局部性原理認為，通常一個數據被用到，其附近的數據也會立馬被用到。

B-Tree：如果一次檢索需要訪問4個節點，數據庫系統設計者利用磁盤預讀原理，把節點的大小設計為一個頁，那讀取一個節點只需要一次I/O操作，完成這次檢索操作，最多需要3次I/O(根節點常駐內存)。數據記錄越小，每個節點存放的數據就越多，樹的高度也就越小，I/O操作就少了，檢索效率也就上去了。

B+Tree：非葉子節點只存key，大大滴減少了非葉子節點的大小，那么每個節點就可以存放更多的記錄，樹更矮了，I/O操作更少了。所以B+Tree擁有更好的性能

InnoDB與Myisam

二者之間有六大區別：

MySQL存儲引擎MyISAM與InnoDB如何選擇

MySQL有多種存儲引擎，每種存儲引擎有各自的優缺點，可以擇優選擇使用：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

雖然MySQL里的存儲引擎不只是MyISAM與InnoDB這兩個，但常用的就是它倆了。可能有站長并未注意過MySQL的存儲引擎，其實存儲引擎也是數據庫設計里的一大重要點，那么博客系統應該使用哪種存儲引擎呢？

下面我們分別來看兩種存儲引擎的區別。

一、InnoDB支持事務，MyISAM不支持，這一點是非常之重要。事務是一種高級的處理方式，如在一些列增刪改中只要哪個出錯還可以回滾還原，而MyISAM就不可以了。

二、MyISAM適合查詢以及插入為主的應用，InnoDB適合頻繁修改以及涉及到安全性較高的應用

三、InnoDB支持外鍵，MyISAM不支持

四、從MySQL5.5.5以后，InnoDB是默認引擎

五、InnoDB不支持FULLTEXT類型的索引

六、InnoDB中不保存表的行數，如select count(*) from table時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數即可。注意的是，當count(*)語句包含where條件時MyISAM也需要掃描整個表

七、對于自增長的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立聯合索引

八、清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表

九、InnoDB支持行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like '%lee%'

通過以上九點區別，結合個人博客的特點，推薦個人博客系統使用MyISAM，因為在博客里主要操作是讀取和寫入，很少有鏈式操作。所以選擇MyISAM引擎使你博客打開也頁面的效率要高于InnoDB引擎的博客，當然只是個人的建議，大多數博客還是根據實際情況下謹慎選擇。

關于MyISAM與InnoDB選擇使用：

MYISAM和INNODB是Mysql數據庫提供的兩種存儲引擎。兩者的優劣可謂是各有千秋。INNODB會支持一些關系數據庫的高級功能，如事務功能和行級鎖，MYISAM不支持。MYISAM的性能更優，占用的存儲空間少。所以，選擇何種存儲引擎，視具體應用而定。

如果你的應用程序一定要使用事務，毫無疑問你要選擇INNODB引擎。但要注意，INNODB的行級鎖是有條件的。在where條件沒有使用主鍵時，照樣會鎖全表。比如DELETE FROM mytable這樣的刪除語句。

如果你的應用程序對查詢性能要求較高，就要使用MYISAM了。MYISAM索引和數據是分開的，而且其索引是壓縮的，可以更好地利用內存。所以它的查詢性能明顯優于INNODB。壓縮后的索引也能節約一些磁盤空間。MYISAM擁有全文索引的功能，這可以極大地優化LIKE查詢的效率。

有人說MYISAM只能用于小型應用，其實這只是一種偏見。如果數據量比較大，這是需要通過升級架構來解決，比如分表分庫，而不是單純地依賴存儲引擎。

其他一些說法：

現在一般都是選用innodb了，主要是myisam的全表鎖，讀寫串行問題，并發效率鎖表，效率低myisam對于讀寫密集型應用一般是不會去選用的。

關于Mysql數據庫默認的存儲引擎：

MyISAM和InnoDB是MySQL的兩種存儲引擎。如果是默認安裝，那就應該是InnoDB，你可以在my.ini文件中找到default-storage-engine=INNODB；當然你可以在建表時指定相應的存儲引擎。通過show create table xx 可以看見相應信息。

Mysql中InnoDB和MyISAM的比較

MyISAM：

        每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。.frm文件存儲表定義。數據文件的擴展名為.MYD (MYData)。

        MyISAM表格可以被壓縮，而且它們支持全文搜索。不支持事務，而且也不支持外鍵。如果事物回滾將造成不完全回滾，不具有原子性。在進行updata時進行表鎖，并發量相對較小。如果執行大量的SELECT，MyISAM是更好的選擇。

        MyISAM的索引和數據是分開的，并且索引是有壓縮的，內存使用率就對應提高了不少。能加載更多索引，而Innodb是索引和數據是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小

        MyISAM緩存在內存的是索引，不是數據。而InnoDB緩存在內存的是數據，相對來說，服務器內存越大，InnoDB發揮的優勢越大。

優點：查詢數據相對較快，適合大量的select，可以全文索引。

缺點：不支持事務，不支持外鍵，并發量較小，不適合大量update

InnoDB：

        這種類型是事務安全的。.它與BDB類型具有相同的特性,它們還支持外鍵。InnoDB表格速度很快。具有比BDB還豐富的特性,因此如果需要一個事務安全的存儲引擎，建議使用它。在update時表進行行鎖，并發量相對較大。如果你的數據執行大量的INSERT或UPDATE，出于性能方面的考慮，應該使用InnoDB表。

優點：支持事務，支持外鍵，并發量較大，適合大量update

缺點：查詢數據相對較快，不適合大量的select

         對于支持事物的InnoDB類型的表，影響速度的主要原因是AUTOCOMMIT默認設置是打開的，而且程序沒有顯式調用BEGIN 開始事務，導致每插入一條都自動Commit，嚴重影響了速度。可以在執行sql前調用begin，多條sql形成一個事物（即使autocommit打開也可以），將大大提高性能。

基本的差別為：MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持。

        MyISAM類型的表強調的是性能，其執行數度比InnoDB類型更快，但是不提供事務支持，而InnoDB提供事務支持已經外部鍵等高級數據庫功能。

spring 事務：

Spring中Propagation類的事務屬性詳解：

PROPAGATION_REQUIRED：支持當前事務，如果當前沒有事務，就新建一個事務。這是最常見的選擇。 

PROPAGATION_SUPPORTS：支持當前事務，如果當前沒有事務，就以非事務方式執行。 

PROPAGATION_MANDATORY：支持當前事務，如果當前沒有事務，就拋出異常。 

PROPAGATION_REQUIRES_NEW：新建事務，如果當前存在事務，把當前事務掛起。

PROPAGATION_NOT_SUPPORTED：以非事務方式執行操作，如果當前存在事務，就把當前事務掛起。 

PROPAGATION_NEVER：以非事務方式執行，如果當前存在事務，則拋出異常。 

PROPAGATION_NESTED：支持當前事務，如果當前事務存在，則執行一個嵌套事務，如果當前沒有事務，就新建一個事務。

事物超時設置:
@Transactional(timeout=30) //默認是30秒

事務隔離級別:
@Transactional(isolation = Isolation.READ_UNCOMMITTED)
讀取未提交數據(會出現臟讀, 不可重復讀) 基本不使用
@Transactional(isolation = Isolation.READ_COMMITTED)
讀取已提交數據(會出現不可重復讀和幻讀)
@Transactional(isolation = Isolation.REPEATABLE_READ)
可重復讀(會出現幻讀)
@Transactional(isolation = Isolation.SERIALIZABLE)
串行化

MYSQL: 默認為REPEATABLE_READ級別
SQLSERVER: 默認為READ_COMMITTED

臟讀 : 一個事務讀取到另一事務未提交的更新數據
不可重復讀 : 在同一事務中, 多次讀取同一數據返回的結果有所不同, 換句話說, 
后續讀取可以讀到另一事務已提交的更新數據. 相反, "可重復讀"在同一事務中多次
讀取數據時, 能夠保證所讀數據一樣, 也就是后續讀取不能讀到另一事務已提交的更新數據
幻讀 : 一個事務讀到另一個事務已提交的insert數據