MySQL的索引失效的原因实例及解决方案

-- 错误示例：数据类型不匹配
select * from users where id = '123'; -- id 是 int 类型，'123' 是字符串
-- 正确示例：确保数据类型一致
select * from users where id = 123; -- 使用正确的数据类型
-- 如果必须使用字符串输入，可以显式转换
select * from users where cast(id as char) = '123';

-- 错误示例：隐式转换
select * from users where age = 25 + 0.0; -- 强制浮点数运算
-- 正确示例：避免不必要的数学运算
select * from users where age = 25; -- 直接使用整数

-- 错误示例：索引列上使用函数
select * from articles where length(title) > 10;
-- 改进方法（取决于需求）
select * from articles where title like '___________%'; -- 假设标题至少有11个字符
-- 或者创建表达式索引（mysql 8.0+）
create index idx_title_length on articles ((length(title)));
select * from articles where length(title) > 10;

create index idx_name on table (col1, col2);
select * from table where col1 = 'value1' and col2 > 'value2';
-- 如果你经常需要基于 col2 的范围查询，可以考虑创建一个单独的索引
create index idx_col2 on table (col2);
 

-- 不理想的查询
select * from names where name like '%john%';
-- 改进方法（根据实际情况）
-- 如果是尾部模糊匹配，可以使用索引
select * from names where name like 'john%';
-- 或者使用全文索引（适用于大量文本搜索）
alter table names add fulltext(name);
select * from names where match(name) against('john');
-- 对于前缀匹配，可以使用索引覆盖
select * from names where name >= 'john' and name < 'johnz';

-- 不理想的查询
select * from users where first_name = 'john' or last_name = 'smith';
-- 改进方法（根据实际情况）
-- 如果查询频率较高，可以考虑创建组合索引
create index idx_first_last_name on users (first_name, last_name);
-- 或者重构查询逻辑，如使用 union
select * from users where first_name = 'john'
union all
select * from users where last_name = 'smith';
-- 利用索引合并（mysql 5.6+）
explain select * from users where first_name = 'john' or last_name = 'smith';

-- 对于小表，即使有索引也可能选择全表扫描
select * from small_table;
-- 对于大表，如果查询返回大量行，优化器也会倾向于全表扫描
select * from large_table where some_condition;

-- 性别列的选择性很低
select android* from employees where gender = 'm';
-- 改善方法（根据实际情况）
-- 尽量避免在低选择性的列上创建独立的索引，除非它们与其他高选择性的列一起组成复合索引
create index idx_gender_salary on employees (gender, salary);

-- 假设有一个覆盖索引 idx_id_name 包含 id 和 name 列
select id, name, address from customers where id = 123;
-- 改善方法
create index idx_id_name_address on customers (id, name, address);

-- 分析表以更新统计信息
analyze table your_table;
-- 或者使用 optimize table 来重建表并更新统计信息
optimize table your_table;
-- 在 mysql 8.0 及以上版本，还可以使用系统变量控制统计信息的收集
set persist optimizer_switch='histogram=on';

-- 在高负载系统中，频繁更新某张表可能会导致读取操作等待写锁释放
-- 解决方案包括但不限于调整事务隔离级别、优化 sql 语句减少锁定时间等。
-- 降低事务隔离级别以减少锁定
set session transaction isolation level read committed;
-- 使用乐观锁策略，如添加版本号列
alter table your_table add column version int default 0;
update your_table set column1 = value1, version = version + 1 where id = specific_id and version =http://www.chinasem.cn current_version;