SQL优化

数据库中的事务：

事务是作为一个单元的一组有序的数据库操作。要么全部执行成功，要么全部执行失败。

事务的特性（ACID）

原子性（Atomicity）：构成事务的逻辑操作必须是最小单位的

一致性（Consistency）：事务提交前和提交后的数据是一致的

隔离性（Isolation）：多个事务之间必须是相互隔离，不能影响

持久性（Durability）：事务提交后，产生的数据必须持久化地保存在数据表中

数据库事务隔离级别

在数据库中，事务要满足ACID的四个性质，即原子性、一致性、持久性以及隔离性。在ACID四个属性中，隔离性是最常放松的一个，可以在数据操作过程中利用数据库的锁机制或者多版本并发控制机制获取更高的隔离级别。

各种隔离级别和数据库事务并发时存在的问题对应情况如下：

隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	√	√	√
读已提交 （Read Committed）	×	√	√
可重复读 （Repeatable Read）	×	×	√
串行化 （Serializable）	×	×	×

• 读未提交：允许脏读，就是在该隔离级别下，可能读到其他会话未提交事务修改的数据，存在脏读、不可重复读、幻读的问题。
• 读已提交：只能查询到已提交的数据。这是Oracle数据库默认的事务隔离级别。存在不可重复读，幻读的问题。
• 可重复读：就是在一个事务里相同条件下，无论何时查到的数据都和第一次查到的数据一致。这是MySQL数据库、InnoDB引擎默认的事务隔离级别。在范围查询时存在幻读的问题。
• 串行化：是最高的事务隔离级别，它严格服从ACID特性的隔离级别。所有的事务依次逐个执行，事物之间互不干扰，该级别可以防止脏读、不可重复读以及幻读。但每个事务读数据时都需要获取表级的共享锁，导致读和写都会阻塞，性能极低。

/*
隔离级别越高，性能越低。
因为隔离级别越低，事务请求的锁越少，
所以大部分数据库系统的隔离级别都是读已提交，
但是MySQL中InnoDB存储引擎默认使用可重复读并不会有任何性能损失。
*/

事务不考虑隔离级别会出现的问题：

脏读、不可重复读、幻读

脏读：

​ 脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据中，也就是不存在时数据。读到了并一定最终不存在的数据，这就是脏读

当事务B开启事务并对数据进行了更新操作，但并未提交事务，但是事务A在这时候却读取到了事务B未提交的事务，这时候事务B选择回滚事务，那么事务A读取到的信息就是错误的，读取到一条不存在的数据。

不可重复读

不可重复读指的是在一个事务内，最开始独到的数据和事务结束前的任意时刻独到的同一批数据出现不一致的情况。

在事务A开启事务对数据表进行多次读取时，事务B对数据进行了更新操作并提交了事务，那么就会造成事务A前一次读取到的数据和后一次读取到的是数据不相同。

幻读（相对来说是可以容忍的）

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。
更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

假如有一张用户表，id是主键，此时表中只有四条数据

id	name	pwd
1	张三	aaa
2	李四	bbb
3	王五	ccc
4	赵六	ddd

幻读的场景

当事务A开启事务对表中数据进行查询，发现只有四条数据，所以这时候他想要插入第五条数据，这时候事务B也进行了第五条数据的插入，并且事务B提交了事务，当事务A提交事务时，会提交失败，因为这时候id=5的数据已经存在了。

SQL优化之如何定位慢SQL

1.开启慢查询日志

-- 查看慢SQL日志是否启用(on表示启用):
showvariableslike'slow_query_log';
-- 查看执行慢于多少秒的SQL会记录到日志文件中
showvariableslike'long_query_time';
-- 可以使用模糊搜索,查看所有含有query的变量信息
*showvariableslike'%query%';

2.显示最近几次的查询

show profiles; # 显示最近的几次查询;

3.查看更丰富的内容，例如cpu的执行效率

show profile cpu, block io for query id;

SQL优化之执行计划

Explain：

​ explain关键字可以查看一些sql语句的执行计划，比如查看该 SQL 语句有没有索引、有没有全表扫描等，从而知道MySQL是如何处理你的SQL语句的。

1、用法：

我们只需在SQL语句之前增加EXPLAIN关键字，在执行查询时，就会返回执行计划的信息

EXPLAIN SELECT * FROM `user`

执行结果输出如下：

2、字段详解

2.1. id

id 列的编号是 select 的序列号，一般比较简单的查询语句里只有一个 select ，稍微复杂点的查询包括子查询或者包含 union 语句的情况会有多个 select，有几个 select 就会有几个 id ,并且 id 的顺序是按 select 出现的顺序增长的：

​ id 相同，执行顺序从上至下

​ id 不同，执行顺序从大到小

​ id相同不同，同时存在，遵守1、2 规则

EXPLAIN SELECT * FROM `user` WHERE user_id = 101 UNION SELECT * FROM `user` WHERE user_id = 102

union结果放在一个匿名临时表中，临时表不在SQL总出现，因此它的id是NULL。

2.2 select_type

查询中每个select 的查询类型。

2.2.1 SIMPLE ： 简单select，不使用 union 和 子查询

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

2.2.3 UNION ： union 中 第二个后面的 select 语句

2.2.4 UNION RESULT : union 的结果

2.2.5 SUBQUERY ： 子查询中的第一个 select

2.3 table

表示数据来自于哪个表，有时不是真实的表的名字（虚拟表），虚拟表最后一位是数字，代表id为多少的查询

2.4 *type

重点关注字段，直接反应SQL的性能是否高效。

这个字段表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据记录的大概范围。这个字段值较多，这里我只重点关注我们开发过程中经常用到的几个字段：system , const, eq_ref, ref, range, index, all;

性能由高到低为：system > const > eq_ref > ref > range > index > all

• system : 表只有一行记录，这个是 const 的特例，一般不会出现，可以忽略。
• const : 表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。

• eq_ref :唯一性索引扫描，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或唯一索引。

• ref :非唯一行索引扫描，返回匹配某个单独值的所有行。

• range : 检索给定范围的行，一般条件查询中出现了>、<、in、 between等查询

• index : 遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。all和index都是读全表，但index是从索引中检索的，而all是从硬盘中检索的

• all : 遍历全表找到匹配的行

2.5 possible_keys

查询可能使用哪些索引来查找，但不一定被查询实际使用

2.6 key

实际使用的索引，如果没有使用索引，则该列是NULL

2.7 key_ len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，一般来说，索引长度越长表示精度越高，效率偏低，长度越短，效率高，但精度就偏低。并不是真正使用索引的长度，是个预估值。

2.8 ref

在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量）、func、NULL，字段名（user_id）

2.9 rows

大致找到所需记录需要读取的行数，注意这个不是结果集里的行数。

2.10 filtered

表示所选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%

2.11 *Extra

这一列展示的是额外的信息，常见的重要值如下：

• Using where : 通常是进行了全表/全索引扫描后再用where字句完成结果过滤【差，需要加索引或者SQL写的不好】

• Using filesort : 表示没有使用索引的排序【差，需要加索引】

• Using temporary : 使用了临时表

• Using index : 表示使用覆盖索引，查询的字段在覆盖索引中就可以获取到

3.JSON格式的执行计划

我们通过在EXPLAIN 关键字和真正的查询语句中间加上 FORMAT=JSON 可以得到一个JSON 格式的执行计划，里面包含该计划花费的成本，可以衡量执行计划的好坏

EXPLAIN FORMAT=JSON SELECT * FROM `user`

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.40"
    },
    "table": {
      "table_name": "user",
      "access_type": "ALL",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.40",
        "prefix_cost": "1.40",
        "data_read_per_join": "4K"
      },
      "used_columns": [
        "user_id",
        "username",
        "password",
        "user_account"
      ]
    }
  }
}

导致索引失效的七种情况

① 在索引的字段上做任何的计算、函数等操作

EXPLAIN SELECT * FROM user WHERE age = age + 1

②索引查询使用范围条件右边的索引会失效

EXPLAIN SELECT * FROM user WHERE name = 'tom' and age > 18

③使用 != 、<>、is null、is not null 使索引失效

EXPLAIN SELECT * FROM user WHERE age <> 18

④使用like以通配符开头的模糊查询

其中“%”在查询数据的左侧时，使索引失效例如：“%tom”,在查询条件的右侧可以使用索引，例如"tom%"

EXPLAIN SELECT * FROM user WHERE name like "%tom"

⑤在查询语句中使用or连接之后，会使索引失效。

​ 哪怕是左右两个都是有索引的字段也会使索引失效

EXPLAIN　SELECT * FROm user where age = 18 or name = "tom"

⑥在查询中存在类型转换

EXPLAIN SELECT * FROM user WHERE userId = "18"

⑦违反最左前缀法则

在查询语句中如果索引有多列，要遵守查询从索引的最左前列开始并且不跳过索引中的列。其中左侧的列可以使用索引，未加索引列之后的所有列索引失效

EXPLAIN SELECT * FROM user WHERE age = 18 AND userId = 18 ane name = "tom"

• FROm user where age = 18 or name = “tom”

### ⑥在查询中存在类型转换

```sql
EXPLAIN SELECT * FROM user WHERE userId = "18"

⑦违反最左前缀法则

在查询语句中如果索引有多列，要遵守查询从索引的最左前列开始并且不跳过索引中的列。其中左侧的列可以使用索引，未加索引列之后的所有列索引失效

EXPLAIN SELECT * FROM user WHERE age = 18 AND userId = 18 ane name = "tom"