谈谈MySql中索引失效的情况
# 谈谈MySql中索引失效的情况
下列是一个索引可视化的网站：
- https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
# 索引失效的情况
查看一个语句是否走索引，可以通过如下sql进行查看
#explain select * from tableName where 条件
explain select * from users where password="222"
2
若对应的key有值，则为查询时走了索引。
当然，在mysql8.0之后，还可以加上analyze来查看具体的执行时间。
# 加上 analyze
explain analyze select * from users where password="222"
2
结果，会返回类似结果的：
-> Table scan on <temporary> (actual time=0.117..0.118 rows=2 loops=1)
实际花费了 0.117毫秒的时间。
# 不满足最左匹配原则
使用联合索引时，比如我们建立了name, age, phone 三个字段的联合索引。
alter table users add index uni_index(name,age,phone);
# 不走索引
explain select * from users where age=12 and phone= 1298672
2
3
因为最左优先原则：会先比较name属性，再接着age，最后phone，如没有，则无法匹配
所以如果where age = 12,则不会生效，而where age = 12 and name="test"则会生效。或者说只有(a),(a,b),(a,b,c)三种查询可以生效。
# 使用不等于（!= 、<>）
explain select * from user where age != 20;
通常情况来说，使用不等于是不走索引的，但也有另外，MySQL中有一个叫做优化器的东西，他会对每一条查询sql做成本分析，然后根据分析结果选择是否使用索引或者全表扫描。
这里举2种情况，如果不等于20的记录跟等于20的记录，数量都比较多，则成本很高，不会走索引。但如果本身记录很少，比如一共只有几条，则会走索引，或者不等于2的仅有10条，也会走索引。
总结：使用不等于的情况下，不一定不走索引，而是看成本，如果走索引成本不大，也会走索引，否则不会。
# “OR”前后存在非索引列，导致索引失效
当where语句中使用or操作符并且两边的条件至少涉及两个字段,且字段中存在非索引列时，MySQL无法使用索引，会转向全表扫描。
**index_merge：**OR前后的两个条件中的列都是索引时，查询中才使用索引，索引类型是“index_merge”，将两个索引字段分别扫描，然后合并。
explain select * from diy.users where age =88 or hobby ='看电影';
# 使用了索引
explain select * from diy.users where age =88 or hobby ='看电影' or password='123'
# 以上语句 则没有使用索引
2
3
4
在上述两个语句中，因为age,hobby都使用了索引，所以第一个sql走了索引，而password没有索引，所以第二个sql不会走索引。
# like中用通配符开头('%xxx')
# 在一个表中创建一个索引
alter table diy.users add index h_index(hobby);
# 以下语句 使用了索引
explain select * from diy.users where hobby like '看%'
# 未使用索引 花费时间更久
explain select * from diy.users where hobby like '%看'
2
3
4
5
6
- %通配符：表示任意字符，可以匹配任意的字符数量。
- _通配符：表示任意单个字符。
再来看个例子：
create table t4(
id int not null primary key,
col1 int,
col2 varchar(50)
);
# 创建一个索引
create index idx4 on t4(col2);
#以下sql 有没有性能问题
select * from t4 where col2 like '%sql%';
2
3
4
5
6
7
8
9
10
它是不会走索引的，因为一开始是模糊的。如果要想其生效，可以改为如下：
select * from t4 where col2 like 'sql%'
# 使用Order By
explain select * from diy.users order by id,age;
以上sql语句，order后跟了2个字段，其中id是主键，但age没建立索引，则在查询时不会走索引。这是因为：ORDER BY子句中的列与索引列不完全匹配（即索引列包含部分数据而非完整列），所以MySQL可能无法有效地利用索引进行排序。
通常来说，order by后跟的列只要是索引列就会走索引，但有时即使order by后跟的列是索引列，也会发生索引失效的情况，这是因为
优化器的作用，可能是由于查询优化器的估算成本较高，或者使用文件排序的开销较小等原因。
# 在索引列上进行计算、函数
现有一个需求，找出name为ty开头的字段信息：
create index idx_name on demos(name);
# 对索引列执行函数计算
explain select * from demos where left(name,2)='ty'
2
3
字段使用函数会让MySql中的优化器无法生效，因为函数的结构可能和B+树中的值不匹配，所以不会走索引。字段能不用函数就不用函数。
当然也有使用索引的解决方法：
SELECT * FROM demos WHERE name LIKE 'ty%';
# 是否生效
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATETIME,
total_amount DECIMAL(10, 2)
);
-- 创建一个覆盖索引
CREATE INDEX idx_order_date_total_amount ON orders (order_date, total_amount);
# 以下sql 会走索引 还是说 索引会失效
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE DATE(order_date) = '2023-10-01' AND total_amount > 50.00;
2
3
4
5
6
7
8
9
10
11
12
13
它可能会生效，可能不生效。为什么会这样呢？
其原因在于，优化器是否重写：
有些数据库系统的优化器足够智能，可以将
DATE(order_date) = '2023-10-01'重写为一个范围查询，例如：WHERE order_date >= '2023-10-01 00:00:00' AND order_date < '2023-10-02 00:00:00'1
这种重写方式使得数据库系统可以利用 order_date 上的索引。
当然，如果优化器没有重写它，那上述sql就发生索引失效。
# 仍生效的例子
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10, 2)
);
-- 创建一个索引
CREATE INDEX idx_price ON products (price);
-- 进行计算
SELECT * FROM products WHERE price * 1.10 > 15.00;
2
3
4
5
6
7
8
9
10
虽然我们在 WHERE 子句中使用了乘法运算，但数据库优化器可能会重写查询为 price > 15.00 / 1.10，从而仍然可以使用 idx_price 索引
所以，请谨记：MySQL 中使用函数并
不一定会导致索引失效，关键取决于函数的类型和对索引列的影响。
# Select *
select * from tableName
使用select * 查询，不加任何条件，也不会走索引，直接返回全部结果，没有用到任何索引，查询效率很低
关于使用select *，其实还有一点需要注意：
在查找数据时，应该尽量不写 *，应该按需编写，因为 ，它代表全部，如果某些列特别长，会消耗大量的内存，且消耗时间也特别长。
还有其它的一些索引失效的情况，这里只列举了一些常用的。
# 优缺点与建议
优点：它类似于书的目录，降低了数据库IO成本，大大提高了数据查询效率
缺点：索引也是数据，因此会占据一些磁盘空间，同时由于索引跟数据存在一定关联关系，在改动数据时，也需要同时对它进行更新，一定程度上减低了更新表的效率（每次对表进行增删改查，MySQL不仅要保存数据，还有保存或者更新对应的索引文件）。
总结：索引确实能提高查询效率，但并非每次查询都会奏效，因为它独特的数据结构，有时也会存在失效的情况。索引其实就是用
空间换取时间的典型例子。数据库表中不是索引越多越好，而是根据情况为那些常用的搜索字段建立索引，效果才是最佳!