操作索引

创建索引

应该创建索引的列：

  • 在经常需要搜索的列上，可以加快搜索的速度
  • 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
  • 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
  • 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
  • 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
  • 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

不该创建索引的列：

  • 对于那些在查询中很少使用或者参考的列不应该创建索引：若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
  • 对于那些只有很少数据值或者重复值多的列也不应该增加索引：这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
  • 对于那些定义为text, image和bit数据类型的列不应该增加索引：这些列的数据量要么相当大，要么取值很少。
  • 当该列修改性能要求远远高于检索性能时，不应该创建索引。

创建表后创建索引：

  1. -- 创建普通索引 
  2. CREATE INDEX index_name ON table_name(col_name);
  4. -- 创建唯一索引
  5. CREATE UNIQUE INDEX index_name ON table_name(col_name);
  7. -- 创建普通/复合组合索引
  8. CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
  10. -- 创建唯一组合索引
  11. CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

注意： 索引名 index_name 是可以省略的，如果省略则默认使用列名

修改表结构创建索引：

ALTER TABLE table_name ADD INDEX index_name(col_name);

创建表时创建索引：

CREATE TABLE table_name (
    ID INT NOT NULL,
    col_name VARCHAR (16) NOT NULL,
    INDEX index_name (col_name)
);

删除索引

-- 直接删除索引
DROP INDEX index_name ON table_name;

-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

查询索引

-- 查看表结构
desc table_name;

-- 查看生成表的SQL
show create table table_name;

-- 查看索引信息（包括索引结构等）
show index from  table_name;

-- 查看SQL执行时间（精确到小数点后8位）
set profiling = 1;
SQL...
show profiles;

image.png

索引失效

MySQL数据库添加索引可以大幅度的提高检索效率，但是前提是能够正确的使用数据库索引，否则即使建立了索引也每天用，即索引失效。下面是以一个例子来说明索引失效的情况：
image.png
这个表的索引建立如下：
image.png

使用!= 或者 <> 导致索引失效

使用!=、<>并不会走索引，相反，更加倾向走全表扫描，如果表的数据量比较大的话，需要谨慎使用。例如：

explain SELECT * FROM `user` WHERE `name` != '阿离';

image.png

类型不一致导致的索引失效

索引列是什么类型，查询的时候就要使用什么类型，不能出现例如id为INT，但是查询的时候使用char的情况。例如：

explain SELECT * FROM `user` WHERE height= 175;

height本来是varchar，但是查询的时候却使用了int，从而导致索引失效：
image.png

函数导致的索引失效

如果索引字段使用了比如DATE这样的函数，那么它是不走索引的，例如：

explain SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';

image.png

运算符导致的索引失效

如果对索引列进行了“+、-、*、/、!”这样的运算，那么它也是不走索引的，例如：

explain SELECT * FROM `user` WHERE age - 1 = 20;

image.png

OR引起的索引失效

OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。例如：

explain SELECT * FROM `user` WHERE `name` = '张三' OR `mobile` = '19928461353';

image.png
上面没有走索引，其实是因为mobile没有索引，其实在8.0以后的MySQL，两个不同字段的OR，只要两个都是索引列，还是会走索引的，例如：

explain SELECT * FROM `user` WHERE `name` = '张三' OR `age` = 18;

image.png用的是两个索引，索引type是index_merge

模糊搜索导致的索引失效

如果用的前置模糊查询，即“%XXX”，那么是不会走索引的，因为没有利用到索引的排序，例如：

explain SELECT * FROM `user` WHERE `name` LIKE '%冰';

image.png
而如果是后置的模糊查询，即“XXX%”其实是走索引的，例如：

explain SELECT * FROM `user` WHERE `name` LIKE '冰%';

image.png