索引的好处
- 通过创建索引,可以在查询的过程中,提高系统的性能
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
- 在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间
索引的坏处
- 创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大
- 索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大
- 在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护
索引的分类
聚集索引
- 表记录的排列顺序和索引的排列顺序一致,所以查询效率快,只要找到第一个索引值记录,其余连续性的记录在物理上一样连续存放.
- 聚集索引的缺点就是修改慢,因为为了使表记录和索引的排列顺序一致,在插入记录的时候,会对数据页重新排序
非聚集索引
- 表记录和索引的排列顺序不一定一致,两种索引都采用B+树的结构,非聚集索引的叶子层并不和实际数据页相重叠,而采用叶子层包含一个指向表记录的指针.
- 非聚集索引层次多,不会造成数据重排
覆盖索引
- 从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
- 使用覆盖索引的好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
索引的实现
B+树索引
散列索引
位图索引
索引的使用
什么时候使用索引
- 经常需要搜索的列上
- 作为主键的列上
- 经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
- 经常需要根据范围进行搜索的列上
- 经常需要排序的列上
- 经常使用在where子句上面的列上
什么时候不应该使用索引
- 查询中很少用到的列
- 对于那些具有很少数据值的列.比如人事表的性别列,bit数据类型的列
- 对于那些定义为text,image的列.因为这些列的数据量相当大
- 当对修改性能的要求远远大于搜索性能时.因为当增加索引时,会提高搜索性能,但是会降低修改性能
使用语法
创建索引的语法：
1
2
CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name
ON {table_name | view_name} [WITH [index_property [,....n]]
- UNIQUE: 建立唯一索引。
1
CREATE UNIQUE INDEX my_index_name ON my_table_name(col_name);
- CLUSTERED: 建立聚集索引。
- NONCLUSTERED: 建立非聚集索引。
- Index_property: 索引属性。
- HASH索引
1
CREATE INDEX my_index_name ON my_table_name USING HASH (col_name);
- EXPLAIN: 分析某个SQL查询语句执行是否使用了索引
1
EXPLAIN SQL查询语言;
- 创建函数索引
1 2
// 创建一个列的记录的长度函数索引 CREATE INDEX my_index_name ON my_table_name (LENGTH(col_name));
- 创建复合索引
1 2
// 对两个及以上的属性创建复合索引 CREATE UNIQUE INDEX my_index_name ON my_table_name (col1,col2);
- 修改索引名称
1
ALTER INDEX my_index_name RENAME TO new_index_name;
索引的失效
- 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
若要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
- 对于多列索引，不是使用的第一部分，则不会使用索引
- like查询是以%开头
- 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
- B+树实现的。没有遵循最左匹配原则。一些关键字会导致索引失效，例如 or，！= ，not in，is null ,is not unll like查询是以%开头，隐式转换会导致索引失效。对索引应用内部函数，索引字段进行了运算。
