系列文章:
- MySQL性能优化[理论篇]-B树索引与hash索引
- MySQL性能优化[理论篇]-聚簇索引和非聚簇索引,InnoDB和MyISAM
- MySQL性能优化[准备篇]-慢查询日志
- MySQL性能优化[准备篇]-单条SQL性能剖析
- MySQL性能优化[实践篇]-索引合并与复合索引
- MySQL性能优化[实践篇]-复合索引实例
- MySQL性能优化[实践篇]-使用B树索引
- 分库分表的一些思考
从上一篇创建索引的实践中,我们看到了索引给我们带来的性能提升是非常可观的。
我们上次创建的表结构非常简单,只有两三个字段,where子句查询条件只有一个字段。
实际应用场景中我们的表结构会更复杂,查询条件也会非常多。在多条件查询的情况下又如何才能用到索引呢,我们可以测试一下。
准备测试数据
创建表结构
create table tb_test(id int primary key auto_increment,
c1 char(1),
c2 char(1),
c3 char(1),
c4 char(1));
生成随机字符的函数
delimiter $$
# 生成随机字符串
create function rand_char() returns char(1)
begin
declare CHARS char(52) default 'abcdefghijklmnopqrstuvwxyz';
return substr(CHARS,floor(1+RAND()*52), 1);
end
$$
生成测试数据的存储过程
create procedure insert_tb_test(c int)
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into tb_test(c1,c2,c3,c4) values(rand_char(),rand_char(),rand_char(),rand_char());
until i = c end repeat;
set autocommit = 1;
end
$$
delimiter ;
调用存储过程生成一百万条测试数据:
call insert_tb_test(1000000);
现在的查询场景是select * from tb_test where c1=? and c2=? and c3=? and c4=?
没有索引的情况下我们试着查询一次:
mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D';
Empty set (0.61 sec) # 0.61秒,很慢
我们可以为c1、c2、c3、c4分别创建一个索引或者为他们创建一个复合索引。
我们先来试试第一种方案。
多个单列索引
创建多个索引
alter table tb_test add index idx_tb_test_c1(c1);
alter table tb_test add index idx_tb_test_c2(c2);
alter table tb_test add index idx_tb_test_c3(c3);
alter table tb_test add index idx_tb_test_c4(c4);
创建索引后查询
mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D';
Empty set (0.05 sec) # 花了 0.05秒
mysql> explain select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_test
partitions: NULL
type: index_