数据库索引事件存储 数据库索引存储在哪里

1、数据库索引：

1.1什么是索引

索引相当于数据库数据的一个目录，他把数据存储在某种数据结构中，可以迅速根据查询条件定位到数据。

1.2索引存储结构

常见的数据库索引一般存储方式为B树或者B+树，这是由二叉平衡树演变出的一种数据结构，相比于二叉树，B树更加矮胖，即存储同样数量的数据，B树高度更小，而这也可以有效的降低磁盘的IO次数。

1.3索引类型

聚集索引：

一个表只有一个聚集索引，类似字典的拼音目录，数据本身就是按照聚集索引的顺序存储的，在非聚集索引无法查到时就会跳到聚集索引查询数据。可以看到，聚集索引更适合数据量大但是不同的值没那么多的场景，但是默认聚集索引时建立在主键的，所以主键决定了数据的物理排序，可以看到主键建立索引并不符合聚集索引特点，不过，如果没有把握，还是不要修改聚集索引，防止影响数据库性能，Innodb一般建议使用自增主键，因为这样只需要往后插数据即可，避免了数据插入排序引起的大范围数据移动。

非聚集索引：

非聚集索引拥有和聚集索引同样的数据结构，一般都是B树，但是和聚集索引不同的是，非聚集索引不是数据的物理排序，也不存有全部的数据，所以非聚集索引查不到就需要回表，这也指导我们在设计索引的时候最好可以尽量减少在两个索引间的跳变以达到最大的效率。

2、Explain含义介绍：

Id：

一般join时id相同，含有子查询时子查询id更高，id越大优先级越高，执行时按优先级执行，id相同的由上到下顺序执行。

select_type:

SIMPLE:简单的select 查询，SQL中不包含子查询或者UNION。

PRIMARY:查询中包含复杂的子查询部分，最外层查询被标记为PRIMARY。

SUBQUERY:在select 或者WHERE 列表中包含了子查询

DERIVED:在FROM列表中包含的子查询会被标记为DERIVED(衍生表)，MYSQL会递归执行这些子查询，把结果集放到临时表中。

UNION:如果第二个SELECT 出现在UNION之后，则被标记为UNION；如果UNION包含在FROM子句的子查询中，则外层SELECT 将被标记为DERIVED。

UNION RESULT:从UNION表获取结果的select。

Table：

数据表名。

Type（重要）：

性能由好到差：system > const > eq_ref > ref > range > index > ALL

假设我们有

联合索引(end_time,bk_biz_id,strategy_id)，

唯一索引(event_id)，

主键(id)

ALL：

全表查询，性能最差。

select begin_time from alarm_event where is_ack=1;

这条查询无法命中任何索引，就会走ALL。

Index：

对整个该索引进行扫描，要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。

select strategy_id from alarm_event where bk_biz_id=1;

这条查询字段在索引存在，但匹配不到最左，所以走index。

Range：

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

select strategy_id from alarm_event where end_time BETWEEN '2019-11-25 05:03:00' AND '2019-11-25 16:00:00';

这条命中索引，且范围查询，会走range。

Ref：

非唯一索引扫描，mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，想实现这种查找，索引是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。

查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。

select bk_biz_id from alarm_event where end_time= '1990-01-01 00:00:00';

这条命中索引，且直接指定某值，结果不一定是一个，会在命中后往后小范围继续查，不一样即止。

eq_ref：

唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。

类似ref，只是匹配条数只有一条。

Const：

通过索引一次查到数据，该类型主要用于比较primary key或者unique索引，因为只匹配一行数据，所以很快；如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量。

select status from alarm_event where event_id='111';

event_id是唯一索引，查询类型为const。

System：

表中只有一条记录。

possible_keys：

显示可能应用在这张表的索引，一个或者多个。

查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

Keys（重要）:

实际使用到的索引。如果为NULL，则没有使用索引。

ken_len:

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的。

ref:

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

Rows（重要）：

根据表统计信息及索引选用的情况，大致估算找到所需记录需要读取的行数。

Extra（重要）：

Using index：

查询的列被索引覆盖，并且where筛选条件是可以命中索引，无需回表。

select bk_biz_id from alarm_event where end_time= '1990-01-01 00:00:00';

查询的字段索引可以覆盖，Using index。

Using where：

将对存储引擎层提取的结果进行过滤，过滤条件字段无索引。Using where本身其实和是否使用索引无关，它表示的是Server层对存储引擎层返回的数据所做的过滤。该过滤可能会回表，也可能不会回表，取决于查询字段是否被索引覆盖。

select bk_biz_id from alarm_event where end_time= '1990-01-01 00:00:00' and notify_status=1001;

可以命中索引，但notify_status无索引覆盖，走Using where。

Using index & Using where：

表示首先存储引擎通过索引检索将检索结果返回（不需要回表），然后在Server层再通过where语句对检索结果进行过滤。它和Using index的区别是多了一次Server层的where过滤操作。该过滤不需回表，因而效率很高。

select bk_biz_id from alarm_event where end_time= '1990-01-01 00:00:00' and strategy_id='111';

命中索引，但不完全匹配最左，需要在索引内过滤，走Using index,Using where，不需回表。

Using index condition：

Index Condition Pushdown(ICP)，是一种在存储引擎层使用索引过滤数据的一种优化方式。这里的“Pushdown”是指将原来在server层进行的table filter中可以进行index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter。使用ICP可以减少存储引擎层返回需要被index filter过滤掉的行记录，省去了存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。Using index condition仅适用于二级索引，一般发生在查询字段无法被二级索引覆盖的场景，该场景下往往需要回表。通过ICP，可以减少存储引擎返回的行记录，从而减少了IO操作。

select level,status from alarm_event where strategy_id='111' and end_time='2019-11-25 16:00:00';

命中索引，且过滤条件在索引内，引擎层进行过滤处理，剩余的行返回server进行回表select。

Using temporary：

查询过程中产生了临时表，例如使用join的子句如果order by的字段不在第一个表，则会产生一个临时表。

Using filesort：

当我们试图对一个没有索引的字段进行排序时，就是filesoft。它跟文件没有任何关系，实际上是内部的一个快速排序。

SELECT `alarm_event`.`strategy_id`, COUNT(`alarm_event`.`strategy_id`) AS `total` FROM `alarm_event` WHERE (`alarm_event`.`bk_biz_id` IN (3) AND (`alarm_event`.`end_time` BETWEEN '2019-11-17 02:29:55' AND '2019-12-17 02:29:55' OR `alarm_event`.`end_time` IS NULL)) GROUP BY `alarm_event`.`strategy_id` ORDER BY `total` DESC LIMIT 10;

这个可以命中索引，且会触发Using temporary; Using filesort。查询所得的数据不完全是原表数据就会产生临时表，排序部署索引字段就会触发内部排序，这个并不能直接指向性能的好坏。

注意：

以上几种并不能完全说明哪种效率更高，只是相对，还需要根据实际执行情况来看。

比如：针对索引("target_key", "status", "end_time"）

SELECT `alarm_event`.`id`, `alarm_event`.`create_time`, `alarm_event`.`event_id`, `alarm_event`.`begin_time`, `alarm_event`.`end_time`, `alarm_event`.`bk_biz_id`, `alarm_event`.`strategy_id`, `alarm_event`.`origin_alarm`, `alarm_event`.`origin_config`, `alarm_event`.`level`, `alarm_event`.`status`, `alarm_event`.`is_ack`, `alarm_event`.`p_event_id`, `alarm_event`.`is_shielded`, `alarm_event`.`target_key` FROM `alarm_event` WHERE (`alarm_event`.`bk_biz_id` IN (2) AND (`alarm_event`.`end_time` BETWEEN '2019-11-25 05:03:00' AND '2019-11-25 16:00:00' OR `alarm_event`.`end_time` IS NULL) AND `alarm_event`.`target_key` IN ('host|10.0.1.11|0')) ORDER BY `alarm_event`.`status` DESC, `alarm_event`.`end_time` DESC LIMIT 10;

Explain: type:ref, extra:Using where

SELECT `alarm_event`.`id`, `alarm_event`.`create_time`, `alarm_event`.`event_id`, `alarm_event`.`begin_time`, `alarm_event`.`end_time`, `alarm_event`.`bk_biz_id`, `alarm_event`.`strategy_id`, `alarm_event`.`origin_alarm`, `alarm_event`.`origin_config`, `alarm_event`.`level`, `alarm_event`.`status`, `alarm_event`.`is_ack`, `alarm_event`.`p_event_id`, `alarm_event`.`is_shielded`, `alarm_event`.`target_key` FROM `alarm_event` WHERE (`alarm_event`.`bk_biz_id` IN (2) AND (`alarm_event`.`end_time` BETWEEN '2019-11-25 05:03:00' AND '2019-11-25 16:00:00' OR `alarm_event`.`end_time` IS NULL) **AND `alarm_event`.`status` in (10, 20,30)** AND `alarm_event`.`target_key` IN ('host|10.0.1.11|0')) ORDER BY `alarm_event`.`status` DESC, `alarm_event`.`end_time` DESC LIMIT 10;

Explain: type:range, extra:Using index condition;Using where

type是ref，下面是range，按道理ref性能高于range

但是分析下，上边为什么是ref，此时以上两个查询命中的是同一个索引("target_key", "status", "end_time"）。

当不带status查询时，只能命中左边第一个target_key，然后在根据索引找到target的点，再根据这个点查询，这种就是ref，但是这种在拿到数据集后，还需要根据status和end_time排序，这两个有索引，不需要回表排序，但是速度仍然不快；

当带status查询时，可以直接命中整个索引("target_key", "status", "end_time"），不需要拿到数据集后再排序，因为有范围查询，所以是range，虽然ref效率高于range，但是数据库查询不是一个一步操作，整体的效率是最重要的。

另外，无论如何上面两个都需要回表查询，因为查询的字段较多，索引无法覆盖；而且索引没有bk_biz_id，所以都有Using where，且该where也会回表，但是因为此时经过处理后数据量有限了，对效率影响不大。

实际测试：

下面的查询速度远快于上面的，所以，并不能单纯根据某条件判断效率，但是explain仍然能给我们提供很好的参考，具体结果还是需要根据实际情况得出结论。

3、索引设计注意点：

3.1、 避免排序顺序不一致。

比如：order_by=['status', '-end_time']

排序类型不一致会导致无法使用索引排序，效率接近全表扫描，应该避免。

3.2、 可以的话，数据库索引字段最好设计为整形。

比如status字段只有三个值（ABNORMAL，RECOVERED，CLOSED），完全可以将其替换为对应数值，这会大大提高索引效率。

3.3、 索引字段避免为NULL。

索引是有序的。当一个空值进入索引时,无法确定其在索引中的位置。而且空值与空值不相等，当检索一个空值时,由于空值与空值并不相等,所以,无法在索引中找到期望的空值索引，对查询效率影响较大。

3.4、尽量走覆盖索引

如果可能，索引尽量走覆盖索引，即上面提到的using index，但是实际情况不可能都能走覆盖，更多的是ref，range，索引需要尽量让我们筛选出的结果集小，减小回表次数。

3.5、尽量减小回表

索引设计时，应尽量减小回表，尤其是在筛选条件或者排序时进行回表，因为数据库时优先使用where去匹配索引拿结果集，所以设计where时就需要多考虑，比如where条件可以大部分匹配到索引，且未匹配到的也能在索引中找到；比如where条件可以带order by条件，这些都可以提高查询效率。

3.6、尽量不要设置重复索引和冗余索引

重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引，mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

冗余索引指a索引的最左保护b索引，这种情况b索引其实时没有意义的，因为根据索引最左匹配原则，a索引可以完成b索引的能力。

3.7、区分度大的优先放左边。

索引的本质时排序，mysql的大多数索引的存储都是B树，如果区分度达到1，则可以很快就定位到数据，走的时eq_ref；区分度未达到1，但是也很大时，根据算法定位到数据后，只需要向后查询少量数据，这时是ref，但是如果区分度很低，那么定位到首位数据后还有大量的数据需要去检索遍历，效率更接近index，意义就不是很大了。

3.8、索引不要太多，索引字段不要太多。

本身索引数，字段数其实没有强制的限制，但是因为索引一般情况都是B树，插入修改删除都会触发树的重排，索引太多容易影响修改效率，而且索引的维护也会损耗空间。具体设置多少，还是需要根据业务场景设置，最合适的就是好的。

3.9、很难触发索引的几种常见情况。

1）在索引列使用数学或函数运算。

数据量优化器会进行评估，数据量小的时候，偶尔可以命中索引，数据量大的时候，一般都无法使用索引，因为优化器认为使用索引的损耗是超过全表扫描的，而且数据库本身并不擅长此类事情，应该把这些运算放到代码里。

2）使用不等于 (!=、<>、not in 、not like等)。

道理类似，数据库优化器认为使用索引消耗更大。=或in可以根据条件命中数据，但是相反的情况，命中数据后筛选的数据量很大，而且需要进行一系列的排除组合，对效率影响很大，还不如全表扫描。

3）like以通配符%开头的情况。

字符串索引是从左开始建立，不知道最左是什么无法使用索引，只能走全表扫描。

3.10、前缀索引无法应用于order by。

order by的时候，不能直接使用索引顺序了，因为前缀索引只是按前缀排序，后面的就不是正常顺序。