MySQL索引
- MySQL索引可以快速提高MySQL的检索速度。索引分单列索引和组合索引
- 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引
- 组合索引：一个索引包含了多个列
- 索引是一张保存了主键和索引字段的表，该表指向了实体表的记录。创建索引需要确保该索引是应用在SQL查询语句的条件
- 索引一样具有缺点，因为我们在执行INSERT，UPDATE和DELETE的时候，更新了表的同时我们也需要更新索引文件
- 使用原则
- 对经常更新的表避免对其使用过多的索引，对经常用于查询的字段应该创建索引
- 数据量小的表最好不要使用索引，数据量小，可能查询全部数据话费的时间比遍历索引的时间还短
- 在一同值少的列上不要建立索引，比如性别字段只有两个取值，男或女
索引分类
- 普通索引index:加速查找，没有约束，可以插入重复值和空值
- 唯一索引：
- 主键索引：primary key:加速查找+约束（不为空且唯一）
- 唯一索引：unique：加速查找+约束（唯一）
- 联合索引:
- primary key(id,name):联合主键索引
- unique(id,name):联合唯一索引
- index(id,name):联合普通索引
- 全文索引fulltext：用于搜索长文章最好
- 空间索引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"
