数据库优化之索引对DML语句效率的影响

        索引是一个可以提高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%就可以考虑创建单列或者多列索引了，创建多列索引时要注意：一定要把筛选数据最多的字段放在创建索引语句的最左侧，遵从左前缀的原则。重要的事情说三遍：
遵循左前缀原则！！！
遵循左前缀原则！！！
遵循左前缀原则！！！