mysql 索引的使用与效果测试

1. 索引相关sql语句

1.1 创建索引

# 为某表中的某个字段创建索引
create index 索引名 on 表名(字段名);

1.2 查看某表索引

# 查看表结构即可获知索引信息
show create table 表名;

1.3 删除索引

# 删除某表中某个索引
drop index 索引名 on 表名;

2. 测试索引效果

2.1 数据准备

1） 准备表

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

表结构如下

mysql> show create table s1;
+-------+------------------------------------------------+
| Table | Create Table                                                                                                                                                                                 |
+-------+------------------------------------------------+
| s1    | CREATE TABLE `s1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `gender` char(6) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

2） 创建存储过程，实现批量插入记录

注意：为了执行批量插入，需要
1. 先执行delimiter $$ 来声明存储过程的结束符号为$$，方便执行创建存储过程的语句
2. 然后再执行语句，创建存储过程(仅创建，尚未调用)
3. 创建完成后，执行delimiter ; 重新声明分号为结束符

#1. 声明存储过程的结束符号为$$
delimiter $$ 

#2. 创建存储过程，实现批量插入记录
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'mlg','male',concat('mlg',i,'@qq'));
        set i=i+1;
    end while;
END$$ #$$结束

#3. 重新声明分号为结束符号
delimiter ; 

3） 查看存储过程

show create procedure auto_insert1\G

mysql> show create procedure auto_insert1\G
*************************** 1. row ***************************
           Procedure: auto_insert1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `auto_insert1`()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'mlg','male',concat('mlg',i,'@qq'));
        set i=i+1;
    end while;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> 

4） 调用存储过程

call auto_insert1();

调用存储过程之后，就开始往s1表中插入数据，此过程如果数据库是装在机械硬盘里，插入数据的过程可能会很慢(插入数据条数较多)，但如果是ssd固态的话，速度会比较快,大概几分钟左右

如下调用存储过程后的结果：

mysql> call auto_insert1();
Query OK, 1 row affected (7 min 21.30 sec)

mysql> 
mysql> select count(*) from s1;
+----------+
| count(*) |
+----------+
|  2999999 |
+----------+
1 row in set (0.97 sec)

mysql> 

如上结果，插入2999999条数据花费7 min 21.30 sec（测试环境为固态硬盘）

2.2 在无索引的前提下测试查询速度

1）记录未创建索引前mysql的表大小

[root@centos-linux-6 blog01]# pwd
/var/lib/mysql/blog01
[root@centos-linux-6 blog01]# du -sh *
12K     book.frm
96K     book.ibd
4.0K    db.opt
12K     s1.frm
177M    s1.ibd
[root@centos-linux-6 blog01]# 

s1表的ibd数据文件(由于InnoDB用的是聚集索引，所以数据和索引都存在ibd文件中)大小为177M

2）无索引状态下进行查询

# 查找一个在范围内，但是id比较大大值
mysql> select * from s1 where id = 2999990;
+---------+------+--------+---------------+
| id      | name | gender | email         |
+---------+------+--------+---------------+
| 2999990 | mlg  | male   | mlg2999990@qq |
+---------+------+--------+---------------+
1 row in set (1.10 sec)

# 查找一个不在表内大数据
mysql> select * from s1 where id = 3999999; 
Empty set (1.10 sec)

mysql> 

在无索引状况下，以id为条件进行数据查询话费约1.10 sec
即便id=3999999不在我们表中，但mysql并不知道，因此他会遍历整个表才能反馈出结果
无索引状态下查询数据，都需要从头开始遍历整张表

2.3 为表某个字段建立索引并以该字段为条件进行查询

1）查看当前s1表文件大小

[root@centos-linux-6 blog01]# du -sh *
12K     book.frm
96K     book.ibd
4.0K    db.opt
12K     s1.frm
233M    s1.ibd
[root@centos-linux-6 blog01]# 

由于为s1表中的id字段创建了索引，因此s1.ibd文件大小为233M，较之前当177M明显增大

2）为id字段建立索引并再次查找

create index 索引名 on 表名(字段名);

创建索引

mysql> create index ind_id on s1(id);
Query OK, 0 rows affected (3.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

再次查找

mysql> select * from s1 where id = 2999990;
+---------+------+--------+---------------+
| id      | name | gender | email         |
+---------+------+--------+---------------+
| 2999990 | mlg  | male   | mlg2999990@qq |
+---------+------+--------+---------------+
1 row in set (0.00 sec)

mysql> select * from s1 where id = 3999999;
Empty set (0.00 sec)

mysql> 

在为id字段创建索引状况下，以id为条件进行数据查询话费约0.00 sec
mysql先到索引表中根据b+树的搜索原理搜到了2999990的结果，然后返回（B+树的作用就是可以大大降低IO操作节省查询时间）
同理，3999999也能迅速得出其不存在的结论

3）以表中未创建索引的字段为条件查询数据

mysql> select * from s1 where email = 'mlg2999990@qq';
+---------+------+--------+---------------+
| id      | name | gender | email         |
+---------+------+--------+---------------+
| 2999990 | mlg  | male   | mlg2999990@qq |
+---------+------+--------+---------------+
1 row in set (1.34 sec)

mysql> 

由对比结果可知，由于没有给email字段加索引，所以当以其为条件进行查询时，速度依旧很慢

3. 总结

1）索引可以加快数据的查询速度，但是会拖慢数据的写速度（因为B+树是一颗多路平衡查找树，数据的插入和删除都可能导致B+树重新平衡，特别对于聚集索引，还可能导致数据的迁移。）
2）索引会存储在mysql的文件中，所以创建索引会占用更多的磁盘资源
3）对一些经常被查，但是不经常被修改的数据就可以创建索引
4）只有区分度比较大的字段/列才合适创建索引；比如username合适而性别不合适
5）一张表可以创建任意多个索引
6）给a字段建立的索引是不能在条件为b字段的时候生效的
7）对主键和unique key这样的约束列是自带索引的，不需要创建索引这些列的查询速度本身就很快

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