MySQL执行计划 mysql执行计划是什么

1. 执行计划

1.1. 执行计划概念

执行计划是什么：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。
作用：分析你的查询语句或是表结构的性能瓶颈。
语法：Explain + SQL语句

执行计划输出内容介绍：表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询。

执行计划包含的信息：

1.2. 执行计划各字段详解

1.2.1. 执行计划-ID

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

规则：
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在

第一种情况 Id相同

第二种情况 Id不同

如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。第三种情况 Id相同又不同

id如果相同，可以认为是一组，从上往下顺序执行；

在所有组中，id值越大，优先级越高，越先执行

1.2.2. 执行计划-select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等复杂查询。

具体描述

例子：Simple：简单的 select 查询,查询中不包含子查询或者UNION

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为

SUBQUERY：在SELECT或WHERE列表中包含了子查询

EXPLAIN

select t1.,(select t2.id from t2 where t2.id = 1 ) from t1

在FROM列表中包含的子查询被标记为DERIVED(衍生)
MySQL会递归执行这些子查询, 把结果放在临时表里。
select t1. from t1 ,(select t2.* from t2 where t2.id = 1 ) s2 where t1.id = s2.id

1.2.3. 执行计划-table

显示这一行的数据是关于哪张表的

1.2.4. 执行计划-type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

需要记忆的 system > const > eq_ref > ref > range > index > ALL

**System：**表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

Const：表示通过索引一次就找到了。

Const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

读取本表中和关联表表中的每行组合成的一行。除 了 system 和 const 类型之外, 这是最好的联接类型。当连接使用索引的所有部分时, 索引是主键或唯一非 NULL 索引时, 将使用该值。

eq_ref 可用于使用 = 运算符比较的索引列。比较值可以是常量或使用此表之前读取的表中的列的表达式。

Ref：非唯一性索引扫描（二级索引），返回匹配某个单独值的所有行。

本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

Range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引

一般就是在你的where语句中出现了between、<、>、in等的查询。

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

Index:当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的索引库，而没有去查询数据。

All：Full Table Scan，将遍历全表以找到匹配的行。

1.2.5. 执行计划-possible_keys 与 key

possible_keys:可能使用的key索引。
Key:实际使用的索引。如果为NULL，则表示没有使用索引。

查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

根据key_len这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。
char和varchar跟字符编码也有密切的联系,
latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。（不同字符编码占用的存储空间不同）

1.2.6. 执行计划-key_len

字符类型

字符类型-索引字段为char类型+不可为Null时

字符类型-索引字段为char类型+允许为Null时

索引字段为varchar类型+不可为Null时

索引字段为varchar类型+允许为Null时

varchar(n)变长字段+允许Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2

日期和时间

datetime类型在5.6中字段长度是5个字节，

datetime类型在5.5中字段长度是8个字节

整数/浮点数/时间类型的索引长度
NOT NULL=字段本身的字段长度
NULL=字段本身的字段长度+1(因为需要有是否为空的标记，这个标记需要占用1个字节)

datetime类型在5.6及其以后版本中字段长度是5个字节，datetime类型在5.5中字段长度是8个字节

key_len 总结：
变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。

而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间。

复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

1.2.7. 执行计划-ref

Ref：显示索引的哪一列被使用了，如果可能的话，是一个常量。哪些列或常量被用于查找索引列上的值。

由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。

1.2.8. 执行计划-rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

1.2.9. 执行计划-Extra

Extra：包含不适合在其他列中显示但十分重要的额外信息

Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

MySQL中无法利用索引完成的排序操作称为“文件排序”

Using temporary

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

USING index

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找;

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

USING index

Using where

表明使用了where过滤

2. 执行计划

2.1. sql优化实战

2.1.1. 尽量全值匹配

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

2.1.2. 最佳左前缀法则-最左匹配

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

2.1.3. 不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

2.1.4. 范围条件放最后

存储引擎不能使用索引中范围条件右边的列

2.1.5. 覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

2.1.6. 不等于要甚用

mysql 在使用不等于(!= 或者<>)的时候有可能无法使用索引会导致全表扫描。

2.1.7. Like查询要当心

like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。

2.1.8. 字符类型加引号

字符串不加单引号索引失效。

2.1.9. OR改UNION效率高

2.2. Mysql架构层面优化

2.2.1 硬件方面的优化

mysql服务器端增大并发的连接数。如果服务器端并发连接数不够，可以增加并发连接数的配置。

show variables like '%max_connections%'

2.2.2 集群

2. 可以做基于主从复制



3. 主从复制+读写分离原理：



4. 
   



5. 基于mycat或者shading做都写分离
   基于mycat

cat schema.xml 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="BLADEX" checkSQLschema="true" sqlMaxLimit="100" dataNode="bladex"/>
        <schema name="BLADEX_FLOW" checkSQLschema="true" sqlMaxLimit="100" dataNode="bladex_flow"/>
        <schema name="XXL_JOB" checkSQLschema="true" sqlMaxLimit="100" dataNode="xxl_job"/>
        <dataNode name="bladex" dataHost="bladex" database="bladex" />
        <dataNode name="bladex_flow" dataHost="bladex_flow" database="bladex_flow" />
        <dataNode name="xxl_job" dataHost="xxl_job" database="xxl_job" />

        <dataHost name="bladex" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="bladexM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="111.com">
                        <readHost host="bladexS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="111.com"/>
                </writeHost>
                <writeHost host="bladexM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="111.com">
                        <readHost host="bladexS2" url="jdbc:mysql://10.1.33.27:3306" user="root" password="123.com"/>
                </writeHost>
        </dataHost>
        <dataHost name="bladex_flow" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="bladex_flowM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="111.com">
                        <readHost host="bladex_flowS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="123.com"/>
                </writeHost>
                <writeHost host="bladex_flowM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="111.com">
                        <readHost host="bladex_flowS2" url="jdbc:mysql://10.1.33.27:3306?useSSL=false" user="root" password="123.com"/>
                </writeHost>
        </dataHost>
        <dataHost name="xxl_job" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="xxl_jobM1" url="jdbc:mysql://10.1.33.21:3306?useSSL=false" user="root" password="1111">
                        <readHost host="xxl_jobS1" url="jdbc:mysql://10.1.33.26:3306?useSSL=false" user="root" password="1111"/>
                </writeHost>
                <writeHost host="xxl_jobM2" url="jdbc:mysql://10.1.33.22:3306?useSSL=false" user="root" password="1111.com">
                        <readHost host="xxl_jobS2" url="jdbc:mysql://10.1.33.27:3306?useSSL=false" user="root" password="1111.com"/>
                </writeHost>
        </dataHost>
</mycat:schema>

或者基于shading也是可以的。

3. 分库分表
   每个微服务一个库
   单个表达可以分表

单个数据库或者表数据量过大，那么可以选择分库分表的方式去优化。

垂直分库

水平方向的的分库分表