索引-01
一、索引的定义
索引用来快速地寻找那些具有特定值的记录或者说索引就是一种加快查找数据的内部机制。
二、索引的分类
索引有很多种：主键索引、唯一索引、普通索引、组合索引、辅助索引、聚集索引、覆盖索引、全文索引等等。
2.1、主键索引：主键索引必须是第一列，唯一标示一行记录，不允许重复值，也不允许有空值。（B树索引）
2.2、唯一索引：与主键索引的唯一区别就是唯一索引允许有空值。
2.3、普通索引: 这是最基本的索引，没有唯一性的说法。
2.4、组合索引：选择多于一列的元素组合成为索引，此索引整体具有唯一性。在查询条件中常用到多个组合列时，可使得这几列建立组合索引，可以直接取出数据，如果查询列都包含在组 合索引中那么可以直接通过索引块查出数据，以此加快效率。组合索引生效：where第一条件必须是索引第一列方可生效。
2.5、辅助索引: 除了主键索引外的索引，叶节点不包括记录的整行数据，只包含主键个一个标签，这个标签可以帮助找到对应的行记录。
2.6、聚集索引：叶节点存储了整行数据，等同于主键，叶节点存储了整行数据。
2.7、覆盖索引：select不可以查询*（所有的），查询的列全部包含在索引列里，可直接取索引列数据，无需回表查询。减少io次数，可以成为特殊的辅助索引，不需要回表的辅助索引。
2.8、全文索引：在索引文档中查询某个关键字的时候建立索引，加快查询速度。
2.9、聚簇索引：叶子节点存储着（索引页）数据行。
2.10、非聚簇索引：叶子节点没有直接指向数据行。
2.11、索引不生效的情况下
（1）以%开头的LIKE查询语句，称为模糊匹配。
（2）OR左右的查询条件没有同时使用索引。
（3）组合索引的第一个where条件没有使用组合索引的第一个字段。
（4）mysql优化器认为全表扫描比使用索引快的情况下，就不使用索引。
（5）如果列类型是字符串，那么一定要在条件中将数据列使用引号包起来。
2.12、索引的优缺点
优点：
（1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
（2）可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
（3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
(4) 在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
(5) 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
三、索引的种类
有BTree索引，哈希索引，全文索引等等，每种索引的应用场景不一样。
3.1、B树索引意味着记录是按照索引的值进行顺序排列的，并且每一个叶子页到根的距离相同。B-Tree索引适用于全键值、键值范围或者键前缀查找。
- 全值匹配：可以查找a=1 and b=1 and c=1
- 匹配最最前缀：可以查找a=1
- 匹配列前缀：可以查找a like ‘a%’
-
匹配范围值：可以查找 a>1 and a<10
3.2、hash索引是基于hash表实现的，hash的特点就是键值对进行映射，同一个值计算出来的hash值肯定是相同的，但是不能保证相邻的两个值映射到相邻的位置上（数据结构hash函数与映 射）。所以hash索引的最大的特点就是等值查询快，不能进行范围索引。
hash存储的是哈希值和指针，不存储字段值、 不是按照索引顺序存储，不能用于排序、不支持部分索引列匹配查找、支持等值查询，不支持范围查询、哈希值冲突多时，不适用
3.3、全文索引
将非结构化数据中的一部分信息提取出来，重新组织，使其变得有一定结构，然后对此有一定结构的数据进行搜索，从而达到搜索相对较快的目的。对英文的文档，建立token类的索引，进 行查询。
四、索引的工作原理
建立索引后，在查询数据时，会快速根据键值来进行查询，由于键值是按顺序排序的，因此极大的提高了查询的效率。当时使用聚集索引时，并且查询字段包含于索引列中，则直接获取索引 数据返回，不用回表查询。如果不是聚集索引，则在查询到叶子节点后，通过叶子节点上的书签找到对应的行数据（称为回表查询）。有效的利用索引比全表查询大大的缩短了查找效率。
五、分析sql语句的工具
通过explain 来分析sql语句使用索引的情况。
Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。
Explain语法：explain select … from … [where …]
分析一下各个属性：
1、id：这是SELECT的查询序列号
2、select_type：select_type就是select的类型，可以有以下几种：
SIMPLE：简单SELECT(不使用UNION或子查询等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)
3、table：显示这一行的数据是关于哪张表的
4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行
6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
8、ref：显示使用哪个列或常数与key一起从表中选择行。
9、rows：显示MySQL认为它执行查询时必须检查的行数。
10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。
1、始终包含聚集索引
当表中不包含聚集索引时，表中的数据是无序的，这会降低数据检索效率。即使通过索引缩小了数据检索的范围，但由于数据本身是无序的，当从表中提取实际数据时，会产生频繁的定位问题，这也使得SQL Server基本上不会使用无聚集索引表中的索引来检索数据。
2、保证聚集索引唯一
由于聚集索引是非聚集索引的行定位器，如果它不唯一，则会使行定位器中包含辅助数据，同时也导致从表中提取数据时，需要借助行定位器中的辅助数据来定位，这会降低处理效率。
3、保证聚集索引最小
每个聚集键值都是所有非聚集索引的叶结点记录，它越小，意味着每个非聚集索引的索引叶包含的有效数据越多，这对于提升索引效率很有好处。
4、覆盖索引
覆盖索引是指索引中的列包含了数据处理中涉及的所有列，覆盖索引相当原始表的一个子集，由于这个子集中包含了数据处理涉及的所有列，因此操作这个子集就可以满足数据处理需要。一般而言，如果大多数处理都只涉及某个大表的某些列，可以考虑为这些列建立覆盖索引。
覆盖索引的建立方法是将要包含的列中的关键列做为索引键列，将其他列做为索引的包含列（使用索引创建语句中的INCLUDE子句）。
5、适量的索引
当数据发生变化时，SQL Server会同步维护相关索引中的数据，过多的索引会加影响数据变更的处理效率。因此，只应该在经常使用的列上建立索引。
适量的索引还体现在对索引列的组合方式的控制上。例如，如果有两个列col1和col2，这两个列的组合会产生三种使用情况：单独使用col1、单独使用col2及同时使用col1和col2。如果有为每种情况都建立索引，则需要建立三个索引。但也可以只建立一个复合索引（col1, col2），这样能够依次满足col1+col2、col1、col2这三种方式的查询，其中，col2利用这个查询会比较勉强（还要配合单独的统计），可以视实际情况确定是否需要为col2建立单独的索引。
特别注意：
不要建立重复索引，目前最常见的重复索引是单独为某个列建立主键和聚集索引
与直接从表中提取数据相比，根据索引检索数据，多了一个索引检索的过程，这个过程要求能够尽量缩小数据检索范围，并且使用最少的时间，这样才能真正保证能够通过索引提高数据检索效率。
实现上述目的，对于索引键列的选择，应该遵循如下原则：
选择性原则
选择性是满足条件的记录占总记录数的百分比，这个比率应该尽可能低，这样才能保证通过索引扫描后，只需要从基础表提取很少的数据。
如果这个比率偏高，则不应该考虑在此列上建立索引。
数据密度原则
数据密度是指列值唯一的记录占总记录数的百分比，这个比率越高，则说明此列越适合建立索引。
在考虑数据密度的时候，还要注意数据分布的问题，只有经常检索的密度高时，才适合建立索引。例如，如果一张表有10万记录，虽然某个列不重复的记录有9万条，但如果经常检索的2万条记录，其不重复的列值才几十条的话，这个列是不太适合建立索引的。另一种情况是，整体数据密度不大，但经常检索的数据的密度大，例如订单的状态，一般来说，订单的状态就几种，但已经Close的订单往往占整个数据的绝大部分，但数据处理的时候，基本上都是检索未Close的订单，这种情况下，为订单的状态列建立索引还是比较有效的（SQL Server 2008中，可以为这种列建立具有更佳效果的筛选索引）。
6、索引键列大小
一般不宜为超过100Byte的列建立索引。
7、复合索引键列顺序
在索引中，索引的顺序主要由索引中的每一个键列确定，因此，对于复合索引，索引中的列顺序是很重要的，应该优先把数据密度大，选择性列，存储空间小的列放在索引键列的前面。
