Mysql常见面试题

数据库索引原理

MyISAM和InnoDB索引的底层实现原理

MyISAM 和 InnoDB 是MySQL的两代搜索引擎，区别在于，对于辅助索引的实现原理不一样，并且MyISAM是索引和文件分离的，而InnoDB不是。

一般以主键为索引的叫做主索引，而以其他键为索引的叫做辅助索引；

MyISAM的实现原理，利用B+树实现：

下面为主键索引：

mysql1

由上图可以看出，col1是主键，而叶子结点存储的数据是一个地址，通过地址找到数据；

下面是辅助索引（和主索引不同的是辅助索引的key是可以重复的） :

mysql2

注意：上图的叶子节点34下面的地址值应为0x07

InnoDB的实现原理，利用B+树实现:

下面是主键索引：

mysql3

这是主索引，即利用主键构造的B+树；

注意，和MyISAM不同的是叶子结点的数据域保存的是全部数据；

下面在看辅助索引：

mysql4

仔细看辅助索引和主索引的区别，辅助索引的叶子结点保存的是主键。

这就是MyISAM和InnoDB最大的不同。

既然MyISAM和InnoDB是MySQL的两代引擎，肯定会有一个提升，而InnoDB是最新一代，那么它到底优在哪里？

试想，MyISAM和InnoDB都是以B+树为基础实现的，相对于B树的不同其实前面已经讲过，即数据域和结点分离。而MyISAM更是索引和文件分离，B+树的叶子结点的数据域存放的是文件内容的地址，主索引和辅助索引的B+树都是如此。那么如果我改变了一个地址，是不是所有的索引树都得改变，正如前面我们讲的在磁盘上频繁的读写操作是效率很低的，而这块又不适用局部原理，因为逻辑上相邻的结点，物理上不一定相邻，那么这样就会造成效率上的降低；

于是乎，InnoDB就产生了，它让除了主索引以外的辅助索引的叶子结点的数据域都保存主键，先通过辅助索引找到主键，然后通过主键找到叶子结点的所有数据，听起来貌似很麻烦，遍历了两棵树，但是，这样如果有了修改的话，改变的只是主索引，其它辅助索引都不用动。而且，数据库中的树的每一个结点的key可不是咱们给的那么少。试想如果一个结点有1024个key，那么高度为2的B+树都有1024*1024个key，所以一般树的高度都很低，所以，遍历树的消耗几乎忽略不计！

另外，我们也就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

为什么使用B+Tree作为数据库索引？

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

这样我们对比上面的B+树和红黑树，比如查找节点21，红黑树要磁盘IO5次，而B+树只要2次，也就是说磁盘IO次数大致为树的高度，这样B+树就脱颖而出了，成为实现索引的不二选择。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。

上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。（InnoDB的索引实现原理）

聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

数据库索引采用B+树而不是B树或红黑树的主要原因：

方便扫库：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适。数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，而红黑树这种结构，高度明显要深的多，并且由于逻辑上很近的节点(父子)物理上可能很远，无法利用局部性。

为什么数据库选B-tree或B+tree而不是二叉查找树作为索引结构？

磁盘IO与预读

磁盘读取依靠的是机械运动，分为寻道时间、旋转延迟、传输时间三个部分，这三个部分耗时相加就是一次磁盘IO的时间，大概9ms左右。这个成本是访问内存的十万倍左右；正是由于磁盘IO是非常昂贵的操作，所以计算机操作系统对此做了优化：预读

预读：每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中。因为局部预读原理说明，当访问一个地址数据的时候，与其相邻的数据很快也会被访问到。每次磁盘IO读取的数据我们称之为一页（page）。一页的大小与操作系统有关，一般为4k或者8k。这也就意味着读取一页内数据的时候，实际上发生了一次磁盘IO。
B-Tree与二叉查找树的对比

我们知道二叉查找树查询的时间复杂度是O(logN)，查找速度最快和比较次数最少，既然性能已经如此优秀，但为什么实现索引是使用B-Tree而不是二叉查找树，关键因素是磁盘IO的次数。

数据库索引是存储在磁盘上，当表中的数据量比较大时，索引的大小也跟着增长，达到几个G甚至更多。当我们利用索引进行查询的时候，不可能把索引全部加载到内存中，只能逐一加载每个磁盘页，这里的磁盘页就对应索引树的节点。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。而磁盘IO的次数和树的高度有关，因此，减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以B-Tree就在这样伟大的时代背景下诞生了。

索引是存在于索引文件中，是存在于磁盘中的。因为索引通常是很大的，因此无法一次将全部索引加载到内存当中，因此每次只能从磁盘中读取一个磁盘页的数据到内存中。而这个磁盘的读取的速度较内存中的读取速度而言是差了好几个级别。

注意，我们说的平衡二叉树结构，指的是逻辑结构上的平衡二叉树，其物理实现是数组。然后由于在逻辑结构上相近的节点在物理结构上可能会差很远。因此，每次读取的磁盘页的数据中有许多是用不上的。因此，查找过程中要进行许多次的磁盘读取操作。

而适合作为索引的结构应该是尽可能少的执行磁盘IO操作，因为执行磁盘IO操作非常的耗时。因此，平衡二叉树并不适合作为索引结构。

为什么说B+树比B树更适合数据库索引？

mysql1

B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小。如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
PS：我在知乎上看到有人是这样说的,我感觉说的也挺有道理的：

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

参考：https://blog.csdn.net/qq_35571554/article/details/82759668

数据库索引类型

索引的优点：

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

首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。

即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

MySQL里的索引类型主要有以下几种：

1. B-Tree索引

最常见的索引类型，基于B-Tree数据结构。B-Tree的基本思想是，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）。但是当索引多列时，列的顺序特别重要，需要格外注意。InnoDB和MyISAM都支持B-Tree索引。InnoDB用的是一个变种B+Tree，而MyISAM为了节省空间对索引进行了压缩，从而牺牲了性能。

2. Hash索引

基于hash表。所以这种索引只支持精确查找，不支持范围查找，不支持排序。这意味着范围查找或ORDER BY都要依赖server层的额外工作。目前只有Memory引擎支持显式的hash索引（但是它的hash是nonunique的，冲突太多时也会影响查找性能）。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引。

例子：

CREATE TABLE testhash (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    KEY USING HASH(fname)
) ENGINE =MEMORY;

3. Spatial (R-Tree)（空间）索引

只有MyISAM引擎支持，并且支持的不好。可以忽略。

4. Full-text索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。Full-text索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的WHERE语句的参数匹配。你可以对某列分别进行full-text索引和B-Tree索引，两者互不冲突。Full-text索引配合MATCH AGAINST操作使用，而不是一般的WHERE语句加LIKE。

从数据结构的角度

B+树索引(O(logn))
哈希索引
- 仅仅能满足”=”,”IN”和”<>”查询，不能使用范围查询。
- 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
- 只有Memory存储引擎显示支持hash索引。
FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）
R-Tree索引（用于对GIS数据类型创建SPATIAL索引）

从物理存储角度

聚集索引（clustered index）
非聚集索引（non-clustered index）

MyISAM的是非聚簇索引，InnoDB使用的是聚簇索引。

从逻辑角度

主键索引：主键索引是一种特殊的唯一索引，不允许有空值。
普通索引或者单列索引。
多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。注意：使用复合索引时遵循最左前缀。
唯一索引或者非唯一索引。
空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。
1
2
CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

注意：

1、unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；

2、index和key为同义词，两者作用相同，用来指定创建索引；

3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；

4、index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；

5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

6、asc或desc指定升序或降序的索引值存储。

索引的使用场景

匹配全值（match the full value）

对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。
例如，租赁表 rental 中通过指定出租日期 rental_date + 库存编号 inventory_id + 客户编号 customer_id 的组合条件进行查询，执行计划的 key和extra两字段的值看到优化器选择了复合索引 idx_rental_date:

MySQL [sakila]> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: const
possible_keys: rental_date,idx_fk_inventory_id,idx_fk_customer_id
          key: rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
 1 row in set, 1 warning (0.00 sec)

explain 输出结果中字段 type 的值为 const，表示是常量；字段 key 的值为 rental_date, 表示优化器选择索引 rental_date 进行扫描。

匹配值的范围查询（match a range of values）

对索引的值能够进行范围查找。
例如，检索租赁表 rental 中客户编号 customer_id 在指定范围内的记录：

MySQL [sakila]> explain select * from rental where customer_id >= 373 and customer_id < 400 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 718
     filtered: 100.00
        Extra: Using index condition
 1 row in set, 1 warning (0.05 sec)

类型 type 为 range 说明优化器选择范围查询，索引 key 为 idx_fk_customer_id 说明优化器选择索引 idx_fk_customer_id 来加速访问，注意到这个列子中 extra 列为 using index codition ，表示 mysql 使用了 ICP（using index condition）来进一步优化查询。

匹配最左前缀（match a leftmost prefix）

最左匹配原则可以算是 MySQL 中 B-Tree 索引使用的首要原则。
仅仅对索引进行查询（index only query）,也叫索引覆盖

当查询的列都在索引的字段中时，查询的效率更高。

所以应该尽量避免使用 select *，需要哪些字段，就只查哪些字段。

匹配列前缀（match a column prefix）

仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。
例如，现在需要查询出标题 title 是以 AFRICAN 开头的电影信息，从执行计划能够清楚看到，idx_title_desc_part 索引被利用上了：

MySQL [sakila]> create index idx_title_desc_part on film_text(title (10), description(20));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [sakila]> explain select title from film_text where title like 'AFRICAN%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: range
possible_keys: idx_title_desc_part,idx_title_description
          key: idx_title_desc_part
      key_len: 32
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
 1 row in set, 1 warning (0.00 sec)

extra 值为 using where 表示优化器需要通过索引回表查询数据。

能够实现索引匹配部分精确而其他部分进行范围匹配（match one part exactly and match a range on another part）

例如，需要查询出租日期 rental_date 为指定日期且客户编号 customer_id 为指定范围的库存：

MySQL [sakila]> MySQL [sakila]> explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and customer_id <=400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: rental_date,idx_fk_customer_id
          key: rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.85
        Extra: Using where; Using index
 1 row in set, 1 warning (0.00 sec)

如果列名是索引，那么使用 column_name is null 就会使用索引

例如，查询支付表 payment 的租赁编号 rental_id 字段为空的记录就用到了索引：

MySQL [sakila]> explain select * from payment where rental_id is null \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Using index condition
 1 row in set, 1 warning (0.00 sec)

索引失效的情况

用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左前缀原则，是不会使用复合索引的。
like查询是以%开头。
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
如果mysql估计使用全表扫描要比使用索引快,则不使用索引

最左前缀匹配原则

定义：就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边

生效原则：组合索引的生效原则：从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用。
比如：

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；
where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果
where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

(0)    select * from mytable where a=3 and b=5 and c=4;
abc三个索引都在where条件里面用到了，而且都发挥了作用
(1)    select * from mytable where  c=4 and b=6 and a=3;
这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样
(2)    select * from mytable where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的
(3)    select * from mytable where a=3 and b>7 and c=3;
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
(4)    select * from mytable where b=3 and c=4;
因为a索引没有使用，所以这里 bc都没有用上索引效果
(5)    select * from mytable where a>4 and b=7 and c=9;
a用到了  b没有使用，c没有使用
(6)    select * from mytable where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的
(7)    select * from mytable where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
(8)    select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果

MyISAM和InnoDB的区别

mysql3

MyISAM简介

MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的 ISAM （Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（另一种数据库引擎）。

下面这张图只是想表达的意思是现在大多数时候我们使用的都是InnoDB存储引擎，但是在某些情况下使用MyISAM更好，比如：MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。 在数据库做主从分离的情况下，经常选择MyISAM作为主库的存储引擎。

mysql5

MyISAM特点

不支持行锁(MyISAM只有表锁)，读取时对需要读到的所有表加锁，写入时则对表加排他锁；
不支持事务
不支持外键
不支持崩溃后的安全恢复
在表有读取查询的同时，支持往表中插入新纪录
支持BLOB和TEXT的前500个字符索引，支持全文索引
支持延迟更新索引，极大地提升了写入性能
对于不会进行修改的表，支持压缩表 ，极大地减少了磁盘空间的占用

InnoDB的简介

InnoDB是MySQL的默认数据库引擎（5.5版之后），2006年五月时由甲骨文公司并购。与传统的ISAM与

MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能。

InnoDB的特点

支持行锁，采用MVCC来支持高并发，有可能死锁
支持事务
支持外键
支持崩溃后的安全恢复
不支持全文索引
读写阻塞与事务隔离级别相关
具有非常高效的缓存特性，能缓存索引，也能缓存数据
支持分区、表空间，类似Oracle数据库

关于二者的对比与总结

3.1 二者的常见对比

1) count运算上的区别： 因为MyISAM缓存有表meta-data（行数等），因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。

2) 是否支持事务和崩溃后的安全恢复： MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务支持事务，外部键等高级数据库功能，具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

3)是否支持外键： MyISAM不支持，而InnoDB支持。

3.2 总结

MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。 在数据库做主从分离的情况下，经常选择MyISAM作为主库的存储引擎。

一般来说，如果需要事务支持，并且有较高的并发读取频率(MyISAM的表锁的粒度太大，所以当该表写并发量较高时，要等待的查询就会很多了)，InnoDB是不错的选择。如果你的数据量很大（MyISAM支持压缩特性可以减少磁盘的空间占用），而且不需要支持事务时，MyISAM是最好的选择。

参考：https://juejin.im/post/5b1685bef265da6e5c3c1c34

字符集及校对规则

字符集指的是一种从二进制编码到某类字符符号的映射。

校对规则则是指某种字符集下的排序规则。

Mysql中每一种字符集都会对应一系列的校对规则。

Mysql采用的是类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采用默认字符集）

PS：整理自《Java工程师修炼之道》

查询缓存的使用

my.cnf加入以下配置，重启Mysql开启查询缓存

1 2	query_cache_type=1 query_cache_size=600000

Mysql执行以下命令也可以开启查询缓存

1 2	set global query_cache_type=1; set global query_cache_size=600000;

如上，开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表，其查询结果也不会被缓存。

缓存建立之后，Mysql的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启缓存查询要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十MB比较合适。此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存：

1	select sql_no_cache count(*) from usr;

数据库并发控制

Mysql的大多数事务型存储引擎实现都不是简单的行级锁，基于并发性能考虑，一般都实现了MVCC多版本并发控制。

Mysql的并发控制是为了实现事务的隔离性，实现隔离性就要解决脏读、不可重复读、幻读问题。

Mysql的并发控制主要有两种方式，一种是多版本的并发控制(MVCC)，一种是基于锁的并发控制。

最为常见的三种并发控制机制：

悲观并发控制，悲观并发控制其实是最常见的并发控制机制，也就是锁；
乐观并发控制，乐观并发控制其实也有另一个名字：乐观锁，乐观锁其实并不是一种真实存在的锁；
多版本并发控制，与前两者对立的命名不同，MVCC 可以与前两者中的任意一种机制结合使用，以提高数据库的读性能。

并发控制要解决的问题：未提交的修改数据

脏读：一个事务读到了另一个事务尚未提交的数据。
不可重复读：同一个事务中两次读取的数据发生改变，这种改变是由另一个事务修改了对应记录引起的。
幻读：同一事务多次查询进行的时候，由于其他插入或删除操作的事务提交，导致每次返回不同的结果集(查到的数据增多或者减少)

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

mysql2

MVCC多版本并发控制

MVCC是通过保存数据在某个时间点的快照来实现的。不管事务执行多长时间，事务看到的数据都是一致的。

MVCC最大的优点就是：读不加锁，读写不冲突。

MVCC的实现原理

为了实现MVCC，InnoDB对每一行都加上了两个隐藏的列，其中一列存储行被修改的时系统的修改版本号，另外一列存储行被删除时的系统的删除版本号。每当一个事务开始的时候，InnoDB都会给这个事务分配一个递增的版本号，事务开始时的系统版本号会作为事务的版本号。

mysql4

实际上，这个描述是很不严格的，问题有以下几点：

每条记录含有的隐藏列不是两个而是三个

它们分别是：

DB_TRX_ID, 6byte, 创建这条记录/最后一次更新这条记录的事务ID

DB_ROLL_PTR, 7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

DB_ROW_ID, 6byte，隐含的自增ID，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

另外，每条记录的头信息（record header）里都有一个专门的bit（deleted_flag）来表示当前记录是否已经被删除
记录的历史版本是放在专门的rollback segment里（undo log）

UPDATE非主键语句的效果是:

老记录被复制到rollback segment中形成undo log，DB_TRX_ID和DB_ROLL_PTR不动

新记录的DB_TRX_ID = 当前事务ID，DB_ROLL_PTR指向老记录形成的undo log

这样就能通过DB_ROLL_PTR找到这条记录的历史版本。如果对同一行记录执行连续的update操作，新记录与undo log会组成一个链表，遍历这个链表可以看到这条记录的变迁）
Mysql的一致性读，是通过一个叫做read view的结构来实现的，下面介绍相关概念和可见性比较算法：

read_view中维护了系统中活跃事务集合的快照，这些活跃事务ID的最小值为up_limit_id，最大值为low_limit_id。

设要读取的行的最后提交事务id(即当前数据行的稳定事务id)为 trx_id_current，
当前新开事务id为 new_id，当前新开事务创建的快照read view 中最早的事务id为up_limit_id, 最迟的事务id为low_limit_id(注意这个low_limit_id=未开启的事务id=当前最大事务id+1)
- 1.trx_id_current < up_limit_id, 这种情况比较好理解, 表示新事务在读取该行记录时, 该行记录的稳定事务ID是小于系统当前所有活跃的事务, 所以当前行稳定数据对新事务可见, 跳到步骤5.
- 2.trx_id_current >= trx_id_last, 这种情况也比较好理解, 表示该行记录的稳定事务ID是在本次新事务创建之后才开启的, 但是却在本次新事务执行第二个select前就commit了，所以该行记录的当前值不可见, 跳到步骤4。
- 3.trx_id_current <= trx_id_current <= trx_id_last, 表示该行记录所在事务在本次新事务创建的时候处于活动状态，从up_limit_id到low_limit_id进行遍历，如果trx_id_current等于他们之中的某个事务id的话，那么不可见，调到步骤4，否则表示可见，调到步骤5。
- 4.从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的undo-log的版本号, 将它赋值该 trx_id_current，然后跳到步骤1重新开始判断。
- 5.将该可见行的值返回。
辅助索引实现MVCC

刚才讲的内容是基于聚簇索引的，只有聚簇索引中含有DB_TRX_ID与DB_ROLL_PTR隐藏列，可以比较容易的实现MVCC，但是辅助中并不含有这几个隐藏列，只含有1个bit的deleted flag。
purge线程

从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。

为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。

为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）

如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

补充概念

多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性(up_limit_id和low_limit_id)。

read view：主要是用来做可见性判断的, 比较普遍的解释便是”本事务不可见的当前其他活跃事务”。

对于read view快照的生成时机, 也非常关键, 正是因为生成时机的不同, 造成了RC,RR两种隔离级别的不同可见性;

在innodb中(默认repeatable read级别), 事务在begin/start transaction之后的第一条select读操作后, 会创建一个快照(read view), 将当前系统中活跃的其他事务记录记录起来;
在innodb中(默认repeatable committed级别), 事务中每条select语句都会创建一个快照(read view);

undo_log

Undo log是InnoDB MVCC事务特性的重要组成部分。当我们对记录做了变更操作时就会产生undo记录，Undo记录默认被记录到系统表空间(ibdata)中，但从5.6开始，也可以使用独立的Undo 表空间。
Undo记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录。当版本链很长时，通常可以认为这是个比较耗时的操作（例如bug#69812）。
大多数对数据的变更操作包括INSERT/DELETE/UPDATE，其中INSERT操作在事务提交前只对当前事务可见，因此产生的Undo日志可以在事务提交后直接删除（谁会对刚插入的数据有可见性需求呢！！），而对于UPDATE/DELETE则需要维护多版本信息，在InnoDB里，UPDATE和DELETE操作产生的Undo日志被归成一类，即update_undo
另外, 在回滚段中的undo logs分为: insert undo log 和 update undo log
- insert undo log : 事务对insert新记录时产生的undolog, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
- update undo log : 事务对记录进行delete和update操作时产生的undo log, 不仅在事务回滚时需要, 一致性读也需要，所以不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。

读操作分成两类：快照读和当前读。

快照读：简单的select操作属于快照读，不加锁。
1
select * from table where ?

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode ;
select * from table where ? for update ;
update table set ? where ? ;
delete from table where ? ;

乐观并发控制

一般采用以下方式：使用版本号(version)机制来实现，版本号就是为数据添加一个版本标志，一般在表中添加一个version字段；当读取数据的时候把version也取出来，然后version+1，更新数据库的时候对比第一次取出来的version和数据库里面的version是否一致，如果一致则更新成功，否则失败进入重试，具体使用大致如下：

begin;
select id,name,version from test_lock where id=1;
....
update test_lock set name='xxx',version=version+1 where id=1 and version=${version};
commit;

先查询后更新，需要保证原子性，要么使用悲观锁的方式，对整个事务加锁；要么使用乐观锁的方式，如果在读多写少的系统中，乐观锁性能更好；

事务机制

关系型数据库需要遵循ACID原则，具体内容如下：

mysql6

原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性： 执行事务前后，数据库从一个一致性状态转换到另一个一致性状态。
隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

为了达到上述事务特性，数据库定义了几种不同的事务隔离级别：

READ_UNCOMMITTED（读未提交）: 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
READ_COMMITTED（读已提交）: 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
REPEATABLE_READ（可重复读）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE（串行化）: 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。

Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别。

锁机制与InnoDB锁算法

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

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

按照锁的粒度把数据库锁分为表级锁和行级锁

表级锁： Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
行级锁： Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。InnoDB支持的行级锁，包括如下几种：
- Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
- Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
- Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合，可解决幻读问题。

虽然使用行级索具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的：

事务更新大表中的大部分数据直接使用表级锁效率更高；
事务比较复杂，使用行级索很可能引起死锁导致回滚。

补充：

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

按照是否可写进一步划分为共享锁(S锁)和排他锁(X锁)

表级锁和行级锁可以进一步划分为共享锁（S）和排他锁（X）

共享锁（S）

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

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

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

两者之间的区别：

共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，而不能加排他锁。获取共享锁的事务只能读数据，不能修改数据。

排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获取排他锁的事务既能读数据，又能修改数据。
共享锁下其它用户可以并发读取，查询数据，但不能修改，增加，删除数据，即资源共享。

另外两种表级锁：IS和IX

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

InnoDB另外的两个表级锁：

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

注意：

这里的意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预。
IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。

InnoDB的锁机制兼容情况如下：

mysql5

当一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之如果请求不兼容，则该事物就等待锁释放。

死锁和避免死锁

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

另外，还需要注意，在通过以下情况使用锁的时候，如果表没有定义任何索引，那么InnoDB会创建一个隐藏的聚簇索引并使用这个索引来加记录锁。

1 2	SELECT ...LOCK IN SHARE MODE; SELECT ...FOR UPDATE;

死锁产生：不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务。

避免死锁：

通过表级锁来减少死锁产生的概率；
多个程序尽量约定以相同的顺序访问表（这也是解决并发理论中哲学家就餐问题的一种思路）；
同一个事务尽可能做到一次锁定所需要的所有资源。

大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；
读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
垂直分区：

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起join操作，可以通过在应用层进行join来解决。此外，垂直分区会让事务变得更加复杂；
水平分区：

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

下面补充一下数据库分片的两种常见方案：
- 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
- 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。