Explain执行计划

一条查询语句经过Mysql查询优化器的基于成本和规则的优化之后生成的一个所谓的计划就是执行计划，这个执行计划展示了执行查询的方式
通过执行计划我们可以分析出：
1.表的读取顺序
2.数据读取操作的类型
3.哪些索引可以使用
4.哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询

执行计划详解

image.png
id：select的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
select_type： 表示对应行是简单还是复杂的查询。
table：表示 explain 的一行正在访问哪个表，即表名。
type：表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref
possible_keys：可能用到的索引。
key：实际用到的索引。
key_len：实际使用到的索引的长度。
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
rows：预估的需要读取的记录条数。
Extra：一些额外的信息。

使用示例

表结构

actor表：
image.png
film表：
image.png
film_actor表：
image.png
通过上面三张表对Explain用法作详细分析

table

无论我们的查询语句有多复杂，里面包含多少张表，到最后也是需要对每张表进行单表查询，Mysql规定Explain语句输出的每一条记录都对应着某个单表的访问方法，该记录的table列代表着该表的表名。
image.png

id

有一个select关键字，就会有一个id
单select的查询语句
image.png
连接查询
有两行是因为涉及到两张表，但是只有一个select关键字，所以id只有一个，都是1
image.png
包含子查询
该条语句有2个select，所以就有2个id
image.png
包含Union子句
两个select对应两个id，但是又多出来一个，是因为id=1是查询f1产生的；id=2是查询f2产生的；id=null是查询f1和f2构建的临时表产生的
image.png

select_type

SIMPLE
简单的select，不使用union及子查询
Explain使用与详解 - 图10
image.png
PRIMARY：最外层的select查询
UNION：union关键字后面一个select查询，不依赖于外部的查询结果集
UNION RESULT：union结果集
Explain使用与详解 - 图12
SUBQUERY
子查询中的第一个select查询不依赖于外部的查询结果集
image.png
DERIVED
用于from子句中有子查询的情况，Mysql会递归这些子查询，把结果放在临时表中
image.png

type

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref
system
对表中只有一条记录的表进行查询并且表的数据统计是精确时就会出现这种type
Inkedimage_LI.jpg
const
对主键(primary key)或者唯一性二级(unique key)索引进行等值匹配时，如果主键或者二级索引是由多个字段构成，那么参与构建索引的字段都要进行等值匹配，才会出现const
image.png
eq_ref
主键(primary key)或者唯一性二级(unique key)索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type
image.png
ref
当使用普通的二级索引进行等值匹配时，就会出现ref
image.png
range
范围扫描通常出现在in(),between,>,<,>=等操作中。使用一个索引来检索给定范围的行
image.png
index
扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
image.png
ALL
即全表扫描，扫描聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了
image.png

possible_keys

这一列显示查询可能使用哪些索引来查找。和实际可能不相符

key

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是NULL。

key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。
image.png
key_len计算规则如下：
1.字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字 或字母占1个字节，一个汉字占3个字节

  • char(n)：如果存汉字长度就是 3n 字节
  • varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为 varchar是变长字符串

2.数值类型

  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节

3.时间类型

  • date：3字节
  • timestamp：4字节
  • datetime：8字节

4.如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

rows

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra

这一列展示的是额外信息。常见的重要值如下：
1）Using index：使用覆盖索引
覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个 查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
image.png
2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
image.png
3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
actor.name没有索引，此时创建了张临时表来distinct：
image.pngfilm.name建立了idx_name索引，此时查询时extra是using index,没有用临时表：
image.png
5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。
actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录：
image.pngfilm.name建立了idx_name索引,此时查询时extra是using index：
image.png

问题一：乐观锁和悲观锁的理解及如何实现，有哪些实现方式？

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。再比如Java里面的同步原语synchronized关键字的实现也是悲观锁。

乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。 乐观锁的实现方式： 使用版本标识来确定读到的数据与提交时的数据是否一致。提交后修改版本标识，不一致时可以采取丢弃和再次尝试的策略。 java中的Compare and Swap即CAS，当多个线程尝试使用CAS同时更新同一个变量时，只有其中一个线程能更新变量的值，而其它线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试。

CAS 操作中包含三个操作数 —— 需要读写的内存位置（V）、进行比较的预期原值（A）和拟写入的新值(B)。如果内存位置V的值与预期原值A相匹配，那么处理器会自动将该位置值更新为新值B。否则处理器不做任何操作。

CAS缺点：
ABA问题： 比如说一个线程one从内存位置V中取出A，这时候另一个线程two也从内存中取出A，并且two进行了一些操作变成了B，然后two又将V位置的数据变成A，这时候线程one进行CAS操作发现内存中仍然是A，然后one操作成功。尽管线程one的CAS操作成功，但可能存在潜藏的问题。从Java1.5开始JDK的atomic包里提供了一个类AtomicStampedReference来解决ABA问题。
循环时间长开销大：
对于资源竞争严重（线程冲突严重）的情况，CAS自旋的概率会比较大，从而浪费更多的CPU资源，效率低于synchronized。 只能保证一个共享变量的原子操作： 当对一个共享变量执行操作时，我们可以使用循环CAS的方式来保证原子操作，但是对多个共享变量操作时，循环CAS就无法保证操作的原子性，这个时候就可以用锁。

问题二：查询经常用到的like如何优化

a）使用覆盖索引，查询字段必须是建立覆盖索引字段
b）如果不能使用覆盖索引则可能需要借助搜索引擎

问题三:覆盖索引是什么，为什么用覆盖索引查找效率也很高

a）select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引
b）覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值