Mysql索引

索引是什么
官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。
可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。磁盘IO从磁盘中读出数据至MySQL服务器内存

优势：

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

劣势：

索引会占据磁盘空间
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

索引分类

索引从**数据结构**进行划分的分为：**B+树索引、hash索引、R-Tree索引、FULLTEXT索引**。
索引从**物理存储**的角度划分为：**聚族索引**和**非聚族索引**。
从**逻辑的角度**分为：**主键索引**、**普通索引、唯一索引、联合索引**以及**空间索引**。
哈希索引**
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
只有 Memory 引擎显式支持哈希索引，这也是 Memory 引擎的默认索引类型。
因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这让哈希索引的速度非常快，但它也有一些限制：
哈希索引数据不是按照索引值顺序存储的，无法用于排序。
哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如在数据列(a,b)上建立哈希索引，如果查询的列只有a就无法使用该索引。（不支持联合索引）
哈希索引只支持等值比较查询，不支持任何范围查询。
对于hash相同的，采用链表的方式解决冲突。类似于hashmap。因为索引的结构是十分紧凑的，所以hash索引的查询很快。

什么是自适应哈希索引？

自适应哈希索引是 InnoDB 引擎的一个特殊功能，当它注意到某些索引值被使用的非常频繁时，会在内存中基于 B-Tree 索引之上再创键一个哈希索引，这样就让 B-Tree 索引也具有哈希索引的一些优点，比如快速哈希查找。这是一个完全自动的内部行为，用户无法控制或配置，但如果有必要可以关闭该功能。

全文索引

全文索引是将存储在数据库中的大段文本中的任意内容信息查找出来的技术。

疑问？用 like + 通配符 就可以实现模糊匹配，为什么还要全文索引？

性能：通配符匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
明确控制：使用通配符和正则表达式匹配，很难明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配；而一个词仅在第一个词确实匹配的情况下，才可以匹配或者才可以不匹配等。这些情况，使用通配符和正则表达式都不满足。
智能化的结果：虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索方式，但它们都不能提供一种智能化的选择结果的方法。 例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但 包含其他相关词的行。（不会分词找类似词）

MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们）， 哪些词不匹配，它们匹配的频率，等等
但是全文索引可能存在精度问题。
例子：

是 ‘胡歌很帅’，但是百度搜索的关键字(标红的就是关键字)却不只是完整的 ‘胡歌很帅’，这一句话被分割为’胡歌’，‘很帅’，‘帅’，‘胡歌很’，’胡歌很帅’等，这就是全文索引里的分词机制，也是导致精度问题的原因。

Q21：什么是全文索引？
通过数值比较、范围过滤等就可以完成绝大多数需要的查询，但如果希望通过关键字匹配进行查询，就需要基于相似度的查询，而不是精确的数值比较，全文索引就是为这种场景设计的。

MyISAM 的全文索引是一种特殊的 B-Tree 索引，一共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的”文档指针”。全文索引不会索引文档对象中的所有词语，它会根据规则过滤掉一些词语，例如停用词列表中的词都不会被索引。

如何处理低效的SQL语句
如何定位低效 SQL？
可以通过两种方式来定位执行效率较低的 SQL 语句。一种是通过慢查询日志定位，可以通过慢查询日志定位那些已经执行完毕的 SQL 语句。另一种是使用 SHOW PROCESSLIST 查询，慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志不能定位问题，此时可以使用 SHOW PROCESSLIST 命令查看当前 MySQL 正在进行的线程，包括线程的状态、是否锁表等，并显示 CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息，可以实时查看 SQL 的执行情况，同时对一些锁表操作进行优化。找到执行效率低的 SQL 语句后，就可以通过 SHOW PROFILE、EXPLAIN 或 trace 等丰富来继续优化语句。

SHOW PROCESSLIST
如果您有root权限，您可以看到所有线程。否则，您只能看到登录的用户自己的线程，通常只会显示100条如果想看跟多的可以使用full修饰（show full processlist）

SHOW PROFILE命令

通过 SHOW PROFILE 可以分析 SQL 语句性能消耗，例如查询到 SQL 会执行多少时间，并显示 CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息。例如 SHOW PROFILE CPU/MEMORY/BLOCK IO FOR QUERY N 分别查询 id 为 N 的 SQL 语句的 CPU、内存以及 IO 的消耗情况。

索引类型

主键索引：索引列中的值必须是唯一的，不允许有空值。
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
唯一索引：索引列中的值必须是唯一的，但是允许为空值。
全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。
空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
其他（按照索引列数量分类）
单列索引
组合索引：组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

SQL语句执行顺序

from (这里也包括from中的子语句)
join
on
Where 不可使用select中的别名，可以使用from中的别名 还有聚合函数
group by(开始使用select中的别名，后面的语句中都可以使用；但是不能使用ROW_NUMBER()等窗口函数的别名)
avg,sum…. 等聚合函数
having
select
distinct
order by
Limit

SQL语句执行过程

当执行一条查询的SQl的时候大概发生了一下的步骤：

1.客户端发送查询语句给服务器。
2.服务器首先检查缓存中是否存在该查询，若存在，返回缓存中存在的结果。若是不存在就进行下一步。（当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过SQL语句生成执行计划的整个过程，进而可以提高语句的执行速度。）
3.服务器进行SQl的解析、语法检测和预处理，再由优化器生成对应的执行计划。
（Parser（分词操作） 我们在客户端发送给 MySQL 的 SQL 语句实质上就是一个字符串，MySQL 需要将其拆分成一个个的分词（语法树）并进行识别，例如识别“SELECT”、“UPDATE”等关键字，将 t1 识别为一张表，将 id 识别为一列等
）
4.Mysql的执行器根据优化器生成的执行计划执行，调用存储引擎的接口进行查询。
5.服务器将查询的结果返回客户端。

MySQL优化器

Mysql在执行时，并不一定就会按照我们写的顺序执行，同时也不需要我们人为的从左到右的安排where后面的条件，mysql优化器会重写sql，如何才能看到mysql优化器重写后的sql呢？此时需要参考explain extended和show warnings这两个命令啦。

explain extended sql语句，然后show warnings查看。explain extended会输出sql的执行计划，查询记录的方式(全表扫描、全索引扫描、索引范围扫描等)、是否用上索引。show warnings会看到优化器重写后的sql。例如：

举例
1. Limit优化提前终止查询：发现满足查询条件的需求后，mysql会立即终止查询。如在使用limit子句，当查询到我们需要的数据行后，即停止访问其他行数据。
2.覆盖索引扫描：**如果索引中的列包含查询中所有需要使用的列，mysql可以使用索引返回需要的数据，而无需查询对应行数据。
3.索引下推优化**
查看执行计划时发现extra一栏中有Using index condition信息，说明使用了索引下推。

索引下推（默认是不推的）

简称ICP，MySQL 5.6引入了索引下推优化，可以在对联合索引(二级索引 || 非主键索引)遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数（大家可能感觉这是正常的，但是mysql5.6之前都不是这样实现的）
查看执行计划时发现extra一栏中有Using index condition信息，说明使用了索引下推。
明显的，第二种方式需要回表查询的全行数据比较少，这就是mysql的索引下推。mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制
注意点：**1、innodb引擎的表，索引下推只能用于二级索引。
举例：**

1. 索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。

对于user_table表，我们现在有（username,age）联合索引（加索引覆盖）**如果现在有一个需求，查出名称中以“张”开头且年龄小于等于10的用户信息，”select * from user_table where username like ‘张%’ and age > 10”.
有两种执行可能：
1、根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于10的用户数据。过程如下图。

2、根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据

索引的数据结构

Hash表：Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。
显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。**

二叉树

保证每次查找都可以这折半而减少IO次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。最坏情况转成链表了
平衡二叉树 时间复杂度是 O(log2n)。查询id=6，只需要两次IO。
缺点：
时间复杂度和树高相关。树有多高就需要检索多少次
平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高

B树：改造二叉树

这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：
B树的节点中存储着多个元素，每个内节点有多个分叉。
节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
父节点当中的元素不会出现在子节点中。
所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。
优点：但是磁盘IO次数会大大减少。比较是在内存中进行的
缺点：B树不支持范围查询的快速查找，

1. 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

   B 树又叫平衡多路查找树。

   B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。
   B+树和B树最主要的区别在于非叶子节点是否存储数据的问题
   所以放索引的磁盘块锁存放的索引数量是会跟这增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。
   范围查询：
   假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。
   首先查找值等于9的数据，查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。
   第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。
   主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。
   可以看到B+树可以保证等值和范围查询的快速查找，

因为 B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO 操作变多，查询性能变低。

什么是IO？

Mysql的索引实现

介绍完了索引数据结构，那肯定是要带入到Mysql里面看看真实的使用场景的，所以这里分析Mysql的两种存储引擎的索引实现：MyISAM索引和InnoDB索引
MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
辅助索引
在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。
查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

HASH索引

　　在MySQL的存储引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是存储引擎根据B-Tree索引自建的，后面会对其做具体说明。

hash索引的特点
1、hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。
2、对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。
3、hash索引包括键值、hash码和指针 。
　　因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。然而，hash索引也是存在其限制的：
hash索引的限制
1、Hash索引必须进行二次查找
　　使用哈市索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。
2、hash索引不能用于外排序
　　hash索引存储的是hash码而不是键值，所以无法用于外排序
3、hash索引不支持部分索引查找也不支持范围查找
　　只能用到等值查询，不能范围和模糊查询
4、hash索引中的hash码的计算可能存在hash冲突
　　当出现hash冲突的时候，存储引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若hash冲突很多的话，一些索引的维护代价机会很高，所以说hash索引不适用于选择性很差的列上（重复值很多）。姓名、性别、身份证（合适）
　　上面说到InnoDB的“自适应hash索引”。就是当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引上在创建一个hash索引，这样就让B-tree索引也具有hash索引的一些优点。这是一个完全自动的内部的行为，用户无法控制或配置，不过，如果有需要，完全可以关闭该功能。

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

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

B+树层数计算

三、那么回到我们开始的问题，通常一棵B+树可以存放多少行数据？
这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数 。

上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。
那么现在我们需要计算出非叶子节点能存放多少指针？
其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170 。那么可以算出一棵高度为2的B+树，能存放117016=18720 条这样的数据记录。
根据同样的原理我们可以算出一个高度为3的B+树可以存放 ：11701170*16=21902400 条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

稀疏索引

只为数据文件的每个存储块设一个键-指针对,它比稠密索引节省了更多的存储空间，但查找给定值的记录需更多的时间。只有当数据文件是按照某个查找键排序时，在该查找键上建立的稀疏索引才能被使用，而稠密索引则可以应用在任何的查找键。如图3-3所示，稀疏索引只为每个存储块设一个键-指针对。键值是每个数据块中第一个记录的对应值。

稠密索引

在稠密索引中，文件中的每个搜索码值都对应一个索引值。也就是说，稠密索引为数据记录文件的每一条记录都设一个键-指针对。如下图所示，索引项包括索引值以及指向该搜索码的第一条数据记录的指针，即我们所说的键-指针对。


Mysql：
Innodb的聚簇索引（主键）是稠密索引，非聚簇（辅助）索引是稀疏索引，所以理所当然一个表只能建立一个稠密索引，但是可以有多个稀疏索引。Innodb的主键索引是与数据存在一起的，所以通过稠密索引，找到叶子结点就可以找到目标数据。
Myisam是稀疏索引，跳跃式的索引存储，它的索引和数据行都是分开的。所以不管怎样，它都会在找到对应索引位置后进行一次回表查询。
回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。因此，可以通过索引先查询出id字段，再通过主键id字段，查询行中的字段数据，即通过再次查询提供MySQL查询速度。

为什么设置了命中了索引但还是造成了全表扫描

其中一个原因就是虽然命中了索引，但在叶子节点查询到记录后还要大量的回表，导致优化器认为这种情况还不如全表扫描会更快些

InnoDB索引

主键索引（聚簇索引）

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。
1.在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
2.如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
3.如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

聚簇索引

的叶子节点就是数据节点，

非聚簇索引

的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

InnoDB的辅助索引只会存储主键值而非磁盘地址
使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。主键索引树检索数据的过程称为回表查询。
创建了一个联合索引idx_abc(a,b,c)。

最左前缀匹配

原则和联合索引的索引存储结构和检索方式是有关系的
组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配
因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

覆盖索引。（是不用回表的二级索引）

Select是无论如何索引怎么搞都要回表；
查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。
*联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，

创建索引的三种方式

创建表时同时创建索引

2.在已有字段直接创建

3. 以修改表的方式添加索引 修改表结构

删除索引

DROP INDEX index_name ON table

索引优化！

1.复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2.. 使用短索引
短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作

1. 索引列排序

尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
5. like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6.不要在列上进行运算**
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。
理论上每张表里面最多可创建16个索引

1. 字符串不加单引号

   1. EXPLAIN简介

   使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
   ➤ 通过EXPLAIN，我们可以分析出以下结果：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询
• table：表示属于哪张数据表
• type：最重要的参数，表示连接使用了何种类型。从最好到最差的连接类型为const，eq_reg，ref，range，index和ALL。
• possible_keys：显示可能应用在这张表中的索引。如果为null，则表示没有可能的索引。
• key：实际使用的索引。如果为null，则表示没有使用索引。
• key_len：使用的索引的长度，在不损失精确性的情况下，长度越短越好。
• ref：表示索引的哪一列被使用了，如果可能的话，是一个常数。
• rows：Mysql认为必须检查的用来返回请求数据的行数。

index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
那么什么是聚簇索引？
聚簇索引的叶子节点就是数据节点，也就是说索引和数据行在一起；反之，如果叶子节点没有存储数据行，那么就是非聚簇索引。
如果没有设置主键，innodb会怎么处理？
如果表定义了主键，则会以这个主键作为key，进行构建聚簇索引
如果没有定义主键，则会选择一个唯一索引作为key，进行构建聚簇索引
如果没有主键也没有唯一索引，那么就会创建一个隐藏的row-id作为key，进行构建聚簇索引。

索引分类

HASH索引

在MySQL的存储引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是存储引擎根据B-Tree索引自建的，后面会对其做具体说明。
hash索引的特点
1、hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。
2、对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。
3、hash索引包括键值、hash码和指针 。
因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。然而，hash索引也是存在其限制的：
2.全文索引：
对文本的内容进行分词，进行搜索，适合大数据集
（1）、主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇索引，
（2）、单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
（3）、唯一索引
索引列的值必须唯一，但允许有空值
（4）、复合索引
即一个索引包含多个列
在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)
当表的行数远大于索引列的数目时可以使用复合索引

聚簇索引的特点：

优点：
数据访问更快，聚集索引将索引和数据保存在同一个B-Tree中，因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点：
更新聚集索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续
二级索引访问需要两次索引查找，而不是一次。

聚集索引和二级索引

另外，索引又可以分成聚集索引和非聚集索引（二级索引），它们区别就在于叶子节点存放的是什么数据：

• 聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点；
• 二级索引的叶子节点存放的是主键值，而不是实际数据。

因此，如果某个查询语句使用了**二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。**

索引失效

什么时候没用

1.有or必全有索引;2.复合索引未用左列字段;
3.like以%开头;除like aaa%外
4.需要类型转换;比如列类型是字符串，那定要在条件中将数据使用单引号引用起来,否则不使用索引
5.where中索引列有运算;
6.where中索引列使用了函数;7.如果mysql觉得全表扫描更快时（数据少）; 比如数据量极少的表

什么情况下不推荐使用索引？

1) 数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引
比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描。
2) 频繁更新的字段不要使用索引
比如logincount登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。
3) 字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引
只有在where语句出现，mysql才会去使用索引

1. where 子句里对索引列使用不等于（<>），使用索引效果一般

因为二叉树的查找是 = 查找，若是一个范围的话无法继续下探

1 覆盖索引

非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

回表：

非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

覆盖索引

在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，

数据库三大范式是什么

第一范式：每个列都不可以再拆分。 姓名和年龄字段
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。（主要针对联合主键） 订单号和产品号联合主键
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。学生姓名和王老师

MySQL事务

事务是作为单个工作单元而执行的一系列操作，单独的逻辑工作单元，进行独立操作，内部所有操作成功即成功，否则失败
事务必须有四种属性：原子性，一致性，隔离性，持久性。
原子性：在事务中进行的修改，要么全部执行，要么全不执行。如果在事务完成之前系统出现故障，SQLServer会撤销在事务中的修改。
一致性：为了事务在查询和修改时数据不发生冲突。一个事务执行前后都必须是一致性状态

隔离性：隔离性是一种用于控制数据访问的机制，能够确保事务只能访问处于期望的一致性级别下的数据。SQLServer使用锁对各个事务之间正在修改和查询的数据进行隔离。
持久性，当一个事物提交之后，数据库状态永远的发生了改变，即这个事物只要提交了，哪怕提交后宕机，他也确确实实的提交了，不会出现因为刚刚宕机了而让提交不生效，是要事物提交，他就像洗不掉的纹身，永远的固化了，除非你毁了硬盘。
SQL 标准定义了四个隔离级别：
READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
什么是脏读？幻读？不可重复读？
脏读(Drity Read)：某个事务已更新一份数据，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，

四大隔离级别实现原理

Mvcc

快照读是基于 MVCC 和 undo log 来实现的，适用于简单 select 语句。
增删查改
在InnoDB中，给每行增加两个隐藏字段来实现MVCC，一个用来记录数据行的创建时间，另一个用来记录行的过期时间（删除时间）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。
于是乎，默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样：
SELECT
只能读到创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。后边版本的删除我能读到，前边没有数据了
INSERT
将当前事务的版本号保存至行的创建版本号
UPDATE
新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号
DELETE
将当前事务的版本号保存至行的删除版本号

产生读的问题

View 在 MVCC 里如何工作的？

我们需要了解两个知识：

Read View 中四个字段作用；
聚簇索引记录中两个跟事务有关的隐藏列；
那 Read View 到底是个什么东西？

Read View 有四个重要的字段：

m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局最大的事务 id + 1。
creator_trx_id ：指的是创建该 Read View 的事务的事务 id。
知道了 Read View 的字段，我们还需要了解聚簇索引记录中的两个隐藏列。

假设在账户余额表插入一条小林余额为 100 万的记录，然后我把这两个隐藏列也画出来，该记录的整个示意图如下：

两个隐藏列

对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：

trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。
在创建 Read View 后，我们可以将记录中的 trx_id 划分这三种情况：

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。
这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

可重复读是如何工作的？

可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

假设事务 A （事务 id 为51）启动后，紧接着事务 B （事务 id 为52）也启动了，那这两个事务创建的 Read View 如下：

事务 A 和 事务 B 的 Read View 具体内容如下：

在事务 A 的 Read View 中，它的事务 id 是 51，由于它是第一个启动的事务，所以此时活跃事务的事务 id 列表就只有 51，活跃事务的事务 id 列表中最小的事务 id 是事务 A 本身，下一个事务 id 则是 52。
在事务 B 的 Read View 中，它的事务 id 是 52，由于事务 A 是活跃的，所以此时活跃事务的事务 id 列表是 51 和 52，活跃的事务 id 中最小的事务 id 是事务 A，下一个事务 id 应该是 53。
接着，在可重复读隔离级别下，事务 A 和事务 B 按顺序执行了以下操作：
事务 B 读取小林的账户余额记录，读到余额是 100 万；
事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务；
事务 B 读取小林的账户余额记录，读到余额还是 100 万；
事务 A 提交事务；
事务 B 读取小林的账户余额记录，读到余额依然还是 100 万；
接下来，跟大家具体分析下。
事务 B 第一次读小林的账户余额记录，在找到记录后，它会先看这条记录的 trx_id，此时发现 trx_id 为 50，比事务 B 的 Read View 中的 min_trx_id 值（51）还小，这意味着修改这条记录的事务早就在事务 B 启动前提交过了，所以该版本的记录对事务 B 可见的，也就是事务 B 可以获取到这条记录。
接着，事务 A 通过 update 语句将这条记录修改了（还未提交事务），将小林的余额改成 200 万，这时 MySQL 会记录相应的 undo log，并以链表的方式串联起来，形成版本链，如下图：

你可以在上图的「记录的字段」看到，由于事务 A 修改了该记录，以前的记录就变成旧版本记录了，于是最新记录和旧版本记录通过链表的方式串起来，而且最新记录的 trx_id 是事务 A 的事务 id（trx_id = 51）。

然后事务 B 第二次去读取该记录，发现这条记录的 trx_id 值为 51，在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 值是否在 m_ids 范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录。而是沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录，所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录。

最后，当事物 A 提交事务后，由于隔离级别时「可重复读」，所以事务 B 再次读区记录时，还是基于启动事务时创建的 Read View 来判断当前版本的记录是否可见。所以，即使事物 A 将小林余额修改为 200 万并提交了事务， 事务 B 第三次读取记录时，读到的记录都是小林余额是 100 万的这条记录。

就是通过这样的方式实现了，「可重复读」隔离级别下在事务期间读到的记录都是事务启动前的记录。

读提交是如何工作的？

读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。

也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。

那读提交隔离级别是怎么工作呢？我们还是以前面的例子来聊聊。

假设事务 A （事务 id 为51）启动后，紧接着事务 B （事务 id 为52）也启动了，接着按顺序执行了以下操作：

事务 B 读取数据（创建 Read View），小林的账户余额为 100 万；
事务 A 修改数据（还没提交事务），将小林的账户余额从 100 万修改成了 200 万；
事务 B 读取数据（创建 Read View），小林的账户余额为 100 万；
事务 A 提交事务；
事务 B 读取数据（创建 Read View），小林的账户余额为 200 万；
那具体怎么做到的呢？我们重点看事务 B 每次读取数据时创建的 Read View。前两次 事务 B 读取数据时创建的 Read View 如下图：
我们来分析下为什么事务 B 第二次读数据时，读不到事务 A （还未提交事务）修改的数据？
事务 B 在找到小林这条记录时，会看这条记录的 trx_id 是 51，在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，接下来需要判断 trx_id 值是否在 m_ids 范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录。而是，沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录，所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录。
我们来分析下为什么事务 A 提交后，事务 B 就可以读到事务 A 修改的数据？
在事务 A 提交后，由于隔离级别是「读提交」，所以事务 B 在每次读数据的时候，会重新创建 Read View，此时事务 B 第三次读取数据时创建的 Read View 如下：
事务 B 在找到小林这条记录时，会发现这条记录的 trx_id 是 51，比事务 B 的 Read View 中的 min_trx_id 值（52）还小，这意味着修改这条记录的事务早就在创建 Read View 前提交过了，所以该版本的记录对事务 B 是可见的。
正是因为在读提交隔离级别下，事务每次读数据时都重新创建 Read View，那么在事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。

快照读和当前读

快照读：读取的是快照版本，也就是历史版本
当前读：读取的是最新版本
普通的SELECT就是快照读，而UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

一致性非锁定读

　　consistent read （一致性读），InnoDB用多版本来提供查询数据库在某个时间点的快照。如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照；如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。

有这样三种锁我们需要了解

• Record Locks（记录锁）：在索引记录上加锁。
• Gap Locks（间隙锁）：在索引记录之间加锁，或者在第一个索引记录之前加锁，或者在最后一个索引记录之后加锁。
• Next-Key Locks：在索引记录上加锁，并且在索引记录之前的间隙加锁。它相当于是Record Locks与Gap Locks的一个结合。
• 所以，在修改的时候一定不是快照读，而是当前读。

而且，前面也讲过只有普通的SELECT才是快照读，其它诸如UPDATE、删除都是当前读。修改的时候加锁这是必然的，同时为了防止幻读的出现还需要加间隙锁。

• 一致性读保证了可用重复读
• 间隙锁防止了幻读

InnoDB采用的MVCC实现方式是：在需要时，通过undo日志构造出历史版本。
简单讲，如果没有MVCC，当想要读取的数据被其他事务用排它锁锁住时，只能互斥等待；而这时MVCC可以通过提供历史版本从而实现读取被锁的数据的历史版本，从而避免了互斥等待。
回想一下
1、利用MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题
2、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据，因此解决了幻读问题
综上所述，默认隔离级别的实现依赖于MVCC和锁，再具体一点是一致性读和锁。

2) 什么时候会加锁？

在数据库增删改查四种操作中，insert、delete和update都是会加排它锁(Exclusive Locks)的，而select只有显式声明才会加锁:

• select: 即最常用的查询，是不加任何锁的
• select … lock in share mode: 会加共享锁(Shared Locks)
• select … for update: 会加排它锁

  2 四种隔离级别实现原理（重要）

  READ UNCOMMITTED
  顾名思义，事务之间可以读取彼此未提交的数据。机智如你会记得，在前文有说到所有写操作都会加排它锁，那还怎么读未提交呢？
  机智如你，前面我们介绍排它锁的时候，有这种说明： 排他锁会阻止其它事务再对其锁定的数据加读或写的锁，但是对不加锁的读就不起作用了。
  READ UNCOMMITTED隔离级别下, 读不会加任何锁。而写会加排他锁，并到事务结束之后释放。
  READ COMMITTED
  顾名思义，事务之间可以读取彼此已提交的数据。
  InnoDB在该隔离级别(READ COMMITTED)写数据时，使用排它锁, 读取数据不加锁而是使用了MVCC机制。
  因此，在读已提交的级别下，都会通过MVCC获取当前数据的最新快照，不加任何锁，也无视任何锁(因为历史数据是构造出来的，身上不可能有锁)。
  但是，该级别下还是遗留了不可重复读和幻读问题： MVCC版本的生成时机: 是每次select时。都会生成最新提交快照，这就意味着，如果我们在事务A中执行多次的select，在每次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读，即：重复读时，会出现数据不一致问题，后面我们会讲解超支现象，就是这种引起的。
  REPEATABLE READ
  READ COMMITTED级别不同的是MVCC版本的生成时机，即：一次事务中只在第一次select时生成版本，后续的查询都是在这个版本上进行，从而实现了可重复读。
  但是因为MVCC的快照只对读操作有效，对写操作无效，举例说明会更清晰一点： 事务A依次执行如下3条sql，事务B在语句1和2之间，插入10条age=20的记录，事务A就幻读了。
  REPEATABLE READ级别，可以防止大部分的幻读，但像前边举例读-写-读的情况，使用不加锁的select依然会幻读。
  SERIALISABLE
  大杀器，该级别下，会自动将所有普通select转化为select … lock in share mode执行，即针对同一数据的所有读写都变成互斥的了，可靠性大大提高，并发性大大降低。

  Mvcc重点：

  读取已提交级别是每执行一次SELECT语句就会重新生成一份ReadView，而可重复读级别是只会在第一次SELECT语句执行的时候会生成一份，后续的SELECT语句会沿用之前生成的ReadView（即使后面有更新语句的话，也会继续沿用）。
  ReadView一致性视图

  MySQL日志

  redo log

  是固定大小、“循环写”的，写满了就会擦掉一部分记录。
  redo log 是物理日志，记录的是“在某个数据页上做了什么修改”。
  有了 redo log，InnoDB 就可以保证即使数据库发生了异常重启
  redo log侧重于重做！redo log中记录的是物理层面的数据页、偏移量。应对的问题是：MySQL异常宕机后，如何将没来得及提交的事物数据重做出来。
  所以仅使用binlog的话，当发生了crash之后，是无法凭借binlog把那些已经commit过的事务进行恢复的；

  逻辑日志与物理日志区别

  例如我们把一个page页中的一个数据从1改为2，再从2改为3，再从3改为4，再从4改为5。这是这个数据在page页中变换的过程。在物理日志中，它只会记录最后的一个值5，表示这个page页中的数据的值为5。而逻辑日志会记录1->2，2->3，3->4，4->5这个数据变化的过程。
  逻辑日志只会记录写操作，不会记录读操作。

  2. binlog

  binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如”给 ID=2 这一行的 c 字段加1“。

binlog 是“追加写”的，一个文件写完了会切换到下一个，不会覆盖以前的日志。
一般我们用 binlog 做主从复制，数据恢复等操作。
因为两者分工不同。binlog主要用来做数据归档，但是它并不具备崩溃恢复的能力，也就是说如果你的系统突然崩溃，重启后可能会有部分数据丢失，而redo log的存在则可以完美解决这个问题。

什么是两阶段提交

当有数据修改时，会先将修改redo log cache和binlog cache然后在刷入到磁盘形成redo log file，当redo log file全都刷入到磁盘时（prepare 状态）和提交成功后才能将binlog cache刷入磁盘，当binlog全部刷新到磁盘后会记录一个xid，然后在relo log file上打上commit标志（commit阶段）。

为什么需要两阶段提交？

我们先假设没有两阶段提交时，可能会有以下两种情况：
redo log 提交成功了，这时候数据库挂掉导致 binlog 没有成功写入。数据库重启之后通过 redo log 把数据恢复回来，但是 binlog 没有成功写入，导致我们在做主从复制或者数据恢复的时候，数据不一致。
binlog 提交成功了，这时候数据库挂掉导致 redo log 没有成功写入。数据库重启之后，无法恢复崩溃之前提交的那个事务，这部分数据更改在主库缺失。但是 binlog 已经成功写入了，从库反而有了该事务的改动，导致数据不一致。
综上我们知道，redo log 和 binlog 必须同时成功或同时失败，才能保证数据一致性。
不管是哪个数据库产品，一定会有日志文件。在MariaDB/MySQL中，主要有5种日志文件：1.错误日志(error log)：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。
2.查询日志(general log)：记录建立的客户端连接和执行的语句。
3.二进制日志(bin log)：记录所有更改数据的语句，可用于数据复制。
4.慢查询日志(slow log)：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。
5.中继日志(relay log)：主从复制时使用的日志。
由binlog和redo log的区别可知：binlog日志只用于归档，只依靠binlog是没有crash-safe能力的。但只有redo log也不行，因为redo log是InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

回滚日志(undo log)

作用：
1、提供回滚操作【undo log实现事务的原子性】
2.保存了事务发生前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读(MVCC)，也即非锁定读
第二次update之后的undo log的回滚指针就会指向刚刚那一条undo log日志，依次类推，就会形成一条undo log的回滚链，方便找到该条记录的历史版本。
什么时候产生：
事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性
什么时候释放：
当事务提交之后，undo log并不能立马被删除，
而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。
而对于UPDATE/DELETE则需要维护多版本信息，在InnoDB里，UPDATE和DELETE操作产生的Undo日志被归成一类，即update_undo。
undo日志属于逻辑日志，redo是物理日志，所谓逻辑日志是undo log是记录一个操作过程，不会物理删除undo log，sql执行delete或者update操作都会记录一条undo日志
结构：逻辑sql语句+回滚链+回滚指针

慢查询日志

如果MySQL重启后则会失效。
1、临时开启慢查询日志（如果需要长时间开启，则需要更改mysql配置文件，第6点有介绍）
set global slow_query_log = on;
6、永久设置慢查询日志开启，以及设置慢查询日志时间临界点
linux中，mysql配置文件一般默认在 /etc/my.cnf
它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中，long_query_time的默认值为10，意思是运行10秒以上的语句。

锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。
隔离级别与锁的关系
在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

按照锁的粒度分数据库锁有哪些？

锁机制与InnoDB锁算法

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。
MyISAM和InnoDB存储引擎使用的锁：

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

从锁的类别上来讲，有共享锁和排他锁。

• 共享锁: 又叫做读锁。 select … lock in share mode: 会加共享锁(Shared Locks)
• 排他锁: 又叫做写锁。 select … for update: 会加排它锁

InnoDB是基于索引来完成行锁

什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
常见的解决死锁的方法
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
如果业务处理不好可以用分布式事务锁或者使用乐观锁
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

SQL 约束有哪几种？

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

六种关联查询

• 交叉连接（CROSS JOIN）
• 内连接（INNER JOIN）
• 外连接（LEFT JOIN/RIGHT JOIN）
• 联合查询（UNION与UNION ALL）
• 全连接（FULL JOIN）
• 交叉连接（CROSS JOIN）

  一，内连接查询

  是指所有查询出的结果都是能够在连接的表中有对应记录的。
  其中，没有部门的人员和部门没有员工的部门都没有被查询出来:这就是内连接的特点，只查询在连接的表中能够有对应的记录，其中e.dept = d.id是连接条件
  

  二，左右连接

  Left outer join，等效于left join，在关联查询中，做外连接查询就是左连接查询，两者是一个概念
  
  

  三，全外连接

  顾名思义，把两张表的字段都查出来，没有对应的值就显示null，但是注意：mysql是没有全外连接的(mysql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接。例如：
  

  四，自连接查询

  自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名
  
  所以，自连接查询一般用作表中的某个字段的值是引用另一个字段的值，

  什么是子查询

  条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果
  嵌套**：**多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

  mysql中 in 和 exists 区别

  mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。
  如果查询的两个表大小相当，那么用in和exists差别不大。
  如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
  not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

  varchar与char的区别

  char的特点
  char表示定长字符串，长度是固定的；
  如果插入数据的长度小于char的固定长度时，则用空格填充；
  因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
  对于char来说，最多能存放的字符个数为255，和编码无关
  varchar的特点
  varchar表示可变长字符串，长度是可变的；
  varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
  对于varchar来说，最多能存放的字符个数为65532
  VARCHAR类型适用于文章标题、商品名称、图片路径等字符串最大长度大于平均长度的列。
  CHAR类型适用于存储密码的MD5值、手机号码、身份证号码、订单号等长度相对固定的列

主键使用自增ID还是UUID？主要根据索引结构

自增ID和UUID作为主键的考虑主要有两方面，一个是性能另一个就是存储的空间大小，一般没有特定的业务要求都不推荐使用UUID作为主键。
因为使用UUID作为主键插入并不能保证插入是有序的，有可能会涉及数据的挪动，也有可能触发数据页的分裂，因为一个数据页的大小就是16KB，这样插入数据的成本就会比较高。
而自增ID作为主键的话插入数据都是追加操作，不会有数据的移动以及数据页的分裂，性能会比较好。
另一方面就是存储空间，自增主键一般整形只要4个字节，长整形才占8字节的大小空间，而使用UUID作为主键存储空间需要16字节的大小，会占用更多的磁盘，在二级索引中也会存出一份主键索引，这样多占用消耗的空间就是两倍，性能低，所以不推荐使用。

分库分表

20.能说一说分库分表吗？怎么分？

首先为什么要分表？（1） 如果一个表的每条记录的内容很大，那么就需要更多的IO操作，如果字段值比较大，而使用频率相对比较低，可以将大字段移到另一张表中，当查询不查大字段的时候，这样就减少了I/O操作 （2）如果表的数据量非常非常大，那么查询就变的比较慢；也就是表的数据量影响查询的性能。（3）表中的数据本来就有独立性，例如分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，而另外一些数据不常用。（4） 分表技术有(水平分割和垂直分割)

水平分表：是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。
水平分库：是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。例如库存，
垂直分库：是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。 例如下单，积分，广告等
垂直分表：将一个表按照字段分成多表，每个表存储其中一部分字段。

ACID靠什么保证的呢？

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性一般由代码层面来保证
I隔离性由MVCC来保证
D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

牛客练习SQL语句平台

查询结果去重牛客题霸牛客网 (nowcoder.com)
牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com)

InnoDB 和 MyISAM 有什么区别？

InnoDB

1、支持事务。默认的事务隔离级别为可重复读（REPEATABLE-READ），通过MVCC（并发版本控制）来实现。
2、使用的锁粒度默认为行级锁，可以支持更高的并发；当然，也支持表锁。
3、支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
4、可以通过自动增长列，方法是auto_increment。
5、配合一些热备工具可以支持在线热备份；
6、在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
7、对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

MyISAM适合：

1、MyISAM：
①不支持事务，但是整个操作是原子性的(事务具备四种特性：原子性、一致性、隔离性、持久性)
②不支持外键，支持表锁，每次所住的是整张表
（1）做很多count 的计算；
（2）插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择；
（3）没有事务。
⑤采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
③一个MyISAM表有三个文件：索引文件，表结构文件，数据文件

主键和外键

主要是为了维护关系数据库的完整性，总结一下：
1.主键是能确定一条记录的唯一标识，
比如，一条记录包括身份正号，姓名，年龄。
身份证号是唯一能确定你这个人的，
其他都可能有重复，
所以，身份证号是主键。
2.外键用于与另一张表的关联。
是能确定另一张表记录的字段，
用于保持数据的一致性。

SQL优化

1.什么是小表驱动大表 ？

小表驱动大表指的是用小的数据集驱动大得数据集。
现有两个表A与B ，表A有200条数据，表B有20万条数据 ;
小表驱动大表 > A驱动表，B被驱动表

综上:小表驱动大表的主要目的是通过减少表连接创建的次数，加快查询速度 。
优化的目标都是尽可能的减少关联的 循环次数，保证小表驱动大表
例: user表10000条数据，class表20条数据
select from user u left join class c u.userid=c.userid
这样则需要用user表循环10000次才能查询出来，而如果用class表驱动user表则只需要循环20次就能查询出来
会出现下面的情形，也就是笛卡尔现象，表一有5条记录，表二有3条记录，那么对于第一张表而言有5种选择，而对于第二张表来说有3种选择。所以结果就是53种选择了，也就是笛卡尔积。：

2.怎么区分那个是驱动表与被驱动表 ？

通过EXPLAIN查看SQL语句的执行计划可以判断在谁是驱动表，EXPLAIN语句分析出来的第一行的表即是驱动表 ;

1. 当使用left join时，左表是驱动表，右表是被驱动表 ;
   2.当使用right join时，右表时驱动表，左表是被驱动表 ;
   3.当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表 ;

结论：

• 当使用left join时，左表是驱动表，右表是被驱动表 ; ;
• 在执行效率上，小表驱动大表优于大表驱动小表 ;
• 驱动表索引没有生效，被驱动表索引有效 ;

优化第二步：去除所有JOIN，让MySQL自行决定，explain第一张表就是驱动表，数据量比其它两张表都要小！

Sql注入

#{} 和 ${} 的区别—详细

数据库优化

1，优化sql和索引
(1)用慢查询日志定位执行效率低的SQL语句。
(2)用explain分析SQL的执行计划。
(3)确定问题，采取相应的优化措施，建立索引。
(4)避免全局扫描：where xx is null, !=, <>, in(可替代为between，exists), not in, or, %开头模糊索引，where中对字段进行表达式操作，
（5）书写高效率的 SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

Mysql查询缓慢原因
1，查询一直很慢的情况

• 没有索引或者没有用到索引
• 统计出错，走的是全表扫描
• I/O吞吐量小，形成瓶颈。
• 网络速度慢

6、对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：
（1）索引的目的是什么？
快速访问数据表中的特定信息，提高检索速度
创建唯一性索引，保证数据库表中每一行数据的唯一性。
加速表和表之间的连接
使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
（2）索引对数据库系统的负面影响是什么？
负面影响：
创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。
（3）为数据表建立索引的原则有哪些？
在最频繁使用的、用以缩小查询范围的字段上建立索引。
在频繁使用的、需要排序的字段上建立索引
（4）什么情况下不宜建立索引？
对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。

Mysql调优方法

1. 避免 SELECT *
2. 选择正确的存储引擎
3. 使用汇总表优化查询

4.not in和<>子查询尽量改成关联查询

5.大表分批次更新删除

6.修改大表结构

7.为搜索字段建索引

1. 当只要一行数据时使用 LIMIT 1
2. 不用 MYSQL 内置的函数，因为内置函数不会建立查询缓存。
3. 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库。
4. Explain + SQL语句

1. System只出现于单表单行（一张表只有一行数据）~基本不可能存在的
2. 表示通过索引一次就找到了,const用于primary key或者unique索引。

因为只匹配一行数据，所以很快 如将主键置于where列表中，MySQL就能将该查询转换为一个常量

4.ref **非唯一的索引扫描，返回匹配单个值的所有行。**

3.eq_ref 唯一性索引扫描，对于每一个索引键，表中只有一行数据与之对应，常见于主键或者唯一索引扫描。

5.range

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<’外，in和or也是索引范围扫描。

6.all = index

这便是所谓的“全表扫描”

6.index

这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。

二，mysql中查询缓存的优化

开启mysql查询缓存，当执行完全相同的sql语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存就会失效，修改比较频繁的表不适合做查询缓存

具体Sql优化操作

一、表设计之关联关系：

1、一对一：数据一部分频繁被访问，一些不怎么被访问，则这样可以创建两张表。
外键：表中用于建立关系的字段称为外键，一张表可能有多个外键，但只会有一个主键。
如果建立关系：从表中添加外键指向主表的主键。
应用场景：用户表和用户信息扩展表；商品表和商品信息扩展表；
例：创建user和userinfo表：假如用户包含用户名、密码、昵称、年龄、地址、性别字段。我们就可以将常用的用户名和密码存储到user表，并未其添加主键id，同时创建从表userinfo，存储其他昵称、年龄等信息，并添加外键userid对应主表的主键id，这样就保证了一对一的关系，在我们需要查询某用户的userinfo时可以用id对应从表外键userid进行匹配查询即可。
2、一对多：有AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条数据。
应用场景：用户表和部门表；商品表和分类表（一个分类里包含很多商品，但是一种商品只能属于一个分类）。
建立关系：在多个一段添加外键，指向另外一张表的主键。
例：创建emp表和dept表：比如我们创建的dept部门表并添加主键id，中有两个部门一个技术部id = 1、一个设计部对应id=2，此时我们再创建一个emp表，并为其添加外键deptid = 1对应主表dept的主键id，例如我们添加小张 deptid = 1，小李deptid = 1，小王deptid = 2，小赵deptid = 2这是我们通过dept表中的主键id 为 1去从表emp中对应的deptid相匹配的1去查找，可以找出小张和小李两个人，这就实现了一对多，一个主表dept对应了从表emp中的多条数据。
3、多对多：有AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的多条数据。
应用场景：老师表和学生表；用户表和角色表；角色表和权限表；
建立关系：通过第三张关系表保存两张主表的关系；第三张关系表里记录的是两个主表的主键；
例：创建老师表，学生表和关系表：
4.自关联

切换引擎方法

mysql表切换引擎的几种方法

【1】ALTER TABLE

mysql > ALTER TABLE mytable ENGINE=InnoDB;

这种语法适合所有引擎，但是转换过程会耗费大量时间。mysql为此要执行一个旧表到新表的逐行复制。在这期间，转换操作可能会占用服务器的所有I/O处理能力，并且在转换时，源表要被读加锁。因此，在一个繁忙的表上做此操作，要加以注意。
如果从一种引擎到另一种引擎做表转换，所有属于原始引擎的专用特性都会丢失。例如，将一个InnoDB表转换成MyISAM表，再转换回来，最初定义在原InnoDB表上的所有外键都会丢失。

【2】转储(Dump)和导入(Import)

如果想对表转换的过程做更多控制，可以选择使用mysqldump工具，将表先转储成一个文本文件，然后再编辑转储文件，修改其中的CREATE TABLE语句。一定要注意修改表名和引擎类型，因为即使引擎类型有所不同，同一数据库也不允许存在相同表名的两张表。另外，mysqldump在CREATE TABLE语句之前，会默默地加上DROP TABLE命令，如果不注意，很可能丢失原有数据。

【3】CREATE 和 SELECT
此方法在前两种方法之间做了一个平衡，它不转储整张表，或者一次性转换所有数据，而是创建一个新表，使用mysql的INSERT … SELECT语法来转移数据。
如下：
mysql > CREATE TABLE innodb_table like myisam_table;
mysql > ALTER TABLE innodb_table ENGINE=InnoDB;
mysql > INSERT INTO innodb_table SELECT FROM myisam_table;
如果数据量不大，这种办法效果不错，但是更高效的办法是增量地填充表，在填充每个增量数据块的时候都提交事务，这样就不会导致撤销日志变得过于庞大。假定id是主键，可以重复运行下列查询(每次逐次增大x和y的值)，直至所有的数据都复制到新表。
mysql > START TRANSACTION;
mysql > INSERT INTO innodb_table SELECT FROM myisam_table
-> WHERE id BETWEEN x AND y;
mysql > COMMIT;
转移操作完成后，源表扔会保留，可以在操作完成后删除，而此时，新表已被填充完毕。注意：如果有必要，请在转换时加锁源表，避免复制时数据不一致。

4.你知道执行一条查询语句的流程吗？

当Mysql执行一条查询的SQl的时候大概发生了以下的步骤：
客户端发送查询语句给服务器。
服务器首先进行用户名和密码的验证以及权限的校验。
然后会检查缓存中是否存在该查询，若存在，返回缓存中存在的结果。若是不存在就进行下一步。注意：Mysql 8就把缓存这块给砍掉了。
接着进行语法和词法的分析，对SQl的解析、语法检测和预处理，再由优化器生成对应的执行计划。
Mysql的执行器根据优化器生成的执行计划执行，调用存储引擎的接口进行查询。服务器将查询的结果返回客户端。
Mysql中语句的执行都是都是分层执行，每一层执行的任务都不同，直到最后拿到结果返回，主要分为Service层和引擎层。

redis的事务和mysql的事务有什么区别

MySQL事务的四大特性：原子性，一致性，隔离性，持久性
mysql：

Begin：显式的开启一个事务

Commit：提交事务，将对数据库进行的所有的修改变成永久性

Rollback：结束用户的事务，并撤销现在正在进行的未提交的修改

redis：

Multi：标记事务的开始

Exec：执行事务的commands队列
Discard：结束事务，并清除commands队列
mysql会默认开启一个事务，且缺省设置是自动提交，即每成功执行sql，一个事务就会马上commit，所以不能rollback
redis默认不会开启事务，即command会立即执行，而不会排队，并不支持rollback
mysql(包含两种方式)：
用Begin、Rollback、commit显式开启并控制一个 新的 Transaction
执行命令 set autocommit=0，用来禁止当前会话自动commit，控制 默认开启的事务
redis：
用multi、exec、discard，显式开启并控制一个Transaction。
mysql：
mysql实现事务，是基于undo/redo日志
undo记录修改前状态，rollback基于undo日志实现
redo记录修改后的状态，commit基于redo日志实现
redis:
redis实现事务，是基于commands队列
如果没有开启事务，command将会被立即执行并返回执行结果，并且直接写入磁盘
如果事务开启，command不会被立即执行，而是排入队列，并返回排队状态(具体依赖于客户端(例如：spring-data-redis)自身实现)。
调用exec才会执行commands队列
Redis事务不支持Rollback(重点)
Redis命令可能会执行失败，仅仅是由于错误的语法被调用(命令排队时检测不出来的错误)，或者使用错误的数据类 型操作某个Key：COMMAND排队失败)
这意味着，实际上失败的命令都是编程错误造成的，都是开发中能够被检测出来的，生产环境中不应该存在。
需要注意的是，即使命令失败，队列中的所有其他命令也会被处理——Redis不会停止命令的处理
如果排队命令时发生错误，大多数客户端将中止该事务并清除命令队列

如果key在Exec命令执行前有改变，那么整个事务被取消，Exec返回null表示事务没有成功。证明有锁冲突了，全部事务取消造成命令浪费，从而解决了分布式事务问题，这种锁处理也称为“乐观锁”，它提供有效昂是保障竞争条件。容易造成资源浪费




watch指令类似于乐观锁事务提交时，如果key的值已被别的客户端改变，整个事务队列都不会被执行
无原子性
Redis 开始事务 multi 命令后，Redis 会为这个事务生成一个队列，每次操作的命令都会按照顺序插入到这个队列中。
但这并不保证原子性，redis的错误分为两种，一种是比较明显的语法错误，整个事务都不会被执行，但是另一种编译未错，运行错误时，其他语句仍然会执行
但是其处理网络IO和执行客户端请求的只有一个线程，对于客户端而言是个单线程服务器。
所以redis一致性和隔离性都天然的好。

Redis为什么需要事务

redis服务器本身而言是没有竞态的，将活跃的客户端一个一个取出，将客户端中的请求一条一条执行，所有的处理都是one by one的。
但是一个redis服务器会有多个客户端进行连接，他们之间可能会出现竞态的。
超卖问题

总结

1. Redis 具备了一定的原子性，但不支持回滚；
2. Redis 并不能用传统的一致性概念来看待。(或者说 Redis 在设计时就无视这点)；
3. Redis 具备隔离性；
4. Redis 通过一定策略可以保证持久性。

Redis的事务就是批处理执行

MySQL中的锁

全局锁

全局锁是怎么用的？

要使用全局锁，则要执行这条命：

flush tables with read lock
执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

对数据的增删查改操作，比如 insert、delete、update等语句；
对表结构的更改操作，比如 alter table、drop table 等语句。
如果要释放全局锁，则要执行这条命令：

unlock tables

当然，当会话断开了，全局锁会被自动释放。

全局锁应用场景是什么？

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。
举个例子大家就知道了。
在全库逻辑备份期间，假设不加全局锁的场景，看看会出现什么意外的情况。
如果在全库逻辑备份期间，有用户购买了一件商品，一般购买商品的业务逻辑是会涉及到多张数据库表的更细，比如在用户表更新该用户的余额，然后在商品表更新被购买的商品的库存

加全局锁又会带来什么缺点呢？

加上全局锁，意味着整个数据库都是只读状态。

那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。
既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？
有的，如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。
因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。
备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。
InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

表锁

先来说说表锁。

如果我们想对学生表（t_student）加表锁，可以使用下面的命令：

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent wirte;

需要注意的是，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

也就是说如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

unlock tables

另外，当会话退出后，也会释放所有表锁。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

元数据锁

再来说说元数据锁（MDL）。

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；
MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL 不需要显示调用，那它是在什么时候释放的?

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，
那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

意向锁

接着，说说意向锁。

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；
也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加独占锁
select … lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select … for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

AUTO-INC 锁

最后，说说 AUTO-INC 锁。

在为某个字段声明 AUTO_INCREMENT 属性时，之后可以在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁；
当 innodb_autoinc_lock_mode = 2，就采用轻量级锁；
当 innodb_autoinc_lock_mode = 1，这个是默认值，两种锁混着用，如果能够确定插入记录的数量就采用轻量级锁，不确定时就采用 AUTO-INC 锁。
不过，当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是会带来一定的问题。因为并发插入的存在，在每次插入时，自增长的值可能不是连续的，这在有主从复制的场景中是不安全的。

行级锁有哪些？

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。
行级锁的类型主要有三类：
Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
前面也提到，普通的 select 语句是不会对记录加锁的，如果要在查询时对记录加行锁，可以使用下面这两个方式：
//对读取的记录加共享锁
select … lock in share mode;
//对读取的记录加独占锁
select … for update;
上面这两条语句必须再一个事务中，当事务提交了，锁就会被释放，因此在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。
那具体跟在哪些纪录上加锁，就跟具体的 select 语句有关系了，比较复杂，这个留到下篇再讲啦。

MySQL事务与隔离级别

脏读
如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。
如果在上面这种情况事务 A 发生了回滚，那么事务 B 刚才得到的数据就是过期的数据，这种现象就被称为脏读。
不可重复读
在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。
在这过程中如果事务 B 更新了这条数据，并提交了事务，那么当事务 A 再次读取该数据时，就会发现前后两次读到的数据是不一致的，这种现象就被称为不可重复读。
事务的隔离级别有哪些？
幻读
在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。
发现和前一次读到的记录数量不一样了，就感觉发生了幻觉一样，这种现象就被称为幻读。

读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；

读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；

可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；

串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

所以，要解决脏读现象，就要升级到「读提交」以上的隔离级别；要解决不可重复读现象，就要升级到「可重复读」的隔离级别。
InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。
「读提交」隔离级别是在每个读取数据前都生成一个 Read View，而「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

可重复读隔离级别是如何实现的？

「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。想要知道可重复读隔离级别是如何实现的，我们需要了解两个知识：

Read View 中四个字段作用；

聚族索引记录中两个跟事务有关的隐藏列；

那 Read View 到底是个什么东西？

SQL语句笔记

图解SQL面试题：如何分组比较？ - 部门工资最高的员工 - 力扣（LeetCode） (leetcode-cn.com)
现在有三个表，“学生表”，“课程表”，“成绩表”。
“学生表”记录了学生的基本信息，有“学号”、“姓名”、“出生日期”、“性别”。

现在要查找出每门课程中成绩最好的学生的姓名和该学生的课程及成绩。
需要注意：可能出现并列第一的情况。
2）如何联结？

“学生信息表”和“成绩表”都有“学号”，所以联结条件为学号。

on a.学号=c.学号

“课程表”和“成绩表”都有“课程号”，所以联结条件为课程号。

on a.课程号=b.课程号

from 成绩表 a
left join 课程表 b on a.课程号=b.课程号
left join 学生信息表 c on a.学号=c.学号

select c.学号,c.姓名,b.课程,a.成绩
from 成绩表 a
left join 课程表 b on a.课程号=b.课程号
left join 学生信息表 c on a.学号=c.学号;

我们来看这句话里的关键词：

1）“每门课程”，每当出现“每”就是要用分组汇总了，所以是子句（group by课程号）

2）“成绩最好” ，就是最大成绩了，所以是max(成绩)

因此，查询条件是：
每门课程中成绩最好的学生的姓名和该学生的课程及成绩。

select 课程号,max(成绩)
from 成绩表
group by 课程号;
4.合并前面的步骤，确定最终查询

将第3步的查询条件，放到第2步多表联结的结果中用于筛选出符合条件的数据。所以查询条件如下：

where (课程号,成绩) in
(select 课程号,max(成绩)
from 成绩表
group by 课程号);

需要注意的是，当两列同时作为关键字段进行条件查询时，比如这个案例里是(课程号,成绩) in，是将两列合成一个值来查找。比如，“语文”和“90”合并为值“语文 90”。
每科最高，in主要作用是把这两个连上比一个

所以这两列的顺序要和子查询里列的顺序保持一致。如果列的段顺序不一样，比如“90 语文”和“语文 90”就匹配不上，那么查询结果就是空的了。

最终sql：
每门课程中成绩最好的学生的姓名和该学生的课程及成绩。
select c.学号,c.姓名,b.课程,a.成绩
from 成绩表 a
left join 课程表 b on a.课程号=b.课程号
left join 学生表 c on a.学号=c.学号
where (a.课程号,a.成绩) in
(select 课程号,max(成绩) from 成绩表 group by 课程号);

找出每个部门工资最高的员工。
select max(sal)
From
Order by department

MySQL分库分表（问题整理）

为何要分库分表

因为数据库中的数据量不一定是可控的
在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多
表中的数据量也会越来越大
相应地，数据操作的开销也会越来越大
另外，单个服务器的资源（如CPU、磁盘、内存、IO等）是有限的
最终数据库所能承载的数据量、数据处理能力都将遇到瓶颈
因此，当业务发展到某个阶段，就需要根据具体场景考虑进行分库分表

分库还是分表

当单表数据量太大，这时会出现查询耗时长，影响正常CRUD。
大彬：解决方法就是，切分成多个更小的表，即分表。
大彬：当单库数据量太大，单库所在服务器上磁盘空间不足，I/O有限。分库

问题一：数据如何选择哪个库

• 固定分片的hash算法求模法
• 日期列分区法
• 一致性Hash

  问题二，在客户端执行分配策略还是代理类？

  代理分片就是在应用层和数据库层之间添加一个代理层，把分片的路由规则配置在代理层，代理层对外提供与JDBC兼容的接口给应用层，在业务实现之后，在代理层配置路由规则即可；
  这种方案的优点：让应用层的开发人员专注于业务逻辑的实现，把分库分表的配置留给代理层处理
  搭建集群提高可用性，
  同样的业务存在缺点：增加了代理层，这样的话对每个数据库操作都增加了一层网络传输，这对性能是有影响的，同时需要维护增加的代理层，也有了硬件成本，线上生产环境出现了问题，不能迅速定位，需要有一定的技术专家来维护

  分布式事务问题

1. 主从宕机恢复中的分布式事务问题，

两阶段提交 redolog 和binlog

1. 各数据库之前的事务问题

两阶段提交，事务协调者

三种解决方案

• 两阶段提交协议

两阶段提交协议中的两阶段是：准备阶段和提交阶段，两个阶段都是由事务管理器（协调者）发起，事务管理器能最大限度的保证跨数据库操作的事务的原子性。

则必须log到完整的不一致信息。也可以是将本地存款服务作为消息发送到消息中间件，由消息中间件接管后续操作。
最后添加的重试机制是最大程度的确保补偿服务执行，保持数据的一致性，如果重试之后还是失败，则将操作保存在消息中间件中，等待后续处理，这样就更多了一重保障

扩容与迁移问题

在分库分表中，如果涉及的分片已经达到了承载数据的最大值，就需要对集群进行扩容，通常包括以下的步骤

按照新旧分片规则，对新旧数据库进行双写
将双写前按照旧分片规则写入的历史数据，根据新分片规则迁移写入新的数据库
将按照旧的分片规则查询改为按照新的分片规则查询
将双写数据库逻辑从代码中下线，只按照新的分片规则写入数据
删除按照旧分片规则写入的历史数据
2步骤中迁移数据时，数据量非常大，通常会导致不一致，因此需要先迁移旧的数据，洗完后再迁移到新规则的新数据库下，再做全量对比，对比评估在迁移过程中是否有数据的更新，如果有的话就再清洗、迁移，最后以对比没有差距为准

主从复制问题

MySQL优化七大步骤

一条SQL语句的执行时间变长，可能是由于以下几个原因导致的。数据量变多，这种情况可以考虑读写分离和分库分表；关联了太多的表（SQL语句本身的问题），应该对SQL进行优化；服务器性能下降，此时就需要对MySQL本身进行调优，可以通过修改my.cnf配置文件进行优化。

3 SQL语句的优化

3.5.type
type字段描述表的连接方式。主要有以下几种，从上到下，性能依次下降。

system：表只有一行数据。
const：表中最多只有一行匹配的数据，常出现于将主键或者unique索引作为查询条件的语句。(eg : SELECT FROM tbl_name WHERE primary_key=1; )
eq_ref：主键或非空唯一索引扫描，对于ref_table表中的每一个键值，other_table中最多只有一条记录与之匹配。(eg : SELECT FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;)
ref：非唯一索引扫描(eg : SELECT FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;)
fulltext：使用全文索引。
ref or null：类似于ref，但是MySQL会额外扫描包含空值的行。(eg : SELECT FROM ref_table WHERE key_column=expr OR key_column IS NULL;)
index merge：对多个索引进行扫描然后将他们各自的结果进行合并。(eg:SELECT FROM tbl_name WHERE key1 = 10 OR key2 = 20;)
unique subquery：在类似于value IN (SELECT primary_key FROM single_table WHERE some_expr)的子查询中，替代eq_ref。其中子查询中的primary_key 是唯一索引。
index subquery：在类似于value IN (SELECT key_column FROM single_table WHERE some_expr)的子查询，替代ref。其中子查询中的key_column 是非唯一索引。
range: 使用索引检索指定范围的行。(eg:SELECT FROM tbl_name WHERE key_column IN (10,20,30);)
index: 扫描整个索引树。
all：扫描全表，以找到匹配的行。

3.6.possible_keys
显示可能应用在这张表上的索引，一个或者多个。查询涉及到的字段，若涉及索引，则该索引会被列出来，但不一定被使用。

3.7.key
实际使用的索引。

3.8.key_len
表示使用了索引的长度，单位为字节。该字段可以用来检查，sql语句是否充分的使用上了索引，该字段越大越好。

3.9.ref
显示表中的哪些列用来和索引进行比较。ref的之也有可能会是一个常数。

3.10.rows
mysql认为在查询时必须要检查的行数。显然地，该值越小越好。
Limit例如

3.11.filtered
表示存储引擎返回的数据在经过server过滤后还剩下多少，是一个百分比。

3.12.extra
这个字段包含MySQL解析查询地额外信息（重要）。该字段的取值比较多，这里只介绍几个比较重要的：
Using filesort：说明MySQL会对数据使用一个外部索引进行排序，而不是按照表内的索引顺序进行读取。MySQL中无法按照索引进行排序的操作称为”文件排序”。（eg：select from emp where id < 1000 order by deptid;其中deptid并没有建立索引）
Using temporary：使用了临时表来保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组group by。（eg：select count() from emp GROUP BY emp.deptid;其中deptid并没有建立索引）
Using index：利用索引进行了排序或分组。
Using where：表明使用了where过滤
Using join buffer：使用了连接缓存
impossible where：where子句的值总是false，根据该条件不能查到任何数据。（eg：select * from table_a where 0 > 1;）
3.13总结
通过对explain结果各个字段的学习，其中有些字段可以用来指导我们进行SQL优化，一些经验和原则可以总结如下：

id字段，一个id值，表示一趟查询，查询的趟数越少越好;
type字段，应该尽量避免全表扫描；
key_len字段，越大越好；
rows字段，越小越好；
extra字段，尽量避免using filesort和using temporary。
换句话说，SQL优化就是通过修改SQL，增加索引等手段，使得这些指标尽可能地满足上述的要求。

4 索引的优化（索引失效问题）

1.复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2.. 使用短索引
短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作

1. 索引列排序

尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
5. like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6.不要在列上进行运算**
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。
理论上每张表里面最多可创建16个索引

1. 字符串不加单引号

小表驱动大表

3.表结构优化（选用适用的字段属性）

为何要表结构优化

数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以减少 IO 次数可以在很大程度上提高数据库操作的性能。

由于MySQL数据库是基于行存储的数据库，而数据库IO操作的时候是以 page 的方式，也就是说，如果我们每行记录所占用的空间量减小，就会使每个 page 中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的 page 就会减少，也就是 IO 操作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数据命中的几率，也就是缓存命中率。我们无法改变数据库中需要存储的数据，但是我们可以在数据的存储方式方面做一些优化。

两个方面

一、数据类型的选择：

（2）对于定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。
1、适当拆分：

我们可能希望将一个完整对象对应一张数据库表，这对于应用程序开发来说是很友好的，但有时可能会在性能上带来较大的问题。当我们的表中存在类似于 TEXT 或者是很大的 varchar 类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们可以将其拆分到另外的独立表中**，**以减少常用数据表所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

2、适度冗余：

冗余确实这样做会增大每条记录的大小，降低每条记录中可存放数据的条数，但是在有些场景下我们仍然还是不得不这样做，比如：被频繁引用且只能通过 Join连接 2张（或者以上）大表的方式才能得到的独立小字段，这样的场景由于每次Join连接仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

3、尽量使用 not null：

（1）null 类型比较特殊，SQL 难优化。虽然 MySQL null 类型和 Oracle 的 null 有差异，会进入索引中，但如果是一个组合索引，那么这个 null 类型的字段会极大影响整个索引的效率。
（2）很多人觉得 null 会节省一些空间，所以尽量让 null 来达到节省IO的目的，但是大部分时候这会适得其反，因为对于允许为 null 的字段，mysql 会多需要一个1字节记录是否为 null；同时也带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL，数字可以默认0，字符串默认“”。

4.事务的处理优化

5.锁表的优化

InnoDB行锁优化建议：

1.尽可能让所有数据检索都通过索引来完成，避免升级为表级锁定，用explain查看是否使用符合预期的索引，如果不走索引，加的行锁也是表锁。

2.合理设计索引，可以缩小行锁的锁定范围，避免造成不必要的锁定影响其他Query执行

3.尽可能减少基于范围的数据检索过滤条件，避免间隙锁锁定不该锁定的记录

4.控制事务大小，减少锁定的资源量和锁定时间长度

5.尽量使用较低级别的事务隔离

6.系统配置的优化，操作系统的连接数，资源回收，内存设置，慢查询设置，MySQL连接数设置，等等

慢查询默认是关。
连接数。
4.2、thread_cache_size缓存线程 优化
thread_cache_size：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户，而不是销毁(前提是缓存数未达上限)。

即可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。

4,2.1、查询thread_cache_size
— 查询服务器 thread_cache_size 配置
show variables like ‘thread_cache_size’;
4.2.2、设置线程缓存数量 thread_cache_size
如果是短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。

对于长连接，不能保证连接的稳定性，所以设置这参数还是有一定必要，可能连接池的问题，会导致连接数据库的不稳定性，也会出现频繁的创建和销毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在50-100左右。

物理内存设置规则：通过比较Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

7.硬件的优化，服务器硬件，网络带宽等等，运维工程师