MySQL执行计划的目录
一、数据库引擎的比较
MySQL数据库中 MyISAM 和 InnoDB 引擎的特性介绍
对比项 | MyISAM | InnoDB |
---|---|---|
主外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
锁 | 支持表锁,操作一条记录的就会锁住曾哥表,不适合高并发的操作 | 支持行锁,只锁定操作该行的数据,不对影响其他行的操作,适合高并发操作 |
缓存 | 只支持缓存索引,不支持缓存真实数据 | 支持缓存索引和真实数据,对内存要求高,而且内存大小对性能有决定性的影响 |
表空间 | 小 | 大 |
关注点 | 性能 | 事务 |
默认安装 | 是 | 是 |
二、SQL执行慢的原因有哪些?
MySQL性能下降,SQL执行时间长、等待时间长(即SQL慢)的原因:
- 查询语句写得烂
- 建立的索引失效
- 关联了太多的Join
- 服务器调优及各个参数的设置(缓冲、线程数等)
三、SQL编写顺序和在数据库中SQL的执行运行
1.我们书写的查询的SQL语句的组成部分
SELECT DISTINCT
[select_list]
FROM table_name
[LEFT | INNER |RIGHT JOIN
table2_name
ON join_condition]
WHERE
[where_condition]
GROUP BY
[group_by_list]
HAVING
[having_condition]
ORDER BY
[order_by_list]
LIMIT
[limit_start limit_number];
2.MySQL数据库执行SQL的顺序:
第一步:先是找到需要在那些查询的表
FROM table_name
[LEFT | INNER |RIGHT JOIN
table2_name
ON join_condition]
第二步:执行where条件语句,在分组前对数据进行筛选
WHERE [where_condition]
第三步:执行group by 语句
GROUP BY [group_by_list]
第四步:执行having语句,对分组后的数据进行筛选
HAVING [having_condition]
第五步:执行select
第六步 :执行distinct 部分,对筛选出来二进行去重
第七步:对数据进行排序
ORDER BY [order_by_list]
最后一步:执行limit语句,进行输出
LIMIT [limit_start limit_number]
执行流程图
这里会用到索引的地方主要有三个部分:where语句、group by语句、order by语句。
四、常见的Join查询
1.内连接插叙
SQL语句
SELECT <select_list>
FROM table_one one
INNER JOIN
table_two two
ON one.key = two.key
插图
2.左外连接查询
SQL语句:
SELECT <select_list>
FROM table_one one
LEFT JOIN
table_two two
ON one.key = two.key
插图
3.右外连接查询
SQL语句
SELECT <select_list>
FROM table_one one
RIGHT JOIN
table_two two
ON one.key = two.key
插图
五、索引进阶
1.索引的概念、本质、优缺点
索引的概念:索引(Index)是是帮助MySQL高效获取数据的有序数据结构。这是在数据之外,数据库维护者满足特定查找算法的数据结构,这些数据以某种简单的方式引用(指向)数据,这样就可在这些数据结构上实现高级查找算法。
索引的本质:一种有序的数据结构。
简单理解:排好序便于快速查找定位数据的数据结构。
索引的优缺点
索引的优点:
- 提高数据的检索效率,降低数据库的IO成本能够使我们的查询更加高效。
- 通过对索引的数据进行排序,降低数据排序的成本,降低了CPU的消耗。
索引的缺点:
- 索引会占用一定的磁盘空间,因为索引本身也是数据,是以索引文件的形式存储在磁盘上的。
- 索引会降低更新表数据的速度,例如INSERT、UPDATE、DELETE,因此如果需要插入大量的数据,应该先删除索引,再插入数据,然后再重建索引。
- 同时也需要花费时间研究和建立合适的优秀的索引,或者优化索引。
2.索引的类型、分类、创建
索引有两种类型:B树索引(多路查找树)和 Hash索引,常说的索引是InoDB使用的B树结构的索引。
索引的目的在于提高效率,可以类比字典。
索引的分类:
- 单值索引:一个索引只包含单个列,一个表可以包含多个单列所以
- 唯一索引:索引列的值必须是惟一的,但允许是空值
- 复合索引:一个索引包含多个列
索引的创建:
# 方式1:
ALTER table_name ADD [UNIQUE] INDEX [index_name] ON (cloumn_name(length));
# 方式2
CREATE [UNIQUE] INDEX index_name ON table_name(cloumn_name(length));
六、索引检索的原理:
原理:如图在一棵B树中,在树中靠近根节点的取值范围越大,如模块1的取值范围是比它的子节点的取值范围的大的,我们查找一个数据,可以通过等分法快速定位到树中取值,例如我们寻找25这个数,可以通过在第一步可发现25在[20-32]之间,下面在模块三种又可以发现25在[21,28]之间,快速定位到25。
MySQL也是这样的进行查找的,先加载磁盘模块1,发现25在p2执行的磁盘模块中,第二次加载磁盘模块3,通过比较发现25在磁盘模块3中的p2执行的磁盘块中,再出加载磁盘5读出数据,在这里发生了三次IO。
而且在非叶子节点(磁盘模块1-4)中:只存储索引的指针,而不存储具体的数据信息,只有叶子节点(磁盘块1-9)才存储数据信息。
七、索引的应用
1、需要建立索引的情况:
- 表格中主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。
- 查询中统计或者分组字段。
原因是建立索引的同时,mysql会对索引进行排序。
2、不适合建立索引的情况:
- 频繁更新的字段不适合创建索引,索引会降低表中数据的更新速度。
- 不经常用到的索引
- 表中的记录太少的,建不建索引都一样,不影响查询速度
- 对于经常重复的字段也不适合建立索引
八、MySQL Query Optimizer
MySQL中有专门负责优化SELECT语句的优化器模块,主要功能是通过计算分析系统中收集到的统计信息,为客户端发送的Query提供MySQL自身认为最优的执行执行计划。
执行流程:
MySQL常见的瓶颈
- CPU:CPU饱和情况一般发生数据装入内存或者从磁盘上读取数据的时候。
- IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。
- 服务器硬件的性能瓶颈:通过top、free、iostat和vmstat来查看系统的性能状态。
九、MySQL的执行计划(explain)
1、explain的使用
使用explain 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,从而分析自己的查询语句或者表结构的性能瓶颈。
使用explian:
# 语法:
explain select语句;
# 示例,这里可是复杂的select语句
explain select * from table_name;
执行explain后,MySQL展示一个表格
2、explain的结果如何进行分析
接下是对表格中每个表头的关键字分析:
2.1 id 的说明:
id的情况 | 解释说明 |
---|---|
id相同 | Query语句的执行顺序由上至下执行 |
id不同 | Query语句如果有子查询,id的序号会递增,id值越大优先级越高,越先被执行。 |
id相同而不同 | 同时存在 |
2.1.1 id值不同的情况:
Query语句有子查询,id的序号会递增,id值越大优先级越高,越先执行。
2.1.2 id 值有相同有不同的情况:
id值相同,可以认为是同一组,执行的的顺序是自上而下,而id不同的,则是id值越大,优先级越高,越先执行
2.2 select 的说明
select_type的取值 | 取值说明 |
---|---|
simple | 简单的select查询,查询语句中不包含子查询或者union |
primary | 查询中如果包含任何复杂的子部分,最外围的查询会被标记为primary |
subquery | 在select语句 或者where列表中包含了子查询 |
derived | 在from列表值包含了子查询被标记为衍生(derived),MySQL会递归执行这些子查询,把结果存放在临时表里 |
union | 如果第二个select出现在union之后,则会被标记为union;如果union包含在from子句的子查询中,外层的select将被标记为drived |
union result | 从union表获取的结果的select |
2.3 table 的说明
table类型这是指明当前部分的操作的具体哪张表的。
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
type的取值与说明
type取值 | 解释说明 |
---|---|
const | 表示通过索引一次就找到了,const 用于比较prikey 或者unique索引,因为只匹配一行数据,索引很快,如果将主键放到where列表中,MySQL就能将查询换为一个常量。 |
eq_ref | 唯一索引扫描,对于每个索引键,表中只有一条记录匹配,常用用于主键或唯一索引扫面。 |
ref | 非唯一性索引扫面,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配单独值的行,然而,它可能会找到多个负荷条件的行,所以它应该属于查找和扫面的混合体 |
range | 只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引,一般就是在和的where语句中出现between and 、< 、> 、in 等的查询中,这种范围索引扫描要比全表扫描好,因为他只需从索引的某一点开始,到某一点结束,不用进行全表扫描 |
index | full index scan,代表只遍历整棵索引树,通常比all快,因为索引文件通常比数据文件小 |
all | full table scan,代表将遍历全表,从而查找到匹配的行 |
总之,一般来说得保证查询至少是range级别的,最好能达到ref。
说明:如果在上百万数据量的表格,一个查询语句出现了all,代表查询的时候是进行全表扫描的,那说明这个SQL语句必须优化。
2.5 possible_keys的说明
possible_keys:显示可能应用到这张表中一个或多个的索引,有可能用到的都会被列出来,但在实际的查询中不一定使用到;即理论上可能用的索引。
2.6 key的说明
key:显示实际用到的索引。
2.7 key_len 的说明
key_len:表示索引中使用的字节数,通过该列计算插叙中使用的索引长度,在不损失精确度的条件下,长度越短越好。
key_len显示的值为索引指端的最大可能长度,并非实际使用长度,即key_len是根据表定义计算得到的,不是通过表内检索出的。
2.8 ref 的说明
ref:显示索引的哪一列被使用,如果可能的话,是一个常数。那些列或者哪些常量被用于查找索引列上的值。
2.9 rows说明
rows:根据表统计信息及索引选用情况,大致估算出找到所需记录需要读取的行数。
2.10 extra 的说明
extra :包含不适合在其他列中显示但十分重要的额外信息
extra的取值 | 解释说明 |
---|---|
Using filesort | 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的已定义好索引顺序进行读取,MySQL中无法利用索引完成的排序操作称之为“文件排序” |
Using temporary | 使用了临时表保存中间结果,MySQL在对查询结果排序时使用到临时表,常见于order by排序和分组查询group by |
using index | 表示相应的select操作中使用到了覆盖索引(Covering Index),避免了访问表的数据行,效率不错;如果同时出现using where,表明索引被用来执行索引键值的查找,如果没有using where,表明索引用来读取数据而非执行查找动作 |
Using where | 表明使用了where过滤 |
Using join buffer | 使用了连接缓存,如果查询中经常出现Using join buffer可以将配置文件的join-buffer的值调大 |
impossible where | 表明了where子句的值总是false,不能用来获取任何元祖,即可能where的语句是前后矛盾的 |
select tables optimized away | 在没有group by子句的情况下,基于索引优化 min/max操作或者对于MyISAM存储引擎优化count(*)操作,不必等到执行阶段进行计算,查询执行计划生成的阶段即完成优化 |
Using temporary的示例:
Using index的示例:
十、覆盖索引、索引失效、总结
覆盖索引(Covering Index)的概念:就是select的数据列只用于从索引中就能够取得,不需要去读取数据行,MySQL可以定义索引返回select列表中的字段,而不必根据索引再次读取文件,换句话说就是查询列要被建立的索引覆盖。
索引分析:
范围性索引会到值索引失效,索引中的一些列使用了> 、< 、!=等比较运算符,会使索引失效。
在两表中索引的优化(join语句的优化):
- 永远要用小结果集驱动大结果集,即小表去驱动大表。
- 保证join语句中被驱动表上的join条件上的字段已经被索引。
- 单无法保证join语句中被驱动表上的join条件上的字段已经被索引且内存资源充足的前提下,不要太吝啬JoinBuffer的二设置。
索引失效的原因:
- 全值匹配我最爱。
- 最佳左前缀法则,如果索引了多列,要遵守最佳左前缀发着,指的查询从索引的最左前列开始,并且不跳过索引中的列。
- 不要在索引列上做任何操作(计算、函数、(自动or手动)类型转换)会导致索引失效而转向全表扫描。
- 存储迎请不能使用超出索引中条件范围右边的值。
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少使用select * 。
- Mysql 在使用不等于(!= 或者<>)的时候无法使用索引,会导致全表扫描。
- is null is not null 也无法使用索引。
- like通配开头(’%abc…’),MySQL的索引失效,导致全表扫描的操作。
- 字符串不加单引号,会有隐式转换,导致索引失效。
- 少用or 用它来连接时会导致索引失效。
小总结:
备注1:如果建立索引 index(abc),使用查询的时候where b = 1 and a=3 and c=10,MySQL能够自动识别并优化,使用索引进行查询。
备注2:建立索引的时候如果不指定排序的方向,默认是升序排序的,如果在查询的时候是有升有降(order by c1 asc ,c2 desc)这样的就会导致索引外的文件排序(using filesort),同升同降就不会。
备注3:group by 基本上都需要进行排序,会有临时表产生。
关于索引使用的一般性建议:
- 对于单键索引,尽量选择针对当前query过滤性好的索引。
- 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中位置越靠前越好。
- 在选择组合索引的时候,尽量选择可以能够包含当前query中where字句中更多字段的索引。
- 尽可能通过分析统计信息调整query的写法来达到选择适合索引的目的。
优化口诀:
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分号写最右,覆盖索引不写*;
不等空值还有or,索引失效要少用;
VAR引号不能丢,SQL高级也不难!