Mysql官方文档：https://dev.mysql.com/doc/refman/5.7/en/

高性能MySQL（第3版）：百度网盘，基于Mysql5.1和Mysql5.5

本机版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.32-log |
+------------+
1 row in set

Mysql的非官方的一些分支版本：Percona Server, MariaDB, PolarDB

MySQL服务器逻辑架构图

连接：每个客户端连接都会在服务器进程中拥有一个线程，每个连接的查询只会在这个单独的线程中执行。

表锁

是个读写锁，是开销最小的锁策略。尽管存储引擎可以管理自己的锁，MySQL服务器还是会使用各种不同的表锁来实现不同的目的，比如服务器会为诸如 ALTER TABLE 之类的操作使用表锁，而忽略存储引擎的锁机制。

LOCK TABLES tablename1 READ, tablename2 write,...;
......
UNLOCK TABLES;

如果一个线程获得在一个表上的 read 锁，该线程和所有其他线程只能从表中读数据，当前线程执行写write操作都会报错，其他线程执行write操作都会被阻塞，直到释放资源后执行。

如果一个线程在一个表上得到一个 write 锁，那么只有拥有这个锁的线程可以从表中读取和写表。其它的线程被阻塞。

unlock tables 会解锁当前线程的所有锁。

lock tables 和事务互相影响可能会产生无法预料的结果，应该尽量少用。

行锁

并发能力强，但是锁开销最大，行锁是在存储引擎层面实现的，而MySQL服务器层面不做实现，服务器层完全不了解存储引擎层中的锁实现。

隔离级别

MySQL定义了四种隔离级别，每一种隔离级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

查看mysql隔离级别：select @@tx_isolation

设置当前会话的的事务级别：

set session transaction isolation level read uncommitted
set session transaction isolation level repeatable read

推荐使用READ COMMITED

https://blog.csdn.net/raoxiaoya/article/details/112039431

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个事务时也会产生死锁，例如：

- 事务1START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.18 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

- 事务2START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

如果凑巧，两个事务都执行了第一条UPDATE语句，更新了一行数据，同时也锁定了该行数据，接着每个事务都尝试去执行第二条UPDATE语句，却发现该行已经被对方锁定，于是产生了死锁。

事务日志

事务日志可以帮助提高事务的效率，使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快的多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据，具体的恢复方式则视存储引擎而定。

事务

Mysql默认采用自动提交模式（AUTOCOMMIT=1），也就是说，如果不是显示的开始一个事务，则每个查询都被当做一个事务执行提交操作。

set autocommit = 1;

当 autocommit == 0 时，所有的查询都是在一个事务中，直到显示的执行commit或者rollback，该事务结束，同时又开始了另一个新事务。

事务是由存储引擎实现的，服务器层不参与管理。如果在事务中混合使用了事务型的表和非事务型的表（例如 InnoDB and MyISAM），在正常提交的情况下不会有什么问题，但如果该事务需要回滚，非事务型的表上的变更就无法撤销。

事务与锁

InnoDB 采用的是两阶段锁定协议，在事务执行过程中，随时都可以执行锁定，锁只有在执行commit 或 rollback 时才会释放，并且所有的锁是在同一时刻被释放，这块属于隐式锁定，Innodb 会根据隔离级别在需要的时候自动加锁。

另外，innodb 也支持通过特定的语句进行显示锁定，这些语句不属于SQL规范。

select ... lock in share mode
select ... for update

mysql 也支持 local tables 和 unlock tables 语句，这是在服务器层实现的，和存储引擎无关，它们有自己的用途，但不能替代事务。经常可以看到，在 InnoDB 上使用 lock tables，这不但没有必要，还会严重影响性能，实际上 InnoDB 的行级锁工作的很好。

lock tables 和事务之间相互影响的话，情况会变得非常的复杂，在某些mysql版本中甚至会产生无法预料的结果，因此本书建议，只有在 autocommit = 0 的情况下不得已使用 lock tabels 之外，其他时候都不要使用 lock tables。

间隙锁 next-key locking

间隙锁使得innodb不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入，防止幻读的出现。

MVCC

全称多版本并发控制（Multi-Version Concurrency Control），一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

数据类型

• 字段类型尽量设置为 NOT NULL。
• DATETIME 与 TIMESTAMP的区别：DATETIME 能保存更大的范围（1001年-9999年），与时区无关，底层存储的是 YYYYMMDDHHMMSS 的整数，存储空间占8个字节，所以可以用来排序，但是mysql输出的时候会以字符串YYYY-MM-DD HH:MM:SS的形式；TIMESTAMP 存储范围从 1970年 起的秒数，它是时间戳，占用4个字节存储空间，将时间戳转换成展示的字符串的过程中有一个时区的概念，它会取操作系统的时区来计算。
• 整数比字符串占的空间小，且查找速度快。

SHOW PROFILE

它是一个查询解析工具，默认是禁用的，可以在会话级别动态的修改

SET profiling = 1;

然后，在服务器上执行所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。当一条查询提交给服务器时，此工具会记录剖析信息到一张临时表，并且给查询赋予一个从1开始的整数标识符。

SHOW STATUS

它返回了一些计数器，既有服务器级别的全局计数器，也有基于某个连接的会话级别的计数器，例如其中的Queries在会话开始时为0 ，每提交一条查询增加1,。如果执行SHOW GLOBAL STATUS，则可以查看服务器级别的从服务器启动时开始计算的查询次数统计，不同计数器的可见范围不一样，不过全局的计数器也会出现在SHOW STATUS的结果中，容易被误以为是会话级别的，千万不要搞迷糊了。

你可能注意到通过EXPLAIN查看查询的执行计划也可以获得大部分相同的信息，但 EXPLAIN是通过估计得到的结果。

ALTER TABLE

这是一个耗时的阻塞操作，会锁表，Mysql在执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表，这样操作可能需要花费很长时间。不是所有的ALTER TABLE 操作都会引起表的重建，例如修改某个列的默认值有两种方法

ALTER TABLE film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

SHOW STATUS显示这个语句做了1000次读和1000次插入操作，换句话说，它拷贝了整张表到一张新表。可是这条语句仅仅是修改了字段的默认值。

理论上，mysql可以跳过创建新表的步骤，列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。

ALTER TABLE file ALTER COLUMN rental_durdation SET DEFAULT 5;

这个语句会直接修改.frm文件而不涉及表结构，所以这个操作是非常快的。

EXPLAIN

认为增加 EXPLAIN 时 Mysql不会真正的执行，这是一个常见的错误理解。实际上，如果查询在 FROM 子句中包括子查询，那么Mysql实际上会执行子查询，将其结果放在一个临时表中，然后完成外层查询优化。

要意识到EXPLAIN只是个近似结果，有时候可能与最终执行的SQL相去甚远。

select_type:表示是简单还是复杂查询，如果是复杂的，那么是三种复杂类型中的哪一种。simple值意味着不包括子查询和union，如果有任何复杂的子部分，则最外层部分标记为primary，其他部分标记如下：
1、subquery
2、derived
3、union
4、union resulttype：访问类型，就是Mysql决定如何查找表中的行。下面是最重要的访问类型，从最差到最优。
1、all: 全表扫描
2、index: 这个跟全表扫描一样，只是Mysql扫描表时按索引次序进行而不是行，它的主要优点是避免了排序，最大的缺点是要承担按索引次序读取整个表的开销，这通常意味着若是按随机次序访问行，开销将会非常大。如果 Extra 中看到 Using index, 说明Mysql正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行，它比按索引次序全表扫描的开销少很多。
3、range: 范围扫描，就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行，这比全索引扫描好一些，因为它用不着遍历全部索引，显而易见的范围扫描是带有 between 或者 where 子句里带有 > 的查询。当Mysql使用索引去查找一系列值时，例如 IN() 和 OR 列表，也会显示为范围扫描。然而，这两者是相当不同的访问类型，在性能上有重要的差异。
4、ref: 这是一种索引访问，它返回所有匹配某个单个值的行，然而，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常量，或者是来自多表查询前一个表里的结果值。ref_or_null 是ref之上的一个变体，它以为这Mysql必须在初次查找的结果进行第二次查找以找出NULL条目。
5、eq_ref: 使用这种索引查找，Mysql知道最多只返回一条符合条件的记录，这种访问方法可以在使用主键或者唯一性索引查找时看到，它会将他们与某个参考值做比较，Mysql对于这类访问类型的优化做的非常好，因为它知道无需估计匹配行的范围或在找到匹配行后再继续查找。
6、const, system: 当Mysql能对词项的某部分进行优化并将其转换成一个常量时，它将会使用这些访问类型，距离来说，如果你通过将某一行的主键放入where子句里的方式来选取此行的主键，Mysql就能把这个查询转换成一个常量，然后就可以高效的将表从连接执行中移除。
7、NULL： 辞职访问方式意味着Mysql能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引，例如，从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问表。possible_keys: 显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。这个列表是在优化过程的早期创建的，因此有些罗列出来的索引可能对于后续优化过程是没用的。key: 显示了Mysql决定采用哪个索引来优化对该表的访问，如果改索引没有出现在possible_keys 列中，那么Mysql选用它是出于另外的原因，例如，它可能选择了一个覆盖索引，哪怕没有WHERE子句。换句话说，possible_keys 揭示了哪一个索引能有助于高效的行查找。而 key 显示的是优化采用哪一个索引可以最小化查询成本key_len: 显示了Mysql在索引里使用的字节数，如果Mysql正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列，如果列的类型是字符串，那么无比要把字符集考虑进去。比如 charset=utf8，那么每个字符最多为3个字节，因为存储引擎并不知道具体的数据，所以每个字符统一按3个字节算，最后得出key_len，它指的是可能的最大长度，也就是字段定义的长度，而不关心具体存储的是多少。ref: 显示了之前的表在key列记录的索引中查找值所用的列或常量。Extra: 这一列包含的是不适合在其他列显示的额外信息。
1、Using index: 表示Mysql将使用覆盖索引，以避免访问表。
2、Using where: 这意味着Mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示 Using where，有时 Using where 的出现就是一个暗示，查询可受益于不同的索引。
3、Using temporary: 意味着Mysql在对查询结果排序时会使用临时表。

EXPLAIN EXTENDED

可以看到被优化后的SQL语句

> explain extended select * from task_user_log where instance_id = 68242 and user_id = 2148382;
> show warnings

创建高性能的索引

索引可以包含一个或多个列的值，如果索引包含多个列的值，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个包含一列的索引是大不相同的。

索引是在存储引擎层实现的，索引不同的引擎情况不一样。

1、B-Tree索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree，大多数MySQL引擎都支持这种索引。

实际上，很多存储引擎（比如 InnoDB）使用的是 B+Tree，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

具体参考：https://blog.csdn.net/raoxiaoya/article/details/134599251

2、哈希索引

基于哈希表实现的，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在MySQL中，只有Memory引擎显示支持哈希索引，这也是Memory引擎的默认索引类型，Memory引擎同时也支持B-Tree索引。

3、多列索引

索引合并：当你的查询语句没有找到合适的索引时，MySQL会使用索引合并技术来优化查询操作，但这恰恰说明你的所以建的很糟糕，可能需要建一个多列索引，而不是多个独立的单列索引。

索引合并就是MySQL需要耗费大量的CPU和内存来将多个单列索引的结果做合并运算。等价于

索引列顺序：对于一个多列索引，存储引擎在查询的时候会从左往右来排序，所以多列索引的顺序至关重要。应该将选择性更高的放在前面，这里的选择性高指的是这个条件返回的结果更少。

4、聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但 InnoDB 的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（Leaf Page）中，术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（那就是主键列），不过，覆盖索引可以模拟多个聚簇索引的情况。

如果没有设置主键列，InnoDB 会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引，这里主要关注 InnoDB。

一个 InnoDB 表中如果设置了主键索引（聚簇索引）和其他索引（称为二级索引），那么二级索引上面保存的是主键值（而不是行指针），通过这个值再去聚簇索引上找到对应的行数据。二级索引这样设计的好处是，减少了当出现行移动或者数据页分裂时二级索引的维护工作。

主键最好是有序的整数，不要使用类似于UUID这样的随机值，这会导致聚簇索引的插入变得完全随机，会形成大量的页分裂和页碎片，使得数据没有任何聚集特性，也会使索引占用空间变大。

5、覆盖索引

有时候可以将SQL改成覆盖索引来提升效率。

数据库碎片：对于 InnoDB 这种将数据存储在磁盘上的引擎，运行一定时间之后，难免会出现数据块的碎片问题，可以使用 OPTIMIZE TABLE命令来优化掉这些碎片；或者重新创建一张表将数据拷贝进去；或者执行一个不做任何操作的 ALTER TABLE 语句 ALTER TABLE table_name ENGING=innodb

性能优化

好的索引可以让查询使用合适的访问类型，尽可能的只扫描需要的数据行。

列表 IN() 的比较

在很多数据库系统中，IN() 完全等同于多个 OR 条件的子句，因为这两者是完全等价的，但在MySQL中则不是这样的，MySQL将 IN() 列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(logn)复杂度的操作，而如果转化成 OR 查询则复杂度是O(n)，所以 IN() 列表中数据越多，MySQL就相比其他数据库的优势越明显。

优化 COUNT() 查询

要区分清楚，统计行数COUNT(*)和统计某个列的数量COUNT(field)。在统计列时要求列值非NULL，否则不会统计在内。

如果MySQL知道某个列不可能为NULL值，那么Mysql内部会将COUNT(field)优化成COUNT(*)

锁的调试

show processlist 可以看到等待锁的进程，而不是哪个进程持有这些锁。

mysqladmin debug可以打印锁的信息到服务器的错误日志中。

服务器级别的锁要比存储引擎层的锁容易调试得多，各个存储引擎的锁互不相同，并且存储引擎可能不提供任何方法查看内部的锁。

使用 information_schema库查看锁的情况