MySQL笔记
文章目录
前言
本编主要针对mysql数据(默认Innodb存储引擎)展开。
一、mysql逻辑架构
1.1mysql架构的组成
一般，官方给出的mysql架构图如下图所示。由这几部分组成：连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件。
我们习惯会将mysql分成两大部分，一部分是数据服务层，一部分是存储引擎层。存储引擎是基于表的，而不是数据库。
1.2 mysql的执行流程
一条sql查询大概都经过哪些步骤？主要流程如下图所示。
1、首先客户端的请求发送到连接池组件。每个客户端连接都会在服务器进程中拥有一个线程。
2、查询是否已经有缓存，有则直接返回。不过已经被弃用了。
3、解析查询语句以创建内部查询结构（解析树）。
4、预处理：表或字段检查。
5、制定执行计划，确定SQL语句执行方案。是否走索引？走哪个索引？等等。
6、由执行器调用存储引擎接口查询，执行优化后的方案。
7、存储引擎返回结果，执行器判断结果是否符合，不符合丢弃结果，直到帅选出所有正确的结果（收到引擎层的结束信号）。
8、将结果集返回客户端。
二、存储引擎
mysql有多种存储引擎，默认是InnoDB存储引擎。常见的还有MyISAM存储引擎。此外还有NDB存储引擎、Memory存储引擎、Archive存储引擎等。
2.1innodb存储引擎
2.1.1 innodb存储引擎的架构
innodb是mysql5.5以后的默认引擎，该引擎适合大量短期的事务处理。支持ACID事务，支持compress、dynamic页格式，支持AIO、多回滚段。innodb存储引擎的架构或组成如下图所示。
2.1.1.1 后台线程
innodb引擎是多线程的模型，有多个不同的后台线程处理不同的任务。
2.1.1.1.1 Master线程
这是一个非常核心的线程，主要负责将缓冲区的数据异步刷新到磁盘，保证数据的一致性，包括脏页刷新、合并插入缓冲、undo页的回收等。master线程一直工作在多个循环中：主循环、后台循环、刷新循环、暂停循环。其中主循环有两大部分的操作：每秒钟的操作和每10秒钟的操作。
每一秒钟的操作包括：
1、日志缓冲刷新到重做日志文件，即时这个事务还没有提交（总是）。
2、合并插入缓冲（可能）。
3、至多刷新100个缓冲池中的脏页到磁盘（可能）。（判断脏页比例，默认超过90%会触发）
4、如果当前没有用户活动，则切换到background loop（可能）。
每十秒钟的操作包括：
1、刷新100个缓冲池中的脏页到磁盘（可能，脏页比例超过70%）。
2、合并至多5个插入缓冲（总是）。
3、将日志缓冲刷新到磁盘（总是）。
4、删除无用的undo页（总是）。
5、刷新100个或者10个脏页到磁盘（总是，脏页比例小于70%）。
2.1.1.1.2 Purge线程
innodb1.1之后，Purge线程用来回收不再需要的undo日志，以此来减轻Master线程的工作。
2.1.1.1.3 Page Cleaner线程
innodb1.2.x之后，引入Page Cleaner线程用来分担Master线程的脏页刷新操作。
2.1.1.2 内存
2.1.1.2.1 缓冲池
对于数据库中页的修改操作，首先修改缓冲池中的页，在以一定的频率刷新到磁盘。刷新磁盘并不是每次更新都发生而是通过一种称为Checkpoint的机制刷新回磁盘。
缓冲池采用基于LRU的淘汰策略，不过不完全是。即最频繁使用的页在LRU队列的前端，最少使用的页在LRU的尾端。当缓冲池不能读取到新缓存的页时，先释放尾端的页。
2.1.1.2.2 重做日志缓冲
innodb引擎会先将日志信息存放在缓冲中再写入重做日志文件。一般不需要设置很大，默认8M已经可以满足大多数应用了。
2.1.2 innodb的特性
2.1.2.1 插入缓冲insert buffer
插入缓冲其实不是缓冲池的一个组成部分，和数据页一样也是物理页的一个组成部分。如果插入的主键是有序的，不需要随机读直接插入叶子节点速度是很快的。但是如果有非唯一辅助索引存在，则插入可能需要大量随机读，会导致性能下降。于是mysql设计出了insert buffer，先将非唯一辅助索引存入插入缓冲，然后再以一定频率进行insert buffer和辅助索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中）。
2.1.2.2 两次写doublewrite
doublewrite并不是用来提升性能而是用来增加可靠性的。一个页是16k，如果缓冲区的脏页触发写磁盘操作，此时若写了2k便宕机则该页文件将损坏无法读取；而重做日志记录的是偏移量加数据，并不能修复还原损坏的页文件。于是就有了两次写的设计：先将缓冲中的脏页（有变化的数据页或索引页）复制到一个doublewrite buffer的缓冲中（大小2M），再通过doublewrite buffer分两次一次1M顺序地写入共享表空间的物理磁盘上（连续128个页，即2个区大小2M的物理磁盘空间），由于是顺序写所以此过程开销会相对较小。最后，才将doublewrite buffer的页写入各个表空间文件中，此时的写入则是离散的。
这样做，如果发生崩溃重启，则innodb存储引擎可以先从共享表空间中找到该页的副本复制到表空间文件，再应用重做日志。
2.1.2.3 自适应哈希索引AHI
哈希是一种比较快的查找方法，一般情况下时间复杂度为O(1)。而B+树查找的时间复杂度取决于树高，生产上一般是34层，故需要34次的查询。于是innodb存储引擎会监控对表上各索引页的查询，自动根据访问频率和模式来自动的为某些热点页建立哈希索引。自适应哈希索引只适用于等值查询，不支持其它查找类型，如范围查询。
2.1.2.4 异步IO
innodb引擎刷新磁盘采用异步IO进行，即AIO。好处是线程无需等待磁盘同步结束，而是交由操作系统完成。需要操作系统本身支持AIO。
2.1.2.5 刷新邻接页
innodb引擎支持刷新邻接页，即在刷新脏页时，检查该页所在区的所有页是否有其它脏页，那么一起刷新。这样做的好处是可以合并多个IO为一个IO操作。不过现在普遍使用固态硬盘，有着较高的IOPS；另外不怎么脏的页进行写入后会不会很快又变成脏页。为此，可以通过配置参数innodb_flush_neighbors的值为0关闭此特性。
2.1.3 innodb的表结构
从innodb存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。结构如下图所示。
表：所有数据都存放在表空间中，默认情况下有一个共享表空间ibdata1。
段：表空间是由段组成的。常见的段有数据段、索引段、回滚段等。数据段即为B+树的叶子节点（Leaf node segment），索引段即为B+树的非叶子节点（Non-leaf node segment）。
区：区是连续页组成的空间，大小都是1M。在默认情况下，innodb存储引擎页的大小为16KB，即一个区中一共有64个连续的页。
页：页是innodb存储引擎磁盘管理的最小单位。常见的页类型有：数据页、索引页、undo页、事务数据页、系统页、插入缓存位图页。
行：innodb存储引擎是面向列的，也就是说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200行，即7992行。
2.1.4 行记录
我们平时看到的行记录不单只有数据本身，还有一些字段列用来标识某些信息。
2.1.4.1 Compact行记录格式
Compact行记录是mysql5.0引入的，其结构如下图所示。
变长长度字段列表：变长长度字段列表是多个长度字段组成的，用来表示行中所有VARCHAR类型的长度，以逆序方式排列，每个字段长度占1-2字节（因为VARCHAR最大长度65535）。
NULL字段标志位：NULL字段标志位用来表示该行数据中是否有NULL值，也是以逆序排列，NULL列对应位(Byte)置1，通常占1字节。NULL字段标志位一个好处是可以使得NULL列的数据不占用空间。
记录头：记录头信息占用5个字节，包含的信息较多。比如：delete_mask ：该行是否已被删除。next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。record_type：表示当前记录的类型，000表示普通记录，001表示B+树非叶子节点记录，010表示最小记录，011表示最大记录。
在记录头后面并不是真实数据，还隐藏了两个字段：tansactionID和roll_pointer，分别是事务ID和回滚指针。会在后面事务一章详讲。
如果行记录没有显示创建主键，则在记录头后面会有一个占6字节的row_id字段用来表示主键。
2.1.4.2 Redundant行记录格式
Redundant行记录是mysql5.0之前使用的行记录方式。与Compact行记录不同的是其没有NULL字段标志位，记录头信息占用6字节，其它的基本一样。这就意味着在NULL处理上两者是不一样的，Redundant行记录对于VARCHAR类型的NULL值一样是不占用存储空间的，但是对于CHAR类型的NULL值是占用存储空间的。
2.1.4.3 Compressd和Dynamic行记录格式
Compressd和Dynamic与上面两者的主要区别在于对BLOB类型的处理不一样。在数据页中只存放20字节的指针，实际数据放在off page中，而上面的Compact和Redundant会存放768个前缀字节，后再带上偏移量，指向行溢出页。
Compressd的另一个功能是会对行记录进行zlip的算法压缩，因此对BLOB、TEXT、VARCHAR这类大长度数据进行非常有效的存储。
2.2 myisam存储引擎
与innodb存储引擎相比，myisam存储引擎不支持事务，但是性能会高些；myisam存储引擎不支持行锁，只到表级锁，增改删会锁表；myisam存储引擎不支持外键约束。
三、文件结构
3.1 undo日志文件
回滚日志文件用来保证事务的原子性，记录的是需要回滚时的信息。比如：在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。
3.2 redo日志文件
重做日志文件是用来保证事务持久性的。事务提交时会先将数据写入到重做日志文件里。redo log记录的是对数据页的修改，在缓冲池中的数据页未被刷新到磁盘前，所有修改都会记录在redo log里。这样做的好处是避免了更频繁的脏页刷盘动作，而redo log属于追加式的，顺序写的性能损耗较小。
其实redo log也并不是直接刷盘的，毕竟多事务并发情况下的IO次数也很大。而是用到一个上面提到的重做日志缓冲。由参数innodb_flush_log_at_trx_commit参数控制刷盘策略，可取的值有：0、1、2，默认值为 1，如下：为0时，不会主动触发写入磁盘的操作；为1时，每次事务提交时，都将重做日志缓冲里的 redo log调用write() 持久化到磁盘。为2时，每次事务提交时，都将重做日志缓冲里的 redo log 调用fsync()延迟持久化。
3.2 binlog文件
binlog文件记录了数据库上的所有改动，以二进制的形式保存在磁盘中。与redo log、undo log不同的是，binlog是SQL服务层的二进制文件，前两者是存储引擎的。
四、并发管理和锁
只要有多个查询需要同时修改数据就会产生并发控制问题。mysql有两个级别的并发控制：服务器级别与存储引擎级别。
锁也可以分为lock和latch，latch是轻量级的，保护内存数据结构，持续到临界资源释放，比如读写锁和互斥量，可以通过指令show engine innodb mutex来进行查看；而lock是保护数据库内容的，持续整个事务过程，有死锁检测机制，可以通过指令show engine innodb status来查看当前数据库中锁的请求。
下面介绍mysql的一些主要锁：
| 锁的类别 | 锁的粒度 | 适用场景 | 备注 |
|---|---|---|---|
| 表锁 | 整张表 | 适用于大多数语句都是读的情况，也适用于读写混合的语句。 | 开销小，但写操作一多容易阻塞 |
| 行级锁(Record Lock) | 行 | 能最大程度的支持并发处理。 | 开销大，是在存储引擎实现的。 |
| 间隙锁(Next-Key Lock/Gap Lock) | 和谓词索引有关；可能是一行也可能是多行 | ||
| 多版本号控制 | 行级锁的变种，在很多情况下可避免加锁操作 | 目前的大多数事务都是使用行级锁+MVCC | 开销小 |
| 自增锁 |
五、事务管理
5.1 mysql事务介绍
事务就是一组sql语句作为一个工作单元以原子方式处理。要么全部成功要么全部失败。mysql的事务管理可以认为采用行级锁和MVCC技术相结合。
mysql存储引擎会把每条增删改语句都隐性包装在一个事务中执行并自动提交(AUTOCOMMIT)，可以使用命令{SET AUTOCOMMIT=OFF}关闭。如果有多条语句需要开启事务可以使用命令{BEGIN TRANSACTION … COMMIT/ROLLBACK}或者{start transaction with consistent snapshot}。
mysql事务满足ACID事务的标准。在事务开启时分配一个事务标识符(DB_TRX_ID)，修改记录时将向undo日志写入如何恢复更改的记录，并且将事务的回滚指针(roll_pointer)指向undo日志，从而保证原子性。mysql的更改会先写到redo日志，避免引起过多的磁盘io，最后会由一个后台进程更新到磁盘。如果事务已提交，存储引擎重启时可根据redo日志进行恢复，从而保证持久性。隔离性则由MVCC（利用记录的版本链和ReadView）或锁机制来保证。当原子性、持久性、隔离性都有保证，便能保证一致性。
5.2 隔离级别以及解决的问题
ANSI SQL中定义了四种隔离级别，如下表所示。不同的隔离级别用以解决不同的事务并发执行问题（脏读、不可重复读、幻读），隔离级别越高事务并发执行问题越少但性能开销越大。
| 隔离级别 | 说明 | 脏读(读取未提交)(Dirty Read) | 不可重复读 | 幻读(Phantom Promble) | 加锁读 | 读策略 |
|---|---|---|---|---|---|---|
| READ UNCOMMITTED(未提交读) | 在事务中可以看到其它事务中还没有提交的修改。 | 是 | 是 | 否 | 否 | |
| READ COMMITTED(提交读) | 自己事务未提交前所做的任何修改对其它事务都是不可见的。自然不支持重复读，避免出现幻读。 | 否 | 是 | 是 | 否 | 一致性非锁定读（总是读取最新一个版本的数据） |
| REPEATABLE READ(可重复读) | 在提交读基础上解决不可重复读问题，保证同一事务中多次读取相同数据行的结果是一样的。 | 否 | 否 | 是 | 否 | 一致性非锁定读（总是读取事务开始时的版本的数据） |
| SERIALIZABLE(可串行化) | 强制事务按顺序执行（需要加读写锁） | 否 | 否 | 否 | 是 | 一致性锁定读 |
这里解释下事务并发执行问题：脏读、不可重复读、幻读。
脏读：事务A读取到事务B修改但未提交的数据，如果此时事务B出错回滚则事务A会拿到一个未修改成功的错误数据。
不可重复读：事务A读取事务B未修改前的数据data1后事务B修改数据为data2，此时事务A还未提交再执行一次读指令会获得data2数据返回，这就导致在事务A里两次相同查询却得到不同的结果。比如：库存为1，事务A
幻读：事务A统计分数80-100的人数10人，此时事务B插入一个用户分数90的数据，事务A再次统计分数80-100的人数则为11人，前后两次统计的人数不一致。当同一个查询在不同的时间产生不同的结果集。
5.3 MVCC的工作原理
首先我们了解一下什么是ReadView。ReadView是实现提交读和可重复读两种隔离级别下的MVCC的一个要素之一。可以简单的理解为是一个事务快照，用以后续与版本链进行对比。在提交读隔离级别下，每个语句执行前都会生成一个ReadView；在可重复读隔离级别下，启动事务时会生成一个ReadView，然后整个事务期间都使用这个ReadView。ReadView的组成如下图所示。
所谓的版本链其实是指被事务版本号标记的前后历史数据构成的链条。在聚簇索引中隐藏了两个列，分别是trx_id和roll_pointer。trx_id记录此条数据被最后改动的一次事务id；roll_pointer为回滚指针，指向undo日志的上一个事务修改记录。
5.3.1 可重复读的工作原理
列举下在可重复读隔离级别下MVCC的工作原理。如图左侧所示。
1、事务A开启事务查询id=1的数据返回sum=100。
2、事务B开启事务修改id=1的sum=200，但未提交。
3、事务A查询id=1的数据依然返回sum=100。
4、事务B提交事务。
5、事务A查询id=1的数据依然返回sum=100。
首先开启事务A和事务B时会创建如图片右上所示的ReadView图。当执行步骤1时发现trx_id=10，小于事务A的ReadView的min_trx_id，于是认为上一个事务已经提交，当前sum=100是最新数据，于是返回100。步骤2事务B修改数据，会先将数据写入undo日志，并将数据行记录的事务id自增1，回滚指针指向旧数据。步骤3事务A再来查询id=1的数据，发现trx_id=11，大于min_trx_id小于max_trx_id,说明此时读到的数据是已经启动但未提交的事务修改的。于是顺着版本链（回滚指针）向上查找，一直找到trx_id小于事务A的ReadView的min_trx_id的最近一条，所以返回结果依然是sum=100。最后事务B提交后，事务A依然进行id=1的查询，返回的sum依然是100而不是200。这是因为在可重复隔离级别下，使用的都是开始事务时创建的同一个ReadView，逻辑同步骤3。
5.3.2 提交读的工作原理
与上面可重复读相似，区别是（事务A）每次读取数据时都会生成一个新的ReadView。区别在于步骤5的查询会返回sum=200。在步骤4事务B提交数据后，事务A新创建的ReadView的creator_trx_id=14，min_trx_id=14，所以在去查询时trx_id=11，小于13，于是返回sum=200。
5.3.3 幻读问题
mysql默认是REPEATABLE READ(可重复读)。解决了脏读、不可重复读的问题，但没有解决幻读的问题。之所以没有使用串行化隔离级别，是认为串行化需要不停的加锁会带来极大的性能损耗。所以如果想要解决幻读问题，某些情况想要强一致性锁定读，可以使用如下语法：
-- 对读取记录增加一个X锁，其它事务不能对已锁定的行加上任何锁
SELECT ... FOR UPDATE;
-- 对读取记录增加一个S锁，其它事务可以对已锁定的行加S锁，但是加X锁则会被阻塞
SELECT ... LOCK IN SHARE MODE;
六、监控调优等工具的使用
6.1 EXPLAIN
EXPLAIN语句用于获取执行计划信息。具体的各个参数表示什么意思可以参考官网的解释：https://mysql.net.cn/doc/refman/8.0/en/explain-output.html。这里讲几个比较常用和重要的。
possible_keys和key：possible_keys表示可以从中选择的索引来查找该表中的行，不代表实际使用的索引，而key才是实际决定使用的索引。这也就意味着key总是比possible_keys少，哪怕没有。同时key_len表示决定使用的索引的长度。
rows和filtered：rows表示mysql认为它必须检查以执行查询的行数。filtered表示按表条件过滤的表行的估计百分比。最大值为 100，这意味着没有发生行过滤。值从 100 开始减少表示过滤量增加。 rows显示检查的估计行数，rows×filtered显示与下表连接的行数。例如，如果rows是1000并且filtered是50.00（50%），则与下表连接的行数为1000×50%=500。
**type：**type表示连接类型，这个还是比较有参考意义的。如下表：
| const | 该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。只读一次速度非常快。 |
|---|---|
| eq_ref | 除了system和const类型，这是最好的连接类型。当连接使用索引的所有部分并且索引是主键或唯一索引时使用它。 |
| ref | 从该表中读取具有匹配索引值的所有行。如果使用的键只匹配几行，这是一个很好的连接类型。 |
| index_merge | 此连接类型表示使用索引合并优化。 |
| range | 仅检索给定范围内的行，使用索引来选择行。range可以在使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LINK, 或IN()运算符将键列与常量进行比较时使用。 |
Extra：额外信息。列举一些如下表：
| Using index | 仅使用索引树中的信息从表中检索列信息，而无需执行额外的查找操作来读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。 |
|---|---|
| Using index condition | 通过访问索引元组并首先测试它们以确定是否读取完整的表行来读取表。这样，除非有必要，否则索引信息用于延迟（ “下推” ）读取全表行。 |
| Using join buffer | 连接的表被部分读入连接缓冲区，然后使用缓冲区中的行来执行与当前表的连接。 |
| Using where | WHERE子句用于限制哪些行与下一个表匹配或发送给客户端 。Extra除非您特别打算从表中获取或检查所有行，否则如果值不是 Using where并且表连接类型是 ALL ，您的查询可能有问题。 |
七、schema的设计与管理
7.1数据类型的选择
表数据类型的选择目标是小和简单。
| 类型 | 存储大小（位） | 说明 | |
|---|---|---|---|
| 整数类型 | TINYYINT / BOOL | 8 | UNSIGNED修饰的话存储范围提高一倍。可以指定宽度，但存储和计算，INT(1)和INT(20)没有区别。 |
| SMALLINT | 16 | ||
| MEDIUMINT | 24 | ||
| INT | 32 | ||
| BIGINT | 64 | ||
| 浮点数类型 | FLOAT | 32 | 计算都是以double类型计算 |
| DOUBLE | 64 | ||
| DECIMAL | 2-17字节 | DECIMAL(a,b) a是整数部分的最大个数，b是小数点后的个数。可用于存储精确的实数。FLOAT和DOUBLE也可用此方法表示。 | |
| 字符串 | CHAR | 定长字符串 | 适用于存储定长短字符串，如MD5；适用于存储单字节，比如单位。CHAR(1)只占用1字节。 |
| VARCHAR | 不定长字符串，尾部有\0 | 用于存储可变长度字符串，需要额外1-2字节记录长度。当使用内存临时表时，多余未用的长度也会占用空间，所以建议指定合适大小VARCHAR(n)。 | |
| 超文本 | TEXT | 用来存储较大数据，如果过大会使用“外部”专门区域存储，行内存储1-4字节指针。 | 采用字符方式存储；不可使用索引。 |
| BLOB | 采用二进制方式存储；不可使用索引。 | ||
| 枚举 | ENUM | 1-2字节 | 存储整数对应有序的字符串，查询返回依旧是字符串。适用于固定少量几个常量且无需频繁变动的列，但要注意与其它类型联接时的效率。 |
| 日期和时间 | DATETIME | 8字节 | 精确到微秒，与时区无关。可保存1001-9999年的时间。 |
| TIMESTAMP | 4字节 | 范围小，只能表示19700101-20380119。会根据时区变化。插入更新可自动设置成当前时间。也可以使用UNSIGNED INT代替，表达到2106年。 |
7.2 主键类型的选择
| 主键类型 | 优点 | 缺点 | 注意点 |
|---|---|---|---|
| 自增id（AUTO_INCREMENT） | 简单快速，内存占用小。可保证顺序写，最大化避免内存碎片。 | 高并发插入可能导致间隙锁竞争，反而影响效率。不利于表的合并或扩展；分库分表后主键会重复。 | 类型选择int,bigint，考虑是否会超过最大行。需要考虑表扩展或迁移时慎重考虑。存在高并发插入情况慎用或不用。 |
| 雪花算法 | 随机且呈现出递增增长，利于数据迁移。 | 依赖系统时钟 | 分布式系统中，每台机器一定要分配不同的workid，需要保证系统时间的一致性。 |
| UUID | 足够随机，利于数据迁移。 | 无序。不利于插入操作，有易于造成页分裂的可能。（不够聚簇） | 存储时应该删除破折号。或者用UNHEX()转换成32位的数字。插入操作较多时不建议使用。 |
八、索引和查询优化
创建索引一般是针对提高查询速度而言的，同时平衡其它DML的性能损耗。
8.1 索引
首先明确，创建索引(index)就是为了检索。索引优化是对查询优化最有效的手段。那么在不考虑内存问题和其它DML性能损耗的情况下，三星索引>二星索引>一星索引。所以在建表的时候，选择合适的schema就尤为重要。有哪些索引？怎么选择索引？如何验证索引是否合适？如何维护索引和表？（索引的创建与优化仅针对大表而言，小表为了避免回表全表扫描会更具有优势）
先介绍一些索引概念：
| 名称 | 介绍 | 适用范围 | 语法 |
|---|---|---|---|
| B+Tree索引 | 适用于全键值、键值范围、键前缀查找(只支持最左前缀) | ||
| 自适应哈希索引 | 当某些索引值被频繁访问时，在B+Tree基础上，在内存中创建一个哈希索引。此过程是全自动化的。 | 热点键值（自适应，可配置文件手动关闭）；适用于联表列。 | |
| 链表索引 | |||
| 位图索引 | |||
| 全文索引 | 适用于查找文本中的关键字(MATCH AGAINST操作) | ||
| 聚簇索引 | innodb默认使用 | ||
| 前缀索引 | 前缀索引的结果应该接近于完整索引的结果 | ALTER TABLE <> ADD KEY <indexName(schema(n))> |
选择索引的一个重要指标便是选择性：不重复的索引值(也叫基数)和数据表的记录总数的比值。（唯一索引的选择性为1，前缀索引的基数应该接近于完整列的基数）可以通过如下计算获得：
mysql> SELECT COUNT(DISTINCT col_3)/COUNT(*) FROM data_1.table_1;
也可以利用mysql> SHOW INDEX from [table]里的Cardinality属性做一个预估(八个数据页的分析得出)
一般将选择性最高的索引放在索引列最前列。但具体问题还要具体分析，最好的方式就是使用EXPLAIN命令+{USING [index]}/{IGNORE [index]}查看，不停地找到满足各个复杂查询的单个或多个索引。是要创建一个索引还是多个索引？是创建单列索引还是多列索引？这些都没有固定的公式的。（知识会忘记，请记住方法，如果方法也容易忘记，至少请记住思想。如果怕忘记思想，请升华为智慧）
mysql单列索引会用到"索引合并"策略，在一定程度上使用多个单列索引进行扫描并将结果合并。有OR的联合(Union)、AND条件相交、前两者组合。但如果扫描到选择性不高的索引，将会有大量数据返回并在内存中做合并处理，严重情况下会比全表扫面恶劣。可以在语句中加入{IGNORE [index]}让优化器忽略掉某些索引。选择多列索引往往比选择多个单列索引效果要好，但要特别注意索引失效的问题。
通常，表联接的字段是要设置索引的。不建议多级表联接，如果必要可尝试拆成星型联接。
索引的维护
1、在(业务)逻辑上去除冗余和重复索引。去除多余的索引。可以用命令查看未使用的索引。mysql> SELECT * FROM sys.schema_unused_indexes;
2、更新表的索引统计信息：ANALYZE TABLE <>;
3、在单次大批量写操作后，整理索引和数据碎片：OPTIMIZE TABLE <>;或者重新导出导入数据。
4、尝试用：CHECK TABLE <>;查看是否有损坏的表或索引；尝试用REPAIR TABLE <>;修复。
8.2 查询
查询优化方法（不做、少做、快速地做）：1、索引优化和库表结构优化。2、减少查询访问数据，减少返回数据（没用到的字段不返回；limit限制）。3、重构查询语句：拆分复杂查询成多个单查询；分解联接查询或重新排序；常数替换或代数等价变换；其它等等。最后，依然可以根据EXPLAIN（重点关注type列、ref列和rows列）具体问题具体分析。
如何判断查询的效率：
参考指标可以有：响应时间（服务时间+排队时间）、扫描的行数、返回的函数。
具体套用公式或算法可以参考“快速上限估计”算法或者QUBE快速估算法。
查询优化小技巧
1、如非指定列，一概使用count(*)（不统计NULL）。
2、联接查询的联接列上有索引。
3、GROUP BY和ORDER BY建议使用同一个索引。
4、多在应用程序进行聚合处理，可减少临时表的创建。
5、使用UNION默认会消除重复行，如果要包含重复行，一定要使用UNION ALL。
九、复制和备份
9.1 备份
mysql的备份可以通过使用命令“mysqldump --databases db1 db2 >dump.sql”输出指定数据库或全部数据库的dump备份文件。可以通过dump文件进行数据库移植。
备份可以分为全量备份、增量备份、日志备份。mysql大部分是通过二进制日志文件备份实现增量备份的。
9.2 复制
复制是协调主从一致性的一种高可用性解决方案。一般其工作步骤如下：
1、主服务器将数据更改记录到二进制文件中（binlog）。
2、从服务器接收主服务器的二进制文件并复制到自己的中继日志文件中。异步返回成功结果。
3、从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据一致性。
需要注意的是接收binlog文件写入中继日志文件后会返回响应，与读取中继日志文件更新引擎数据是异步分开执行的。
附录1 常用术语解释
ACID事务：原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)。
MVCC(Multiversion Concurrency Control)：多版本并发控制。
DML(Data Manipulation Language)：数据操纵语句，关键字主要包括 insert、delete、udpate 和select 等。
DDL(Data Definition Languages)：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。
DCL(Data Control Language)：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。
一星索引：“窄索引”。即索引扫描过滤结果的行数尽量小。
二星索引：“排序星”。索引的排序和谓词的排序相同。即返回结果无需在内存中再排序。
三星索引：“覆盖索引”。无需进行回表操作，即索引结果便是查询结果。
脏页：缓冲池中被修改的页还没有刷新到磁盘中，由此造成内存中页和磁盘中页数据的不一致。
脏读：指一个事务可以读到另一个事务未提交的数据。
参考资料
1、官网：https://mysql.net.cn/doc/refman/8.0/en/union.html
2、《高性能MYSQL》第四版 ——Silivia Botros、Jeremy Tinley
3、《MySQL技术内幕–InnoDB存储引擎》——姜承尧
4、优秀博客参考：https://xiaolincoding.com/mysql/，https://pdai.tech/md/db/sql/sql-db.html
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
