MySQL索引优化解决方案--索引测试--explain(2)

已于 2024-06-26 13:52:08 修改
阅读量248 收藏
点赞数 3
分类专栏： # MySQL索引优化解决方案 文章标签： mysql 数据库 索引测试 explain type
于 2024-06-25 10:59:47 首次发布
本文为博主原创文章，未经博主允许不得转载。
本文链接：https://blog.csdn.net/my_study_everyday/article/details/139951545
版权

索引测试

  1. 通过存储过程往数据库中插入300w 条数据
  2. 分别测试使用索引和没有使用索引的情况下，where 查询的一个效率对比。
-- 创建表
DROP TABLE IF EXISTS person;
CREATE TABLE person(
PID int(11) auto_increment comment '编号',
PNAME varchar(50) comment '姓名',
PSEX varchar(10) comment '性别',
PAGE int(11) comment '年龄',
SAL decimal(7, 2) comment '工资',
PRIMARY KEY (PID)
);
# 创建存储过程
create procedure insert_person(in max_num int(10))
begin
	declare i int default 0
	set autocommit = 0;
	repeat
	set i = i + 1;
	insert into person (PID,PNAME,PSEX,PAGE,SAL) values (i, concat('test', floor(rand()*10000000)),IF(RAND()>0.5,'男','女'), FLOOR((RAND()*100)+10),FLOOR((RAND()*19000)+1000));
	until i = max_num
	end repeat;
	commit;
end;

-- 调用存储过程
call insert_person(3000000);


-- 不使用索引，根据PNAME进行查询
select * from person where PNAME = 'test1209325'; #10.813秒

-- 给PNAME建立普通索引
alter table person add index idx_pname(PNAME);
select * from person where PNAME = 'test1209325'; #5.032秒
-- 主键索引
select * from person where PID = 2800000; #0.021秒

explain 关键字

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。可以用来分析查询语句或是表的结构的性能瓶颈。其作用如下：

  1. 表的读取顺序
  2. 哪些索引可以使用
  3. 数据读取操作的操作类型
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

EXPLAIN 关键字使用起来比较简单： explain + SQL语句

准备测试数据：

-- 创建四张测试表
CREATE TABLE t1(
	id int(10) auto_increment,
	content varchar(100),
	primary key (id)
);

CREATE TABLE t2(
	id int(10) auto_increment,
	content varchar(100),
	primary key (id)
);


CREATE TABLE t3(
	id int(10) auto_increment,
	content varchar(100),
	primary key (id)
);


CREATE TABLE t4(
	id int(10) auto_increment,
	content varchar(100),
	primary key (id)
);

-- 每张表中添加一条数据
INSERT INTO t1(content) values (CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) values (CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) values (CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) values (CONCAT('t4_',FLOOR(1+RAND()*1000)));

explain之id介绍

select 查询的序列号，表示查询中执行select子句或操作表的顺序。

# id相同时，执行顺序由上至下。
explain select * from t1,t2,t3 where t1.id = t2.id and t2.id=t3.id; #得到表格1

# id不同，如果是子查询，id的序号会递增，id值越大优先级越高，则先被执行。
explain select t1.id from t1 where t1.id in (
	select t2.id from t2 where t2.id in (
		select t3.id from t3 where t3.id = 1
	)
) # 得到表格2


# id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越大，优先级越高越先执行。

explain select t2.* from t2,(select * from t3) s3 where s3.id = t2.id; #得到表3

表格1：

idselect_typetablepartitionstypepossible_keyskeyken_lenrefrowsfilteredExtra
1SIMPLEt1nullsystemPRIMARYnullnullnull1100null
1SIMPLEt2nullsystemPRIMARYnullnullnull1100null
1SIMPLEt3nullsystemPRIMARYnullnullnull1100null

表格2：

idselect_typetabletypepossible_keyskeyken_lenrefrowsExtra
1PRIMARYt1indexnullPRIMARYnullnull1
2DEPENDENTt2unique_subPRIMARYPRIMARY4func1Using index
3SIMPLEt3constPRIMARYPRIMARY4const1Using index

表格3：

idselect_typetabletypepossible_keyskeyken_lenrefrowsExtra
1PRIMARY<derived2>systemnullnullnullnull1
1PRIMARYt2constPRIMARYPRIMARY4const1
2DERIVEDt3ALLnullnullnullnull1

Explain之select_type介绍

查询的类型，常见值有：
SIMPLE：简单的select查询，查询中不包含子查询或者UNION
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
SUBQUERY：在select或where列表中包含了子查询

Explain之table介绍

显示这一行的数据是关于哪张表的。

Explain之type介绍

  • system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

  • const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

  • range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
    一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

  • index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的。

  • All：Full Table Scan，将遍历全表以找到匹配的行。

从最好到最差依次是:system>const>eq_ref>ref>range>index>All 。一般来说，最好保证查询能达到range级别，最好能达到ref。

Explain之prossible key和key介绍

prossible key: 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则改索引将会被列出来，但不一定会被查询实际使用上。

key: 查询中实际使用的索引，如果为NULL，则没有使用索引。

Explain之ref介绍

显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

Explain之rows介绍

rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。

Explain之extra介绍

一些常见的重要的额外信息：
Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”。
Using temporary：Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。
Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错。
Using where：表示使用了where过滤。

完整案列

-- 建表
DROP TABLE IF EXISTS person;
CREATE TABLE person  (
  PID int(11) AUTO_INCREMENT COMMENT '编号',
  PNAME varchar(50) COMMENT '姓名',
  PSEX varchar(10) COMMENT '性别',
	PAGE	int(11) COMMENT '年龄',
  SAL decimal(7, 2) COMMENT '工资',
  PRIMARY KEY (PID)
);
-- 创建存储过程
create procedure insert_person(in max_num int(10))
begin
	declare i int default 0;
	set autocommit = 0; 
	repeat
	set i = i +1;
	insert into person (PID,PNAME,PSEX,PAGE,SAL) values (i,concat('test',floor(rand()*10000000)),IF(RAND()>0.5,'男','女'),FLOOR((RAND()*100)+10),FLOOR((RAND()*19000)+1000));
	until i = max_num
	end repeat;
	commit;
end;

-- 调用存储过程
call insert_person(30000000);
 
-- 不使用索引，根据pName进行查询
select * from person where PNAME = "test1209325"; #10.813S

-- 给PNAME建立索引
alter table person add index idx_pname(PNAME);
select * from person where PNAME = "test1209325"; # 0.032S
select * from person where PID = 2800000; #0.021


--  创建四张测试表
CREATE TABLE t1(
	id INT(10) AUTO_INCREMENT,
	content  VARCHAR(100),  
	PRIMARY KEY (id)
);
CREATE TABLE t2(
	id INT(10) AUTO_INCREMENT,
	content  VARCHAR(100),
	PRIMARY KEY (id)
);
CREATE TABLE t3(
	id INT(10) AUTO_INCREMENT,
	content  VARCHAR(100),
	PRIMARY KEY (id)
	);
CREATE TABLE t4(
	id INT(10) AUTO_INCREMENT,
	content  VARCHAR(100),
	PRIMARY KEY (id)
);

-- 每张表中添加一条数据
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));

INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));

INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
	
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

#id相同时，执行顺序是从上往下
explain select * from t1,t2,t3 where t1.id=t2.id and t2.id = t3.id;

#id不同时
explain select t1.id from t1 where t1.id in
(select t2.id from t2 where t2.id in 
(select t3.id from t3 where t3.id = 1)
);

#id相同和id不同
explain select t2.* from t2,(select * from t3) s3 where s3.id = t2.id;

-- select_type
#simple 简单的select查询
explain select * from t1;

#derived  primary
explain select * from (select t1.content from t1) s1;

#subquery
explain select t2.* from t2 where t2.id = (select t3.id from t3);

-- tpye
#All
explain select * from t1 where t1.content = "abc";
#system
explain select * from (select t1.id from t1 where id = 1) t;
#const 索引一次就找到了
explain select * from t1 where id = 1;
#eq_ref
explain select t1.*,t2.* 
from t1
join t2
on t1.id = t2.id;
#ref非唯一索引扫描
alter table t1 add index idx_t1_content(content);
explain select * from t1 where t1.content = "abc";

#range
explain select * from t2 where t2.id >0;
#index
explain select id,content from t1;
-- possible keys   key
explain select * from t2 where t2.id is not null;

-- ref
explain select t2.*,t3.* from t2,t3 where t2.id = t3.id;

-- rows 
#删除person上的索引
drop index idx_pname on person;
alter table person add index idx_pname(PNAME);
explain select person.* from person where pname = "test3263609";


drop table if exists emps;
CREATE TABLE emps (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主键id",
  name VARCHAR (24) COMMENT '姓名',
  age INT COMMENT '年龄',
  job VARCHAR (20) COMMENT '职位'
);
 
INSERT INTO emps(name,age,job) VALUES('zhangsan',22,'manager');
INSERT INTO emps(name,age,job) VALUES('lisi',23,'clerk');
INSERT INTO emps(name,age,job) VALUES('wangwu',24,'salsman');
INSERT INTO emps(name,age,job) VALUES('赵六',23,'salsman');
-- extra
-- using filesort 排序时没有使用索引
explain select * from emps order by age;
-- using temporary 分组时没有使用索引
explain select count(*),job from emps group by job;
-- using index 
explain select id,name from emps;
-- using where
explain select * from emps where name = "张三";
胡西风_foxww
关注
  • 3
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 索引ref_MySQL索引优化-性能分析Explain（转）
weixin_29038345的博客
01-27 1859
1. explain是什么使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。2. explain能干吗可用于分析：表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询3.explain怎么分析执行计划包含的信息如下：各字段解释3.1 idselect查...
MySQL高级-索引优化（超详细）
SIESTA030的博客
02-14 6522
性能分析 MySQL Query Optimizer Mysql中由专门负责优化SELECT语句的优化器，主要功能就是通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的，但**不一定是DBA觉得最优的，这部分最耗时间**）。 当客户端向MySQL请求一条Query的时候，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MQO会对整条Query进行优化，处理掉一些常量表达式的预算，将其换算成常量值并对，Que
MySQL索引系列--索引的优化--ORDER BY
IT利刃出鞘的博客
02-08 5258
本文介绍MySQL的ORDER BY索引的优化方案。 MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MYSQL高级篇-----索引优化分析
沉淀、分享、成长，让自己和他人都能有所收获！
08-07 1万+
对于单键索引，尽量选择针对当前query过滤性更好的索引。在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。假设index(a, b, c)where语句索引是否被使用可以，使用到a可以，使用到a，b可以，使用到a，b，c。...............
Mysql索引优化解决方案.doc
03-07
"Mysql索引优化解决方案" MySQL索引是帮助MySQL高效获取数据的数据结构。MySQL在存储数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种引用（指向）表中的数据，这样我们就可以通过数据结构上实现...
MySQL-性能优化的-9-种知识.docx
12-17
MySQL性能优化的9种知识 ...MySQL性能优化需要考虑到多个方面，包括字段属性的选择、事务控制和锁定、索引的使用、外键的使用、锁定表和数据库设计等。只有通过合理的设计和优化，才能提高数据库的性能和可扩展性。
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
MySQL优化全攻略-相关数据库命令
09-11
MySQL优化是提升数据库性能的关键步骤，它涉及到一系列的技术和策略，包括但不限于查询优化、索引优化、存储引擎选择、数据库架构设计等。本篇主要关注MySQL的内置命令在优化中的应用，尤其是`SHOW`和`EXPLAIN`命令...
Mysql索引性能优化问题解决方案
01-19
mysql 创建的优化就是加索引,可是有时候会遇到加索引都没法达到想要的效果的情况, 加上了所以,却还是搜索的全数据,原因是sql EXPLAIN SELECT cs.sid, -- c.courseFrontTitle, -- c.imgBig, cs.studyStatus, ...
mysqladmin——MySQL Server管理程序（二）
u011565038的博客
06-22 1120
mysqladmin是一个命令行工具，用于执行简单的 MySQL 服务器管理任务，如检查服务器的状态、创建和删除数据库、重载权限等。
mysql wrnning Difficult to find free blocks in the buffer pool解决方法
前方的路在刚开始
06-28 117
原因：这种情况，多半出现在别人在非常大的写入，或者百万级的查询中。解决方式，centos7在线安装的mysql，我使用的是mysql8,。
Mysql之GROUP BY与PARTITION BY区别
m0_51014049的博客
06-22 397
PARTITION BY 是窗口函数的一部分，用于定义窗口（即数据分区），但是它不会改变结果集的行数。PARTITION BY 用于窗口函数内，将结果集划分为几个分区，计算聚合值或其他窗口函数。这个查询会返回每一行，并在每一行中增加一个列 totalAmount，它表示的是该 employee_id 内所有 amount 的总和。结果集：GROUP BY 改变结果集的行数，只返回每组一行。PARTITION BY 用于窗口函数，增加计算列，不改变结果集的结构。
mysql的information_schema浅析
liulanba的博客
06-24 648
information_schema 是 MySQL 中的一个虚拟数据库，它包含了关于 MySQL 服务器的所有元数据。
MySQL中ALTER LOGFILE GROUP 语句详解
u011565038的博客
06-25 658
在 MySQL 的 InnoDB 存储引擎中，ALTER LOGFILE GROUP语句用于修改重做日志组（redo log group）的配置。重做日志是 InnoDB 用来保证事务持久性的一个关键组件，它们用于在系统崩溃后恢复数据。
谈一下MySQL的两阶段提交机制
WLKQNYJY_SHT的博客
06-22 1707
为了保证事务的持久性和一致性，MySQL需要确保redo log和binlog的同步持久化。在事务提交时，这两个日志必须保持一致，以避免出现数据不一致的问题。MySQL通过“两阶段提交”的机制来实现这一点。
在linux系统中使用docker、mysql实例
m0_61604045的博客
06-23 809
linux
44 mysql batch insert 的实现
最新发布
970655147的专栏
06-28 174
执行 sql 如下 “insert into tz_test_02 (`field1`, `field2`) values ("你好0", "没意思0"), ("你好1", "没意思1"), ("你好2", "没意思2"), ("你好3", "没意思3"), ("你好4", "没意思4"), ("你好5", "没意思5"), ("你好6", "没意思6"), ("你好7", "没意思7"), ("你好8", "没意思8"), ("你好9", "没意思9");tz_test_02 的数据列表拷贝如下。
【高级篇】MySQL新特性探索：迈向数据库技术前沿（十八）
David的博客
06-28 458
从MySQL 8.x的发布可以看出，MySQL团队致力于保持数据库技术的领先，不仅满足当前的业务需求，也为未来的技术趋势做好准备。作为本书的最后一章，我们希望通过上述内容的探讨，不仅让读者对MySQL新特性有了深入理解，也启发大家思考如何在实际工作中有效利用这些特性，以及如何紧跟数据库技术的发展步伐，为自己的项目或企业带来更大的价值。随着云环境的日益普及和数据处理需求的多样化，MySQL作为数据存储的核心，其发展轨迹无疑值得我们持续关注与期待。提示配图。
mysql索引优化 explain
05-26
在MySQL中，优化查询的一个重要手段就是使用索引。EXPLAIN是用来分析查询语句的工具，可以通过分析EXPLAIN输出结果来进行索引优化。下面是一些常用的优化技巧： 1. 索引覆盖：通过使用覆盖索引，可以避免MySQL访问表格数据，从而提高查询速度。如下所示： ``` EXPLAIN SELECT id FROM table WHERE col1 = 'value'; ``` 在这个查询中，只需要访问索引就可以返回结果，不用访问表格数据。 2. 索引选择：使用合适的索引可以提高查询的效率。一般来说，选择最左前缀列的索引可以让MySQL选择更加有效的索引。如下所示： ``` EXPLAIN SELECT * FROM table WHERE col1 = 'value' AND col2 = 'value2'; ``` 在这个查询中，如果存在(col1, col2)的复合索引，则MySQL会选择它来执行查询，这样可以避免全表扫描。 3. 避免使用函数：在查询中，使用函数会导致MySQL无法使用索引，从而降低查询效率。如下所示： ``` EXPLAIN SELECT * FROM table WHERE YEAR(date_col) = '2021'; ``` 在这个查询中，使用YEAR函数会导致MySQL无法使用(date_col)的索引来执行查询，可以考虑将查询条件改为： ``` EXPLAIN SELECT * FROM table WHERE date_col >= '2021-01-01' AND date_col < '2022-01-01'; ``` 4. 避免使用OR：在查询中，使用OR会导致MySQL无法使用索引，从而降低查询效率。如下所示： ``` EXPLAIN SELECT * FROM table WHERE col1 = 'value1' OR col2 = 'value2'; ``` 在这个查询中，可以考虑将查询条件改为： ``` EXPLAIN SELECT * FROM table WHERE col1 = 'value1' UNION SELECT * FROM table WHERE col2 = 'value2'; ``` 5. 避免全表扫描：全表扫描是一种效率比较低下的查询方式，可以通过优化查询条件或者添加合适的索引来避免全表扫描。如下所示： ``` EXPLAIN SELECT * FROM table WHERE col LIKE '%value%'; ``` 在这个查询中，可以考虑将查询条件改为： ``` EXPLAIN SELECT * FROM table WHERE col LIKE 'value%'; ``` 这样可以使用(col)的索引来执行查询，避免全表扫描。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值