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