MySQL索引优化分析3—单表查询优化

最新推荐文章于 2023-04-27 13:35:42 发布

原创

最新推荐文章于 2023-04-27 13:35:42 发布 · 782 阅读

2 ·

CC 4.0 BY-SA版权

本文详细分析了MySQL单表查询时的索引优化，包括数据准备、索引失效的各种情况，如全值匹配、最左前缀法则、函数操作、范围查询等，并给出了避免索引失效的一般性建议，旨在提升SQL查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL索引优化分析3——SQL查询优化之单表查询

1.数据准备
2.单表使用索引及常见索引失效
3.单表查询索引优化的一般性建议

当在一个表中使用索引时，其目的是为了提高SQL查询效率，前面两节也分别对Mysql数据库索引以及SQL查询的执行计划进行了介绍。问题是，SQL查询语句是如何使用表中索引来提高查询效率的？

本节将通过对一些不同索引设置及对应的不同SQL查询语句的SQL执行情况进行对比分析，来详细介绍SQL查询语句的索引优化。

1.数据准备

首先是实验案例的数据准备。通过批量数据脚本，创建dept 与emp 两个数据表，分别插入10万与50万条数据。

1.1 建表

首先创建一个部门表dept，一个员工表emp，相关字段、属性、数据库设置如下：

 CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 ceo INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `empno` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.2 设置参数log_bin_trust_function_creators

如果创建函数时，假如报错：This function has none of DETERMINISTIC…

是由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：

windows下my.ini[mysqld]加上：

log_bin_trust_function_creators=1

linux下 /etc/my.cnf 的 [mysqld]加上：

log_bin_trust_function_creators=1

1.3 创建SQL函数,保证每条数据都不同

为了向表中批量插入随机的数据，创建两个SQL函数，用于插入数据时使用。

1.随机产生字符串函数

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO  
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52