MySQL知识点（个人总结）

目录

1.MySQL架构设计图

2.存储引擎MylSAM和InnoDB的区别

3.MVCC（Multiversion Concurrency Control）多版本并发控制

4.事务

5.索引

6.锁

7.redo log 和 bin log

8.小表、大表驱动选择

9.mysql常用的优化

1.MySQL架构设计图

（图片来源于：《MySQL45讲》）

MySQL分为server层、引擎层。引擎层是插件式的，常见的有MylSAM,InnoDB。InnoDB在MySQL 5.5.5版本以后成为了默认引擎。其中server层的查询缓存一般可以关闭（MySQL8.0后已移除查询缓存功能），查询缓存的设计是key，value的；将查询语句作为key，查询结果作为value，当查询语句有一个空格的改变，或者对其中一个表的数据有修改或者新增，查询缓存就失效了，所以建议关闭。

2.存储引擎MylSAM和InnoDB的区别

1. 是否支持行级锁。MylSAM支持最小粒度的锁为表级锁，InnoDB支持行级锁和表级锁，默认为行级锁。
2. 是否支持事务和crash-safe能力。MylSAM不支持事务和不具备crash-safe的能力，InnoDB都有。
3. 是否支持外键。MylSAM不支持外键，InnoDB支持。不过《阿里Java开发手册》上强制不让使用外键与级联，一切外键概念必须在应用层面解决。以下为给出的解释：

       以学生和成绩的关系为例，学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的student_id，同时触发成绩表中的studengt_id更新，即成为级联更新。外键和级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

4. 是否支持MVCC。MVCC为InnoDB独有，MVCC在高并发下比单纯的加锁更高效。MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。

5.是否有redo log。redo log（重做日志）为InnoDB独有，正是因为有redo log，当数据库发生异常重启的时候，数据不会丢失，所以InnDB有crash-safe能力。redo log 详细的在第7小节总结。

3.MVCC（Multiversion Concurrency Control）多版本并发控制

4.事务

5.索引

6.锁

7.redo log 和 bin log

8.小表、大表驱动选择

9.mysql常用的优化

     9.1 设计规范，好的优化应该从设计开始注意：

1. 存储相同名称，相同类型的列时，数据类型应该设置为一致。一般用作关联的列，不一致会导致隐式转换的问题，导致索引失效。
2. 设置合适的字符存储长度。可以节约数据库表空间和索引存储，且可以提升检索速度；列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差。例如存储flag标志的时候，应使用tinyint（1）；
3. char和varchar。char用于定义一个固定长度的字符串，范围（1-255）在定义字段时就必须指定长度，会预先分配存储空间，如果未达到指定长度时，会使用空格填充达到指定长度，如果数据长度差异较大时，会造成空浪费，且读取数据时候需要trim()一下去除空格。但是当数据长度固定（例：手机号，身份证号）时，使用char就很合适，因为char长度固定，在检索时不需要考虑边界问题，检索速度快于varchar；varchar是可变长字符串，不预先分配存储空间，使用起来更加灵活，但是长度不要超过5000个字符。如果长度超过5000个字符，应将字段类型定义为text。且独立出来一张表，使用主键关联，避免应该其他字段的索引效率。
4. 将所有的列定义为NOT NULL。索引 NULL 列需要额外的空间来保存，所以要占用更多的空间进行比较和计算时要对 NULL 值做特别的处理。
5. 表必须有主键。建议使用int或者bigint。

9.2 索引设计

1. 单张表的索引，建议不要超过5个，超过5个就需要考虑表设计的合理性。过多索引会导致数据插入更新速度变慢。
2. 常用设置索引的列。常用出现在where、order by、group by、distinct后面的列；用作表join的列。
3. 数据区分度低的列不应该加索引。例：sex：0-女、1-男
4. 业务上具有唯一性的字段，应设置为唯一索引。
5. varchar字段设置索引的时候，需要注意索引长度。不要全字段设置索引，一般不建议varchar字段设置索引。
6. 经常查询的列，可巧妙运用覆盖索引。避免回表。
7. 联合索引顺序。如果通过调换组合索引的位置，可以减少一个索引，那么应该把那个索引放在左边。若无这种情况，可以把区分度更高的列放在左边。

9.3 查询技巧