数据库索引（聚集与非聚集）

作用：

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构；索引是用来对数据表中的一个列或多个列进行排序的数据结构，在这里数据库用的平衡树，B-Tree和B+Tree 平衡多路查找树
例如：

select * from table1 where id=10000

如果没有索引，那么必须遍历整个表，直到找到id为10000的这一行数据为止；有了索引之后则可直接通过索引来查找

索引类型：

Mysql中索引类型主要分为 聚集索引、 非聚集索引两种
聚集索引：是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法，聚集索引存储记录是物理上连续存在，一个表中只能有一个聚集索引，因为物理存储只能有一个顺序
非聚集索引：非聚集索引则是逻辑上连续存在，但是物理上不连续；但是非聚集索引一个表可以有多个，同时数据库索引创建默认时非聚集索引

当一个表没有加主键（聚集索引）的时候，它的数据是无序的放置在磁盘存储器上，一行一行的排列的很整齐，跟我们认知的表很接近；但给表加上了主键之后，就相当于在表中建立了聚集索引，因为主键索引一般都是聚集索引（SQL Server默认主键为聚集索引，而MySql主键就是聚集索引），那么表在磁盘上的存储结构就变成了树状结构

非聚集索引的二次查询问题：

聚集索引索引的叶子节点就是对应的数据节点，可以直接获取到数据到对应的数据，而非聚集索引在索引处没有覆盖到对应的列，它指向的仍是一个索引节点，只是一个指针指向对应的数据块，也就是主键的意思，所以非聚集索引查询出来的值只能得到主键ID，要得到具体数据要在根据得到的主键ID通过聚集索引进行查找，找到主键ID对应的数据行存储的位置得到最终结果。
创建表user_inf:

user_id	user_name	user_birthday
1	张三	1997-1-1
2	李四	1995-2-2
3	王五	1998-3-3

user_id为主键，即聚集索引， user_name为非聚集索引

create index index_birthday on user_inf(user_birthday);

执行

select user_name from user_inf where user_birthday = '1997-1-1';

它的过程实际上是先通过非聚集索引index_birthday查找到user_birthday = '1997-1-1’的所有记录的主键ID值，然后通过得到的主键ID值执行聚集索引查找，找到主键ID值所在行的真实位置，最后，从得到的真实数据中得到user_name字段的值返回，得到最终结果

但是我们执行一下两条语句是不需要进行二次查询的，直接就可以从非聚集索引的节点里获取到值了

select user_id,user_birthday from user_inf where user_birthday = '1997-1-1';
select user_birthday from user_inf where user_birthday = '1997-1-1';

也就是说，如果不查询非索引的字段的话是不需要二次查询的

解决方法：

采用覆盖索引
建立两列以上的索引，即可查询符合索引里的列的数据而不需要进行回表二次查询，如index_username_birthday;执行：

create index index_username_birthday on user_inf(user_name,user_birthday);

则上面原本进行二次查询的语句的执行过程会变为通过非聚集索引index_username_bi-rthday查找user_birthday的叶节点的内容，但是，叶节点中除了有表的主键ID的值以外还有user_name字段的值，而我们只查询id和user_name两个字段的值，因此，不需要通过主键ID再次回表查询了，直接取得值返回就可以了；通过这种覆盖索引直接查找的方式可以避免二次查询的问题，大大提高查询的性能

主键：

表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列被称为表的主键(pk),用于强制表的的实体完整性。 一个表只能有一个PRIMARY KEY约束，并且该列不能为空值。 创建主键时，数据库引擎会主动创建唯一的索引的来强制实施PRIMARY KEY约束的唯一性要求。如果表中不存在聚集索引或未显示指定聚集索引，则将创建唯一的聚集索引以强制实施PRIMARY KEY约束。

建立索引的原则：

1. 定义为主键的数据列一定要建立索引
2. 定义为外键列的一定要建立索引
3. 对于经常查询的列最好建立索引
4. 对于需要在指定范围内的快速或频繁查询的数据列
5. 经常出现order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，则索引中的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用
6. 经常出现在where子句中的数据列
7. 对于查询很少涉及到的列，重复值比较多的列不要建立索引，因为既然这些列很少用到，则添加索引不能增加效率，反而会降低系统的维护速度和增大空间需求
8. 对于定义为text、image、bit的数据类型的列不要建立索引 因为这些列要么数据量很大，要么使用很少，不利于索引
9. 限制表的索引数目。对于一个存在大量更新操作的表，所建立的索引的数目一般不超过三个，最多不要超过15个。

索引的缺点：

索引虽说会太高查询速度，但是会使写入的效率降低，因为索引采用平衡树的结构来排序，并且这个结构必须要一直保持在正确的状态下，增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构，因此，在每次数据改变时，DBMS必须去重新梳理(索引)的结构以确保它的正确性，这会额外带来很打的性能开销，所以不是所有的表都能建立索引，只有数据量大的表才适合建立索引，并且建立的适合的列上性能会更好