mysql innodb 引擎內幕讀書筆記2

1 關於全表掃描

   如果要找的記錄有高選擇性，可以用索引；但如果找出來的資料量佔總記錄很大，比如超過20%，

則最佳化器不會使用索引了，而是全表掃描。

2 順序讀：是指根據索引的葉結點就能順序地讀取所需要的資料，只是邏輯上的，在物理磁碟上可能是隨機讀，但物

理上的資料還是比較有順序的。隨機讀：指訪問輔助索引葉點不能完全得到結果，需要根據輔助索引葉結點中的

主鍵去找實際行資料。

3 輔助索引的最佳化：輔助索引的葉結點包含主鍵，但並不包含完整的行資訊，innodb總會先從輔助索引的葉結點去判

斷是否能得到所需要的資料

4 聯合索引：好處是可以對第2個索引值進行排序，例如我們需要查詢某個使用者的購物情況，並按時間排序，取出最近三

次的購買記錄，可以使用

聯合索引避免多一次的排序操作，因為索引本身在葉結點中已經排序了

5 MYISAM中的鎖是表鎖，INNODB中提供非鎖定讀，行級鎖的支援，沒相關的開銷。實現如下兩類型的行級鎖：

   S 共用鎖定，允許事務讀一行資料  獨佔鎖定 X，允許事務刪除或更新一行資料

    意圖鎖定為表級的鎖，目的是為了在一個事務中揭示下一行將被請求的鎖的類型，有兩種：

      

        意圖共用鎖IS LOCK：事務要獲得一個表中某幾行的共用鎖定

        意向獨佔鎖定IX LOCK，事務要獲得一個表中某幾行的排它鎖

       查看命令：show engine innodb status\G,在transcations部分，可以看到比如select * from t where a<4

lock in share mode的字句

    在新的innodb plugin中，在information_schema下有innodb_trx,innodb_locks,innodb_lock_watis三張表，能

具體查看鎖的具體資訊：

6 一致性的非鎖定讀操作

    

     通過多版本控制的方式讀取當前執行時間資料庫中的行資料，如果讀取的行資料有執行delete,update操作，則

讀取的操作不會因此而等待行上的鎖釋放，相反，會讀取行的一個快照資料。

   三種行鎖的設計：

  1） record lock:單個行上的鎖

  2） 間隙鎖：鎖定一個範圍，但不包含記錄本身

  3） next-key lock:鎖定一個範圍，並且鎖定記錄本身

  

   INNODB預設的交易隔離等級為read repeatable,sql server和oracle的為read commited;而innodb中，用

innodb_lock_wait_timeout來控制鎖的等待時間，預設為50秒，為一個動態數，可以修改，

innodb_rollback_on_timeout設定是否在等待逾時後對事務進行復原

7 事務中

   計算TPS的方法：(com_coomit+com_rollback)/time  ，但方法的前提是所有的事務必須都是顯式提交的，如果存

在隱式的提交和復原，不會計算到com_commit和com_rollback

   show global status like 'com_commit'\G;

    查看當前會話的交易隔離等級，可以用：

       select @@tx_isolation\G;

      查看全域：  select @@global.tx_isolation\G;

  另外，在預存程序中，最好不要使用declare exit handler for sqlexception來處理異常，應該由程式去處理異常

8 備份

  1)邏輯備份

      mysqldump --all-databases >dump.sql  //備份所有資料庫

      mysqldump --databases db1 db2 db3>dump.sql  //備份指定的資料庫

     備份時，用參數--single-transcation ，備份前，先執行start transcation命令，獲得備份時的一致性，只對

innodb 有效果。

    --lock-tables：只對myisam表有效

    --local-all-tables:對所有表上鎖

   2）備份二進位檔案

       [mysqld]

   log-bin

    sync_binlog=1

     innodb_support_xa=1

    恢複二進位檔案

        mysqlbinlog binlog.0001 |mysql -uroot -p test

  2)熱備工具 xtrabackup

       ./xtrabackup --backup  //完全備份

    實現增量備份

      先完全備份： ./xtrabacup --backup

     然後記下LSN

     ./xtrabackup --prepare

      ./xtrabackup --backup --incremental=LSN號

9 效能調優

   1） 如果是多核的話，可以修改innodb_read_io_threads和innodb_write_io_threads來增大線程，充分利用多核

   2）記憶體方面，看innodb的命中率

       show global status like 'innodb%read%'\G;

      參數含義如下：

       innodb_buffer_pool_reads:表示從物理磁碟讀取頁的次數

      innodb_buffer_pool_read_ahead:預讀的次數

      innodb_buffer_pool_read_ahead_evicted:預讀的頁，但是沒有被讀取就從緩衝池中被替換的頁的數量，一般

用來判斷預讀的效率

       innodb_buffer_pool_read_requests:從緩衝池中讀取頁的次數

        innodb_data_read:總共讀入的位元組數

       innodb_data_reads:發起讀取請求的次數，每次讀取可能需要讀多少個頁

    緩衝池命中率=innodb_buffer_pool_read_requests/

(innodb_buffer_pool_read_requests+innodb_buffer_pool_read_ahead+ innodb_buffer_pool_reads)

       平均每次讀取的位元組數=innodb_data_read/innodb_data_reads

     3)測試載入器sysbench,分為prepare,run和clean三個階段

   比如：

   sysbench --test=fileio --file-num=16 --file-total-size=2G prepare

     產生16個檔案

    然後測試隨機讀取新能

   sysbench --test=fileio --file-total-size=2G --file-test-mode=rndrd-max-time=180 --max-

requests=1000000 --num-thread=16 --init-rng=on

   最後要cleanup

       sysbench --test=fileio --file-num=16 --file-total-size=2G cleanup

  

4) tpc測試載入器  tpcc-mysql

          tpcc-c的效能單位是tpmC,transcation per minute的縮寫。

win下的測試載入器在:http;//code.google.com/p/david-mysql-tools/download/list