MySQL-索引优化

前言：

索引作为加速数据检索的主要工具，在优化查询性能方面发挥着不可或缺的作用。合理地设计和优化索引，不仅可以显著减少查询时间，还能降低数据库的负载，提升整体系统的性能。

过度或不当的索引设计可能导致性能下降，尤其在数据量庞大或复杂查询的场景下。因此，了解MySQL索引的工作原理、优化策略及其在不同场景下的应用显得尤为重要。

1、like语句的前导模糊查询不使用索引：

select * from doc where title like '%XX'； --不能使用索引
select * from doc where title like 'XX%'； --非前导模糊查询，可以使用索引

2、负向条件查询不能使用索引：

负向条件有：!=、<>、not in、not exists、not like 等

例如下面SQL语句：（假设status的取值为0、1、2、3、4）

select * from doc where status != 1 and status != 2; --不能使用索引

select * from doc where status in (0,3,4); --优化为 in 查询，可以使用索引

3、范围条件右边的列不能使用索引（范围列可以用到索引）

范围条件有：<、<=、>、>=、between等。

索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引 (emp_no 、title、from_date )，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。

select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

4、在索引列做任何操作（计算、函数、表达式）会导致索引失效而转向全表扫描：

select * from doc where YEAR(create_time) <= '2016'; -- 不能使用索引

select * from doc where create_time<= '2016-01-01'; -- 可以使用索引

select * from order where date < = CURDATE()； -- 不能使用索引

select * from order where date < = '2018-01-2412:00:00'; -- 可以使用索引

select id from t where substring(name,1,3)=’abc’ -- 不能使用索引

select id from t where name like ‘abc%’ -- 可以使用索引

select id from t where num/2=100 -- 不能使用索引

select id from t where num=100*2 -- 可以使用索引

5、强制类型转换会导致全表扫描：

MySQL遇到类型转换时会自动将字符串转换为数字类型

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引，因为内部发生的类型转换。

select * from user where phone=13800001234; -- 不能使用索引

select * from user where phone='13800001234'; -- 可以使用索引

6、使用组合索引时，要符合最左前缀原则：

组合索引的字段数不允许超过5个。如果在a,b,c三个字段上建立联合索引 index(a,b,c)，那么他会自动建立 a、(a,b)、(a,b,c) 三组索引。

（1）建立联合索引的时候，区分度最高的字段在最左边：

（2）存在等号和非等号混合判断条件时，在建立索引时，把等号条件的列前置，如 where a > ? and b= ?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

（3）最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致，但还是建议 where 条件的顺序和联合索引一致。

7、利用覆盖索引来进行查询操作，避免回表，减少select * 的使用 ： 

覆盖索引：被查询列要被所建的索引覆盖，被查询列的数据能从索引中直接取得，不用通过行定位符 再到 row 上获取，加速查询速度

8、进行join联表查询的字段需要建立索引，join最好不要超过三个表，需要 join 的字段，数据类型必须一致： 

多表关联查询时，保证被关联的字段需要有索引。left join是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用right join。

创建索引语法： 

1.创建单列索引

CREATE INDEX index_name ON table_name(column_name);

2.创建多列索引

CREATE INDEX index_name ON table_name(column1, column2, ...);