高性能mysql笔记

高性能mysql笔记（该版本主要针对5.1）

mySql逻辑架构

行级锁

1、行级锁只在存储引擎层实现，而mysql服务器曾没有实现。

事务

1、事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O,因此事务日志的方式快的多。
2、如果事务日志写入到磁盘，但是数据还没有存储到磁盘，这时系统崩溃，存储引擎在重启时会自动恢复。
3、mysql服务器不管理事务，事务是由下层的存储引擎是实现的，所以在同一事务中，使用多种存储引擎是不可靠的。
4、显式和隐式锁定
InnoDB采用的是两阶段锁定协议，在事务的执行过程中随时都可以锁定，锁只有在commit或rollback的时候才会释放。（隐式锁）

多版本控制

mvcc是通过每行记录保存两个隐藏的列实现的，一个是行的创建时间、一个是行的过期时间。存储的是版本号

查看表结构命令

SHOW TABLE STATUS LIKE ‘USER’

mysql 存储引擎

采用InnoDB存储引擎
InnoDB 采用mvcc支持高并发，默认的隔离级别是可重复读, 并且通过间隙锁防止幻读发生，间隙锁不仅锁定查询涉及的行，还会对多因中间的间隙进行锁定，可以防止幻影行的插入。

索引：
mysql采用的是聚簇索引，二级索引（非主键索引）中必须包含主键列，因此逐渐应该尽可能小

尽量避免NULL

1、如果查询中包含可以为null的列，对于mysql来说更加难以优化，因为可为Null的列使得索引、索引统计和值比较都更复杂
2、使用null的列会使用更多的存储空间、在mysql中也需要特殊处理

高效索引

1、创建一个包含两列的索引和创建两个包含一列的索引 大小是相同的
2、B-TREE
按照顺序进行存储（适合方位查找），并且每一个叶子页到根的距离相等

高性能索引策略

1、独立的列：索引不能是表达式的一部分，也不能是函数的一部分
2、前缀索引和索引选择性
对于字符比较长的列，可以只索引开始的那一部分，但是这样会降低索引的选择性。对于BLOB 、TEXT、或者长度很长的varchar必须使用前缀索引
3、多列索引（索引合并 index merge）
4、正确的所引列顺序：选择性高的放在前面
5、聚簇索引
6、覆盖索引（如果一个查询包含（覆盖）所有需要查询字段的值，我们就称为覆盖索引）
7、使用索引做排序
8、避免创建重复和冗余索引
9、未使用的索引（打开userStates服务器变量，然后查询informatiion_schema.index_statistics查询每一个索引的使用情况）

索引案例学习