Sql优化总结！

最新推荐文章于 2022-04-19 19:56:01 发布
最新推荐文章于 2022-04-19 19:56:01 发布
阅读量262 收藏 5
点赞数 1
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liuerchong/article/details/118444746
版权

文章目录

sql执行很慢的原因

偶尔很慢

数据库在刷新脏页（flush）

当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

刷脏页有下面4种场景（后两种不用太关注“性能”问题）：

  • redo log写满了：redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
  • 内存不够用了：如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。
  • MySQL 认为系统“空闲”的时候：这时系统没什么压力。
  • MySQL 正常关闭的时候：这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

拿不到锁

这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了，这个时候，只能等。

一直这么慢

没有用到索引（索引失效）

（1）字段没有建立索引，进行全表扫描——sql语句写的很烂

select * from t where 100 < c and c < 100000;

刚好你的 c 字段上没有索引，那么抱歉，只能走全表扫描了，你就体验不会索引带来的乐趣了，所以，这回导致这条查询语句很慢。

（2）字段有索引，但却没有用索引（索引失效）

列举一种索引失效的情况：

​ 1）在索引列上做运算的时候不会走索引

select * from t where c - 1 = 1000;
正确的应该是：
select * from t where c = 1000 + 1;

​ 2）对字段进行了函数操作，不会用上索引

select * from t where pow(c,2) = 1000;

假设函数 pow 是求 c 的 n 次方，实际上可能并没有 pow(c,2)这个函数。其实这个和上面在左边做运算也是很类似的。

数据库选错了索引

select * from t where 100 < c and c < 100000;

主键索引和非主键索引是有区别的，主键索引存放的值是整行字段的数据，而非主键索引上存放的值不是整行字段的数据，而且存放主键字段的值。

我们如果走 c 这个字段的索引的话，最后会查询到对应主键的值，然后，再根据主键的值走主键索引，查询到整行数据返回。

就算你在 c 字段上有索引，系统也并不一定会走 c 这个字段上的索引，而是有可能会直接扫描扫描全表，找出所有符合 100 < c and c < 100000 的数据。

查看SQL 执行情况

在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化

查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值：

SQL

show status like 'Com_______';show status like ‘Innodb_rows_%’;
在这里插入图片描述
Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

Com_select 执行 select 操作的次数，一次查询只累加 1。
Com_insert 执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update 执行 UPDATE 操作的次数。
Com_delete 执行 DELETE 操作的次数。
Innodb_rows_read select 查询返回的行数。
Innodb_rows_inserted 执行 INSERT 操作插入的行数。
Innodb_rows_updated 执行 UPDATE 操作更新的行数。
Innodb_rows_deleted 执行 DELETE 操作删除的行数。
Connections 试图连接 MySQL 服务器的次数。
Uptime 服务器工作时间。
Slow_queries 慢查询的次数。
Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。

Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

  • 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。具体可以查看本书第 26 章中日志管理的相关部分。
  • show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

在这里插入图片描述

`1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看

`2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

`3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

4） db列，显示这个进程目前连接的是哪个数据库

5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

6） time列，显示这个状态持续的时间，单位是秒

7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

8） info列，显示这个sql语句，是判断问题语句的一个重要依据`

explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划 ：

SQL

explain select * from tb_item where id = 1;

在这里插入图片描述
字段 含义

id select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table 输出结果集的表
type 表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------> all )
possible_keys 表示查询时，可能使用的索引
key 表示实际使用的索引
key_len 索引字段的长度
rows 扫描行的数量
extra 执行情况的说明和描述

explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种 ：

1） id 相同表示加载表的顺序是从上到下。

SQL

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;
在这里插入图片描述
id 不同id值越大，优先级越高，越先被执行。

SQL

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))
在这里插入图片描述
3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

SQL

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.user_id= '2') a WHERE r.id = a.role_id ;
在这里插入图片描述

explain 之 select_type

表示 SELECT 的类型，常见的取值，如下所示：

SIMPLE 简单的select查询，查询中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY 在SELECT 或 WHERE 列表中包含了子查询
DERIVED 在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION 若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT 从UNION表获取结果的SELECT

explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

NULL MySQL不访问任何表，索引，直接返回结果
system 表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const 表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range 只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all 将遍历全表以找到匹配的行
结果值从最好到最坏以此是：

PERL

`NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system > const > eq_ref > ref > range > index > ALL`
一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。

explain 之 key

PROPERTIES

`possible_keys : 显示可能应用在这张表的索引， 一个或多个。

key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。`

trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。

打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SQL

SET optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000;

SQL

select * from tb_item where id < 4;
最后， 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：

SQL

select * from information_schema.optimizer_trace\G;

Sql执行

顺序

  • (8) SELECT(9) DISTINCT column,… 选择字段 、去重

  • (6) AGG_FUNC(column or expression),… 聚合函数

  • (1) FROM [left_table] 选择表

  • (3) <join_type> JOIN <right_table> 链接

  • (2) ON <join_condition> 链接条件

  • (4) WHERE <where_condition> 条件过滤

  • (5) GROUP BY <group_by_list> 分组

  • (7) HAVING <having_condition> 分组过滤

  • (10) ORDER BY <order_by_list> 排序

  • (11) LIMIT count OFFSET count; 分页

实例说明

select[distinct]  
from  
join（如left join）  
on  
where  
group by  
having  
union  
order by  
limit

顺序：FROM——ON——JOIN——WHERE——GROUP BY——SUM、COUNT——HAVING——SELECT——DISTINCT——ORDER BY——LIMIT

与写SQL的顺序不同，SQL的执行顺序并不是从select开始，而是从from开始

1、FROM：先去获取from里面的表，拿到对应的数据，生成虚拟表1。

2、ON：对虚拟表1应用ON筛选，符合条件的数据生成虚拟表2。

3、JOIN：根据JOIN的类型去执行相对应的操作，获取对应的数据，生成虚拟表3。

4、WHERE：对虚拟表3的数据进行条件过滤，符合记录的数据生成虚拟表4。

5、GROUP BY：根据group by中的列，对虚拟表4进行数据分组操作，生成虚拟表5。

6、CUBE|ROLLUP（聚合函数使用）：主要是使用相关的聚合函数，生成虚拟表6。

7、HAVING：对虚拟表6的数据过滤，生成虚拟表7，这个过滤是在where中无法完成的，同时count（expr）返回不为NULL的行数，而count（1）和count（*）是会返回包括NULL在内的行数。

8、SELECT：选择指定的列，生成虚拟表8。

9、DISTINCT：数据去重，生成虚拟表9。

10、ORDER BY：对虚拟表9中的数据进行指定列的排序，生成虚拟表10。

11、LIMIT：取出指定行的记录，生成虚拟表11，返回给查询用户。

基础Sql优化

查询SQL尽量不要使用select *，而是具体字段

反例：

SELECT * FROM student

正例：

SELECT id,NAME FROM student

理由：

  • 字段多时，大表能达到100多个字段甚至达200多个字段
    只取需要的字段，节省资源、减少网络开销
    select * 进行查询时，很可能不会用到索引，就会造成全表扫描

避免在where子句中使用or来连接条件

查询id为1或者薪水为3000的用户：

反例：

SELECT * FROM student WHERE id=1 OR salary=30000

正例：
使用union all

SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000

分开两条sql写

SELECT * FROM student WHERE id=1
SELECT * FROM student WHERE salary=30000

理由：

  • 使用or可能会使索引失效，从而全表扫描
    对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的

in 和 not in 也要慎用，否则会导致全表扫描

select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)
用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

使用varchar代替char

反例：

deptname char(100) DEFAULT NULL COMMENT ‘部门名称’
正例：

deptname varchar(100) DEFAULT NULL COMMENT ‘部门名称’

理由：

  • varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
  • char按声明大小存储，不足补空格
  • 其次对于查询来说，在一个相对较小的字段内搜索，效率更高

尽量使用数值替代字符串类型

  • 主键（id）：primary key优先使用数值类型int，tinyint
  • 性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
  • 支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
  • 服务状态（state）：1-开启、2-暂停、3-停止
  • 商品状态（state）：1-上架、2-下架、3-删除

查询尽量避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。
通常采用分页，一页习惯10/20/50/100条。

使用explain分析你SQL执行计划

SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。

EXPLAIN
SELECT * FROM student WHERE id=1

是否使用了索引及其扫描类型

SQL索引概念（详解B+树）

type：

  • ALL 全表扫描，没有优化，最慢的方式
  • index 索引全扫描
  • range 索引范围扫描，常用语<，<=，>=，between等操作
  • ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
  • eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
  • const 当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理
  • null MySQL不访问任何表或索引，直接返回结果
  • System 表只有一条记录(实际中基本不存在这个情况)

性能排行：

System > const > eq_ref > ref > range > index > ALL

possible_keys：

显示可能应用在这张表中的索引

key：

真正使用的索引方式
创建name字段的索引
提高查询速度的最简单最佳的方式

ALTER TABLE student ADD INDEX index_name (NAME)

优化like语句：

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效

反例：

EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE ‘%1’
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE ‘%1%’

正例：

EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE ‘1%’

理由：
未使用索引：故意使用sex非索引字段

EXPLAIN
SELECT id,NAME FROM student WHERE NAME=1 OR sex=1

主键索引生效

EXPLAIN
SELECT id,NAME FROM student WHERE id=1

索引失效，type=ALL，全表扫描

EXPLAIN
SELECT id,NAME FROM student WHERE id LIKE ‘%1’

字符串怪现象

反例：

#未使用索引
EXPLAIN
SELECT * FROM student WHERE NAME=123

正例：

#使用索引
EXPLAIN
SELECT * FROM student WHERE NAME=‘123’

理由：

  • 为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

索引不宜太多，一般5个以内

_ 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间

  • 再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要
    insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定
  • 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要

索引不适合建在有大量重复数据的字段上

如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引
列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

where限定查询的数据

数据中假定就一个男的记录

反例：

SELECT id,NAME FROM student WHERE sex=‘男’

正例：

SELECT id,NAME FROM student WHERE id=1 AND sex=‘男’

理由：

需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销
避免在索引列上使用内置函数
业务需求：查询最近七天内新生儿（用学生表替代下）

给birthday字段创建索引：

ALTER TABLE student ADD INDEX idx_birthday (birthday)

当前时间加7天：

SELECT NOW()
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)

反例：

EXPLAIN
SELECT * FROM student
WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();

正例：

EXPLAIN
SELECT * FROM student
WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);

理由：

  • 使用索引列上内置函数

索引失效：

在这里插入图片描述

索引有效：
在这里插入图片描述

避免在where中对字段进行表达式操作

反例：

EXPLAIN
SELECT * FROM student WHERE id+1-1=+1

正例：

EXPLAIN
SELECT * FROM student WHERE id=+1-1+1

EXPLAIN
SELECT * FROM student WHERE id=1

理由：

  • SQL解析时，如果字段相关的是表达式就进行全表扫描

  • 字段干净无表达式，索引生效

避免在where子句中使用!=或<>操作符（mysql5.5后也会走索引）

应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

反例：

EXPLAIN
SELECT * FROM student WHERE salary!=3000

EXPLAIN
SELECT * FROM student WHERE salary<>3000

理由：

  • 使用!=和<>很可能会让索引失效

去重distinct过滤字段要少

#索引失效
EXPLAIN
SELECT DISTINCT * FROM student

#索引生效
EXPLAIN
SELECT DISTINCT id,NAME FROM student

EXPLAIN
SELECT DISTINCT NAME FROM student

理由：

  • 带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间
    where中使用默认值代替null
    环境准备：

#修改表，增加age字段，类型int，非空，默认值0
ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;

#修改表，增加age字段的索引，名称为idx_age
ALTER TABLE student ADD INDEX idx_age (age);

反例：

EXPLAIN
SELECT * FROM student WHERE age IS NOT NULL
正例：

EXPLAIN
SELECT * FROM student WHERE age>0

理由：

  • 并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关
    如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的
    如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点

进行批量操作

批量插入性能提升

大量数据提交，上千，上万，批量性能非常快，mysql独有

多条提交：

INSERT INTO student (id,NAME) VALUES(4,‘name1’);
INSERT INTO student (id,NAME) VALUES(5,‘name2’);

批量提交：

INSERT INTO student (id,NAME) VALUES(4,‘name1’),(5,‘name2’);

理由：

  • 默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升
    数据量小体现不出来

批量删除优化

避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

伪删除设计

商品状态（state）：1-上架、2-下架、3-删除

理由：

  • 这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
    同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
  • 通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
  • 操作速度快，特别数据量很大情况下

提高group by语句的效率

可以在执行到该语句前，把不需要的记录过滤掉

反例：先分组，再过滤

select job，avg（salary） from employee
group by job
having job =‘president’ or job = ‘managent’;

正例：先过滤，后分组

select job，avg（salary） from employee
where job =‘president’ or job = ‘managent’
group by job;

复合索引最左特性

创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

EXPLAIN
SELECT * FROM student WHERE NAME=‘name1’

没有出现左边的字段，则不满足最左特性，索引失效

EXPLAIN
SELECT * FROM student WHERE salary=3000

复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAIN
SELECT * FROM student WHERE NAME=‘陈子枢’ AND salary=3000

虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME=‘name1’

理由：

  • 复合索引也称为联合索引
    当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
    联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

排序字段创建索引

什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。

#使用*，包含了未索引的字段，导致索引失效
EXPLAIN
SELECT * FROM student ORDER BY NAME;

EXPLAIN
SELECT * FROM student ORDER BY NAME,salary

#name字段有索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME

#name和salary复合索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME,salary

EXPLAIN
SELECT id,NAME FROM student ORDER BY salary,NAME

#排序字段未创建索引，性能就慢
EXPLAIN
SELECT id,NAME FROM student ORDER BY sex

删除冗余和重复的索引

SHOW INDEX FROM student

#创建索引index_name
ALTER TABLE student ADD INDEX index_name (NAME)

#删除student表的index_name索引
DROP INDEX index_name ON student ;

#修改表结果，删除student表的index_name索引
ALTER TABLE student DROP INDEX index_name ;

#主键会自动创建索引，删除主键索引
ALTER TABLE student DROP PRIMARY KEY ;

不要有超过5个以上的表连接

关联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高
如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

阿里规范中，建议多表联查三张表以下
inner join 、left join、right join，优先使用inner join
三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

  • inner join 内连接，只保留两张表中完全匹配的结果集

  • left join会返回左表所有的行，即使在右表中没有匹配的记录

  • right join会返回右表所有的行，即使在左表中没有匹配的记录
    理由：

  • 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
    同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

in子查询的优化

日常开发实现业务需求可以有两种方式实现：

一种使用数据库SQL脚本实现
一种使用程序实现
如需求：查询所有部门的所有员工：
#in子查询
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#这样写等价于：

#先查询部门表
SELECT id FROM tb_dept

#再由部门dept_id，查询tb_user的员工
SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：

List<> resultSet;
for(int i=0;i<B.length;i++) {
for(int j=0;j<A.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}

上面的需求使用SQL就远不如程序实现，特别当数据量巨大时。

理由：

数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿
尽量使用union all替代union
反例：

SELECT * FROM student
UNION
SELECT * FROM student

正例：

SELECT * FROM student
UNION ALL
SELECT * FROM student

理由：

  • union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
  • union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
    union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION
liu1002880404
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
专栏目录
SQL设计的优化
zrh_CSDN的博客
07-29 247
在执行数据库的查询时，如果要查询的数据有很多，假设有2000万条，用什么方法可以提高查询效率(速度)？在数据库设计，SQL设计方面有什么优化的方法？ 注：本题考查的是大数据量数据条件下，数据库设计和数据查询优化的问题，是数据库知识的深度考查。 结合本题目，已知数据量为2000万条，为了提高查询效率，需要从数据库的设计、SQL设计方面入手，结合数据库的基础知识，尽量多地总结出提高查询效率的方法，...
MySQL高级——Explain信息中select_type字段解释
小志的博客
02-06 5192
1、select_type类型有哪些 序号 select_type 类型 含义 1 SIMPLE 简单的 select 查询,查询中不包含子查询或者UNION 2 PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为Primary 3 SUBQUERY 在SELECT或WHERE列表中包含了子查询 4 DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生);MySQL会递归执行这些子查询, 把结果放在临时表里。 5 UNION 若第二个SEL
mysql select_type simple_9.2.6 select_type
weixin_42701915的博客
01-27 665
9.2.6 select_typeselect_type列提供了各种表示table列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED和UNION。其他可能的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION以及UNCACHEABLE QUERY。1. SIMPLE对于不包含子查询...
Sql优化总结！详细！（2021最新面试必问）
热门推荐
布诺的博客
04-09 13万+
Sql优化基础Sql优化查询SQL尽量不要使用select *，而是具体字段避免在where子句中使用or来连接条件使用varchar代替char尽量使用数值替代字符串类型查询尽量避免返回大量数据使用explain分析你SQL执行计划是否使用了索引及其扫描类型创建name字段的索引优化like语句：字符串怪现象索引不宜太多，一般5个以内索引不适合建在有大量重复数据的字段上where限定查询的数据避免在索引列上使用内置函数避免在where中对字段进行表达式操作避免在where子句中使用!=或<>操
为什么不能使用select * 而要使用select 具体字段查询数据？
qq_42852587的博客
02-25 535
为什么不能使用select * 而要使用select 具体字段查询数据？ 根据个人见解，我觉得主要包括一下几个方面： 因为分析器解析的时候要把所有的字段解析了，而有些字段基本不用，所以就会减慢查询速度 没用的字段会增加网络开销，尤其是数据比较多的字段（text。varchar等，因为长度超过一定字节后，会把超出的数据序列化到另一个地方，所以读取的时候会增加一次io操作。） 而且如果有索引的话，select * 就不能进行覆盖索引了，每次都要进行回表查询。 ...
SQL优化总结
06-26
个人开发过程中SQL优化经验总结,以及部分网上搜索的资料.
SQL优化总结.docx
09-03
SQL优化是数据库性能提升的关键环节，它涉及到查询速度、资源消耗和系统稳定性等多个方面。以下是一些关于SQL优化的关键知识点： 1. **索引优化**： - 索引的合理使用至关重要，应确保主要的查询条件字段都有索引...
sql 优化个人总结
08-29
sql优化总结，对sql 优化在实战中对于sql优化的浅显理解，大神勿喷。
SQL语句优化总结
04-12
SQL语句优化总结，很实用，讲解详细，适用于新手，用于平时工作中
SQL 优化经验总结34条
09-11
SQL优化是数据库管理中至关...以上就是对SQL优化经验的总结，这些技巧能够帮助你编写出性能更好的SQL语句，提高数据库的运行效率。在实际应用中，应结合具体数据库系统的特点和实际业务需求，灵活运用这些优化策略。
mysql查询慢Sending data耗时问题
orangeTop的博客
06-15 8317
背景：刚接手一个完结项目，要性能测试，100w条数据查询时间不超过10s，这个功能的查询是多表关联，现在才几千条数据但是在数据库执行sql的时候发现耗时几十秒，肯定满足不了要求，然后我又是小白，不懂就百度各种大神的经验吧，然后有了一下操作。。。。一、在navicat中执行了sql 查看下耗时都在 Sending data 中，然后就去看各种大神 怎么去解决，然后就加索引吧，第一次使用索引，就百度了...
SQL优化
简书的专栏
10-25 2127
文章目录一、Linux下RPM版MYSQL安装、启停1.1 环境1.2 MySQL版本1.3 MySQL安装二、MySQL启动问题、配置文件、编码问题三、MySQL分层、存储引擎3.1 MySQL逻辑分层3.2 存储引擎四、SQL解析过程、索引、B树4.1 SQL需要优化的原因4.2 SQL的编写与解析过程4.3 索引的利弊五、B树与索引5.1 B树5.2 索引六、SQL优化准备6.1 SQL性能...
SQL优化的几点建议
明明如月的技术博客
05-31 2252
一、背景 现在MySQL已经成为Java Web开发的标配。 面试也经常问到“MySQL索引的数据结构是什么？”、“MySQL慢查询怎么看？”、“聚簇索引和非聚簇索引的区别是什么？”，“怎么SQL优化”等等。 本文就“怎么SQL优化”，简单讲几个原则。 二、几条原则 对经常搜索、排序、分组列建索引 不重复的值，基数越大，效果越好 索引的数据类型尽可能的短 最左前缀原则 不要建立过...
SQL优化 编写SQL注意事项 书写高质量SQL的30条建议（持续更新）
知世故而不世故
04-19 1012
文章目录列表1、查询 SQL 尽量不要使用 select *，而是 select 具体字段2、如果知道查询结果只有一条，或者只要最大/最小一条记录，建议用 limit 13、应尽量避免在 where 子句中使用 or 来连接条件4、日常做分页需求时，一般会用 limit 实现。但是当偏移量特别大的时候，查询效率就变低。5、优化 like 语句 列表 本文将结合实例 Demo，阐述30条有关于优化 SQL 的建议。多数是实际开发中总结出来的，希望对大家有帮助。 提示：以下是本篇文章正文内容，下面案例可供参
mysql不要使用select_到底为什么不要用SELECT *
weixin_39631316的博客
02-01 1335
无论工作还是面试，说到sql优化，比说的一个问题就是，代码中sql不要出现 SELECT *,之前一直也没有深入去研究研究，为什么，只是记住了，代码中注意了，但是就在今天逛某某论坛时，又看到有同学在发布这样的经验分享，读完，有感觉模模糊糊，懵懵懂懂。遂下定决心，整理一篇，为什么不要使用**SELECT * **，直接进入正。一、为什么不要使用_SELECT *_首先我们参考一下《阿里java开发...
4.sql优化
黑某人
06-16 293
Sql优化总结 Sql执行顺序： ​ **（8）**SELECT （9） DISTINCT column,… (选择字段、去重) **（6）**AGG_FUNC(column or expression),… 聚合函数 **（1）**FROM [left_table] 选择表 （3）<join_type> JOIN <right_table> 连接 **（2）**ON <join_condition> 连接条件 **（4）**WHERE <where_condit
ORACLESQL性能优化.ppt
最新发布
11-21
2. SQL优化机制：包括共享SQL区域、SQL语句处理的阶段（如解析、执行和提取）、共享游标以及遵循良好的SQL编码标准。 二、Oracle的优化器 Oracle的优化器是决定如何执行SQL语句的关键组件，它可以基于成本或规则...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值