Mysql进阶操作及SQL优化

对同一个业务进行查询，由熟悉SQL的大神和初试SQL的小白分别写可能速度会相差几百倍。如何让自己操作SQL的姿势更加帅气呢，为什么有些SQL执行那么快有些又要那么久呢，那么就让我们来探索其中的奥秘，深入浅出，写出无与伦比的美丽的SQL。

本篇文章将带你学习如下几点

Explain语句的使用及相关字段说明
如何正确使用索引
SQL优化
其他辅助操作

Explain使用

Explain命令是Mysql为我们提供的对查询SQL语句的执行分析，如是否使用了索引，使用了哪条索引，是否回表等等。通过Explain命令我们就能知道该从哪里对我们的SQL进行优化，比如没有使用索引，组合索引命中的不够多，查询有使用临时表进行排序导致过慢等等。

命令使用：在我们的查询语句前添加 EXPLAIN 即可

EXPLAIN SELECT * from users where id in ('1','2','3')

执行结果:

我们来对各个参数来个认识：

id：表示执行顺序，值越大越先执行，相同,执行顺序由上而下，本条SQL并没有子查询，所以只有一条数据。

select_type：查询类型

SIMPLE：表示此查询不包含 UNION 查询或子查询
PRIMARY：表示此查询是最外层的查询
SUBQUERY：子查询中的第一个 SELECT
UNION：表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT：UNION 的结果
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）

table：查询涉及的表或衍生的表

partitions：如果表中存在分区，则显示命中分区id，如果没有命中分区显示null；

type⭐️：重要，用于我们判断查询是否高效，上面我们的type就是 range,因为使用了IN进行范围查找

system: 表中只有一条数据，这个类型是特殊的 const 类型。
const: 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。 const 查询速度非常快，它仅仅读取一次即可。比如下面的例子，我们使用主键id进行等值查询， type 就是 const 类型的：explain select * from users where id = 1；
eq_ref: 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。例如：explain select * from users, user_role where usersid = user_role.user_id
ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询。例如下面这个例子中，就使用到了 ref 类型的查询：explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5
range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。
index: 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到(相当于覆盖索引，后面会讲), 而不需要扫描数据。当是这种情况时，Extra 字段会显示 Using index。
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说，我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。如果遇到了ALL的查询语句，一定要注意查询条件，可以添加索引，对该语句一定要进行改造，否则一条SQL可能会执行长达几分钟。

possible_keys：查询时，可能使用到的索引.这里指的只是可能，并不一定用到，具体用到什么索引由Key来判断

key：具体使用的索引

key_len：使用了索引的字节数，这个字段可以评估组合索引是否完全被使用。对于一些组合索引，可以根据这个字段来判断使用了哪几个索引

ref：这个表示显示索引的哪一列被使用了，如果可能的话,是一个常量。type属性里也有ref，注意区别。

rows⭐️：重要，mysql 查询优化器根据统计信息，估算 sql 要查找到结果集需要扫描读取的数据行数，这个值非常直观的显示 sql 效率好坏，原则上 rows 越少越好。

filtered：表示返回结果的行数占需读取行数的百分比，所以越大越好，表示并没有过多的数据行扫描

extra：额外的信息会在 extra 字段显示

using filesort ：表示 mysql 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大。
using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
using temporary：查询有使用临时表, 一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化。
using where ：表名使用了where过滤

如何正确使用索引

那么既然我们通过Explain发现我们的SQL问题了，接下来就该进行优化了，那么最重要的就是正确使用索引，首先索引在我们查询中起到最重要的作用，相当于书的目录，你可以快速定位某一知识点在哪一页，而不需要一页一页进行翻阅。所以不论我们数据量大不大，都要有索引。我们接下来都以Innodb为文件引擎B+树为索引数据结构来讲解如何创建和使用好索引。

首先明白索引可以分为

聚集索引（主键索引）一个表只有一个，该索引不仅不仅保存索引信息，而且叶子结点保存真正的数据。
非聚集索引（次级索引）叶子结点保存聚集索引值，如果不覆盖索引，在进行查询时会进行回表操作（得到聚集索引id，再到聚集索引中查询真实数据）

合适的主键：

有序性：为什么要有序呢？我们的索引使用的是B+树，有序是为了减少B+树的调整，减少页的分裂和合并，比如当前页中能存6个主键，并且现在有1、2、3、5、6、7的主键，现在你插入一个4，由于插入位置的页空间不够，需要进行页的分裂，分成两个页，这个操作是非常费时间的，如果这样的插入或删除操作多了，那么B+树就在不停的调整，都没空进行查询了。
大小合适：前面我们也讲了次级索引会存储主键索引值，如果主键索引太大，那么会占用非常多的空间，对于Mysql，在查询的时候都会把查询的页读入内存，由于占有过大，那么我们能读入内存中的页也就少了，有些查询的页可能就不在内存导致时间慢。

合适的索引：

区分度高：也可以说是基数大，如果一个索引他的值都是1和0那么它是不适合做索引的，这也会导致页的分裂合并，并且查询效率和没有索引的提升不大。
有序：如上
大小：如上

根据实际业务创建索引：

对于常查询的字段建立索引：比如某个业务需要经常通过城市编号（city）来进行查询信息，那么我们可以为city进行创建索引。如果还要通过城市加年龄（age），那么我们可以创建(city,age)的组合索引，如果我们创建了组合索引就不需要单独创建city索引了。例如：创建了(a,b,c)索引相当于创建了(a)、(a,b)、(a,b,c)三个索引。这点大家可以去翻阅:最左匹配原则，我们就不细讲了
order by中的列：对order by中的列设置索引，可以让我们的数据不用进行额外的排序操作，节省空间以及内存，比如：获取成绩大于80的同学的信息，并且按照成绩排序，那么我们就可以建立成绩的索引来提高效率 :

select * from student_score where score>90 order by score
覆盖索引：对我们要获取的值进行创建覆盖索引，避免回表操作，如通过成绩查找学生的班级、姓名信息。可以创建(score,class_name,student_name)的索引。通过覆盖索引就能避免回表操作

select class_name,student_name from student_score where score>80

创建索引时我们先考虑1，如果查询速度还是不理想再考虑2，然后3。如果还是不行，那就赶紧删酷跑路了。

SQL优化

现在我们已经创建好了索引，但是有些小朋友还是会问，公司数据量太大了，我的SQL还是这么慢呀？我明明只查一条数据有索引怎么还那么慢？

那么我们就要学习几个SQL优化的操作了，认真阅读！！！

避免!= < > or(可以使用union),这些会导致我们的索引失效，<>会导致组合索引中后面的索引失效，如果可以的话尽量不使用
避免where中对字段进行表达式操作，如 where num/2 =100 同样会使索引失效，改为： where num=100*2
避免where对字段进行函数操作，同上
不使用select * ，对于我们返回的值我们应该拿多少就取多少，即使全部要，最好也写出来，这样也是为了可阅读性
养成习惯对于明确查找一条数据的使用limit 1来防止游标下滑（主键索引和唯一索引可以不使用），这样是为什么只查一条数据但是也很慢的原因，这是由于索引不是唯一索引，查询到了后，还是会继续往后查询，所以针对明确一条数据的我们可以使用limit 1
避免返回大量数据这点不用多说吧：相信不会有任何一家公司允许你 select * from table 就没了，看到会给你一个limit 的限制，不然可能一条sql可以查到天昏地暗了
子查询优化：进行子查询优化还是需要一些条件的，比如这种场景：我需要再log表中查找某个创建时间及以后的数据，那么我们可以直接： created>xxx来进行查询，但是我们还可以更高级：首先查找第一条创建时间为xxx之后的：1.select id from log where creaded>xxx limit 1。得到后我们可以使用2. select log_info from log where id>search_id(1中查询到的id)。这样我们使用了主键索引进行查询，同时避免了回表操作

其他辅助操作

关于数据库设计：

尽量使用数字字段，如ip转换为数字后再存储，数字的比较速度远远快于字符串
避免where进行null值判断
尽量不使用临时表
尽量使用varchar代替char
索引不要建立过多，或重复，索引也会占用很多空间和mysql对它的维护成本
按需使用tinyint、int、bigint，避免空间浪费

慢查询日志：

在配置文件中进行如下配置，可以帮助我们定位差sql

[mysqld]

slow_query_log = ON #是否打开慢查询日志
slow_query_log_file = /var/mylog/sql-slow.log #日志位置
long_query_time = 1 #超过多少时间算慢sql，单位是s

慢查询可以帮助我们监控

对于数据库优化的几个阶段：

从上开始对问题进行解决分析，如果不行再往下

慢查询定位效率低的sql
expalin分析sql
确定问题优化
搭建缓存，要注意系统的复杂度、一致性问题
读写分离：主从等，好处：负载均衡
垂直拆分
水平拆分