MySQL索引优化（二）

最新推荐文章于 2023-04-09 07:00:00 发布
最新推荐文章于 2023-04-09 07:00:00 发布
阅读量506 收藏 3
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36602071/article/details/128307398
版权

文章目录

一、查询优化

1. 索引失效

（1）最左前缀法则：如果索引有多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。（针对复合索引）
（2）不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。
（3）存储引擎不能使用索引中范围条件右边的列。
（4）Mysql在使用不等于时无法使用索引，会导致全表扫描。
（5）is null可以使用索引，但是is not null无法使用索引。
（6）like以通配符开头会使索引失效，导致全表扫描。
（7）字符串不加单引号，索引会失效。
（8）使用or连接时，索引失效。

  • 创建表
drop table if exists students;
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主键id",
  sname VARCHAR (24) COMMENT '学生姓名',
  age INT COMMENT '年龄',
  score INT COMMENT '分数',
	time TIMESTAMP COMMENT '入学时间'
);
 
INSERT INTO students(sname,age,score,time) VALUES('小明',22,100,now());
INSERT INTO students(sname,age,score,time) VALUES('小红',23,80,now());
INSERT INTO students(sname,age,score,time) VALUES('小绿',24,80,now());
INSERT INTO students(sname,age,score,time) VALUES('黑',23,70,now());
  • 创建复合索引
alter table students add index idx_sname_age_score(sname,age,score);

在这里插入图片描述

  • 使用复合索引查询

key_len表示使用索引的字节大小，值越大表示索引使用越充分

explain select * from students where sname="小明" and age = 22 and score = 100;
explain select * from students where sname="小明" and age = 22;
explain select * from students where sname="小明";

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

（1）不满足最左前缀法则，索引失效

explain select * from students where age = 20;

在这里插入图片描述

查询条件是sname、score，中间跳过了age， 所以score没有用到索引，只有sname用到了索引
跟上面单独使用sname查询时的ken_len一样，都是99

explain select * from students where sname="小明" and score = 80;

在这里插入图片描述

（2）在索引列上做任何计算、函数操作，索引失效

-- 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。
explain select * from students where left(sname,2) = "小明";

在这里插入图片描述

（3）存储引擎使用索引中范围条件右边的列，索引失效

age是范围查找，所以age右边的score列索引失效，ken_len=104说明只有sname、age列索引生效

-- 存储引擎不能使用索引中范围条件右边的列。
explain select * from students where sname="小明" and age > 22 and score = 100;

在这里插入图片描述

（4）Mysql使用不等于时，索引失效

-- Mysql在使用不等于时无法使用索引会导致全表扫描。
explain select * from students where sname != "小明";

在这里插入图片描述

（5）is null可以使用索引，但是is not null无法使用索引

explain select * from students where sname is  null;

在这里插入图片描述

-- is null可以使用索引，但是is not null无法使用索引。
explain select * from students where sname is not null;

在这里插入图片描述

（6）like以通配符开头，索引失效

-- like以通配符开头会使索引失效导致全表扫描。
explain select * from students where sname like "%明";

在这里插入图片描述

（7）字符串不加单引号，索引失效

-- 字符串不加单引号索引会失效。
explain select * from students where sname = 123;

在这里插入图片描述

（8）使用or连接时，索引失效

-- 使用or连接时索引失效。
explain select * from students where sname="小明" or age = 22;

在这里插入图片描述

小结

在这里插入图片描述

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

2. 排序优化

1.尽量避免使用Using FileSort方式排序。
2.order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。
3.where子句中如果出现索引范围查询会导致order by索引失效。

KEY a_b_c(a,b,c)
order by 能使用索引最左前缀
-- ORDER BY a
-- ORDER BY a,b
-- ORDER BY a,b,c
-- ORDER BY a DESC, b DESC, c DESC

如果WHERE使用索引的最左前缀定义为常量，则order by能使用索引
-- WHERE a = CONST ORDER BY b,c 
-- WHERE a = CONST AND b = CONST ORDER BY c 
-- WHERE a = CONST AND b > CONST ORDER BY b,c 

不能使用索引进行排序
-- ORDER BY a ASC, b DESC, c DESC // 排序不一致
-- WHERE g = CONST ORDER BY b,c // 丢失a索引
-- WHERE a = CONST ORDER BY c  // 丢失b索引
-- WHERE a = CONST ORDER BY a,d  // d不是索引的一部分
-- WHERE a in(...) ORDER BY b,c //对于排序来说，多个相等条件也是范围查询

3. 单表优化

  • 建表
-- 单表查询优化
CREATE TABLE IF NOT EXISTS article (
id INT(10) PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) NOT NULL,
category_id INT(10) NOT NULL,
views INT(10) NOT NULL,
comments INT(10) NOT NULL,
title VARBINARY(255) NOT NULL,
content TEXT NOT NULL
);
 
INSERT INTO article(author_id, category_id, views, comments, title, content) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

#1.查询category_id为1的，且comments大于1的情况下，views最多的id和author_id的信息
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;

在这里插入图片描述

#2.建立索引
alter table article add index idx_ccv(category_id,comments,views);
#3.再次测试，comments是范围查询，所以右边views列索引失效
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;

在这里插入图片描述

#4.重新创建索引
drop index idx_ccv on article;
// comments是范围查询，所以不建立comments复合索引
alter table article add index idx_cv(category_id,views);
#5.再次测试
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;

在这里插入图片描述

4. 关联查询优化

  • 建表
CREATE TABLE IF NOT EXISTS class (
id INT(10) AUTO_INCREMENT,
card INT(10),
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS book (
bookid INT(10) AUTO_INCREMENT,
card INT(10),
PRIMARY KEY (bookid)
);
 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

内连接时，mysql会自动把小结果集的表选为驱动表，所以大表的字段最好加上索引。
左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理。
我们最好保证被驱动表上的字段建立了索引。

#1.联表查询
explain select *
from class
left join book
on class.card = book.card;

在这里插入图片描述

#2.建立索引
alter table book add index idx_card(card);
#3.测试
explain select *
from class
left join book
on class.card = book.card;

在这里插入图片描述

5. 分组优化

参照排序优化。

  • 建表
drop table if exists students;
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主键id",
  sname VARCHAR (24) COMMENT '学生姓名',
  age INT COMMENT '年龄',
  score INT COMMENT '分数',
	time TIMESTAMP COMMENT '入学时间'
);
 
INSERT INTO students(sname,age,score,time) VALUES('小明',22,100,now());
INSERT INTO students(sname,age,score,time) VALUES('小红',23,80,now());
INSERT INTO students(sname,age,score,time) VALUES('小绿',24,80,now());
INSERT INTO students(sname,age,score,time) VALUES('黑',23,70,now());

-- 分组优化
alter table students add index idx_sas(sname,age,score);

#没有WHERE条件，GROUP BY age不满足最左前缀，所以是Using temporary; Using filesort
explain select count(*),sname from students GROUP BY age;

在这里插入图片描述

explain select count(*),sname 
from students 
where sname="小明" and age > 22
GROUP BY score;

在这里插入图片描述

二、慢查询日志

1. 慢查询日志简介

MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。

2. 慢查询日志使用

默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数。
查看是否开启：show variables like ‘%slow_query_log%’;
开启日志：set global slow_query_log = 1;
设置时间：set global long_query_time = 1;
查看时间： SHOW VARIABLES LIKE ‘long_query_time%’;
查看超时的sql记录日志：Mysql的数据文件夹下
C:\ProgramData\MySQL\MySQL Server 5.7\Data\设备名称-slow.log
注意：非调优场景下，一般不建议启动该参数，慢查询日志支持将日志记录写入文件，开启慢查询日志或多或少带来一定的性能影响。

-- 慢查询日志
-- 查看是否开启：show variables like '%slow_query_log%';
show variables like '%slow_query_log%';

在这里插入图片描述

-- 开启日志：
set global slow_query_log = 1;
-- 设置时间: 
set global long_query_time = 1;
-- 查看时间: 需要重新开启一个会话才能查看刚设置的时间
SHOW VARIABLES LIKE 'long_query_time%';
  • 需要重新开启一个会话才能查看刚设置的时间，cmd新建连接创建新会话
C:\Users\Administrator>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)

mysql>
  • 查询一个有300万数据的表，没有建PAGE索引，耗时超过1s
select * from person where PAGE = 51;

在这里插入图片描述

  • 打开慢查询日志文件
    在这里插入图片描述
Super_Leng
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
21.3.6 范围条件右侧的列无法使用索引
weixin_41275260的博客
06-25 219
21.3.6　范围条件右侧的列无法使用索引 使用联合索引查询数据时，如果按照联合索引中字段的某个范围查询数据，则此字段后面的列无法使用索引，会进行全表扫描。 mysql> EXPLAIN SELECT * FROM t_goods WHERE t_category_id > 1 AND t_name = '卫衣' \G *************************...
MySql索引优化
12-14
MySQL索引优化是数据库管理中的关键环节，它直接影响着数据查询效率和系统整体性能。创建索引的主要目的是为了加速数据的检索，特别是对于大型数据库，这一点尤为重要。索引能够确保数据的唯一性，使得数据库表中的...
MySQL存储引擎不能使用索引中范围条件右边的列
Jasonzhou
10-29 1586
一、前言 对于range查询，在一条组合索引里面，range字段后面的列是不生效的，不会使用索引，range列自身仍是生效的。 二、正文 （1）首先建立一个学生表： （2）建立一个联合索引（name,age,class_id） 测试 Case1： 只有 name 且是等于 explain select * from student where name = 'Mitsunari' 结果如下： 注意：ken_len字段长度和ref字段使用了索引中的第一列，也就是name。 Case2： name和ag
【mysql 5.7】存储引擎不能使用索引中范围条件右边的列 & range
m0_45406092的博客
01-04 1712
概述 对于range查询，在一条组合索引里面，range字段后面的列是不生效的，不会使用索引 1 准备数据 1.1 建表 DROP TABLE IF EXISTS staff; CREATE TABLE IF NOT EXISTS staff ( id INT PRIMARY KEY auto_increment, name VARCHAR(50), age INT, pos VARCHAR(50) COMMENT '职位', salary DECIMAL(10,2)
为什么范围后索引会失效 存储引擎不能使用索引中范围条件右边的列
JavaEdge全是干货的技术号
02-21 1713
比如说有三个字段 a b c，建立复合索引a_b_c。此时叶子节点的数据排序后可能为 (a=1 b=1 c=1) (a=1 b=2 c=1) (a=1 b=2 c=3) (a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2) (a=3 b=0 c=1) (a=3 b=3 c=5) (a=3 b=8 c=6) 查找 select a,b,c from table where a = 2 and b = 5 and c = 2 先根据a = 2找到第二
mysql索引失效的常见9种原因详解
qq_63815371的博客
04-22 2万+
目录 前言： 1.最佳左前缀法则 2.主键插入顺序 3.计算、函数、类型转换(自动或手动)导致索引失效 4.范围条件右边的列索引失效 5.不等于(!= 或者<>)导致索引失效 6.is null可以使用索引，is not null无法使用索引 7.like以通配符%开头索引失效 8.OR 前后只要存在非索引的列，都会导致索引失效 9.数据库和表的字符集统一使用utf8mb4 特别鸣谢： 前言： MySQL中提高性能的一个最有效的方式是对数据表设计合...
mysql索引优化.xmind
05-10
mysql索引优化思维导图
Mysql索引步骤及优化
12-14
MySQL索引是数据库管理系统中用于加速数据检索的重要工具。它的工作原理类似于书籍的目录，能够快速定位到数据表中的特定记录。索引分为聚簇索引和非聚簇索引两类。聚簇索引根据数据的物理存储位置进行排序，而非聚...
mysql查询不走索引及解决方法
09-04
mysql查询，通过explain 分析，没有利用到索引，查询效率不高等出现的问题。
MySQL索引设计原则 索引使用、索引失效、索引SQL优化
最新发布
一只文森特
04-09 914
MySQL索引设计原则、索引使用、索引失效、索引SQL优化
索引使用规则及索引失效情况
Maringss的博客
12-05 3524
索引使用规则及索引失效情况
Mysql索引失效
m0_62946761的博客
09-17 9696
在数据库日常使用中，因索引失效而导致的没有充分利用索引，是导致数据库查询效率低下的一个重要原因之一。 本文介绍索引失效的十一种情况。
[MySQL高级](四) 索引优化之索引失效
睁眼看世界
05-22 3657
1. 前言 　　索引的建立是为了让我们更加高效快速的查询出结果，但是，要想充分利用起索引，我们首先要解决的最大问题就是要避免索引失效，下面我们来一起通过实例来探讨造成索引失效的情况，并通过优化SQL查询语句来避免索引失效。 　　➤ 准备工作：、 创建数据表SQL CREATE TABLE `staffs` ( `id` int(11) NOT NULL AUTO_INCREMENT...
MySQL避免索引失效
qq_41622603的博客
08-11 210
创建索引 下面所有示例都是围绕这个索引来说 ALTER TABLE users ADD INDEX idx_users_accountSexUsername(account,sex,userName); 一、全值匹配 指的是创建的索引在查询条件里全部使用到，这种情况索引一般都不会失效 把where后面userName和sex的顺序反过来索引也不会失效 二、左前缀法则： 指的是查询从索引的最左前列开始并且不跳过索引中的列。 因为创建索引的顺序是account→userNam.
索引简介
cc_xp的博客
03-28 193
1. 索引基础 MySQL中，索引是在存储引擎层而不是服务器层实现的，所以没有统一的索引标准：不同的存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层实现也可能不同。 2. B-Tree索引 B-Tree索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。 假设有如下数据表： CREAT...
8、索引类型详解
you can you up up up的博客
10-16 2674
索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。 存储方式区分 根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。 1) B-树索引 B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。 B-树索引是一个典型的数据结构，其包含的组件主要有以下几个： 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼
MySQL 高级知识（索引、优化）
热门推荐
Crystal---的博客
07-19 3万+
MySQL体系架构 连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件； 1、连接层：主要完成一些类似于连接处理，授权认证及相关的方案； 2、服务层：主要完成大多数核心服务功能； 3、引擎层：负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信； 注：储存引擎式基于表的，而不是数据库的； MySQL存储...
MySQL索引优化深度解析
本文主要探讨了MySQL如何使用索引以及索引优化的策略，涵盖了索引的分类、MyISAM和InnoDB存储引擎的索引结构、索引的优缺点以及设计原则。 在MySQL中，索引对于提高查询速度至关重要。它们可以显著提升全值匹配和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值