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

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

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

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

先创建一个数据表：

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='北京市'

 

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语句会使用组合索引中的每一列：

下面这条sql语句使用了范围查询：

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

索引只能使用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'

 

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

 EXPLAIN SELECT * FROM tb_seller WHERE NAME='科技' AND STATUS = '0'

  

如果使用下面的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 = '小米科技'