【小墨mysql】mysql之四------sql优化-CSDN博客

本文链接：https://blog.csdn.net/as4566/article/details/109107703

一，前言

大家好，我是小墨。

mysql章节更新到最新第四期，之前3期分别为

mysql系列之一---索引

mysql系列之二---binlog

mysq系列之三---事务

这一期我们主要讲述如何优化我们的数据库mysql，从以下几个方向进入：

如何找到mysql瓶颈？
mysql查询缓慢原因
sql优化

这一章我尽力讲全,欢迎阅读，不多说直接上车。

二，如何找到sql瓶颈

在我们进行mysql使用的时候会经常遇到发现一次查询缓慢的情况，我们来考虑下分别有几种可能性导致查询缓慢。

1）sql查询缓慢

这种是我们最常见的优化点，一般一抓一个准，这个也是我们讲述重点，所以我放到后面重点讲述，这里我就说一下

两个点：1）如何定位到查询缓慢的语句？ 2）使用explain分析sql语句

如何找到查询缓慢的语句

MySQL可以通过慢查询日志定位那些执行效率较低的SQL 语句，我们谈下如何使用：

使用慢查询日志，一般分为四步：

开启慢查询日志 set global slow_query_log = on;
设置慢查询阀值 set global long_query_time = 1; 单位秒，这个可以自己设置
确定慢查询日志路径。在my.cnf找到[mysqld]下面加上log-slow-queries=/data/mysqldata/slowquery.log

通过设置后就可以去日志中查看找到目前系统的查询慢的语句，然后对语句进行分析

explain分析sql语句

我们使用explain语句可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL

我这里使用我之前的文章例子，

mysql> create table test (
-> id int auto_increment,
-> a int,
-> b int ,
-> c int,
-> d int ,
-> e int ,
-> PRIMARY KEY (id),
-> index abcd (a,b,c,d)
-> );

explain字段我们讲一下

MySQL优化：定位慢查询的两种方法以及使用explain分析SQL

我们重点讲几个内容：

select_type：讲述查询的类型，有以下几个重点我们说下：

SIMPLE：查询中不包含子查询或者UNION
PRIMARY:查询中若包含任何复杂的子部分，最外层查询的标记
SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询的标记
DERIVED（衍生）：在FROM列表中包含的子查询的标记

key：显示MySQL实际决定使用的索引。如果没有索引被选择，键是NULL

type：单表的访问方法，最好到最差的性能分别为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref，我们讲几个常见的

const：基于主键或者唯一索引查询

ref：非唯一性索引扫描，返回匹配某个单独值的所有行
range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行
index: 扫描全部索引树

key_len：显示MySQL决定使用的键长度。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，从这里可以判断使用多重主键的时候使用了哪几部分

rows：表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的

通过explain可以看出sql的具体情况

三，sql优化

我们使用sql的时候，使用explain其实是可以分析出具体sql的执行计划，我们其实都知道我们更快的查询到对应数据库数据，都是使用索引，只不过索引的使用好坏导致了查询的效率差距。

这一节希望大家结合这一篇博客mysql系列之一---索引食用，能够更好的理解。

1，索引走错

这个其实是sql执行之前的分析器，优化器做了个判断，它选择的时候发现，走A索引没有额外的代价，比如走B普通索引并不能直接拿到我们的值，还要进行回表，然后他就认为这样选择更好，忽略了有可能要扫描更多行。

一般这种情况在我们做了explain分析后，然后发现走错后一般处理方式是：

走force index() 指定你要sql走的索引，当然这个方法比较笨，我看了下其他博客不是很建议
找到为什么会走错索引的真正原因，会走错索引往往是复杂sql下需要sql做了排序等等多余操作，重建更合理索引，优化sql这里后面有机会举个例子

2，覆盖索引 and 联合索引

对于这两种索引不多加叙述，我们为了能够更快的查找到对应数据，都会尽量利用索引，这两种索引就是这时候使用

覆盖索引

通过将我们要查找的数据列和对应的主键一起建立覆盖索引，可以通过直接走索引，一般的非聚簇索引记录的数据是

主键id，通过这种覆盖索引就可以帮助我们减少一次使用主键id在回表查数据的时间。

联合索引

当我们需要根据某个人名称去查找到他的年龄信息时，我们对这种高配搜索一般都是可以建立联合索引index(age,name)，从而

可以直接在索引上直接找到对应的数据，极快加速了查找速度，当然要考虑索引的占据空间，不能太随便建立。

3，索引失效

索引失效是一种很常见的现象，大多都是进行了索引上的多余操作，我们都知道mysql查找索引是二叉查找法，那么如果让mysql

无法进行二叉比较就会导致退化，甚至直接全表扫描。一般我们都是建议最好使用全值匹配。以下是几个反例

1）不遵循最左前缀法

节省时间使用了JAVA知音的例子和图片

举例：create index idx_obj on user(age asc,height asc,weight asc)
联合索引的排序有这么一个原则，从左往右依次比较大小，就拿刚才建立的索引举例子，他会先去比较age的大小，如果age的大小相同，那么比较height的大小，如果height也无法比较大小，那么就比较weight的大小，最终对这个索引进行排序里是引用
表如下：
B+树：（注意是非簇集索引，最后数据是主键）