一、SQL 语义陷阱

引号的方言差异

标准的 SQL 中只允许用单引号表达字符串类型。有些 SQL 方言允许使用双引号包裹字符串，如 MySQL，有些则不允许，如 Oracle。

反引号是专门用来表达 identifier 的。

字符串与数字的隐式转换 pitfall

Data truncation: Truncated incorrect

不要小看 MySQL，它出 warning 就一定有错误。

不要滥用 MySQL 字符串到decimal，和 decimal 到 string 的转换。这样有时候 MySQL 不只是 warning。

二、存储引擎对比

参考：https://www.zhihu.com/question/20596402

MyISAM 与 InnoDB 的区别

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

如何选择

是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

三、配置与运维

MySQL 常用配置与状态查询

-- 查看自动提交
SELECT @@autocommit
-- 查看全局隔离级别和会话隔离级别
SELECT @@global.tx_isolation, @@tx_isolation;
-- 查看引擎的事务状态，这里可以看出死锁日志，但需要  PROCESS privilege(s)
show engine innodb status
# 查看表详情
show table status like 'dept_emp'
#  查看当前存储引擎默认的行格式
SHOW VARIABLES LIKE '%innodb_default_row_format%'

# 查看全部 binlog 文件
show binary logs;
# 查看最新的binlog，带有 position
show master status;

# 查看某个 binlog 的内容
show binlog events in 'binlog.000156';

压缩

压缩算法

Table Compression

InnoDB存储引擎是按照索引组织表（index-organized table）的方式组织数据的，数据存储在B-tree索引（clustered index/primary key & secondary index）中。Table Compression是针对整个表，和相关索引进行的，而不是单独的数据行。

B-tree页经常被更新，InnoDB会尽量减少B-tree节点的分裂（split），减少不必要的压缩和解压页。为此，InnoDB在每个B-tree页中都预留了未压缩的"modification log"空间，记录页的变更。对于update和insert的数据量较小时，会先写入"modification log"，不用立刻重构整个页。当"modification log"空间用完了，InnoDB解压该页，应用变更（apply），然后重新压缩。如果压缩失败，该B-tree叶节点就要进行分裂了。在写入量比较大的场景，比如某些OLTP应用，为了避免频繁压缩失败，InnoDB会在页中保留一些额外空间（padding），在"modification log"用完，页重构时，仍有足够的空间避免分裂。

在压缩表中，每个压缩的页（1K, 2K, 4K 或 8K）都对应着没被压缩的16K的页。当检索该页中的数据时，若不在内存中（Buffer Pool），MySQL会将其从存储中读取到内存，解压。为了降低I/O和减少解压次数，有时内存中会同时存在压缩的页，和非压缩的页。为了维持可用的内存，MySQL会将非压缩的页回收掉，只保留压缩的页在内存中，又或一个页较长时间没被访问，压缩页会被写回磁盘，以释放内存。对于Table Compression，数据会被压缩变小，在存储中读写数据时，能有效减少I/O次数，提升吞吐量。压缩的数据读入到内存中，会进行解压；写入存储时，会进行压缩。压缩解压过程会消耗CPU资源。在内存中，会同时存在压缩的页，和非压缩的页，内存空间使用比正常要大。

可见使用Table Compression，会大幅提升I/O效率，消耗更多CPU资源，消耗更多内存资源。在生产负载下，Table Compression特性能否在有效降低存储空间的前提下，还保持一个不错的性能，往往就取决于该三个因素的相互作用结果。

Page Compression

Page Compression需要借助PUNCH HOLE特性，该特性要操作系统和文件系统的支持（Centos7的ext4和xfs文件系统都是支持PUNCH HOLE的）。在Linux操作系统上，存储的最小单元是一个块的大小（Block size ），Page Compression压缩成功的条件是page compressed size ≤ page size - N*Block size（N≥1，且整数）。比如innodb_page_size=16K，Block size=4K，该页被压缩为12K，这样在磁盘上就占用3个Block size，从而节省了一个Block size。整个过程比较简单，利用数据压缩 + PUNCH HOLE来实现，数据页在内存中表现是一个正常的页，只在读写到磁盘时，才进行数据压缩、解压处理。

可见使用Page Compression，会大幅提升I/O效率，消耗更多CPU资源，但相比Table Compression不会消耗那么多的内存资源，在生产负载下，Page Compression特性能否在有效降低存储空间的前提下，提供一个良好的性能，取决于该两个因素的相互作用，以及设备对于PUNCH HOLE特性的支持程度。

压缩比

normal-16k，innodb_page_size = 16384。

tabcmp-8k，Table Compression，ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8。

tabcmp-4k，Table Compression，ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4。

pcmp，Page Compression/Transparent Page Compression，COMPRESSION=‘zlib’（有zlib和lz4两种压缩算法，选择取决于业务对存储空间的诉求，一般zlib压缩比高于lz4，所以zlib。）。

压缩比 = 压缩后数据大小/压缩前数据大小。

对于压缩，tabcmp-8k压缩比为22%，tabcmp-4k压缩比为32%，pcmp压缩比为44%。

性能比

性能比 = 压缩后QPS/压缩前QPS。

性能比，各OLTP场景tabcmp-8k在70%左右，tabcmp-4k和pcmp不稳定。对于线上业务实际负载，相对极限压测，根本不会达到这么高，那么70%的性能比，还是不错的。

什么业务适合开启压缩

数据特点

影响压缩成功率的一个关键因素是数据本身的属性，对于字符串的压缩效果一般都比较好，比如数据类型是CHAR，VARCHAR，TEXT或BLOB这样的字段。相反对于二进制数据的压缩效果一般都不是太好，比如数据类型是int或float这样的字段。

负载特点

负载类型是开启压缩要考虑的另一个关键因素。根据Table Compression和Page Compression工作原理示意图可见，开启压缩后的性能情况，是I/O能力的提升，与CPU使用率上升，内存使用效率下降，多方因素相互作用的一个结果。经此也不难看出，对于I/O bound，而不是CPU-bound的，读多写少的负载特点，压缩对于性能提升是有帮助的，退而求其次不至于性能下降太多，业务无法接受，而此时我们已经多收获了40%~60%的存储空间。