MySQL

1、在MySQL中更新数据的时候，BufferPool,RedoLogBuffer,RedoLog,BinLog,UndoLog都有那些作用？事务提交和脏数据是怎么回事？

UndoLog在数据库执行的时候，发生异常的时候能够做数据回滚。

写入RedoLog主要是为了解决由于系统故障，在重启数据库的时候能够基于redoLog去补全数据文件中的数据

BinLog记录了SQL的逻辑操作，同时还可以借助my2sql，binlog2sql或者MyFlash等工具完成数据的修复，而且可以用它来做主从数据同步，或者实时数据仓库，实时数据湖都需要用到binlog日志

事务提交，主要是为了保证数据不丢失，这里的脏数据就表示的是，缓存页和数据页上的数据不一致

2、我认为最关键的是修改硬盘上的数据，在执行SQL语句的时候，直接更新硬盘上的数据行吗？

通过BufferPool,RedoLogBuffer以及后台的IO线程向磁盘中刷入数据。通过效率去考量，异步的不定时的执行硬盘数据的修改可以提升MySQL的并发能力，同时可以减少磁盘的读写次数，而RedoLog还借用了顺序写的机制，可以极大地提高写入redolog的速度

假设 执行 Update xxx-table Set name=‘小明’ Where id=‘1002’；Commit;

来自：https://www.bilibili.cm/video/BV1Dr4y197ai/?spm_id_from=333.337.search-card.all.click&vd_source=25624397e7273e73d03463e30081a37a

 # 归档日志：记录所有的修改，以及原有的数据。truncate只记录一条语句，速度较快。记录的类似于是SQL语句
 # 重做日志：用于数据崩溃，将数据进行持久化，本该直接写入硬盘，但直接写入需要随机写，比较耗时间，将其写入到另外一个日志文件中，追加写，速度较快，在发生断电等突发事件后，可以将数据库中的数据与日志文件相对照，如果有问题，则对照日志文件修改。
 物理上的操作，修改了数据块的某个位置的某条记录改成了什么样子
 # 回滚日志：记录未操作事务之前的数据
 # 慢查询日志：平时是不开放的，自己可以打开，将执行时间较长的sql写入
 # 查询日志：平时是不开放的，自己可以打开，将所有的sql写入
 # 错误日志：记录错误。

更新SQL执行过程

首先，可以确定的说，查询语句的那一套流程，更新语句也是同样会走一遍。执行语句前要先连接数据库，这是连接器的工作。在表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表T上所有缓存结果都清空。接下来，分析器会通过此法和语法解析知道这是一条更新语句。优化器决定要使用ID这个索引。然后，执行器负责具体执行，找到这一行，然后更新。与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log(重做日志)和binlog(归档日志)。

重做日志（redo log）:

重做日志是记录数据页内容更改的预写日志，它为应用于数据页的所有更改提供持久性。在服务崩溃的情况下，它用于恢复对已修改但尚未刷新到磁盘的数据页的修改，即崩溃恢复（crash-safe）。redolog是循环写，写到末尾是要回到开头继续写的。

归档日志（binlog）:

归档日志属于逻辑日志，记录数据库更改的“事件”，例如表创建操作或对表数据的更改，归档日志有两个重要目的：

1.主从复制，从主同步通过主数据库发送归档日志中的“事件”给备份数据库进行数据同步。

2.数据恢复：还原数据到误操作之前的某个备份，然后重新执行备份后记录的归档日志中的事件到误操作事件

这两种日志有以下三点不同：

• redolog是InnoDB引擎特有的；binlog是MySQL的SErver层实现的，所有的引擎都可以使用。

• redolog是物理日志，记录的是“在某个数据也上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1”。

• redolog 是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

有了对这两个日志的概念性理解，再来看执行器和InnoDB引擎在执行update语句时的内部流程：

1. 执行器先找引擎取ID = 2 这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID = 2 这一行所在的数据也本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在是N+1,得到新的一行数据，在调用引擎接口写入这行数据。

3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redolog里面，此时redo log处于 prepare状态。然后告知执行器执行完成了，随时可以提交事务。

4. 执行器生成这个操作的binlog,并把binlog写入磁盘。

5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交状态，更新完成。

两阶段提交：

为了让两份日志之间的逻辑一致，redo log的写入拆成了两个步骤：prepare和commit,这就是“两阶段提交”。如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

MySQL的体系结构

MySQL Server使用可插拔的存储引擎体系结构，该体系结构使存储引擎可以加载到正在运行的MySQL服务器或从正在运行的MySQL服务器上卸载。MySQL的体系结构示意图如下：

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

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

• 存储引擎负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎，不同存储引擎的表数据存取方式不同，支持的功能也不同。不同的存储引擎共用一个Server层，也就是连接器到执行器的部分。现在最常用的存储引擎是InnoDB,它从MySQL5.5.5版本开始成为了默认存储引擎。可以在建表时指定引擎，如：create table t (i int)ENGINE = MEMORY;

  • InnoDB存储引擎：它是MySQL5.5版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。

  • 可以在建表时指定引擎，如：create table t(i int)ENGINE =MEMORY;

  • MyISAM存储引擎：在MySQL5.5版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。

  • Memory存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果mysqld进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用Memory存储引擎。

  • NDB存储引擎：也叫做NDB Cluster存储引擎，主要用于MySQL Cluster分布式集群环境，类似于Oracle的RAC集群。

  • Archive存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

Select执行过程

下面的是MySQL的基本架构示意图，从中可以清楚地看到SQL语句在MySQL的各个功能模块中的执行过程。

1.连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的：

mysql -hip -P $port -u$user -p

连接命令中的mysql是客户端工具，用来跟服务端建立连接。在完成经典的TCP握手后，连接器就要开始认证你的身份，这个时候用的就是输入的用户名和密码。

如果用户名密码认证通过，连接器会到权限表里面查出当前用户拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，也不影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，可以在show processlist命令中看到它。show processlist 的结果中的Command列显示为“Sleep”的这一行表示系统里面的空闲连接。

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

应用端优化：

• 最好池化连接，从而减少连接创建、关闭的次数

• 最好控制长连接的最大生存期，以来避免超出wait_timeout指定的时限被MySQL强制断开，形成无效连接，二来释放连接中的内存（执行过程中临时使用的内存是管理再连接对象里面的）。如HikariCP中的maxLifetime属性。

2.查询缓存 MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询语句，value是查询结果。如果查询能够直接在这个缓存中找到key,那么这个value就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

查询缓存的失效非常频繁，只要有一个对一个表的更新，这个表上所有查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，拿这张表上的查询才适合使用查询缓存。

好在MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显示指定，像下面这个语句一样：

 mysql> select SQL-CACHE * from T where ID=10;

MySQL8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

3.分析器 如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。分析器先回做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串是什么，代表什么。MySQL从输入的“select”这个关键字识别出来，这是一个查询语句。他也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。做完了这些识别之后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断输入的这个SQL语句是否满足MySQL语法。

4.优化器 经过了分析器，MySQL就知道要做什么了。再开始执行之前，还要经过优化器的处理。

优化其实在表里面有多个索引的时候，决定使用那个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句时执行两个表的join:

 mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

即可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2,再判断t2里面的d的值是否等于20。也可以先从表t2里面取出c=20的记录的ID值，再根据ID值关联到t1,在判断t1里面的c的值是否等于10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

5.执行器 MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，要先判断一下对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示。

 mysql> select * from T where ID=10;

如果有权限，就打开表继续执行。打开表的时候，优化器就会根据表的引擎定义，去使用这个引擎提供的接口。比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1.调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；

2.调用引擎接口取这个表的“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。至此，这个语句就执行完成了。

对于与索引的表，执行逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

MySQL优化

SQL优化的本质就是用最少的资源最快完成查询任务。影响SQL执行效率的因素很多，可以在以下几个层面进行优化：

1. 应用程序级调优：SQL语句调优，管理变化调优

2. 数据库结构调优：分库、分表

3. 实例级调优：内存、数据结构、实例配置

4. 操作系统交互：I/O、SWAP、Parameters

   作为应用开发人员，应该关注的是数据库调优、SQL调优，以及JDBC调优，其中由于SQL的多样性，SQL调优又是尤为困难的。

优化概括

• 创建表的时候，应尽量建立主键，逐渐最好使用自增的整数。大数据表删除，用truncate table代替delete。

• 在常用查询字段(where)、关联字段(join)、排序字段(order by)、分组字段(group by)上创建索引，避免大表全表扫描；避免索引失效，对常用查询尽量做到索引覆盖(避免回表)和索引下推(避免数据返回Server过滤)

• 合理使用索引，在OLTP(联机事务处理过程)应用中一张表的索引不要太多。组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片；数据量小及字段值重复较多的字段不要创建索引。

• 尽量少用关联子查询，这种查询会消耗大量的CPU资源；检查一个结果集（外表）的记录是否在另外一个结果集（字表）中存在匹配记录尽量使用in或者exists子查询，而不是连接查询；多表查询的查询语句中，选择小表作为驱动表。

• 尽量多用commit语句提交事务，可以及时释放资源、解锁、释放日志空间、减少管理花费。尽量避免写过于复杂的SQL，不一定非要一个SQL解决问题；

• 在允许部分操作失败的前提下（如果失败，人工介入），尽量减小事务的粒度；

MySQL索引

索引介绍

索引是帮助MySQL高效获取数据的数据结构。

MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用（指向）表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。这种数据结构就是索引。

简单理解为“排好序的可以快速查找数据的数据结构”。

索引数据结构

1. 哈希表

   哈希表的英文叫“Hash Table”,我们平时也叫它“散列表”或者“Hash表”。哈希表用的是数组支持按照下标随机访问数据的特性，所以散列表其实就是数组的一种扩展，由数组演化而来。可以说，如果没有数组，就没有散列表。

   哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。

   哈希表这种结构适用于只有等值查询的场景，is [not]null也能使用索引

2. B树

   B树是一种树数据结构，是一个n叉树，每个节点通常有多个孩子，一颗B树包含根节点、内部节点和叶子节点。B树通常用于数据库和操作系统的文件系统中。

   B树实际上是一个很大的家族，因此在学习的时候，需要留意所提及的B树具体是那种B树。B树可以细分多个子类别，在我们熟悉的数据结构中，二叉树容易与B树混淆。二叉树和B树都是平衡树，但二叉树它的每个节点里中能存储一个键值，而B树中的每个节点都存储了大量键值，因此树不会太高。

3. 上图是一个典型的B树，他的节点里存储了很多键值，这些键值也是有序排列的，比如图中的1，2，5，7，9，12，16，18，21。每个键值都会指向目标数据。

   B+树是B树最常见的一个字类别，下图就是一个典型的B+树。B+树的特点是叶子层节点存储了全部键值，这些键值再指向目标数据，比如图中1，2，5，9，12，18，21。内部节点中重复存储部分键值，但不含数据指针。叶子节点层有一个正向的遍历列表。

4. 为什么经常使用B树来作为数据库的索引结构？

   实际上是B+树。B+树非常适用于数据库的索引结构，他的最主要目的就是减少磁盘IO，每个节点对应磁盘中的一个页，访问节点对应一次磁盘IO。因此我们会希望树非常扁，即树的高度非常少，因为树的高度就是访问磁盘IO的次数。

   为什么使用B+树？因为B+输在节点不用存储数据或者数据指针，因此每个节点里能存储的键值要比B树多，存储的键值多，B树就会变得非常扁，高度会非常低，磁盘IO就更少。因此我们选用的经常是B+树。

   还有一个原因是我们经常需要范围查找，比如上图中要找到有2~9的数据，我们把2的数据找到后，沿着右侧方向就能把5和9也找到，因为再页的节点层有右上指针，因此我们不再需要从跟出发，而是直接向右移动就能找到。

   B+树支持等值和范围查询，而且能够很好地配合磁盘的读写特性，减少单词查询的磁盘访问次数。

InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以B+树索引的形式存放的，这种存储方式的表称为索引组织表。也就是说创建表时MySQL会自动创建主键索引，主键索引的叶子节点存放的是行数据。而其他索引的叶子节点存放的是主键，其他索引也称为二级索引。

如果建表时未指定主键，则使用第一个非空的唯一索引列作为索引列，如果连非空的唯一索引都没有则自动添加一个自增的6字节长度的字段来作为索引列。

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

• 如果语句是select * from T where ID=500,即主键查询方式，则只需要搜索ID这颗B树；

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

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引分类

根据索引的具体用途，MySQL中的索引在逻辑上分为以下5类：

1.主键索引

主键索引是一种唯一性索引，不允许值重复或者值为空，并且每个表只能有一个主键。主键索引又叫聚集索引（其它索引称为非聚集索引），或主索引（其它索引称为二级索引）

注意：由于主键在每个索引中都存在，所以大的列不适合做主键。一般情况下建议创建一个整形的自增主键，同等长度下可以提供更多的值，自增可以避免页分裂。

2.普通索引

普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是INDEX或KEY。

3.唯一性索引

唯一性索引是不允许索引列具有相同的索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一性索引。创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据重复。

注意：在业务允许的情况下尽量使用普通索引，在有唯一索引的表中插入数据时需要读取索引来判断数据的唯一性

4.全文索引

全文索引只能在VARCHAR、Char、TEXT类型的列上创建，并且只能在InnoDB和MyISAM存储引擎上创建

注意：全文索引对中文只做了单字分词，可以加入分词插件

5.空间索引

空间索引主要用于地理空间数据类型GEOMETRY。

性能优化

1.索引覆盖

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上取数据。如：

 drop table if exists users;
 ​
 create table users(
     user_id int auto_increment primary key,
     user_name varchar(20) unique,
     password varchar(20)
 );
 -- 查看索引
 show index from users
 -- 查看执行计划
 explain select user_id,user_name from users where user_name='test1'

可以在执行计划的extra列中看到Using index,表示使用了索引覆盖，避免了回表（由于数据user_id,user_name在user_name列的索引中都存在，所以不需要通过主键从主键索引中获取数据）

插入测试数据的存储过程

 DROP PROCEDURE IF EXISTS idata;
 ​
 DELIMITER ;;
 CREATE PROCEDURE idata()
 BEGIN
     DECLARE i INT DEFAULT 1000;
     DECLARE user_name VARCHAR(20);
     WHILE i > 0 DO
         SET user_name = CONCAT('test',i);
         INSERT INTO users(user_name,password)   VALUES(user_name,'123456');
         SET i = i -1;
     END WHILE;
 END;;
 delimiter ;
 call idata();

2.最左前缀原则

创建表并添加数据和索引

 drop database if exists pratice ;
 create database practice;
 use practice;
 drop table if exists test;
 create table test(
     id int not null primary key auto_increment,
     col1 int,
     col2 int,
     col3 int
 );
 create index idx_clc2 on test(col1,col2);
 ​
 #递归添加数据
 set session  cte_max_recursion_depth = 9999999;
 insert into test(col1, col2, col3)
 with recursive  d as(
     select  1 n,1000*rand() c1,1000*rand() c2,1000*rand() c3
     union all
     select n+1,1000*rand(),1000*rand(),1000*rand()
     from d
     where n < 100000
 )
 select  c1,c2,c3 from d;

 #查看执行计划
 #当执行以下两条语句时，使用索引来进行扫描。
 explain
 select *
 from test t where col1 = 100 and col2 = 100;
 explain
 select *
 from test t where col1 = 100;
 #而当执行以下语句时又会进行全表扫描
 explain
 select *
 from test t where col2 = 100;

B+树这种数据结构，可以使用索引的“最左前缀”来定位记录，所以可以通过调整索引列的顺序来复用索引。

注意：

• 创建的组合索引在查看索引结果中显示的是两条记录

• like %1这种过滤条件不符合最左前缀原则，一样会导致不能从索引中定位记录

• 如果where条件中通过and连接了col1和col2列，哪个在前是无所谓的，优化器会自动选择

#优化器是智能的，选择成本更低，有可能表中所有数据均比100大，这个时候在索引中找再回表，比较麻烦
 explain
 select *
 from test t where col1 > 100;
 #走了索引
 explain
 select *
 from test t where col1 > 10000;
 #使用索引
 explain
 select *
 from employee e
 where email like 'abc%';
 #全表扫描
 explain
 select *
 from employee e
 where email like '%bc%';

3.索引下推

MySQL5.6引入了索引条件下推优化（Index Condition Pushdown(ICP)）,可以减少存储引擎必须访问基表的次数以及MySQL服务层必须访问存储引擎的次数。索引条件下推优化是指如果where子句中的过滤条件可以使用索引中的列来过滤，则MySQL服务器会将这部分条件下推到存储引擎。存储引擎通过使用索引中的列来过滤推送条件，并且在满足此条件的情况下，才从基表中读取行。

索引下推使用条件：

• 只能用于range、ref、eq_ref、ref_or_null访问方法；

• 只能用于InnoDB和MyISAM存储引擎及其分区表；

对存储引擎来说，索引下推只适用于二级索引（非聚簇索引）；

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于聚簇索引来说，数据和索引是在一起的，不存在回表一说。

引用了子查询的条件不能下推；

引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

4.前缀索引

前缀索引是指基于字段的前一部分内容创建的索引。BLOB、TEXT或者很长的VARCHAR类型字段必须使用前缀索引，因为MySQL对索引的长度有限制。MySQL5.7默认不能超过3072字节。

前缀索引的优点是可以节省空间，提高索引性能，但缺点是会降低索引的选择性。

索引的选择性是指不重复的索引值（基数）和表中的数据总量的比值，范围处于（1/总数据量）到1之间。选择性越高的索引查询效率越高，因为可以过滤掉更多的数据。主键和唯一索引的选择性是1。 

select 
 count(distinct left(email,4))/count(distinct email) left4,
 count(distinct left(email,5))/count(distinct email) left5,
 count(distinct left(email,6))/count(distinct email) left6
 from employee
 ​
 left4 |left5 |left6 |
 0.7200|0.9200|1.0000|

示例中，当前缀长度达到6时，选择性和索引整个email没有区别。因此，可以基于该字段创建一个前缀索引：

create index idx_employee_email on employee(email(6));

注意：

• 前缀索引中保存的索引字段值是不完整的，所以索引覆盖会失效

• 如果字符串本身前缀的区分度不够，可以采用以下方式中的一种：

  • 创建前缀索引，存储数据时使用reverse函数反转字符串，当然查询时也需要反转条件；

  • 在原有表中增加一列，用来存储查询列的hash值（crc32()）,然后在hash字段创建索引，由于加了字段，所以有额外的存储消耗，而且不同的值可能有相同的hash值，所以查询条件中除了hash字段还需要加入原始字段，会有额外的计算消耗。

索引失效

1.条件字段函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走索引搜索功能。如日期字段的索引，如果按照月份去搜索，则无法根据索引去过滤数据：

 drop table if exists users;
 ​
 create table users(
 user_id int auto_increment primary key,
 user_name varchar(20) unique,
 password varchar(20) default null,
 register_time datetime default now(),
 index index_register_time(register_time)
 );
 ​
 insert into users(user_name,password) value('user1','123');
 insert into users(user_name,password) value('user2','123');
 insert into users(user_name,password) value('user3','123');
 ​
 #查询8月份注册的用户
 #查看执行计划，type=all\key=null\Extra=Using where,表示全表扫描，没有使用索引
 explain select user_id,user_name from users where month(register_time)=8;
 # 查看执行计划，type=range、key=index_register_time、Extra=Using index condition,表示索引条件下推到引擎，引擎基于索引进行了索引方位扫描
 expalin select user_id,user_name from users where (register_time >= '2020-8-1' and register_time<'2020-9-1');

注意：使用索引和使用索引过滤数据是两个概念，如：

 explain select user_id from users where month(register_time) = 8

结果解析：type=index、key=index_register_time、Extra=Using where;Using index,表示使用了索引index_register_time,但是没有基于索引去过滤数据（index表示索引全扫描），使用这个索引只是由于这个索引比主索引的数据量要小

2.隐式类型转换

如果条件中的字面常量的数据类型和索引列的数据类型不一致，会导致索引列中数据的数据类型的转换，而不同数据类型的排序规则是不同的，索引中的有序性失效。如：

 explain select user_id,user_name from users where register_time >= 2020-8-1

结果解析：

type=all、possible_keys=index_register_time、key=null,Extra=Using where,表示可能使用索引index_register_time,但是没有使用，而是进行了全表扫描

注意：2020-8-1表的是一个整型值202081

3.隐式字符编码转换

如果两个表的关联字段至少有一个创建了索引，这个时候如果执行关联查询是会去使用索引的，但是如果这两个字段的字符编码不一致，由于要进行字符编码转换，可能会导致索引失效。

4.使用前置通配符

WHERE子句中，如果索引列所对应的值第一个字符由通配符开始，索引将不被采用。如：

explain select user_id,user_name from users where username like '%u';

闲笔

1.MySQL分数据库，由于数据库中表太多,库中无关联的表分开，关联的表放到两个数据库，启动分布式事务。

2.分表，可以横向，也可以纵向。

3.读写分离：增删改 A机器，执行操作之后，写入归档日志，并向B机器发送消息，解读归档日志，有一定延时

查询 B机器

4.对于读多写少，对于一致性要求低的数据，可以使用Redis

5.数据源池化原因：TCP三次握手，四次挥手，进行操作只有两次。

6.MySQL：支持事务，技术成熟，易上手，数据只能有一份，否则难以保证事务一致性。

7.Redis:缓存，提高效率。

8.集成化管理日志：将多个系统连接到消息中间件，使用扇形交换器，绑定一个队列。将队列中的消息放到ES中。