mysql索引

索引概念

索引是一种帮助mysql提高查询效率的数据结构，就类似是目录

优点：可以大大提高数据的查询速度

缺点：

-存储索引会占用数据库的数据空间

-维护索引会影响数据库的性能，当对表的数据进行增删改时，由于数据发生了变化因此需要重新维护索引，重新生成索引

索引分类(重点)

innodb

-主键索引，当为表设置主键后会自动建立主键索引，innodb为聚簇索引

-普通索引(单列索引、单值索引)，即一个索引只包含单列，一个表可以有多个单列索引

id name index age index ，这里name、age都为普通索引

-唯一索引，索引列的值需要唯一，可以存在一个空值，但null值可以存在且重复，因为null表示未知，因此两个null值即不相等也相等

-复合索引，即一个索引包含多个列

id (name age)index，name和age时

myisam

-全文索引

在定义索引的列上全文查找，允许在这些索引列上插入空值和重复值

如果数据库中存储的数据为blog或weibo之类的长篇文本，用户一般希望以模糊匹配（where a like “%xxx%”）的方式来查询，由于条件的前缀并不是固定的值，所以没法利用B+树索引进行比较查找，故而效率会很低，为了解决这类问题，引入了全文索引。 全文索引（Full-Text Search）是将数据库中某段文字（或词语）快速查找出来的技术。其本质是一种映射表结构，在表中存储了单词与单词所在文档的位置的映射关系（其key是对应的数据（单词），value是数据的位置），相对于B+树索引的key-val来讲，顺序是倒过来的，所以也叫倒排索引。这个索引表，也叫做辅助表

主键索引跟唯一索引的区别

主键索引的列值不能为空，唯一索引列值可以为空

索引的基本操作

-创建主键索引 自动创建 建表时创建

create table t_user(id varchar(20) primary key,name varchar(20));

-创建单列索引

建表时创建，注意此时索引名需要跟列名一致

create table t_user(id varchar(20) primary key,name varchar(20),key(name));

建表后创建

create index 索引名 on t_user(name);

删除索引

drop index 索引名 on 表名

-创建唯一索引

建表时创建

create table t_user(id varchar(20)) primary key,name varchar(20),unique(name))

建表后创建

create unique index nameIndex on t_user(name)

-创建复合索引

建表时创建

create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));

建表后创建

create index 索引名 on t_user(name,age)；

1）最左前缀原则

最左匹配原则：最左优先，以复合索引最左边为起点任何连续的索引都能匹配上。使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。使用order by 进行排序时，同样可以使用索引

例如现在有一个复合索引a,b,c

其实就是a绝对有序，b、c局部有序，即在a确定某个值情况下，b跟c是有序的；当知道索引是什么之后，要看它的条件是否有使用到索引，特别是在有order by的情况下

使用情形：

1）a，b，c三个都出现，就会使用到联合索引，因为mysql在查询过程中会动态调整查询字段顺序以利用查询，a只要出现就好，跟a放在哪没关系

2）a，a和b，a和c三种情形下都会使用索引，可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引

3）like模糊查询

当%开头则不会使用索引，“A%”会使用索引，因为前缀是排好序的，因此索引可以找到

而“%A”和“%A%”不会使用到索引，因为无法排序

4）匹配范围值

例如：a>1 and a<3 and b>1最左边1<a<3是可以使用到索引的，而b>1不能使用到索引，需要逐条过滤

当然如果改成a=1，那也是可以走联合索引的

5）排序

select * from table order by a,b,c limit 10;
select * from table order by a,b limit 10;

上面这些都可以使用到索引

A>5 AND B=2 ——当范围查询使用第一列，查询条件仅仅能使用第一列

A=5 AND B>6 AND C=2 ——范围查询使用第二列，查询条件仅仅能使用前二列

上面这些条件将用上部分组合索引查询

select * from my_table where col_b=1 order by col_a

如果col_b是作为过滤条件，则col_b前面的字段都应该在过滤条件中，col_a和col_b都走不了索引，因为col_a在组合索引左边，但是col_a不在查询条件中

索引的底层原理

当我们往数据表(有主键)里插入无序数据时，为什么查询时是有序的

因为mysql底层自动为主键创建主键索引，主键索引会对该列进行排序

为什么要排序

因为排序查询速度较快，例如查询id=3时，只需要按照顺序查找到3即可，而不需要大海捞针全靠运气查询

但是这样查询速度会很慢，因此mysql对索引又进一步优化

基于页的形式进行管理，如查找id=4的，那么将会在页目录查找，再去数据页找，而不是一个个节点遍历

无论是页目录还是数据页存储大小都是16KB

上面说的这种结构是B+Tree，innodb存储引擎就是用B+Tree实现索引结构的

而B-Tree结构每个节点不仅包含数据的key值，还包含数据的data值，由于每一页存储空间是有限的(16KB)，如果存储了data数据那么会导致每一页能存储的key数量很小，当存储量大时会导致B-Tree深度较大，增大磁盘IO次数，进而影响查询效率

而B+Tree中，所有数据都是存放在以键值大小为顺序的叶子节点上，而非叶子节点只存储key值，这样可以增加非叶子节点存储的key值数量，增大每一层的存储数量，降低B+Tree的高度

B+Tree相对B-Tree的两点不同

-非叶子节点只存储键值信息

-叶子节点上的data就是数据本身，代表数据的物理存储顺序;

-所有叶子节点之间都有一个指针且所有数据都放在叶子节点上

B+Tree高度一般在2-4层，mysql的innodb存储引擎在设计时顶层是常驻内存，因此查找某一个键值的行记录时醉倒需要1-3次IO操作

聚簇索引和非聚簇索引

聚簇索引(主键索引)：将数据和索引放到一块，索引结构的叶子节点保存了行数据，一个表里只能有一个聚簇索引，默认是主键索引。据主键在B+树中依次比较，直到找到底层对应的数据页（叶子节点），然后通过二分查找的方式在数据页中定位到目标行，故其对针对主键的排序查找和范围查找的速度是特别快的。

非聚簇索引(普通索引、二级索引、辅助索引)：将数据和索引分开存储，索引结构的叶子节点指向了数据对应的位置

辅助索引data域存储相应记录主键的值而不是地址，所以辅助索引找数据会有一次回表的查询

首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录

InnoDB

innodb有聚簇索引和非聚簇索引

• InnoDB使用的是聚簇索引，通过主键索引就可以直接找到存储在B+树叶子节点上存储的数据，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据

• 若对Name列(辅助索引)进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

• 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似oracle中的RowId）**来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可
  
  MYISAM

myisam只有非聚簇索引，无论是主键索引B+tree还是辅助索引B+tree，叶子节点上都是存储一个指向真正数据的地址，数据不存在b+tree上

为什么要使用索引

1）通过创建唯一性索引，可以保证数据库表中 每一行数据的唯一性

2）可以大大加快数据的检索速度

3）帮助服务器避免排序和使用临时表

4）将随机IO变为顺序IO

索引这么好，为什么不一个列创建一个索引呢

1）当队表中的数据进行增删改时，索引也需要动态维护，索引太多降低数据的维护速度

2）索引需要物理空间，如果索引太多那么空间会更大

3）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

索引是如何提高查询速度的

索引可以将无序的数据变成相对有序的数据，就像查目录一样

使用索引有什么注意事项

1）数据量少的表不要使用索引，使用了改善也不大

2）删除长期未使用的索引，不用的索引存在会造成不必要的性能损耗

3）频繁更新的字段不要使用索引

4）模糊查询以%开头的则不能使用索引

5）如果是组合索引，如果不满足最左匹配原则，则全表搜索

6）如果where语句里的判断条件出现了对null的判断，即可能出现null了

当索引出现null值时，无法确定该null值应该放在哪

7）应避免where语句中使用！=或<>操作符，因为大几率mysql会判断全表搜索比用索引快

8）应避免where语句上对索引字段进行函数操作，以及字段计算

9）若使用了order by，那order by后的字段顺序必须跟联合索引顺序一致，否则全表查询

索引主要使用的数据结构是什么

1）哈希索引：对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询速度快，其余大部分场景，建议使用BTree索引

2）BTree索引：BTree索引使用的就是B+Tree。但对于主要的两种存储引擎的实现方式是不同的

MyISAM和InnoDB实现BTree索引方式的区别

1）myisam上b+tree叶子节点的data域存放的是数据记录的地址，根据地址去读取相应数据记录。这也被称为“非聚集索引”（索引文件跟数据文件分开）。

2）innodb上，主要可以分成两类看，当叶子节点上的key是主键时，则data域保存了该行上的所有数据,该索引也叫做聚集索引。当key不是主键时，叶子节点上的data域存储的是主键的值而不是地址，则需要先取出主键的值，再走一遍表

什么是聚集索引，非聚集索引？

聚集索引：也是主键索引，索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。

非聚集索引：表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。myisam的b+tree的data域存储的是数据存放的地址

什么叫做覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称之为覆盖索引，我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作

ps：使用实例：

现在我创建了索引(username,age)，在查询数据的时候：select username , age from user where username = ‘Java’。要查询出的列username、age都存储在索引树里，所以，就不用回表

select id，username from user where username = ‘Java’也不需要回表

但如果查询一个sex字段则需要回表

如果数据库给ID,name,age构建了各自的主键和普通索引，假设select id,name,age from xxtable where name = ‘张三’，去主键索引树里找

在选择索引和编写利用这些索引查询时，有什么原则

1）单行访问是很慢的

特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立)。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了许多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率

2）按顺序访问范围数据是很快的

原因1：顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘)

原因2：如果服务器能够按需要顺序读取数据，那么就不需要额外的排序操作，并且GROUP BY 查询也无须再做排序和将行按组进行聚合计算了

3）索引覆盖查询是很快的

如果一个索引包含了查询所需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问

Myisam索引

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址

主键索引

表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。简单分析下查询时的磁盘IO情况：

场景一：根据主键等值查询数据

-先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

-将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

-检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于30的索引项。（1次磁盘IO）

-从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录。（1次磁盘IO）

-将记录返给客户端。

磁盘IO次数：3次索引检索+记录数据检索

**备注：**以上分析仅供参考，MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了分析索引的使用过程

辅助索引

在 MyISAM 中，辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据

innodb索引

主键索引

每个InnoDB表都有一个主键索引(聚簇索引) ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

-在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引

-如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引

-如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增

场景一：等值查询数据

-先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

-将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

-检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返-回给客户端。（1次磁盘IO）

磁盘IO数量：3次

辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。以表user_innodb的age列为例，age索引的索引结果如下图

底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录

场景一：等值查询的情况

覆盖索引

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。可以看一下执行计划：

覆盖索引的情况：

未使用到覆盖索引：

索引失效的场景

1、where语句包含or时，索引可能会失效；当or左右两边的查询列命中相同的索引时就不会失效

-- 假设user表中的user_id列有索引，age列没有索引
-- 能命中索引
select * from user where user_id = 1 or user_id = 2;
-- 无法命中索引
select * from user where user_id = 1 or age = 20;
-- 假设age列也有索引的话，依然是无法命中索引的
select * from user where user_id = 1 or age = 20;

可以根据情况尽量使用union all或者in来代替，这两个语句的执行效率也比or好些

2、where语句中索引列使用了负向查询，可能会导致索引失效

负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。其实负向查询并不绝对会索引失效，这要看MySQL优化器的判断，全表扫描或者走索引哪个成本低了

3、索引字段可以为null，使用is null或is not null时，可能会导致索引失效

其实单个索引字段，使用is null或is not null时，是可以命中索引的

4、在索引列上使用内置函数，一定会导致索引失效

比如下面语句中索引列login_time上使用了函数，会索引失效：

select * from user where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;

5、隐式类型转换导致的索引失效

字段类型和查询数据的值类型不一致，会导致字段上的索引失效。

select * from my_table where col_a=1  
select * from my_table where col_b=1603296000000;

col_a是字符类型，使用了数字类型进行查询。

col_b是datetime类型，针对datetime/date/time类型，MySQL增删查改都要基于字符串形式日期去处理，否则MySQL就需要额外进行转换。（虽然底层储存的是数字类型，但是并不是存储时间戳，底层是处理是统一将外部传入的字符串进行转换，比如是date类型通过将 “2021-12-01” 字符串转数字 20211201 这种形式去存储）

6、对索引列进行运算，一定会导致索引失效

运算如+，-，*，/等，如下：

select * from user where age - 1 = 10;

优化的话，要把运算放在值上，或者在应用程序中直接算好，比如：

select * from user where age = 10 - 1;

7、like通配符可能会导致索引失效

like查询以%开头时，会导致索引失效。解决办法有两种

将%移到后面，如

select * from user where `name` like '李%';

利用覆盖索引来命中索引

select name from user where `name` like '%李%';

8、联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效

当创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。比如下面的语句就不会命中索引：

select * from t where k2=2;
select * from t where k3=3;
select * from t where k2=2 and k3=3;

下面的语句只会命中索引(k1)：

select * from t where k1=1 and k3=3;

9、不等于、不包含

索引：index(col_a,col_b,col_c)

select * from table where col_a=1 and col_b not in (1,2)  
select * from table where col_a=1 and col_b != 1

10、 选择性过低，直接走全表

选择性过低会导致索引失效。由于通过二级索引查询后还有回表查询的开销，如果通过该字段只能过滤少量的数据，整体上还不如直接查询数据表的性能，则MySQL会放弃这个索引，直接使用全表扫描。底层会根据表大小、IO块大小、行数等信息进行评估决定

索引：index(col_a)

select * from table where col_a>'2017-10-22'

11、asc和desc混用

索引：index(col_a,col_b,col_c)

select * from my_table where col_a=1 order by col_b desc,col_c asc

desc 和asc混用时会导致索引失效，不建议混用