索引相关基础基础知识：Mysql——SQL性能优化基础知识
测试表创建：
CREATE TABLE IF NOT EXISTS staff (
id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '自增主键',
name VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT(4) NOT NULL DEFAULT 0 COMMENT '年龄',
position VARCHAR(32) NOT NULL DEFAULT '' COMMENT '职位',
entry_time DATETIME NOT NULL DEFAULT current_timestamp() COMMENT '入职时间'
) COMMENT '员工表';
INSERT INTO staff (name, age, position, entry_time)
values
('Leo', 25, 'Java开发', current_timestamp()),
('James', 36, '测试', current_timestamp()),
('Jones', 22, '运维', current_timestamp());
ALTER TABLE staff ADD INDEX idx_staff_nameAgePosition(name, age, position);
-- OR
CREATE INDEX idx_staff_nameAgePosition ON staff(name, age, position);常见索引失效的原因
违背左前缀法则
EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';
EXPLAIN SELECT * FROM staff WHERE age = 25 AND `position` = 'Java开发';
EXPLAIN SELECT * FROM staff WHERE `position` = 'Java开发';
观察上面的三条执行计划可以得出：
如果索引有多个字段参与，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不能跳过索引列。
EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND `position` = 'Java开发';
如果按照索引列的顺序中间的部分断开了，索引失效了，同样是会影响到性能的，但是没有过于严重。
总结下来就是：带头大哥不能死，中间兄弟不能断。
不要在索引列上做任何的操作（计算、函数、自动或者手动的类型转换）
EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo';
EXPLAIN SELECT * FROM staff WHERE LEFT(`name`, 4) = 'Leo';
由于索引列上存在了函数运算，导致了索引直接失效，从ref级别的const比较，直接变成了ALL全表扫描。索引列上能不要计算就不计算。
搜索引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';
EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND age > 25 AND `position` = 'Java开发';
虽然第二条语句使用了索引，但是性能也是收到极大的影响：
由于name通过索引进行了ref级别的const检索，但是由于第二个条件由于使用了范围比较，导致Mysql无法再使用索引进行检索，position就无法使用索引了。
between、<、>、in等范围性的过滤条件打断了索引列。
尽量使用覆盖索引，杜绝select *的使用
什么是覆盖索引？
查询column1，column2，正好存在一个复合索引是使用column1和column2创建的，且查询顺序和复合索引的字段顺序一致，则查询就可以直接通过该复合索引检索结果，就不需要去表中进行数据扫描了。
EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';
EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';
EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 'Leo' AND age = 25;
EXPLAIN SELECT `name` FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';
上述例子中，使用select *的语句，Extra实际应该是Using where，但是不知道由于什么原因没有显示（暂不做深究），当使用了覆盖索引查询后，Extra为Using index，避免了访问表数据，效率不错。
使用不等于（!=或者<>）时候将索引失效，导致全表扫描
EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 'Leo';
EXPLAIN SELECT * FROM staff WHERE `name` != 'Leo';
EXPLAIN SELECT * FROM staff WHERE `name` <> 'Leo';
由于使用了不等于，导致了索引失效。
EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` != 'Leo';
上述不等于语句，使用了覆盖索引之后，没有出现全表扫描，检索类型变为了index，还能接受。
看了这个例子后，也不要害怕，并不是说导致索引失效了就什么都不用了，该这么写还是这么写，没有那么绝对的。优化SQL并不是完全杜绝没有所谓了不良SQL，而是尽可能的在满足业务的情况下，复合当下情况的最优SQL。
IS NULL、IS NOT NULL导致索引失效
EXPLAIN SELECT * FROM staff WHERE `name` IS NULL;
EXPLAIN SELECT * FROM staff WHERE `name` IS NOT NULL;
以通配符开头的like模糊搜索会导致索引失效
EXPLAIN SELECT * FROM staff WHERE `name` LIKE 'Leo%';
EXPLAIN SELECT * FROM staff WHERE `name` LIKE '%Leo%';
like模糊查询只要不是%开头，其实还是可以接受的。
如下是代替like的模糊查询功能的函数执行计划，看一下，心里有个底就好了：
EXPLAIN SELECT * FROM staff WHERE POSITION('Leo' IN `name`) > 0 ;
EXPLAIN SELECT * FROM staff WHERE LOCATE('Leo', `name`) > 0 ;
POSITION实际上是LOCATE的别名。
EXPLAIN SELECT * FROM staff WHERE INSTR(`name`,'Leo') > 0 ;
实际上这些可以代替like的函数（POSTION、LOCATE、INSTR），都是由于索引字段参与了函数的运算，导致索引失效。
如果必须需要使用前后%的模糊索引呢？应该怎么优化？
- 使用复合索引的方式：
EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` LIKE '%Leo%';
显然index要比ALL稍微好一些的。
只要select 列表属于复合索引，不管是否全量匹配复合索引的列，还是单个列属于复合索引，都可以防止like '%%'的索引失效，切记不能在select 列表出现复合索引列之外的其他列（主键除外）。
- 使用主键
EXPLAIN SELECT id FROM staff WHERE `name` LIKE '%Leo%';
字符串不加单引号导致索引失效
EXPLAIN SELECT `name`, age, `position` FROM staff WHERE name = '2000';
EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 2000;
很显然，不加引号使Mysql隐性的发生了类型转换，ref直接下降到了index。如果select 列表是*，直接就变为了ALL全表扫描。
尽量少用OR（也可能会导致索引失效）
EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' OR `name` = 'James';
EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 'Leo' OR `name` = 'James';
