索引及其explain效果

如需转载请注明出处https://my.oschina.net/feistel/blog/2996921

先看个上个索引效果的实例。

mysql>explain select * from comment_infos 
where article_id=212 and status=1
order by submit_time desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comment_infos
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 353
     filtered: 1.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql>create index article_id_idx_status on comment_infos(article_id,status);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>explain select * from comment_infos 
where article_id=212 and status=1
order by submit_time desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comment_infos
   partitions: NULL
         type: ref
possible_keys: article_id_idx_status
          key: article_id_idx_status
      key_len: 10
          ref: const,const
         rows: 5
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

删除索引
mysql>alter table comment_infos drop index article_id_idx_status;

怎么知道建了索引有没用，效果怎么样？
explain，查看执行计划，在查询语句前面加上explain。

1.type为ALL表示全表扫描，为ref是表示使用索引
2.key为NULL表示没有使用索引
3.rows表示扫描的行数，只是一个估算值，使用索引后从353降低到5行。
4.Extra中的Using index condition表示使用ICP优化（默认开启，把数据过滤放到存储引擎层，减少访问基表和Server层访问引擎的次数）
    相应的还有MRR（默认开启，索引中查找的字段存在相同值，排序其主键，达到顺序IO）
    BKA(默认关闭，读取join表记录时使用顺序IO)

----------------------------------------------------------------------------------------------------------------

1

索引是对数据库表的一列或多列的值进行排序的一种结构。

屁话：索引相当于书的目录，可以快速...

人话：对于表的一个字段（列），用该字段（或字段们）上的值构建成B+树，当where该字段（字段们）时就在该B+树上查找，而主键会自动创建索引，这就是为什么最好用整形作为主键的原因。那为什么B+树快呢？

神话：先说BST树（二分查找树），为了能动态查找（可以插入删除）而存在。像什么折半查找、分块查找属于静态的。但是，BST上的树查找效率仍然不够高，最坏情况下需要的查找次数是树的高度，这时AVL树(平衡二叉树)，通过平衡因子动态降低树的高度从而提高查找效率。而B树，更是降低了树的高度，一个节点可以包含多个关键字，并且树的度数（分叉数）不一定为2，对于B+数查找一次需要遍历一遍树（树的高度）。能同时满足动态查找和高效查找的B+树，岂不乐哉。

----------------------------------------------------------------------------------------------------------------

2

主键会自动创建索引，那么主键和索引的区别？

1.主键是索引，但是索引不是主键。
2.主键索引是聚集索引，而不是主键的字段，就叫作非聚集索引或普通索引。（区别仅为名字不同）
3.主键索引必须唯一，索引没有这个限制，在不重复的列中建立索引就是唯一索引。
4.主键索引不能包含null值，索引没有这个限制。
5.表中可以有多个索引，但是主键只有一个。

以上
主键索引是聚集索引，其余的都叫作非聚集索引（普通索引）
唯一索引，其值不能包含重复的

----------------------------------------------------------------------------------------------------------------

3

覆盖索引

B+树中的叶子节点除了带有索引的值外，还包含有主键的值，当使用索引查找时，实际上是查找该叶子节点上的主键值，再用主键值返回原表中查找相应的行。

当select只包含有主键值时（比如select id这样的），索引完后直接返回，而不再需要到原表中获取其他的字段值。

覆盖索引的效率非常之高。

----------------------------------------------------------------------------------------------------------------

4

联合索引

两个或两个以上的字段创建的索引，就叫联合索引，毫无波澜。

最左前缀原则：比如我建立的联合索引（B,C,D），那么其实也自动建立了普通索引B和联合索引（B，C）。

----------------------------------------------------------------------------------------------------------------

5

前缀索引
哈希索引

顾名思义。

----------------------------------------------------------------------------------------------------------------
至此，共勉，如需转载请注明出处https://my.oschina.net/feistel/blog/2996921