索引是一个可以提高select查询最有效的手段,可以在数据库中为一列或者多列建立索引,创建索引首先将数据按照从小到大排序,让后存储到存盘中。Mysql中数据存储以页的方式存储,页大小默认16K,存放数据的称之为表页,存放索引的称之为索引页,两者存储角度没有什么区别,但是表页一般相互独立,而索引页之间的关系呈树形结构。
根据树的层级结构划分:根节点、叶子节点、分支节点。叶子结点存储索引列排序后的值,同时存储相邻页的编号和值对应行所在表页的编号,根节点和分支节点主要存储索引列的范围,以及每个范围对应的索引页号。
假设有一个没有索引的表,当要查询表中的某些记录时,需要把所有表页都加载到内存中,在每个表页中筛选出符合的记录以二维表形式返回给用户。若表中的数据量比较大,会有很大的IO开销,而且在筛选数据时CPU的负载也很大,这种方式称之为“全表扫描”。而使用索引时,只需要根据查询范围(前期sql中使用索引)迅速定位到索引表的叶子结点和相关的表页。此期间只需要加载部分索引页和表页即可,减少了IO和CPU的负载,可以很大程度上提高查询效率。但是索引的建立需要审时度势,当表的查询频率比较低,建立索引只会占用磁盘空间,而且索引的空间占用还不小,另外表的数据量大小就更没必要建立索引,总不能书的目录比正文还厚吧,哈哈!
下面使用一个实验来测试索引对DML语句的影响。首先创建两张表test1,test2:
create table test1(id int,name VARCHAR(20));
create table test2(id int ,name VARCHAR(20));
创建一个存储过程分别向test1和test2中插入数据
创建向表中插入记录的存储过程proc1()
delimiter $$
create procedure proc1(cnt int)
begin
##定义i为整形,默认值为1
declare i int default 1;
##开启事务
start transaction;
##MySQL repeat 循环结构
repeat
##插入youhua库中的表test1、test2插入数据
insert into youhua.test1(id,name) values(i,concat('abc',i));
insert into youhua.test2(id,name) values(i,concat('abc',i));
##变量i自增1
set i = i+1;
##当i值大于输入cnt值时,退出循环体
until i>cnt end repeat;
##提交事务
commit;
end $$
delimiter ;
插入100万条记录
call proc1(1000000);
为test2的id加上唯一索引
create unique index un_id on test2(id);
测试一、百万数据取一条
先查询test1表
select id,name from test1 where id = 200000;
查询test2表
select id,name from test2 where id = 200000;
test1表没有索引执行的是全表扫描,执行时间0.5秒左右,而test2使用索引,查询时间很短,短到0.00,博主也很懵逼啊。
测试二、百万数据取10000条
select id,name from test1 where id between 10001 and 20000
select id,name from test2 where id between 10001 and 20000
这次是范围查询,test1依旧是全表扫描,test2执行只用了0.04s,此查询过程中,mysql会根据条件的范围快速定位到索引页的一些分支节点,分支节点再遍历其下的叶子节点返回即可,效率很高。
测试三、百万数据取全部
select id,name from test1 where id >0;
select id,name from test2 where id >0;
本次的索引没有派上用场,因为要查询全部,需要加载全部的表页,倘若使用索引会把索引页和表页全部全部加载,效率比不使用索引还要慢,这点mysql后台已经做了优化判断,自动选择代价低的方式,即直接全表扫描。
测试四、更新某一条数据
update test1 set name=’tom’ where id = 1000;
update test2 set name=’tom’ where id = 1000;
更新某条普通数据其实受查询的影响很大,test1需要加载全表,因为它也不知道到底有几个id=1000的记录,而test2上建立了唯一索引能够知道定位了id=1000的几率完成更新。
测试五、更新索引字段
update test1 set id=id+1000000;
update test2 set id=id+1000000;
更新索引字段时,索引的弊端就开始暴露了,test1表只需要遍历全表对id+1000000即可,而test2需要在这基础上维护索引,3.7 : 14.7效率慢了一批啊。
测试六、插入数据
再创建两张表test3和test4
create table test3 like test1;
create table test4 like test2;
我们把test1中的数据分别放到test3和test4
insert into test3 select id,name from test1
insert into test4 select id,name from test1
插入数据和更新差不多,test2需要维护索引,索引多做了一些工作。
测试七、删除数据
delete from test3;
delete from test4;
删除时,test2需要把表页和索引页中的数据全部删除,又多走一步。
由上述七个实验可以看到,索引的利弊,可以提升查询效率,但增删改就变慢了。所以建立索引时一定要按照业务来,搞清楚表的读写比例,不要盲目建立索引。比较合理的方法就是找出常用的查询条件字段,如果这些查询能返回总记录的3%~5%就可以考虑创建单列或者多列索引了,创建多列索引时要注意:一定要把筛选数据最多的字段放在创建索引语句的最左侧,遵从左前缀的原则。重要的事情说三遍:
遵循左前缀原则!!!
遵循左前缀原则!!!
遵循左前缀原则!!!