MySQL高级—Sql优化

最新推荐文章于 2023-03-17 23:55:37 发布
最新推荐文章于 2023-03-17 23:55:37 发布
阅读量186 收藏 1
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43778787/article/details/107508320
版权

MySQL高级

sql查询慢的原因

执行时间或者等待时间长

  1. 查询语句写的烂

  2. 索引失效，（要么没有建索引或者建了索引没有用上）索引包括单值索引(一个索引只包含单个列)和复合索引(一个索引包含多个列)、唯一索(索引列必须唯一)

  3. 关联查询用太多的join

  4. 服务器调优及各个参数设置（缓冲、线程数等）

MySQL执行顺序

程序员写的sql语句
select
from
join on 
where
group by
having 
order by
limit
机器读取顺序
from
on join
where 
group by
having 
select
order by
limit

七种JOIN

七种JOIN.png
JOIN续.png

索引

是一种帮助sql高效获取数据的数据结构，简单的理解为“排好序的快速查找的数据结构”所以使用索引快，默认是B+树

优势：

​ 类似图书馆建书目索引，田炯数据检索效率，降低数据库IO成本；通过索引列对数据进行排序，减低数据排序的成本，降低了CPU消耗

劣势：

​ 索引列也是要占用空间的，虽然索引大大提高了查询效率，同时也降低更新表的速度，如对表进行增删改操作，因为更新表时不仅要保存数据，还要保存索引文件每次更新添加了的字段

创建索引

create index 索引名称（一般格式idx_+多个字段名的驼峰原则） on 表名(字段1、字段2)

删除索引

drop index 索引名称 on 表名

查看索引

show index from 表名

哪些情况需要创建索引

  1. 主键会自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其它表关联的字段，外键关系建立索引
  4. 频繁更新的字段不适合创建索引
  5. where条件中用不到的字段不用建立索引
  6. 查询中排序的字段建立索引
  7. 查询中统计或者分组字段

哪些情况不需要创建索引

  1. 表记录太少
  2. 经常增删改的表——因为提高了查询效率，同时也降低更新表的速度，如对表进行增删改操作，因为更新表时不仅要保存数据，还要保存索引文件
  3. 数据重复且分布平均的表字段，

mysql常见瓶颈

CPU:cpu在饱和的时候，一般是发生在数据装入内存或从磁盘上读取数据时候

IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

服务器硬件性能的瓶颈

Explain

定义

​ 查看执行计划，使用explain可以模拟优化器执行SQL查询语句，从而知道Mysql是如何处理你的sql语句的，分析你的查询语句和表结构的性能瓶颈

作用：

  1. 知道表的读取顺序

  2. 数据读取操作的操作类型

  3. 哪些索引真实被使用

  4. 哪些索引被理论使用

  5. 表之间的引用

  6. 每张表有多少行别优化器查询

格式：

​ explain+SQL语句

包含的信息

expalin头部信息.png

各个字段的解释

explain信息1.png
explain信息2.png

explain信息3.png

优化案例

案例所用的库是window下的explain库

Explain执行计划单表 两表 三表 建表SQL语句
1. 单表

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
 
 
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,1,1,'3','3');
 
SELECT * FROM ARTICLE;

2. 两表建表SQL语句

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
 
 
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
 
 
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

3. 三张表的语句在上放的基础上多加一张表

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;
 
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

1、单表优化 表article

#	1、查询article表中category_id为1,且comments大于1的情况下，views最多的author_id 
explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
#	2、查看索引,只使用了一种主键索引
show index from article;
# 	3、创建索引(使用组合索引)来优化查询，因为使用explain发现type是All，而且没有用到索引，Extra还使用了Using filesort，查询效率低，使用索引之后type变成了range且使用了索引
CREATE INDEX idx_article_ccv on article(category_id,comments,views);
#	4、解决Extra中还使用了Using filesort，不要建三个字段的组合索引，因为comment>1是一个范围值，对后面的索引views就失效了，所以在这就建两个字段的索引，不要为comments建立索引
#	5、先删除索引，重新建立索引，这时再执行第二条语句查看，发现Extra没有使用Using filesort，此时优化成功
DROP INDEX idx_article_ccv ON article;
CREATE INDEX idx_article_cv on article(category_id,views);

2、两表优化 表class和表book

#	1、先查看join后的执行计划信息，看看哪里需要优化，type都为All，且没有用到索引，rows加起来是40，接下来进行优化，索引应该加在左表还是右表呢？经过测试左连接索引加在右表上，右连接加在左表上
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#	2、创建索引,查看第一条语句，type其中一个变成了ref，rows加起来才21，性能优化了
CREATE INDEX idx_book_card ON book(card);

3、三表优化 表book、表class、表phone

#	1、先删除三表中所有的索引，再进行测试
DROP INDEX idx_book_card ON book;
DROP INDEX idx_article_cv ON article;
#	2、使用左连接进行测试，开始查看执行计划信息时，type都为All，rows都为20，而且没有用到索引，所以可以进行性能优化
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card=phone.card
#	3、还是根据左连接索引加在右表上，右连接加在左表上，查看第一条语句，明显进行了性能优化了
CREATE INDEX idx_book_card ON book(card);
CREATE INDEX idx_phone_card ON phone(card);

索引优化

索引优化，防止索引失效

索引优化.png

**全值匹配我最爱：**指的是创建了几个索引列，就使用了几个。

**使用覆盖索引：**就是查询的字段（也可以是部分字段）正好就是索引列，避免使用select *，在Extra中就会出现一个using index，此时性能提升了

优化口诀

​ 例如为表user的字段name、age和password创建了索引 idx_user_nameAgePassword

第一句：最佳左前缀法则：简单理解为带头大哥必须有，中间兄弟不能断（在where后面name索引必须有，age不能断，否则会发生索引失效）

​ **例子：**第一条sql语句使用到了索引，其他的不行

SELECT * FROM user WHERE name='zs' AND age=12;
SELECT * FROM user WHERE age=12 AND password='123456';
SELECT * FROM user WHERE name='zs' AND password='123456';

**第二句：**索引列上无计算

​ **例子：**下面语句性能就不好了，创建的索引失效了

select * from user where left(name,3)='lzy';

**第三句：**范围之后全失效

​ **例子：**在age>4是一个范围，后面的password索引失效了，只用到了name和age索引

explain select * from user where name='zs' and age>4 and password='123456';

**第四句：**like百分号加右边，否则索引失效。如果非要用两个%，解决办法就是使用覆盖索引（不要使用select *），查询的字段（也可以是部分字段，但不能超出索引个数）与建立的索引个数和顺序最好要一样

​ 例子：

#创建索引后，
create index idx_user_nameAge on user(name,age);
#下面的语句索引失效,因为使用了select *
explain select * from user where name like '%zs%';
#使用覆盖索引
explain select name from user where name like '%zs%';
explain select name,age from user where name like '%zs%';
#下面一条语句的字段数超出了索引个数，所以索引失效了
explain select name,age,password from user where name like '%zs%';

**第五句：**varchar类型的字段的引号不能丢

​ **例子：**name字段是varchar类型的，其中有个name为200

explain select * from user where name='200';
#由于mysql内部会进行自动类型转换，将数字当字符串查询，变成了全表扫描，索引失效
explain select * from user where name=200

索引是否被用到

案例索引是否被用到.png

酒未醒～
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL高级SQL语句
weixin_55609814的博客
06-30 2万+
MySQL高级SQL语句MySQL高级SQL语句函数数学函数聚合函数字符串函数 MySQL高级SQL语句 use kgc; create table location (Region char(20),store_name char(20)); insert into location values ('East','Boston') ; insert into location values ('East','New York'); insert into location values ('west',
大道相通，得鱼忘筌 - 从Oracle的SQL优化到MySQL的SQL优化.pdf
06-16
接下来，我们将探讨Oracle和MySQL在SQL优化方面的共性和差异性，以及这些知识如何帮助数据库管理员（DBA）在不同环境中实施有效的SQL优化。 首先，我们要明确两个数据库系统的基本架构。Oracle数据库是一个功能强大...
MySQL高级优化_整理.docx
10-12
MySQL高级优化_整理.docx；mysql优化文档；mysql优化；
高级SQL优化(一)
jenmhdn
12-08 335
目录： Oracle数据完整性和锁机制  索引及优化之表分析  表分析、约束及表间关系  Oracle体系结构1 Oracle体系结构2  海量数据库及分区1  海量数据库及分区2  海量数据库及分区3  海量数据库及分区4  高级SQL优化(一)  PPT和源码下载：    http://sishuok.com/forum/posts/list/6...
高级SQL优化(二) ——《12年资深DBA教你Oracle开发与优化——性能优化部分》
jdnha的专栏
12-10 924
目录： Oracle数据完整性和锁机制  索引及优化之表分析  表分析、约束及表间关系  Oracle体系结构1 Oracle体系结构2  海量数据库及分区1  海量数据库及分区2  海量数据库及分区3  海量数据库及分区4  高级SQL优化(一)   高级SQL优化(二)   高级SQL优化(三) 常用优化工具  PPT和源码下载：    http://sishuok.
Mysql高级--SQL优化
m0_63526467的博客
04-13 246
文章目录5. SQL优化5.1 大批量插入数据5.2 优化insert语句5.3 优化order by语句5.3.1 环境准备5.3.2 两种排序方式5.3.3 Filesort 的优化5.4 优化group by 语句5.5 优化嵌套查询5.6 优化OR条件5.7 优化分页查询5.7.1 优化思路一5.7.2 优化思路二5.8 使用SQL提示5.8.1 USE INDEX5.8.2 IGNORE INDEX5.8.3 FORCE INDEX 5. SQL优化 5.1 大批量插入数据 环境准备 ： CREA
MySQL高级之SQL优化
qq_44005305的博客
03-17 165
之间的连接、关闭等消耗。优化后的方案为 ： insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；
mysql优化-sql高级
06-13
mysql优化从以下几个方面介绍 mysql的架构 索引优化分析 查询截取分析 mysql锁机制 主从复制
mysql高级, 深入学习sql优化, mysql底层
03-13
一、MySQL高级特性 1. 视图（Views）：视图是虚拟表，基于一个或多个表的查询结果。它允许用户创建复杂查询的简化表示，提高数据的安全性，通过权限控制限制用户对原始数据的访问。 2. 事务（Transactions）：事务...
Mysql高级性能优化思维导图
最新发布
07-28
MySQL高级性能优化的关键点： 数据库设计和规范化：确保数据库表结构合理、字段类型正确，并进行适当的规范化，以减少数据冗余和提高查询效率。 索引优化：根据查询需求创建适当的索引，避免过多或不必要的索引，...
MySQL性能优化 SQL优化方法技巧
05-23
MySQL性能优化 SQL优化方法技巧
【MySQL高级】SQL优化
专注于Java领域开发 关注我 带你玩转Java
11-04 492
SQL优化
Mysql——高级——SQL优化（四）
最初的模样
11-04 249
1、优化SQL步骤 （1）查看SQL执行频率 MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息，也可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。下面的命令显示了当前 session 中所有统计参数的值： show status like 'Com_______'; .
Mysql高级进阶（sql优化）
sunshunli的博客
08-15 759
一：Mysql高级有哪些东西？ 1、mysql的架构 架构这里我们主要说的是引擎： 看你的mysql现在已提供什么存储引擎: #看你的mysql当前默认的存储引擎: show variables like '%storage_engine%'; 各个存储引擎的介绍： 1、InnoDB存储引擎 InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的...
MySQL高级（SQL性能分析，SQL优化）
dyk
02-28 6848
SQL性能分析,SQL优化
MySQL数据库高级 & SQL优化
bad_boy的博客
09-09 108
标识列（自增长列） 可以不用手动插入值，系统提供默认的序列值 标识列不是必须和主键搭配的，但要求是个key（主键、唯一索引、外键） 一个表至多有一个标识列 标识列的数据类型只能是数值型 主键和唯一索引的区别 保证唯一性 是否允许为空 一个表可以有多少个 是否可组合 主键 √ × 至多有一个 √ 唯一 √ √（但只允许一个为null） 可以有多个 √ 存储过程和函数 类似 Java 中的方法，一组预先编译好的SQL语句，批处理语句 提高代码的重用性 简化操作 减少了编译次数并且
SQL优化经验（MySQL高级）
weixin_44050355的博客
08-29 471
1
MySQL之SQL优化（高级）
hcyxsh的博客
03-17 165
MySQL之SQL优化 一 批量插入数据 使用load命令 （1）前提需要数据是有序的，否则耗时长。 load datat local infile '数据文件路劲' into table '表' fields terminated by ',' lines terminated by '\n';--字段间以逗号隔开，每行之间以换行符隔开 （2）关闭唯一性校验 在导入数据前执行SET UNIQUE_CHECKS=0,关闭唯一性校验，在导入后执行SET UNIQUE_CHECKS=1开启唯一性校验，可以提
mysql 高级优化sql语句
jinQiangol的博客
08-03 154
1、当需要单表有大量的数据，用到分页时的优化sql 应用场景：分页查询流水表 SELECT * FROM test a INNER JOIN (SELECT ID FROM test LIMIT 1500000, 10) b on a.ID = b.ID 先通过分页查询聚簇索引的ID，再和原表关联取交集。 2、group by 分组函数要使用标识列（主键列）来分组才能命中索引（如果用position表的外键来分组，不会走索引） select * from account a,p...
MySQL高级优化与Linux安装指南
- **SQL优化**：编写高效的查询语句，利用索引、避免全表扫描、减少JOIN操作，以及合理使用子查询和临时表。 - **服务器优化**：调整系统参数，如innodb_buffer_pool_size、max_connections等，以适应特定的工作...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值