索引优化实战七

最新推荐文章于 2024-08-06 11:01:29 发布
最新推荐文章于 2024-08-06 11:01:29 发布
阅读量109 收藏
点赞数
分类专栏： Mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chengqiuming/article/details/120380913
版权

一 like 的前后模糊匹配

like 以通配符开头（'%abc...'），mysql 索引失效会变成全表扫描的操作。

二 实战

# % 作为 like 的前缀，会失效
mysql> explain select * from staffs where name like '%July%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# % 作为 like 的前缀，会失效
mysql> explain select * from staffs where name like '%July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 索引生效：% like 加右边
mysql> explain select * from staffs where name like 'July';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

三 解决 like '%字符串%'时，索引不被使用的方法

1 表脚本

CREATE TABLE `tbl_user`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `age`INT(11) DEFAULT NULL,
  `email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

2 操作

# 建立索引前，全表扫描
mysql> explain select name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 建立索引前，全表扫描
mysql> explain select id from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 建立索引前，全表扫描
mysql> explain select name from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 建立索引前，全表扫描
mysql> explain select age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 建立索引前，全表扫描
mysql> explain select name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 建立索引
mysql> create index idx_user_nameAge on tbl_user(Name,age);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 使用到覆盖索引，索引生效
mysql> explain select name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

# 使用覆盖索引，索引生效
mysql> explain select id from tbl_user where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

# 使用覆盖索引，索引生效
mysql> explain select name from tbl_user where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

# 使用覆盖索引，索引生效
mysql> explain select age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

# 使用覆盖索引，索引生效
mysql> explain select id,name from tbl_user where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

# 使用覆盖索引，索引生效
mysql> explain select id,name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

# 使用覆盖索引，索引生效，主要是 email 惹的祸
mysql> explain select name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

# 没能用到覆盖索引，索引失效
mysql> explain select * from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 没能用到覆盖索引，索引失效，主要是 email 惹的祸
mysql> explain select id,name,age,email from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3 结论

使用覆盖索引可以解决问题。

chengqiuming
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL索引优化实战
Liu_G的博客
07-27 934
联合索引、Online DDL
mysql索引优化实战
qq_41604890的博客
07-11 629
MySQL 索引优化全攻略 Mysql最左匹配原则 MYSQL | 最左匹配原则 索引类型 在实际应用中，InnoDB 是 MySQL 建表时默认的存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采 用最多的索引类型。 在创建表时，InnoDB 存储引擎默认使用表的主键作为主键索引，该主键索引就是聚簇索引（Clustered Index），如果表没有定义主键，InnoDB 就自己产生一个隐藏的 6 个字节的主键 ID 值作为主键索引， 而创建的主键索引默认..
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL索引优化实战宝典
我是一名热爱编程和分享知识的技术作者，在金融和教育领域有多年的研发管理经验，感谢您访问我的博客，一定会为您带来不一样的收货！
03-27 1348
MySQL索引是MySQL数据库用于快速查找和访问数据的一种数据结构，它就像书的目录一样，可以帮助数据库系统更快地定位到所需数据的位置，从而大大提高查询性能。下面来看一下索引分类。
sql索引优化实战总结
weixin_43811057的博客
05-31 1584
一、 避免索引失效 -- 最左匹配原则 * -- 范围条件右边的索引失效 -- 不再索引列上做任何操作 * -- 使用不等于(!=或者<>)索引失效 -- is not null无法使用索引 -- like以通配符开头(%qw)索引失效 * -- 字符串不加引号索引失效 -- 使用or连接索引失效 -- 尽量使用覆盖索引 1.1 全值匹配 -- 创建组合索引 create index idx_name_dep_age on employee(name,dep_id,age) -- 索引字
MySQL索引优化实战（一）
qq_27740127的博客
06-18 287
引入了索引下推优化，在联合索引遍历过程中，匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据（首先会在联合索引树中匹配到”LiLei“开头的索引，然后拿这些匹配到的对应的主键挨个去做回表操作，去主键索引树找出对应的记录，再去比对age和position是否符合。虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，提供先进的推理，复杂的指令，更多的创造力。
MySQL索引优化实战一
tqq的博客
11-28 1262
一篇MySQL调优文章实战
Mysql索引优化实战
weixin_45161367的博客
07-08 738
Mysql索引优化实战
2024MySQL最新索引优化实战一
最新发布
业精于勤荒于嬉，行成于思毁于随
08-06 722
对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。
Mysql索引优化实战一
lmj3732018的博客
04-18 9107
一、示例表 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` ti...
Oracle DBA实战索引优化
03-05
### Oracle DBA实战索引优化 #### 一、索引概述与原理 索引是数据库管理系统(DBMS)中一种可选的数据结构，它的主要目的是为了加快数据查询的速度。Oracle数据库中的索引通常由一系列行标识符（ROWID）及其对应的...
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
Mysql索引优化实战.zip
05-24
本资料“Mysql索引优化实战”旨在深入探讨如何通过有效的索引策略提升MySQL数据库的查询性能。 首先，我们需要理解索引的基本概念。在数据库中，索引就像书的目录，它能帮助数据库系统快速定位到数据，而无需遍历...
mysql出现which is not functionally dependent on columns in GROUP BY clause报错解决方案
实践求真知
05-10 1万+
一数据表结构 CREATE TABLE `sys_user_role` ( `id` varchar(30) NOT NULL, `uid` varchar(30) NOT NULL, `rid` varchar(30) NOT NULL, PRIMARY KEY (`id`,`uid`,`rid`), KEY `user_role_ibfk_2` (`uid`), KEY `user_role_ibfk_1` (`rid`), CONSTRAINT `sys_user_r.
python的mysql数据库的删除操作
实践求真知
11-22 1万+
一 代码 import pymysql # 打开数据库连接 db = pymysql.connect("localhost","root","root","db_test01" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 删除语句 sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" %
E-R图转化为关系表
实践求真知
03-22 6963
一E-R图 二关系表 这里只列出4个关系表。 t_project(project_id,project_name) t_order(project_id,order_id,data_type,creater,creat_time,end_time,state) t_workorder(order_id,workorder_id,operator,start_time,end_ti...
慢查询日志
实践求真知
09-21 5546
一什么是慢查询日志 1 MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。 2 具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10秒 以上的语句。 3 由它来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条 sql 执行超过5秒钟，我们...
SQLServer索引设计与优化实战指南
"本文档是关于SQL Server索引设计与优化的综合指南，涵盖了从集簇索引设计、非聚簇索引创建到索引优化和维护等多个方面，旨在提升SQL Server数据库系统的性能。" 在SQL Server中，索引的设计与优化是至关重要的，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值