sql优化-单表优化

小丁学Java

已于 2024-07-04 09:18:56 修改

阅读量1.3k

点赞数 43

分类专栏： MySQL高级篇文章标签： sql 数据库单表优化

于 2024-07-04 09:05:00 首次发布

本文链接：https://blog.csdn.net/m0_65152767/article/details/140162447

版权

MySQL高级篇专栏收录该内容

46 篇文章 0 订阅

订阅专栏

文章目录

0、索引优化原则
1、在docker内部连接mysql
2、数据准备
3、创建表 dept 和 emp
4、插入50万数据到 emp 表中
5、查找姓名以"abc"开头的员工信息
6、如果员工的姓名的前三位是abc 那么他就满足要求
7、查找姓名含有"abc"的员工信息
8、查找年龄不等于25的员工
9、查找姓名不为空的员工信息
10、查找姓名等于"123"的员工信息
11、mp通过querywrapper生成的动态sql 如果传入的参数类型和数据库字段类型不一致是否会导致索引失效？

0、索引优化原则

对索引列进行计算、函数处理、类型转换都会导致索引失效，转向全表扫描
like模糊查询时，以通配符开始（_、%）会导致索引失效，变成全表扫描
不等于！=、不等于<>、is not null、not exists 会导致索引失效
字符串不加单引号索引失效
sql优化：对条件字段创建索引

1、在docker内部连接mysql

[root@localhost ~]# docker exec -it spzx-mysql /bin/bash
root@ab66508d9441:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 720
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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>

2、数据准备

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;

3、创建表 dept 和 emp

mysql> 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;
Query OK, 0 rows affected, 2 warnings (1.16 sec)

mysql> 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;
Query OK, 0 rows affected, 4 warnings (0.24 sec)

4、插入50万数据到 emp 表中

4.1、创建函数

set global log_bin_trust_function_creators=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),1));  
		SET i = i + 1;
	END WHILE;
	RETURN return_str;
END $$

#用于随机产生区间数字
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(from_num +RAND()*(to_num -from_num+1));
RETURN i;  
END$$

#假如要删除
#drop function rand_string;
#drop function rand_num;

4.2、存储过程

# 插入员工存储过程
DELIMITER $$
CREATE PROCEDURE  insert_emp(START INT, max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	#set autocommit =0 把autocommit设置成0  
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO emp (empno, NAME, age, deptid ) VALUES ((START+i) ,rand_string(6), rand_num(30,50), rand_num(1,10000));  
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

 
#往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`(max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

4.3、调用存储过程

#执行存储过程，往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000); 

#执行存储过程，往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100000,500000);

5、查找姓名以"abc"开头的员工信息

mysql> select * from emp where name like 'abc%';
+--------+--------+--------+------+--------+
| id     | empno  | name   | age  | deptId |
+--------+--------+--------+------+--------+
|   8186 | 108186 | abckRF |   40 |   7162 |
|  60377 | 160377 | aBClvt |   44 |   2887 |
| 101035 | 201035 | abciJX |   45 |   7511 |
| 102248 | 202248 | ABcJmh |   30 |   6740 |
| 116003 | 216003 | ABcLxd |   50 |   7498 |
| 143871 | 243871 | aBCktn |   33 |     97 |
| 148663 | 248663 | AbCLVx |   40 |    691 |
| 155165 | 255165 | aBCjmJ |   43 |   2861 |
| 182915 | 282915 | aBCjnN |   31 |   9787 |
| 196153 | 296153 | abclXg |   45 |   2800 |
| 212956 | 312956 | AbCLWA |   46 |   3406 |
| 253174 | 353174 | AbCKSj |   36 |    660 |
| 323480 | 423480 | aBClxC |   38 |   3357 |
| 332790 | 432790 | ABcIjU |   49 |   9672 |
| 342022 | 442022 | AbCIHn |   37 |   9104 |
| 346928 | 446928 | ABcKtO |   45 |   3330 |
| 352803 | 452803 | AbCLVw |   38 |   7207 |
| 353917 | 453917 | abclUS |   42 |   3634 |
| 383781 | 483781 | ABcKtO |   46 |   5384 |
| 388356 | 488356 | ABcIjU |   49 |    541 |
| 389827 | 489827 | aBCjmH |   40 |   6501 |
| 435658 | 535658 | ABcKsH |   34 |    435 |
| 440021 | 540021 | ABcJor |   47 |   1907 |
| 449413 | 549413 | abckQB |   34 |   4615 |
| 496441 | 596441 | aBClwC |   38 |   3105 |
+--------+--------+--------+------+--------+
25 rows in set (0.17 sec)

花费0.17秒

5.1、执行计划 select * from emp where name like ‘abc%’;

mysql> explain select * from emp where name like 'abc%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

5.2、sql优化：对条件字段name创建索引

mysql> create index idx_name on emp(name);
Query OK, 0 rows affected (4 min 31.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from emp where name like 'abc%';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | emp   | NULL       | range | idx_name      | idx_name | 63      | NULL |   25 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

5.3、使用name索引查询 select * from emp where name like ‘abc%’

mysql> select * from emp where name like 'abc%';
+--------+--------+--------+------+--------+
| id     | empno  | name   | age  | deptId |
+--------+--------+--------+------+--------+
| 342022 | 442022 | AbCIHn |   37 |   9104 |
| 332790 | 432790 | ABcIjU |   49 |   9672 |
| 388356 | 488356 | ABcIjU |   49 |    541 |
| 101035 | 201035 | abciJX |   45 |   7511 |
| 102248 | 202248 | ABcJmh |   30 |   6740 |
| 389827 | 489827 | aBCjmH |   40 |   6501 |
| 155165 | 255165 | aBCjmJ |   43 |   2861 |
| 182915 | 282915 | aBCjnN |   31 |   9787 |
| 440021 | 540021 | ABcJor |   47 |   1907 |
| 449413 | 549413 | abckQB |   34 |   4615 |
|   8186 | 108186 | abckRF |   40 |   7162 |
| 435658 | 535658 | ABcKsH |   34 |    435 |
| 253174 | 353174 | AbCKSj |   36 |    660 |
| 143871 | 243871 | aBCktn |   33 |     97 |
| 346928 | 446928 | ABcKtO |   45 |   3330 |
| 383781 | 483781 | ABcKtO |   46 |   5384 |
| 353917 | 453917 | abclUS |   42 |   3634 |
|  60377 | 160377 | aBClvt |   44 |   2887 |
| 352803 | 452803 | AbCLVw |   38 |   7207 |
| 148663 | 248663 | AbCLVx |   40 |    691 |
| 212956 | 312956 | AbCLWA |   46 |   3406 |
| 496441 | 596441 | aBClwC |   38 |   3105 |
| 323480 | 423480 | aBClxC |   38 |   3357 |
| 116003 | 216003 | ABcLxd |   50 |   7498 |
| 196153 | 296153 | abclXg |   45 |   2800 |
+--------+--------+--------+------+--------+
25 rows in set (0.00 sec)

name加索引耗时0秒，name不加索引耗时0.17秒

6、如果员工的姓名的前三位是abc 那么他就满足要求

mysql> select * from emp where left(name,3) = 'abc';
+--------+--------+--------+------+--------+
| id     | empno  | name   | age  | deptId |
+--------+--------+--------+------+--------+
|   8186 | 108186 | abckRF |   40 |   7162 |
|  60377 | 160377 | aBClvt |   44 |   2887 |
| 101035 | 201035 | abciJX |   45 |   7511 |
| 102248 | 202248 | ABcJmh |   30 |   6740 |
| 116003 | 216003 | ABcLxd |   50 |   7498 |
| 143871 | 243871 | aBCktn |   33 |     97 |
| 148663 | 248663 | AbCLVx |   40 |    691 |
| 155165 | 255165 | aBCjmJ |   43 |   2861 |
| 182915 | 282915 | aBCjnN |   31 |   9787 |
| 196153 | 296153 | abclXg |   45 |   2800 |
| 212956 | 312956 | AbCLWA |   46 |   3406 |
| 253174 | 353174 | AbCKSj |   36 |    660 |
| 323480 | 423480 | aBClxC |   38 |   3357 |
| 332790 | 432790 | ABcIjU |   49 |   9672 |
| 342022 | 442022 | AbCIHn |   37 |   9104 |
| 346928 | 446928 | ABcKtO |   45 |   3330 |
| 352803 | 452803 | AbCLVw |   38 |   7207 |
| 353917 | 453917 | abclUS |   42 |   3634 |
| 383781 | 483781 | ABcKtO |   46 |   5384 |
| 388356 | 488356 | ABcIjU |   49 |    541 |
| 389827 | 489827 | aBCjmH |   40 |   6501 |
| 435658 | 535658 | ABcKsH |   34 |    435 |
| 440021 | 540021 | ABcJor |   47 |   1907 |
| 449413 | 549413 | abckQB |   34 |   4615 |
| 496441 | 596441 | aBClwC |   38 |   3105 |
+--------+--------+--------+------+--------+
25 rows in set (0.19 sec)

mysql> explain select * from emp where left(name,3) = 'abc';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

left(name,3)函数被用来获取name字段值的最左边（也就是开头）的3个字符，然后这个结果被与字符串’abc’进行比较。只有当name字段的前3个字符恰好是’abc’时，相应的记录才会被选中并返回。
left函数：这是一个字符串函数，用于从字符串的左边开始提取指定数量的字符。在这个例子中，它从name字段的每个值中提取前3个字符

对索引列进行计算、函数处理、类型转换都会导致索引失效

索引列上的函数操作：在查询条件中对索引列使用了LEFT函数，这可能导致索引无法被有效利用。优化方法是尽量避免在索引列上进行函数操作，或者考虑创建一个函数索引来覆盖这种查询。

7、查找姓名含有"abc"的员工信息

mysql> explain select * from emp where name like '_abc%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from emp where name like '%abc%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

like模糊查询时，以通配符（_、%）开始会导致索引失效

8、查找年龄不等于25的员工

8.1、执行计划 elect * from emp where age!=25

mysql> explain select * from emp where age!=25;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |    90.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

8.2、sql优化：对条件字段age创建索引

mysql> create index idx_age on emp(age);
Query OK, 0 rows affected (1.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY  |            1 | id          | A         |      499086 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | idx_name |            1 | name        | A         |      338680 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | idx_age  |            1 | age         | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.07 sec)

8.3、再次执行计划 elect * from emp where age!=25

mysql> explain select * from emp where age!=25;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_age       | NULL | NULL    | NULL | 499086 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

还是没有走索引，只是可能会走索引idx_age
！= 会导致索引失效

9、查找姓名不为空的员工信息

mysql> explain select * from emp where name is not null;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 499086 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

is not null 会导致索引失效

10、查找姓名等于"123"的员工信息

mysql> explain select * from emp where name=123;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 499086 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

确保数据类型匹配：如果name列是字符串类型（如VARCHAR），你应该确保在查询中使用字符串类型的值。例如SELECT * FROM emp WHERE name = '123';
此时发生了类型转换

11、mp通过querywrapper生成的动态sql 如果传入的参数类型和数据库字段类型不一致是否会导致索引失效？

一般不会：设置参数时一般通过对象携带，对象参数数据库表创建。
设置条件后，querywrapper会根据实体类字段类型以preparedStatement预编译方式设置参数，保证参数类型转换成了数据库表字段类型。

小丁学Java

关注

43
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
sql优化-单表优化

被用来获取name字段值的最左边（也就是开头）的3个字符，然后这个结果被与字符串’abc’进行比较。只有当name字段的前3个字符恰好是’abc’时，相应的记录才会被选中并返回。：在查询条件中对索引列使用了LEFT函数，这可能导致索引无法被有效利用。优化方法是尽量避免在索引列上进行函数操作，或者考虑创建一个函数索引来覆盖这种查询。：这是一个字符串函数，用于从字符串的左边开始提取指定数量的字符。在这个例子中，它从name字段的每个值中提取前3个字符。
复制链接

扫一扫