mysql innodb 引擎内幕读书笔记1

1 mysql为单进程多线程的架构
2 查看当前数据库支持的引擎
   show engines\G;

3 innodb的后台线程有7个，4个IO THREAD，1个MASTER THREAD,1个锁，1个错误监控线程。
  show engine innodb status\G;

4 innodb 的内存组成：缓冲池，重做日志缓冲池，额外的内存池
   show variables like 'innodb_buffer_pool_size'\G;(缓冲池)
   show variables like 'innodb_log_buffer_size'\G;重做日志缓冲池，
   show variables like 'innodb_additional_mem_pool_size'\G;

具体看缓冲池的方法：
show engine innodb status\G;

BUFFER POOL AND MEMORY
----------------------
Total memory allocated 385277496; in additional p
Buffer pool size   21632
Free buffers       21543
Database pages     89
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page
Pages read 89, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer poaol page gets since the last printout

  分配的内存为21632（buffer pool size表示有多少个缓冲帧）*16(每个buffer frame为16K）/1024=338M，free表

示有多少空闲,database pages表示已使用多少个。modified db pages表示脏页的数量


5 master thread的工作原理不：每10秒合拼插入缓冲，不是每秒发生，而是判断当前IO压力去进行，INNODB会判断缓

冲池中的脏页比例(buf_get_modified_ratio_pct),如果有超过70%的，则刷100个脏页到磁盘，如果比例小过70%，则

刷新10%到磁盘

6 double write buffer:由两部分组成，一部分是内存中的double write buffer，大小为2MB，另外的是磁盘上
共享表空间上的连续的128个页，即两个区，大小同样为2MB，当缓冲池脏页更新时，不写磁盘，而是通过memcpy 函数

先COPY到内存中的double write buffer,然后再分2次，每次写1MB到共享表空间的物理磁盘上，然后再调用fsync函数

，同步磁盘。
  show global status like 'innodb_dblwr%'\G;
   如果innodb_dblwr_pages_writen:innodb_dblwr_writes远小于64：1，说明磁盘写入压力不高

7 MYSQL中的参数类型，可以设置为当前会话，也可以设置为整个周期
   set read_buffer_size=524288;
   查全局  select @@global.read.read_buffer_size\G;
    查会话的  select @@sessionread.read_buffer_size\G;

8 慢查询日志：
   注意，5.1开始，慢查询以毫秒来计算，
   show variables like '%long%';
    查看开关是否打开
     show variables like 'long_slow_queries';
   show variable like 'long_query_time';
    可以设置一个开关，当开关ON时，如果SQL没用到索引，则把这个语句也记录下来
   show variable like 'long_queries_not_using_indexs';
  查看慢查询日志的记录： 
     mysqldumpslow xxxx.log
   5.1开始可以啊慢查询的日志记录放到一张表中：
9 查询日志：记录了所有对数据库请求的信息，放在主机名.log文件中

10 二进制文件：
    binlog_cache_size:所有未提交的二进制日志都会记录到缓存中，等事务提交时再将从缓冲中的写入到二进制日

志文件中，默认为32KB
    5.1中，binlog_format参数，可以设置的值有statement(用传统方式记录日志），row（记录的是表的行更改情况

），如果设置ROW，可以设置read commited的事务隔离级别，有更好的性能，但磁盘空间增大很多。
    1）查看二进制日志文件
         mysqlbinlog --start-position=203 XXXX.00004
       可以看到SQL语句
      而如果设置了ROW后，必须要用
        mysqlbinlog  --vv --start-position=xxx  222.004这样看更清楚

    
11)
  innodb的默认文件可以在innodb_data_file_path中设置
   [mysqld]
       innodb_data_file_path=/db/indabat1:2000m;/db2/innodb:autoextend
   指定了用两个文件来组成表空间，最好这两个文件位于不同的磁盘上。

12） 重做日志文件，也有重做日志文件组的概念，跟ORACLE的差不多，可以设置多个组，每个组有两个文件，写完一

个再写另外一个。
   查看重做日志文件组的方法：show variables like '%innodb%log%'\G;

13) innodb表也是由表空间，段，区，页，行。段由数据段，索引段，回滚段组成
     区由64个连续的页组成，每个页16KB，每个区为1MB。每行最多放16KB/2-200行记录即7992行记录

14) COMPACT行记录的格式
    变长字段长度列表  NULL标志位   记录头信息  列1数据 列2数据。。。
   其中NULL值不占存储空间
15） 注意的是,mysql中的varchar65536长度是说所有varchar列的长度总和，如果超出了，则无法创建。
16） MYSQL中，比如对于NOT NULL字段，如果非要插入非法字段，默认是不提示错误的，要提示错误的话，
设置sql_mode,即：
   set sql_mode='STRICT_TRANS_TABLES';
17 MYSQL 5.1中每张表可建6个触发器，目前只支持for each row的触发方式
18 MYSQL 5。1中的分区
   RANGE分区，LIST，HASE分区，KEY分区，不论哪种分区，当表中有主键或唯一索引时，分区列必须是唯一索引
的一个组成部分。

   1）RANGE分区
        create table t(id int) partition by range(id)
         ( partition p0 values less than(10),partition p1 values less than(20));
     查看分区信息：
     select * from information_schema.partitions where table_schema=database() and tablename='t';
     注意的是，优化器只能对year(),to_days(),to_second(),unix_timestamp进行优化，而象
year(date)*100+month(date)这些则不会优化。
  
2）LIST分区
       离散值。
3）HASH分区
     partition by hash(year(b))
4)mysql 5.5中，新增加了column分区，可以不一定象以前那样，都是整型的数据，
比如
partition p0 values less than ('2009-01-01');
分区作在OLAP上比较好，如果是OLTP上的话，除非对主键查询，否则其实会更慢，
有可能。


19)  二叉查找树：左子树比根小，右子数比根大；
     平衡二叉树：任何结点的左右子树高度最大差为1
      B+树中一般用旋转代替拆分，增加效率
     聚集索引和辅助索引内部都是B+；
     聚集索引：叶子结点中存放的是整张表的行记录；聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节

仍然是索引节点，但它有一个指向最终数据的指针。

20）innodb plugin开始，支持快速建立索引的方法，但只对辅助索引起效果，对主键的创建和删除还是需要重新建立

一张表，辅助索引的话，会加S锁；查看索引方法
   show index from 表名
每列的含义：
    non_unique:非唯一的索引
    key_name:索引的名
     seq_in_index:索引中该列位置
　　column_name:索引的列
　　collation:列以什么方式存储，Ｂ＋树索引总是Ａ
　　cardinality:表示索引中唯一的数目的估计值，如果非常小，要考虑是否建立索引
　　　sub_part:是否是列的部分被索引，如果索引整个列，则该字段为ＮＵＬＬ
　　packed:关键字如何被压缩，如果没被压缩，则为NULL
    NULL:是否索引的列包含有null值，
　　index_type:索引的类型，INNODB 中都为Ｂ＋
　 分析cardinality时，不大准确，要用analyse tables去多分析，值就准确了