MySQL

1. MySQL引擎

	MyISAM	Innodb
事物支持	不支持	支持
锁的粒度	只支持表锁	支持行锁
select count(*) from table	直接读取，当有where条件时也需要遍历表	需要遍历表
外键	不支持	支持
索引	非聚集索引	聚集索引
压缩表	支持，压缩后的表只读	不支持
地理空间数据索引	支持	不支持

区别记忆口诀：“事外行”-“可压空间查询快”

应用场景：

1. MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyISAM
2. InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

其他引擎：

• Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

MySQL的存储引擎为MyISAM 不支持事务是什么意思：

首先要了解数据库里的事务是什么意思。事务在计算机数据库里 ：在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。简单的讲 举例来说：A 汇100块钱给 B，A 减少100 ，B增加100， 这是一个事务，只要中间有任何错误，这个事务的所有操作都要撤销。所以MyISAM不支持事务就是这个意思，需要程序进行撤销操作。Innodb 是支持事务的。
　　mysql的存储引擎包括：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。
　　最常使用的2种存储引擎:
　　1.Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。
　　2.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

聚簇索引和非聚簇索引的区别：

在《数据库原理》里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引。

2. 索引

2.1 索引分类

索引的优点

• 大大减少了服务器需要扫描的数据行数。
• 帮助服务器避免进行排序和创建临时表（B+Tree 索引是有序的，可以用来做 ORDER BY 和 GROUP BY 操作）；
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，也就将相邻的数据都存储在一起）。

2.1.1 B 树或 B+ 树索引

B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

B+Tree 索引适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。

如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。

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

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

为什么使用 B 数或 B+ 树？

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B Tree 作为索引结构，主要有以下两个原因：

（一）更少的检索次数

平衡树检索数据的时间复杂度等于树高 h，而树高大致为 O(h)=O(logdN)，其中 d 为每个节点的出度。

红黑树的出度为 2，而 B Tree 的出度一般都非常大。红黑树的树高 h 很明显比 B Tree 大非常多，因此检索的次数也就更多。

B+Tree 相比于 B-Tree 更适合外存索引，因为 B+Tree 内节点去掉了 data 域，因此可以拥有更大的出度，检索效率会更高。

（二）利用计算机预读特性

为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，因此速度会非常快。

操作系统一般将内存和磁盘分割成固态大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点，并且可以利用预读特性，相邻的节点也能够被预先载入。

使用 B+ 树的理由

数据库索引采用 B+ 树的主要原因是 B树在提高了磁盘 IO 性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+ 树应运而生。B+ 树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低） 。

2.1.2 哈希索引

InnoDB 引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找；

2.1.3 全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

2.4. 空间数据索引（R-Tree）

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

必须使用 GIS 相关的函数来维护数据。

2.2. 索引失效

带头大哥不能死，中间兄弟不能断

这句话特指多列索引，。如 (a,b,c) 建立的索引，条件中 (a,b,c) 使用顺序也必须时(a,b,c) ，尾部缺失仍可使用该列索引，头部和中间缺失则不行。

计算类转还有null，模糊or到非索引，范围之后全失效。

索引列上计算、函数调用或类型转换都会使索引失效。

不等于和like的左模糊查询会使索引失效。

or连接到非索引列会使索引失效。

范围查询之后的索引全部失效。

2.3 sql优化

覆盖索引要多用

只返回索引列，要比返回 * 快，会使用到索引。包括 ORDER BY 也最好覆盖索引。

覆盖索引只 select 返回的字段只通过索引就可获取相应的值，不用去获取行

最强索引放前面

让选择性最强的索引列放在前面，索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。

分解切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

MySQL 优化常用工具

• 慢查询日志（查询哪些语句执行次数最多，哪些语句执行比较慢）
• explain （显示 select 语句的索引使用形况）
• profile（性能分析，sql 时间，CPU/内存使用量，锁时间）

3. MySQL 的锁与隔离级别

3.1 封锁

封锁粒度

MySQL 中提供了两种封锁（封锁即加锁）粒度：行级锁以及表级锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。

但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

封锁类型

读写锁：

• 排它锁（Exclusive），简写为 X 锁，又称写锁。一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。MySQL 通过在事物中用 select ... for update 语句手动加排它锁。
• 共享锁（Shared），简写为 S 锁，又称读锁。一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。

意向锁：

使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。 MySQL中主要用来解决表锁和行锁共存的情况，试想一下，若事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

• 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
• 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

这样表锁只需检查IS或IX就可知道有没有行锁的存在。

封锁协议

1）三级封锁协议

• 一级封锁协议 修改数据时必须加X锁，事物结束时才能释放。解决丢失更改问题。因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。
• 二级封锁协议 一级协议的基础上，读取数据必须加 S 锁，读完立即释放，解决了脏读问题。因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
• 三级封锁协议 在二级协议的基础上，读取数据必须加 S 锁，事物结束时才能释放，解决了不可重复读问题。因为读数据时，其它事务不能对该数据加 X 锁，从而避免了在读的期间数据发生改变。

2）两段锁协议

加锁和解锁分为两个阶段进行。

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。

事务遵循两段锁协议是保证可串行化调度的充分条件。

3.2 Innodb 行锁

InnoDB行锁是通过给索引（无论什么索引）上的索引项加锁来实现的（这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的）。 InnoDB这种行锁实现特点意味着：

只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

更具体的说，select 的 where 子句中没有索引列会使用表锁而不是行锁。

所以当查询语句使用索引的时候，行锁会把索引相关的数据行锁定，其他与此行相关的获取锁操作必须等待锁的释放。

3.3 间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如 emp 表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL： Select * from emp where empid > 100 for update; 是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

3.4 事物的隔离级别

隔离级别主要解决的问题依此为，丢失更改、脏读、不可重复读和幻读的问题。这些问题之所以会成为问题，因为隔离性满足时每个事物认为只有自己在执行，那么就不应该出现这些问题。但并发又是必须的，所以要针对问题的容忍度设置相应的隔离级别。

Read Uncommitted（读未提交）

通过一级封锁协议实现（写时加 X 锁），主要避免了丢失更改的问题，也即保证了并发程序的正确性，但不能保证事物的正确性。因为读到的是未提交的数据，所以该数据可能会回滚而变成脏数据，即有脏读的问题。

Read Committed（读已提交）

通过二级封锁协议实现（读时加 S 锁，读完立即释放），主要避免了脏读问题。当时读到数据就会释放锁，事物完成之前其他事物可能又会更改数据，所以有不可重复读的问题。

Repeatable（可重复都）

通过三级封锁协议实现（读锁在事物结束时释放），主要避免了不可重复读问题。但是由于默认加的是行锁，所以对聚集函数的操作会有幻读错误。

Serialize（可串行化）

通过加表锁来实现，使得事物完全隔离，串行的实现。

可以看的出来四种隔离级别，隔离性越来越高，并发性越来越低。

3.5 MySQL 乐观锁与悲观锁的实现

乐观锁

将版本号和要跟新的值写在一个语句中更新。

悲观锁

通过 select ... for update 手动加排它锁，若未获取锁会阻塞等待获取锁。select ... for update 会在事物结束自动释放锁。

4. 数据库的切分和主从复制

4.1 切分

水平切分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库 payDB、用户数据库 userBD 等。

Sharding 策略

• 哈希取模：hash(key) % NUM_DB
• 范围：可以是 ID 范围也可以是时间范围
• 映射表：使用单独的一个数据库来存储映射关系

Sharding 存在的问题及解决方案

1）事务问题

使用分布式事务来解决，比如 XA 接口。

2）JOIN

可以将原来的 JOIN 查询分解成多个单表查询，然后在用户程序中进行 JOIN。

3）ID 唯一性

• 使用全局唯一 ID：GUID。
• 为每个分片指定一个 ID 范围。
• 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)。

更多内容请参考：

• How Sharding Works
• 大众点评订单系统分库分表实践

4.2. 主从复制

事先会保证，主库和从库数据一致。

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

• binlog 线程 ：负责将主服务器上的数据更改写入二进制文件（binlog）中。
• I/O 线程 ：负责从主服务器上读取二进制日志文件，并写入从服务器的中继日志中。
• SQL 线程 ：负责读取中继日志并重放其中的 SQL 语句。

读写分离

主服务器用来处理写操作以及实时性要求比较高的读操作，而从服务器用来处理读操作。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

MySQL 读写分离能提高性能的原因在于：

• 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
• 从服务器可以配置 MyISAM 引擎，提升查询性能以及节约系统开销；
• 增加冗余，提高可用性。

5. 数据库连接方式及状态查询

5.1 Java 与 MySQL 的连接方式

JDBC 或 连接池（c3p0，druid），由于要保持连接传数据所以用的是 TCP。

druid 优点

性能更高（sql 解析器），完备的检测系统

5.2 连接状态查询

• 连接数查询（root 可查看所有，普通账号只能查看自己的）

  show processlist

• 各种状态查询

  show status like '%下面变量%'

6. MVCC

MVCC 使用时间戳（TS）、递增的事务 ID（T）实现事务一致性。MVCC 通过维护多版本数据，保证一个读事务永远不会被阻塞。

读时最最近的已写完的版本数据

写时，同时只有时间戳最小的可以写完成，其他事物要等待这个事物完成，再执行