happyenjoylife

Innodb存儲

表空間是邏輯存放所有數據的地方，默認情況下會共享一個表空間——ibdata1，但如果把innodb_file_per_table=ON后每張表可以單獨放到一個表空間內，但還是有很多數據保存在共享的表ibdata1中，如undo信息等。

表空間由各種段(segment)組成，常見的段有數據段、索引段等。Innodb是索引組織的，數據段就是clustered index的葉結點。需要注意的是，不是每個對象都有段。

區(extend)是由64個連續的頁組成，每個頁（page）固定為16KB，所以每個區總共為1M。頁是innodb最小的磁盤管理單位。

Innodb是按行進行存放的，每個區最少可以保存2條記錄，否則就成鏈式結構了。每行數據除了自定義列以外，還會增加事務id和回滾指針列。如果沒有定義primary key也沒有not null的unique,則會增加6字節的RowId列作為主鍵。
        
            圖片來自：http://www.cnblogs.com/chjw8016/archive/2011/03/08/1976891.html

Innodb表的限制

        一個表不能包含超過1000列。

  內部最大鍵長度是3500字節，但MySQL自己限制這個到1024字節。

  除了VARCHAR, BLOB和TEXT列，最大行長度稍微小于數據庫頁的一半。即，最大行長度大約8000字節。LONGBLOB和LONGTEXT列必須小于4GB, 總的行長度，頁包括BLOB和TEXT列，必須小于4GB。InnoDB在行中存儲VARCHAR，BLOB或TEXT列的前768字節，余下的存儲的分散的頁中。

雖然InnoDB內部地支持行尺寸大于65535，你不能定義一個包含VARCHAR列的，合并尺寸大于65535的行。

·                mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),

·                    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),

·                    -> f VARCHAR(10000), g VARCHAR(10000));

·                ERROR 1118 (42000): Row size too large. The maximum row size for the

·                used table type, not counting BLOBs, is 65535. You have to change some

·                columns to TEXT or BLOBs

 在一些更老的操作系統上，數據文件必須小于2GB。

 InnoDB日志文件的合并尺寸必須小于4GB。

最小的表空間尺寸是10MB。最大的表空間尺寸是4,000,000,000個數據庫頁（64TB）。這也是一個表的最大尺寸。

 InnoDB表不支持FULLTEXT索引

Innodb索引

默認情況下Memory使用存儲hash索引，但也支持b+tree索引。Hash索引只用于=或者<=>的等式比較，不能用來加速order by操作，只能通過關鍵字來搜索一行。innodb只支持b+樹索引，進一步分為clustered index 與　secondary index。在一次查詢中，只能使用一個索引。

Innodb是索引組織表，clustered index的葉結點保存著整行的數據。如果，定義了primary key，則clustered index就是primary key的索引；如果沒有定義primary key mysql會選中第一個僅有not null列的unique索引作為主鍵，并把此索引當作clustered index使用；如果沒找到這樣的列，innodb會創建一個6字節的RowId作為主鍵。所以每張表有且只有一個clustered index。

         Secondary index的葉結點不包括行的全部數據，包含鍵值以外還包括一個bookmark，可以告訴innodb到什么地方可以找到相對應的完整行數據，還保存了主鍵的健值。Secondary index包含主鍵，但不包含完整的行數據，所以innodb總是會先從secondary index的葉節點判斷是否能得到所需的數據。如,

         Create table t(a int, b varchar(20), primary key(a), key(b));

Explain select * from t;

         會發現mysql選擇了索引b，而不是a.

復合索引

         復合索引是在多列（>=2）上建立的索引，又叫多列索引或聯合索引。Innodb中的復合索引也是b+ tree結構。索引的數據包含多列(col1, col2, col3…)，在索引中依次按照col1, col2, col3排序。如(1, 2), (1, 3),(2,0)…

         使用復合索引要充分利用最左前綴原則，顧名思義，就是最左優先。如創建索引ind_col1_col2(col1, col2)，那么在查詢where col1 = xxx and col2 = xx或者where col1 = xxx都可以走ind_col1_col2索引。

在創建多列索引時，要根據業務需求，where子句中使用最頻繁且過濾效果好的的一列放在最左邊。

索引操作

         可以通過DML語句操作innodb索引。因為innodb是索引組織的表，對索引的操作會造成鎖表，先生成一張臨時表，將數據從原始表中寫到臨時表，再將原始表刪除，最后將臨時表表名改為原始表表名！因增加、刪除、修改字段會對主索引產生影響，所以也會鎖表。對secondary index從Innodb plugin開始，支持快速索引創建的方法，在創建的過程中不需要重建表，所以速度會很快，同時引擎會在表上加S鎖，在創建過程中只能進行讀操作。

索引設計原則

1.       搜索的索引列，不一定是所要選擇的列。也就是說，最適合索引的列是出現在where子句中的列，或者連接子句中指定的列，而不是出現在select關鍵字后的選擇列表中的列。

2.       使用唯一索引。考慮某列的分布，索引的列的基數越大，索引的效果越好。例如，對性別M/F列做索引沒多大用處。

3.       使用短索引。如果是對字符串進行索引，如果有可能應該指定前綴長度。

4.       利用最左前綴。盡量將使用頻繁且過濾效果好的字段放“左邊”

5.       不要過度索引。

6.       Innodb默認會按照一定的順序保存數據，如果明確定義了主鍵，則按照主鍵順序保存。如果沒有主鍵，但有唯一索引，就按照唯一索引的順序保存。如果有幾個列都是唯一的，都可以作為主鍵的時候，為了提高查詢效率，應選擇最常用訪問的列作為主鍵。另外，innodb的secondary index都會保存主鍵的鍵值，所有主鍵要盡可能選擇較短的數據類型。可以看出，應當盡量避免對主鍵的修改。經過dba的測試，保證主鍵的遞增可以提高插入性能。

Mysql如何使用索引

1.       對于創建的多列索引，只要查詢的條件中用到了最左邊的列，索引一般就會被使用。

2.       對于使用like的查詢，后面如果是常量并且只有%號不在第一個字符，索引才可能被使用。

3.       如果對大文本進行搜索，應該使用全文索引，而不是使用like ‘%...%’. 但不幸的是innodb不支持全文索引。

4.       如果列名是索引，使用 index_column is null將使用索引。Oracle是不行的。

5.       如果mysql估計使用索引比全表掃描更慢，最不會使用索引。

6.       如果使用memory/head表并且where條件中不使用”=”進行索引列，那么不會用到索引。Head表只有在”=”的時候才會使用索引。

7.       用or分割開的條件，如果or前的條件中的列有索引，而后面列中沒有索引，那么涉及到的索引都不會被用到。

8.       不是多列索引的第一部分不會走索引。

9.       以%開始的like不會走索引

10.   如果列是字符串，那么一定要在where條件中把字符串常量值用引號引起來，否則不能走索引。因為，mysql默認把輸入的常量值進行轉換以后才進行檢索。

11.   經過普通運算或函數運算后的索引字段不能使用索引

12.   不等于操作不能使用索，<>、not in等

13.   Order by 優化：某些情況下，mysql可以使用一個索引滿足order by,而不需要額外的排序。Where條件與order by 使用相同的索引，并且order by的順序和索引順序相同，并且order by的字段都是升序或者都是降序。

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2

DESC;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

但是以下情況不使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ；

--order by 的字段混合 ASC 和 DESC

SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ；

-- 用于查詢行的關鍵字與 ORDER BY 中所使用的不相同

SELECT * FROM t1 ORDER BY key1, key2 ；

-- 對不同的關鍵字使用 ORDER BY     

可以使用explain查看sql的執行計劃。

異常表

每一個try語句塊catch的異常都與異常表中的一項相對應，異常表中的每一項都包括：

1. 起點
2. 終點，始終把catch異常位置的pc指針偏移量的最大值大１
3.  處理異常時跳轉到的字節碼序列中的pc指針偏移量
4.  被catch的異常類的常量池索引

例如：

public class Test {
    public static void main(String[] args) {

        try {
            Class.forName("java.lang.String");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }
}

用javap –c查看字節碼如下：

Compiled from "Test.java"
public class Test extends java.lang.Object{
public Test();
  Code:
   0:    aload_0
   1:    invokespecial    #1; //Method java/lang/Object."<init>":()V
   4:    return

public static void main(java.lang.String[]);
  Code:
   0:    ldc    #2; //String java.lang.String
   2:    invokestatic    #3; //Method java/lang/Class.forName:(Ljava/lang/String;)Ljava/lang/Class;
   5:    pop    
   6:    goto    14
   9:    astore_1
   10:    aload_1
   11:    invokevirtual    #5; //Method java/lang/ClassNotFoundException.printStackTrace:()V
   14:    return
  Exception table:
   from   to  target type
     0     6     9   Class java/lang/ClassNotFoundException
}

可見ClassNotFoundException異常可能會在0~6之間拋出，9開始處的代碼處理此異常。

當產生異常的時候，jvm將會在整個異常表中搜索與之匹配的項，如果當前pc在異常表入口所指的范圍內，并且所拋出的異常是此入口所指向的類或者其子類，則跳轉到對應的處理代碼繼續執行。

方法可能會拋出哪些已檢查異常

Class文件的attribute_info中保存有Exceptions屬性，記錄著每個方法throws的異常信息。具體的可以查看class類文件格式相關的文章。

athrow指令從棧頂彈出Throwable對象引用，拋出異常。

finally語句

在jvm規范中，finally語句是通過jsr/jsr_w與ret指令實現的。當執行jsr/jsr_w的時候將finally執行完成后的返回地址壓入棧中，進入finally后會馬上將此地址保存到一個局部變量中，執行完成后，ret從此局部變量中取出返回地址。？？？為什么會先把返回地址保存到局部變量中呢？？？因為，當從finally語句返回的時候需要將返回地址成棧中彈出，當finally語句非正常結束(break,continue,return, 拋異常)的時候就不用再考慮這個問題。

以下是jvm規范中Compiling finally的一段：

void tryFinally() {
    try {
        tryItOut();
    } finally {
        wrapItUp();
    }
}
the compiled code is
Method void tryFinally()
   0     aload_0            // Beginning of try block
   1    invokevirtual #6         // Method Example.tryItOut()V
   4     jsr 14            // Call finally block
   7     return            // End of try block
   8     astore_1            // Beginning of handler for any throw
   9     jsr 14            // Call finally block
  12     aload_1            // Push thrown value
  13     athrow            // and rethrow the value to the invoker
  14     astore_2            // Beginning of finally block
  15     aload_0            // Push this
  16     invokevirtual #5         // Method Example.wrapItUp()V
  19     ret 2            // Return from finally block
Exception table:
       From     To     Target         Type
    0        4        8           any

當tryItOut排除任何異常后都將會被異常表中的any項捕獲，執行完finally后，會執行athrow指令將異常拋出。

從jdk的某一個版本開始就不會編譯出編譯出含jsr/jsr_w、ret的字節碼了，因為有指令上的缺陷，導致jvm的檢驗和分析系統出現漏洞。

再說finally的非正常退出

在finally中使用break、continue、return、拋出異常等認為是finally的非正常結束。非正常結束的時候，ret指令不會被執行，很可能會出現意想不到的結果。如：

public class Test {
    public static boolean test(boolean b) {
        while (b) {
            try {
                return true;
            } finally {
                /**//*
                break;                          始終返回false
                continue;                         javac編譯再java執行會出現死循環
                                                在eclipse中甚至會出現報錯：提示找到不main class
                return false;                     始終返回false
                throw new RuntimeException("");    拋出異常
                 */
            }
        }

        return false;
    }

    public static void main(String[] args) {
        System.out.println(test(true));
    }
}

建議：在寫finally語句的時候，盡量避免非正常結束！

單庫單表

單庫單表是最常見的數據庫設計，例如，有一張用戶(user)表放在數據庫db中，所有的用戶都可以在db庫中的user表中查到。

單庫多表

隨著用戶數量的增加，user表的數據量會越來越大，當數據量達到一定程度的時候對user表的查詢會漸漸的變慢，從而影響整個DB的性能。如果使用mysql, 還有一個更嚴重的問題是，當需要添加一列的時候，mysql會鎖表，期間所有的讀寫操作只能等待。

可以通過某種方式將user進行水平的切分，產生兩個表結構完全一樣的user_0000,user_0001等表，user_0000 + user_0001 + …的數據剛好是一份完整的數據。

多庫多表

         隨著數據量增加也許單臺DB的存儲空間不夠，隨著查詢量的增加單臺數據庫服務器已經沒辦法支撐。這個時候可以再對數據庫進行水平區分。

分庫分表規則

         設計表的時候需要確定此表按照什么樣的規則進行分庫分表。例如，當有新用戶時，程序得確定將此用戶信息添加到哪個表中；同理，當登錄的時候我們得通過用戶的賬號找到數據庫中對應的記錄，所有的這些都需要按照某一規則進行。

路由

         通過分庫分表規則查找到對應的表和庫的過程。如分庫分表的規則是user_id mod 4的方式，當用戶新注冊了一個賬號，賬號id的123,我們可以通過id mod 4的方式確定此賬號應該保存到User_0003表中。當用戶123登錄的時候，我們通過123 mod 4后確定記錄在User_0003中。

分庫分表產生的問題，及注意事項

1.   分庫分表維度的問題

假如用戶購買了商品,需要將交易記錄保存取來，如果按照用戶的緯度分表，則每個用戶的交易記錄都保存在同一表中，所以很快很方便的查找到某用戶的購買情況，但是某商品被購買的情況則很有可能分布在多張表中，查找起來比較麻煩。反之，按照商品維度分表，可以很方便的查找到此商品的購買情況，但要查找到買人的交易記錄比較麻煩。

所以常見的解決方式有：

     a.通過掃表的方式解決，此方法基本不可能，效率太低了。

     b.記錄兩份數據，一份按照用戶緯度分表，一份按照商品維度分表。

     c.通過搜索引擎解決，但如果實時性要求很高，又得關系到實時搜索。

2.   聯合查詢的問題

聯合查詢基本不可能，因為關聯的表有可能不在同一數據庫中。

3.   避免跨庫事務

避免在一個事務中修改db0中的表的時候同時修改db1中的表，一個是操作起來更復雜，效率也會有一定影響。

4.   盡量把同一組數據放到同一DB服務器上

例如將賣家a的商品和交易信息都放到db0中，當db1掛了的時候，賣家a相關的東西可以正常使用。也就是說避免數據庫中的數據依賴另一數據庫中的數據。

一主多備

在實際的應用中，絕大部分情況都是讀遠大于寫。Mysql提供了讀寫分離的機制，所有的寫操作都必須對應到Master，讀操作可以在Master和Slave機器上進行，Slave與Master的結構完全一樣，一個Master可以有多個Slave,甚至Slave下還可以掛Slave,通過此方式可以有效的提高DB集群的QPS.                                                       

所有的寫操作都是先在Master上操作，然后同步更新到Slave上，所以從Master同步到Slave機器有一定的延遲，當系統很繁忙的時候，延遲問題會更加嚴重，Slave機器數量的增加也會使這個問題更加嚴重。

此外，可以看出Master是集群的瓶頸，當寫操作過多，會嚴重影響到Master的穩定性，如果Master掛掉，整個集群都將不能正常工作。

所以，1. 當讀壓力很大的時候，可以考慮添加Slave機器的分式解決，但是當Slave機器達到一定的數量就得考慮分庫了。 2. 當寫壓力很大的時候，就必須得進行分庫操作。

         另外，可能會因為種種原因，集群中的數據庫硬件配置等會不一樣，某些性能高，某些性能低，這個時候可以通過程序控制每臺機器讀寫的比重，達到負載均衡。

備份地址：http://happyenjoylife.iteye.com/admin/blogs/1042538