数据库索引相关知识

关于索引

什么是索引

索引与书的目录类似，可以快速定位要查找的数据所有位置，索引使数据库程序无需扫描整个表，就可以在其中找到所需要的数据，索引包含了一个表中包含值的列 表，其中包含了各个值的行所存储的位置，索引可以是单个或一组列，索引提供的表中数据的逻辑位置，合理划分索引能够大大提高数据库性能。 索引需要注意：加索引的列一定要是经常使用来条件筛选的列，并且where中要避免对有索引的列进行计算，索引并不是越多越好，索引会占用更多的存储空间，也会对insert 、update、delete这些操作有一定的性能影响。

索引的优点

• 大大的缩短查询事件，加索引至少比普通查询快100倍
• 减少磁盘IO（像字典一样可以直接定位）
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

索引的缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
• 当对表中的数据进行增加，删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度

创建索引的规则

• 在经常需要搜索的列上创建索引，可以加快搜索的速度；
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
• 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 在经常需要排序的列上创建索引，因为索引已经排序，这样可以利用索引的排序加快排序的查询时间，减少一次文件排序。
• 在经常使用where中的列上创建索引，加快条件的判断速度。

不适合创建索引的情况

• 很少使用得列不应该创建
• 对于很少数据值的列也不应该增加索引，因为这些列的取值很少，列入人事表的性别列在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大，增加索引并不能明显加快搜索速度。
• 对于那些定义为text，image，bit数据类型的列不应该增加索引。这是因为这些列的数据量要么相当大，要么取值很少
• 当修改性能远远大于检索性能时不应该创建索引。因为，修改性能和检索性能时相互矛盾的。

索引失效的情况

• 如果条件中有or，即使其中有条件带索引也不会使用（想要使用or，又想让or索引生效，只能将or条件中的每个列都加上索引）
• 多列索引，不是使用的第一部分，则不会使用索引
• 类型转换时索引不生效（如果列类型是字符换，那一定要在条件中将数据使用引号引用起来，否则不适用索引）
• where中索引列有运算
• where中索引列使用了函数
• 如果mysql觉得全表扫描更快时（数据少），索引不生效。
• like 模糊查询中，右模糊查询（321%）会使用索引，而%321 和%321%会放弃索引而使用全局扫描
• 查询的数量是大表的大部分，应该是30%以上
• not in，not exist不使用索引
• 存储引擎不能使用索引中范围条件右边的列。——范围之后的索引失效。（< ,> between and,）
  
  执行查询一时，索引全部使用到。
  执行查询二时，索引使用到name和age，但是name是使用索引检索，而age着重索引排序，这时age为范围查找，pos索引将失效

如何创建索引

• 创建索引语句

CREATE INDEX index_name ON table(column)

• 修改表时加索引语句

ALTER TABLE table_name ADD INDEX index_name ON (column)

• 唯一索引

CREATE UNIQUE INDEX indexName ON table(column)

• 修改表时加唯一索引语句

ALTER TABLE table_name ADD UNIQUE indexName ON (column)

• 主键索引

CREATE TABLE 'table' (
    'id' int(11) NOT NULL AUTO_INCREMENT ,   
    PRIMARY KEY ('id')
);

• 复合索引（组合索引）

ALTER TABLE `table` ADD INDEX indexName (name,xb,age);