1 回表
# 回表查询:先定位主键值,再定位行记录的查询
性能 比 聚集索引(只扫一遍索引树) 更低
# eg: 辅助索引查询
对于辅助索引查询方式而言,一共搜索了两棵 B+Tree,
第一次搜索 B+Tree 拿到主键值后 再 去搜索主键索引的 B+Tree,这个过程就是所谓的回表。
# 回表优化: 慢查询优化的一种,减少回表的次数
核心:sql 查询时,只select 索引包含的字段
1.单个辅助索引,只查该索引的字段
eg: name为辅助索引, 那么select时,只查询主键id 或 name
2.若需要两个或多个字段,减少回表:可将该两个建联合索引 # 但谨慎使用,若索引过大,也会影响mysql性能
eg: name、age为联合索引, 那么select时,只查询主键id 或 name 或 age
2 慢查询日志
# 慢查询日志
设定一个最长查询时间,检测所有超出该时间的sql语句,并记录到慢查询日志文件中,然后针对性的进行优化!
# 默认情况下,MySQL数据库并不启动慢查询日志,需要手动设置
当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志或多或少会带来一定的性能影响。
2.1 开启慢查询日志
# 1.配置MySQL自动记录慢日志
vim /etc/my.cnf
[mysqld]
slow_query_log = off/on # 是否开启慢日志记录
long_query_time = 2 # 慢查询的阀值,超过此时间,则记录到慢查询日志 默认:10s
slow_query_log_file = /usr/slow.log # 指定慢日志文件存放位置,可以为空 默认:host_name-slow.log
log_queries_not_using_indexes = OFF # 未使用索引的搜索是否记录
min_examined_row_limit = 100 # SQL查询结果返回行数的阀值,小于该行数,SQL则不被记录
# 该参数尽量不用 eg:如果查询半小时,返回少于100
# 注:
# 1.查看当前配置信息:
show variables like '%slow_query_log%' # 查看慢日志是否开启
# 2.修改当前配置:
set global 变量名 = 值
# 2.查看MySQL慢日志
cat /usr/slow.log
2.2 慢查询日志分析
# 在生产环境中,如果要手工分析慢查询日志,查找、分析SQL,显然是个体力活
MySQL提供了日志分析工具mysqldumpslow
# 记录到慢日志中的日志是没有顺序的,是追加的模式
# mysqldumpslow命令
mysqldumpslow -s c -t 10 /data/slow/slow.log # 输出记录次数最多的10条SQL语句
mysqldumpslow -s at -t 10 /data/slow/slow.log # 输出平均查询时间最高的10条SQL语句
# 参数:
-s # 是表示按照何种方式排序 默认:at
c # 记录次数
t、at # 查询时间、平均查询时间
l、al # 锁定时间、平均锁定时间
r、ar # 返回记录数、平均返回记录数
-t # 是top n的意思,即为返回前面多少条的数据;
# 扩展工具
1.pt-query-diagest 自动做判断,先优化哪一个 # mysql运维工具:percona-toolkit里面的
2.mysqlsla # 专门用于处理分析Mysql的日志而存在
# 重要的是先找到,哪条语句最慢,而且执行次数最多
3 explain-执行计划
https://cloud.tencent.com/developer/article/1093229
# 通过 explain 命令 可获取select语句的执行计划 而不是执行这条SQL
explain + 查询SQL ---> 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化
# explain执行计划 返回的字段参数信息
核心指标:
1.type 找到结果的查询方式
# 达到range及以上
2.rows 找到结果的 所读取的行数 # 就是缩小 条件查询的范围 eg: id=100 和 id>100
# 绝大部分rows小的语句,执行一定很快 (有例外,下面会讲到)
所以优化语句基本上都是在优化rows
3.table # 显示改行数据是关于哪张表
4.key # 使用的索引
# 1.id 查询顺序标识
id列是select的序列号,有几个select 就有几个id 并且id的顺序是按select出现的顺序增长的
表示查询中执行select子句或操作表的顺序
# 注:
a.在嵌套查询中 id越大的语句 越先执行
b.该值可能为NULL 是其他行select的联合(union)结果 # union的结果总是放在一个匿名临时表
# 2.select_type 子句类型
表示查询中每个select子句的类型(简单OR复杂)
# 值:
a.SIMPLE # 简单查询 查询不包含子查询和union
b.PRIMARY # 复杂查询中 最外层的select子查询
c.SUBQUERY # 包含在select 或 where中的子查询
d.DERIVED # 包含在from子句中的select derived n.衍生
mysql会递归执行并将结果放到一个临时表中,服务器内部称为"派生表"
e.UNION # 位于union中第二个及其以后的select
f.UNION RESULT # 从union临时表检索结果的select
g.DEPENDENT SUBQUER # 包含在select或where中的子查询,并依赖于外层查询中发现的数据
h.DEPENDENT UNION # 位于union中第二个及其以后的select,并依赖于外层查询中发现的数据
i.UNCACHEABLE SUBQUER # 一个子查询的结果不能被缓存,必须重新评估外链接的第一行
# 3.table 数据表 *****
对应行的数据是关于哪张表的 表名或者别名
# 注:
1.当from子句中有子查询时,是 <derivenN> 格式 表示当前查询依赖id=N的查询,先执行id=N的查询
2.当UNION RESULT时,是<union1,2>格式 1和2表示参与union的 select行id
# 4.type 关联类型或访问类型 *****
表示MySQL在表中找到所需行的方式
# 常见值有:
ALL、index、range、ref、eq_ref、const、system、NULL # 从左到右 性能从差到好
ALL # 即全表扫描 对于数据表从头到尾找一遍
需优化,说明查询没有走索引:
1.语句本身的问题
2.索引的问题,没建立索引
index # 即全表扫描索引树 与ALL区别为只遍历索引树
range # 索引范围扫描 对索引列进行范围查找
eg: > < >= <= between and in() or
ref # 使用非唯一索引或者唯一索引的部分前缀 可能会找到多个符合条件的行
简单说:用了索引,但不是唯一返回值,可能返回多条数据
eq_ref # 类似ref,区别就在使用的索引是唯一索引或主键索引 对于每个索引键值,最多只有一条记录匹配
简单说:
就是多表连接中使用primary key或者 unique key作为关联条件
join条件使用的是primary key或者 unique key
const、system # mysql能对查询的某部分进行优化并将其转化成一个常量。
eg: 将主键置于where中,MySQL就能将该查询转换为一个常量
system是const类型的特例,当查询的表只有一行的情况下
NULL # MySQL在优化过程中分解语句,执行时甚至不用访问表或索引
eg:在索引列里选取最小值,可以通过单独索引查找完成,不需要在执行时访问表。
或者走的缓存中已经存在的
# 5.possible_keys
显示查询可能使用的索引
# 6.Key *****
显示实际决定使用的索引
# 7.key_len
表示索引中使用的字节数
# 注:
1.可通过key_len计算规则,计算查询中使用的索引长度,实现短索引原则 # 值为索引字段的最大可能长度,并非实际使用长度
2.索引最大长度是768字节
# 8.ref
表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
常见的有:const(常量)、func、NULL、字段名(例:film.id)
# 9.rows *****
表示MySQL根据表统计信息及索引选用情况,估算 找到查询结果 所需要读取的行数
# 10.Extra *****
该列包含MySQL解决查询的详细信息,有以下几种情况:
# 值:
distinct # 表示mysql一旦找到了与行相联合匹配的行 就不再搜索了 性能高的表现
Using index # 表示mysql将使用覆盖索引,以避免回表 性能高的表现
Using where # 表示mysql服务器将在存储引擎检索行后,再进行过滤
就是先读取整行数据,再按 where 条件进行检查,符合就留下,不符合就丢弃
Select tables optimized away # 表示仅通过使用索引,优化器可能仅从聚合函数结果中返回一行
-----------以下避免出现 需索引优化----------
Using temporary # 表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
Using filesort # 表示MySQL中无法利用索引完成排序操作,而使用一个外部索引排序
Using join buffer # 表示在获取连接条件时没有使用索引,使用了连接缓存区来存储中间结果
Impossible where # 表示where子句的值总是false,没有符合条件的行
4 慢查询优化
# 慢查询优化的基本思路步骤:
1.先运行看看是否真的很慢,注意设置SQL_NO_CACHE # SQL_NO_CACHE的作用是禁止缓存查询结果
2.where条件单表查,锁定最小返回记录表。
# 意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,
单表每个字段分别查询,看哪个字段的区分度最高
3.explain查看执行计划,是否与预期一致 # 从锁定记录较少的表开始查询
4.order by limit 形式的sql语句,让排序的表优先查
5.了解业务方使用场景
6.加索引时,参照建索引的几大原则
7.观察结果,不符合预期继续从1分析
# 企业SQL优化思路:
1.把一个大的不使用索引的SQL语句按照功能进行拆分
2.长的SQL语句无法使用索引,能不能变成2条短的SQL语句让它分别使用上索引。
3.对SQL语句功能的拆分和修改
4.减少“烂”SQL
由运维(DBA)和开发交流(确认),共同确定如何改,最终由DBA执行
5.制定开发流程
# 不适合走索引的场景:
1.唯一值少的列上不适合建立索引或者建立索引效率低。例如:性别列
2.小表可以不建立索引,100条记录。
3.对于数据仓库,大量全表扫描的情况,建索引反而会慢 # 读写数据时,需要额外的建索引操作,反而降低性能
# 查看表的唯一值数量:
select count(distinct user) from mysql.user;
select count(distinct user,host) from mysql.user;
# 建索引流程:
1.找到慢SQL
show processlist;
记录慢查询日志
2.explain select句,条件列多
3.查看表的唯一值数量:
select count(distinct user) from mysql.user;
select count(distinct user,host) from mysql.user;
条件列多 可以考虑建立联合索引
4.建立索引(流量低谷)
force index
5.拆开语句(和开发)
6.like '%%'不用mysql,使用全文搜索ES
标签:语句,--,mysql09,查询,回表,索引,SQL,日志,select
From: https://www.cnblogs.com/Edmondhui/p/16836092.html