SQL优化之索引优化

最新推荐文章于 2023-11-17 17:30:13 发布
最新推荐文章于 2023-11-17 17:30:13 发布
阅读量2.6k 收藏 16
点赞数 6
分类专栏： MySQL数据库 文章标签： 索引 mysql优化 mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40520912/article/details/118106017
版权

索引优化

1、 分析SQL执行的性能

​ 使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。分析所编写的查询语句或是表结构的性能瓶颈。

具体语法：

EXplain DQL语句；

执行的结果一共包括以下几个字段：

idselect_typetablepartitionstypepossible_keyskeykey_lenrefroesfilteredextra
字段描述
id查询的序列号，是一组数字，用来表示查询中执行select子句或者是操作表的顺序。
select_type表示查询的类型，一般有SIMPLE(简单查询)、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）
table查询所用的表
partitions显示的为分区表命中的分区情况，非分区表该字段为空（null）。5.7以后就是默认项
type连接的类型
possible_keys可能使用的索引
key实际使用的索引
key_len索引字段的长度
rows扫描行的数量
extra执行情况的说明和描述

type连接类型性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref-------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------>all )

EXTRA属性：

using index ：使用覆盖索引时会出现。
 
using where：在查找使用索引的情况下，需要回表去查找所需的数据。
 
using index condition：查找使用了索引，但是需要回表查找数据。
 
using index ; using where：查找使用了索引，且需要的数据都在索引列中，不需要回表查询数据。

2、 防止索引失效

2-1 全值索引

对索引中所有的列都指明值，是做好用的一种避免索引失效的方法。

案例：查询1班身份证号为123456的学生的信息。

1.建立索引：
mysql> create index careId_class_index on admin(careId,class);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

2.查看索引：
mysql> show index from admin;
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| admin |          0 | PRIMARY            |            1 | id          | A         |           5 | NULL     | NULL   |      | BTREE      |         |               |
| admin |          1 | careId_class_index |            1 | careId      | A         |           5 | NULL     | NULL   |      | BTREE      |         |               |
| admin |          1 | careId_class_index |            2 | class       | A         |           5 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

3.使用索引
   3.1 不采用全值索引
   mysql> explain select * from admin  where careId = '123456';
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | admin | NULL       | ref  | careId_class_index | careId_class_index | 22      | const |    1 |      100 | NULL  |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set
     3.2 采用全值索引
     mysql> explain select * from admin  where class = '1'  and careId = '123456';
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys      | key                | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | admin | NULL       | ref  | careId_class_index | careId_class_index | 44      | const,const |    1 |      100 | NULL  |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+

在这里插入图片描述

2-2 最左前缀法则

​ 定义： 如果在索引中包含了多个字段，即采用复合索引时，通常需要遵循最左前缀原则，即查询从索引的最左前列开始，并且不跳过索引中的列。

案例：

​ 1 .索引不是失效

1.创建索引
mysql> create index careId_class_index on admin(careId,class);
2.使用索引
mysql> explain select * from admin  where careId = '123456';


mysql> explain select * from admin  where careId = '123456' and class = '1';

在这里插入图片描述

​ 2.索引失效：没有遵循最左前缀原则，跳过第一个索引的字段。

1.创建索引
mysql> create index careId_class_index on admin(careId,class);
2.使用索引
mysql> explain select * from admin  where class = '1';

在这里插入图片描述

3.如果出现遵循了最左前缀原则，但是后面跳跃了一个字段，则跳跃后的字段索引失效，此时只有最左端的字段的索引起作用。

2-3 比较后失效

​ 在使用范围查找时，后面的索引失效，简言之就是使用比较运算符后的查找条件的索引失效。

案例：

1.创建索引
mysql> create index index_careId_class_age on admin(careId,class,age);
2.使用索引

在这里插入图片描述

可知：前面两个字段careId、class使用了索引，但是age没有使用索引，即比较后索引失效。

2-4 索引列运算失效

​ 在索引包含的列上进行各种运算，会导致进行全表扫描，此时索引失效。

案例：

1.创建索引
mysql> create index name_index on admin(name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
2.使用索引
mysql> explain select * from admin where substring(name,2,1)='o';
结果：此时进行全表扫描

2-5 字符创不加单引号

一般情况下如果一个字符创不加单引号，此时索引会失效，因为对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

案例：

1.加单引号，没有失效

mysql> explain select * from admin where careId = '123456' and class = '1';

在这里插入图片描述

2. 没加单引号，此时没加单引号的索引失效，
mysql> explain select * from admin where careId = '123456' and class = 1;

在这里插入图片描述

2-6 尽量不使用全部查询

即尽量别使用像select * 这样的语句，多使用覆盖查询，也就是索引的字段。如果查询的列多出了建立索引时所用的列，此时也会影响查询的效率。

案例：

1.select *
mysql> explain select * from admin where careId = '123456' and class = '1' and age =18;

在这里插入图片描述

2.超过索引的范围
mysql> explain select name,age from admin where careId = '123456' and class = '1';

在这里插入图片描述

2-7 在条件查询中使用或者（or）关键字时，索引失效。

在使用or进行条件筛选时会使索引失效。

mysql> explain select * from admin where careId = '123456' or class = '1';

在这里插入图片描述

此时可以使用合并查询union来代替

mysql> explain select * from admin where careId = '123456' union all select * from admin where class = '1';

在这里插入图片描述

2-8 在模糊查询like中，分情况

​ 通配符%在前失效，但是在尾部时索引不会失效，即前失后保。

1.通配符在最前端。

mysql> explain select * from admin where name like'%o';

在这里插入图片描述

2.通配符在后端。

mysql> explain select * from admin where name like'j%';

在这里插入图片描述

2-9 判空不一定

判空主要有两个操作运算符：is null 与 is not null，使用判空运算符时主要看数据表在设计时是否可以为空，从而来判断索引是否失效。

2-10 不等号不一定

在使用不等于运算符（!= <>）时可能索引会失效。

mysql> explain select * from admin where careId <> '123456';

在这里插入图片描述

mysql> explain select * from admin where admin.careId = '123456';

在这里插入图片描述

2-11 多使用复合索引

当多个字段需要建立索引时此时就可以使用复合索引，即建立一个索引相当于建立了好几个索引。

创建复合索引：
mysql> create index deptno_sal_index on emp(deptno,sal);
   
   相当于建立了两个索引：
                   deptno
                   deptno + sal
                   
创建单列索引：
mysql> create index deptno_index on emp(deptno);
mysql> create index sal_index on emp(sal);

在使用索引时，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。

--------------------------------------END------------------------------------

数据库索引基础：----------------------------------------------------《加载完成》------------------------------------------------------------------------------
欢迎关注，谢谢

悸%动
关注
  • 6
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL优化之索引优化案例
shuyoujing的博客
07-25 529
前言 本次优化会从如下几个维度去考虑 单表优化、两表优化、三表优化 单表优化数据准备 create table book ( bid int(4) primary key, name varchar(20) not null, authorid int(4) not null, publicid int(4) not null, typeid int(4) not null ); insert into book values(1,'tjava',1,1,2) ; insert into b
mysql优化之索引优化
架构路上的博客
11-12 218
mysql优化之索引优化
MySQL explain执行计划解读
热门推荐
上善若水，水善利万物而不争。
03-02 7万+
本文我们主要介绍了MySQL性能分析以及explain的使用，包括：组合索引、慢查询分析、MYISAM和INNODB的锁定、MYSQL的事务配置项等，希望能够对您有所帮助。 1.使用explain语句去查看分析结果 如explain select * from test1 where id=1;会出现：id selecttype table type possible_keys key ke
MySQL 5.7 优化：Explain 执行计划近万字详解
一只奋斗的猪
01-04 5390
目录1. 介绍2. Explain 结果列详解2.1 id2.2 select_type2.3 table2.4 partitions2.5 type（非常重要）2.6 possible_keys2.7 key2.8 key_len2.9 ref3.10 rows2.11 filtered2.12 Extra 【注】 当前系统环境： MySQL 5.7，其他版本略有不同，后期会抽时间单独说明。 只介绍常见的场景，其他少见的场景暂不研究。 文档参考： MySQL 官方 Explain 文档 1. 介绍
evaluate函数使用无效_MySQL · 源码分析 · 聚合函数（Aggregate Function）的实现过程...
weixin_39637723的博客
10-26 433
总览聚合函数（Aggregate Function）顾名思义，就是将一组数据进行统一计算，常常用于分析型数据库中，当然在应用中是非常重要不可或缺的函数计算方式。比如我们常见的COUNT/AVG/SUM/MIN/MAX等等。本文主要分析下该类函数实现的一些框架，不涉及到每个函数的详尽分析。聚合函数（Aggregate Function）实现的大部分代码在item_sum.h和http://item_...
SQL的索引以及优化
wszhm123的博客
04-03 3078
我们不管在写代码，或者对执行数据库操作的时候，SQL优化是不可缺少的一环。所以这个功能至关重要。下面我们来说说SQL语句优化： 定位慢查询 show status like 'connections' ------------------------当有多少客户端连接数据库 show status like 'slow_queries'----------------------查询有多少......
SQL优化——order by优化、Using filesort和Using index讲解
weixin_44860226的博客
07-22 289
如果在大数据量排序的时候出现了filesort 可以将这个参数的大小往上调。增加缓冲区的空间，避免占用磁盘 提升sql效率。创建的索引当中默认是升序排序所以，虽然使用了索引但是如果是倒序排序的话还是会出现using filesort的情况。出现using idex说明了排序效率比较高。因为它直接通过索引给我们返回了有序数据。如果索引排序违背了最左前缀法则，也会出现using filesort。什么是using filesort、 using idex？如果想优化掉这个using filesort。
SQL语法之索引
tenger的专栏
07-22 608
注：MSSQLServer2000按照索引的结构，可以将其划分为两大类：聚簇索引（Clustered index）和非聚簇索引（Noclustered index）。 按照表中建立索引的那一列（或列组合）中的数据是否各不相同，可以将索引分为唯一索引和非唯一索引。 建立索引的条件如下：(1)只有表的拥有者才能建立索引(2)每个表只能建立一个聚簇索引(3)每个表最多可以建立249个非聚
索引与sql的优化
m0_71467744的博客
08-25 1526
（2）主键顺序插入：因为innodb类型的表是按照主键的顺序存储的，所以导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率，如果innoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用主键顺序插入以提高导入数据的效率；尽量减少额外排序，通过索引直接返回数据，where条件和order by 使用相同的索引，并且order by的顺序和索引的顺序相同，且order by的字段要么都是升序，要么都是降序。如果没有索引，则应该考虑增加索引；...
SQLAdvisor美团SQL索引优化建议工具
12-14
　SQLAdvisor是美团开源的一款SQL索引优化建议工具， 是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、...
SqlServer 索引自动优化工具
12-15
该工具的核心理念是根据实际的查询执行情况来重建和优化索引，以确保它们对查询和更新操作的效能最大化。 首先，该工具会在热备数据库服务器上定期抓取执行计划，这通常通过监控SQL Server的缓存来实现。由于执行...
千万级数据sql优化_索引
10-31
千万级数据sql优化_索引，大概从12个角度层次阐述哪些可以通过索引进行优化，哪些关键词会影响到索引的使用
SQL优化-索引
01-27
### SQL优化之索引详解 #### 一、SQL概述及分类 SQL，即Structured Query Language，是一种用于管理和处理关系型数据库的标准语言。SQL主要由以下几个部分组成： 1. **数据定义语言（Data Definition Language, ...
SqlServer性能优化高效索引指南.pdf
05-05
提高索引的存储效率可以通过选择合适的索引类型、优化索引的结构、减少二次查找等方法来实现。减少维护的开销可以通过合适的索引数量、减少索引的更新频率、使用压缩技术等方法来实现。 索引的类型包括聚集索引和非...
【mysql系列】mysql group by 执行原理及千万级别count 查询优化
最新发布
我活在当下，我巨他妈勇敢。
11-17 368
大家好，我是蓝胖子,前段时间mysql经常碰到慢查询报警，我们线上的慢sql阈值是1s，出现报警的表数据有 7000多万，经常出现报警的是一个group by的count查询，于是便开始着手优化这块，遂有此篇，记录下自己优化过程中的心得。优化慢sql前，肯定是要懂sql的查询逻辑，所以我先介绍下group by 语句的执行逻辑。
sql索引优化实战总结
weixin_43811057的博客
05-31 1371
一、 避免索引失效 -- 最左匹配原则 * -- 范围条件右边的索引失效 -- 不再索引列上做任何操作 * -- 使用不等于(!=或者<>)索引失效 -- is not null无法使用索引 -- like以通配符开头(%qw)索引失效 * -- 字符串不加引号索引失效 -- 使用or连接索引失效 -- 尽量使用覆盖索引 1.1 全值匹配 -- 创建组合索引 create index idx_name_dep_age on employee(name,dep_id,age) -- 索引字
MySQL高级（SQL性能分析，SQL优化）
dyk
02-28 6832
SQL性能分析,SQL优化
mysql aggregate是什么类型_Aggregate类型以及值初始化
weixin_39874196的博客
01-20 241
引言在C++中，POD是一个极其重要的概念。要理解POD类型，我们首先需要理解Aggregate类型。下文结合stackoverflow上的高票回答将对Aggregate类型做一个全面的解读。对于Aggragates的定义C++标准(C++ 03 8.5.1 §1)中的正式定义如下： An aggregate is an array or a class (clause 9) with no us...
sql 全解（索引、索引失效、sql优化）
demo_yo的博客
07-04 788
1. 小表驱动大表 2. 建索引，一张表不超过5个索引 3. 走索引，尽量满足最左匹配，避免索引失效
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值