mysql innodb内幕_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去多分析，值就准确了