1. 存储引擎

MySQL 5.5版之前的默认数据库存储引擎是 MyISAM。
MySQL 5.5版本后的默认数据库存储引擎是 InnoDB。

区别 MyISAM InnoDB
是否支持行级锁 只有表级锁 支持行级锁和表级锁，默认行级锁
是否支持事务和崩溃后安全恢复 查询具有原子性，但不提供事务支持
不具有崩溃后安全恢复能力		 提供事务支持，外部键等高级功能
具有事务、回滚、崩溃修复能力
是否支持外键 不支持 支持
是否支持MVCC 不支持 支持
存储引擎实现方式 非聚簇索引 聚簇索引

MVCC：multiple version concurrent control

2.索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B树， B+树和Hash。

2.1. 索引的优缺点：

优点：

  1. 加快数据的检索速度
  2. 创建唯一性索引可以保证数据库表中每一行数据的唯一性

缺点：

  1. 创建索引和维护索引耗费很多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低SQL执行效率。
  2. 占用物理存储空间：索引需要使用物理文件存储，也会耗费一定空间。

2.2. 索引结构

2.2.1. B树 和 B+树的区别

  1. B树的所有节点既存放 键(key) 也存放 数据(data);而B+树只有叶子节点存放 key 和 data，其他内节点只存放key。
  2. B树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  3. B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。


2.2.2. Hash索引和 B+树索引优劣分析

Hash索引：指的就是Hash表，最大的优点就是能够在很短的时间内，根据Hash函数定位到数据所在的位置，这是B+树所不能比的。
Hash索引缺点：

  1. Hash冲突
  2. Hash索引不支持顺序和范围查询（最大的缺点）

B+树索引：有序，在范围查询中优势很大

所以Hash索引适合单条查询，B+树索引适合范围查询。

2.3. 索引类型

2.3.1. 主键索引（Primary Key）

  1. 数据表的主键列使用的就是主键索引。
  2. 一张数据表有只能有一个主键，并且主键不能为null，不能重复。
  3. 在mysql的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

2.3.2. 二级索引（辅助索引）

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等索引属于二级索引。

  1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
  2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
  3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
  4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。


2.3.3. 聚簇索引与非聚簇索引

聚簇索引
索引结构和数据和数据一起存放的索引。主键索引属于聚簇索引。

聚簇索引的优缺点：
优点：

  1. 聚簇索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

缺点：

  1. 依赖于有序的数据：B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，数据是整型还好，如果类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  2. 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引
索引结构和数据分开存放的索引。二级索引属于非聚簇索引。
非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚簇索引的优缺点：
优点：

  1. 更新代价比聚簇索引小，因为非聚簇索引的叶子节点是不存放数据的。

缺点：

  1. 非聚簇索引也依赖于有序的数据，这点和聚簇索引一样；
  2. 可能会二次查询（回表），这是非聚簇索引的最大缺点。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

查找内容本身就建立了索引：select id from table where id=1;，这种情况就不需要进行回表查询

2.3.4. 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

  1. 如主键索引，如果一条SQL需要查询主键，那么正好根据主键索引就可以查到主键。
  2. 再如普通索引，如果一条SQL需要查询times，times字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

例：select

2.4. 索引创建原则

单列索引：由一列属性组成的索引。

组合索引（多列索引）：由多列属性组成的索引。

最左前缀原则：假设创建的组合索引由3个字段组成：ALTER TABLE table ADD INDEX index_name(a,b,c);
当查询条件为 a/ a AND b / a AND b AND c 时，索引才会生效。

选择合适的字段作为索引：

  1. 不为NULL的字段，如果为NULL，建议修改为0或false这种短值短字符代替。
  2. 被频繁查询的字段
  3. 被作为条件查询的字段
  4. 被经常用于连接的字段

不适合创建索引的字段：

  1. 被频繁更新的字段：增加维护索引的成本
  2. 不被经常查询的字段：没必要建索引
  3. 尽可能建组合索引，而非单列索引：一个索引就要对应一棵B+树，组合索引的多个字段在一个索引上，可以减少B+树的创建，节约磁盘空间。
  4. 冗余索引：如(name,age) 和 name，尽量扩展已有索引，而不是创建新的索引。
  5. 字符串类型用前缀索引代替普通索引，前缀索引仅限于字符串类型，会比普通索引占更小的空间。

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

3. 事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。（典型的银行转账案例）

3.1. 事务的四大特性（ACID）

  1. 原子性（Atomicity）：事务的最小执行单位，不允许分割。确保动作要么全部完成，要么完全不起作用。
  2. 一致性（Consistency）：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。
  3. 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他的事务干扰，各并发事务之间的数据库是独立的。
  4. 持久性（Durability）：一个事务被提交以后，它对数据库中的数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

3.2. 并发事务带来的问题

  1. 脏读：读其它事务未正式提交的数据。
  2. 丢失修改：两个事务都对一个数据进行修改，最终只保留了一个事务的修改结果。

例:money=30,
事务A:money=money-10 ;
事务B:money=money-15 ；
结果money=15，事务A的修改丢失。

  1. 不可重复读：事务内多次读取同一数据时，某次读到了其它事务提交修改的数据。

例:money=30,
事务A: 第一次读 money=10;
第二次读 money=10;
事务B: money= money+15;
事务A: 第三次读 money = 25。

  1. 幻读：事务内的某次查询比之前查到的多了几行。

例: 记为操作X ：select id,name,age from test where age<20;
事务A 第一次操作X，{1, “xiaoming”, 10}
事务B insert into test values(2, “xiaoqiang”, 20);
事务A 第二次操作X，{1, “xiaoming”, 10},{2, “xiaoqiang”, 20}

不可重复读与幻读的区别：

  • 不可重复度是读取同一行数据时产生的问题；
  • 幻读是读取某几行数据时产生的问题；

3.3. 事务的隔离级别

SQL标准定义了4个隔离级别：
READ-UNCOMMITTED（读未提交）：允许读取尚未提交的数据变更，导致脏读、不可重复度、幻读；
READ-COMMITTED（读已提交）：允许读取并发实物已提交的数据，导致不可重复度和幻读；
REPEATABLE-READ（可重复读）：同同一字段的多次读取结果一致，导致幻读；
SERIALIZABLE（可串行化）：最高隔离级别，完全服从ACID，事务之间没有干涉。

隔离级别 脏读 不可重复读 幻读
READ-UNCOMMITTED √ √ √
READ-COMMITTED × √ √
REPEATABLE-READ × × √
SERIALIZABLE × × ×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。
InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

4. 锁机制与InnoDB锁算法

4.1. MyISAM和InnoDB存储引擎使用的锁：

  • MyISAM采用表级锁(table-level locking)。
  • InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

4.2. 表级锁和行级锁对比：

  • 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。

其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
事务更新大表中的大部分数据直接使用表级锁效率更高；

  • 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁导致回滚。

    • Record Lock：对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
    • Gap Lock：对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
    • Next-key Lock：锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

  • 页级锁： 锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。页级进行了折衷，一次锁定相邻的一组记录。BDB支持页级锁（MySQL不支持）。开销和加锁时间界于表锁和行锁之间，会出现死锁。锁定粒度界于表锁和行锁之间，并发度一般。

    4.3. 锁分类：

    按照粒度分类，可以分为表级锁和行级锁，上面已经总结过了。
    按照是否可写分类：可以进一步分为共享锁（s）和排它锁（X）。

一句话概括：
加了共享锁的数据，其它事务只能继续加共享锁读取，直到所有的共享锁都被释放。
加了排它锁的数据，只允许加锁的事务读取和修改，其他事务不能读取修改，直到排它锁被释放。

4.3.1. 共享锁（S）

共享锁（Share Locks，简记为S）又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。

共享锁(S锁)又称为读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

4.3.2. 排它锁（X）

排它锁（Exclusive lock,简记为X锁) 又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。

4.3.3. 共享锁和排它锁的区别：

  1. 共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不 能加排他锁。获取共享锁的事务只能读数据，不能修改数据。
  2. 排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获取排他锁的事务既能读数据，又能修改数据。

4.3.4. 意向锁

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

InnoDB意向锁为表级锁：
意向共享锁（IS）：表示事务准备给数据行加入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

4.3.5. 死锁和避免死锁

InnoDB的行级锁是基于索引实现的，如果查询语句未命中任何索引，那么InnoDB会使用表级锁. 此外，InnoDB的行级锁是针对索引加的锁，不针对数据记录，因此即使访问不同行的记录，如果使用了相同的索引键仍然会出现锁冲突。

  • MyISAM一次性获得所需的全部锁。
  • InnoDB的锁是逐步获得的，所以当两个事务都需要获得对方持有的锁，导致双方都在等待，于是产生了死锁。InnoDB检测到死锁后会将一个事务释放锁并回滚，另一个事务获得锁并完成事务。

如何避免死锁：

  • 通过表级锁来减少死锁产生的概率；
  • 多个程序尽量约定以相同的顺序访问表；
  • 同一个事务尽可能做到一次锁定所需要的所有资源。

5. 大表优化

从以下几个方面来优化：
字段、索引、SQL语句引擎、系统参数调优、读写分离、缓存、表分区、垂直拆分、水平拆分

5.1. 字段

  • 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT,如果非负则加上UNSIGNED
  • VARCHAR的长度只分配给真正需要的空间
  • 使用枚举或整数代替字符串类型
  • 尽量使用TIMESTAMP而非DATETIME
  • 单表不要有太多字段，建议20以内
  • 避免使用NULL字段，很难查询优化且占用额外的索引空间
  • 用整形来存IP

5.2. 索引

  • 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
  • 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
  • 值分布很稀少的字段不适合建索引，例如”性别”这种只有两三个值的字段
  • 字符字段只建前缀索引
  • 字符字段最好不要做主键
  • 不用外键，由程序保证约束
  • 尽量不用UNIQUE，由程序保证约束
  • 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

5.3. 查询SQL

  • 可通过开启慢查询日志来找出较慢的SQL
  • 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
  • sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
  • 不用SELECT *
  • OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
  • 不用函数和触发器，在应用程序实现
  • 避免%xxx式查询
  • 少用JOIN
  • 使用同类型进行比较，比如用’123’和’123’比，123和123比
  • 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
  • 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
  • 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

5.4. 引擎

MyISAM：对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用，适合SELECT密集型的表
InnoDB：适合INSERT 和 UPDATE 密集型的表

5.5. 读写分离

从库读，主库写。

5.6. 表分区

表分区好处：

  • 可以让单表存储更多的数据
  • 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
  • 部分查询能够从查询条件确定只落在少数分区上，速度会很快
  • 分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备
  • 可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
  • 可以备份和恢复单个分区

分区的限制和缺点：

  • 一个表最多只能有1024个分区
  • 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
  • 分区表无法使用外键约束
  • NULL值会使分区过滤无效
  • 所有分区必须使用相同的存储引擎

分区的类型：

  • RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
  • LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
  • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
  • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

分区最适合的场景：数据的时间序列性比较强，则可以按时间来分区

5.7. 垂直拆分

根据数据库表的相关性进行拆分。
优点：

  • 可以使行数据变小，一个数据块（Block）能存放更多的数据，在查询时减少IO
  • 可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放在一起，将经常改变的放在一起
  • 数据维护简单

缺点：

  • 主键出现冗余，需要管理冗余列
  • 会引起表连接的JOIN操作（增加CPU开销），可以通过在业务服务器上进行join来减少数据库压力
  • 依然存在单表数据过大的问题（需要水平拆分）
  • 事务处理复杂

5.8. 水平拆分

水平拆分是通过某种策略将数据分片来存储，分为 库内分表 和 分库 两部分。
库内分表：单纯解决了单一表数据过大的问题，没能降低单台服务器的访问压力和IO、CPU、网络等。
分库：每片数据分散到不同的MySQL数据库，达到分布式的效果。

优点：

  • 不存在单库大数据和高并发的性能瓶颈
  • 应用端改造较少
  • 提高了系统的负载能力和稳定性

缺点：

  • 分片事务一致性难以解决
  • 跨节点join性能差，逻辑复杂
  • 数据库多次扩展 和 维护量 很大

5.9. 分片原则

  • 能不分就不分，参考单表优化
  • 分片数量尽量少，分片尽量均匀分布在多个数据节点上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果。只在必要时进行扩容，增加分片数量
  • 分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容
  • 尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题
  • 查询条件尽量优化，尽量避免 select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。
  • 通过数据冗余和表分区来降低跨库join的可能。

参考文献

https://blog.csdn.net/qq_34337272/article/details/80611486
https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/MySQL.md#%E6%9F%A5%E8%AF%A2%E7%BC%93%E5%AD%98%E7%9A%84%E4%BD%BF%E7%94%A8