MySQL调优

最新推荐文章于 2024-07-25 17:06:50 发布

EzrealYi

最新推荐文章于 2024-07-25 17:06:50 发布

阅读量916

点赞数 24

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/EzrealYi/article/details/135373862

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

性能瓶颈定位MySQL慢查询

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化SQL，更重要的是得先找到需要优化的SQL语句。MySQL数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL，这将极大程度帮助我们快速定位到问题所在，以便对症下药。

性能优化的思路

首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句
其次使用explain命令去查询由问题的SQL的执行计划
最后可以使用show profile[s] 查看由问题的SQL的性能使用情况
优化SQL语句

MySQL慢查询日志

慢查询日志用来记录在 MySQL 中执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。

慢查询参数

执行下面的语句

SHOW VARIABLES LIKE "%slow_query%" ;

在这里插入图片描述

slow_query_log：是否开启慢查询,on为开启,off为关闭;
slow_query_log_file：慢查询日志文件路径

SHOW VARIABLES LIKE "%long_query_time%" ;

在这里插入图片描述

long_query_time : 阈值，超过多少秒的查询就写入日志

show variables like 'log_queries_not_using_indexes';

在这里插入图片描述

系统变量 log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。

开启慢查询日志（临时）

在MySQL执行SQL语句设置,但是如果重启MySQL的话会失效。

set global slow_query_log=on;
set global long_query_time=1;

开启慢查询日志（永久）

修改：/etc/my.cnf，添加以下内容，然后重启MySQL服务

[mysqld]
lower_case_table_names=1
slow_query_log=ON
slow_query_log_file=/usr/local/mysql5.7.44/log/mysql-slow.log
long_query_time=1

（数据库操作超过100毫秒认为是慢查询，可根据需要进行设定，如果过多，可逐步设定，比如先行设定为2秒，逐渐降低来确认瓶颈所在）

慢查询测试

select SLEEP(3);

在这里插入图片描述

格式说明：

第一行，SQL查询执行的具体时间
第二行，执行SQL查询的连接信息，用户和连接IP
第三行，记录了一些我们比较有用的信息，
- Query_timme，这条SQL执行的时间，越长则越慢
- Lock_time，在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
- Rows_sent，查询返回的行数
- Rows_examined，查询检查的行数，越长就越浪费时间
第四行，设置时间戳，没有实际意义，只是和第一行对应执行时间。
第五行，执行的SQL语句记录信息

MySQL性能分析 EXPLAIN

概述

explain（执行计划），使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。

explain主要用于分析查询语句或表结构的性能瓶颈。

通过explain命令可以得到:

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

EXPLAIN字段介绍

explain使用：explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。

在这里插入图片描述

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

数据准备

-- 创建数据库
CREATE DATABASE test_explain CHARACTER SET 'utf8';

-- 创建表
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );

-- 每张表插入3条数据
INSERT INTO L1(title) VALUES('heima001'),('heima002'),('heima003');
INSERT INTO L2(title) VALUES('heima004'),('heima005'),('heima006');
INSERT INTO L3(title) VALUES('heima007'),('heima008'),('heima009');
INSERT INTO L4(title) VALUES('heima010'),('heima011'),('heima012');

id字段

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下

EXPLAIN SELECT * FROM  L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;

在这里插入图片描述

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

EXPLAIN SELECT * FROM L2 WHERE id = (
    SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'heima009'));

在这里插入图片描述

select_type 与 table字段

查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

simple : 简单的select查询，查询中不包含子查询或者UNION

EXPLAIN SELECT * FROM L1;

在这里插入图片描述

primary : 查询中若包含任何复杂的子部分，最外层查询被标记

EXPLAIN SELECT * FROM L2 WHERE id = (EXPLAIN SELECT * FROM L2 WHERE id = (
    SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'heima03'));

在这里插入图片描述

subquery : 在select或where列表中包含了子查询

EXPLAIN SELECT * FROM L2 WHERE L2.id = (SELECT id FROM L3 WHERE L3.title = 'heima03' )

在这里插入图片描述

derived : 在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
union : 如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived

union result : UNION 的结果

EXPLAIN SELECT * FROM L2
UNION 
SELECT * FROM L3

在这里插入图片描述

partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	one	p201801,p201802,p201803,p300012	index	NULL	PRIMARY	9	NULL	3	100	Using index

type字段

type显示的是连接类型，是较为重要的一个指标。下面给出各种连接类型,按照从最佳类型到最坏类型进行排序:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

-- 简化
system > const > eq_ref > ref > range > index > ALL

system : 表仅有一行 (等于系统表)。这是const连接类型的一个特例,很少出现。
const : 表示通过索引一次就找到了, const用于比较 primary key 或者 unique 索引. 因为只匹配一行数据,所以如果将主键放在 where条件中, MySQL就能将该查询转换为一个常量
```
EXPLAIN SELECT * FROM L1 WHERE L1.id = 1
```
eq_ref : 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配. 常见与主键或唯一索引扫描
```
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.id = L2.id ;
```
ref : 非唯一性索引扫描, 返回匹配某个单独值的所有行, 本质上也是一种索引访问, 它返回所有匹配某个单独值的行, 这是比较常见连接类型.

未加索引之前
```
EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;
```
加索引之后
```
CREATE INDEX idx_title ON L2(title);

EXPLAIN SELECT * FROM L1 ,L2 WHERE L1.title = L2.title ;
```
range : 只检索给定范围的行,使用一个索引来选择行。
```
EXPLAIN SELECT * FROM L1 WHERE L1.id > 10;
EXPLAIN SELECT * FROM L1 WHERE L1.id IN (1,2);
```
key显示使用了哪个索引. where 子句后面使用 between 、< 、> 、in 等查询, 这种范围查询要比全表扫描好
index : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,一般是使用了索引进行排序分组
```
EXPLAIN SELECT * FROM L1 ORDER BY id;
```
ALL : 对于每个来自于先前的表的行组合,进行完整的表扫描。
```
EXPLAIN SELECT * FROM L1;  
```
一般来说,需要保证查询至少达到 range级别,最好能到ref

possible_keys 与 key字段

possible_keys
- 显示可能应用到这张表上的索引, 一个或者多个. 查询涉及到的字段上若存在索引, 则该索引将被列出, 但不一定被查询实际使用.
key
- 实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
- 覆盖索引：一个索引包含(或覆盖)所有需要查询的字段的值,通过查询索引就可以获取到字段值

理论上没有使用索引,但实际上使用了

EXPLAIN SELECT L1.id FROM L1;

在这里插入图片描述

理论和实际上都没有使用索引

EXPLAIN SELECT * FROM L1 WHERE title = 'heima01';

在这里插入图片描述

理论和实际上都使用了索引

EXPLAIN SELECT * FROM L2 WHERE title = 'heima02';

在这里插入图片描述

key_len字段

表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度.

key_len 字段能够帮你检查是否充分利用了索引 ken_len 越长, 说明索引使用的越充分

key_len表示使用的索引长度，key_len可以衡量索引的好坏，key_len越小索引效果越好，那么key_len的长度是如何计算的？

创建表

CREATE TABLE `user` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`),
  KEY `idx_sex` (`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

使用explain 进行测试

在这里插入图片描述

id字段类型为bigint，长度为8，id为主键，不允许Null ，key_len = 8 。
```
EXPLAIN select * FROM user WHERE id = 1;
```
name的字段类型是varchar(10)，允许Null，字符编码是utf8，一个字符占用3个字节，varchar为动态类型，key长度加2，key_len = 10 * 3 + 2 + 1 = 33 。
```
EXPLAIN select * FROM user WHERE name = 'tom';
```

联合索引key_len计算

我们删除user表其他辅助索引，建立一个联合索引

ALTER TABLE user DROP INDEX `idx_name`, DROP INDEX `idx_age`, DROP INDEX `idx_sex`;
 
ALTER TABLE user ADD INDEX `idx_name_age`(`name`, `age`);

1、部分索引生效的情况

我们使用name进行查询

EXPLAIN select * FROM user WHERE name = 'tom';

在这里插入图片描述

由于联合索引，根据最左匹配原则，使用到索引只有name这一列，name的字段类型是varchar(10)，允许Null，字符编码是utf8，一个字符占用3个字节，varchar为动态类型，key长度加2，key_len = 10 * 3 + 2 + 1 = 33 。

2、联合索引完全使用索引的情况

EXPLAIN select * FROM user WHERE name = '张三' AND age = 19;

在这里插入图片描述

由于联合索引，使用到（name，age）联合索引，name的字段类型是varchar(10)，允许Null，字符编码是utf8，一个字符占用3个字节，varchar为动态类型，key长度加2，key_len = 10 * 3 + 2 + 1 = 33 ，age的字段类型是int，长度为4，允许Null ，key_len = 4 + 1 = 5 。联合索引的key_len 为 key_len = 33 + 5 = 38。

ref 字段

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
- L1.id=‘1’; 1是常量 , ref = const
```
EXPLAIN SELECT * FROM L1 WHERE  L1.id='1';
```
- L2表被关联查询的时候,使用了主键索引, 而值使用的是驱动表(执行计划中靠前的表是驱动表)L1表的ID, 所以 ref = test_explain.L1.id
```
 EXPLAIN SELECT * FROM L1 LEFT JOIN L2 ON  L1.id = L2.id WHERE L1.title = 'heima01';
```

rows 字段

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；越少越好

使用like 查询,会产生全表扫描, L2中有3条记录,就需要读取3条记录进行查找

EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title LIKE '%hei%';

在这里插入图片描述

如果使用等值查询, 则可以直接找到要查询的记录,返回即可,所以只需要读取一条

EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title = 'heima03';

在这里插入图片描述

总结: 当我们需要优化一个SQL语句的时候，我们需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描；使用explain关键字可以模拟优化器执行sql语句，从而知道mysql是如何处理sql语句的,方便我们开发人员有针对性的对SQL进行优化.

表的读取顺序。（对应id）
数据读取操作的操作类型。（对应select_type）
哪些索引可以使用。（对应possible_keys）
哪些索引被实际使用。（对应key）
每张表有多少行被优化器查询。（对应rows）
评估sql的质量与效率 (对应type)

filtered 字段

它指返回结果的行占需要读到的行(rows列的值)的百分比

extra 字段

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息

准备数据

CREATE TABLE users (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    uname VARCHAR(20),
    age INT(11)
);
 
INSERT INTO users VALUES(NULL, 'lisa',10); 
INSERT INTO users VALUES(NULL, 'lisa',10);
INSERT INTO users VALUES(NULL, 'rose',11);
INSERT INTO users VALUES(NULL, 'jack', 12);
INSERT INTO users VALUES(NULL, 'sam', 13);

Using filesort
```
EXPLAIN SELECT * FROM users ORDER BY age;
```
执行结果Extra为Using filesort，这说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。

典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

filtered 它指返回结果的行占需要读到的行(rows列的值)的百分比
Using temporary
```
EXPLAIN SELECT COUNT(*),uname FROM users WHERE uid > 2  GROUP BY uname;
```
执行结果Extra为Using temporary，这说明需要建立临时表 (temporary table) 来暂存中间结果。性能消耗大, 需要创建一张临时表, 常见于group by语句中. 需配合SQL执行过程来解释, 如果group by和where索引条件不同, 那么group by中的字段需要创建临时表分组后再回到原查询表中. 如果查询条件where和group by是相同索引字段, 那么就不需要临时表.
Using where

意味着全表扫描或者在查找使用索引的情况下,但是还有查询条件不在索引字段当中.
```
EXPLAIN SELECT * FROM users WHERE age=10;
```
此语句的执行结果Extra为Using where，表示使用了where条件过滤数据

需要注意的是：
1. 返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；
2. 使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断。例如本例查询的 age 未设置索引，所以返回的type为ALL，仍有优化空间，可以建立索引优化查询。
Using index

表示直接访问索引就能够获取到所需要的数据(覆盖索引) , 不需要通过索引回表.
```
-- 为uname创建索引
alter table users add index idx_uname(uname);

EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa';
```
此句执行结果为Extra为Using index，说明sql所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。
Using join buffer

需要进行嵌套循环计算.
```
EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE sex = '0') u2 ON u1.uname = u2.uname;
```
执行结果Extra为Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算, 这里每个表都有五条记录，内外表查询的type都为ALL。

问题在于两个关联表join 使用 uname，关联字段均未建立索引，就会出现这种情况。

常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。
Using index condition

搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。

Using index condition 叫作 Index Condition Pushdown Optimization (索引下推优化)。

Index Condition Pushdown (ICP)是MySQL使用索引从表中检索行的一种优化。如果没有ICP，存储引擎将遍历索引以定位表中的行，并将它们返回给MySQL服务器，服务器将判断行的WHERE条件。在启用ICP的情况下，如果可以只使用索引中的列来计算WHERE条件的一部分，MySQL服务器就会将WHERE条件的这一部分推到存储引擎中。然后，存储引擎通过使用索引条目来评估推入的索引条件，只有当满足该条件时，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
```
explain select * from L5 where c > 10 and d = ''; 
```

索引下推

Using index condition 叫作 Index Condition Pushdown Optimization (索引下推优化)。

Index Condition Pushdown (ICP)是MySQL使用索引从表中检索行的一种优化。如果没有ICP，存储引擎将遍历索引以定位表中的行，并将它们返回给MySQL服务器，服务器将判断行的WHERE条件。在启用ICP的情况下，如果可以只使用索引中的列来计算WHERE条件的一部分，MySQL服务器就会将WHERE条件的这一部分推到存储引擎中。然后，存储引擎通过使用索引条目来评估推入的索引条件，只有当满足该条件时，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

存储引擎读取索引记录；
根据索引中的主键值，定位并读取完整的行记录；
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

存储引擎读取索引记录（不是完整的行记录）；
判断条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
存储引擎把记录交给层，层检测该记录是否满足条件的其余部分。

索引下推的具体实践

使用一张用户表tuser，表里创建联合索引（name, age）。

在这里插入图片描述

如果现在有一个需求：检索出表中名字第一个字是张，而且年龄是10岁的所有用户。那么，SQL语句是这么写的：

select * from tuser where name like '张%' and age=10;

在这里插入图片描述

那接下来的步骤是什么呢？

没有使用ICP

在MySQL 5.6之前，存储引擎根据通过联合索引找到name likelike '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。

在这里插入图片描述

可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。

使用ICP

而MySQL 5.6 以后，存储引擎根据（name，age）联合索引，找到，由于联合索引中包含列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

在这里插入图片描述

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

Extra主要指标的含义(有时会同时出现)

using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：索引下推优化
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

索引优化整合案例实现

JOIN优化

JOIN算法原理

1) JOIN回顾

JOIN 是 MySQL 用来进行联表操作的，用来匹配两个表的数据，筛选并合并出符合我们要求的结果集。

JOIN 操作有多种方式，取决于最终数据的合并效果。常用连接方式的有以下几种:

在这里插入图片描述

2) 驱动表的定义

什么是驱动表 ?

多表关联查询时,第一个被处理的表就是驱动表,使用驱动表去关联其他表.
驱动表的确定非常的关键,会直接影响多表关联的顺序,也决定后续关联查询的性能

驱动表的选择要遵循一个规则:

在对最终的结果集没有影响的前提下,优先选择结果集最小的那张表作为驱动表

说明：user表为大表（100万条数据），user2为小表（1000条数据），两个表结构一致，都只含有一个索引，即主键(id)索引

-- 创建表user+插入数据（100万条）
create table user(id bigint not null primary key auto_increment, 
    name varchar(20) not null default '' comment '姓名', 
    age tinyint not null default 0 comment 'age', 
    gender char(1) not null default 'M'  comment '性别',
    phone varchar(16) not null default '' comment '手机号',
    create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
    ) engine = InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户信息表';

CREATE PROCEDURE insert_user_data(num INTEGER) 
BEGIN
DECLARE v_i int unsigned DEFAULT 0;
set autocommit= 0;
WHILE v_i < num DO
insert into user(`name`, age, gender, phone) values (CONCAT('lyn',v_i), mod(v_i,120), 'M', CONCAT('152',ROUND(RAND(1)*100000000)));
SET v_i = v_i+1;
END WHILE;
commit;
END

call insert_user_data(1000000);

-- 创建表user2+插入数据（1000条）
create table user2 select * from user where 1=2;-- 复制表，仅复制表结构（不会创建自增主键，索引，需手工创建）
ALTER TABLE `user2` ADD PRIMARY KEY ( `id` ) ;-- 创建主键索引

CREATE PROCEDURE insert_user2_data(num INTEGER) 
BEGIN
DECLARE v_i int unsigned DEFAULT 0;
set autocommit= 0;
WHILE v_i < num DO
insert into user2(`name`, age, gender, phone) values (CONCAT('lyn',v_i), mod(v_i,120), 'M', CONCAT('152',ROUND(RAND(1)*100000000)));
SET v_i = v_i+1;
END WHILE;
commit;
END

call insert_user2_data(1000);

测试

说明：下面测试按join的连接字段是否为索引列分2种情况测试，先测试大表join小表，再测试小表join大表，分别执行2次，注释中记录了2次的查询时间

-- join的连接字段为索引列
SELECT * from user u LEFT JOIN user2 u2 on u.id = u2.id;-- 3.681s 3.770s 3.650s
SELECT * from user2 u2 LEFT JOIN user u on u.id = u2.id;-- 0.002s 0.002s 0.003s

-- join的连接字段为非索引列
SELECT * from user u LEFT JOIN user2 u2 on u.name = u2.name;-- 124.450s 139.875s 142.904s
SELECT * from user2 u2 LEFT JOIN user u on u.name = u2.name;-- 140.093s 142.917s 139.737s

通过上述测试结果发现：1.join的连接字段为索引列比非索引列快了十条街；2.在join的连接字段为索引列时，小表join大表比大表join小表快了十条街，在join的连接字段为非索引列时，小表join大表与大表join小表的查询速度似乎差不多。这足以验证第一节的join优化结论。

3) 三种JOIN算法

1.Simple Nested-Loop Join（简单的嵌套循环连接 )

简单来说嵌套循环连接算法就是一个双层for 循环，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果.
这种算法是最简单的方案，性能也一般。对内循环没优化，在5.6之前如果join字段为非索引字段，会采用这种join算法。。

例如有这样一条SQL:

-- 连接用户表与订单表 连接条件是 u.id = o.user_id
select * from user t1 left join order t2 on t1.id = t2.user_id;
-- user表为驱动表,order表为被驱动表

转换成代码执行时的思路是这样的:

for(user表行 uRow : user表){
    for(Order表的行 oRow : order表){
        if(uRow.id = oRow.user_id){
            return uRow;
        }
    }
}

匹配过程如下图
SNL 的特点
- 简单粗暴容易理解，就是通过双层循环比较数据来获得结果
- 查询效率会非常慢,假设 A 表有 N 行，B 表有 M 行。SNL 的开销如下：
  - A 表扫描 1 次。
  - B 表扫描 M 次。
  - 一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N * M 次

2) Index Nested-Loop Join（索引嵌套循环连接）

Index Nested-Loop Join 其优化的思路: 主要是为了减少内层表数据的匹配次数 , 最大的区别在于，用来进行 join 的字段已经在被驱动表中建立了索引。
从原来的 匹配次数 = 外层表行数 * 内层表行数 , 变成了 匹配次数 = 外层表的行数 * 内层表索引的高度 ，极大的提升了 join的性能。
当 order 表的 user_id 为索引的时候执行过程会如下图：

注意：使用Index Nested-Loop Join 算法的前提是匹配的字段必须建立了索引。

3) Block Nested-Loop Join( 块嵌套循环连接 )

如果 join 的字段有索引，MySQL 会使用 INL 算法。如果没有的话，MySQL 会如何处理？
因为不存在索引了，所以被驱动表需要进行扫描。这里 MySQL 并不会简单粗暴的应用 SNL 算法，而是加入了 buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。
- 在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进行 join 用到的列都缓存到 buffer 中。buffer 中的数据有一个特点，里面的记录不需要一条一条地取出来和 order 表进行比较，而是整个 buffer 和 order表进行批量比较。
- 如果我们把 buffer 的空间开得很大，可以容纳下 user 表的所有记录，那么 order 表也只需要访问一次。
- MySQL 默认 buffer 大小 256K，如果有 n 个 join 操作，会生成 n-1 个 join buffer。
```
mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

mysql> set session join_buffer_size=262144;
Query OK, 0 rows affected (0.00 sec)
```

4) 总结

永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)
为匹配的条件增加索引(减少内层表的循环匹配次数)
增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

1、为什么join的连接字段使用索引字段比使用非索引字段快？ 因为采用了Index Nested-Loop Join算法，极大的减少了内层表的匹配次数。

2、为什么小表join大表比大表join小表快？ 这里先讨论Join字段为索引字段的情况，因为小表join大表更能显著地减少外层驱动表的循环次数，比如在第2节的举例，外层驱动表为100万条数据，内层表为1000条数据。如果外层驱动表为大表，即使采用Block Nested-Loop Join算法，因为join buffer的大小总是有限的，最终外层驱动表还是需要接近10万次循环；而用小表join大表的话，外层驱动表仅用了1000次左右的循环，再加上join字段为索引字段，用到了Index Nested-Loop Join算法，又极大的减少了内层大表的循环次数，所以join字段为索引字段+小表join大表结合起来的查询速度非常快。

3、为什么当join的连接字段为非索引字段时，大表Join小表与小表join大表的速度差不多？ 因为虽然说把小表作为驱动表能极大减少外层循环的次数，但是内层表为大表，由于连接字段为非索引字段，不能用Index Nested-Loop Join算法减少内层循环的次数，所以当join的连接字段为非索引字段时，两种形式的区别不大。

in和exists函数

上面我们说了小表驱动大表,就是小的数据集驱动大的数据集, 主要是为了减少数据库的连接次数,根据具体情况的不同,又出现了两个函数 exists 和 in 函数

创建部门表与员工表,并插入数据

-- 部门表
CREATE TABLE department (
  id INT(11) PRIMARY KEY,
  deptName VARCHAR(30) ,
  address VARCHAR(40) 
) ;

-- 部门表测试数据
INSERT INTO `department` VALUES (1, '研发部', '1层');
INSERT INTO `department` VALUES (2, '人事部', '3层');
INSERT INTO `department` VALUES (3, '市场部', '4层');
INSERT INTO `department` VALUES (5, '财务部', '2层');

-- 员工表
CREATE TABLE employee (
  id INT(11) PRIMARY KEY,
  NAME VARCHAR(20) ,
  dep_id INT(11) ,
  age INT(11) ,
  salary DECIMAL(10, 2)
);

-- 员工表测试数据
INSERT INTO `employee` VALUES (1, '鲁班', 1, 15, 1000.00);
INSERT INTO `employee` VALUES (2, '后裔', 1, 22, 2000.00)
INSERT INTO `employee` VALUES (4, '阿凯', 2, 20, 3000.00);
INSERT INTO `employee` VALUES (5, '露娜', 2, 30, 3500.00);
INSERT INTO `employee` VALUES (6, '李白', 3, 25, 5000.00);
INSERT INTO `employee` VALUES (7, '韩信', 3, 50, 5000.00);
INSERT INTO `employee` VALUES (8, '蔡文姬', 3, 35, 4000.00);
INSERT INTO `employee` VALUES (3, '孙尚香', 4, 20, 2500.00);

1) in 函数

假设: department表的数据小于 employee表数据, 将所有部门下的员工都查出来,应该使用 in 函数

-- 编写SQL,使in 函数
SELECT * FROM employee e WHERE e.dep_id IN (SELECT id FROM department);

in函数的执行原理

in 语句, 只执行一次, 将 department 表中的所有id字段查询出来并且缓存.
检查 department 表中的id与 employee 表中的 dep_id 是否相等, 如果相等添加到结果集, 直到遍历完department 所有的记录.

在这里插入图片描述

-- 先循环: select id from department; 相当于得到了小表的数据
for(i = 0; i < $dept.length; i++){  -- 小表
    -- 后循环: select * from employee where e.dep_id  = d.id;
    for(j = 0 ; j < $emp.legth; j++){  -- 大表
    
        if($dept[i].id == $emp[j].dep_id){
            $result[i] = $emp[j]
            break;
        }
    }
}

结论: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in

2) exists 函数

假设: department表的数据大于 employee表数据, 将所有部门下的的员工都查出来,应该使用 exists 函数.
```
explain SELECT * FROM employee e WHERE EXISTS 
(SELECT id FROM department d WHERE d.id = e.dep_id);
```
exists 特点

exists 子句返回的是一个布尔值，如果有返回数据，则返回值是true，反之是false。

如果结果为 true , 外层的查询语句会进行匹配,否则外层查询语句将不进行查询或者查不出任何记录。

exists 函数的执行原理

-- 先循环: SELECT * FROM employee e;
-- 再判断: SELECT id FROM department d WHERE d.id = e.dep_id

for(j = 0; j < $emp.length; j++){  -- 小表

-- 遍历循环外表，检查外表中的记录有没有和内表的的数据一致的, 匹配得上就放入结果集。
    if(exists(emp[i].dep_id)){   -- 大表
        $result[i] = $emp[i];
    }
}

3) in 和 exists 的区别

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in
如果主查询得出的结果集记录较少，子查询中的表较大且又有索引时应该用 exists
一句话: in后面跟的是小表，exists后面跟的是大表。

order by优化

MySQL中的两种排序方式

索引排序: 通过有序索引顺序扫描直接返回有序数据
额外排序: 对返回的数据进行文件排序
ORDER BY优化的核心原则: 尽量减少额外的排序，通过索引直接返回有序数据。

1.索引排序

因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN分析查询时，Extra显示为Using index。

在这里插入图片描述

比如查询条件是 where age = 21 order by name，那么查询过程就是会找到满足 age = 21 的记录，而符合这条的所有记录一定是按照 name 排序的，所以也不需要额外进行排序.

2.额外排序

所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。EXPLAIN分析查询时，Extra显示为Using filesort。

1) 按执行位置划分

Sort_Buffer MySQL 为每个线程各维护了一块内存区域 sort_buffer ，用于进行排序。sort_buffer 的大小可以通过 sort_buffer_size 来设置。

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| sort_buffer_size        | 262144  |
+-------------------------+---------+

mysql> select 262144 / 1024;
+---------------+
| 262144 / 1024 |
+---------------+
|      256.0000 |
+---------------+

注: sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。

Sort_Buffer + 临时文件

如果加载的记录字段总长度（可能是全字段也可能是 rowid排序的字段）小于 sort_buffer_size 便使用 sort_buffer 排序；如果超过则使用 sort_buffer + 临时文件进行排序。

临时文件种类：

临时表种类由参数 tmp_table_size 与临时表大小决定，如果内存临时表大小超过 tmp_table_size ，那么就会转成磁盘临时表。因为磁盘临时表在磁盘上，所以使用内存临时表的效率是大于磁盘临时表的。

2) 按执行方式划分

执行方式是由 max_length_for_sort_data 参数与用于排序的单条记录字段长度决定的，如果用于排序的单条记录字段长度 <= max_length_for_sort_data ，就使用全字段排序；反之则使用 rowid 排序。

mysql> show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+

2.1) 全字段排序

全字段排序就是将查询的所有字段全部加载进来进行排序。

优点：查询快，执行过程简单缺点：需要的空间大。

select name,age,add from user where addr = '北京' order by name limit 1000; -- addr有索引

在这里插入图片描述

上面查询语句的执行流程:

初始化 sort_buffer，确定放入 name、age、addr 这3个字段。
从索引 addr 中找到第一个满足 addr=’北京’ 的主键ID（ID_x）。
到主键索引中找到 ID_x，取出整行，取 name、addr、age 3个字段的值，存入 sort_buffer。
从索引 addr 取下一个记录的主键ID。
重复3、4，直到 addr 值不满足条件。
对 sort_buffer 中的数据按照 name 做快速排序。
把排序结果中的前1000行返回给客户端。

2.2) rowid排序

rowid 排序相对于全字段排序，不会把所有字段都放入sort_buffer。所以在sort buffer中进行排序之后还得回表查询。

缺点：会产生更多次数的回表查询，查询可能会慢一些。

优点：所需的空间更小

select name,age,addr from user where addr = '北京' order by name limit 1000; -- addr有索引

假设 name、age、addr3个字段定义的总长度为36，而 max_length_for_sort_data = 16，就是单行的长度超了，MySQL认为单行太大，需要换一个算法。放入 sort_buffer 的字段就会只有要排序的字段 name，和主键 id，那么排序的结果中就少了 addr 和 age，就需要回表了。

在这里插入图片描述

上面查询语句的执行流程:

初始化 sort_buffer，确定放入2个字段，name 和 id。
从索引 addr 中找到第一个满足addr=’北京’的主键ID（ID_x）。
到主键索引中取出整行，把 name、id 这2个字段放入 sort_buffer。
从索引 addr 取下一个记录的主键ID。
重复3、4，直到addr值不满足条件。
对 sort_buffer 中的数据按照 name 做快速排序。
取排序结果中的前1000行，并按照 id 的值到原表中取出 name、age、addr 3个字段的值返回给客户端。

总结

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

3.排序优化

添加索引

为 employee 表创建索引

-- 联合索引
ALTER TABLE employee ADD INDEX idx_name_age(NAME,age);

-- 为薪资字段添加索引
ALTER TABLE employee ADD INDEX idx_salary(salary);

查看 employee 表的索引情况
```
SHOW INDEX FROM employee; 
```

场景1: 只查询用于排序的索引字段, 可以利用索引进行排序,最左原则

查询 name, age 两个字段, 并使用 name 与 age 行排序

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name,e.age;

在这里插入图片描述

场景2: 排序字段在多个索引中,无法使用索引排序

查询 name , salary 字段, 并使用 name 与 salary 排序

EXPLAIN SELECT e.name, e.salary FROM employee e ORDER BY e.name,e.salary;

在这里插入图片描述

场景3: 只查询用于排序的索引字段和主键, 可以利用索引进行排序

查询 id , name , 使用 name 排序

EXPLAIN SELECT e.id, e.name FROM employee e ORDER BY e.name;

在这里插入图片描述

场景4: 查询主键之外的没有添加索引的字段，不会利用索引排序

查询 dep_id ,使用 name 进行排序

EXPLAIN SELECT e.dep_id FROM employee e ORDER BY e.name;

在这里插入图片描述

场景5: 排序字段顺序与索引列顺序不一致,无法利用索引排序

使用联合索引时, ORDER BY子句也要求, 排序字段顺序和联合索引列顺序匹配。
```
EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.age,e.name;
```

场景6: where 条件是范围查询时, 会使order by 索引失效

比如添加一个条件 : age > 18 ,然后再根据 age 排序.

EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age > 10 ORDER BY e.age;

在这里插入图片描述

注意: ORDERBY子句不要求必须索引中第一列,没有仍然可以利用索引排序。但是有个前提条件，只有在等值过滤时才可以，范围查询时不
```
EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age = 18 ORDER BY e.age;
```

在这里插入图片描述

场景7: 升降序不一致,无法利用索引排序

ORDER BY排序字段要么全部正序排序，要么全部倒序排序，否则无法利用索引排序。

-- 升序
EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name , e.age ;

-- 降序
EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name DESC, e.age DESC;

在这里插入图片描述

name字段升序,age字段降序,索引失效

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name, e.age DESC;

在这里插入图片描述

索引优化原则&失效情况

创建表插入数据

CREATE TABLE users(
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL COMMENT '姓名',
  user_age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  user_level VARCHAR(20) NOT NULL COMMENT '用户等级',
  reg_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
);

INSERT INTO users(user_name,user_age,user_level,reg_time)
VALUES('tom',17,'A',NOW()),('jack',18,'B',NOW()),('lucy',18,'C',NOW());

创建联合索引

ALTER TABLE users ADD INDEX idx_nal (user_name,user_age,user_level) USING BTREE;

1. 全值匹配

按索引字段顺序匹配使用。

EXPLAIN SELECT * FROM users WHERE user_name = 'tom';

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17 
AND user_level = 'A';

按顺序使用联合索引时, type类型都是 ref ,使用到了索引效率比较高

在这里插入图片描述

2. 最佳左前缀法则

如果创建的是联合索引,就要遵循 最佳左前缀法则: 使用索引时，where后面的条件需要从索引的最左前列开始并且不跳过索引中的列使用。

场景1: 按照索引字段顺序使用，三个字段都使用了索引,没有问题。

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' 
AND user_age = 17 AND user_level = 'A';

在这里插入图片描述

场景2: 直接跳过user_name使用索引字段，索引无效，未使用到索引。
```
EXPLAIN SELECT * FROM users WHERE user_age = 17 AND user_level = 'A';
```
场景3: 不按照创建联合索引的顺序,使用索引
```
EXPLAIN SELECT * FROM users WHERE 
user_age = 17 AND user_name = 'tom' AND user_level = 'A';
```
where后面查询条件顺序是 user_age、user_level、user_name与我们建的索引顺序user_name、user_age、user_level不一致，为什么还是使用了索引，这是因为MySql底层优化器给咱们做了优化。

但是，最好还是要按照顺序使用索引。

最佳左前缀底层原理

MySQL创建联合索引的规则是: 首先会对联合索引最左边的字段进行排序 ( 例子中是 user_name ), 在第一个字段的基础之上 再对第二个字段进行排序 ( 例子中是 user_age )

所以: 最佳左前缀原则其实是个B+树的结构有关系, 最左字段肯定是有序的, 第二个字段则是无序的(联合索引的排序方式是: 先按照第一个字段进行排序,如果第一个字段相等再根据第二个字段排序). 所以如果直接使用第二个字段 user_age 通常是使用不到索引的.

在这里插入图片描述

3. 不要在索引列上做任何计算

不要在索引列上做任何操作，比如计算、使用函数、自动或手动进行类型转换,会导致索引失效，从而使查询转向全表扫描。

插入数据

INSERT INTO users(user_name,user_age,user_level,reg_time) VALUES('11223344',22,'D',NOW());

场景1: 使用系统函数 left()函数
```
EXPLAIN SELECT * FROM users WHERE LEFT(user_name, 6) = '112233';
```
where条件使用计算后的索引字段 user_name，没有使用索引，索引失效。

场景2: 字符串不加单引号 (隐式类型转换)

EXPLAIN SELECT * FROM users WHERE user_name = 11223344;

在这里插入图片描述

注: Extra = Using where 表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；
( 需要回表去查询所需的数据 )

4. 范围之后全失效

存储引擎不能使用索引中范围条件右边的列

场景1: 条件单独使用user_name时, type=ref, key_len=82

-- 条件只有一个 user_name
EXPLAIN SELECT * FROM users WHERE user_name = 'tom';

在这里插入图片描述

场景2: 条件增加一个 user_age ( 使用常量等值) ,type= ref , key_len = 86

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17;

在这里插入图片描述

场景3: 使用全值匹配, type = ref , key_len = 168 , 索引都利用上了.

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' 
AND user_age = 17 AND user_level = 'A';

在这里插入图片描述

场景4: 使用范围条件时, avg > 17 , type = range , key_len = 86 , 与场景3 比较,可以发现 user_level 索引没有用上.
```
EXPLAIN SELECT * FROM users WHERE user_name = 'tom' 
AND user_age > 17 AND user_level = 'A';
```

5. 尽量使用覆盖索引

尽量使用覆盖索引（查询列和索引列尽量一致，通俗说就是对A、B列创建了索引，然后查询中也使用A、B列），减少select *的使用。

场景1: 全值匹配查询, 使用 select *

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17 
AND user_level = 'A';

在这里插入图片描述

场景1: 全值匹配查询, 使用 select 字段名1 ,字段名2
```
EXPLAIN SELECT user_name , user_age , user_level FROM users WHERE user_name = 'tom' 
AND user_age = 17 AND user_level = 'A';
```
使用覆盖索引（查询列与条件列对应），可看到Extra从Null变成了Using index，提高检索效率。

注: Using index 表示使用到了索引 , 并且所取的数据完全在索引中就能拿到

(使用覆盖索引的时候就会出现)

6. 使用不等于（!=或<>）会使索引失效

使用 != 会使type=ALL，key=Null，导致全表扫描，并且索引失效。

使用 !=

EXPLAIN SELECT * FROM users WHERE user_name != 'tom';

在这里插入图片描述

7. is null 或 is not null也无法使用索引

在使用is null的时候，索引完全失效，使用is not null的时候，type=ALL全表扫描，key=Null索引失效。

场景1: 使用 is null

EXPLAIN SELECT * FROM users WHERE user_name IS NULL;

场景2: 使用 not null

EXPLAIN SELECT * FROM users WHERE user_name IS NOT NULL;

在这里插入图片描述

8. like通配符以%开头会使索引失效

like查询为范围查询，%出现在左边，则索引失效。%出现在右边索引未失效。口诀：like百分加右边。

场景1

EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom%';

在这里插入图片描述

场景2

EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom';

在这里插入图片描述

场景3

EXPLAIN SELECT * FROM users WHERE user_name LIKE 'tom%';

在这里插入图片描述

注: Using index condition 表示查找使用了索引，但是需要;';查询数据

解决%出现在左边索引失效的方法：使用覆盖索引。

Case1:

EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack%';

在这里插入图片描述

对比场景1可以知道, 通过使用覆盖索引 type = index,并且使用了 Using index,从全表扫描变成了全索引扫描.

注: Useing where; Using index; 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

Case2:

EXPLAIN SELECT id FROM users WHERE user_name LIKE '%jack%';

在这里插入图片描述

这里出现 type=index，因为主键自动创建唯一索引。

Case3:

EXPLAIN SELECT user_name,user_age FROM users WHERE user_name LIKE '%jack%';
EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name LIKE '%jack%';
EXPLAIN SELECT id,user_name,user_age,user_level FROM users WHERE user_name LIKE '%jack%';

在这里插入图片描述

上面三组, explain执行的结果都相同，表明都使用了索引.

Case4:

EXPLAIN SELECT id,user_name,user_age,user_level,reg_time FROM users WHERE user_name 
LIKE '%jack%';

在这里插入图片描述

分析：由于只在（user_name,user_age,user_level）上创建索引，当包含reg_time时，导致结果集偏大（reg_time未建索引）【锅大，锅盖小，不能匹配】，所以type=ALL。
like 失效的原理

在这里插入图片描述

%号在右: 由于B+树的索引顺序，是按照首字母的大小进行排序，%号在右的匹配又是匹配首字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。所以有些时候可以用到索引.
%号在左: 是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的，所以不能按照索引顺序查询，就用不到索引.
两个%%号: 这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的.

9. 字符串不加单引号导致索引失效

varchar类型的字段，在查询的时候不加单引号导致索引失效，转向全表扫描。

场景1

SELECT * FROM users WHERE user_name = '123';
SELECT * FROM users WHERE user_name = 123;

上述两条sql语句都能查询出相同的数据。

在这里插入图片描述

场景2:

通过explain执行结果可以看出，字符串（name）不加单引号在查询的时候，导致索引失效（type=ref变成了type=ALL，并且key=Null），并全表扫描。

10. 少用or，用or连接会使索引失效

在使用or连接的时候 type=ALL，key=Null，索引失效，并全表扫描。

在这里插入图片描述

EzrealYi

关注

24
点赞
踩
29

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL调优

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化SQL，更重要的是得先找到需要优化的SQL语句。MySQL数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL，这将极大程度帮助我们快速定位到问题所在，以便对症下药。
复制链接

扫一扫