前言

本文是Mysql使用的一些总结，包含了常见面试题、日常开发的经验。适合有一定Mysql基础知识的开发人员复习，从数据库的一些基本特性、数据库调优两个方面帮您回顾整个Mysql。

1、数据库的基本特性

Mysql数据库支持InnoDB和MyISAM两种存储引擎，InnoDB是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

1.1、事务

事务指的是满足 ACID （原子性、一致性、隔离性、持久性）特性的一组操作。在并发环境下，事务的隔离性很难保证，会产生以下三种一致性问题：

  • 脏读：当前事务可以读到另外事务未提交的数据。
  • 不可重复读：一个事务内多次读取同一数据不一样。（修改、删除）
  • 幻读的问题：一个事务内多次读取同一数据不一样。（插入）

1.2、隔离级别

为了保证数据的一致性，标准定义了4类隔离级别，分别是：

  • 未提交读（READ UNCOMMITTED）
    所有事务均可以看到其它未提交事务的执行结果。
  • 提交读（READ COMMITTED）
    所有事务只能看到其它已提交事务的执行结果。（Oracle 默认使用的事务隔离级别）
  • 可重复读（REPEATABLE READ）
    在同一个事务中多次读取同一数据的结果是一样的。（Mysql的InnoDB默认支持的事务隔离级别）
  • 可串行化（SERIALIZABLE）
    完全串行化的读，每次读都需要获得共享锁，读写相互会阻塞。

1.3、封锁

从封锁的粒度区分，MySQL 中提供了两种封锁粒度：行级锁以及表级锁。行级锁就是锁住一行，表级锁就是锁住整个表，在系统并发程度上行级锁比表级锁要强。从封锁的类型区分，MySQL 中提供了两种封锁类型：读写锁以及意向锁。

1.3.1、读写锁

读写锁分为读锁（共享锁）、写锁（互斥锁）。读写锁的两个规则：一个事务加了写锁，可以执行读取和更新，加锁期间其它事务不可以加任何锁；一个事务加了读锁，可以执行读取，不能执行更新，其它事务可以加读锁，不能加写锁。

1.3.2、意向锁

意向锁是实现多粒度锁的一种方式。意向锁在原来的读写锁之上引入了意向读锁、意向写锁，意向读/写锁都是表锁，用来表示一个事务想要在表中的某个数据行上加写锁或读锁。有以下两个规定：一个事务在获得某个数据行对象的读锁之前，必须先获得表的意向读锁或者更强的锁；一个事务在获得某个数据行对象的写锁之前，必须先获得表的意向写锁。注意：申请意向锁的动作是数据库完成的，程序员无需使用代码申请。

1.4、MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制，提供并发访问数据库时，对事务内读取的到的内存做处理，用来避免写操作堵塞读操作的并发问题。MVCC的基本思想是：写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系。

1.4.1、InnoDB的MVCC实现机制

InnoDB的MVCC实现机制主要通过三个方面：事务版本号、Undo日志、ReadView。

事务版本号TRX_ID是事务开始时的系统版本号，系统版本号SYS_ID是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。

Undo日志中存储多个版本的快照，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。快照中记录事务版本号TRX_ID、标记删除DEL和执行操作。

ReadView 结构主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, ...}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。 在进行 SELECT 操作时，判断数据行快照是否可以使用：

  • TRX_ID < TRX_ID_MIN：表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
  • TRX_ID > TRX_ID_MAX：表示该数据行快照是在事务启动之后被更改的，因此不可使用。
  • TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX：需要根据隔离级别再进行判断
  • 提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
  • 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

2、数据库调优

2.1、磁盘访问原理

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。如果数据不在同一个磁盘块上，那么通常需要进行寻道（磁头从开始移动到数据所在磁道），从而增加磁盘数据读取时间。


面试小知识：为什么B+Tree 比红黑树更适合做索引？

B+ Tree 相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。

2.2、索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

2.2.1、B+Tree 索引

B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型，在查找的时候，只需要对树进行搜索，不再需要进行全表扫描，速度会快很多。B+ Tree 的有序性，还可以用于排序和分组。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

2.2.2、哈希索引

哈希索引能以 O(1) 时间进行查找，因为无序，因此只支持精确查找，无法用于部分查找和范围查找，也不支持排序和分组。

2.2.3、全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。InnoDB 存储引擎在 MySQL 5.6版本之后InnoDB存储引擎开始支持全文索引。

2.2.4、空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

2.3、单表优化

2.3.1、字段
  • 使用枚举或整数代替字符串类型
  • 尽量使用TIMESTAMP而非DATETIME
  • 单表不要有太多字段，建议在20以内
  • 避免使用NULL字段，很难查询优化且占用额外索引空间
  • 用整型来存IP
2.3.2、索引
  • 尽量在WHERE子句中让选择性最强的索引列放在前面
  • 尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎无法走索引而全表扫描
  • 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
  • 字符字段只建前缀索引
  • 需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好
2.3.3、查询SQL
  • 可通过开启慢查询日志来找出较慢的SQL
  • 列运算会导致索引失效，尽量避免
  • sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
  • 不用SELECT *
  • OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
  • 不用函数和触发器，在应用程序实现
  • 避免%xxx式查询
  • 使用同类型进行比较，比如用’123’和’123’比，123和123比
  • 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
  • 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
  • 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

2.4、系统调优参数

具体的调优参数内容较多，这里介绍一些比较重要的参数：

  • back_log：堆栈中的新请求数，可以从默认的50升至500
  • wait_timeout：数据库连接闲置时间，可以从默认的8小时减到半小时
  • max_user_connection: 最大连接数，默认为0无上限，最好设一个合理上限
  • thread_concurrency：并发线程数，设为CPU核数的两倍
  • skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问
  • innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB
  • query_cache_size：缓存MySQL中的ResultSet，一般不建议太大
  • thread_cache_size：连接池中线程的大小，设置一个合理值可以快速响应

结束语