mysql索引

1、什么是索引
索引是一种数据结构，数据库索引，就是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库中的数据，索引的实现通常使用B树和B+树。

2、索引类型
（1）主键索引：数据列不允许重复，不允许为null，一个表只能有一个主键
（2）二级索引：辅助索引，因为二级索引的叶子节点存储的数据是主键，也就是说通过二级索引，可以查找到主键的位置。
（3）唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。
（4）普通索引：对字段没有限制
3、索引的基本原理
原理就是把无序的数据变为有序的数据
（1）把创建了索引的列的内容进行排序。
（2）对排序结果生成倒排表
（3）在倒排表内容上拼上数据地址链
（4）在查询的时候，先拿到倒排表内容，再读取数据地址链，从而拿到具体数据。

4、什么情况下不要使用索引
（1）数据唯一性差的字段不要使用索引。（男女性别就2个）
（2）频繁更新的字段不要使用索引。
（3）字段不在where语句出现时不要添加索引
（4）数据量少的情况下不要添加索引（使用了改善不大）

5、索引什么时候失效

5、什么是最左前缀原则
（1）就是最左优先，在创建多列索引时，根据业务需求，where子句中使用最频繁的一列放在最左边。
（2）最左前缀匹配原则：mysql会一直向右匹配直到遇到范围查询（>,<,between,like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4，如果建立(a,b,c,d)的顺序索引，d是用不到索引的，如果建立(a,b,d,c)的索引就都可以用到，a,b,d的顺序可以任意调整。
（3）=和in可以乱序，比如a=1 and b=2 and c=3建立（a,b,c）索引可以任意顺序，mysql的查询优化器可以帮你优化成索引可以识别的形势。

6、B树和B+树的区别
（1）在B树种，可以把键和值存放在内部节点和叶子节点，但在B+树，内部节点都是键，没有值，叶子节点同时存放键和值。
（2）B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。
（3）B树的检索过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，查找就结束了。而B+树的查找效率就很稳定，任何查找都是从根节点到叶子节点。

7、Hash索引和B+树所有有什么区别或者说优劣呢?
（1）首先知道hash索引和B+书索引的底层实现原理
hash索引的底层就是hash表，进行索引时，调用一次hash函数就可以获得相应的键值，然后回表中查询获得实际的数据。
B+树索引的底层是多路平衡查找树，对于每一次查询都从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。
不同点：
（1）hash索引进行等值查询更快，但是无法进行范围查询
因为在hash索引经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树是多路平衡查找树，天然支持范围。
（2）hash索引不支持使用索引进行排序
（3）hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理是因为hash函数不可预测，AAAA和AAAB没有相关性。
（4）hash索引任何时候都避免不了回表查询数据，而B+树可以在（聚簇索引，覆盖索引）的时候，只通过索引完成查询。
（5）hash索引虽然在等值查询上较快，但是不稳定，因为当某个键值存在大量重复的时候，发生hash碰撞，此时的效率可能极差。而B+树的查询效率比较稳定。

8、为什么索引用B+树而不用红黑树？
（1）因为树的查询时间与树的高度有关，B+树是一颗多路平衡查找树，每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读功能。每次读取磁盘页时都会读取一整个节点。可以降低树的高度，提高查找效率。
（2）因为索引通常比较大，储存在磁盘中，无法一次将全部索引加载到内存中，每次只能读取一个页到内存中。而且磁盘每次都会预读，即使只需要一个字节，磁盘也会把这页的数据放到内存中。红黑树这种结构，明显高度要高得多。即使逻辑上距离很近的节点，物理上也离得很远，因此红黑树读取I/O的次数也为H。

9、聚簇索引和非聚簇索引的区别
（1）聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据。
（2）非聚簇索引：将数据存储与索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时，在内存中直接搜索索引，然后通过索引找到磁盘相应数据。

10、覆盖索引
（1）如果一个索引包含所有需要查询字段的值，我们就称之为覆盖索引。
（2）在InnoDB存储引擎中，如果不是主键索引，叶子节点中存储的是主键加列值。最终还是要回表，就是通过主键在查找一次，这样的查找速度较慢。覆盖索引就是要把查询出的列和索引是对应的，不做回表操作。InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚簇索引中的记录。

11、组合索引和失效情况
组合索引就是把单列的索引组合起来，组合索引会遵守最左前缀匹配原则。
组合索引不论如何改变顺序，都会走索引，前提是在where语句之后。
必须有组合索引里的第一个字段，但是与顺序无关，例如a,c或c,a，这种场景是可以命中索引的。但是，b,c或c,b这种是不会命中索引的。
测试sql : desc select id from account where a=1 and b=2 and c=3 ;
desc select id from account where a=1 and b=2 ;
desc select id from account where a=1 ;

这三种都会走索引
desc select id from account where b=2 and c=3;
desc select id from account where c=3;
这两种情况都是不命中的.

12、使用where时哪些语法尽量不要用？
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
in 和 not in 也要慎用，否则会导致全表扫描
应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
13、主键使用自增ID还是UUID？
推荐使用自增ID，不要使用UUID。

因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。
总之，在数据量大一些的情况下，用自增主键性能会好一些。
关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

mysql建立索引需要注意的地方
（1）在经常需要搜索的列上，可以加快搜索的速度；
（2）在经常使用在 where 子句的列上面创建索引，加快条件的判断速度。 （3）在经常需要排序的列上（order by）创建索引，因为索引已经排序，这样查询可以利用索引的
排序，加快排序查询时间。
（4）对于中到大型表，索引都是非常有效的，但是特大型表的话，索引维护开销会很大
（5）在经常在连接的列上，这些列主要是一些外键，可以加快连接的速度。
（6）避免 where 子句中对字段施加函数，这会造成无法命中索引。
（7）在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主
键。（8）将打算加索引的列设置为 NOT NULL，否则导致引擎放弃使用索引而进行全表扫描。 （9）删除长期未使用的索引，不用的索引的存在会造成不必要的性能循环，MySQL5.7 可以通过查
询 sys 库的 chema_unused_indexes 视图来查询哪些所以从未被使用。
（10）在使用 limit offset 查询缓慢时（可以通过慢查询日志进行分析），可以借助索引来提高性能。

创建索引的原则
创建索引适合在where子句中出现的列
如果是数量比较少的话就没必要添加索引。
不要过度使用索引，因为索引也要占用额外的磁盘空间
定义有外键的数据列一定要建立索引。
尽量扩展索引，而不是新添加索引。
更新频繁的字段不适合添加索引。
区分度少的数据不适合做索引。