列存储（MySQL知识点总结）

一. 存储引擎

1.1 InnoDB

支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。

1.2 myisam

它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不支持事务，而且不支持外键，如果执行大量的select MyISAM比较适合。

1.3 InnoDB 和 MyISAM 的比较

1、事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。2、并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。3、外键：InnoDB 支持外键。4、备份：InnoDB 支持在线热备份。5、崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。6、其它特性：MyISAM 支持压缩表和空间数据索引。

1.4 其他存储引擎

1、ARCHIVE：用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。2、CSV ：在存储数据时，会以逗号作为数据项之间的分隔符。3、BLACKHOLE：会丢弃写操作，该操作会返回空内容。4、FEDERATED：将数据存储在远程数据库中，用来访问远程表的存储引擎。5、MEMORY：置于内存的表6、MERGE：用来管理由多个 MyISAM 表构成的表集合

二. 索引

2.1 B+ Tree 原理

1、B Tree 指的是 Balance Tree，也就是平衡树，平衡树是一颗查找树，并且所有叶子节点位于同一层。2、B+ Tree 是 B 树的一种变形，它是基于 B Tree 和叶子节点顺序访问指针进行实现，通常用于数据库和操作系统的文件系统中。3、B+ 树有两种类型的节点：内部节点（也称索引节点）和叶子节点，内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存在叶子节点。4、内部节点中的 key 都按照从小到大的顺序排列，叶子节点的记录也是按照从小到大排列的。5、每个叶子节点都存有相邻叶子节点的指针。

2.2 B-Tree

1、所有键值分布在整个树中（区别与B+树，B+树的值只分部在叶子节点上）2、任何关键字出现且只出现在一个节点中（区别与B+树）3、搜索有可能在非叶子节点结束（区别与B+树，因为值都在叶子节点上，只有搜到叶子节点才能拿到值）4、在关键字全集内做一次查找，性能逼近二分查找算法

2.3 B + 树与 B 树的对比

1、B+ 树的磁盘 IO 更低B+ 树的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。2、B+ 树的查询效率更加稳定由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。3、B 树元素遍历的效率低下。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低）。

2.4 哈希索引

2.4.1 特点

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。

2.4.2 弊端

1、Hash索引只能精确匹配，不能使用范围查询。2、Hash索引无法被用来数据的排序操作。3、Hash索引不能利用部分索引键查询。4、Hash索引在任何时候都不能避免表扫描。5、Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。

2.5 聚簇索引和非聚簇索引

2.5.1 介绍

聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式。将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据。

2.5.2 好处与限制

聚簇索引的好处：按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的io操作。聚簇索引的限制：对于MySQL数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。由于数据物理存储排序方式只能有一种，所以每个mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

2.6 索引优化

2.6.1 索引的优缺点及使用条件

优点：1、提高数据检索的效率，降低数据库的IO成本。2、通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

缺点：1、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间。

使用条件：哪些情况需要创建索引？1、主键自动建立唯一索引2、频繁作为查询条件的字段应该创建索引3、查询中与其它表关联的字段，外键关系建立索引4、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度5、查询中统计或者分组字段

哪些情况不需要创建索引？1、表记录太少。2、经常增删改的表或者字段。Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件。3、Where条件里用不到的字段不创建索引。

问题：为什么对于非常小的表，大部分情况下简单的全表扫描比建立索引更高效？如果一个表比较小，那么显然直接遍历表比走索引要快（因为需要回表）。

2.7 前缀索引

当索引是很长的字符序列时，这个索引将会很占内存，而且会很慢，这时候就会用到前缀索引了。所谓的前缀索引就是去索引的前面几个字母作为索引，但是要降低索引的重复率，索引我们还必须要判断前缀索引的重复率

2.8 覆盖索引

索引包含所有需要查询的字段的值。具有以下优点：1、索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。2、一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。3、对于 InnoDB 引擎，若覆盖索引能够覆盖查询，则无需访问主索引。

2.9 查询性能分析 - EXPLAIN

用法：explain+SQL语句

EXPLAIN字段解析：

信息

描述

id

查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序

**两种情况**

id相同，执行顺序从上往下

id不同，id值越大，优先级越高，越先执行

select_type

查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

1、simple ——简单的select查询，查询中不包含子查询或者UNION

2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记

3、subquery——在select或where列表中包含了子查询

4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中

5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived

6、union result:UNION 的结果

type

显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序

1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。

2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量

3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描

4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体

5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描

6、index: 出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组

7、all：遍历全表以找到匹配的行

注意:一般保证查询至少达到range级别，最好能达到ref。

possible_keys

指出MySQL能使用哪个索引在该表中找到行

key

显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。

key_len

表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。

ref

显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值

rows

根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

包含不适合在其他列中显示，但是十分重要的额外信息

1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”

2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。

3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。

4、Using where :表明使用where过滤

5、using join buffer:使用了连接缓存

6、impossible where:where子句的值总是false，不能用来获取任何元组

7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

见到Using temporary和Using filesort，就意味着MySQL根本不能使用索引，结果是检索会很慢，需要优化sql了。

三. 优化的一些方向

优化数据访问减少请求的数据量：1、只返回必要的列：最好不要使用 SELECT * 语句。2、只返回必要的行：使用 LIMIT 语句来限制返回的数据。3、缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。减少服务器端扫描的行数：1、最有效的方式是使用索引来覆盖查询。

重构查询方式1、切分大查询：一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。2、分解大连接查询：将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：① 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。② 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。③ 减少锁竞争；④ 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。

索引失效的一些情况

1、查询条件中有or2、like查询是以'%'开头3、对查询的列上有运算或者函数的4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引5、左连接查询或者右连接查询查询关联的字段编码格式不一样6、如果mysql估计使用全表扫描要比使用索引快,则不使用索引7、如果查询中没有用到联合索引的第一个字段，则不会走索引

四. 事务

4.1 描述

事务是指满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

4.2 特性

ACID 四个特性了，四个特性分别是：Atomicity：原子性；Consistency：一致性；Isolation：隔离性；Durability：持久性。原子性：事务被视为不可分割的最小单元，事务的所有操作要么全部成功，要么全部失败回滚。一致性：数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的。持久性：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢。

4.3 ACID 之间的关系

事务的 ACID 特性概念很简单，但不好理解，主要是因为这几个特性不是一种平级关系：·只有满足一致性，事务的结果才是正确的。·在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。·在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。·事务满足持久化是为了能应对数据库崩溃的情况。

4.4 隔离级别

1、读未提交读（READ UNCOMMITTED）事务中的修改，即使没有提交，对其他事务也是可见的。2、读已提交（READ COMMITTED）一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其他事务是不可见的。3、可重复读（REPEATABLE READ）保证在同一个事务中多次读取同样数据的结果是一样的。4、可串行化（SERIALIZABLE）强制事务串行执行。需要加锁实现，而其它隔离级别通常不需要。默认的隔离级别READ-COMMITTED，可通过SELECT @@tx_isolation; 查询

五. mysql锁机制

5.1 介绍

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。

5.2 分类

从对数据操作的类型（读写）

读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分:表锁、行锁

表锁(偏读) 偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

行锁(偏写) 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

InnoDB行锁实现方式：InnoDB行锁是通过给索引上的索引项加锁 来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。而且即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。

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

5.3 MVCC（多版本并发控制）

介绍

MVCC全称是： Multiversion concurrency control，多版本并发控制，提供并发访问数据库时，对事务内读取的到的内存做处理，用来避免写操作堵塞读操作的并发问题。

举例

程序员A正在读数据库中某些内容，而程序员B正在给这些内容做修改（假设是在一个事务内修改，大概持续10s左右），A在这10s内 则可能看到一个不一致的数据，在B没有提交前，如何让A能够一直读到的数据都是一致的呢？

有几种处理方法，第一种： 基于锁的并发控制，程序员B开始修改数据时，给这些数据加上锁，程序员A这时再读，就发现读取不了，处于等待情况，只能等B操作完才能读数据，这保证A不会读到一个不一致的数据，但是这个会影响程序的运行效率。还有一种就是：MVCC，每个用户连接数据库时，看到的都是某一特定时刻的数据库快照，在B的事务没有提交之前，A始终读到的是某一特定时刻的数据库快照，不会读到B事务中的数据修改情况，直到B事务提交，才会读取B的修改内容。

思路

一个支持MVCC的数据库，在更新某些数据时，并非使用新数据覆盖旧数据，而是标记旧数据是过时的，同时在其他地方新增一个数据版本。因此，同一份数据有多个版本存储，但只有一个是最新的。

MVCC提供了 时间一致性的 处理思路，在MVCC下读事务时，通常使用一个时间戳或者事务ID来确定访问哪个状态的数据库及哪些版本的数据。读事务跟写事务彼此是隔离开来的，彼此之间不会影响。假设同一份数据，既有读事务访问，又有写事务操作，实际上，写事务会新建一个新的数据版本，而读事务访问的是旧的数据版本，直到写事务提交，读事务才会访问到这个新的数据版本。

MVCC有两种实现方式

第一种实现方式是将数据记录的多个版本保存在数据库中，当这些不同版本数据不再需要时，垃圾收集器回收这些记录。

第二种实现方式只在数据库保存最新版本的数据，但是会在使用undo时动态重构旧版本数据，这种方式被Oracle和MySQL/InnoDB使用。Undo 日志MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

实现机制

简述 MVCC可以认为是行级锁的一个变种，它可以在很多情况下避免加锁操作，因此开销更低。MVCC的实现大都都实现了非阻塞的读操作，写操作也只锁定必要的行。 InnoDB的MVCC实现，是通过保存数据在某个时间点的快照来实现的。一个事务，不管其执行多长时间，其内部看到的数据是一致的。也就是事务在执行的过程中不会相互影响。

机制 InnoDB的MVCC，通过在每行记录后面保存两个隐藏的列来实现：一个保存了行的创建时间，一个保存行的过期时间（删除时间），当然，这里的时间并不是时间戳，而是系统版本号，每开始一个新的事务，系统版本号就会递增。在RR隔离级别下，MVCC的操作如下：

1、select操作。 ·InnoDB只查找版本早于（包含等于）当前事务版本的数据行。可以确保事务读取的行，要么是事务开始前就已存在，或者事务自身插入或修改的记录。 ·行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取的行，在事务开始之前未删除。2、insert操作。将新插入的行保存当前版本号为行版本号。3、delete操作。将删除的行保存当前版本号为删除标识。4、update操作。变为insert和delete操作的组合，insert的行保存当前版本号为行版本号，delete则保存当前版本号到原来的行作为删除标识。 由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

六. 复制

主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。I/O 线程 ：负责从主服务器上读取- 二进制日志，并写入从服务器的中继日志（Relay log）。SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。读写分离能提高性能的原因在于：1、主从服务器负责各自的读和写，极大程度缓解了锁的争用；2、从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；3、增加冗余，提高可用性。 读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

七. 面试题

7.1 什么是覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

7.2 主键索引和普通索引有什么不一样？

主键索引也被称为聚簇索引,叶子节点存放的是整行数据; 而非主键索引被称为二级索引,叶子节点存放的是主键的值.如果根据主键查询, 只需要搜索ID这颗B+树而如果通过非主键索引查询, 需要先搜索k索引树, 找到对应的主键, 然后再到ID索引树搜索一次, 这个过程叫做回表。

总结：非主键索引的查询需要多扫描一颗索引树, 效率相对更低。

7.3 什么情况下mysql会回表查询？

MySQL innodb的主键索引是簇集索引，也就是索引的叶子节点存的是整个单条记录的所有字段值，不是主键索引的就是非簇集索引，非簇集索引的叶子节点存的是主键字段的值。

回表是什么意思？就是你执行一条sql语句，需要从两个b+索引中去取数据。举个例子：表tbl有a,b,c三个字段，其中a是主键，b上建了索引，然后编写sql语句SELECT b FROM tbl WHERE a=1 这样不会产生回表，因为所有的数据在a的索引树中均能找到。SELECT * FROM tbl WHERE b=1 这样就会产生回表，因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引树，这就叫回表。索引覆盖就是查这个索引能查到你所需要的所有数据，不需要去另外的数据结构去查。其实就是不用回表。怎么避免？不是必须的字段就不要出现在SELECT里面。或者b,c建联合索引。但具体情况要具体分析，索引字段多了，存储和插入数据时的消耗会更大。这是个平衡问题。

7.4 大表怎么DDL变更

Online DDL

MySQL 5.6到5.7对于MySQL 5.6到5.7的版本，可以使用OnLine DDL的方式变更，对于大表来说，执行时间会很长，好处是在Master上DML操作不受影响，但是会导致主从延时。

假如Master上添加字段执行了20分钟，相应的Slave也要执行20分钟，在这期间Slave一直处于延迟状态，会造成业务数据不一致，比如用户在Master下单成功，由于Slave延迟查询不到订单信息，用户误以为网络原因没有下单成功，又下了一单，导致重复下单的情况。这种方式会导致主从延迟，根据业务情况，只能选择在业务低峰期执行了。MySQL 8.0变更方式

用过Oracle的都知道，DDL变更都是修改元数据，上亿的表在Oracle中DDL变更都是瞬间完成。MySQL 8.0也推出了INSTANT方式，真正的只修改MetaData，不影响表数据，所以它的执行效率跟表大小几乎没有关系。建议新系统上线用MySQL的话尽量使用MySQL 8.0，老的数据库也可以升级到MySQL 8.0获取更好的性能。

7.5 模糊匹配like %%怎么优化

1、尝试利用覆盖索引特性，先查出ID，再反查。2、使用复合索引，利用前面的字段先过滤一些数据。3、创建全文索引。MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引

7.6 数据库连接池到底应该设多大？

计算公式下面的公式是由PostgreSQL提供的，不过我们认为可以广泛地应用于大多数数据库产品。你应该模拟预期的访问量，并从这一公式开始测试你的应用，寻找最合适的连接数值。

连接数 = ((核心数 2) + 有效磁盘数)核心数不应包含超线程(hyper thread)，即使打开了hyperthreading也是。如果活跃数据全部被缓存了，那么有效磁盘数是0，随着缓存命中率的下降，有效磁盘数逐渐趋近于实际的磁盘数。这一公式作用于SSD时的效果如何尚未有分析。按这个公式，你的4核i7数据库服务器的连接池大小应该为((4 2) + 1) = 9。取个整就算是是10吧。这个配置就能轻松搞定3000用户以6000TPS的速率并发执行简单查询的场景。如果连接池大小超过10，会看到响应时长开始增加，TPS开始下降。

why ? 一颗CPU核心同一时刻只能执行一个线程，然后操作系统切换上下文，核心开始执行另一个线程的代码，以此类推。给定一颗CPU核心，其顺序执行A和B永远比通过时间分片“同时”执行A和B要快，这是一条计算机科学的基本法则。一旦线程的数量超过了CPU核心的数量，再增加线程数系统就只会更慢，而不是更快。

7.7 撞库、脱库和洗库

撞库”撞库”是黑客通过收集互联网已泄露的用户和密码信息，生成对应的字典表，尝试批量登陆其他网站后，得到一系列可以登录的用户。很多用户在不同网站使用的是相同的帐号密码，因此黑客可以通过获取用户在A网站的账户从而尝试登录B网址，这就可以理解为撞库攻击。

说得简单一点，就是一个小偷，入室盗窃后偷到了一串钥匙，然后他拿着这串钥匙，在整个小区里面挨家挨户地进行开锁。这个过程就是撞库。拖库拖库本来是数据库领域的术语，指从数据库中导出数据。到了黑客攻击泛滥的今天，它被用来指网站遭到入侵后，黑客窃取其数据库。黑客通过技术手段窃取数据库的过程叫做拖库。就像小偷偷东西是一样的。“拖库”的通常步骤为：

1、黑客对目标网站进行扫描，查找其存在的漏洞，常见漏洞包括SQL注入、文件上传漏洞等。（小偷蹲点）

2、通过该漏洞在网站服务器上建立“后门(webshell)”，通过该后门获取服务器操作系统的权限。（小偷想办法进入室内）

3、利用系统权限直接下载备份数据库，或查找数据库链接，将其导出到本地。（小偷盗走值钱的东西）洗库“洗库”，属于黑客入侵的一种，就是黑客入侵网站，通过技术手段将有价值的用户数据归纳分析，售卖变现。说的简单一点，就是一个小偷，入室盗窃后偷到了很多东西，他对这些赃物分类，然后进行销赃的过程。

7.8 为什么MySQL不建议使用delete删除数据

经过测试，发现执行完delete语句后，再执行sql语句效率变慢，可能是因为delete语句会产生碎片数据存储在文件系统上的，总是不能100%利用分配给它的物理空间，删除数据会在页面上留下一些”空洞”。或者随机写入（聚集索引非线性增加）会导致页分裂，页分裂导致页面的利用空间少于50%，另外对表进行增删改会引起对应的二级索引值的随机的增删改，也会导致索引结构中的数据页面上留下一些"空洞"，虽然这些空洞有可能会被重复利用，但终究会导致部分物理空间未被解决方案1、数据归档 2、改为逻辑删除

7.9 SQL调优的经验之谈

SQL规范性检查

select检查1、UDF用户自定义函数SQL语句的select后面使用了自定义函数UDF，SQL返回多少行，那么UDF函数就会被调用多少次，这是非常影响性能的。2、text类型检查如果select出现text类型的字段，就会消耗大量的网络和IO带宽，由于返回的内容过大超过max_allowed_packet设置会导致程序报错，需要评估谨慎使用。3、内联子查询在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。

from检查1、表的链接方式在MySQL中不建议使用Left Join，即使ON过滤条件列索引，一些情况也不会走索引，导致大量的数据行被扫描，SQL性能变得很差，同时要清楚ON和Where的区别。2、子查询由于MySQL的基于成本的优化器CBO对子查询的处理能力比较弱，不建议使用子查询，可以改写成Inner Join。

where检查1、索引列被运算当一个字段被索引，同时出现where条件后面，是不能进行任何运算，会导致索引失效。2、类型转换对于Int类型的字段，传varchar类型的值是可以走索引，MySQL内部自动做了隐式类型转换；相反对于varchar类型字段传入Int值是无法走索引的，应该做到对应的字段类型传对应的值总是对的。3、列字符集从MySQL 5.6开始建议所有对象字符集应该使用用utf8mb4，包括MySQL实例字符集，数据库字符集，表字符集，列字符集。避免在关联查询Join时字段字符集不匹配导致索引失效，同时目前只有utf8mb4支持emoji表情存储。

group by检查1、前缀索引group by后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的。2、函数运算

order by检查1、前缀索引order by后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的。2、字段顺序排序字段顺序，asc/desc升降要跟索引保持一致，充分利用索引的有序性来消除排序带来的CPU开销。

limit检查对于limit m, n分页查询，越往后面翻页即m越大的情况下SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行关联查询。 例如使用select * 的情况下直接用limit 600000，10 扫描的是约60万条数据，并且是需要回表60W次，也就是说大部分性能都耗在随机访问上，到头来只用到10条数据，如果先查出来ID，再关联去查询记录，就会快很多，因为索引查找符合条件的ID很快，然后再回表10次。就可以拿到我们想要的数据。

表结构检查1、NOT NULL属性根据业务含义，尽量将字段都添加上NOT NULL DEFAULT VALUE属性，如果列值存储了大量的NULL，会影响索引的稳定性。2、DEFAULT属性在创建表的时候，建议每个字段尽量都有默认值，禁止DEFAULT NULL，而是对字段类型填充响应的默认值。3、TEXT类型不建议使用Text数据类型，一方面由于传输大量的数据包可能会超过max_allowed_packet设置导致程序报错，另一方面表上的DML操作都会变的很慢，建议采用es或者对象存储OSS来存储和检索。

7.10 数据库自增ID用完了会怎么样？

自增ID达到上限用完了之后，分为两种情况：1、如果设置了主键，那么将会报错主键冲突。2、如果没有设置主键，数据库则会帮我们自动生成一个全局的row_id，新数据会覆盖老数据解决方案：表尽可能都要设置主键，主键尽量使用bigint类型，21亿的上限还是有可能达到的，但是覆盖数据显然是不可接受的。

7.11 undo log、binlog、redo log、undo loghttps://www.yuque.com/haolonglong/msvmro/tbfzo8

7.12 MySQL 普通索引和唯一索引的区别

查询和更新上的区别

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。建议尽量选择普通索引。

MySQL 的查询操作

普通索引会多次检索，不过因为 InnoDB 的数据是按照数据页为单位进行读写的，需要读取数据时，并不是直接从磁盘读取记录，而是先把数据页读到内存，再去内存中检索。所以性能的影响就是微乎其微。

除非在数据页的最后一条记录还是符合要求的数据，就需要再读一个数据页，这种情况很少，对CPU的消耗基本可以忽略了。

因此说，在查询数据方面，普通索引和唯一索引没差别。

MySQL 的更新操作

当需要更新一个数据时，如果数据在内存中就直接更新，而如果这个数据还没有在内存中的话，在不影响数据一致性的 前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据了。在下次查询需要访问这个数据的时候，将数据读入内存，然后执行change buffer中与这个有关的操作。通过这种方式就能保证这个数据逻 辑的正确性。

将change buffer中的操作应用到原数据，得到最新结果的过程称为merge。除了访问这个数据会触发merge外，系统有后 台线程会定期merge。在数据库正常关闭(shutdown)的过程中，也会执行merge操作。

如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存 是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

什么情况下会使用change buffer？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断 现在表中是否已经存在k=4的记录，而这必须要将数据读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

change buffer用的是buffer pool里的内存，不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool 的50%。结论：唯一索引用不了change buffer，只有普通索引可以用。

change buffer 的适用场景

change buffer 的作用是降低更新操作的频率，缓存更新操作。这样会有一个缺点，就是更新不及时，对于读操作比较频繁的表，不建议使用 change buffer。

因为更新操作刚记录进change buffer中，就读取了该表，数据页被读到了内存中，数据马上就merge到数据页中了。这样不仅不会降低性能消耗，反而会增加维护change buffer的成本。