Mysql实战题目，如何加索引2？

最新推荐文章于 2023-06-27 15:44:22 发布
最新推荐文章于 2023-06-27 15:44:22 发布
阅读量771 收藏 2
点赞数
分类专栏： mysql 文章标签： mysql sql mysql实战
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hl_java/article/details/100054933
版权

Mysql实战题目，如何加索引2？
这一篇，较上一篇文章区别在于构造的数据内容发生了变化

实战题目

select * from t2 where a=? and b>? order by c limit 0,100 如何加索引

测试环境

mysql> select version() ;
+-----------+
| version() |
+-----------+
| 8.0.17    |
+-----------+
1 row in set (0.01 sec)

附
快速搭建一个指定版本的Mysql https://blog.csdn.net/hl_java/article/details/90259538

表及数据准备

drop table if exists t2;
create table t2(
id int primary key auto_increment, 
a int ,
b int ,
c int 
) ENGINE=InnoDB;


drop procedure if exists add_data;
DELIMITER ;;
CREATE PROCEDURE add_data2(IN loop_times INT)
BEGIN
    DECLARE var INT DEFAULT 1;
    WHILE var <= loop_times DO
        INSERT INTO t2 VALUES (NULL,var%10,var ,loop_times - var); 
        SET var = var + 1;
    END WHILE;
END 
;;
DELIMITER ;


SET @type = 100000;
CALL add_data2(@type);

不同场景测试

SQL1
select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
说明：根据插入的数据我们提前分析sql查询的结果集为5000，然后limt之后就只返回100条了

SQL2
select * from t2 where a=1 and b>100 order by c limit 0,100 ;
说明：根据插入的数据我们提前分析sql查询的结果集为9990，然后limt之后就只返回100条了

仅有主键索引的情况

select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
100 rows in set (0.03 sec)

select * from t2 where a=1 and b>100 order by c limit 0,100 ;
100 rows in set (0.03 sec)

附
explain select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100068 |     3.33 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

explain select * from t2 where a=1 and b>100 order by c limit 0,100 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100068 |     3.33 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

增加字段a索引的情况

alter table t2 add index idx_a (a);

select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
100 rows in set (0.01 sec)

select * from t2 where a=1 and b>100 order by c limit 0,100 ;
100 rows in set (0.01 sec)


附
mysql> explain select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_a         | idx_a | 5       | const | 10000 |    33.33 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t2 where a=1 and b>100 order by c limit 0,100 ;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_a         | idx_a | 5       | const | 10000 |    33.33 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

增加字段a,b联合索引的情况

alter table t2 add index idx_ab (a,b);

select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
100 rows in set (0.01 sec)

select * from t2 where a=1 and b>100 order by c limit 0,100 ;
100 rows in set (0.02 sec)


附
explain select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | idx_ab        | idx_ab | 10      | NULL | 5000 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.01 sec)

explain select * from t2 where a=1 and b>100 order by c limit 0,100 ;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | idx_ab        | idx_ab | 10      | NULL | 18222 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

增加字段a,c联合索引的情况

alter table t2 drop index idx_ab;
alter table t2 add index idx_ac (a,c);

select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
100 rows in set (0.00 sec)

select * from t2 where a=1 and b>100 order by c limit 0,100 ;
100 rows in set (0.00 sec)

附
explain select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_ac        | idx_ac | 5       | const | 18242 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

explain select * from t2 where a=1 and b>100 order by c limit 0,100 ;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_ac        | idx_ac | 5       | const | 18242 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

增加字段a,b,c联合索引的情况

alter table t2 drop index idx_ac;
alter table t2 add index idx_abc (a,b,c);

select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
100 rows in set (0.01 sec)

select * from t2 where a=1 and b>100 order by c limit 0,100 ;
100 rows in set (0.01 sec)

附
explain select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | idx_abc       | idx_abc | 10      | NULL | 5000 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.01 sec)

explain select * from t2 where a=1 and b>100 order by c limit 0,100 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | idx_abc       | idx_abc | 10      | NULL | 18462 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

增加字段c,a,b联合索引的情况

alter table t2 drop index idx_abc;
alter table t2 add index idx_cab (c,a,b);

select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
100 rows in set (0.01 sec)

select * from t2 where a=1 and b>100 order by c limit 0,100 ;
100 rows in set (0.01 sec)

附
explain select * from t2 where a=1 and b>50000 order by c limit 0,100 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | index | idx_cab       | idx_cab | 15      | NULL |  100 |     3.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

explain select * from t2 where a=1 and b>100 order by c limit 0,100 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | index | idx_cab       | idx_cab | 15      | NULL |  100 |     3.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

表数据内容调整后
与上一篇依旧没有大的变化，加索引后时间开销也还分布在0.01 ~ 0.02 sec之间

下面这篇文章会提到答案
Mysql的explain之Extra字段讲解

明月(Alioo)
关注
专栏目录
创建索引原则及SQL添加索引
碼农的日常
08-14 1万+
一、数据库建立索引的原则  1.确定针对该表的操作是大量的查询操作还是大量的增删改操作。  2.尝试建立索引来帮助特定的查询。检查自己的sql语句，为那些频繁在where子句中出现的字段建立索引。  3.尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间，同时，复合索引也占磁盘空间。  4.对于小型的表，建立索引可能会影响性能  5.应该避免对具有较少值的字段进行索引。 ...
几个mysql练习题目压缩包
09-03
这个压缩包文件"几个mysql练习题目压缩包"显然包含了若干个与MySQL相关的练习题目，旨在帮助用户提升MySQL的技能和知识。以下是一些可能涵盖的MySQL知识点： 1. 数据类型：在MySQL中，了解各种数据类型（如INT, ...
索引怎么加合适？
qq_41115103的博客
12-06 1394
索引怎么加合适？1，用eplain查看sql执行原理 1，用eplain查看sql执行原理 EXPLAIN简介 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 ➤ 通过EXPLAIN，我们可以分析出以下结果： 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用...
Mysql实战题目，如何加索引？
hl_java的专栏
08-24 1338
Mysql实战题目，如何加索引？ 实战题目 select * from t where a=? and b>? order by c limit 0,100 如何加索引 测试环境 mysql> select version() ; +-----------+ | version() | +-----------+ | 8.0.17 | +-----------+ 1 row i...
mysql的索引创建原则
mlh532354163的博客
03-16 487
索引创建原则： 1.最左匹配原则： 比如a=1 and b=2, and c>3 and d=4, a b c d字段都创建了索引，但此时只用到了 a b索引；如果改成a=1 and b=2 and d=4 and c>3，将d和c对调下，此时用到了a b d索引 2.=和in位置无关 3.常出现在where或on后的字段 4.order by 或group后的字段，添加索引加...
MySQL系列（四）— SQL调优实战
最新发布
baihehanqiu1314的博客
06-27 947
本篇文章从几个方面进行讨论：一是索引遵循的原则，二是利用索引进行SQL调优时的注意事项，三是对常见问题SQL该如何进行调优。
MySQL数据库SQL实战题目汇总详解
由于实战题目汇总强调mysql，因此还会涉及一些MySQL数据库特有的知识点。比如，MySQL特有的数据类型、存储引擎（如InnoDB、MyISAM）、分区、复制、备份与恢复等高级特性。 4. 数据查询优化 查询优化是数据库性能...
MySQL数据库SQL实战题目汇总解析
其描述中提及了"mysql"，标签为"数据库 mysql sql"，从文件名称列表"Database-SQL-Actual-Combat-master-8"可以推断该压缩包内可能包含以MySQL数据库为背景的SQL实战题目集合。 首先，我们需要了解SQL（Structured ...
2023年MySQL大厂面试题全解析：实战技巧与索引深度讲解
- 自适应哈希索引（011题）和2-3树/2-3-4树（012题）是针对特定查询优化的高级索引技术，理解它们有助于应对复杂查询场景。 7. 主键处理: - 013题对比了自增主键和字符串类型主键，解释了它们的区别以及对数据库...
揭秘MySQL OCP DBA认证实战测试题目
MySQL DBA测试题是一份针对MySQL数据库管理员（DBA）职业认证的内部考试题目集，包含选择题和综合实习题，旨在检验考生对MySQL数据库管理、架构以及优化的理解。以下是部分内容的详细解析： 1. 关于查询问题： a) ...
如何给字符串字段加索引
qq_50876039的博客
10-20 348
如何给数据库的字符串类型字段添加索引
MySql添加索引的几种方式
热门推荐
HunterYoung0718的博客
10-20 1万+
1、添加primary key(主键索引)alter table 表名 add primary key(列名);2、添加unique（唯一索引）alter table 表名 add unique(列名);3、添加普通索引index(普通索引)alter table 表名 add index 索引名（index name） （列名）；4、添加fulltext（全文索引）alter table 表名 add fulltext （列名）；5、添加多列索引。
mysql 性能分析及explain用法(转)
weixin_30580341的博客
08-17 229
转自：http://blog.sina.com.cn/s/blog_4586764e0100o9s1.html 1 使用explain语句去查看分析结果，如 explain select * from test1 where id=1;会出现：id selecttype table type possible_keys key key_len ref rows extra...
谈谈SQL优化，如何判断是否要加索引
weixin_71118996的博客
08-11 758
谈谈SQL优化，如何判断是否要加索引
Mysql的explain之Extra字段讲解
hl_java的专栏
08-24 1765
认真阅读了一下explain之Extra字段，前2篇关于一道sql （select * from t where a=? and b>? order by c limit 0,100 ） 如何加索引的问题答案终于浮出水面 Using index 表示使用了覆盖索引，性能上会快很多 覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。 Using index con...
SQL高级之order by优化
youhebuke225的博客
06-13 2683
假设 KEY a_b_c(a,b,c)
mysql 复合索引 总结
2x 青年，欢乐多。
03-11 4473
对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。下面用几个例子对比查询条件的不同对性能影响. create table test( a int, b in
2年java，蚂蚁一面，卒
chengwengwan4930的博客
07-10 143
其实我一个都没答上来。并不是因为我笨，是因为我不会。在大扰的帮助下，现在我会了，求求你再给我一个机会。 TreeSet/HashSet 区别 顾名思义，首先是结构上的不同 1、TreeSet背后的结构是TreeMap，也就是红黑树，能够实现自动排序。它通过equals方法或者compar...
MySQL-第四章-索引及执行计划
因上努力，果上随缘。但行好事，莫问前程。
06-01 311
1. 索引作用 提供了类似于书中目录的作用,目的是为了优化查询 2. 索引的种类(算法) B树索引 Hash索引 R树 Full text GIS 3. B树 基于不同的查找算法分类介绍 B-tree B+Tree 在范围查询方面提供了更好的性能(> < >= <= like) B*Tree 4. 在功能上的分类 4.1 辅助索引(S)怎么构建B树结构的? (1). 索引是基于表中,列(索引键)的值生成的B树结构 (2). 首先提取此列
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值