MySQL优化

最新推荐文章于 2024-07-16 23:16:44 发布

zx_1305769448

最新推荐文章于 2024-07-16 23:16:44 发布

阅读量160

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/zx_1305769448/article/details/120665224

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

MySQL的SQL优化

为什么进行SQL优化？

SQL语句执行耗时较长
索引失效

SQL编写过程

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

SQL执行过程

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

SQL的编写过程和执行过程之所以有区别，是因为MySQL中有专门负责优化SELECT语句的优化器模块。

如何进行SQL优化？

最大化利用索引
尽可能避免全表扫描
减少无效数据的查询

总结：主要的就是优化索引。

何为索引？

MySQL官方对索引的定义：索引（index）是帮助MySQL高效获取数据的数据结构。

总结：索引是数据结构，该数据结构的目的是以空间获取时间。

索引的优点：

提高数据检索的效率，降低数据库的IO成本。
降低数据库排序的成本，降低了CPU的消耗。

索引的缺点：

索引本身也是数据，需要消耗物理空间存储，存在内存或硬盘
索引的建立需要考虑是否符合条件
- 数据量很小则不适合建立
- 更新频繁的字段则不适合建立
- 很少使用的字段怎不适合建立
- 某个字段包含重复的数据则不适合建立
索引虽然提高了查询的效率，但是会降低增删改的效率。

索引的分类

单值索引：只包含一个列
唯一索引：索引列在表里是唯一的，允许有空值
复合索引：包含多个列
主键索引：是唯一索引的一种，但是不允许空值

索引的操作

创建索引

-- 方式一：
create [unique] index index_name on  tab_name(column_name(length),..)；

-- 方式二：
alter table tab_name add [unique] index index_name(column_name(length),...)；

删除索引

-- 方式一：
drop index index_name on tab_name;

-- 方式二：
alter table tab_name drop index index_name;

修改索引

-- 添加主键
alter table tab_name add primary key (column_name,...);

-- 添加唯一索引
alter table tab_name add unique index index_name (column_name,...);

-- 添加普通索引
alter table tab_name add index index_name (column_name,...);

-- 添加全文索引
alter table tab_name add fulltext index_name(column_name,...);

查看索引

show index from tab_name;

索引建立的原则

不要建立太多索引，因为维护索引需要消耗空间和性能。
频繁增删改的字段不要建立索引，字段频发修改，索引也要修改，会影响MySQL性能
为频发查询的字段建立索引，建立的索引要为经常作为查询条件的字段建立索引，可提高查询效率
避免为“大字段‘建立索引。就是尽量使用字段长度小的字段作为索引；例如，要为varchar(5)和varchar(200)的字段建立索引，则优先考虑为varchar(5)的建立；如果非要为varchar(200)的字段建立索引那么可以这样：
```
-- 为varchar(200)的字段建立索引，将其长度设置为 20 
create index index_name on tab_name(column_name(20))
```
选择数据分区大的列建立索引，如果某个字段包含许多重复的数据则不适合建立索引。例如：性别字段。如果值出现的几率几乎相等，无论如何搜索都会查询到一半的数据，在这种情况下，不要建立索引。因为MySQL他还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

惯用百分比界线30%，匹配的数据量超过一定限制的时候，查询优化器就会放弃索引，导致索引失效。

执行计划

explain 可以模拟优化器执行SQL语句，从而知道MySQL如何执行SQL语句。

explain SQL：select语句，delete语句，insert语句，update语句。

 +----+-------------+----------+------+---------------+------+---------+------+------+---
 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 +----+-------------+----------+------+---------------+------+---------+------+------+---

id

id是select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下；
id不同，id值越大优先级越高，优先被执行；

表的执行顺序与表中数据息息相关，举个例子：表A：3条数据表B：4条数据表C：6条数据。
假设在执行某条SQL的情况下表A、B、C的id值相同，假设执行顺序：表A、B、C。在给表B添加4条数据后，再次执行前面的这条SQL发现执行顺序变为：表A、C、B。

这是因为中间结果会影响表的执行顺序：
3 * 4 * 2 = 12 * 2 = 24
3 * 2 * 4 = 6 * 4 = 24
虽然最后结果一样，但是中间过程不一样。中间过程越小占用的空间越小，所以在ID值相同的情况下数据小的表优先查询。

select_type

select_type查询的类型，主要用于区分普通查询，联合查询，子查询等的复杂查询。

值包含：

PRIMARY：包含子查询的主查询，最外层部分；
SUBQUERY：查询语句中的子查询，非最外层；
SIMPLE：简单查询，即SQL中不包含子查询或者UNION查询
DERIVED：查询过程中创建的子表
UNOIN：若第二个SELECT语句出现在UNION之后，则被标记为UNION，若UNION包含在FROM子查询中外层SELECT将被标记为DERIVED
UNION ERSULT：UNION表的结果

table

table是查询的表

type

type是索引的类型，比较重要

结果值从最好到最坏

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

system 和 const 是理想情况，ref 和 range是实际

system：表示表只有一行记录，或衍生表只有一行数据的主查询。
const：仅仅只能查询到一条记录的SQL，并且用于主键索引或者唯一键索引
eq_ref：唯一索引：对于每一个索引键的查询，返回匹配唯一一行的数据，有且只有一个，不能多于也不能为0；常见主键或唯一索引扫描。
ref：非唯一索引，对于每个索引键的查询，返回多个匹配的所有行包括0行
range：检索指定范围的行，where右面一个范围的查询，between and，in，>，>=等
index：查询全部索引中的数据
ALL：全表扫描数据

possible_keys | key

possible_keys是可能用到的索引，是一种预测

key是实际用到的索引

如果为null则没有用到索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xNnV35tX-1633744831109)(/Users/zhaoxianwang/Library/Application Support/typora-user-images/image-20211008153252888.png)]

key_len

key_len索引使用的长度，通过索引的长度，来判断复合索引到底有没有使用。

ref

ref指明当前表索引用的字段

rows

rows表示查询的行数，实际通过索引查询到的个数

Extra

Extra包含不适合在其他列中显示但是又十分重要的额外信息。

常见值：

Using filesort：文件内排序；出现这个值表示额外的进行一次排序，性能小航比较大，需要进行SQL优化。常见order by语句。
- ==对于单值索引，如果排序和查询的是同一个字段，则不会出现Using filesort，反之，则出现。==一般通过where哪些字段就order by哪些字段来避免。
- 对于复合索引，按照建立复合索引的顺序来使用，不要跨列或则无序使用。
Using temporary：使用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。性能消耗很大，需要SQL优化，常见于group by语句
- 出现的原因，已经有表了但是不使用，必须再来一张表。可以通过查询哪些列，就通过哪些列来分组来避免。
Using index：代表性能还不错；表示使用到了覆盖索引，不读取源文件，只从索引文件中获取数据，不需要回表查询。
覆盖索引（Covering Index）,一说为索引覆盖: 查询的数据列从索引文件中就能够取得，不必读取原数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询到的列全部都在索引列中就是索引覆盖。
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

如果用到了索引覆盖时，会对possible_key和key造成影响：
- 如果没有使用到where，则索引只出现在key中；
- 如果有where，则索引出现在key和possible_key中；
Using where：表示需要回表查询。
Impossible WHERE：where语句永远为FALSE，永远不成立。

zx_1305769448

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL优化

MySQL的SQL优化为什么进行SQL优化？SQL语句执行耗时较长索引失效SQL编写过程select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...SQL执行过程from ...on ... join ...where ... group by ... having ...select distinct ...or
复制链接

扫一扫