第五章MYSQL

5.1 MYSQL基础 5.2 MYSQL优化

5.1 MYSQL基础

范式(1,2,3,BCNF,4,5)
语法，应用-略

架构

程序实例(逻辑管理)
	容灾机制，权限划分，缓存机制，SQL解析，日志管理，索引管理，锁管理等
存储(SSD 文件系统等)

执行流程

连接器-分析器-优化器-执行器
先写redo进入prepare阶段。再写binlog进入commit阶段

索引 Hash和B+树

B+树和B树区别：数据都在叶子节点，每个叶子节点包含一串数据，
每个叶子节点会连接起来方便读取
B+树优点：正因为这些区别，磁盘读写代价低，查询效率稳定，有
利于对数据库扫描

密集索引，稀疏索引–略

图片就是正不过来，放弃了
MyISAM和InnoDB区别

1 如上图索引区别
2 MyISAM不支持事务，适合频繁全表查询，对增删改频率不高
3 MyISAM默认表级锁，不支持行级锁
   InnoDB支持行级锁和gap锁

联合索引最左匹配原则

mysql会一直向右匹配直到遇到范围查询(<,>,between,like)就停止，
其中=和in可以乱序，就意味着比如查a/ ab / ba / abc  等，
都可以走abc联合索引	

另外bc查询虽然不符合abc的最左匹配原则，但是mysql会全索引扫描，
仍然会走这个abc索引

事务四大特性

原子性：要么全做，要么全不做
一致性：无论A和B如何转账，A和B金钱总量不变
隔离性
持久性：提交的数据永久保存

事务四大隔离级别 前人一个讲解的很好的博客

未提交读-会脏读
已提交读 -会不可重复读
可重复读(mysql默认隔离级别)-会幻读
串行化

就这四大隔离级别背了n次，以前老是忘记

MYSQL如何实现的可重复读—通过版本号比较

MQSQL如何解决幻读问题

表象：快照读，伪MVCC
本质：next-key锁(行锁+gap锁)	

已提交度下当前读和快照读一样，但是可重复读下快照读可能会读老版本不一样。

where条件下精确查询且全部命中则只加记录锁不加gap锁，否则会加一个gap锁。
gap锁是指如where了id=1，3没全部命中，所有关于id的负无穷到1，1到3，3到
正无穷的gap都会锁住，这样就解决了幻读问题。

SQL顺序 where>group by>having。通常having和group by一起使用，where过滤行，having过滤组

覆盖索引

假设id为主键索引，name为普通索引。
select id from table where name='xxx'
innodb中通过name普通索引直接定位到主键索引id值，不需要再通过id主键索引回表再次遍历，叫做覆盖索引

全文索引

即倒排索引，myisam和innodb5.6后支持

索引下推

select id from table where name='xxx' and age = '4'
普通索引name在定位时直接进行数据筛选age=4，则不需要再次回表主键索引遍历

Redo Undo Log

undo用于回滚原先数据，redo用于防止故障数据丢失
undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子，而redo log是物理日志，记录的是数据页的物理变化

假设有A、B两个数据，值分别为1,2.
1. 事务开始
2. 记录A=1到undo log
3. 修改A=3
4. 记录A=3到 redo log
5. 记录B=2到 undo log
6. 修改B=4
7. 记录B=4到redo log
8. 将redo log写入磁盘
9. 事务提交

binlog

redo是innodb独有，binlog是所有引擎都有
redo是物理日志，记录数据页修改。binlog是逻辑日志，记录语句原始逻辑。
redo是循环写会覆盖，binlog是不断追加，不会覆盖之前日志信息

5.2 MYSQL优化

1 schema与数据类型设计优化

join中分片尽量使用相同的库，相同的分片策略。
经常join查询的等值字段适合作为均衡字段
(即用来水平拆分的字段，能建索引，尽量是数字)

1 
表必须有主键
不用外键，关联通过应用层代码控制
表数量小于1000万，尽量单表不分表
大字段，访问频率低的字段拆分到拓展表(垂直拆分)
控制表总数，分表数量在1024内

2 合理使用字段类型
正确区分tingint, int 4字节，bigtype 8字节
varchar(20) 存手机号
int 存IPv4，不用char(15)
金额用decimal，设置精度
所有字段设成not null并且设置默认值(存null占用空间大且使索引统计更复杂)

3 索引规范
命名规范
尽量不用join，如用join字段type要一样且创建索引
频繁更新字段最好不要设索引
单张表索引控制5个以内，可以强制走索引
组合索引字段数不超过5个，理解最左匹配原则，不重复建索引

4 SQL规范
禁止select*
insert必须指定字段
禁止where下用函数或者表达式，这样会走全表扫描不走索引
禁止负向查询和%开头的模糊查询，会走全表扫描
避免直接返回大结果集造成内存溢出，可用分段或者游标
返回结果集尽量用limit分页显示
避免较大limit和offset
不要大表join和子查询
应用程序必须捕获SQL异常
大表扫描尽量镜像库上做

垂直拆分
长度短，访问率高的放一张表，如主表
长度长，访问频率低的放拓展表
将经常一起访问的字段放一张表

水平拆分
根据业务拆分
后期根据创建时间拆分

如何平滑的添加字段？
	设计表时候，提前设计key value字段。
	比如临时活动秒杀系统，key可以临时改成是否秒杀，value值为是或者不是。
	活动结束key value字段再可以改成别的

2 性能监控

1 show profile 显示各个步骤执行时间(即将淘汰)
2 performance schema监控，或者通过额外配置进行监控
3 show processlist 查看连接线程个数，观察不正常状态

3 执行计划

1 根据慢查询定位查询SQL
2 理解explain分析SQL
3 修改SQL或者尽量让SQL走索引

2 explain
	id(查看执行顺序) 
	select_type(查询方式) 
	table(查询的表) 
	重要type(数值越靠前，效率越高)
		system一行记录/const最多一个匹配行/ref非唯一索引值/range/index/all全表扫描
		当type为index和all时候代表可以选择优化
	possible_keys(是否使用索引且可能用了啥索引)
	rows(估算行读取数)
	重要extra
		当使用filesort和temporary，这两个查询很慢，都没走索引
		filesort 无法索引排序，只能排序算法排序
		temporary 临时表保存中间结果，查询后再删除

3 修改SQL + 加索引 + 测试性能快慢后，强制走自定义索引