华企号 后端开发 测试索引

测试索引

1准备

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程,实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

2在没有索引的前提下测试查询速度

#无索引:mysql根本就不知道到底是否存在id等于333333333的记录,也不知道存在几条id=333333333的记录,只能把数据表从头到尾扫描一遍,此时有多少个磁盘块就需要进行多少IO操作,所以查询速度很慢
mysql> select * from s1 where id=333333333;
Empty set (0.33 sec)

3在表中已经存在大量数据的前提下,为某个字段段建立索引,建立速度会很慢

测试索引插图

或者用alter table s1 add primary key(id);加主键,建索引很慢的。

4在索引建立完毕后,以该字段为查询条件时,查询速度提升明显

测试索引插图1

​ 1. mysql先去索引表里根据b+树的搜索原理很快搜索到id等于333333333的记录不存在,IO大大降低,因而速度明显提升

​ 2. 我们可以去mysql的data目录下找到该表,可以看到占用的硬盘空间多了

​ 3. 需要注意,如下图

测试索引插图2

5总结

#1. 一定是为搜索条件的字段创建索引,比如select * from s1 where id = 333;就需要为id加上索引

#2. 在表中已经有大量数据的情况下,建索引会很慢,且占用硬盘空间,建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据,然后以id为数据项,创建索引结构,存放于硬盘的表中。
建完以后,再查询就会很快了。

#3. 需要注意的是:innodb表的索引会存放于s1.ibd文件中,而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在innodb中,表数据文件本身就是按照B+Tree(BTree即Balance True)组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集,所以innodb要求表必须要有主键(Myisam可以没有),如果没有显式定义,则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则mysql会自动为innodb表生成一个隐含字段作为主键,这字段的长度为6个字节,类型为长整型.

你想一下,是不是全部加索引就一定好的呢,首先,我们加上索引,查询时快了,但是写入的时候就慢了,还记得吗,每次插入新的记录,你的整个索引结构都会跟着改,所以如果你乱加索引,你会发现,即便是你的网站没有几个人在注册,或者说没有几个写入数据的操作,你的磁盘IO会居高不下,磁盘在疯狂的转,因为你每插入一条数据,我们的索引都需要重新建,重建的索引要写入硬盘里面的,还记得我们给那三百万条数据建索引的时候的速度吗?每次都要把所有的数据取出来,做好一个数据结构,然后再写回硬盘,也就是要经历很多的IO才能实现这个事儿,所以乱加索引的一个弊端就是,你很少的写入都会导致你的磁盘IO非常的高,导致效率很差,所以我们要学一下怎么正确的加索引。

作者: 华企网通王鹏程序员

我是程序员王鹏,热爱互联网软件开发和设计,专注于大数据、数据分析、数据库、php、java、python、scala、k8s、docker等知识总结。 我的座右铭:"业精于勤荒于嬉,行成于思毁于随"
上一篇
下一篇

发表回复

联系我们

联系我们

028-84868647

在线咨询: QQ交谈

邮箱: tech@68v8.com

工作时间:周一至周五,9:00-17:30,节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博
返回顶部