MySQL索引的使用

MySQL索引

• MySQL索引可以快速提高MySQL的检索速度。索引分单列索引和组合索引
  • 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引
  • 组合索引：一个索引包含了多个列
• 索引是一张保存了主键和索引字段的表，该表指向了实体表的记录。创建索引需要确保该索引是应用在SQL查询语句的条件
• 索引一样具有缺点，因为我们在执行INSERT，UPDATE和DELETE的时候，更新了表的同时我们也需要更新索引文件
• 使用原则
  • 对经常更新的表避免对其使用过多的索引，对经常用于查询的字段应该创建索引
  • 数据量小的表最好不要使用索引，数据量小，可能查询全部数据话费的时间比遍历索引的时间还短
  • 在一同值少的列上不要建立索引，比如性别字段只有两个取值，男或女

索引分类

1. 普通索引index:加速查找，没有约束，可以插入重复值和空值
2. 唯一索引：
   • 主键索引：primary key:加速查找+约束（不为空且唯一）
   • 唯一索引：unique：加速查找+约束（唯一）
3. 联合索引:
   • primary key(id,name):联合主键索引
   • unique(id,name):联合唯一索引
   • index(id,name):联合普通索引
4. 全文索引fulltext：用于搜索长文章最好
5. 空间索引spatial：几乎不用

索引的创建与删除

• 在已存在的表上创建

create [unique|fulltext|spatial] index 索引名 on 表名 (字段[(长度)] [ASC|DESC])

• alter table方式创建

alter table 表名 add [unique|fulltext|spatial] 索引名（字段名[(长度)] [ASC|DESC]);
alter table 表名 add primary key (字段)：添加一个主键

• 创建表的时候创建索引

create table 表名 
（
column1,类型 [约束条件...],
......
[unique|fulltext|spatial] index|key [索引名] (字段名[(长度)] [ASC|DESC]);
）

• 删除索引

drop index 索引名 on 表名

创建实例

• 我们可以通过以下sql代码创建一张表格并且插入300万条数据

//创表表格
create table s1 (id int,name varchar(20),gender char(6),email varchar(50))
//定义函数：往表格当中插入数据
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,concat("Tom",i),'male',concat("Time",i,"@qq.com"));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
//调用定义的函数
call auto_insert1();

• 不加索引的情况下查询数据需要花费很长的时间

mysql> select * from s1 where id = 40000;
+-------+----------+--------+------------------+
| id    | name     | gender | email            |
+-------+----------+--------+------------------+
| 40000 | Tom40000 | male   | Time40000@qq.com |
+-------+----------+--------+------------------+
1 row in set (1.33 sec)

• 加上索引之后可以看到查询速度有很大提升

mysql> create index idx on s1(id);
Query OK, 0 rows affected (3.66 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from s1 where id = 40000;
+-------+----------+--------+------------------+
| id    | name     | gender | email            |
+-------+----------+--------+------------------+
| 40000 | Tom40000 | male   | Time40000@qq.com |
+-------+----------+--------+------------------+
1 row in set (0.00 sec)

select * from s1 where id = 40000;虽然命中索引，但未能覆盖索引。因为我们select的字段为*, 除了id以外还需要其他字段值，这是需要时间的。 如果select id from s1 where id = 4000,就是覆盖索引命中索引了

• 主键索引
  将一个字段设置为主键，便是主键索引，不仅仅是代表一个约束

• 唯一索引：索引列的值必须是唯一的，但是必须有空值。如果是组合索引，则列值的组合必须是唯一的

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

• 组合索引

mysql> create index idn  on s1(id,name);
Query OK, 0 rows affected (4.76 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select email from s1 where id = 281235 and name = "Tom281235";
+-------------------+
| email             |
+-------------------+
| Time281235@qq.com |
+-------------------+
1 row in set (0.00 sec)

1.组合索引遵循最左前缀匹配原则
create index idn on s1(id,name)可以命中:

• select * from s1 where id = 123;
• select * from s1 where id = 40000 and name = "xxx"

2.组合查询遵循最左匹配原则：

• mysql会一直向右匹配直到遇到范围查询后就停止，(范围查询包括>,<,between,like)。比如查询条件a=1 and b=2 and c>3 and d=4，如果建立(a,b,c,d)顺序的索引，d是用不到索引的，但是如果建立(a,b,d,c)的索引则都可以用到
  3.组合索引一般用and链接，用or连接提高不了速度

• 索引合并

mysql> create index nameIndex on s1(name);
Query OK, 0 rows affected (7.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index emailIndex on s1(email);
Query OK, 0 rows affected (9.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引合并可以命中的：

• select * from s1 where name = "Tom"
• select * from s1 where email = "xxx"
• select * from s1 where name = "xxx" and email = "xxx"