有哪些常见的数据库优化方法

r囧r小猫 2024-04-05 06:48 12阅读 0赞

数据库优化这个话题很大，我从最常见的也是最有效的优化手段索引优化的角度来回答一下：

系统的性能瓶颈很多时候都出现在数据库，而数据库的性能优化最先入手之处应当是索引，通过索引的优化可以用最少的成本获得最大的性能提升。

我们来通过一个例子看看索引对数据库查询效率的影响：

先创建一个数据表：

CREATE TABLE \`tb\_user\` (  
\`id\` BIGINT (20),  
\`user\_name\` VARCHAR (200)  
  
) ENGINE=MYISAM;  
编写存储过程插入300万条记录

DELIMITER $$  
CREATE  
PROCEDURE \`pro\_tb\_user\`()  
BEGIN  
DECLARE i INT;  
SET i=0;  
WHILE i>=0 && i<= 3000000 DO  
INSERT INTO \`tb\_user\`  
(\`id\`, \`user\_name\`) VALUES  
(i,'admin');  
SET i=i + 1;  
END WHILE;  
  
END$$  
DELIMITER ;  
调用存储过程

CALL pro\_tb\_user();  
根据id查询：

SELECT \* FROM tb\_user WHERE id = 123  
查询速度很快，接近0秒，因为id是主键，会添加索引。

根据user\_name查询：

SELECT \* FROM tb\_user WHERE user\_name = 'aaab'  
耗时：0.69秒

查询速度较慢，因为user\_name上没有索引。

现在在user\_name上建立索引：

CREATE INDEX idx\_item\_name ON tb\_user(user\_name);  
再来查询一次，发现耗时接近0秒，效率提升了六十多倍，这就是索引带来的巨大效率提升。

既然索引能带来如此多的性能提升，索引如何用呢？

先准备环境：

create table \`tb\_seller\` (  
\`sellerid\` varchar (100),  
\`name\` varchar (100),  
\`nickname\` varchar (50),  
\`password\` varchar (60),  
\`status\` varchar (1),  
\`address\` varchar (100),  
\`createtime\` datetime,  
primary key(\`sellerid\`)  
)engine=innodb default charset=utf8mb4;  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');  
insert into \`tb\_seller\` (\`sellerid\`, \`name\`, \`nickname\`, \`password\`, \`status\`, \`address\`, \`createtime\`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');  
我们可以创建单列索引和组合索引，究竟优先选择哪种呢？答案是组合索引，当创建一个组合索引的时候就相当于创建了多个索引。例如：

创建name,status,address三个字段的组合索引：

CREATE INDEX idx\_seller\_name\_sta\_addr ON tb\_seller(NAME,STATUS,address);  
相当于创建了三个索引:

name  
name+status  
name+status+address  
在查询的时候数据库会选择最优索引。

如果查询的时候使用了索引则效率高，反之效率低，在什么情况下会使用索引什么情况下不会使用索引呢？我们来看看下面几种情况

查询的时候对组合索引中的每一列都指定具体的值，此种情况下会使用索引，效率高：

例如：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME='小米科技' AND STATUS='1' AND address='北京市'

![5b04b4349b1049a7aa85092114e8d810.png][]

1)最左前缀法则：

如果使用多个列创建了组合索引，则必须满足最左前缀法则才会使用索引，即查询条件中使用了组合索引靠左边的列就会使用索引，不能跳过组合索引的列。

例如：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME = '小米科技'  
这条查询语句的条件使用了name,组合索引为:“name,status,address”，属于组合索引靠左边的列。

再比如:

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME = '小米科技' AND STATUS = 1  
这条查询语句的条件使用了name和status，属于组合索引靠左边的列。

再比如：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME = '小米科技' AND STATUS = 1 AND address = '北京市'  
这条查询语句的条件使用了name,status和address属于组合索引靠左边的列。

这三条查询语句都满足最左前缀法则会使用索引。

再来看看不符合最左前缀法则的例子：

例如：

EXPLAIN SELECT \* FROM tb\_seller WHERE STATUS = 1  
这条sql语句的查询条件跳过了name,直接使用status不符合最左前缀法则

EXPLAIN SELECT \* FROM tb\_seller WHERE STATUS = 1 AND address='北京市'  
这条sql语句同样跳过了name不符合最左前缀法则

如果查询条件跳过了组合索引的某列则只有左边的索引会生效

例如：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME = '小米科技' AND address='北京市'  
只有name上的索引会生效

2)范围查询右边的列不能使用索引

例如：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME = '小米科技' AND STATUS='1' AND address = '北京市'  
这条sql语句会使用组合索引中的每一列：

![b516ef14b8bb4950a25a53ea040af5a8.png][]  
下面这条sql语句使用了范围查询：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME = '小米科技' AND STATUS>'1' AND address = '北京市'

![f67ddd53c381464daaea3ee16c3f14bc.png][]  
索引只能使用name和status索引，不能使用address索引，因此key\_len 为410

3）如果在索引列上进行运算操作索引会失效：

例如：

EXPLAIN SELECT \* FROM tb\_seller WHERE SUBSTRING(NAME,3,2) = '科技'  
这个sql语句的索引列name使用了substring函数所以索引会失效。

4）字符串不加单引号会导致索引失效:

例如：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME='科技' AND STATUS = '0'

![0101a00f02204f9aa6109cf90b00a5ac.png][]

字符串’0’加了单引号，会使用索引列name和status查询

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME='科技' AND STATUS = '0'

![27362207b2994d3e88d53562d2139e81.png][]

如果使用下面的sql语句，0没有加单引号，只会使用索引列name来查询：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME='科技' AND STATUS = 0  
5.尽量使用覆盖索引即查询的列都是索引中的列避免使用 select \* ：

例如：

EXPLAIN SELECT NAME,STATUS FROM tb\_seller WHERE NAME='科技' AND STATUS = '0' AND address='西安市'  
效率比：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME='科技' AND STATUS = '0' AND address='西安市'  
要高。

6.用or分割开的条件当中只要有一个非索引列则不会使用索引：

例如：

EXPLAIN SELECT NAME,STATUS FROM tb\_seller WHERE STATUS = '0' OR PASSWORD='123'  
会导致全表扫描

7.以%开头的模糊查询索引失效。

例如：

EXPLAIN SELECT \* FROM tb\_seller WHERE NAME LIKE '%米'  
这种情况可以采用覆盖索引来解决：

EXPLAIN SELECT NAME FROM tb\_seller WHERE NAME = '小米科技'

[5b04b4349b1049a7aa85092114e8d810.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/04/a56c8e95a4d04bb6b7ef3fc1dd7a2bad.png
[b516ef14b8bb4950a25a53ea040af5a8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/04/fe80cfddccfa4e5f83129df934eae0c7.png
[f67ddd53c381464daaea3ee16c3f14bc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/04/ea12c430ef2f434093548146db99d66e.png
[0101a00f02204f9aa6109cf90b00a5ac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/04/d74a283d6abb4e61bb0acd47dcce5e13.png
[27362207b2994d3e88d53562d2139e81.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/04/3cc3f2e5a6d046b3a05f501e854992c4.png