并不是一本好书——SQL笔记

在知乎上发过一次，这里也发一遍吧

--------正文开始--------

草草翻完了《高性能MySQL》，印象最深的地方就是：这确实不适合初学者去看。

花了三个月的时间慢慢看完了这本，看的一头雾水。第一章概论讲了不少新奇的概念，比如隔离级别，比如多版本并发控制(MVVC)。但是从第二章起，所讲的内容基本就和日常应用没什么太大关系了。如果要简单概括一下的话就是

1. 储存引擎务必使用InnoDB

2. 创建新数据库时要用最新的MySQL版本(当然是GA版)

3. 轻易不要升级MySQL

然后就没了。对于其他的内容，不管是服务器性能优化还是MySQL主从备份，这些其实都和一名普通的技术人员没有关系。相较于书中提到的通过修改frm文件实现快速新增列这种神奇方法，技术人员最好的做法还是：尽可能的避免使用alter语句或者说在项目一开始时就要想好数据表的用途。如果你是想改进自己的SQL水平的话，那应该去看 SQL快速优化300例 ,至少，不是这本书。

写一下收获吧(SQL相关，不仅仅是MySQL)，毕竟翻完了不是。

1. 尽量避免在线上使用alter

在使用alter新增列时,会引发一个全表锁，数据库会暂停响应直到新列添加完成(在已有数据中添加新列,在数据库表结构中添加列定义)。锁定的时间随表内数据量的大小线性增长。如果是在线上环境的话，即使很短的时间，也够阻塞不少请求了←_←

2. 可以通过冗余数据来提升SQL查询的性能

在标准的数据库教科书中，数据表结构按说是要符合范式要求的(1NF~5NF)。比如，通过把用户信息和订单内容独立开，这样就可以设计出来没有冗余数据的数据库表结构——俗称学院派数据库表结构。但是，学院派依托的环境是银行系统这样的大型工程，查询带来的性能损耗远小于冗余数据带来的损耗。但在真实应用中，表里的数据很少能达到1000万行以上的，在这时，大部分的性能全浪费在多次查询上了。这时候，学院派的做法就不如直接在数据表中添加冗余数据(例如把用户手机号和订单一起存取),这样在展示的时候一条SQL就可以搞定。

> 『PHP绝大部分的性能都浪费在和MySQL服务器通讯上了』

3. 使用tinyint或者varchar作为枚举类型，而不是使用enum

理由很简单: enum在新增类型的时候需要使用alter语句进行全表新增，线上数据库时不时的来上一回全表锁谁受的了。。。

一般来说，使用 tinyint + 代码中利用常量进行定义 是最好的方案，如果要增强可读性的话可以使用varchar, 因为常量一共也不超过10个字母，从性能上来说varchar也可以接受

4. 可视化工具

客户端的话个人建议使用adminer,有ngnix之后配上一个index.php文件就能用。非要使用客户端的话用MySQL Workbench也可以，MySQL自己出的。这两个都在《高性能MySQL》的推荐之列，可以考虑

5. 使用调试语句查看性能

常用的调试语句如EXPLAIN, SHOW这种，现用现查即可

6. 索引数据一般都在内存里

结论:排序时直接使用索引排序是最快的,索引不要太多，太多之后跟把整个表放内存里就等效了(还不如使用Redis)

补充:排序时EXPLAIN发现不是index,而是filesort也不用太担心，因为只有这两种状态，不是index就是filesort，性能只要不是太坑直接上就行

7. 分表分库，历史数据独立建表

MySQL处理1000万行以下的数据时性能是非常好的——那1000万行以上时怎么办呢？

直接分表啊。

比如，可以按时间分，自增id在500万之前的，独立分到一个表里，在程序代码里写死，用到的时候再去读

或者，按一个数取模，根据余数选择对应的表。

8. 对于重要数据，一定要开启二进制日志

手滑删过全表的同学都懂。。。

然后，解释下两个概念：

1. 隔离级别

每执行一次SQL称为一件事务，如果事务所涉及到的内容在事务进行中发生了改变，对应于事务所能读取到的实际内容，就产生了四种标准情况，这四种标准情况被称为四种隔离级别(仅就MySQL而言，对于其他数据库实现可能会有不同的区分标准)

1. READ UNCOMMIT(未提交读)

在READ UNCOMMIT级别中，即使没有提交，每个事务的操作对于其他事务也都是可见的。在这种情况下，事务可以读取未提交的数据，又称脏读(DIRTY READ)。从性能上来说，脏读并不比其他模式优秀多少，但是会引发各种严重的问题(比如说银行存款数据写入到一半来了一个读操作。。。)。一般情况下，不建议使用

2. READ COMMIT(未提交读)

大部分数据库系统默认的隔离级别都是READ COMMIT(但MySQL不是)。在READ COMMIT这一级别中，事务所修改的数据只有提交了之后才会被其他事务读取到。换句话说，一个事物从开始之后到结束之前，所做的任何修改对其他事物都是不可见的。这个级别实际上已经比较符合我们读取数据的预期了。但是，如果执行两次同样的查询，可能会出现两遍结果不一致的情况(查询执行过程中有其他事务提交完成),所以，这一级别又叫不可重复读(nonrepeatable read)

3. REPEATABLE READ(可重复读)

REPEATABLE READ解决了脏读的问题，同时也是MySQL的默认事务隔离级别。这一级别保证了在同一个事务中多次读取同样的记录结果是一致的。但是理论上，可重复读还是没法解决幻读(Phantom Read)的问题。幻读是指：在某个事务读取某个范围内的的记录时(id>1 && id < 100),另外一个事物又再该范围内插入了新纪录，当之前的事务再次读取该范围的记录时，就会产生幻行(Phantom Row)。不过InnoDB通过多版本并发控制(MVVC, Multiversion Concurrency Control)解决了这个问题

4. SERIALIZABLE(可串行话)

SERIALIZABLE是最高的隔离级别。它通过强制让事务串行执行，可以避免前面所说的全部问题。本质上说，SERIALIZABLE会在读取的每一行数据上都加上锁, 对性能影响非常严重。只有在非常需要确保数据一致性，且可以接受没有并发的情况下，才可以考虑使用此级别


2. 多版本并发控制


这是个很玄乎的词，但说白了就是：通过保存数据在某个时间点的快照，来确保对于不同开始时间的事务，他们对于同一张表，在同一时刻看到的数据都是一样的。


对于InnoDB来说就是：通过在每行记录后边保存两个隐藏列，一列记录创建时间，一列记录过期时间（实际上存储的是系统版本号）,每开始一个事务，系统版本号都会自动递增。在事务开始时刻的系统版本号就会作为事务的版本号，用来作为数据库查询的依据，以此实现：多版本并发控制


大致就这些。看起来很高大上的一本书，实际上看了跟没看差不多(DBA除外)。不推荐阅读/购买