MySQL中级

Server version: 5.7.20 MySQL Community Server (GPL)

一、数据库基础知识

（一）范式设计：

1.第一范式：列不可分。

字段信息不能再次拆分。不满足第一范式的表需要对字段进行拆分来达到满足第一范式的要求。
左图不符合第一设计范式；右图通过拆分字段来解决：

2.第二范式：强依赖。

在第一范式的基础上加入新的限制条件，要求实体属性完全依赖于主关键字，一个表主键不能和某个字段对应的两行数据关联起来。出现这种情况需要建立一个中间表来解决这个问题。
该表不满足第二范式设计，一个主键id和两个产品id关联：

中间表：

3.第三范式：表字段无传递性。

满足第三范式的前提是先满足第二范式。第三设计范式要求表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即每个属性都跟主键直接关系而不是间接关系。比如下面订单表设计不符合第三设计范式，因为订单表中的产品名称和表主键是间接关系，而跟产品id是直接关系，因为在产品表中通过产品id也能找到产品名称，这里产生了数据冗余。

产品表：

这里如果产品表中产品id和产品名称发生变化，那么订单表中的产品名称就会和其产生差异。所以这里需要把订单表中的产品名称列取消掉，让订单表中的订单主键id和产品id直接关联，通过产品id在产品表中仍然能够找到产品名称，这样就符合第三设计范式了。

（二）反范式设计：

完全符合范式化设计并不是完美无缺，因为在实际业务场景中会存在大量的表关联查询，而表设计都做成了范式化设计（甚至很高的范式），大量的表关联起来很影响查询的性能。
**反范式化设计就是违反范式化设计**：
1.为了性能和查询效率适当违反数据库范式化设计的要求；
2.为了查询性能，允许存在部分（少量）冗余数据。
换句话来说反范式化设计就是使用空间换取时间。

（三）范式化和反范式对比：

1.范式化的更新操作通常比反范式化要快，因为字段相对少；
2.当数据库较好的范式设计时，就只有很少或没有重复数据，所以只需要修改更少的数据；
3.范式化设计的表通常更小，所以占据的内存较少；
4.范式化设计的缺点是通常需要关联，稍微复杂的查询语句在符合范式设计的表上都可能需要至少一次关联，也许更多；
5.复杂一些的查询语句可能使一些索引无效。例如，范式化设计可能将字段放在不同的表中，而这些列如果在同一张表中本可以共用同一个索引。

（四）字段数据类型优化：

1.优化基本原则：

①更小的通常更好：

使用可以正确存储数据的最小数据类型，通常更快，因为占用更少的磁盘、内存和CPU高速缓存，并且处理时需要的CPU周期也更少。比如有一个数据既可以用字符串存储也可以用整型存储，此时优先选择整型。因为字符串会涉及到字符集和校验规则。

②简单就好：

简单数据类型的操作通常需要更少的CPU周期。例如，由于字符串涉及到字符集和校验规则的原因导致使用字符串的代价更高。因此应该使用MySQL内建类型而不是字符串来存储日期和时间。

③尽量避免null：

建表时最好指定列为NOT NULL，除非真的需要存储null值。如果查询中包含可为NULL值的列，对MySQL来说更难优化，因为可为NULL值的列使得索引、索引统计和值比较都更加复杂。而且可为NULL值的列占用的存储空间也会更多，在MySQL里面需要特殊处理。可为NULL值的列被索引时，每个索引记录需要额外的一个字节来存储。
把可为NULL值的列改为NOT NULL带来的性能提升比较小，所以调优时没必要首先在现有的schema中查找并改掉这种情况，除非确定这会导致问题。但是如果计划在列上建索引，就应该尽量避免将该列设计成可为NULL值的列。

2.整数类型：

存储整数可以使用的几种数据类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别占用8/16/24/64位存储空间，也就是1/2//3/4/8个字节。
同时整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255，而TINYINT的存储范围是-128-127。无符号和有符号类型使用相同的存储空间，并且性能相同，因此可以根据实际情况选择合适的的类型。另外integer和int存储及大小没有任何差别，只是为了业务上区分。

MySQL可以为整数类型指定宽度，例如INT(11),但是对大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

备注：MySQL中没有long型，对应只有bigint型。当数据量很大时，既需要保证数据存储的精度、又需要保证数据存储的效率时只能选择整型的bigint型，因为实数类型的float、double保证不了精度，而decimal又保证不了效率，此时只能选择bigint型。

3.实数类型：

实数类型：FLOAT、DOUBLE、DECIMAL。
实数是带有小数部分的数字。MySQL既支持精确类型的存储DECIMAL类型，也支持不精确数据类型的存储比如FLOAT类型和DOUBLE（浮点类型）。DECIMAL类型用于存储精确的小数，本质上MySQL是以字符串形式存储这种数据类型。所以CPU不支持对DECIMAL的直接计算，只是在MySQL中自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点数计算，所以浮点运算明显更快。
浮点类型在存储同样范围的的值时，通常比DECIMAL使用更少的空间。FLOAT占用4个字节，DOUBLE占用8个字节，DECIMAL里面存储65个数字，DECIMAL对于列的空间消耗比较大，另外DOUBLE比FLOAT有更高的精度和更大的范围。

①如何选择数据类型？

对精度要求不高并且需要快速运算的时候，选择FLOAT和DOUBLE。应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储银行或金融数据。
在数据量比较大而且对精度要求比较高的情况下，可以考虑使用BIGINT代码DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

4.字符串类型：

MySQL支持多种字符串类型，包括：CHAR、VARCHAR、BLOB、TEXT、ENUM（枚举）、SET。
VARCHAR和CHAR是两种最主要的字符串数据类型。

①VARCHAR:

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。在内部实现上，既然是变长，VARCHAR需要使用1或2个额外字节来记录字符串长度，如果列的最大长度小于等于255字节，则使用一个字节表示，否则使用两个字节表示。
VARCHAR节省了存储空间，所以对性能有所帮助。但是由于行是变长的，在UPDATE时新值比旧值长时，使行变得比原来更长，此时需要做额外的工作。如果一个行占用的空间增长，并且在页内没有足够的空间可以存储，在这种情况下，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页使行可以放进页内。

②CHAR:

CHAR是定长的，MySQL总会根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格，CHAR值会根据需要采用空格进行填充以方便比较。

③CHAR和VARCHAR如何选择？

字符串列的最大长度比平均长度大很多，列的更新很少；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
CHAR适合存储很短的字符串，或者所有值定长或都接近同一个长度。例如CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR要比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率，例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。
另外，使用VARCHAR(5)和VARCHAR(200)存储"hello"在磁盘上的开销是一样的，不过此时我们应该选择更短的列，因为它的内存消耗更少。

④BLOB和TEXT类型：

BLOB和TEXT都是为存储大数据而设计的字符串数据类型，分别采用二进制和字符方式存储，前者没有字符集和排序规则，而后者有，这是它们的区别。
与其他类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象来处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会采用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部区域存储实际的值。
使用BLOB和TEXT要慎重：
(a)BLOB和TEXT会引起一些性能问题，所以应该尽量避免使用BLOB和TEXT类型；
(b)一定要用，建议把BLOB和TEXT分离到单独的表中；
(c)在不必要的时候避免检索大型的BLOB和TEXT值。例如，select * 查询就不是很好的选择，除非能够确定作为约束条件的WHERE子句后只会找到所需要的数据行。否则，很可能会导致在网络上传输大量无意义的值。建议搜索索引列，决定出需要的数据行，然后从符合条件的数据行中检索BLOB和TEXT值；
(d)可以使用合成的(Synthetic)索引来提高大文本字段(BLOB或TEXT)的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过三列值找到数据行了。但是，这种技术只能用于精确匹配的查询（散列值对于类似“<或“>=”等范围搜索操作符是没有用处的）。可以使用MD5函数生成散列值，也可以使用SHA1（或CRC32），或者使用自己的应用程序逻辑来计算散列值。

5.枚举类型：

如果表中的字段取值是固定的几个字符串，可以使用枚举列代替常用的字符串类型。枚举列会把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中，MySQL在内部会将每个值在列表中位置保存为整数，这样的话可以让表的大小大为缩小。

CREATE TABLE
enum_test(e ENUM(' fish', 'apple', 'dog') NOT NULL);

INSERT INTO
enum_test(e) VALUES(1),(2),(3);

但是要注意：
①因为枚举列实际存储为整数，而不是字符串，所以不要使用数字作为ENUM枚举常量，因为这种双重性很容易导致混乱，例如ENUM('1','2','3')。
②枚举字段是按照内部存储的整数而不是定义的字符串进行排序的，所以尽量按照需要的顺序来定义枚举列。

6.日期和时间类型：

MySQL可以使用许多类型来保存时间和日期值，例如YEAR、DATE、DATETIME、TIMESTAMP。MySQL能够存储的最小时间单位为秒。
datetime存储日期范围：1001~9999；
timestamp存储日期范围：1970~2038,并且跟时区有关系。
如果需要存储比秒更小粒度的日期和时间值，由于MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用BIGINT类型存储微妙级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

（五）命名规范：

1.推荐命名规范：

（1）可读性原则；

（2）必须使用小写字母或数字；

（3）不适用复数名词；

（4）禁用保留字；

（5）索引命名。

（六）索引：

1.官方定义：

使得MySQL高效获取数据的数据结构。其本质是数据结构，作用就是高效获取数据。

2.B+树索引：比较关键

3.全文索引：

4.哈希索引（内部）：较少使用

范围查找时效率较低：因为它适用于根据哈希值精确查找。
不支持排序：因为不同的哈希值分布在不同的哈希槽上面。
不支持组合索引：当存在哈希索引时，会将组合索引中涉及到的字段变成一个哈希值，没办法实现部分字段的组合。
存在哈希冲突：当数据量比较大时，哈希冲突的概率会增加。哈希冲突的概率越高，数据查询的效率越低。可以通过单链表的方式解决哈希冲突问题。

（七）Tree：

1.树：

树(Tree):N个节点构成的有限集合。
树中有一个称为“根(Root)”的特殊节点。同一级节点之间不能连接，否则不能称为树。
其余节点可分为M个互不相交的树，称为原来节点的子树。每个节点有两个分叉的称为二叉树。

2.二叉树：

基于树结构形成的只有两个分叉的树形数据结构。

3.二叉查找树：

（1）首先是一颗二叉树，即每个节点有且只有两个分叉。
（2）左子树节点上所有的值小于根节点上的值；
（3）右子树节点的所有的值大于或等于根节点上的值。

4.AVL平衡二叉树：

（1）前提是它得是二叉查找树。
（2）左右两个子树的高度差（平衡因子）的绝对值不能超过1；
（3）左右两个子树都是一颗平衡二叉树。
（4）满足以上条件的目的：使得树的高度最低，因为树查找的效率决定于树的高度。二叉查找树的“二叉”是基于二分查找算法实现数据的搜索，不像单链表是基于顺序查找实现数据搜索，如果树的高度很高只有一个分支就变成单链表。顺序查找算法不管从平均查找次数还是最坏查找次数角度来说效率都不如二分查找算法。平衡二叉树也叫做平衡二叉查找树，因为它是基于二叉查找树实现的，平衡二叉树的高度越低意味着二分查找数据时查找的次数越少，查找次数越少自然意味着查找效率或性能更高。

5.B-Tree:

（1）非叶子节点既存储索引又存储实际数据，而且叶子节点没有链接在一起，这是和B+Tree的不同之处。

6.B+Tree：多叉平衡查找树

（1）相对平衡二叉树来说，B+Tree有多个分支，这是和平衡二叉树只有两个分支的不同之处；
（2）B+Tree数据结构仍然满足左子树上的每个节点值小于根节点值，右子树上的节点值大于等于根节点值；
（3）标准的B+Tree的叶子节点是通过单向链表进行链接，但是MySQL的InnoDB在实现数据存储引擎时稍微做了些优化，叶子节点的链接使用的是双向链表进行链接；
（4）非叶子节点存储索引，叶子结点存储实际数据；
（5）InnoDB存储引擎使用的数据结构是B+Tree。

7.B*树：

（1）与B+Tree的不同之处：非叶子节点之间也有指针指向；
（2）Oracle数据库使用B*树这种数据结构。

8.为什么使用B+Tree而不使用B-Tree？

（1）因为B树每个节点除了存储数据之外还存储索引信息，当每次查询大小固定的数据时，B树返回的目标数据就会变少，需要更多次数的查询才能获取全部想要的数据，所以相同规模数据的情况下使用B树这种数据结构会导致io次数增加；而B+Tree的数据都集中存放在叶子节点，相对B树来说每次查询可以返回更多条数据记录，io次数自然也就减少了，减少io次数性能自然也就提升了；
（2）范围查询时B+Tree的效率明显优于B树。因为B+Tree的叶子节点上存储的数据都是按照一定的顺序进行存储，查询速度会很快。

二、MySQL中的索引：

（一）B+Tree索引：

1.主键索引/聚集索引/聚簇索引：

在MySQL中，主键索引、聚集索引、聚簇索引都是相同的概念，它们是和主键关联的一种索引，除此之外的索引都是二级索引，二级索引也叫辅助索引。该类型根据表的主键构建一颗B+Tree，并且将整张表的行记录数据存放在B+Tree叶子节点中。

2.辅助索引/二级索引：

辅助索引和二级索引是同一类型索引只是叫法不一样。通过辅助索引获得主键，在通过主键索引（聚集索引）找到一个完整的行记录，这个过程叫做回表。

3.组合索引/复合索引/联合索引：

组合索引、复合索引、联合索引是相同概念的索引，它是将表中的多个列组合起来进行索引。

4.覆盖索引：

覆盖索引不是真正的索引，它只是一种思想。从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

（二）自适应一致性哈希索引：

1.InnoDB存储引擎中除了前面说的各种索引外，还有一种自适应哈希索引，我么知道B+Tree的查询次数取决于B+Tree的高度，在生产环境中，B+Tree的高度一般为3、4层，故需要3、4次的IO查询。
2.在InnoDB存储引擎内部会自动监控索引表，如果监控到某个索引经常使用，那么就认为这个索引对应的数据是热数据，然后InnoDB存储引擎内部会自动为这些热点数据创建一个hash索引，这个索引称之为自适应哈希索引。创建索引之后，下次再查询到这个索引，可以直接通过hash算法推导出记录的地址，直接一次性就能查询到数据，比重复去B+Tree中查询三四次节点的效率会高出不少。
3.自适应哈希索引是数据库自动创建的，不能进行人工人为干预。在MySQL5.7（包括该版本）之后默认开启自动创建自适应哈希索引。

（三）全文索引：

1.全文检索(Full-Text Search)：

（1）它是将存储在数据库中的整本书或整篇文章中的任意内容信息查找出来的一种技术。它可以根据需要获取全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。我们比较熟悉的Elasticsearch、Solr等就是全文检索引擎，底层都是基于Apache Lucene的。比如，我们要保存如下唐诗宋词，数据库中应该如何设计？
|朝代|作者|诗词年代|标题|诗词全文|
|唐|李白||…|静夜思|床前明月光，疑是地上霜。 举头望明月，低头思故乡。|
|宋|李清照|…|如梦令|常记溪亭日暮，沉醉不知归路，兴尽晚回舟，误入藕花深处。争渡，争渡，惊起一滩鸥鹭。|
|…|…|…|…|…|
要根据朝代或者作者寻找诗，都很简单，比如“‘select 诗词全文 from 诗词表 where 作者=‘李白’”，如果数据很多查询速度很慢，我们可以在对应的查询字段上建立索引加快查询速度。
（2）但是如果现在有个需求：要求找到包含“望”字的诗词怎么办？
用“‘select 诗词全文 from 诗词表 where 诗词全文 like ‘%望%’”,这意味着要扫描数据库中的全文字段，逐条比对，找出所有包含“望”字关键字的记录。基本上，数据库中一般的SQL优化手段都是用不上的。数据量少大概率还可以接受，如果数据量稍微有点大，就完全接受不了，更何况互联网环境下基本上都是大数据量的数据。可以用倒排索引解决这个问题。

2.倒排索引：

（1）倒排索引就是，将文档中的关键字全部提取处理，然后将关键字和文档的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字时，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到包含关键字的文档。于是我们可以按如下方式保存数据：
|序号|关键字|蜀道难|静夜思|春台望|鹤冲天|
|1|望|有|有|有|有|
如果查哪首诗词中包含“上”字，那么可以在上述表格中填入新的记录：
|序号|关键字|蜀道难|静夜思|春台望|鹤冲天|
|1|望|有|有|有|有|
|2|上|有|||有|
（2）从InnoDB1.2.x版本开始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列。不过MySQL在设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索的支持并不友好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符(delimiter)的语言，如中文、日语、韩语等。所以MySQL中的全文检索功能比较弱鸡，了解即可。

（四）索引在查询中的使用：

索引的作用：
1.一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度；
2.一个select查询语句在执行过程中最多能使用一个二级索引，即使在where条件中用了多个二级索引。

（五）高性能的索引创建策略：

1.索引列的类型尽量小：

（1）我们在定义表结构时要显示指定列的类型，以整数类型为例，有ITNYINT、SMALINT、MEDUMINT、INT、BIGINT这几种，它们占用的存储空间依次递增。这里所说的类型大小指的是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如果我们想要对整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的数据类型，比如能使用INT就不要使用BIGINT，能使用MEDUMINT就不要使用INT，这是因为数据类型越小，在查询时进行的比较操作越快（CPU层面），而且数据类型越小占用的内存空间越小，在一个数据页内就能存放更多条数据记录，从而减少磁盘IO带来的性能损耗，也就是说可以把更多的数据页缓存到内存中，从而加快读写效率。
（2）上面的建议对于表的主键来说更加适用，因为不仅聚簇索引中会存放主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，所以如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的IO。

2.索引的选择性计算(选择哪个字段建立索引)：

（1）创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指，表中列所对应的不重复的索引值（也称为基数，cardinality）和数据表的记录总数(N)的比值，取值范围是[1/N,1]。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
（2）很差的索引选择性就是列中的数据重复度很高，比如性别字段，只有男或女。那么我们在查询时，即使使用这个索引，从概率的角度来说，也能查出一半的数据出来。

创建表：

CREATE TABLE `person` (
  `name` varchar(255) DEFAULT NULL,
  `sex` char(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `area` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（3）从选择性计算结果可以看出，当然是姓名字段的离散度最高，因为该字段里面的数据重复度最低，没有任何的重复数据；而性别字段则相反，重复度非常高，故不适合用来建立索引。
计算选择性/离散性（根据计算结果决定选择用哪个字段建立索引）：

SELECT 
count(DISTINCT name)/count(*) 姓名,
count(DISTINCT sex)/count(*) 性别,
count(DISTINCT age)/count(*) 年龄,
count(DISTINCT area)/count(*) 城市 
FROM person;

3.前缀索引：

针对blob、text、很长的varchar字段，MySQL不支持索引他们的全部长度，需建立前缀索引。

（1）语法：

alter table tableName add key/index (column(X));

（2）缺点：

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点，因为MySQL无法使用前缀索引做group by和order by,也无法使用前缀索引做覆盖扫描。

（3）后缀索引：

后缀索引(suffix index)有时候也有用途(例如，找到某个域名的所有电子邮件地址)。原生MySQL并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器或应用程序自行处理来维护索引。

（4）找合适前缀长度：

可以看出，从第10个开始选择性的增加值很高，随着前缀字符的越来越多，选择度也在不断上升，但增长到第15时，选择性值的增加开始变慢和第14没有多大差别了，已经很接近整个列的选择性了。因此，针对这个字段做前缀索引的话，从第13到第15都是不错的选择。因此找到了合适的前缀长度，取中间值14作为前缀长度。

①创建表：

CREATE TABLE `order_exp` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint(22) NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint(6) NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

②计算离散性/选择性：

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

（5）创建前缀索引：

alter table order_exp add key(order_note(14));

（6）测试：

建立前缀索引后查询语句并不需要修改：经过测试建立前缀索引后查询速度快0.024s-0.020s=0.004秒。

select * from order_exp where order_note = '你好，李焕英。7排23号,过期时长:DD00_23S';

4.只为搜索、排序、分组的列创建索引：

也就是说，只为出现在where子句中的列、连接子句中的列创建索引，而出现在查询列表中的列一般没必要建立索引，除非是需要使用覆盖索引；又或者为出现在order by或group by子句中的列创建索引。具体来说如下：

（1）搜索：只为条件中的列建立索引即可

select order_note from .... and ....;

（2）排序：

select * from order_exp order by insert_time,order_status,expire_time;

查询出来的结果集需要按照insert_time值排序，如果记录的insert_time值相同，则需要按照order_status来排序，如果order_status值相同，则按照expire_time排序。回顾一下联合索引的存储结构，u_idx_day_status索引本省就是按照上述规则排序的，所以直接从索引中提取数据，然后进行回表查询操作就可以取出该索引中不包含的列就好了。

5.多列索引：

（1）多列索引并不是要为表的每个列都创建索引或者按照错误的顺序创建多列索引。正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。在一个多列B+Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的order by或group by和distinct等子句的查询需求。
（2）综上，索引列的顺序至关重要。对于索引列的顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性高的列放在前面通常是很好的，这时候索引的作用就是优化where条件的查找。在这种情况下，这样设计的索引能够最快的过滤出需要的行，对于在where子句中只使用了索引部分索引的前缀查询来说选择性也更高。
（3）然而，性能不只是依赖于索引列的选择性，也和查询条件相关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情况下索引的选择性最高。同时，在性能优化的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

6.三星索引：

（1）概念：

对于一个查询来说，一个三星索引，可能是最好的索引。满足的条件如下：

①索引将相关的记录放到一起则获得一星（比重27%）；
②如果索引中的字段顺序和查找数据时用到的字段顺序一致则获得二星（比重23%）；
③如果索引中的列包含查询中的全部列则获得三星（宽索引星）（比重50%）。
其中，第三颗星最重要，因为在查询时如果有一个字段不包含在索引里面可能会导致很多磁盘随机读（回表操作）。第一颗星（27%）和第二颗星23%）的重要性差不多，所以在大部分情况下，会先考虑第一颗星，但是需要根据业务情况调整这两颗星的优先度。

（2）一星：索引将相关的记录放到一起则获得一星。

如果一个查询相关的索引行是相邻的或者至少相距足够靠近的话，必须扫描的索引片的宽度就会缩短至最短，也就是说，让索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好。

（3）二星（排序星）：索引中的数据顺序和查找顺序一致则获得二星。

在满足一星的情况下，当查询需要排序(order by)、分组(group by)，如果查询字段的顺序和索引字段的顺序一致（索引本身是有序的），就可以不用再排序了，一般来说排序是影响性能的关键因素。

（4）三星（宽索引星）：索引中的列包含查询中的全部列则获得三星。

在满足了二星的情况下，如果索引列包含了这个查询中所需要的所有列（包括where子句和select子句中所需要的列，也就是覆盖索引），这样一来查询就不再需要再回表了，减少了查询步骤和IO请求次数，性能几乎可以提升一倍。

7.设计三星索引实战：

（1）实战一：

①SQL：

-- 创建表：
CREATE TABLE customer (
	cno INT,
	lname VARCHAR (10),
	fname VARCHAR (10),
	sex INT,
	weight INT,
	city VARCHAR (10)
);
-- 建立索引：
CREATE INDEX idx_cust ON customer (city, lname, fname, cno);
-- 查询：
select cno,fname from customer where lname=’xx’ and city =’yy’ order by fname;

②评估：

第一颗星：所有等值谓词的列（lname和city）是相邻的，靠的很近，符合条件；
第二颗星：order by的fname字段包含在组合索引中且是自动排序好的，符合；
第三颗星：建索引用到的字段(city, lname, fname, cno)包含查询中用到的所有字段(cno,fname)，符合。

（2）实战二：

①SQL：

-- 创建表：
CREATE TABLE `test` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`user_name` VARCHAR (100) DEFAULT NULL,
	`sex` INT (11) DEFAULT NULL,
	`age` INT (11) DEFAULT NULL,
	`c_date` datetime DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 12 DEFAULT CHARSET = utf8;
-- 建立索引：
create index idx_cust_test on test(user_name,sex,age);
-- 查询：
select
   user_name,
	 sex,
	 age
from
  test
where
  user_name like '马%'
and sex = 0
order by
  age;

②分析：

第一颗星和第三颗星满足条件；
第二颗星不满足条件，因为中间有过滤条件，不能保证后面有序。

③重新建立索引：

-- 建立索引：
create index idx_cust_test2 on test(sex,age,user_name);

④再分析：

第一颗星：不满足，只可以匹配到sex，而且sex的匹配性很差，意味着这是一个宽索引片；同时因为age也会导致排序问题选择的碎片问题。查询不是连续的；
第二颗星：满足，等值sex的情况下，age是有序的；
第三颗星：满足，select查询的列都包含在索引中。

三、MySQL调优：

（一）MySQL调优金字塔：

1.硬件和操作系统层面的优化：

（1）硬件层面：

影响MySQL性能的因素有：CPU、可用内存大小、磁盘读写速度、网络带宽；

（2）操作系统层面：

应用文件的句柄数、操作系统的网络配置都会影响MySQL的性能。这部分的优化主要由运维工程师去完成。
在硬件基础资源优化的过程中，我们应该重点关注服务本省承载的体量，然后提出合理的指标，避免出现资源浪费。

2.架构设计层面的优化：

MySQL是一个磁盘IO非常频繁的关系型数据库。在高并发和高性能的场景中，MySQL必然会承受巨大的并发压力，而此时我们的优化方式可以分为几个部分：

（1）搭建MySQL主从集群：

单个MySQL服务容易单点故障，一旦服务器宕机就会导致依赖MySQL数据库的应用全部无法响应。主从集群或者主主机群可以保证服务的高可用性。

（2）读写分离设计：

在读多写少的场景中，通过读写分离设计方案，可以避免读写冲突带来的性能影响。

（3）引入分库分表设计：

分库可以降低单个服务器节点的IO压力；分表可以降低单张表的数据量，从而提升SQL查询效率。

（4）针对热点数据：

针对热点数据，可以引入更为高效的非关系型数据库，比如Redis、mogoDB等，它们可以很好的缓解MySQL的访问压力，同时提升数据检索性能。

3.MySQL程序配置优化：

（1）my.cnf:

MySQL是一个经过互联网大厂验证过的生产级别的成熟数据库，对于MySQL数据库本身的优化，一般是通过MySQL中的配置文件my.cnf来完成的，比如：

①MySQL5.7版本默认的最大连接数是151个，这个值可以在my.cnf中修改；

②binlog日志默认是不开启；

③缓存池buffpool的默认大小配置。

由于这些配置一般都和用户安装的硬件环境以及使用场景有关系，因此这些配置官方只会提供一个默认值，具体情况还得由使用者来修改。

（2）配置项的修改：

关于配置项的修改，需要关注两个方面：

①配置的作用域分为会话级别和全局；

②是否支持热加载。

因此，针对这两个点，我们需要注意的是：
A.全局参数的设定对于已经存在的会话无法生效；
B.会话参数的设定会随着会话的销毁而失效；
C.全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效。

4.SQL优化：

SQL优化演示用到的表：
s1:

CREATE TABLE `s1` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint(22) NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint(6) NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_insert_time` (`insert_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=111253 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

s2:

CREATE TABLE `s2` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint(22) NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint(6) NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

order_exp:

CREATE TABLE `order_exp` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint(22) NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint(6) NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

order_exp_cut:

CREATE TABLE `order_exp_cut` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) DEFAULT NULL COMMENT '订单的编号',
  `order_note` varchar(100) NOT NULL COMMENT '订单的说明',
  `insert_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint(22) NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint(6) NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

（1）SQL优化三部曲：

①慢SQL的定位和排查：

通过慢查询日志和慢查询日志分析工具得到有问题的SQL列表。

（a）慢查询日志：

MySQL记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。

-- 慢查询日志的开关：默认情况下是关闭状态
show variables like '%slow_query_log%';
-- 查看慢查询日志文件的存放路径：
show variables like '%slow%';
-- 查看慢查询日志设定的时间阈值：
show variables like '%long_query_time%';
-- 开启慢查询：ON->1
set global slow_query_log = 1;
-- 关闭慢查询：OFF->0
set global slow_query_log = 0;

-- 通过在mysql数据库中创建表来解决报错：1146 - Table 'mysql.slow_log' doesn't exist
desc slow_log;
CREATE TABLE `slow_log` (
    `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `user_host` mediumtext NOT NULL,
    `query_time` time NOT NULL,
    `lock_time` time NOT NULL,
    `rows_sent` int(11) NOT NULL,
    `rows_examined` int(11) NOT NULL,
    `db` varchar(512) NOT NULL,
    `last_insert_id` int(11) NOT NULL,
    `insert_id` int(11) NOT NULL,
    `server_id` int(10) unsigned NOT NULL,
    `sql_text` mediumtext NOT NULL
    ) 
ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='just only a slow log';

-- 查询日志时是否启用索引：
show variables like '%log_queries_not_using_indexes%';
-- 查询日志时启用索引：ON->1
set global log_queries_not_using_indexes = 1;
-- 查询日志时不启用索引：OFF->0
set global log_queries_not_using_indexes = 0;

-- Linux中mysql命令行中通过mysqldumpslow命令分析慢查询日志：
mysqldumpslow -s(排序) -r(以获得的结果的行数排序) -t(总时间) 10(只展示前面的10条) centosvm-slow.log;

②执行计划分析:

针对慢SQL，通过explain关键字查看当前SQL的执行计划，重点关注type、key、rows、filtered等字段，从而定位SQL执行慢的根本原因，再有的放矢的进行优化。

(a)id:explain之后一个select对应一个id。

(b)select_type：查询类型；

通过某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

explain select * from s1 where id in (select id from s2) or order_no = 'a';

explain select * from s1 where id in (select id from s2 where id = 123 union select id from s1 where id = 321);

©table：从哪张表查数据；

(d)partitins：和分区表有关；

(e)type：访问类型；

表示mysql对某个表执行查询时的访问方法或者访问类型，这个结果值是衡量查询性能好坏的一个重要的指标，ALL是全表扫描；SYSTEM主要针对系统表，如果一般查询能达到这个级别则说明SQL性能很好。结果值从最好到最坏依次是：

EXPLAIN SELECT * FROM s1 WHERE id IN ( SELECT id FROM s2 );
-- id：两条查询记录的id都是1。查询过程中，mysql查询优化器会将SQL1优化成SQL2，按照SQL2执行就只有一个id。
-- SQL1：SELECT * FROM s1 WHERE id IN ( SELECT id FROM s2 );
-- SQL2：SELECT	s1.* FROM	s1,	s2 WHERE	s1.id = s2.id;

explain select * from s1,s2 where s1.id=s2.id;

EXPLAIN SELECT * FROM s1 WHERE id IN ( SELECT id FROM s2 ) OR s1.order_note = 'a';
-- 此时优化器做不了优化，所以有两个不同id。

EXPLAIN SELECT	* FROM	s1 UNION SELECT	* FROM	s2;
-- 此时会出现三行记录，其中有两个不同id，另一个id为空。出现为空的id是因为使用union来进行联合查询，需要用到临时表来去重。

EXPLAIN SELECT	* FROM	s1 UNION ALL SELECT	* FROM	s2;
-- 如果使用union all替换union之后，就会少一条id为空的记录，因为union all不需要使用临时表。

explain select * from s1 where order_no = 'a';

explain select * from s1 inner join s2;

explain select * from s1 union select * from s2;

explain select * from s1 union select * from s2 union select * from order_exp;

explain select * from s1 where id = 716;

explain select * from order_exp_cut where order_no = 'abc' or order_no is null;

备注：因为order_no字段可以为空，所以需要额外一个字节来进行存储，又因为varchar类型变长它需要额外2个字节来存储，所以总长度是：50x3+1+2=153。如果让order_no字段非空，它的总长度就会少一个，为：50x3+2=152，如下图所示：

explain select * from order_exp where order_no = 'abc' or order_no is null;

explain select * from s1 where order_no = 'a' or insert_time = '2021-03-22 18:37:13';

explain select * from s1 where id in (select id from s2 where s1.insert_time = s2.insert_time) or order_no = 'a';

范围查询时的查询条件字段如果建立了索引，通常情况下访问类型即为range:

explain select * from s1 where order_no in ('a','b','c');
explain select * from s1 where order_no > 'a' and order_no < 'b';

explain select insert_time,order_status,expire_time from s1 where expire_time = '2021-03-22 18:37:13';

(f)possible_keys：查询可能会用到的索引；

(g)key：实际使用的索引；

(h)key_len：实际使用的索引长度，用来分析索引使用了几个字段；

(i)ref：和type进行关联，原来的type列是什么类型，那么ref列就是什么类型；

explain select * from s1 where order_no = 'a';

因为查询条件’a’是一个常量，所以ref列的值为const；因为是等值查询，所以type类型为ref,如果是范围查询则type列的值为range；如果是关联查询，则type列的值为eq_ref。

(j)rows：在表中查数据时扫描到的行数；

(k)filtered：数据库表中扫描到的数据中符合查询条件的数据的大概百分比；

(l)Extra：额外的描述信息，比如提示使用到索引或哪个关键字等。

③使用show profile工具：

show profile工具是MySQL提供的用来分析当前会话中SQL语句资源消耗情况的工具，可用于SQL调优的测量。在当前会话中，show profile默认情况下是关闭的，打开之后保存最近15次的运行结果。针对慢SQL，使用profile工具进行详细分析，可以得到SQL执行过程中的所有资源开销情况，如IO开销、CPU开销、内存开销等。

（2）SQL优化规则：

①SQL查询一定要基于索引进行数据扫描；

响应时间=服务时间+排队时间；
尽量使用覆盖索引；

②索引列上避免使用函数或者运算（表达式），否则会导致索引失效；

explain select * from order_exp where id+1=17; -- id=16

explain select * from order_exp where id=16;

explain select * from order_exp where year(insert_time)=year(date_sub(now(),interval 1 year));

下面两条SQL查询功能相同，只是写法不同，但是索引不会失效：

explain select * from order_exp where year(insert_time)=year(date_sub(now(),interval 1 year));

explain select insert_time,order_status,expire_time from order_exp where insert_time between str_to_date('01/01/2021','%m/%d/%Y%') and str_to_date('12/31/2021','%m/%d/%Y%');

③where子句中的like %尽量放右边：范围查询放右边；

查询条件中如果包含范围查询，索引只对最前面的范围查询生效。索引对第一个范围查询往后的所有条件都不会生效，因为根据B+树的特点，第一次使用了范围查找，就不能保证后面的查找是有序的。比如下面的SQL在执行过程中，联合索引中的列只有insert_time(长度0)、order_status(长度6，加上变长需要用两个长度来存)前两个字段使用上了索引，后面的字段对索引没有生效。所以索引总长度key_len:6个长度+2个长度=8个长度。

explain select * from order_exp_cut where insert_time = '2021-03-22 18:34:55' and order_status > -1 and expire_time > '2021-03-22 18:35:00' and expire_time < '2021-03-22 18:35:19';

④使用索引扫描，联合索引中的列，从左往右命中的越多越好，尽量全值匹配：最佳左前缀法则

如果需要针对某个字段进行查询，可以单独对这个字段建立索引即可。

explain select * from order_exp where insert_time = '2021-03-22 18:34:55' and order_status = 0 and expire_time = '2021-03-22 18:35:19';

explain select * from order_exp where insert_time = '2021-03-22 18:34:55' and expire_time = '2021-03-22 18:35:19';

explain select * from order_exp_cut where insert_time = '2021-03-22 18:34:55' and order_status = 0 and expire_time > '2021-03-22 18:35:00' and expire_time < '2021-03-22 18:35:19';

⑤尽量使用SQL语句中用到的索引完成排序，避免使用文件排序的方式：排序列尽量不使用不包含在同一个索引的字段排序，否则走文件排序。

假设一个查询的排序字段中包含多个字段，如果多个字段没有包含在同一个联合索引而是每个字段都有自己的单独索引，那么这个查询不会走索引扫描而是全表扫描，因为按照B+树的特性，只有属于同一个索引那么查找数据才是有序的（因为对同一个索引来说本身整个B+树就是有序的），在数据查找过程中是顺序IO过程。如果不属于同一个索引，对mysql读取数据来说是随机IO过程，因为要从不同索引对应的B+树结构中找数据，而不同的索引对应的B+树结构又是相互独立没有关联性，所以这个时候的数据查找只能走全表扫描。例如：

explain select * from order_exp order by order_no,insert_time;

⑥查询有效的列信息，避免使用*代替列信息（尽量使用覆盖索引）；

用*会查询所有的字段值信息，如果查询条件不是通过主键来查找数据或者使用的是建立辅助索引的字段来查找数据，那么当需要查找出值字段中如果存在字段没有建立索引，那么就需要通过辅助索引找到主键索引，然后找到主键索引对应的B+树，从而找到一条完整的记录，这个过程是通过回表来查找到目标数据，数据检索性能较差。
如果要查询的字段刚好是建立联合索引的字段，那么查询就会走覆盖索引，效率较高。

EXPLAIN SELECT insert_time, order_status, expire_time FROM order_exp_cut WHERE insert_time = '2021-03-22 18:34:55' AND order_status = 0 AND expire_time > '2021-03-22 18:35:00' 
AND expire_time < '2021-03-22 18:35:19';

⑦永远小结果集驱动大结果集；

⑧尽量全值匹配。

explain select * from order_exp where insert_time = '2021-03-22 18:34:55' and order_status = 0 and expire_time = '2021-03-22 18:35:19';

⑨不等于要慎用：不等于可以用范围查询替代。

使用不等于(!=或者<>)的时候索引不会生效，此时查询走全表扫描。

⑩NULL和NOT有影响：

order_exp表中order_no字段非空；order_exp_cut表中字段可为空。两张表中的order_no字段都建立了辅助索引。

explain select * from order_exp where order_no is null; -- 全表扫描
explain select * from order_exp where order_no is not null; -- 全表扫描
explain select * from order_exp_cut where order_no is null; -- 使用索引扫描
explain select * from order_exp_cut where order_no is not null; -- 全表扫描

-- insert_time是联合索引中的字段，该字段在order_exp表中非空、在order_exp_cut表中可为空
explain select order_status,expire_time from order_exp where insert_time is null; -- 使用联合索引，访问类型是等值ref
explain select order_status,expire_time from order_exp where insert_time is not null; -- 使用联合索引，访问类型是index
explain select order_status,expire_time from order_exp_cut where insert_time is null; -- 使用联合索引，访问类型是等值ref查询
explain select order_status,expire_time from order_exp_cut where insert_time is not null; -- 使用联合索引，访问类型是range范围查询

字符串类型查询时记得加引号：

-- order_no是varchar类型，字段上建立了辅助索引，如果查询时不加引号，查询优化器在进行优化时会自动在该索引列上加函数将整型处理成字符串类型，因为索引列上使用了函数，所以此时这个字段上的索引在查询时会因为失效而走全表扫描
explain select * from order_exp where order_no = 6;
explain select * from order_exp where order_no = '6';

使用OR关键字连接查询条件：查询时尽量使用有效字段替代*，让查询走覆盖索引。

explain select * from order_exp where order_no = 'DD00_6S' OR order_no = 'DD00_9S';

explain select * from order_exp where expire_time = '2021-03-22 18:34:55' OR order_no = 'DD00_6S';

explain select * from order_exp where expire_time = '2021-03-22 18:34:55' OR order_note = 'abc';

a.给字段建立索引后重新查询，发现查询会走索引（合并）：

create index idx_order_note on order_exp(order_note);
explain select * from order_exp where expire_time = '2021-03-22 18:34:55' OR order_note = 'abc';

b.改造SQL：在order_note字段上没有建立索引的前提下，如下两个SQL同等作用，不过使用union all进行查询时有一部分查询会走索引扫描。

explain select * from order_exp where expire_time = '2021-03-22 18:34:55' OR order_note = 'abc';
explain select * from order_exp where expire_time = '2021-03-22 18:34:55' union all select * from order_exp where order_note = 'abc';

尽量按主键顺序插入行：因为按照B+树的特点只有这样才能保证顺序IO，随机IO代价太大。

优化count查询：

使用count进行行数统计时，如果某个列里面存在空值，当根据该字段进行统计时值为空的这一样不会计入总行数，查询统计不到改行。

优化limit查询：

-- SQL1：全表扫描
explain select * from order_exp limit 10000,100;
-- SQL2：优化了SQL1。使用了主键索引扫描、全表扫描、二级索引扫描，性能不是最好的因为使用了全表扫描。
explain select * from (select id from order_exp limit 10000,100) b,order_exp a where a.id = b.id;
-- SQL3：使用主键查询，访问类型是range。
explain select * from order_exp where id > 10000 limit 10000,100;

关于null的特别说明：

mysql认为null是一个不确定的值，它代表没有。

select 1=1; --查询结果：1
select 0=0; --查询结果：1
select null=null; --查询结果：null
select null!=null; --查询结果：null

（二）查询优化器：

四、事务和事务隔离级别

事务是一个不可分割的数据库操作序列，也是数据库并发控制基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

（一）事物及事物特性：

1.事务特性：

（1）原子性（atomicity）:

一个事务中涉及到的操作，要么全部成功、要么全部失败。在InnoDB里面是通过undo_log日志来保证事务原子性的，undo_log记录了数据修改之前的值（逻辑日志），一旦发生异常，就可以通过undo_log日志实现回滚操作。

（2）一致性（consistency）:

事务操作使数据库从操作前的一致性状态变成事务成功后的另一个一致性状态。以转账为例，如果转账成功那么转出方转出的钱数量要和转入方的钱数量一致；如果转账失败那么转出方和转入方的账上余额都不变，还是转账之前的状态。
事务的一致性是通过事务的原子性、隔离性、持久性来保证的。

（3）隔离性（isolation）:

事务与事务之间是相互独立、执行过程没有关联，一个事务执行过程对另一个事务执行过程不可见，只有当一个事务执行结束后，执行结果才对另一个事务可见。
事务的隔离性是通过MVCC或锁来保证。当事务第一次进行范围查询时没有数据，然后另外一个事务新插入了一条之前不存在的数据的情况下，因为这种之前没有数据也就版本链上的Readview没有信息，这种情况下产生的幻读问题MVCC不能完全解决；如果InnoDB采用加间隙锁的方式则可以完全解决这种场景下产生的幻读问题。

（4）持久性（durability）:

事务一旦执行成功，那么其结果将会永久持久化到硬盘。如果硬盘物理介质被破坏那么数据有可能会丢失。
事务的持久性是通过redo_log来实现的，在写数据时，会先写到内存的buffer pool里面，同时记录到redo_log里面，如果在刷盘之前出现异常，在重启后从redo_log里面读取数据然后写入磁盘，从而保证数据的持久性。

2.事务并发引起的问题：

（1）脏读（dirty read）：

事务A修改了数据并未提交，然后又回滚。在事务A回滚数据之前，事务B读取了事务A未提交的数据，此时对事务B来说，读到的数据就是脏数据。

（2）不可重复读（unrepeatable read）：

事务B第一次从数据库读取数据之后，事务A修改了事务B读取的数据，当事务B再次读取数据时读到了事务A修改之后的数据，导致事务B两次读取的数据不一致，这个过程就是不可重复读。

（3）幻读（phantom read）:

事务B第一次从数据库读取数据之后，事务A往数据库又新增了一条数据，当事务B第二次再从数据库读取数据时，读到了第一次没有读到的数据，就跟产生了幻觉一样，所以叫幻读。
事务问题严重程度排名：脏读>不可重复读>幻读。

3.四种事务隔离级别：

SQL92标准中在可重复读的情况下会存在幻读问题，但是mysql中的可重复度隔离级别已经部分解决了幻读问题，但是没有完全解决幻读问题。
事务隔离级别在解决事务并发问题时会牺牲一部分性能，隔离级别越高性能损耗越大，其中可串行化隔离级别的性能最低。

（1）隔离级别的查看和设置：

level: {
    REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}
-- 查看事务隔离级别：
show variables like 'transaction_isolation';
select @@transaction_isolation;
-- 修改事务隔离级别：
set global transaction isolation level serializable; -- 在全局范围内影响。
set session transaction isolation level serializable; -- 在会话范围内影响。
set session transaction isolation level repeatable read; -- 在会话范围内影响。
set transaction isolation level serializable; -- 只对执行语句后的下一个事务产生影响。

4.MySQL事务语法和注意事项：

（1）手动操作事务：

-- 手动开启全局事务：
start transaction;
-- 手动提交事务：
commit;
-- 手动回滚事务：
rollback;

（2）保存点：

-- 事务自动提交开关：
show variables like '%autocommit%';
-- 关闭自动提交：如果要为SQL创建保存点需要用到此操作
set autocommit=0;
-- 开启自动提交：
set autocommit=1;

（3）隐式提交：

DDL语句的执行会使得开启手动提交的事务自动提交。即使回滚也也于事无补，不能回滚到事务提交前的状态。

（二）MVCC：

1.版本链：

（1）版本链形成过程：

CREATE TABLE `teacher` (
  `number` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `domain` varchar(100) NOT NULL,
  PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（2）版本链的作用：

Innodb引擎就是通过在版本链的基础上加锁来实现不同的事务隔离级别。在UNCOMMITTED READ隔离级别下只读取最新记录的数据，虽然并发性能很好，但是不可靠因为会读到脏数据。

（3）概念：

对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，如果创建表中包含主键或者非null的unique键时都不会包含row_id列；创建表时如果没有设置主键，那么mysql会自动为表创建一个rowid作为表的主键）：

  ①trx_id:每当一个事务对聚簇索引记录进行改动时，都会把该事务对应的事务id赋值给trx_id隐藏列；
  ②roll_pointer:每当对某条聚簇索引记录进行改动时，都会把旧的版本记录写入到undo日志中，然后这个隐藏列就相当于一个指针，通过它可以找到之前的旧记录。

补充点：

  ①undo日志：为了实现事务的原子性，InnoDB存储引擎会在实际进行增、删、改一条记录之前，把原来的数据记录记录到undo日志里面。一般每条记录的改动就会对应一条undo日志，但在某些更新操作中可能会对应两条或者多条记录。一个事务在执行操作过程中可能会增、删、改多条数据记录，也就是需要记录多条undo日志，这些日志会按照生成的顺序分别被称为0号日志、1号日志、2号日志......等，这个编号也被称为undo no。

当往表里面插入一条数据之后：

INSERT INTO teacher VALUES(1, '李瑾', 'JVM系列');

假设插入该记录的事务id是60，那么此时该条数据记录的示意图如下所示：

假设有两个id分别为80/120的事务要对上面这条数据记录进行改动，流程如下：

每次对数据记录的改动都会记录进undo日志中，每条undo日志都有一个roll_pointer属性（insert操作没有该属性，因为它没有更早的版本），roll_pointer指针属性可以将那些undo日志连接起来，串成一个链表，如下图所示：

对于该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer连接成一个链表，我们把这个链表称只为版本链，版本链的头结点就是当前记录的最新值。另外，每个版本中还包含生成这个版本时对应的事务id。于是利用这个记录的版本链来控制并发事务访问相同记录的行为，那么这种机制就被称之为多版本并发控制（Muti-Version Concurrency Control,即MVCC）。

2.ReadView:

ReadView作用于SQL查询。
对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最版本就好了（所以就会出现脏读、不可重复读、幻读）。
对于使用SERIALIZABLE隔离级别的事务来说，InnoDB使用加锁的方式来访问记录（也就是所有的事务都是串行的，当然不会出现脏读、不可重复读、幻读）。
对于使用READ COMMITTED和REPEATABLE RAED隔离级别的事务来说，都必须保证读到已经提交的事务修改过的记录，也就是说假如另一个事务对记录进行修改但是尚未提交，是不能读取到最新版本的记录的。为此，InnoDB提出了一个ReadVeiw的概念，其中包括4个比较重要的内容：

（1）m_ids：

表示生成ReadView时当前系统中活跃的读写事务的id列表；

（2）min_trx_id：

表示生成ReadView时当前系统中活跃的读写事务的id列表中最小的事务id，也就是m_ids中的最小值。

（3）max_trx_id：

表示生成ReadView时系统应该分配给下一个事务的id值。max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1、2、3这三个事务，之后id为3的事务提交了。那么之后一个新的读写事务在生成ReadView时，m_ids就包括1和2，min_trx_id就是1，max_trx_id就是4。

（4）creator_trx_id：

表示生成该ReadView的事务的id。

3.READ COMMITTED:

（1）脏读问题的解决：

READ COMMITTED隔离级别的事务每次在查询时都会生成一个独立的ReadView。
在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别最大的一个区别就是它们生成ReadView的时机不同。还是以Teacher表为例，假设现在Teacher表中只有一条由事务id为60的事务插入的一条记录，接下来看看两种不同隔离级别在生成ReadView时的时机到底不同在哪里：

①READ COMMITTED：

每次读取数据前生成一个ReadView。比方说现在系统里面有两个id分别是80和120事务在执行，其中id为80的事务执行的SQL如下：

UPDATE teacher  SET name = '马' WHERE number = 1;
UPDATE teacher  SET name = '连' WHERE number = 1;
...

此刻，表teacher中number为1的记录得到的版本链如下：

假设id为80、隔离级别是READ COMMITTED的事务现在开始执行：

使用READ COMMITTED隔离级别的事务
BEGIN;
SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'

第一次select的时间点如下：

这个select的执行过程如下：
在执行select语句时会先生成一个ReadView，ReadView的列表的内容就是[80,120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是‘连’，该版本的trx_id为80，这个事务id包含在m_ids中，所以不符合可见性要求（trx_id属性值在ReadView的min_trx_id和max_trx_id之间说明在生成ReadView时创建该版本的事务处于未提交的活跃状态，该版本不可以被访问；如果不在ReadView的min_trx_id和max_trx_id之间说明生成ReadView时该版本的事务已经被提交，因此该版本可以被访问），因此不能访问最新的这条记录，那么根据roll_pointer调到次新版本的记录。下一个（次新）版本的列name的内容是‘马’，该版本的trx_id也是80，该值也在m_ids列表内，所以也不符合要求，那么继续跳到下一个（次次新）历史版本。下一个（次次新）历史版本的列name的内容是‘李瑾’，该版本的trx_id值是60，这个值不在ReadView的m_ids范围内，所以这个版本是符合要求的，最后返回给用户的数据版本记录就是这个列name为‘李瑾’的记录。所以有了这种机制就不会发生脏读的问题。因为在这个查找数据的过程中会去判断是否是活跃版本，只有非活跃版本的数据才能返回，不可能读到没有commit的记录。

（2）不可重复读问题：

接上面，现在把事务id为80的事务提交一下，然后在事务id为120的事务中更新一下表teacher中number为1的记录：

-- Transaction120：id为120的事务开始修改记录
BEGIN;
-- 更新了一些别的表的记录
UPDATE teacher  SET name = '严' WHERE number = 1;
UPDATE teacher  SET name = '晁' WHERE number = 1;

此时，teacher表中number为1的记录的版本链如下：

然后再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个number为1的记录，如下：

BEGIN;
-- SELECE1：Transaction 80、120均未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'
-- SELECE2：Transaction 80提交，Transaction 120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'连'

第2次select的时间点如下图：

这个select2的执行过程如下：

SELECT * FROM teacher WHERE number = 1;

在执行select语句时又会单独生成一个ReadView，该ReadView信息如下：
m_ids列表的内容就是120，min_trx_id为120，max_trx_id为121，creator_trx_id为0。然后从当前记录对应的版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是‘晁’，该版本的trx_id为120，它包含在m_ids列表内，所以不符合可见性要求，根据roll_pointer调到下一个（次新）历史版本。下一个（次新）历史版本的列name的内容是’严’，该版本的trx_id值为120，也包含在m_ids里列表内，所以也不符合要求，继续跳到下一个（次次新）版本。下一个（次次新）版本的列name的内容是‘连’，该版本的trx_id为80，它小于ReadView中的min_trx_id值120，所以不包含在m_ids列表范围内，所以这个版本是符合要求的，因此最后返回给用户的版本就是这条列name内容为‘连’的记录。
以此类推，如果之后id为120的事务也提交了，那么再次在使用READ COMMITTED隔离级别的事务中查询表teacher中number为1的记录时，得到的结果就是‘晁’了，具体流程不做分析了。
上面使用READ COMMITTED的事务的两次查询得到的结果明显不一致，出现了不可重复读问题。

4.REPEATABLE READ:

（1）解决不可重复读问题：

使用REPEATABLE READ隔离级别的事务第一次在读取数据时生成一个ReadView。对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会再重复生成了。假如系统里面现在有两个id分别为80和120的事务正在执行，其中id为80的事务要执行的SQL如下：

UPDATE teacher  SET name = '马' WHERE number = 1;
UPDATE teacher  SET name = '连' WHERE number = 1;
...

此时，teacher表中number为1的记录对应的版本链如下图：

假设id为80的事务使用REPEATABLE READ隔离级别并且现在开始执行：

此时，假设有一个使用READ COMMITTED隔离级别的事务开始执行查询：

BEGIN;
-- SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'

这个select1的执行过程如下：
在执行select语句时会生成一个ReadView：m_ids列表内容就是[80,120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。然后从版本链中挑选可见的数据记录，从图中可以看出，最新版本记录的列name的内容是‘连’，该版本的trx_id是80，它包含在m_ids列表内，所以不符合可见性要求（trx_id属性值在ReadView的min_trx_id和max_trx_id之间说明创建ReadView时生成该版本数据记录的事务仍然处于活跃状态，那么该版本的记录不可以被访问；如果不在，说明生成ReadView时创建该版本记录的事务已经提交，该版本可以被访问），根据roll_pointer调到下一个（次新）历史版本。下一个版本的列name的内容是‘马’，该版本的trx_id也为80，也在m_ids列表内，所以也不符合要求，继续跳到下一个（次次新）历史版本。下一个（次次新）历史版本的列name的内容是‘瑾’，该版本的trx_id为60，其值小于ReadView的min_trx_id的值，因此不在m_ids列表内，所以这个版本是符合要求的，所以最后返回给用户的版本就是这条列name为‘李瑾’的记录。
接下来，我们把id为80的事务提交了，然后到id为120的事务中更新number为1的数据记录：

-- Transaction120
BEGIN;
-- 更新了一些别的表的记录
UPDATE teacher  SET name = '严' WHERE number = 1;
UPDATE teacher  SET name = '晁' WHERE number = 1;

此刻，teacher表中number为1的记录对应的版本链如下图所示：

然后再到刚才使用REPEATABLE READ隔离级别的事务中查看number为1的这条数据记录，结果如下：

-- BEGIN;
-- SELECE1：Transaction 80、120均未提交。
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'
-- SELECE2：Transaction 80提交，Transaction 120未提交。
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'

select2的执行过程如下：
当前事务使用的隔离级别是REPEATABLE READ，而在select1查询时已经生成过ReadView了，所以此时select2查询时直接复用该事务第一次查询时生成的ReadView，其m_ids列表的内容是[80,120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
根据前面的分析，返回给用户的数据记录值仍然是‘李瑾’。也就是说两次select查询得到的结果是重复的，记录的列name值都是‘李瑾’，这就是可重复读的含义。

（2）总结：ReadView中的比较规则（前两条）

①如果被访问版本的trx_id属性值和ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过记录，所以该版本记录可以被当前事务访问到；
②如果被访问版本的trx_id属性值小于ReadView中的creator_trx_id值，表明生成该版本的事务在当前事务生成ReadView之前已经被提交了，所以该版本可以被当前事务访问到。

5.MVCC下的幻读现象以及问题解决

幻读是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，而这一记录来自另一个事务新添加的记录。
可以分析下：
在REPEATABLE READ隔离级别下的事务T1先根据某个搜索条件读取到多条记录，然后事务T2插入一条符合搜索条件的新记录并提交，然后事务T1再次根据相同条件进行查询时。结果会怎样？
按照ReadView中的比较规则（后两条）：
③如果被访问版本对应的事务trx_id属性值大于或等于ReadView中的max_trx_id值，表明该版本的事务在当前事务生成ReadView之后才开启，所以该版本记录不可以被当前事务访问到；
④如果被访问版本对应的事务trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是否在m_ids列表中，如果在则说明创建ReadView时生成该版本的记录的事务还处于活跃状态，该版本不可以被访问；如果不在，则说明创建ReadView时生成该版本的事务已经被提交，那么该版本则可以被访问到。不管事务T2比事务T1是否先开启，事务T1都看不到T2的提交的。这个通过版本链、ReadView以及可见性的判断规则可以分析出来。
但是，在REPEATABLE READ隔离级别下InnoDB中的MVCC可以很大程度上避免幻读问题，而不是完全截止掉幻读问题。看看下面的情况：

在事务T1中查询number为30的数据：

select * from teacher where number = 30;

显然，此时number=30的记录是不存在的。接着在事务T2中，执行如下SQL往数据库中插入一条数据：

insert into teacher values(30,'豹','数据湖');

这个时候，回到事务T1中执行一条update语句来更新事务T2刚插入的number=30记录：

update teacher set domain='RocketMQ' where number=30;
select * from teacher where number = 30;

事务T1很明显出现了幻读现象。在REPEATABLE READ隔离级别下，事务T1第一次执行普通的select语句时生成了ReadView（但是没有版本链，因为没有历史数据），之后事务T2向teacher表中插入一条记录并提交，然后T1也进行了一个update语句。ReadView并不能阻止事务T1执行UPDATE或者DELETE语句来改动这个新插入的记录，但是这样一来，这条新记录的trx_id隐藏列的值就变成了T1的事务id（这是trx_id的特性，当有事务对聚簇索引的记录进行修改时，当前事务的id值就会赋值给原来记录的隐藏列的trx_id）。

之后事务T1在使用普通select语句去查询这条记录时就可以看到这条记录了，也就可以把这条记录返回给客户端。因为这个特殊现象的存在，我们也可以认为MVCC并不能完全禁止幻读（就是第一次读是空的情况，且在自己的事务中对该条数据进行了修改）。

6.MVCC总结

综上可知，所谓的MVCC(Multi-Version Concurrency Control,多版本并发控制)指的就是在使用READ COMMITTED和REPEATABLE READ这两种隔离级别的事务在执行普通select查询的过程中访问版本链的过程，这样以来可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
READ COMMITTED和REPEATABLE READ这两种隔离级别最大的区别就是生成ReadView的时机不同：
使用READ COMMITTED隔离级别的事务在每次进行普通select查询前都会生成一个ReadView，而使用REPEATABLE READ隔离级别的事务只在第一次执行普通的select查询前生成一个ReadView，之后的查询都重复使用这个ReadView，从而基本上可以避免幻读现象（就是第一次读如果ReadView为空的情况中的某些情况则避免不了）。
另外，所谓的MVCC只是在我们执行普通的select查询时才生效，截至目前我们所见的都是普通的select语句查询都算是普通的查询，至于什么是不普通的查询，后面会说（锁定读）。

五、MySQL中的锁

（一）解决并发事务问题

事务并发执行可能会带来各种问题，最大的一个难点：
一方面要最大程度的利用数据库的并发访问，另一方面还要确保每个用户能以一致的方式读取和修改数据，尤其是一个事务进行读取操作的同时另一个事务进行改动操作。

（二）并发事务问题：脏读、不可重复读、幻读。

1.方案一：读操作MVCC，写操作进行加锁

事务利用MVCC进行读取操作称之为一致性读，或者一致性无锁读，也称之为快照读，但是往往读取的是历史版本数据。所有普通的select语句（plain select）在READ COMMITTED和REPEATABLE READ隔离级别下都算是一致性读。
一致性读并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动。
很明显，采用MVCC方式的话，读-写操作彼此并不冲突，性能更高，采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。一般情况下我们更愿意采用MVCC方式来解决读-写并发执行的问题，但是某些业务场景下，会要求必须采用加锁的方式执行。

2.方案二：读、写都采用加锁的方式

（1）适用场景：

业务场景不允许读取记录的旧版本，而是每次都必须读取记录的最新版本，比方在银行存款，就需要先把账户余额读取出来，然后加上本次存款的数额，最后再写入到数据库中。在将账户余额读取出来后就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才能访问到储户的账户余额。这样在读取记录的时候也就需要对其进行加锁操作，这样以来也就意味着读-写操作也像写-写操作那样排队执行。

（2）脏读问题的产生：

当前事务读取到了另一个事务写入的未提交的记录，如果另一个事务在写入记录时给记录加锁，那么当前事务就无法读到那个事务的写入记录，自然也就不会产生脏读问题。

（3）不可重复读问题的产生：

当前事务先读取一条记录，另一个事务对该条记录进行修改并且提交，当前事务再次以相同条件查询这个记录时，得到的结果和之前查询的结果不一致，如果当前事务在第一次查询时给该记录加锁，那么另一个事务就无法修改该记录了，自然也就不会发生不可重复读问题。

（4）幻读问题的产生：

当前事务先读取一个范围的记录，然后另一个事务向该范围内插入了新记录，当前事务再次读取该范围记录时发现了新插入的记录，这个新插入的记录也叫做幻影记录。采用加锁的方式解决幻读问题就有点不太容易了，因为当前事务第一次进行范围读取记录时幻影记录并不存在，所以读取时就显得有些麻烦（因为并不知道给谁加锁）。InnDB是如何解决这个问题的后面再说。

（三）锁定读

也称之为当前读，读取的是最新的记录，并且对读取的记录进行加锁，阻塞其他事务对该记录进行修改，避免出现安全问题。
当前读：
select lock in share mode(共享锁)、select for update(排他锁)、update(排他锁)、insert(排他锁/独占锁)、delete(排他锁)、串行化事务隔离级别都是当前读。
既然要保证前后两次读取数据一致，那么读取数据的时候锁定要读的数据，不允许其他事务修改就行了。这种实现方式也称之为LBCC(基于锁的并发控制，Lock-Based Concurrency Control)。

1.共享锁和独占锁

在使用加锁的方式解决问题时，既要允许读-读情况不收影响，又要使写-写情况不收影响，还得让读-写、写-读情况中的操作都不受影响，MySQL中的锁有好几类：

（1）共享锁：Shared Locks,简称S锁。

在事务要读取一条记录时，需要先获取该记录的S锁。假如事务T1获取了一条记录的S锁，事务T2也想获取这条记录的S锁，那么T2也是可以获取到该记录的S锁。也就是说事务T1和事务T2可以同时持有S锁。

（2）独占锁（排他锁）：Exclusive Locks,简称X锁。

在事务改动一条记录时，需要先获取该记录的X锁。如果事务T2也想获得该记录的X锁来改动这条记录，那么此操作会被阻塞，直到事务T1提交并且将X锁释放掉之后。在事务T1获取某条记录X锁之后，事务T2如果还想获取该记录锁，不管是想获取S锁还是X锁都会被阻塞，直到T1事务提交。
所以说S锁和S锁相互兼容，S锁和X锁相互排斥，X锁和X锁也是相互排斥。相互兼容时同一时间可以有多个事务同时获取该锁，相互排斥时同一时间只能有一个事务可以获取该锁。
|X|不兼容X|不兼容S|
|S|不兼容X|兼容S|

2.锁定读的SELECT语句

当事务T1提交之后T2加X锁才会成功：


接下来，在事务T3中开启事务，并且对记录加X锁成功：

在事务T4中尝试对记录加S锁，但是会处于阻塞状态直到超时，原因是T3持有X锁且未释放，这同时也验证了X锁和S锁互斥：

接下来，在事务T3仍然持有X锁的前提下，在事务T4中对记录尝试加X锁，会发现仍然会阻塞直到超时，原因是T3持有X锁且未释放，这同时也验证了X锁和X锁互斥：

只有当T3事务提交并且释放本来持有的X锁之后，在T4事务中加S锁和加X锁才会成功。

3.写操作的锁

平常所用到的写操作无非是：DELETE、UDPATE、INSERT这三种：

（1）DELETE

对一条记录做DELETE操作的过程其实就是在B+树中定位到这条记录的位置，然后获取这条记录的X锁，然后再执行delete mark操作。我们可以把这个定位待删除记录到B+树位置中的过程看成是一个获取X锁的锁定读。

（2）INSERT

一般情况下，新插入一条记录的操作并不会加锁，InnoDB通过一种称之为隐式锁的机制来保护这条新插入的记录在事务提交前不被事务访问。当然，在一些特殊情况下insert操作也会获取锁，具体情况后面再说。

（3）UPDATE

对一条记录做update操作时分为三种情况：
①如果未修改记录的键值并且被更新的列在修改后占用的存储空间在修改前后未发生变化，则先在B+树中定位这条记录的位置，然后获取该记录的X锁，最后在原记录的位置进行修改操作。其实我们可以把这个定位待修改记录到B+树位置中的过程看成是一个获取X锁的锁定读。
②如果未修改记录的键值并且被更新的列中至少有一个列在修改后所占用的存储空间发生变化，则先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，将这条记录彻底删除掉（就是把这条记录移入垃圾链表），最后再重新插入一条全新的记录。这个定位待修改记录到B+树位置中的过程可以看成是一个获取X锁的锁定读，新插入的记录由insert提供的隐式锁进行保护。
③如果修改了记录的键值，则相当于在原记录上做delete操作之后再来一次insert操作，加锁操作就需要按照delete和insert的规则进行了。

（四）锁的粒度

之前谈到的锁都是针对记录加锁，可以称之为行级锁或行锁，对记录加锁影响的也只是加锁的记录而已，此时锁的粒度比较细；
当然一个事务也可以针对表加锁，此时可以称之为表级锁或表锁，这个时候影响的就是加锁的整张表中的所有记录，这时锁的粒度就比较粗。
给表加锁也分为共享锁（S锁）和独占锁（X锁）。

1.表锁与行锁的比较

（1）锁定粒度：表锁>行锁；

（2）加锁效率：表锁>行锁；

（3）冲突概率：表锁>行锁；

（4）并发性能：表锁<行锁；

2.给表加S锁

如果一个事务给表加S锁：
其他事务可以获得该表的S锁；其他事务可以获得该表中记录的S锁；其他事务不可以获取该表的X锁；其他事务也不可以获取该表中记录的X锁。

3.给表加X锁

如果一个事务给表加了X锁（意味着这个事务要独占这张表），那么：
别的事务获取不到该表的S锁；别的事务获取不到该表中记录的S锁；别的事务获取不到该表的X锁；别的事务获取不到该表中记录的X锁。

（1）场景举例：

为了更好的理解这个表级别的S锁和X锁以及后面的意向锁，举一个现实生活中的例子。就拿曾经很火爆的互联网风口项目共享Office来说明加锁：
共享Office有栋写字楼，写字楼自然会有很多层。办公室都是共享的，客户可以随便选择办公室进行办公。每层楼可以容纳很多客户同时进行办公，每当一个客户进入某个楼层去办公，就相当于在这个楼层的入口处挂了一把S锁，如果有很多客户进入到楼层进行办公，就相当于在很多楼层处挂了很多把S锁（类似于行级别的S锁）。
有时候楼层需要进行检修，比如换天花板、换地板、检修水电啥的，假设这些项目不能同时开展。如果某个楼层针对某个项目进行检修，就不允许客户入场办公，也不允许其他维修项目入场进行维修，此时相当于在该楼层门口处挂了一把X锁（类似于行级别的X锁）。
不过，上述场景中提到的两种锁都是针对楼层而言的，有时候当然会有一些特殊需求：

①有投资人要考察Office的环境

投资人和公司并不想影响客户进入大楼办公，但是此时不能有楼层进行检修，所以可以在大楼门口放一把S锁（类似于表级别的S锁）。此时：
来大楼办公的客户看见大门口有S锁，可以继续进去办公；修理工看见门口有S锁，则先在大门口等着，啥时候投资人走了，把大楼门口的S锁撤掉之后再进入大楼进行维修。

②公司要和房东谈条件

此时不允许大楼中有正在办公的楼层，也不允许对楼层进行检修。所以可以在大门口放置一把X锁（类似表级别的X锁）。此时：
来办公的客户看见门口有X锁，则需要先在大楼门口处等着，啥时候条件谈好了，把大楼的X锁撤掉之后再进入大楼办公；修理工看见门口有X锁，则先在大楼门口处等着，啥时候谈判结束，把大楼的X锁撤掉之后再进入大楼进行维修。

（五）意向锁

上面场景中有两个问题：
如果我们想对整栋大楼加S锁，首先需要确保大楼中没有正在维修的楼层，如果有楼层正在进行维修，需要等到维修结束后才可以对整栋大楼整体加S锁，那么我们如何知道大楼中是否有楼层正在维修呢？
如果我们想对整栋大楼加X锁，首先需要确保大楼中没有正在办公的客户或没有楼层正在维修，如果有客户办公或者有楼层在维修，需要等到客户办公结束以及维修结束离开楼层后才能对整体楼层加X锁。那么我们如何知道是否有客户正在楼层办公或者有楼层正在进行维修呢？
针对上面两个问题，如果采用遍历的方式去查看每个楼层是否有加锁，那效率太低了。这个时候InnoDB中的意向锁应运而生。

1.意向共享锁：Intention Shared Lock,简称IS锁。

当事务准备在记录上加S锁时，需要先在记录对应的表上加IS锁。

2.意向独占锁：Intention Exclusive Lock,简称IX锁。

当事务准备在记录上加X锁时，需要先在记录对应的表上加IX锁。
视角回到大楼和楼层上：

如果有客户要进入楼层办公，那么先在整栋大楼门口放一把IS锁（表级锁），然后再到要办公的楼层入口处放一把S锁（行锁）。
如果有维修工要进入大楼进行维修，那么需要先在整栋大楼门口放一把IX锁（表级锁），然后再到具体要维修的楼层入口处放一把X锁（行锁）。
之后：
如果有投资人要参观大楼，也就是想在整栋大楼门口前放S锁（表锁）时，首先看一下大楼门口是否有放IX锁，如果有就意味着此时大楼有楼层正在进行维修，需要等到维修结束把IX锁撤掉之后才可以给整栋大楼加S锁。
如果写字楼存在谈判问题，也就是想在整栋大楼门口处加X锁（表锁）时，首先得看一下大门口是否有放IS锁或IX锁，如果有则说明有客户正在楼层办公或者有楼层正在进行维修活动，需要等到客户办公结束以及楼层维修活动结束把IS锁和IX锁撤掉之后才可以对整栋大楼加X锁。

3.总结：

IS、IX锁是表级锁，它们的出现是为了在加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，从而避免用遍历的方式查看表中是否存在记录被加锁。也就是说，IS锁和IX锁相互兼容，IX锁和IX锁兼容。如下表中的兼容性：
|兼容性|X|IX|S|IS|
|–|–|–|–|–|
|X|不兼容|不兼容|不兼容|不兼容|
|–|–|–|–|–|
|IX|不兼容||不兼容||
|–|–|–|–|–|
|S|不兼容|不兼容|||
|–|–|–|–|–|
|IS|不兼容||||
锁的组合性：（意向锁没有行锁）
|组合性|X|IX|S|IS|
|–|–|–|–|–|
|表锁|有|有|有|有|
|–|–|–|–|–|
|行锁|有||有||

（六）MySQL中的表锁和行锁

MySQL支持多种存储引擎，不同的存储引擎对锁的支持情况也不一样。这里重点讨论InnoDB存储引擎中的锁。

1.其他存储引擎中的锁

对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁，而且这些存储引擎不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。比方说在session1中对一个表执行select操作，就相当于给这个表加了一个表级锁S锁，如果select操作未完成时，session2相对这个表执行update操作，相当于要获取表的X锁，此操作会被阻塞，直到session1中的select操作完成并释放掉表级S锁，然后session2才可以获取该表的X锁，然后执行SQL语句进行update操作。
因为使用MyISAM、MEMORY、MERGE这些存储引擎的表在同一时刻只允许一个会话对表进行写操作，所以这些存储引擎最好用在只读、或者大部分都是读操作，或者单用户的场景下。另外，在MyISAM存储引擎中有一个称之为Concurrent Inserts的特性，支持在对MyISAM表读取的同时插入记录，这样可以提升数据写入速度。关于Concurrent Inserts的细节，详情可以参考相关文档。

2.InnoDB存储引擎中的锁

InnoDB存储引擎既支持表锁、也支持行锁。表锁实现简单，占用资源少，但是锁的粒度很粗，有时候只需要锁住表中的某些记录，如果使用表锁，会把其他的记录也加锁，所以性能比较差。行锁粒度更细，可以实现对行记录的精准控制。

（1）InnoDB中的表级锁

①表级别的S锁、X锁、元数据锁

在对某个表执行select、insert、delete、update语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁和X锁的。
另外，在对某个表执行诸如LATER TABLE、DROP TABLE之类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、UPDATE、DELETE的语句会发生阻塞；同理，某个事物对某个表正在执行SELECT、INSERT、UPDATE、DELETE语句时，在其他会话中对这个表执行DDL语句也同样会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁（Metadata Locks,简称MDL）来实现的，一般情况下不会使用InnoDB存储引擎提供的表级别的S锁或X锁。因为InnoDB存储引擎提供的表级S锁或X锁相当鸡肋，只在一些特殊情况下才用到，比方说崩溃恢复过程中用到。不过我们可以手动获取这两种锁，比方说在系统变量autocommit=0（关闭MySQL的事务自动提交），innodb_table_locks = 1（开启表锁功能）时（在这两个条件下InnoDB能够感知到表锁），手动获取InnoDB存储引擎提供的的表t的S锁和X锁可以按如下方式写：
LOCK TABLE t READ: InnoDB存储引擎会对表t加表级别的S锁。
LOCK TABLE t WRITE: InnoDB存储引擎会对表t加表级别的X锁。
注：尽量避免在使用InnoDB存储引擎的表上使用LOCK TABLE这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。

②表级别的IS锁、IX锁

当我们在对使用InnoDB存储引擎的表中的某些记录加S锁之前，需要先给表加一个表级IS锁；当我们在对使用InnoDB存储引擎的表中的某些记录加X锁之前，需要先给这些记录对应的表加一个表级IX锁。
IS锁和IX锁的作用是标记表中是否有记录加了S锁或X锁，以免后续在对整张表加表级别的S锁或X锁时用遍历的方式判断表中是否有记录加了锁。

③表级别的AUTO-INC锁

在使用MySQL的过程中，我们可以为表的某个列添加AUTO_INCREMENT属性，之后再插入记录时，可以不用指定该列的值，系统会自动为它赋上递增的值。系统实现这种自动为AUTO_INCREMENT修饰的列赋递增值的原理主要有两个：

A.采用AUTO_INC锁：

就是在执行插入语句时给表加一个AUTO_INC表级锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO_INC锁释放掉。这样一个事务在持有AUTO_INC锁的过程中，其他事务的插入语句都会被阻塞，这样可以保证一个语句在插入数据记录时分配的递增值是连续的。
如果我们在插入语句执行前不确定具体要插入多少条数据记录（无法确定插入记录的数量），比方说使用INSERT…SELECT、REPLACE…SELECT或者LOAD DATA这种插入语句，一般是使用AUTO_INC这个表级锁来为AUTO_INCREMENT修饰的列生成对应的值。

B.采用一个轻量级锁：

在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的AUTO_INCREMENT修饰的列的值之后，就把这个轻量级锁释放掉，并不需要等到整个语句执行结束才释放。
如果我们在插入语句前就可以确定具体要插入多少条记录，那么一般采用轻量级锁的方式对AUTO_INCREMENT修饰的列赋值。这种方式可以避免锁表，数据写入性能较好。
InnoDB提供了一个innodb_autoinc_lock_mode的系统变量来控制系统到底采用哪种方式来为AUTO_INCREMENT修饰的列进行赋值。当值为0时采用AUTO_INC锁；当值为1时采用轻量级锁；当值为2时采用两种方式的混合（也就是插入记录数量确定时采用轻量级锁，不确定时采用AUTO_INC锁）。
当innodb_autoinc_lock_mode值为2时，可能会造成不同事务中的插入语句为AUTO_INCREMENT修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的。

show variables like 'innodb_autoinc_lock_mode';

（2）InnoDB中的行级锁

行锁，也称为记录锁，顾名思义就是在记录上加的锁。但是这个记录指的是通过给索引上的索引项加锁。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
不论是使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来对数据加锁。
只有执行计划真正使用了索引，InnoDB才会使用行锁：
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如很小的表，它就不使用索引，这种情况下InnoDB将使用表锁而不是行锁。
同时，当我们使用范围条件而不是相等条件检索数据，并请求锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。
不过，即使是使用行锁，InnoDB里也是分成了各种类型的。换句话说即使是对同一条记录加锁，如果类型不同，起到的功效也是不同的。比如对teacher表的number字段设置索引并插入几条记录：

create index idx_number on teacher(number);

接下来看看有哪几种行锁类型。

①Recod Locks

也叫记录锁，就是仅仅把一条记录锁上，官方的类型名称为：LOCK_REC_NOT_GAP。比如我们把number值为6的这条记录加一个记录锁的示意图如下：

记录锁分S型和X型：当一个事务获取了某一条记录的S型记录锁之后，其他事务仍然可以获取该记录的S型记录锁，但不能获取该记录的X型记录锁；当一个事务获取了某一条记录的X型记录锁之后，其他事务既获取不到该记录的S型记录锁也获取不到该记录的X型记录锁。

②Gap Locks

MySQL的InnoDB存储引擎在REPEATABLE READ隔离级别下解决幻读问题的方案有两种：使用MVCC方案解决或采用加锁的方式解决。使用加锁方式解决幻读问题时，当事务第一次执行读取操作时，那些幻影记录是不存在的，这时是无法给幻影记录加记录锁的。InnoDB提出了一种称之为Gap Locks的锁，官方类型名称为：LOCK_GAP，我们可以简称为gap锁。
间隙锁实质上是给索引前后的间隙上锁，不是对索引本身上锁。
会话1开启一个事务T1，执行如下更新操作：

show variables like 'innodb_status_output%'; -- 查看innoDB引擎中哪个事务对哪些记录加了锁
-- 下面这两个状态为1的值需要组合在一起：
set global innodb_status_output_locks=1; -- 开启：日志中显示锁状态
set global innodb_status_output=1; -- 开启状态输出
-- 下面这两个状态为0的值需要组合在一起：
set global innodb_status_output_locks=0; -- 关闭（默认）：日志中显示锁状态
set global innodb_status_output=0; -- 关闭状态输出（默认）
-- 查看innoDB给事务分配的锁的情况：
show engine innodb status\G(该条语句执行时不要输入分号)

begin;
update teacher set domain ='Spring' where number between 2 and 6; -- 范围更新操作

此时，MySQL的InnoDB存储引擎会对（2,6）和（6,10）这两个左右开区间加gap间隙锁。如果有记录更改或插入操作在这两个区间内是会被阻塞直到超时失败的，如下图所示：

此时，意味着不允许别的事务在（2,6）和（6,10）这两个左右开区间插入新记录：
下面这条SQL语句会执行失败：

begin;
insert into teacher value(5,'晁','docker');

为什么不能执行成功？因为记录(5,‘晁’,‘docker’)这条数据要写入的话，在索引idx_number上，刚好落在（2,6）这个开区间内，是有锁的，当然不允许插入。但是当SQL语句变为下面这条就可以写入成功，因为70这条记录不在被锁的区间内：

begin;
insert into teacher value(31,'晁','docker');

③Next-Key Locks

临键锁是记录锁和间隙锁的组合，也就是索引本身加上之前的间隙，间隙锁保证在RR隔离级别下不会出现幻读问题，防止同一个事务内得到的范围查询结果不一致。假设索引包含的的值为10、11、13、20，那么临建锁的加锁范围就是（-∞，10],(10,11],(11,13],(13,20],(20,+∞)。对于最后一个间隔，临键锁锁定索引中最大值以上的间隙，以及值高于索引中任何实际值的supremum（在查看锁信息时可以看到这个标识）。

（七）死锁

1.概念

死锁是指两个或两个以上进程或事务在执行过程中相互占用资源并请求锁定对应的资源而造成的一种阻塞现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁。

（1）解决死锁问题的思路就是切断环路，避免并发形成环路：

①不同程序并发存取多个表，尽量约定以相同顺序访问表，可以大大降低死锁的概率；

②在同一个事务中，尽量做到一次锁定所有的资源，以减少死锁产生的概率；

③对于非常容易产生死锁的业务部分，可以尝试使用升级锁粒度，通过表锁来减少死锁概率；

④死锁和索引密不可分，合理优化索引。

（2）分享两个分析死锁很透彻的文章：

死锁分析1
死锁分析2

2.MySQL中的死锁

MySQL中出现死锁的原因也是一样的。

六、MySQL8新特性

见附件：MySQL8新特性.md

七、MySQL体系架构

见附件：MySQL体系架构.md

八、MySQL中的系统库

见附件：MySQL中的系统库.md

十、MySQL执行原理

见附件：MySQL的执行原理-1.md、MySQL的执行原理-2.md

十一、InnoDB引擎底层解析

见附件：InnoDB引擎底层解析.md

十二、事务底层与高可用原理

见附件：事务底层与高可用原理.md

十三、MySQL8新特性底层原理

见附件：MySQL8新特性底层原理.md