MySQL调优？ref:列与索引的比较显示关联的字段。如果使用常数等值查询，则显示const(联合索引中的列用到几个常数

MySQL的执行流程

说到这个首先得看MySQL的执行流程：

简单来说就是MySQL查询时会首先查询缓存，如果存在就不会执行直接返回了，如果不存在会执行接下来的sql解析操作，而默认的缓存是关闭的；Navicat查出的是空我也不懂，还好命令行敲出来的是关闭的；

我这里只简单勾一下，主要说的不是缓存，因为MySQL8后不用关心缓存，我这里主要的是sql语句；对于开发中最常见的问题就是你这sql优化的吗，效率怎么样......等问题；

MySQL的优化

说到优化，首先想到的必须是explain，想查看sql语句的执行效率，先explain走起，想看看索引有没有生效？有没有回表查询？覆盖索引在哪？统统执行流程走一遍；

explain概要描述：（explain字段概念描述来自网上搜索）
id:选择标识符
select_type:表示查询的类型
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较显示关联的字段。如果使用常数等值查询，则显示const(联合索引中的列用到几个常数等值查询就显示几个const)，如果是连接查询，则会显示关联的字段
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
extra:执行情况的描述和说明

explain列说明：

一、select_type查询中每个select子句的类型

(1) SIMPLE(简单SELECT，不使用UNION或子查询等)（一般都是它）

(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

二、type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行，ALL是从磁盘中读取

index: Full Index Scan，index与ALL区别为index类型只遍历索引树，index是从索引中读取

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

三、extra

Using where SQL使用了where条件过滤数据
Using index SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录（简单来说就是查询的字段都在索引中不需要去查行记录）

Using index condition 确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录（回表操作）

Using filesort 这类SQL语句性能极差，需要进行优化。比如在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是在order by的列上添加索引，避免每次查询都全量排序

Using temporary 需要建立临时表(temporary table)来暂存中间结果。这类SQL语句性能较低，往往也需要进行优化。典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集

Select tables optimized away：For explains on simple count queries (i.e. explain select count(*) from people) the extra section will read "Select tables optimized away." This is due to the fact that MySQL can read the result directly from the table internals and therefore does not need to perform the select（MySQL官方解释：大概意思就是对于简单计数MySQL可以直接从表内部读取，因此不需要执行select）

首先我也先用存储过程创建一定量数据如下:

CREATE PROCEDURE xxx()
begin
    declare 定义一个初始值n integer default 1;
    while n<=10000 do
    insert into person values(n, concat('随便输入属性值',n), n, date_sub(NOW(), interval n second));
    set n=n+1;
    end while;
end

比如说我现在这张表mytest的列名分别是id、name、age、createtime其中id是主键

MySQL（Innodb引擎）的优化首先想到的是索引，那么Innodb的索引是怎么一回事呢？首先Innodb的索引可分为两大类一种叫做聚簇索引（clustered index ），一种叫做非聚簇索引（secondary index），一般来说聚簇索引是按照主键来创建的，创建的规则是：

如果定义主键，Innodb会利用主键来生成其聚簇索引

如果没有定义主键则第一个not NULL unique（非空唯一索引）列是聚簇索引

如果上面两种都没有，则Innodb会隐式的创建一个自增的列(rowid)作为聚簇索引

非聚簇索引的话就是普通二级索引，二级索引一般建立在经常查询的列上，与聚簇索引的区别就是查询除了叶子结点上的这几列还有回表用的主键（这里是回表二次查询）（Innodb默认主键以外的索引都是二级索引）；

举个例子：上面的mytest的主键是id，二级索引是name

先加上二级索引

alter table mytest add index(name);

根据普通索引name查询id，name，前面说到普通索引的话会查询叶子节点上的列和主键，正好与需要的字段匹配，查看执行过程的extra为using index符合覆盖索引执行效率高，

再看

我这傻玩意extra居然为空，但总结其实是

Using index condition

，它确实命中索引，但age值却不在索引树上，依然需要通过访问具体的记录去访问值（回表查）；

再看：

上面是10000条数据，但是explain时执行影响的行数却是10230，这里是MySQL的读取问题，MySQL进行io读取数据时并不是数据展示的行数据，而是以页（包含多个数据）为基本单位，执行explain时，MySQL会进行估算造成不准。可以Analyze table tablename来收集表的统计信息（大数据量、多变更操作），除非执行计划不准确，否则不要轻易执行该操作，如果是很大的表该操作会影响表的性能。。。（不挖了，再挖就出不来了。。。）

联合索引就不说了，到处都看到。。。说吧，突然就不会了：

drop index name on mytest ;//去除索引

添加id、name、age、联合索引

alter table mytest ADD INDEX test_index(id,name,age);//添加联合索引

通过name查询possible_keys为空，但实际使用却用到了联合索引，extra中使用了过滤条件和满足覆盖索引，不用回表

再看添加个查询字段：type变为all，extra为using where（全表过滤）就因为多了一个普通字段

再次说明：type的性能ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）；全表扫性能糟差；

对了，联合索引当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然a,c组合也可以，但实际上只用到了a的索引，c并没有用到！见下下图（此时我已新增link列，将索引顺序变为age,nam,link方便测试）

上下两张图对比一下会发现ac索引a生效，c不生效，（如果bc 索引不生效下下图）

大概简单说下。。。（不深入，再深入就废了。。。总之就是按顺序来）

对于联合索引和单列索引等等各种索引的操作问题究竟是使用哪个，MySQL会有他的一套机制按照他的最小成本来使用，比如主键索引和联合索引，我一个主键就解决了查询，联合自然就排不上用场（此例不知恰不恰当）

最左前缀匹配：MySQL在查询时对于等值的判断就是识别度越高的越放在最左边，因为最左匹配，所以联合索引创建时将识别度高，高频率使用的放前面

change buffer ：

（仅用于普通索引，对唯一性索引无效，因为唯一性索引肯定要读数据页做唯一性判断，此时操作了内存，那么操作内存会更快，没必要经过change buffer）

首先需要知道数据是以文件形式存储的，MySQL会将我们的表生成一个个.ibd文件。

你的每一次对表的操作就是对该.ibd文件的操作，而这种操作就带来了相应的物理读取。（.ibd文件除了存储我们的数据，还存储了相关索引）对于读请求，内存缓冲池(buffer pool)（以页为单位，和开头的缓存区分，缓冲池收集各种操作记录，缓存是读操作（叨一下））会减少磁盘io操作，当我们进行insert的时候，我们的数据会发生变化，索引也会发生变化,,,看图吧，一目了然，使用场景看图就知道了，适用于写多读少的操作，因为每一次读都会操作merge合并，会得不偿失，有了change buffer，就可以记录操作定期批量进行磁盘io操作，顺带说下什么时候会触发merge，1.访问数据页会merge，2.后台有线程会定期merge，3.数据库关闭的时候会merge。。。。

show variables like '%change_buffer%'//查看change buffer，具体参数可以百度

数据库水真深，我。。。傻。。。了。。。还有好多好多，好多好多。。。就先这么多。。。有机会再补。。。（再勾一句，以上所有测试只针对MySQL8，其他版本不清楚）

第一次掘金发文，排版啥的不太清楚，我也是直接从微信公众号上复制过来的，有些格式可能丢了，如有需要可移步微信公众号查看

https://mp.weixin.qq.com/s/moeg65IlvVsgYfVHF5RWgA

撒花，完结！！！

一个没有梦想的梦想家