SQL优化

最新推荐文章于 2023-07-10 13:09:48 发布
最新推荐文章于 2023-07-10 13:09:48 发布
阅读量1.7k 收藏 2
点赞数 4
文章标签： mysql 索引 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44231137/article/details/108474177
版权

EXPLAIN

索引的优势和劣势

索引简单来说是排好序的数据结构，所以它能大幅度的提高查询效率，降低数据排序的成本，降低数据库的IO成本。

但是索引也同样占用很大空间，通常是以索引文件的形式存储在磁盘上，并且更新数据的同时得更新相应的索引。

总的来说：索引能提高查询效率，但降低更新效率，所以经常更新的表尽量不要加索引

索引的分类和索引命令语句

索引分类

  1. 单值索引：一个索引只包含单个列，一个表可以有多个单值索引。
  2. 唯一索引：索引列的值必须唯一，但允许有空值
  3. 复合索引：一个索引包含多个列

一张表建议建立索引不要超过五个

基本语法

  1. 创建：
    (1)CREATE [UNIQUE] INDEX indexname ON mytable(columnname(length));
    (2)ALTER mytable ADD [UNIQUE] INDEX [indexname] ON (columnname(length));
  2. 删除：DROP INDEX [indexname] ON mytable;
  3. 查看：SHOW INDEX FROM table_name\G;
    (\G存在的情况下列形式显示，不存在的情况下行形式显示)
  4. 使用ALTER命令：ALTER TABLE mytable ADD (PRIMARYKEY)((UNIQUE|INDEX|FULLTEXT)indexname)(column_list);
    PRIMARYKEY：唯一索引(主键索引，一般自动添加)，索引值唯一，且不能为null。
    UNIQUE：唯一索引，索引值唯一，可出现null，且null可重复出现。
    INDEX：普通索引，索引值可出现多次。
    FULLTEXT：全文索引。

判断是否适合建索引

适合建立索引的情况

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段
  3. 查询中与其它表关联的字段
  4. 查询中排序的字段
  5. 查询中统计或分组的字段

不适合建立索引的情况

  1. 频繁更新的字段（更新不止是更新记录，还得更新索引）
  2. Where语句用不到的字段
  3. 表的记录不多
  4. 频繁增删改的表
  5. 数据列大量重复内容（如：性别，只有 男，女）

一般来说复合索引优于单值索引

性能分析

MySql Query Optimizer

MySql自带的查询优化器，MySql有专门负责优化SELECT语句的优化器模块。

MySql 常见瓶颈

  1. CPU：CPU在饱和的时候，一般发生在数据装入内存或者从磁盘读取数据的时候。
  2. IO：磁盘I/O瓶颈常发生在装入数据远大于内存容量的时候。
  3. 服务器硬件问题。

EXPLAIN(查看执行计划)

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySql是如何处理你的SQL语句，从中得出如何优化你的SQL语句。

如何使用？

在SQL语句前+EXPLAIN关键字（如：explain select * from userdb）

包含信息

  1. id：select查询的序列号，表示操作顺序，id相同的情况下顺序由上到下，id不同的情况下顺序由大到小。
  2. select_type：查询类型，主要用于区别普通查询，联合查询，嵌套查询等的复合查询。
  3. table：显示这一行数据是关于哪张表
  4. type：从好到怀：system(表只有一行记录，系统表)>const(通过一次索引就找到)>eq_ref(唯一索引扫描)>ref(非唯一索引扫描)>range(只索引给定范围)>index(全索引扫描)>all(全表扫描) 一般来说要保证达到range级别
  5. possible_keys：可能用到的哪些索引
  6. key：实际用到的索引
  7. key_len：索引中使用的字节数，在不损失精确性的情况下，越小越好。
  8. ref：显示索引的哪些列被使用
  9. rows：查找到所需数据的行数
  10. extra： 加分项：USING index , 扣分项：USING filesort、USING temporary（有扣分项的情况下尽量优化）

EXPLAIN实际运用

检验是否有效

  • 创建表
CREATE TABLE IF NOT EXISTS blogs(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) UNSIGNED NOT NULL,
category VARCHAR(20) NOT NULL,
views INT(20) NOT NULL,
assist INT(20) NOT NULL,
comments VARCHAR(20) NOT NULL,
title VARCHAR(255) NOT null,
content TEXT NOT NULL);
  • 插入数据
INSERT INTO blogs(author_id,category,views,assist,comments,title,content)VALUES
(1,'java',821,100,'1','java','1'),
(2,'sql',534,100,'2','sql','1'),
(3,'c',1928,100,'3','c','1'),
(4,'c++',851,100,'4','c++','1'),
(5,'c#',89,50,'5','c#','1');
  • 没有建立索引的情况下用EXPLAIN：
EXPLAIN SELECT id, author_id  from blogs where comments = '1' and views > 1 ORDER BY assist desc LIMIT 1;

在这里插入图片描述

  • 查找表中的索引
show index from blogs;

这里只有sql自主建的主键索引
在这里插入图片描述

  • 建立索引
create index idx_blogs_cva on blogs (comments,views,assist);
  • 再查找表中的索引
show index from blogs

在这里插入图片描述

  • 再用EXPLAIN查看性能
EXPLAIN SELECT id, author_id  from blogs where comments = '1' and views > 1 ORDER BY assist desc LIMIT 1;

在这里插入图片描述
这里很明显已经用到了索引，但是Extra里面出现了Using filesort，SQL需要优化

  • 优化方式：删除索引，再建立comments，assist索引（具体原因，后面会说到）
Drop index idx_blogs_cva on blogs;
create index idx_blogs_ca on blogs(comments,assist);
EXPLAIN SELECT id, author_id  from blogs where comments = '1' and views > 1 ORDER BY assist desc LIMIT 1;

在这里插入图片描述

检验如何创建索引更高效

  • 创建表
CREATE TABLE IF NOT EXISTS class(
	class_id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	class_card INT(20) UNSIGNED NOT NULL
);

CREATE TABLE IF NOT EXISTS book(
	book_id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	book_card INT(20) UNSIGNED NOT NULL
);
  • 插入数据
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
INSERT INTO book(book_card) VALUES(FLOOR(1+(RAND()*10)));
  • 在没建索引的情况下用EXPLAIN查看两表连接
EXPLAIN SELECT * FROM book RIGHT JOIN class ON class_card = book_card;

在这里插入图片描述

  • 建立索引优化
ALTER TABLE book ADD INDEX idx_book_c (book_card);
  • 再用EXPLLAIN查看
EXPLAIN SELECT * FROM book RIGHT JOIN class ON class_card = book_card;

在这里插入图片描述

  • 建立class的索引，再用EXPLAIN
ALTER TABLE class ADD INDEX idx_class_c (class_card);
EXPLAIN SELECT * FROM book RIGHT JOIN class ON class_card = book_card;

在这里插入图片描述

-删除book索引，再用EXPLAIN

DROP index idx_book_c on book;
EXPLAIN SELECT * FROM book RIGHT JOIN class ON class_card = book_card;

在这里插入图片描述
很明显效率不如添加book表的索引好
总结一下：right join (右连接) ，用左表建索引效果高于右表，left join (左连接) 同理，即：与连接相对的表建立索引效果较好。 尽量用小结果集驱动大结果集（如右连接，就是左表驱动右边，在左表建立索引的效果要好于右表）。

用EXPLAIN得出调优结论

最佳左前缀法则

查询应从索引的最左前列开始并且不跳过索引中的列

  • 首先建立表并插入一些数据，创建表，并用EXPLAIN查看索引是否失效
CREATE TABLE students(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT(10) UNSIGNED NOT NULL DEFAULT 0,
grade INT(10) UNSIGNED NOT NULL  DEFAULT 0,
pos VARCHAR(20)  DEFAULT ''
);

INSERT INTO students(name,age,grade,pos) VALUES ('zs',20,97,'monitor');
INSERT INTO students(name,age,grade,pos) VALUES ('ls',22,74,'committee');
INSERT INTO students(name,age,grade,pos) VALUES ('ww',21,100,'committee');
INSERT INTO students(name,age,grade,pos) VALUES ('mw',21,59,'personal');
INSERT INTO students(name,age,grade,pos) VALUES ('zl',21,87,'personal');

ALTER TABLE students ADD INDEX idx_students_nap (name, age, pos);

EXPLAIN SELECT * FROM students WHERE name='zs';

在这里插入图片描述

EXPLAIN SELECT * FROM students WHERE name='zs' AND age=20;

在这里插入图片描述

EXPLAIN SELECT * FROM students WHERE name='zs' AND age=20 AND pos='monitor';

在这里插入图片描述
很明显，三条语句依次是增加了精度，同时key_len也增加，但是都用到了索引
现在看看索引失效的情况：

EXPLAIN SELECT * FROM students WHERE  age=20 AND pos='monitor';

在这里插入图片描述

EXPLAIN SELECT * FROM students WHERE  pos='monitor';

在这里插入图片描述

EXPLAIN SELECT * FROM students WHERE  age=20;

在这里插入图片描述
这三次效果一样，都是全表扫描

避免函数、计算、类型转换等操作

在select语句里面进行函数、计算、类型转换等操作，会使索引失效，从而变为全表扫描

EXPLAIN SELECT * FROM students WHERE LEFT(name,2) = 'mw';

在这里插入图片描述

EXPLAIN SELECT * FROM students WHERE name = 'mw';

在这里插入图片描述

尽量不要使用不等于（即：!= 或 <>）

在where子句中使用不等于的时候无法使用索引。

EXPLAIN SELECT * FROM students WHERE name <>'mw';

在这里插入图片描述

尽量不要用’%name’ 或’%name%'的LIKE操作（'name%'可用）

EXPLAIN SELECT * FROM students WHERE name like'%mw';

在这里插入图片描述

EXPLAIN SELECT * FROM students WHERE name like'mw%';

在这里插入图片描述

墨屋丶
关注
  • 4
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL 优化
09-11
SQL 优化
聊聊sql优化的15个小技巧
lisu061714112的专栏
11-10 8137
前言 sql优化是一个大家都比较关注的热门话题，无论你在面试，还是工作中，都很有可能会遇到。 如果某天你负责的某个线上接口，出现了性能问题，需要做优化。那么你首先想到的很有可能是优化sql语句，因为它的改造成本相对于代码来说也要小得多。 那么，如何优化sql语句呢？ ​ 我的个人技术博客：https://susan.net.cn​​​​​​​ ​ 这篇文章从15个方面，分享了sql优化的一些小技巧，希望对你有所帮助。 最近无意间获得一份BAT大厂大佬写的刷题笔记，一下子打通了我的任督二脉，越来越觉得算法没
sql优化的15个小技巧
weixin_56017850的博客
04-12 1万+
最近找了找怎么优化SQL,总结了15个基础技巧 因为最近一直在写sql的原因,所以需要知道sql该怎么优化,怕哪一天线上的接口,出了问题,需要优化,就需要采用改造成本最小的. 先上个思维导图 1.避免使用select* ...
SQL优化的15个小技巧，纯干货分享！
黄团团的个人博客
07-10 1万+
普通索引最基本的索引，没有任何限制唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值主索引即主键索引，关键字PRIMARY复合索引为了更多的提高MySQL效率可建立组合索引，遵循“最左前缀”原则全文索引可用于MyISAM表，MySQL5.6之后也可用于innodb表，用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时和空间以上就是目前我对SQL优化的一些个人理解和总结，如果遗漏，欢迎评论区补充说明！
sql优化常用的几种方法：19种最有效的sql优化技巧
Lost_in_the_woods的博客
07-07 2万+
我们来谈谈项目中常用的MySQL优化方法，共19条，具体如下：1、EXPLAIN做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例，标注（1、2、3、4、5）我们要重点关注的数据：MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。3、SE
【mysql】SQL优化15种方法
A-Itfuture的博客
03-09 6328
其实，开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化， 但是，随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。还
mysql数据库sql优化
10-21
1. SQL优化 1 1.1. 优化实战 1 1.1.1. 策略1.尽量全值匹配 1 1.1.2. 策略2.最佳左前缀法则 2 1.1.3. 策略3.不在索引列上做任何操作 2 1.1.4. 策略4.范围条件放最后 3 1.1.5. 策略5.覆盖索引尽量用 3 1.1.6. 策略6.不...
SQL优化 SQL优化软件 SQL优化工具
12-14
SQL优化 SQL优化软件 SQL优化工具 很好用的工具，可以分析优化TSQL语句，oracle数据库语句优化工具
sql优化
11-13
sql优化
收获不止SQL优化
08-07
第2章　风驰电掣——有效缩短SQL优化过程 24 2.1　SQL调优时间都去哪儿了 25 2.1.1　不善于批处理频频忙交互 25 2.1.2　无法抓住主要矛盾瞎折腾 25 2.1.3　未能明确需求目标白费劲 26 2.1.4　没有分析操作难度乱调优...
SQL - SQL优化的几种方法
MinggeQingchun的博客
10-26 1541
13、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。10、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
navicat sql server_分析两分钟，跑数两小时？这份SQL优化大全建议收藏
weixin_39619858的博客
11-25 1004
分析两分钟，跑数两小时？在使用SQL过程中不仅要关注数据结果，同样要注意SQL语句的执行效率。本文涉及三部分，篇幅较长，建议收藏后翻看：SQL介绍SQL优化方法SQL优化实例1、MySQL的基本架构1)MySQL的基础架构图左边的client可以看成是客户端，客户端有很多，像我们经常你使用的CMD黑窗口，像我们经常用于学习的WorkBench，像企业经常使用的Navicat工具，它们都是一个客户端...
sql优化的N种方法_持续更新
热门推荐
wang5701071的博客
09-25 8万+
当你访问网站的时候,有的时候会慢的想让你砸电脑,这个时候服务器要背锅了吗? 不,要背锅的不仅仅是服务器,数据库也有很大责任,不负责任的sql开发者更会让你崩溃的.为了提高sql响应速度,还是好好了解下sql的优化吧 sql优化的方式 1: explain关键字 :使用explain关键字来查看当前sql语句的执行情况,来对症下药.因为内容较多,放在最后进行讲解 2: 正确的建立索引:索引的建立对sql的影响是非常大的,如果对索引不太理解的可以看我另一篇文章: ............
SQL优化的方法
m0_45312259的博客
05-30 7007
（1）建立物化视图或尽可能减少多表查询。（2）以不相干子查询替代相干子查询。（3）只检索需要的列。（4）用带in的条件子句等价替换or子句。（5）经常提交commit，以尽早释放锁。（6）避免嵌套的游标（Cursor）和多重循环等。（7）在经常查询的列上创建索引，提高查询效率。（8）避免使用模糊查询进行匹配，如果一定要使用，建议使用最左模糊匹配原则。（9）慢的查询的sql，根据性能和存储容量大小进行评估，适当的可以考虑水平分表和垂直分表，以提高sql的查询性能。
12条 SQL 优化方案（非常实用）
qq_43842093的博客
06-23 5016
一、SQL语句及索引的优化SQL语句的优化索引的优化/如何避免索引失效二、数据库表结构的优化：使得数据库结构符合三大范式与BCNF三、系统配置的优化四、硬件的优化在开始介绍如何优化sql前，先附上mysql内部逻辑图让大家有所了解主要负责跟客户端建立连接、获取权限、维持和管理连接优先在缓存中进行查询，如果查到了则直接返回，如果缓存中查询不到，在去数据库中查询。MySQL缓存是默认关闭的，也就是说不推荐使用缓存，并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。
数据库SQL优化
qq：489366879
04-27 322
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：  select id from t where num is null 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库. 备注、描述、评论
SQL语句优化的30种方法，总结的太棒了
biyusr的专栏
07-06 2万+
1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。2、应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： 4、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： ..
sql优化的15个小技巧（必知五颗星），面试说出七八个就有了
GQ_666的博客
01-13 4万+
目录 前言 1 避免使用select * 2 用union all代替union 3 小表驱动大表 4 批量操作 5 多用limit 6 in中值太多 7 增量查询 8 高效的分页 9 用连接查询代替子查询 10 join的表不宜过多 11 join时要注意 12 控制索引的数量 13 选择合理的字段类型 14 提升group by的效率 15 索引优化 前言 sql优化是一个大家都比较关注的热门话题，无论你在面试，还是工作中，都很有可能会遇到。 如果某天你负责的某
sqlserver sql优化
最新发布
09-22
SQL Server的SQL优化可以采取以下几种方式： 1. 使用合适的索引：通过在查询中使用索引，可以加快查询速度。需要根据具体情况选择合适的索引类型，并确保索引的列与查询条件或连接条件相匹配。 2. 使用合适的连接...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值