Mysql索引的一些基本问题

1. 什么是索引？索引有什么用？

索引(Index)是帮助MySQL高效获取数据的数据结构。能够增加查询数据和对数据排序的速度。但是在对表的插入，删除和更新的时候需要对索引进行维护，所以索引也不能创建太多

2. 索引有哪些类型

普通索引：最基本的索引，它没有任何限制，用于加速查询。

唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

按照物理位置还分为聚簇索引和非聚簇索引

聚簇索引：实际存储的循序结构与数据存储的物理机构是一致的

非聚簇索引：物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系

简单来说就是数据库表中数据物理储存结构是按照聚簇索引进行排序的，和非聚簇索引没有关系。

对于聚簇索引，一张表中只有一个，一般来说都是表中的主键。如果在设置主键之前没有设置聚簇索引，那么在设置主键时会默认以主键作为聚簇索引进行排序。
对于非聚簇索引，一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引

3. 何时使用聚簇索引和非聚簇索引？

动作描述	使用聚簇索引	使用非聚簇索引
列经常被分组排序	使用	使用
返回某范围内的数据	使用	不使用
一个或极少不同值	不使用	不使用
小数目的不同值	使用	使用
大数目的不同值	不使用	使用
频繁更新的列	不使用	使用
外键列	使用	使用
主键列	使用	使用
频繁修改索引列	使用	使用

4. 怎么避免索引失效？

1. like以%开头时，索引无效；当like前缀没有%，后缀有%时，索引有效
   
   

2. or语句前后没有同时使用索引。当or左右查询字段只是一个索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
   

3. 组合索引，不是第一列索引，索引失效
   
   

4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
   
   

5. 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
   

6. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
   

7. 对索引字段进行计算操作、字段上使用函数。

8. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

5. MYSQL索引方法有哪些？

mysql目前主要支持的索引方法有：B-Tree，Hash

B-Tree: B-Tree是最常见的索引类型，所有的列都是排序过的，每个叶节之间距离相等。因此B-Tree适合查询范围的数据，可以直接支持数据排序。

B-Tree在Mysanyo引擎中，索引文件是记录磁盘的地址；而在InnoDB中有索引，所以可以直接根据索引记录，直接找到叶节点的数据域。

Hash索引:

• 特点：

1. 检索效率高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，需要多次IO访问。

2. 由于Hash索引比较的是进行Hash运算之后的Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。因此，在任何时候都不能避免全表扫描。

3. 仅支持精确查询，例如："=",“IN"和”<=>"，不能使用范围查询：由于Hash索引比较的是进行Hash运算之后的Hash值，无法判断返回，所以它只能用于等值的过滤，不能用于基于范围的过滤。

4. 不支持排序：由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

5. 只有Memory引擎支持显式的Hash索引，但是它的Hash是nonunique的，冲突太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引

6. InnoDB也是支持hash索引，但是，我们必须启用，hash索引的创建由InnoDB存储引擎引擎自动优化创建，所以无法人工干预哈希索引的创建。