MySQL学习笔记——读《MySQL技术内幕 InnoDB存储引擎》

show ENGINES; #存储引擎支持情况

select version(); #查看当前版本号

ALTER table mytest ENGINE = MyISAM; #更改表引擎

show VARIABLES like 'innodb_%io_threads'; #列出数据库属性变量字段

show ENGINE INNODB STATUS; #InnoDB当前状态 包括锁的情况 表更新的情况 线程的情况 缓冲池的情况 表整体增删改查的次数

select * from INNODB_BUFFER_POOL_STATS; #查看InnoDB缓存池的状态

select @@tx_isolation; #查看当前隔离级别

官方文档：dev.mysql.com/doc/refman/… 有中文版 如下图所示：

重点参数分析： 1.innodb_io_capacity #每次从缓存区刷新脏页到磁盘时，刷新脏页的最大数量（固态硬盘可以根据实际情况调高这个数值） 2.show GLOBAL STATUS like 'innodb_dblwr%'; Innodb_dblwr_pages_written/Innodb_dblwr_writes如果远小于64 说明系统写入压力并不是很高 3.innodb_flush_neighbors #是否刷新邻近页 如果是机械硬盘则打开 如果是固态硬盘 则设置为0 4.innodb_fast_shutdown #非正常关闭时的full purge,merge insert buffer,脏页刷回磁盘的策略

InnoDB的关键特性： 1.Insert Buffer 使用条件：a.索引是辅助索引 b.索引不是唯一的 2.两次写 3.自适应hash索引 4.AIO 5.刷新邻接页

InnoDB的优化方案： 1.数据库层面 a.错误日志 2.表层面 a.慢查询日志 long_query_time slow_query_log 参数的设置 log_queries_not_using_indexes #这个参数是用来记录没有使用索引的SQL语句 可以有效记录索引失效的情况 set GLOBAL log_output='TABLE' 可以配置慢查询记录为表输入 这样就可以通过 SELECT * FROM slow_log 来查询了

小技巧： 1.在数据导入过程中关闭外键约束的检查以减少时间开销： SET foreign_key_checks = 0; LOAD DATA .... SET foreign_key_checks = 1; 2. select database() from dual; #查看当前数据库 3. InnoDB经常会自行“优化重构”sql语句 可以通过 EXPLAIN EXTENDED sql语句 的方式来查看优化过的sql语句 4. innodb_flush_log_at_trx_commit 和 sync_binlog这两个参数对数据库写入性能影响很大，在安全性要求不高（比如刷数据的时候）的情况下，可以都关闭，能够极大地提升性能 5. 全文检索 默认字段不能少于4字节 否则不计入统计范围 默认字段长度的参数为？

比较容易忽视的参数： 1.innodb_online_alter_log_max_size: 实现Online DDL的索引修改时，用来存放DML操作日志的缓存大小。在LOCK为NONE时，如果这个参数太小，容易抛异常，见书本P210

InnoDB漏洞和补救： 1.索引信息的统计和索引的使用存在不稳定现象 比如索引的cardinality的统计值并不是实时的（因为如果是实时的话 开销太大了） 比如对两条基本一样的语句执行explain 但是最终结果 一个使用了索引 一个没使用 所以 建议在非高峰时期 对核心表做ANALYZE TABLE的操作 能使优化器和索引更好工作

索引的使用： 1.InnoDB引擎中只有BTREE索引 高选择性的时候用索引，高选择性的判断依据是索引的cardinality show index from table; #查看某张表中索引的状态（包含了cardinality属性） cardinality/记录数 接近1的时候 说明具有高选择性 就可以使用索引（比如性别这类的字段就不适合加索引） OLTP和OLAP应用也不同：OLTP可能会对姓名等进行查找，可以加索引。OLAP就很少会对这种微观的角度进行分析，所以可以不加索引，除非有hash join的场景。OLAP常常会对时间做索引，也可以结合partition对时间段统计类查询进行优化。 2.需要指定索引时 用FORCE INDEX 而不是 USE INDEX 3.同样一个辅助索引，同样的查询，InNoDB会根据数据量大小而采取全表扫描还是使用索引

`DROP TABLE IF EXISTS `t_playlist_info_test`;
CREATE TABLE `t_playlist_info_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `vivo_tags` varchar(50) DEFAULT '' COMMENT 'vivo标签',
  PRIMARY KEY (`id`),
  KEY `idx_vivo_tags` (`vivo_tags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌单表';`
复制代码

当这个表数据量1000时 执行：

`EXPLAIN
select * from t_playlist_info_test where vivo_tags = '测试'`
复制代码

可以看到

采取了全表扫描的方式 并没有用到vivo_tags上面的辅助索引 而当这个表数据量1W的时候 再执行上述的语句时 可以看到

锁机制： 用户监控锁情况的三张表information_schema.INNODB_TRX,information_schema.INNODB_LOCKS,information_schema.INNODB_LOCK_WAITS

分布式事务： 查看是否打开XA事务的支持

性能调优： 1.怎样去预估数据库大小 2.怎样判断数据库内存到了瓶颈： show global status like 'innodb%read%';

缓存命中率=Innodb_buffer_pool_read_requests/(Innodb_buffer_pool_read_requests+Innodb_buffer_pool_reads+Innodb_buffer_pool_read_ahead) 缓存命中率低于99%就说明内存到了瓶颈

一些常识： 1.count()一般比快，原因是count(*)会用到覆盖索引，IO次数要比使用聚合索引少 2.参数设置有三个级别 最高级别是通过修改my.cnf 然后是set global来设置全局参数 再就是。。。