一、索引是什么?
索引是表的目录,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于新华字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,以此快速定位查询数据。对于索引,会保存在额外的文件中。
二、索引的注意事项
索引可以提高查询速度,但会减慢写入速度,索引的缺点是创建和维护索引需要耗费时间。
索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,
所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过5个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
1、哪些字段适合创建索引
a、表的主键、外键必须有索引;外键是唯一的,而且经常会用来查询
b、经常与其他表进行连接的表,在连接字段上应该建立索引;
c、数据量超过300的表应该有索引;
d、重要的SQL或调用频率高的SQL,比如经常出现在where子句中的字段,order by,group by, distinct的字段都建议添加索引
e、经常用到排序的列上,因为索引已经排序。
f、经常用在范围内搜索的列上创建索引,因为索引已经排序了,其指定的范围是连续的
2、什么场景不适合创建索引
1、 在查询中很少使用或者参考的列不应该创建索引
既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
2、只有很少数据值的列也不应该增加索引
因为结果集合就是相当于全表查询了,所以没有必要。由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
3、对于那些定义为text, image和bit数据类型的列不应该增加索引。
这些列的数据量要么相当大,要么取值很少
4、当修改性能远远大于检索性能时,不应该创建索引。
修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。
5、不会出现在where条件中的字段不该建立索引。
6、如果列均匀分布在 1 和 100 之间,却只是查询中where key_part1 > 1 and key_part1 < 90不应该增加索引
三、索引规范
1、主键规范
1、每个表必须显示指定主键;
2、主键尽量为一个字段,且为数字类型,避免使用字符串;
3、 主键尽量保持增长趋势,建议使用id的生成器;
4、主键尽量杜绝联合索引
四、常用索引及操作
1、普通索引
仅加速查询 最基本的索引,没有任何限制,是我们大多数情况下使用到的索引。
CREATE INDEX index_name on account_info(user_id);
alter table account_info add index index_name(user_id);
2、唯一索引
与普通索引类型相比,唯一索引效率更高:加速查询 + 列值唯一
CREATE UNIQUE INDEX user_name on account_info(user_id) ;
3、组合索引
将几列作为一条索引进行检索,使用最左匹配原则
4、删除索引
drop index_name on account_info;
alter table account_info drop index index_name;
5、查看索引
show index from account_info;