mysql学习笔记-索引优化【2】-索引实际案例分析

最新推荐文章于 2024-07-12 16:56:26 发布
最新推荐文章于 2024-07-12 16:56:26 发布
阅读量102 收藏
点赞数
分类专栏： 学习笔记 mysql 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39236708/article/details/106224396
版权

测试数据

CREATE TABLE test03 (
	id INT PRIMARY KEY NOT NULL auto_increment,
	c1 CHAR (10),
	c2 CHAR (10),
	c3 CHAR (10),
	C4 CHAR (10),
	c5 CHAR (10)
);
insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1 ,c2,c3,c4, c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
# 建立索引
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
  1. 问题：我们创建了复合索引idx_ test03_ _c1234 ，根据以下SQL分析下索引使用情况?
# 第一次测试
explain select * from test03 where c1="a1";
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |      100 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+

# 第二次测试
explain select * from test03 where c1="a1" and c2 ="a2";
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |      100 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+

# 第三次测试
explain select * from test03 where c1="a1" and c2 ="a2" and c3 ="a3"
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 93      | const,const,const |    1 |      100 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+

# 第四次测试
explain select * from test03 where c1="a1" and c2 ="a2" and c3 ="a3" and c4 ="a4";
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 |      100 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+

# 第五次次测试
explain select * from test03 where c1="a1" and c2 ="a2" and c4 ="a4" and c3 ="a3";
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 |      100 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+

# 第六次测试
explain select * from test03 where c4 ="a4" and  c3 ="a3" and c2 ="a2" and  c1="a1" ;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 |      100 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
# 总结
因为mysql内部的sql（查询）优化器

# 第七次测试
explain select * from test03 where  c1="a1" and c2 ="a2" and c3 > "a3" and c4 ="a4";
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 |       20 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+

# 第八次
 explain select * from test03 where  c1="a1" and c2 ="a2" and c4 > "a4" and c3 = "a3" ;
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 124     | NULL |    1 |      100 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+

# 第九次测试
select * from test03 where  c1="a1" and c2 ="a2" and c4 = "a4" ORDER BY c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |       20 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
c3 作用在排序而不是在查找

# 第十次测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al'AND c2 = 'a2'ORDER BY	c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |      100 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
# 第11次测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al'AND c2 = 'a2'ORDER BY	c4;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |      100 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
# 出现了 filesort

# 第12次测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al'AND c5 = 'a5' ORDER BY	c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |       20 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
# 只用c1一个字段索引，但是c2、c3用于排序，无filesort

# 第13次测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al'AND c5 = 'a5' ORDER BY	c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |       20 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
# 出现filesort

# 第14次测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al'AND c2 = 'a2' ORDER BY	c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |      100 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
# 第15次测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al' AND c2 ='a2' AND c5 = 'a5' ORDER BY	c2,c3;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |       20 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
# 用C1、c2两个字段索引，但是c2、c3用于排序无filesort
第16次测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al' AND c2 ='a2' AND c5 = 'a5' ORDER BY	c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |       20 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
# 本例有常量c2的情况，和测试17相比
无filesort
第17 次测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al' AND c5 = 'a5' ORDER BY	c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |       20 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
# 产生filesort
第18次测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al' AND c4 = 'a4' GROUP By 
c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |       20 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
第19测试
explain SELECT	* FROM	test03 WHERE	c1 = 'al' AND c4 = 'a4' GROUP BY c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                                  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |       20 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+

总结

定值、范围还是排序，般order by是给个范围

groupby基本上都需要进行排序，会有临时表产生

建议

  • 1.对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 2.在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 3.在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
  • 4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

[优化总结口诀]

  1. 全值匹配我最爱，最左前缀要遵守;
  2. 带头大哥不能死，中间兄弟不能断;
  3. 索引列上少计算，范围之后全失效;
  4. LIKE百分写最右，覆盖素引不写星;
  5. 不等空值还有or，索引失效要少用;
  6. VAR引号不可丢，SQL高级也不难!
weixin_39236708
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql索引优化实际例子_MySQL索引优化的实际案例分析
weixin_39800387的博客
01-19 85
Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景，其优化原理也非常的简单，就是利用索引的有序性，优化器沿着索引的顺序扫描，在扫描到符合条件的M行数据后，停止扫描；看起来非常的简单，但是我经常看到很多性能较差的sql没有利用这个优化规律，下面将结合一些实际的案例来分析说明：案例一：一条sql执行非常的慢，执行时间为：root@test 02:00:4...
MySQL高级——索引优化案例7
小志的博客
03-09 320
目录一、建表语句二、案例三、案例结论四、like ‘%字符串%’ 时索引不被使用的解决方式五、like ‘%字符串%’ 时索引不被使用的结论 一、建表语句 1、员工表的建表语句 CREATE TABLE emps( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名', age INT NOT NULL DEFAULT 0 COMMENT '年龄', pos VARCHAR(20) NOT N
一看就会的mysql索引优化（真实案例）
weixin_40413961的博客
08-25 1523
背景 (使用的数据库：MYSQL 5.7 版本，InnoDB 引擎) 自从服务加了Skywalking后，将大部分慢接口暴露出来。于是就有了这次慢接口的优化。大概的优化过程，没有详细的具体过程 优化前： 优化后： 优化步骤 1. 排查 通过skywalking可以清楚的看到慢接口是在哪一步比较慢。通过调用情况可以清楚的看到其链路调用情况如下图： 如果说你的服务没有接如这种情况监控服务，那我们可以使用阿里巴巴开源的Arthas来进行链路追踪（使用trace进行查看每一步方法的调用耗时）a
索引优化的案例
我就是我的博客
03-01 250
文章目录索引优化单表优化过程多表优化过程总结 索引优化 单表优化过程 ## 建表语句 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) unsigne...
【MySQL优化】实际案例MySQL优化
咔咔博客
04-03 723
索引优化一直在MySQL中占着很重要的位置，但是刚刚入门的还没有接触到这块的知识。已经工作很久也不是很注重这块，只求功能不求性能。当然咔咔也包含在这其中。 这篇文章不会过深的探讨索引原理，只是针对于实际案例给予优化方案。咔咔在网上也搜索了很多的关于MySQL优化方面的文章，但是大多都是以原理为主。个别的排版还很难辨认， 于是在2020/4/2号的22:51咔咔开始撸这篇文章 大战SQL...
MYSQL学习笔记-索引[文].pdf
10-11
MYSQL学习笔记-索引 MYSQL学习笔记-索引是一篇关于MYSQL数据库索引的详细笔记，涵盖了索引的基本概念、类型、创建方法以及使用场景。下面是笔记中的主要知识点： 一、索引的基本概念 * 在数据库表中，使用索引...
MYSQL学习笔记-索引参照.pdf
12-08
MYSQL学习笔记-索引参照.pdf
MYSQL学习笔记-索引
08-28
MYSQL学习笔记-索引MYSQL学习笔记-索引MYSQL学习笔记-索引
MySQL学习笔记：索引优化
01-14
MindManager脑图，纯自己写的一些感想，如有不当，请各位老师多多指点！真心感谢！
MySQL学习笔记(2)——索引
12-14
总的来说，理解并合理使用索引是优化MySQL查询性能的关键，需要根据实际情况权衡索引带来的增益和维护成本。在设计数据库时，应考虑查询模式，有针对性地创建和使用索引，同时注意避免上述可能导致索引失效的操作。...
初始C++
2302_81016149的博客
07-08 799
在C语言我们学习函数的时候，我们学习过生命周期这个概念。那么，什么是生命周期呢？生命周期通常指的是一个对象或变量从创建到销毁所经历的时间段。那么，命名空间是什么？咱们可以这样理解：在这个空间的生命周期内，对这个空间内的全部标识符起一个名字，可对其进行引用，可类似与C语言中的结构体。#include<stdlib.h>//此处不包此头文件代码可正常运行，那该如何解决此处情况呢？int main()//此处编译不通过原因为：在不包头文件前为变量，包含后为函数。return 0;
set user & password for database 设置数据库 用户和密码
DavidsonGong的博客
07-12 93
USE sbms;
mysql笔记1
m0_62975692的博客
07-11 233
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！
Redis数据结构-String篇
时间如瑾 代码如诗
07-12 421
对⼀个内部表示成long型的string执行append, setbit, getrange这些命令，针对的仍然是string的值（即⼗进制表示的字符串），而不是针对内部表⽰的long型进⾏操作。因此，在这些命令的实现中，会把long型先转成字符串再进行相应的操作。String 的内部存储结构⼀般是 sds（Simple Dynamic String，可以动态扩展内存），但是如果⼀个String 类型的 value 的值是数字，那么 Redis 内部会把它转成 long 类型来存储，从⽽减少内存的使用。
docker-compose安装PolarDB-PG数据库
wnfff的博客
07-12 190
docker-compose安装PolarDB-PG数据库
【MySQL】8.复合查询
最新发布
weixin_74113106的博客
07-12 430
MySQL复合查询
系统架构设计师教程 第二章 计算机系统基础知识-2.3计算机软件
Remon的专栏
07-09 717
《系统架构设计师教程》清华第二版 2.3计算机软件 章节的详细解读
Java：使用synchronized和Redis实现并发控制的区别
好幸运
07-08 456
而Redis则适用于分布式环境下的同步需求，虽然操作相对复杂，但它能够解决跨JVM的资源竞争问题。: 是Java语言内置的关键字，用于实现线程间的同步。这种同步是** JVM层面**的，对本地内存中的对象进行操作。Redis: 由于是基于网络通信的分布式存储，它的性能受到网络延迟和带宽的影响。然而，在分布式环境下，Redis提供的分布式锁机制能够有效解决跨JVM的同步问题，尽管相比。Redis: 使用Redis实现同步（如通过SETNX命令设置锁），需要手动编写代码来获取和释放锁。
配置Redis时yml的格式导致报错
weixin_63680330的博客
07-12 539
这个错误是由于在创建名为'redissonClient'的bean时出现了问题。具体原因是Redis连接异常，无法解析主机名'null'。请检查您的Redis配置，确保主机名和端口号正确。但是我明明已经配置了，一番排查后发现是缩进问题。
MySQL学习笔记
04-09
MySQL学习笔记是对MySQL学习过程中的重要知识点进行记录和总结的文件。在学习MySQL的过程中，首先...通过学习MySQL学习笔记，可以掌握MySQL的基础知识和进阶的优化管理操作，为以后的MySQL开发和管理打下良好的基础。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值