MySQL之索引(3)（索引基本语法、SQL执行计划、常见索引失效原因与解决方法）

目录

一、索引基本语法。

（1）创建索引。

（2）查看索引。

（3）删除索引。

（4）给多列添加组合索引。

1、何时添加索引？？

2、组合索引。

二、SQL执行计划。

（1）什么是SQL执行计划？

（2）查看SQL执行计划的作用。

（3）如何使用？

1、关键字：explain（EXPLAIN）。

（4）SQL执行计划中重要字段说明。

1、select_type。

2、table。

3、type。（重点关注的列）

4、key。（重点关注的列）

5、possible_keys。（重点关注的列）

6、key_len。

7、rows。

三、索引失效？？

（1）什么是索引失效？

（2）常见的索引失效原因及解决方法。

1、不符合最左前缀原则的查询。（最左匹配原则）

2、隐式类型转换导致索引失效。

3、使用了左模糊查询。

4、字符串条件并未使用引号引起来！

5、索引列直接进行加减操作。

6、对索引列进行函数操作。

7、关键字order by使用时。

（3）与索引操作有关的sql优化！

---

一、索引基本语法。

（1）创建索引。

• 关键字：INDEX。
• UNIQUE：表示是唯一索引。（其中主键索引与唯一索引都属于UNIQUE）
• 针对于大文本——>FULLTEXT。（全文索引）

---

• 索引名：index_name。
• 给对应的字段添加索引（字段可以是一个列或多个列——联合索引）

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (

index_col_name,... ) ;

---

---

（2）查看索引。

• 可以通过数据库可视工具（DataGrip）直接查看。

---

• 也可以使用命令直接查看。

SHOW INDEX FROM table_name;

---

---

（3）删除索引。

• 在实际开发时，数据库管理员会定期删除某些表的索引并且重构。这样可以删除一些“索引碎片”。
• 还有情况就是：原先某张表的查询需求高，而现在对应增加、删除、修改的需求更多，就需要把原先的索引删除。因为索引是只提高查询效率、降低增、删、改效率。

---

DROP INDEX index_name ON table_name;

• 注意：取名使用关键字，尽量要避免。如果真的取了，那么使用符合``进行包裹！！

---

（4）给多列添加组合索引。

1、何时添加索引？？

• 添加索引：这张表查询多，增删改少。否则,不建议加
• 给那些列加索引：where中经常出现列，order by排序的列（因为MySQL中本身就是B+树索引，是有序的），select查询的列。

---

2、组合索引。

• 比如某张员工信息表（emp）。其经常是员工姓名（ename）与员工工资（sal）。员工姓名（ename）与员工工作（job）。员工工作（job）与员工工资（sal）进行组合查询。

---

• 所以就需要给这三个字段添加一个组合索引！

二、SQL执行计划。

（1）什么是SQL执行计划？

• SQL执行计划（Execution Plan）是数据库系统在执行SQL查询之前，根据查询语句生成的一份详细的执行步骤说明书。
• 这份说明书描述了数据库如何执行查询，包括数据访问路径、连接顺序、使用哪些索引、是否需要排序或聚合等操作。
• 执行计划对于数据库性能优化至关重要，因为它可以帮助开发者和数据库管理员理解查询的执行方式，并据此进行优化。

---

（2）查看SQL执行计划的作用。

• 通过分析SQL的执行计划，可以识别查询中的性能瓶颈。如全表扫描、索引未被使用、过多的排序和聚合操作等，并据此进行优化。
• 这样就可以进行调整索引策略、重写查询语句或调整数据库结构来解决对应问题。

---

（3）如何使用？

1、关键字：explain（EXPLAIN）。

• 全表扫描。

• 使用已创建的索引。

---

（4）SQL执行计划中重要字段说明。

1、select_type。

---

2、table。

• 涉及的表名。

---

3、type。（重点关注的列）

• 区间索引，这是重要的列。其显示连接使用了何种类型。

---

• 其中从（最好——>最差）的连接类型如下显示。
• ALL是最差的。一定要进行优化。

---

• type字段可能出现的值（详细介绍）

---

4、key。（重点关注的列）

• 实际使用到的索引。
• 如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。

---

5、possible_keys。（重点关注的列）

• 指出MySQL能使用哪个索引在该表中找到行。
• 如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

---

6、key_len。

• 最长的索引宽度。如果键是NULL，长度就是NULL。
• 在不损失精确性的情况下，长度越短越好。

---

7、rows。

• 显示MySQL认为它执行查询时必须检查的行数。（查询多少行）

三、索引失效？？

（1）什么是索引失效？

• 索引失效是指在进行查询操作时，数据库没有利用索引来提升查询效率，而是采用了全表扫描的方式，这会大大增加查询时间和系统负担。

（2）常见的索引失效原因及解决方法。

1、不符合最左前缀原则的查询。（最左匹配原则）

• 在使用多列索引（组合索引）时，如果查询条件没有从索引的最左列开始，索引可能会失效。

---

• 如下演示。员工信息表（emp）。已经创建组合索引员工姓名(ename)、员工工作(job)、员工工资(sal)——>index_emp_ename_job_sal（组合索引）。

---

• 对于下面几条sql语句都是索引没有失效。
• 因为最左匹配原则——组合索引中最左边字段ename存在就生效！

explain  select * from emp where ename ='SMITT'; #使用到索引

explain select * from emp where ename ='SMITT' and job='sxxx'; #使用到索引

explain select * from emp where ename ='SMITT' and sal>1000; #使用到索引

explain select * from emp where ename ='SMITT' and sal>1000 and job ='xxx'; #使用到索引

explain select * from emp where   sal>1000 and ename ='SMITT'; #使用到索引

---

• 而下面的几个sql语句——索引就失效了。
• 因为where中未出现最左的字段。

explain select * from emp where job ='xx' and sal>1000; #索引失效

explain select * from emp where job ='xx';  #索引失效

---

• 如果select后跟的列只要是是组合索引中的单字段或多字段，且where中未出现最左的字段，索引并不会失效！

• 也就是：如果select中查询的列满足索引出现的列，使用索引！

explain select ename from emp where job ='xx' and sal>1000; #索引未失效
explain select empno from emp where job ='xx' and sal>1000; #索引失效

---

2、隐式类型转换导致索引失效。

• 例如员工表（emp）的字段empno为int类型，并给它设置成唯一索引。进行select查找。int类型时不会出现问题！
• 但修改字段empno为varchar类型会出现下面情况。（索引失效！！）

# empno: int  赋值类型不一样, 隐式转换后还是会使用索引
explain  select * from emp where empno = 7788;  # 使用索引

explain  select * from emp where empno = '7788'; # 使用索引

################################################################################

# empno: varchar   如果字段类型是文本类型, 赋值类型是int, 索引失效
explain  select * from emp where empno = 7788;  # 没有使用索引  mysql: 隐式类型转换

explain  select * from emp where empno = '7788'; # 使用索引

---

3、使用了左模糊查询。

• 顾名思义：如果在like模糊查询中，以"%"或者"_"开头（前缀）都会导致索引失效！因为索引无法利用模糊匹配的前缀。

# 在like模糊查询中, %,_ 出现在左边, 索引失效
explain select * from emp where ename like 'S%'; #使用索引
explain select * from emp where ename like '%S%';  #没有使用索引
explain select * from emp where ename like '_S%';  #没有使用索引

---

4、字符串条件并未使用引号引起来！

• 如果字符串条件未使用单引号或双引号引起来，可能会导致索引失效。
• 例如，如果dept_no是字符串类型，那么在查询时应该使用引号，否则可能导致索引失效。

---

5、索引列直接进行加减操作。

• 索引列直接进行加减操作通常会导致索引失效！
• 这是因为数据库的索引是基于列的值进行优化的，当列的值被修改（如加减操作）后，索引中的值与实际的数据行不再匹配，因此无法利用索引进行快速查找。

create index index_emp_comm on emp(comm);
explain select * from emp where comm=2000; # 使用索引

# 如果索引列进行加减乘除运算, 索引也失效
explain select * from emp where comm+1=2000; # 没有索引

---

6、对索引列进行函数操作。

• 对索引列进行一些函数操作，都会使索引失效！

# 列使用函数, 索引失效
explain select * from emp where length(ename) = 5;

---

7、关键字order by使用时。

• 在某些情况下，关键字"order by"可能会导致索引失效。
• 所以推荐关键字"order by"后面接关键字"limit"。保证能够走索引！

---

8、使用关键字 IN。

• 在有关键字"IN"的子句中，如果列表中的值过多，可能会导致索引失效。

---

（3）与索引操作有关的sql优化！

• 除了给对应的字段添加索引——>对sql进行优化。
• 还可以查看SQL的执行计划——>如看其重要变量"type"是否太低了，继续调整合适的SQL语句。