MySQL索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。 目的在于提高查询效率，可以类比字典。

 

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引只是提高效率的一个因素，如果你的MySQL有大量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

 

• 简单理解为 “排好序的快速查找数据结构” ：
  在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。

• 索引优势

• 类似图书馆简历书目索引，提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
• 索引劣势

索引分类

1.主键索引

设定为主键后数据库会自动简历索引，InnoDB采用聚簇索引
语法：

# 随表一起创建
CREATE TABLE emp (
    # 使用AUTO_INCREMENT关键字的列必须要有索引
    ID int(10) UNSIGNED AUTO_INCREMENT
    , NAME varchar(8)
    , PRIMARY KEY(ID)
 )

# 单独建主键索引
ALTER TABLE emp add PRIMARY KEY emp(id);

# 删除主键索引
ALTER TABLE emp drop PRIMARY KEY;   # 修改主键索引前必须删除(drop)原索引，再新建(add)索引

2.单值索引

 

# 随表一起创建
CREATE TABLE emp (
    # 使用AUTO_INCREMENT关键字的列必须要有索引
    ID int(10) UNSIGNED AUTO_INCREMENT
    , EMP_NO varchar(8)
    , NAME varchar(8)
    , KEY(EMP_NO)
 )

# 单独建单列索引
create index idx_emp_no on emp(EMP_NO)

# 删除单列索引
drop index idx_emp_no

3.唯一索引

 

# 随表一起创建
CREATE TABLE emp (
    # 使用AUTO_INCREMENT关键字的列必须要有索引
    ID int(10) UNSIGNED AUTO_INCREMENT
    , EMP_NO varchar(8)
    , NAME varchar(8)
    , UNIQUE(EMP_NO)
 )

# 单独建唯一索引
create unique index idx_emp_no on emp(EMP_NO)

# 删除主键索引
drop index idx_emp_no on emp

4.复合索引

在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的多个列建索引）;
当表的行数远大于索引列的数目时可以使用复合索引。

# 随表一起创建CREATE TABLE emp (    # 使用AUTO_INCREMENT关键字的列必须要有索引    ID int(10) UNSIGNED AUTO_INCREMENT    , EMP_NO varchar(8)    , NAME varchar(8)    , key(EMP_NO，NAME) )#建立唯一索引是必须保证所有的值是唯一的(除了null)，若有重复数据，会报错# 单独建唯一索引create index idx_no_name on emp(EMP_NO,NAME)# 删除主键索引drop index idx_no_name on emp

【基本语法】

# 创建
alter < table_name > add [unique] index <index_name> on <column_name>

# 删除
drop index <index_name> on <table_name>

#查看
show index from <table_name>

#使用ALTER命令
#方式1：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null
alter table <table_name> add primary key <column_name>

#方式2：该语句添加一个唯一索引，值必须是唯一的(null外，null可能会出现很多次)
alter table <table_name> add unique key <column_name>

#方式3：该语句添加普通索引，索引值可以出现很多次
alter table <table_name> add index <index_name>(column_name)

#方式4：该语句指定了索引为FULLTEXT，用户全文索引
alter table <table_name> add FULLTEXT <index_name>(column_name)

• 哪些情况需要建立索引

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引（where后面的语句）
  • 查询中与其他表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题（在高并发下倾向创建组合索引）
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段

• 哪些情况不需要建立索引

  • 表记录太少
  • 经常增删改的表（因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）
  • where 条件里用不到的字段不创建索引
  • 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

查询优化

• 索引的使用

1. 全值匹配我最爱
   staffs 表建立索引 idx_staffs_nameAgePos,以name，age，pos的顺序建立，全值匹配标识按顺序匹配。
   
2. 最佳左前缀原则
   如果索引了多列，要遵守最左前缀原则，值得是查询从索引的最左前列开始，并且不跳过索引中的列
   and 忽略左右关系，即使没有按顺序，由于优化器的存在，会自动优化
3. 不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描。
   
4. 存储引擎不能使用索引中范围条件右边的列
   范围若有索引则能使用到索引，范围条件右边的索引会失效（范围条件右边与范围条件使用的同一个组合索引，右边的才会失效，若是不同索引则不会失效）
   
5. **尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select ***
   
6. MySQL在使用不等于（!= 或 <>）的时候无法使用索引，会导致全表扫描。
   where age != 10 and name = 'xxx' 这种情况下，mysql会自动优化将 name = 'xxx' 放在 age != 10 之前，name依然能使用索引，只是age的索引失效
   
7. is not null 也无法使用索引，但是 is null 是可以使用索引
   
8. like 以通配符开头（'%xxx'）索引失效变成全表扫描
   like '%xxx'：type 类型会变成all
   like 'xxx%'：type 类型为range，算是范围，可以使用索引
   
9. 字符串不加单引号索引失效
   底层进行类型转换时索引失效，使用了函数造成了索引失效
   

10.少用or，用它连接时索引会失效

 

【例子小节】
此时复合索引index（a,b,c）

 

 

• https://juejin.cn/post/6844904122169180168