索引

索引

索引的定义

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

如何创建索引

建表时创建：

CREATE TABLE 表名(
字段名 数据类型 [完整性约束条件],
       ……，
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]
);

说明：

UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
SPATIAL:可选。表示索引为空间索引。
INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是    一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。
长度:可选。指索引的长度，必须是字符串类型才可以使用。
ASC:可选。表示升序排列。
DESC:可选。表示降序排列。
注：索引方法默认使用B+TREE。

建表后创建：

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；	
或	
CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；

索引的类型

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。数据库中索引可以分为两种类型：聚簇索引（聚集索引）和非聚簇索引（非聚集索引）。

聚集索引：表数据是按照索引的顺序来存储的，也就是说，索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。正因为索引的数据需与数据物理存储的顺序一致，在一张表上最多只能创建一个聚集索引。

非聚集索引：表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页、数据行的逻辑指针。为了提高索引的性能，一般采用B树来实现。

如何选择合适的列创建索引

1. 在where 从句，group by 从句，order by 从句，on 从句中出现的列；
2. 索引字段越小越好；
3. 离散度大的列放到联合索引的前面；比如：
   select * from payment where staff_id = 2 and customer_id = 236;
   针对上面的查询是 index(sftaff_id, customer_id) 好？还是index(customer_id, staff_id)好？
   因为customer_id的离散度更大，因此用后面的更合适！！
   那么问题来了。怎么判断离散度呢，可以使用 select count(distinct customer_id), count(distinct staff_id) from 表名
   谁的值大，说明这一些列的离散度更高！

创建索引需要注意SQL语句的写法

一、数据库建立索引的原则
1.确定针对该表的操作是大量的查询操作还是大量的增删改操作。
2.尝试建立索引来帮助特定的查询。检查自己的sql语句，为那些频繁在where子句中出现的字段建立索引。
3.尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间，同时，复合索引也占磁盘空间。
4.对于小型的表，建立索引可能会影响性能
5.应该避免对具有较少值的字段进行索引。
6.避免选择大型数据类型的列作为索引。

mysql索引建立的原则
索引查询是数据库中重要的记录查询方法，要不要进入索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑，下面给出实际中的一些通用的原则：

1. 在经常用作过滤器的字段上建立索引；
2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；
3. 在不同值较少的字段上不必要建立索引，如性别字段；
4. 对于经常存取的列避免建立索引；
5. 用于联接的列（主健/外健）上建立索引；
6. 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；
7. 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有有限数目（不是很少）唯一的列；进行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决于DBA的所设计的数据库结构。