1 什么是索引

索引，是存储引擎快速找到记录的一种数据结构

2 索引类型

查看索引：

show index from table `tb_name`;

从物理存储来看，索引分为聚集索引和非聚集索引

  • 聚集索引：决定数据在磁盘上的物理排序，一个表中只有一个聚集索引

  • 非聚集索引：并不能决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据

从逻辑角度，索引可以分为：

  • 普通索引：index 最基本的索引，没有任何限制

ALTER TABLE table_name ADD INDEX idx_name (column)
CREATE INDEX  idx_name ON tb_name(column)

  • 唯一索引

CREATE UNIQUE INDEX idx_email ON tb_name(column)

  • 主键索引

ALTER TABLE tb_name ADD PRIMARY KEY (id);

  • 联合索引

ALTER TABLE tb_name ADD INDEX idx_name(column1,column2, column3)

  • 全文索引

ALTER TABLE tb_name ADD FULLTEXT (column)

索引一经创建不能修改，如果要修改索引，只能删除重建。
删除索引：

DROP INDEX idx_name ON tb_name

3 索引建立的规则

  1. 适合建立在经常出现where、select字句的列中

  2. 不适合在类别数量较小的列，例如性别

4 索引失效十个可能

1）查询条件中包含or，而or后面的字段没有建立索引

分析：

or+没有索引的字段时，如果它使用索引扫描，当它遇到没有索引的字段时，它还是要走全表扫描，这时它的流程就是全表扫描+索引扫描+合并

如果不使用索引，则是一次全表扫描。

2）当字段类型时字符串时，使用where需要使用“”，否则索引失效

分析：当字符串不加“”，会做隐式的类型转换，例如userId时字符串类型，不加“”则会被认为是数字类型，从而索引失效

3）like通配符前面若是加了“%”，则会导致索引失效

分析：

当like查询以%开头，会导致索引失效，有两种方式优化：

  • 使用覆盖索引，只查询索引的字段

  • 把%放到like后面

4）联合索引，遵守最左前缀原则

当联合索引查询条件列不包含，索引第一个的列时，会导致索引失效

分析：

  • 当我们创建了一个联合索引时（a,b,c），相当于创建了（a),(a,b),(a,b,c)三个索引。

5）在索引列上使用MySQL的内置函数，索引失效

例如：count（）、max（）等等

6） 对索引列运算，使用+、-、*、/运算，会导致索引失效

7）索引使用范围查询时可能会失效

例如：当使用！=、<、>、not in这些时，索引失效，可以使用<=、>=

8）索引字段上使用is null，is not null，可能导致索引失效

  • B-tree索引is null不会走，is not null会走索引

  • 建立的索引不允许为null值，当为null值时，使用is null、is not null，索引失效，但是联合索引允许存在不全为null的值，最好建表时，使用not null约束索引列

  • 当两个is not null使用or连接时，索引失效

9）左连接查询或者右连接查询查询关联字段的编码格式不一致时，导致索引失效

例如，左表的id字段是utf8mb4，右表的id字段是utf8，执行左外连接查询时，索引失效

10）MySQL使用全表扫描比使用索引快时，则不使用索引

  • 当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。

  • 不要给'性别'等增加索引。如果某个数据列里包含了均是"0/1"或“Y/N”等值，即包含着许多重复的值，就算为它建立了索引，索引效果不会太好，还可能导致全表扫描。