MySql索引无效！告诉我发生了什么事？

索引错误

准备数据：

CREATE TABLE `dept` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`deptName` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	ceo INT NULL ,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

CREATE TABLE `emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`empno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`deptId` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
	#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

1。函数计算导致的索引误差

-- 显示查询分析
EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';
EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效

2。 LIKE以%

EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效

开头的索引错误扩展：阿里巴巴《Java开发手册》

[必填]页面搜索中严禁左模糊或者完全无辜。如有需要，请使用一个搜索引擎来解决它。

3。不等于 (!= 或 ) 索引错误

EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ;
EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; --索引失效

4。 IS NOT NULL 和 IS NULL ，MySQL查询优化器会选择使用索引，此时值的类型为Range（范围查询）

-- 将 id>20000 的数据的 name 值改为 NULL
UPDATE emp SET `name` = NULL WHERE `id` > 20000;

-- 执行查询分析，可以发现 IS NOT NULL 使用了索引
-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效，由查询优化器的算法决定
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL;

测试后，将名称更改回值

UPDATE emp SET `name` = rand_string(6) WHERE `id` > 20000;

5.类型转换导致索引错误

EXPLAIN SELECT * FROM emp WHERE name='123'; 
EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效

6。全匹配是我的最爱

准备：

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");

问题：对于以下查询语句创建哪个索引最有效

-- 查询分析
EXPLAIN SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 执行SQL
SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 查看执行时间
SHOW PROFILES;

创建索引并重新运行上面的结论测试：：它可以发现，最高效的查询应用公共索引idx_age_deptid_name

7。最佳左前缀规则

准备：

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");
-- 创建索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

问题：以下SQL语句能否匹配到idx_age_deptid_name字段和多少个索引字段？

测试：

• 如果索引的列较多，则必须遵循最左边的前缀规则。即从索引的左列开始查询，不跳过索引中的任何列。要将索引用于
• 过滤条件，必须按照创建 索引的顺序满足 。如果跳过某个字段，则无法使用索引后面的字段。 ？如果我们改变deptId的带条件，例如deptId > 100，那么整个索引就失效了。根据成本计算，MySQL优化器认为不需要使用索引，因此执行全表扫描。 （注：由于表中数据是随机生成的，所以当前测试中根据具体数据进行不同测试的结果会有所不同，是否使用索引最终由优化器决定）

  

  测试2 :

  -- 创建索引并执行以上SQL语句的EXPLAIN（将deptid索引的放在最后）
  CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid);
  -- 使用了完整的索引
  

  

  补充：如果以上两个索引都存在，MySQL优化器会自动选择最佳的解决方案

  作者：Esofar