SQL优化--索引优化/日志查询

最新推荐文章于 2022-11-19 09:11:00 发布
最新推荐文章于 2022-11-19 09:11:00 发布
阅读量302 收藏
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_39410753/article/details/107302815
版权

文章目录

SQL优化

原因：性能低、执行时间长、等待时间长、SQL语句较差（连接查询）、索引失效、服务器参数设置不合理（缓冲数、线程数）

1 SQL语句

编写过程

select distinct .. from .. join .. on .. where .. group by .. having .. ording by .. limit ..

解析过程：

from .. on .. join .. where .. group by .. having .. select distinct .. order by .. limit ..

2 索引

2.1 索引弊端

  1. 索引本身很大，可以存放在内存/硬盘
  2. 索引不适用情况：数据量小、频繁更新的字段不适合作为索引、少使用的字段
  3. 索引降低增删改效率，改完数据还得改索引

2.2 索引分类

  • 单值索引：单列、一个表可以有多个单值索引
  • 唯一索引：索引不能重复
  • 主键索引：不能是null，其他和唯一索引相同
  • 复合索引：多个列构成的索引

2.3 索引操作

创建
普通索引 INDEX 唯一索引 UNIQUE INDEX;
CREATE 索引类型 索引名 ON 表名(username(length)) 

删除
DROP index 索引名 on 表名;

修改
ALTER table mytable ADD UNIQUE [indexName] (username(length));

查看
SHOW INDEX FROM 表名;

通过ALTER命令添加和删除索引

ALTER TABLE 表名 ADD 索引类型 索引名（字段）

ALTER TABLE 表名 DROP 索引类型 索引名（字段）

2.4 EXPLAIN解读

  1. 分析SQL执行计划：EXPLAIN，可以模拟SQL优化器执行SQL语句，了解SQL状况
  2. SQL自带的优化器，可能干扰自己的优化

在这里插入图片描述

id：编号
select_type：查询类型
table：表
type：类型
possible_keys：预测可用到的索引
key：实际使用的索引
key_len：实际使用的索引的长度
ref：表之间的引用
rows：通过索引查询到的数据量
Extra：额外的信息

#查询课程编号为2， 或教师编号为 3 的老师信息
EXPLAIN
SELECT c.cid,t.*
FROM teacher t,course c
WHERE (c.cid=2 OR c.tid=3) 
AND t.tid=c.tid;

使用EXPLAIN，其中因为是两表查询，id相同，从上到下执行，排序顺序根据表内行数决定，teacher表数据3行，课程表数据4行，所以课程在老师下面。
在这里插入图片描述

select_type查询类型
  1. SIMPLE：简单查询不包括自查询或者UNION
  2. PRIMARY/UNION:PRIMARY查询中最外层的SELECT（外层查询），UNION（内层查询）
  3. DEPENDENT UNION：UNION中国呢，查询处于内层的SELECT，内层和外层有依赖关系
  4. UNION RESULT：UNION操作的结果，id通常为NULL
  5. SUBQUERY：自查询中的首个select
  6. DEPENDENT SUBQUERY:自查询中的首个select但是依赖于外层表
  7. DERIVED：衍生查询，使用到了临时表
type索引类型

前提是查询的列是索引列，不然肯定是all，不会有索引类型
常见类型，越靠左性能越高，其中system，const只是理想情况，实际能达到ref>range
system > const > eq_ref > ref > range > index > all

1 . system：只有一条数据的系统表
2. const：仅能查到一条数据，且索引类型是primary key 或者unique索引
3. eq_ref：唯一性索引，对于每个索引键的查询，返回匹配唯一一行数据（例如对id查询，每个id查到有且仅有一个数据，不能多个也不能0个）。常见于唯一索引和主键索引
4. ref：非唯一性索引，对于每个索引见的查询，返回匹配的所有行，可以为0或者多个

在这里插入图片描述

#type ref因为一个老师对应两个课程
EXPLAIN
SELECT *
FROM course c
WHERE c.tid = 2;

在这里插入图片描述
5. range：范围查询，where后面是一个范围(between, >,<,>= 特殊：in 有时会失效变为无索引)

#type range
EXPLAIN
SELECT *
FROM course c
WHERE c.tid >= 2;

在这里插入图片描述
6. index 和 all 都是查询所有数据，index查询所有索引数据，all查询所有数据

#index tid列有索引
select tid from course;
#all
select * from course;
possible_keys & key & key_len

possible_keys可能用到的索引，对使用的索引进行预测。key实际使用的索引。null表示没用索引
key_len索引长度：用于判断复合索引是否被完全使用
其中：

  • int：两个字节
  • 字符：三个字节
  • null：一个字符判读是否为空
  • varchar：占两个自己标示可变长度

当前索引是两个int的复合索引，可以为空，所以len是5

索引（tid,cid）和主键索引 cid

EXPLAIN
SELECT *
from course where cid = 1;
key_len = 4 使用的主键索引

EXPLAIN
SELECT *
from course where tid = 1;
key_len = 5 使用联合索引，可以为null

EXPLAIN
SELECT tid,cid
from course;
key_len = 9 使用联合索引全部，可以为null
ref栏

与type的ref不同，指明当前表参考的字段

#ref
EXPLAIN
SELECT * 
FROM course c,teacher t
WHERE c.tid = t.tid 
AND t.tname = "张三";

tname有索引，且判断条件是常量，在ref中显示const表示常量

t表参考的是const常量，c表参考的是t.tid
在这里插入图片描述

row

row: 被索引优化查询的数据个数，也就是通过索引查询到的数据个数
在这里插入图片描述
在这里插入图片描述

Extra额外信息
  • using filesort：性能消耗大，需要额外的一次排序（查询）。常见于ORDER BY
    排序：先进行查询，在对数据进行排序

对于单索引：如果排序和查找是同一字段，则不会出现using filesort，如果不是同一字段，就会出现using filesort。
建议：where哪些字段，就order by哪些字段
对于复合索引：（i1,i2,i3）最左前缀原则，
建议：where和order by按照复合索引顺序使用，不要跨列或者无序使用

select * from table where i1="a" order by i2; 
select * from table where i1 = "a" and i2 ="b" order by i3;
都不是using filesort

select * from table where i1="a" order by i3; 
是using filesort
  • using temporary：性能损耗大，用到了临时表，已经有表了，但是不符合条件，还需要重新建一个临时表。常见于GROUP BY，查询哪些列，就根据那些列进行group by

查询cname使用tid进行group by，就会使用临时表

EXPLAIN
SELECT COUNT(cname)
FROM course c
WHERE cname in ("语文","数学")
GROUP BY c.tid;

在这里插入图片描述

  • using index：性能提升 （覆盖索引），说明不读取原文件，只需查询索引文件，不需要回表查询。查询的数据刚好是索引中的数据，速度得到提升。
    如果用到了索引覆盖（using index）会对possible_keys和key造成影响：

    • 如果没有where，则索引只出现在key中
    • 如果有where 索引出现在key和possible_key中

  • using where: 表示需要回表查询

使用where in 需要回表，in操作会使索引失效

EXPLAIN
SELECT cname
FROM course c
WHERE cname in ("语文","数学");

在这里插入图片描述
使用where = 不需要回表

EXPLAIN
SELECT cname
FROM course c
WHERE cname = "语文";

在这里插入图片描述
联合索引是（cname,tid,cid）,

EXPLAIN
SELECT *
FROM course 
WHERE tid = 1;

在这里插入图片描述

  • impossible where：where子句永远为false

3 优化语句

3.1 单表优化

create table test(
	a1 int(4) not null,
	a2 int(4) not null,
	a3 int(4) not null,
	a4 int(4) not null);
alter table test add index idx_all(a1,a2,a3,a4);

推荐写法,正确使用索引：
select * from test where a1 = 1,a2 = 1,a3 = 1,a4 = 1;

打乱顺序不推荐，但是系统会自动排序
select * from test where a4 = 1,a3 = 1,a2 = 1,a1 = 1;

在这里插入图片描述

EXPLAIN
SELECT *
FROM test
WHERE a1 = 1
AND a2 = 2
AND a4 = 4;

using index 说明一部分不需要回表，也就是a1 和 a2但是a4跨列，无效索引所以需要回表，长度8，只用了两个索引
在这里插入图片描述

#using filesort
EXPLAIN
SELECT *
FROM test
WHERE a1 = 1
AND a4 = 4
ORDER BY a3;

通过a3进行排序，但是a3不在索引中，所以需要重新查询排序一次。where 和 order by 拼起来是否满足索引顺序，a1 后是a3产生跨列，就会失效
在这里插入图片描述

3.2 多表优化

课程表
在这里插入图片描述
老师表
在这里插入图片描述

  • 做外连接，查询语文课程的所有信息
EXPLAIN
SELECT *
FROM teacher t LEFT JOIN course c
ON t.tid = c.tid
WHERE c.cname = '语文';

如何设置索引，索引加在哪个表上。规定：小表驱动大表。如本例t.tid = c.tid和c.tid = t.tid结果等价，但是要判断哪个表小，数据量小的表放在左边
给teacher的tid增加索引，并且给course的cname增加索引
在这里插入图片描述

3.3 原则总结

1 . 复合索引：不要跨列或无序使用（最佳左前缀原则），尽量使用全索引匹配
2. 不要在索引上进行任何操作（计算、函数、类型转换）否则索引失效

当前复合索引(sid,cid)

#对索引进行操作，失效
EXPLAIN
SELECT * 
FROM sc
WHERE sid = 1 AND cid * 2 = 1;

右侧失效，左侧依然满足
在这里插入图片描述

EXPLAIN
SELECT * 
FROM sc
WHERE sid * 2 = 1 AND cid * 2 = 1;

两个索引都失效，如果复合索引左侧索引失效，则全部失效
在这里插入图片描述
3. 复合索引不能使用不等于（!= <>）或者is null(is not null)，否则自身及右侧索引全部失效。索引优化是概率的优化，SQL优化器会影响我们的优化，结论不是百分百正确，可以通过EXPLAIN查看
4. 尽量使用覆盖索引（using index）
5. like模糊查询：以常量开头’xxx%’，不要以%开头’%xxx’，否则索引失效。可以通过索引覆盖优化一部分查询，如果模糊查询的内容是索引的一部分
6. 尽量不要使用类型转换，例如char->int
7. 尽量不要使用OR，将or左侧的索引失效

3.4 其他优化方法

  1. exist和in：如果主查询的数据集大，则使用IN；如果子查询数据集大则使用exist
    exist：select * from table where exist(子查询) ;
    将主查询需要的的结果（列），放到子查询中进行校验，查看子查询是否有需要数据，如果有数据保留，否则为null
    in：select * from table where 列名 in (子查询);从子查询的结果集中查询
  2. order by 优化： using filesort 有两种算法：双路排序，单路排序（根据IO次数）。
    双路排序：MySQL4.1之前使用，扫描两次磁盘，第一次从磁盘读取排序字段，在buffer缓冲区中进行排序；第二次读取其他内容
    单路排序：MySQL4.1之后默认使用，只需一次IO，读取全部。但如果数据过大，不一定是一次IO。可以调整缓冲区大小set max_length_for_sort_data = 1024(Byte)。如果buffer太低，需要排序的列比buffer还大，自动转换为双路排序。
    a. 选择使用单路、双路；调整buffer大小
    b. 避免使用select *，难以索引覆盖
    c. 复合索引不要跨列使用
    d. 保证全部排序的一致性，都是升序或者降序

4 SQL排查

4.1 慢查询日志

MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阈值的SQL语句（long_query_time默认10秒），慢查询日志默认是关闭的，建议开发调优时打开，部署时关闭。

#慢查询日志
SHOW VARIABLES LIKE '%slow_query_log%';
#临时开启--在内存中开启
SET GLOBAL slow_query_log = 1;

#永久开启
在my.cnf文件中追加配置
在[mysqld]中添加
slow_query_log = 1
slow_query_log_file = 文件地址

#打开日志会和创建函数起冲突



#查看修改阈值
SHOW VARIABLES LIKE '%long_query_time%'
SET GLOBAL long_query_time = 5;
永久设置和慢查询日志设置相同

4.2 profiles查询海量日志

通过profile分析海量数据

#profiles
show profiles;--显示查询结果
show variables like '%profiling%';--查看
set profiling = on;--打开

精确分析sql诊断

--查询所有信息
show profile all for query query_id;--上一部查询出的queryID
--查询cpu 和 io信息
show profile cup,block io for query --queryID

全局日志查询：开启后记录所有SQL语句，只在调优开发中使用，默认关闭。开启后的表会记录在mysql.general_log表中，系统自带的表。

show  variables like '%general_log%';
#开启全局日志
set global general_log = 1;
#将文件放在表中
set global log_output = 'table';
#将文件写在log文件中
set global log_output = 'file';
set global general_log_file = '文件地址/general.log';
Cccccrj
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL优化篇：慢查询日志.pdf
04-18
主要介绍MySQL数据库中慢查询日志的使用流程、使用方式、以及日志分析工具mysqldumpslow的相关内容
MySQL慢查询：慢SQL定位、日志分析与优化方案
JavaShark的博客
06-04 250
每一个SQL都需要消耗一定的I/O资源，SQL执行的快慢直接决定了资源被占用时间的长短。假设业务要求每秒需要完成100条SQL的执行，而其中10条SQL执行时间长导致每秒只能完成90条SQL，所有新的SQL将进入排队等待，直接影响业务为了更好的优化慢SQL，我们来简单了解下MySQL的执行原理绿色部分为SQL实际执行部分，主要分为两步： 除了sql的方式，我们也可以在配置文件（my.ini）中修改，加入配置时必须要在[mysqld]后面加入 开启慢查询会带来CPU损耗与日志记录的IO开销，所以建议间
如何使用 MySQL 慢查询日志进行性能优化 - Profiling、mysqldumpslow 实例详解
蒋川@卡拉云
11-18 901
当我们开始关注数据库整体性能优化时，我们需要一套 MySQL 查询分析工具。特别是在开发中大型项目时，往往有数百个查询分布在代码库中的各个角落，并实时对数据库进行大量访问和查询。如果没有一套趁手的分析方法和工具，就很难发现在执行过程中代码的效率瓶颈，我们需要通过这套工具去定位 SQL 语句在执行中缓慢的问题和原因。 本教程带领大家学习和实践 MySQL Server 内置的查询分析工具 —— 慢查询日志、mysqldumpslow、Profiling，详细讲解如何使用他们提升代码执行效率。如果你想根据自己.
数据库 之 存储引擎 索引 日志
power_hsun的博客
09-16 493
Python 列表(List) 序列是Python中最基本的数据结构。序列中的每个元素都分配一个数字 - 它的位置，或索引，第一个索引是0，第二个索引是1，依此类推。 Python有6个序列的内置类型，但最常见的是列表和元组。 序列都可以进行的操作包括索引，切片，加，乘，检查成员。 此外，Python已经内置确定序列的长度以及确定最大和最小的元素的方法。 列表是最常用的Python数据类型，它可以...
Mysql之慢查询日志、SQL、索引、配置、分库、分表优化
hukehukehukehuke的博客
05-27 265
1、数据库优化的目的 2、可以从哪几个方面进行数据库优化
mysql-sql优化-小米开源-soar
12-13
小米开源的SOAR（SQL Optimizer And Rewriter）是一个专门针对MySQL数据库的SQL优化工具，它旨在帮助开发者和DBA更有效地诊断和优化SQL查询。 SOAR的主要功能包括： 1. **SQL诊断**：通过对SQL语句的分析，提供慢...
收获，不止SQL优化--抓住SQL的本质
05-07
《收获，不止SQL优化--抓住SQL的本质》这本书深入浅出地探讨了SQL优化与数据库管理的核心概念，尤其针对Oracle数据库进行了详细的阐述。SQL是数据库查询语言，它的优化对于提升数据库性能至关重要。书中通过故事化的...
mysql面试题-mysql经典面试题目-数据库的基本概念-SQL语法-事务处理-索引优化-性能调优-mysql-面试题目
最新发布
01-20
【MySQL面试题】在面试MySQL相关的职位时，面试官可能会问到一系列关于数据库基础、SQL语法、事务处理、索引优化以及性能调优的问题。以下是一些可能的面试重点： 1. **数据库基本概念**： - 数据库是用于存储和...
SQL-Optimization.rar_oracle_sql优化
09-24
7. 优化索引：考虑使用复合索引、函数索引、位图索引等不同类型的索引来适应不同类型的查询。 四、SQL优化实战 在实际工作中，我们需要根据查询语句的具体情况，结合Oracle的特性进行优化。例如，分析慢查询日志，...
面试官:慢日志那么多，怎么找到最想优化的sql？
绅士jiejie的博客
04-05 332
这里就需要了解一下Mysql提供的日志分析工具mysqldumpslow 输入帮助命令了解下mysqldumpslow的参数： mysqldumpslow --help 参数 说明 s 表示按照何种方式排序 c 访问次数 I 锁定时间 r 返回记录 t 查询时间 al 平均锁定时间 ar 平均返回记录数 at 平均查询时间 t 返回前面多少条数据...
MySQL优化查询之慢查日志的使用
岁月安然
09-20 266
慢查日志的使用 mysql数据库自带了对sql语句的日志查询，在慢查日志中，你可以设置慢查日志中所包含语句执行时间是多长时间以上的。 凡是大于这个查询时间的都会记录到慢查日志中。mysql默认是不开启慢查日志的。需要我们手动进行设置。 慢查日志的设置 首先选定一个位置，新建一个日志文件这里用"mysql-show.sql" 设置慢查日志的文件为刚建的。在dos窗口中登录mysql后，输入： ...
给你100万条数据的一张表，你将如何查询优化？
weixin_34315189的博客
06-28 238
文章链接博客 https://blog.csdn.net/yongshuai185/article/details/55506210   1.两种查询引擎查询速度（myIsam 引擎 ） InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行。 MyISAM只要简单的读出保存好的行数即可。 注意的是，当c...
绝对神器，用 SQL查 Linux日志，查询效率高到飞起
zhenfanaz的博客
06-22 350
最近发现点好玩的工具，迫不及待的想跟大家分享一下。 大家平时都怎么查Linux日志呢？ 像我平时会用tail、head、cat、sed、more、less这些经典系统命令，或者awk这类三方数据过滤工具，配合起来查询效率很高。但在使用过程中有一点让我比较头疼，那就是命令参数规则太多了，记的人脑壳疼。 那查日志有没有一种通用的方式，比如用SQL查询，毕竟这是程序员都比较熟悉的表达式。 今天分享的工具q，就实现了以写SQL的方式来查询、统计文本内容，一起看看这货到底有什么神奇之处。 搭个环境 q是一个
sql server 各种时间日期查询 [转]
kekewf的博客
11-19 5067
sql server 各种时间日期与范围的查询
sql server 性能优化日志
你懂了我的冬天的博客
05-09 1009
sql server 性能优化日志 尽量减少行及操作，例如：在逻辑可以替代的情况下使用group by 而非 over函数 当某个表数据是缓慢流入，建议 加 时间字段上加聚集索引 当操作读取大表，且此大表全量更新，建议先drop table ， 然后 select into ，最后加索引 不使用 select * ...
日志表数据量大查询慢问题
zz_study
05-18 5335
问题​ 系统里的操作日志表，主要记录员工发起的各个请求(CRUD)，刚建了没几个月，已经上千万条记录了，导致页面查询的时候，大概需要3-5秒的时间，如果加上条件查询，基本5秒多了。这个时间是完全不能接受的，所以打算重新设计一下。日志表的元数据：(id,name,url,param,add_time…)分析现在表每天大概30-50w的数据增长，基本每秒都有十几条的数据插入写多查少，数据库没有做读写分...
mysql 通过日志表记录创建时间排序当前表_mysql优化——查询优化
weixin_29234423的博客
02-03 659
这一篇mysql优化是注重于查询优化，根据mysql的执行情况，判断mysql什么时候需要优化，关于数据库开始阶段的数据库逻辑、物理结构的设计结构优化不是本文重点，下次再谈查看mysql语句的执行情况，判断是否需要进行优化当感觉操作数据库查询语句速度变慢，不符合生产效率要求时，可按照以下步骤进行查看 1、 慢查询的开启与捕获，查看可能是哪些SQL语句造成的查询速度慢 2、 explain+SQL语...
mysql 慢查询日志的设置与优化
雪锋的笔记
12-08 1290
1 引言 MySQL数据中有记录慢查询的一种手段。并且是MySQL自带的。可用来排查那些查询sql语句执行得慢。从而给开发者提供一个调优得依据。 MySQL 慢查询的相关参数解释： slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。 long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。 slow-query-log-file：5.6及以上版本 MySQL数据库慢查询日志存储路径。 2 慢查询日志配置 默认情况下slow_que...
ORACLE-SQL性能优化: 选用合适的优化器，提高查询效率
常见的SQL优化技术包括合理使用索引、避免使用全表扫描、减少连接操作、使用查询优化器提示、避免使用SELECT *等方法。合理使用索引可以加快查询的速度，避免使用全表扫描可以减少数据库的开销，减少连接操作可以...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值