数据库的索引有哪些

从数据结构上分类

B+Tree索引

Hash索引

FullText索引

从物理存储结构上分类

聚簇索引

非聚簇索引

从字段上分类

唯一索引

主键索引和唯一索引对字段的要求是要求字段为主键或unique字段，

而那些建立在普通字段上的索引叫做普通索引，既不要求字段为主键也不要求字段为unique。

主键索引

建立在主键字段上的索引

一张表最多只有一个主键索引

索引列值不允许为null

通常在创建表的时候一起创建

普通索引（二级索引、辅助索引）

主键索引和唯一索引对字段的要求是要求字段为主键或unique字段，

而那些建立在普通字段上的索引叫做普通索引，既不要求字段为主键也不要求字段为unique。

前缀索引

前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。

例如，可以对persons表中的name(varchar(16))字段 中name的前5个字符建立索引。

create index index_name on persons (name(5)) comment '前缀索引';

前缀索引可以建立在类型为

• • char
  • varchar
  • binary
  • varbinary

的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

从组成索引的字段个数上分类

单列索引

建立在单列上的索引，仅一个字段

联合索引（复合索引）

建立多个字段的联合索引，需要注意的是，虽然是多个字段的索引，但并不是建立了多个索引视图，还是一个索引，他们是按照建立索引的书写顺序排序

什么是覆盖索引

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

如果没有索引，怎么去寻找数据

A：假设根据主键查找一条数据，而且假设那个表总共就一个数据页，那么就太简单了！首先到数据页的页目录里根据主键进行二分查找，找到主键对应的槽位，然后去槽位里遍历槽位里每一行数据，就能快速找到那个主键对应的数据了。

A：如果不跟据主键找的话，那就没办法使用主键的那种页目录来二分查找的，只能进入到数据页里，根据单向链 表依次遍历查找数据了，这就性能很差了。

如果有很多个数据页的话，如果没有索引，无论是根据主键还是非主键查询都性能差因为如果第一个数据页里没有想要的数据，就得从第二个数据页里找，这似乎就是全表扫描了。而且数据页都是加载到buffer pool里了，占内存。最坏的情况下，得把所有数据页里的每条数据都得遍历一遍，才能找到需要的那条数据，那条数据在最后一个数据页的最后面存着，这就是全表扫描了！

数据页、页目录、槽位是什么关系

使用数据库的时候，用的比较多的都是查询数据，在写每一条SQL的时候，都会考虑这条数据是否会走索引，会不会导致全表扫描，每次都会关注查询的慢SQL等等，那么MySQL是怎么进行查找数据的？先了解下数据文件在磁盘中是怎样进行物理存储。 数据页在磁盘文件物理存储中，数据页之间是组成双向链表，数据页内部的数据行是单项链表，而且数据行是根据主键从小到大排序，然后在每个数据页都有一个页目录，里面根据数据行的主键存放目录，同时数据行是是被分散到不同的槽位里去，这样的话，每个数据页中的主键与具体存放的槽位有目录的关系，如下图所示：

什么是索引下推？

首先mysql分为三层：service层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索等、引擎层、文件系统层。

索引下推就是把之前服务层做的检索筛选工作，现在让引擎层去先做了，这样可以有效减少回表次数，也就是要减少IO操作，提升性能；

索引下推使用条件

• 只能用于range、 ref、 eq_ref、ref_or_null访问方法；
• 只能用于InnoDB和 MyISAM存储引擎及其分区表；
• 对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

• 引用了子查询的条件不能下推；
• 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

什么是回表

所谓回表是指，查询用到的非聚簇索引里面的列，不能满足查询结果列的需要，需要回表，使用聚簇索引再查一遍。如果条件里有主键，肯定用聚簇索引，一次就能将所有列查出来，没有回表一说。

只有在使用非聚簇索引，索引列不能覆盖需要查询的列（即不是覆盖索引），只能回表-用非聚簇索引中的叶子节点的data（即主键），再查一次聚簇索引，获取需要的列。

mysql查询优化

建表设计方面：

1、状态字段用数值表示代替用英文字符，好处是数据库查询引擎比较时肯定是比较数字的快，字符串还要一个一个比较

2、设计表时存储空间可以用varchar代替char，因为char是固定长度的，存在一定的空间的浪费；用tinyint代替int，因为tinyint占用1个字节，int占用4个字节

3、一个表中索引最多不要超过6个，索引不要在重复值很大的列上建立，如：性别

4、对作为查询条件或者order by排序字段上建立索引

sql语句优化方面

1、用like可能会导致索引失效，如 like '%xxx'会导致索引失效，但是select * like 'xxx%'右边相似会走全索引扫描，如果用覆盖索引，可以解决索引失效的问题可以用覆盖索引

2、条件列where上尽量不要用 != ,is null ,is not null，函数，计算表达式等这些

3、不要用where or 条件，可以用union all 来代替

4、where in的条件 可以用between/exists代替in

5、select * 不要用，需要什么字段找什么字段