mysql积累--索引

索引

参考：https://zhuanlan.zhihu.com/p/78982303

1. 概念

索引其实是一种排序的数据结构，能够帮助我们快速的检索数据库中的数据。索引建立经常使用的字段上（不仅仅是经常作为where条件的字段）

• 优点：索引可以提高查询的速度，但是创建和维护索引需要耗费时间，
• 缺点：索引会影响插入的速度，如果需要插入大量的数据时，最好是先删除索引，插入数据后再建立索引。

2. 分类

• index ---- 普通索引,数据可以重复，没有任何限制。
• unique ---- 唯一索引,要求索引列的值必须唯一，但允许有空值；如果是组合索引，那么列值的组合必须唯一。
• primary key ---- 主键索引,是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的同时创建主键索引。
• 组合索引 ---- 在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
• fulltext ---- 全文索引,是对于大表的文本域：char，varchar，text列才能创建全文索引，主要用于查找文本中的关键字，并不是直接与索引中的值进行比较。fulltext更像是一个搜索引擎，配合match against操作使用，而不是一般的where语句加like。
  注: 全文索引目前只有MyISAM存储引擎支持全文索引，InnoDB引擎5.6以下版本还不支持全文索引
  所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节，索引有两种存储类型，包括B型树索引和哈希索引。

3. 两种索引类型

3.1 hash索引

• 把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
• 哈希索引适合等值查询，但是无法进行范围查询
• 在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题（如果键值不是唯一的，需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；）
• 哈希索引也不支持多列联合索引的最左匹配规则

3.2 B+树索引
参考：https://blog.csdn.net/guanghuichenshao/article/details/81948438

聚簇索引和非聚簇索引，在查询数据的区别：

聚簇索引，就是将索引和数据放到一起，找到索引也就找到了数据，我们刚才看到的B+树索引就是一种聚簇索引。InnoDB引擎使用B+Tree作为索引结构(聚簇索引)

非聚簇索引就是将数据和索引分开，查找时需要先查找到索引，然后通过索引回表找到相应的数据。MyISAM引擎使用B+Tree作为索引结构(非聚集)

B+ Tree的叶子节点都可以存哪些东西：

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大

4. 生效条件

• 不跨列：对于复合索引（a,b, c），不要跨列或无序使用（最左匹配原则，a and c，a生效，c不生效；b and c，都不生效）。尽量使用全索引匹配(a,b,c)
• 不用不等号和is null: 对于复合索引（a,b, c）,复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。（a and b > 5 and c,a和b生效，c不生效）
• 不使用or:尽量不要使用or，否则索引失效(or左侧的字段失效)
• 不进行运算：不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效
• 不进行类型转换：尽量不要使用类型转换（显示、隐式），否则索引失效
• like不要以’%'开头：模糊查询时，使用%匹配时：’a%‘会使用索引，’%a‘不会使用索引， 这也是最左前缀匹配原则

尽量使用索引覆盖（using index）（a,b,c）

   select a,b,c from xx..where a=  .. and b =.. ;

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B_TREE。B_TREE 索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。