MySQL学习笔记一：SQL执行过程、事务隔离、索引

1、一条SQL查询语句执行过程

MySQL的逻辑架构图，可看到各个功能模块执行过程：

MySQL 可以分为 Server 层和 存储引擎 层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、 Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令：

mysql -h$ip -P$port -u$user -p

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就开始认证身份。

1. 如果用户名或密码不对，会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
2. 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

一个用户成功建立连接后，即使用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有新建的连接才会使用新的权限设置。

连接完成后，如果没有后续的动作，这个连接就处于空闲状态，可以用 show processlist 命令查看。

客户端如果太长时间没动静，连接器会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。

数据库里面有两种连接：

1. 长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
2. 短连接是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，因此在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

但是全部使用长连接后，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

解决这个问题的两种方案： 

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开 连接，之后要查询再重连。
2.  如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

MySQL 拿到一个查询请求后，会先查看查询缓存，之前是否执行过这条语句。之前执行过的语句及其结果会以 key-value对的形式，被直接缓存在内存中。key 是查询的语句， value 是查询的结果。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

但大多数情况下不要使用查询缓存：

1. 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
2. 对于更新压力大的数据库，查询缓存的命中率会非常低。
3. 除非业务是有一张静态表，很长时间才会更新一次。 比如，系统配置表。

可以将参数 query_cache_type 设置成 DEMAND，默认的 SQL 语句都不使用查询缓存。

如果要使用查询缓存的语句，可以用 SQL_CACHE 显式指定：

mysql> select SQL_CACHE * from T where ID=10；

而MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

分析器

分析器先会做“词法分析”。MySQL需要识别出SQL语句中的字符串分别是什么，代表什么。例如MySQL从”select”这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。

之后做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断SQL语句是否满足MySQL语法。

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引，或如在一个语句有多表关联(join)的时候，决定各个表的连接顺序。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

执行器

执行器阶段先判断用户对表T有没有执行查询的权限，如没就会返回没有权限的错误，如下：

mysql> SELECT * FROM T WHERE ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表时，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

对于有索引的表，执行的逻辑也差不多。

---

2、一条SQL更新语句执行过程

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）。

重要的日志模块：redo log

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术， WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（还是要写文件，只不过是顺序写，效率高）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下图所示。

• wirte pos 是当前记录的位置，一边写一边后移。 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据 文件。 write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。
• 如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

InnoDB这种机制保证了即使数据库异常宕机，之前的数据也不会丢失，这个能力称为crash-safe。

作用	确保事务的持久性，防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。redo log本身是为了弥补binlog的固有的缺点才做的。binlog不会实时写磁盘，特别是在事务的情况下，不commit binlog不会写入磁盘。
记录的内容	物理格式的日志，记录的是物理数据页面的修改的信息，redo log是按顺序写入redo log file的物理文件中去的。
什么时候产生	事务或者操作开始的时候就会产生
什么时候释放	当对应事务或操作的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

重要的日志模块：binlog

redo log是 InnoDB 引擎特有的日志，而引擎层上面的 server 层也有自己的日志，称为 binlog（归档日志）。

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系 统——也就是 redo log 来实现 crash-safe 能力。

作用	用于复制，在主从复制中，从库利用主库上的 bin log 进行重播，实现主从同步。（在异常重启的情况下，会用到redo log）
内容	逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句（增删改）。bin log有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条， 更新前和更新后都有。
什么时候产生	事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到 binlog中。(redo log是在事务开始之后就开始逐步写入磁盘)   因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了 bin log 的情况下，对于较大事务的提交，可能会变得比较慢一些。
什么时候释放	bin log 的默认保持时间是由参数 expire_logs_days 配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

redolog和binlog的不同

• 作用不同：redo log是保证事务的持久性的，是事务层面的；bin log作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。
• redo log 是 InnoDB 引擎特有的；bin log 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• 内容不同：redo log是物理日志，是数据页面的修改之后的物理记录；bin log是逻辑日志，可以简单认为记录的就是sql语句。
• redo log 是循环写的，空间固定会用完；bin log 是可以追加写入的。“追加写”是指 bin log 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
• 另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。
• 恢复数据的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的bin log。

update的流程

这里面涉及到了两阶段提交。将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。 MySQL通过两阶段提交过程来完成事务的一致性的，也即保证 redo log和 binlog 的一致性的，理论上是先写redo log，再写 bin log ，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

• 如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
• 简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

如何让数据库恢复到半个月内的任何一秒的状态？

如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 bin log，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

• 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
• 然后，从备份的时间点开始，将备份的 bin log 依次取出来，重放到中午误删表之前的那个时刻。

innodb_flush_log_at_trx_commit 各参数值具体含义：

• 0：每秒将log buffer的内容写事务日志并且刷新到磁盘。（耗时最短，风险大）
• 1：每个事务提交后，将log_buffer的内容写事务日志并刷新数据到磁盘。 （耗时最长，风险小）
• 2：每个事务提交，将log_buffer内容写事务日志，但不进行数据刷盘。

sync_binlog 参数值具体含义：

• 默认是 0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。 （性能最好，风险最大）
• 如果 >0，表示每 sync_binlog 次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是 sync_binlog=1 了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。

---

3、事务隔离

事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务。下面以 InnoDB 为例，总结一下事务。

隔离性与隔离级别

事务就有四种特性：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性），下面主要分析 I，即“隔离性”。

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

隔离得越严实，效率就会越低。因此很多时候，要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

• 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交：一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

•  可重复读 隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
•  读提交    隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
•  读未提交 隔离级别下直接返回记录上的最新值，没有视图概念。
•  串行化    隔离级别下直接用加锁的方式来避免并行访问。

Oracle 数据库的默认隔离级别其实就是 “读提交” ，而 MySQL 是 “可重复读” 。配置隔离级别方式是，设置 transaction_isolation 参数。比如要设置为“读提交”，则要将启动参数 transaction_isolation 的值设置成 READ-COMMITTED。可以用 show variables 来查看当前的值。

mysql> show variables like 'transaction_isolation';

事务隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。 

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

read-view 存储在undo日志（回滚日志）中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。什么时候删除undo日志呢？

答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。那什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

基于上面的说明，一个建议是尽量不要使用长事务。

比如说：某个业务操作需要批量插入数据，而且数据量还不少，如果这整个操作都包在一个事务里面，只有等到数据操作完了，DB连接才会被释放。一旦外部系统发起请求，并发调用这个操作，那么一下子将有大量的DB连接被持有而没有被释放掉，这个时候，如果还有其他请求到来，就很大可能获取不到数据库连接，请求也就只能等待，整个系统的吞吐量就大大下降。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。

除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

事务的启动方式

• 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
• set autocommit=0，这个命令会将这个线程的自动提交关掉(如果这个参数为1，那么每执行一个操作就会自动提交一次)。意味着如果只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到主动执行 commit 或 rollback 语句，或者断开连接。

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。建议总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

但是，使用显式方式开启事务会带来“多一次交互”的问题，因为每次开启事务都需要使用begin语句。为了避免这个问题，可以使用commit work and chain指令代替commit指令。这个指令的意思是提交事务并自动启动下一个事务，这样就省去了再次执行 begin 语句的开销。

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

如何避免长事务对业务的影响？

从应用开发端来看：

1. 确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。（将其改为1）
2. 确认是否有不必要的只读事务。有些框架会不管什么语句先用 begin/commit 框起来。这种只读事务可以去掉。
3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令， 来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

事务隔离补充知识点

1、在MySQL里，有两个“视图”概念：

• 一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。
• 另一个是 InnoDB 在实现 MVCC时用到的一致性读视图。事务的一致性视图可以理解为整个数据库的快照。即 consistent read view ，用于支持 RC 和 RR 隔离级别的实现。它没有物理结构，用于在事务执行期间定义“我能看到什么数据”。

2、“快照”在 MVCC 里是怎样工作的？

• InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
• 每行数据也是有多个版本的，每次事务更新数据，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务ID，记为 row trx_id 。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
• 也就是说，数据表中的一行记录，其实可能有多个版本（row），每个版本都有自己的 row trx_id。

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。

因此，InnoDB 代码实现上，一个事务只需要在启动的时候，找到所有已经提交的事务 ID 的最大值，记为 up_limit_id；然后声明说，“如果一个数据版本的 row trx_id 大于 up_limit_id，我就不认，我必须要找到它的上一个版本”。当然，如果一个事务自己更新的数据，它自己还是要认的。

有了这个声明后，系统里面随后发生的更新，是不是就跟这个事务看到的内容无关了呢？ 因为之后的更新，产生的新的数据版本的 row trx_id 都会大于 up_limit_id，而对它来说，这些新的数据版本是不存在的，所以这个事务的快照，就是“静态”的了。

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

3、在一个支持MVCC并发控制的系统中，哪些操作是快照读？哪些操作又是当前读呢？

以MySQL InnoDB为例：

• 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

  • select * from table where ?;

• 当前读：更新数据都是先读后写的，而这个读，只能读当前的值。特殊的读操作，插入/更新/删除操作，属于当前读，需要加X锁。

  • select * from table where ? lock in share mode;
  • select * from table where ? for update;
  • insert into table values (…);
  • update table set ? where ?;
  • delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。（第二和第三个语句加了S锁和X锁）

4、事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。 如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

• 在可重复读隔离级别下，只需要在事务开始的时候找到那个 up_limit_id，之后事务里的其他查询都共用这个 up_limit_id；
• 在读提交隔离级别下，每一个语句执行前都会重新算一次 up_limit_id 的值。

---

4、深入浅出索引

索引的常见模型：

　　哈希表：哈希表是一种以Key-Value存储数据的结构，只要输入key，就可以找到对应的value。哈希的思路很简单， 把值放在数组里，有一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。不可避免地多个key值经过hash计算可能会出现同一个值，处理这种情况的一种方法是，拉出一个链表。查找时先通过key计算hash值找到这个链表，然后按顺序遍历链表。需要注意的是，hash存储的value并不是递增的，所以哈希索引做区间查询的速度很慢，对于范围查询只能走全表扫描。所以，哈希表这种结构适用于只有等值查询的场景。比如Memcached及其他一些NoSQL引擎。

　　有序数组：有序数组在等值查询和范围查询场景中的性能都很优秀。如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据时就很麻烦，在有序数组中间插入一个记录，就必须挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎。

　　搜索树：二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。当然为了维持 O(log(N)) 的查询复杂度，需要保证这棵树是平衡二叉树，为了保证是平衡二叉树所做的操作的时间复杂度也是 O(log(N)) 。树可以有二叉，也可以有多叉。多叉树保证儿子从左到右递增。二叉树是搜索效率最高的，但是实际上大多数数据库存储并不使用二叉树。其原因是，索引不止存在于内存中，还要写到磁盘上。

你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据 块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于 一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间， 这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

N叉树由于在读写性能上的优点，以及适配磁盘的访问模式，已经被广泛应用。以InnoDB为例，这个N的值差不多是1200，这棵树高是 4 的时候，就可以 存 1200 的 3 次方个值，已经可以存储17亿左右的数据了。

InnoDB索引模型：

　　InnoDB 使用了B+树的索引模型。索引类型可以分为主键索引和非主键索引。

　　主键索引的叶子节点存储的是整行数据。在InnoDB中，主键索引也被称为聚簇索引（clustered index）。

　　非主键索引的叶子节点内容是主键的值，在InnoDB中，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树， 得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

　　因此，基于主键索引和普通索引的查询有很大的区别。如果使用主键索引ID来查询，只需要搜索ID对应的B+树。而如果使用非主键索引K来进行查询，需要先搜索K索引树，得到主键索引的值，再到主键索引树种进行搜索。

以默认的 Innodb 存储引擎为例：

作为主键时，uuid和自增相比较，自增更适合。原因：

1. uuid是无序的， 插入数据时，页的位置会发生变化，页分裂，速度慢。
2. uuid占的空间大，并且 Innodb 中，别的索引还都要包含主键的值，那么每个索引的空间也都会增大，占的空间大，需要读数据时一般会认为需要的io次数多。

索引维护：

　　B+树为了维护索引有序性，在插入新值的时候，需要做必要的维护。如果可以直接插入到末尾就会直接插入，否则则需要逻辑上挪动后面的数据，空出位置来。

       而如果要插入的位置的数据页已经满了，根据B+树的算法，需要申请一个新的数据页，然后挪动部分数据到新的页上，这个过程称为页分裂。整体空间利用率及性能都会受到相应影响。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。页合并的过程，可以认为是分裂过程的逆过程。

       基于以上索引维护内容，解释了为什么大多数建表语句都要求有自增主键。Not NULL PRIMARY KEY AUTO_INCREMENT。这样每次操作都会是追加操作，直接插入到末尾。另外，如果使用别的有业务逻辑的字段来做主键一是很难保证有序性。二来由于非主键索引储存的是主键的值，如果用较长的字段做主键，则普通索引叶子节点就会相应较大，普通索引所占用的空间也会变大。

       显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

　　当然事无绝对，在特定场景下也可能使用业务字段做主键更合适。如：1.只有一个索引，2.该索引是唯一索引。即典型的Key-Value场景。

重建索引问题

为什么要重建索引？

• 索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

通过两个 alter 语句重建索引 k，以及通过两个 alter 语句重建主键索引是否合理？

• 重建索引 k ：alter table T drop index k; alter table T add index(k);
• 重建主键索引： alter table T drop primary key; alter table T add primary key(id);

重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。

覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点：

1.     索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
2.     因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
3.     一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。
4.     Innodb的聚簇索引，覆盖索引对innodb表特别有用。(Innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)。

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

最左前缀原则：顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上

1. 注：如果第一个字段是范围查询需要单独建一个索引
2. 注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

例如当创建(a,b,c)复合索引时，想要索引生效的话,只能使用 a和a、b和a、b、c三种组合。

在建立联合索引时，如何安排索引内的字段顺序？

• 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
• 其次考虑的就是空间，比如name 字段是比 age 字段大的 ，那就建议创建一个(name,age) 的联合索引和一个 (age) 的单字段索引。

索引下推

在Mysql5.6之前，只能从最左前缀查询到ID开始一个个回表，到主键索引上找出数据行，再对比字段值。

Mysql5.6之后，引入索引下推的优化，可以在遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

总之在满足语句需求的情况下，尽量地减少访问资源是数据库设计的重要原则之一。我们在使用数据库的时候，尤其在设计表结构时，也要以减少资源消耗为目标。