索引失效优化
select * 导致索引失效
覆盖索引:当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中（联合索引），可以直接使用索引查询而不需要回表,这就是覆盖索引。
使用select * 会导致以下问题:
- 增加查询分析器解析成本。
- 无用字段增加网络传输成本，尤其是text 类型的字段。
- 无法使用覆盖索引
之所以说使用select * 会导致索引失效,是因为在某些场景下某些sql语句可以使用覆盖索引进行优化。使用了select * 就没法使用覆盖索引了。
列区分度太低导致不走索引
优化 像sex、逻辑删除、这种列就不要加索引了。
联合索引违反最左前缀或者断层
MySQL可以为多个字段创建索引，一个索引可以包括16个字段。
对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。
例如索引是key index （a,b,c）。可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 。当最左侧字段是常量引用时，索引就十分有效。
create table myTest(
a int,
b int,
c int,
KEY a(a,b,c)
);
select * from myTest where a=3 and b=5 and c=4; ---- abc顺序
abc三个索引都在where条件里面用到了，而且都发挥了作用
select * from myTest where c=4 and b=6 and a=3;
where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样。
select * from myTest where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的
select * from myTest where a=3 and b>7 and c=3; ---- b范围值，断点，阻塞了c的索引
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
select * from myTest where b=3 and c=4; --- 违反最左前缀
因为a索引没有使用，所以这里 bc都没有用上索引效果
select * from myTest where a>4 and b=7 and c=9;
a用到了 b没有使用，c没有使用
select * from myTest where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的
select * from myTest where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果
关于联合索引为何会失效:juejin.cn/editor/draf…
优化:使之符合最左前缀原则
使用LIKE关键字的查询:%在前
SELECT
*
FROM
single_table s1
WHERE
s1.key1 like '%a%';
优化:使用覆盖索引,可以优化查询
SELECT
id,key1
FROM
single_table s1
WHERE
s1.key1 like '%a%';
哈哈,虽然用覆盖索引可以优化,但是估计也只是用了索引下推而已,没有做全表扫描,走的是扫描索引树。
所以这个方案不建议使用。
优化:索引下推
比如 name,city列
现在需要对name和city做条件查询
where name = '李四' and city like '%河南'
此时可以用到(city,name)索引
先查找到name索引列符合条件的数据,然后再过滤
优化:使用instr(field, str) 函数
mysql 进行模糊查询时，可使用内部函数 instr，替代传统的 like 方式，并且速度更快。
instr(field, str) 函数，第一个参数 field 是字段，第二个参数 str 是要查询的串，返回串 str 的位置，没找到就是0。
SELECT
id,key1
FROM
single_table s1
WHERE
instr(s1.key1,'a')>0
实际没有验证过
优化:新增一列，存储该字段的反转。
比如数据有记录: name=彭方亮,亮方彭
现在的需求是查询%彭方亮,那么我们只需要查询亮方彭%
关于like右边有%为什么可以走索引:juejin.cn/editor/draf…
索引列有运算
select * from user where id/2=100
优化:索引列运算转为对输入参数运算
select * from user where id=100*2
索引列使用了函数
select * from user where id/2=100
优化:将索引列函数,转为输入参数上
索引列数据类型隐式转换
数据类型转换的规则是什么？为什么有数据类型转换，就需要走全索引扫描？
先来看第一个问题，你可能会说，数据库里面类型这么多，这种数据类型转换规则更多，我记不住，应该怎么办呢？
这里有一个简单的方法，看 select “10” > 9的结果：
如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是1；
如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是0。
select “10” > 9返回的是1，
所以你就能确认MySQL转换规则：在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。
select * from tradelog where tradeid=110717;
交易编号tradeid这个字段上，本来就有索引，但是explain的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid的字段类型是varchar(32)，而输入的参数却是整型，所以需要做类型转换。
所以先将tradid列 由字符串转为int类型在比较
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
优化:使用和索引列一样的数据类型，将数据类型转换放在输入参数上
索引列使用隐式编码转换
mysql> CREATE TABLE `tradelog` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`operator` int(11) DEFAULT NULL,
`t_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tradeid` (`tradeid`),
KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> CREATE TABLE `trade_detail` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`trade_step` int(11) DEFAULT NULL, /*操作步骤*/
`step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
PRIMARY KEY (`id`),
KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
第1步，是根据id在tradelog表里找到L2这一行；
第2步，是从L2中取出tradeid字段的值；
第3步，然后根据tradeid值到trade_detail表中查找条件匹配的行。explain的结果里面第二行的key=NULL表示的就是，这个过程是通过遍历主键索引的方式，一个一个地判断tradeid的值是否匹配。
进行到这里，你会发现第3步不符合我们的预期。因为表trade_detail里tradeid字段上是有索引的，我们本来是希望通过使用tradeid索引能够快速定位到等值的行。但，这里并没有。
如果你去问DBA同学，他们可能会告诉你，因为这两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引。这个回答，也是通常你搜索这个问题时会得到的答案。
但是你应该再追问一下，为什么字符集不同就用不上索引呢？
我们说问题是出在执行步骤的第3步，如果单独把这一步改成SQL语句的话，那就是：
mysql> select * from trade_detail where tradeid=$L2.tradeid.value;
其中，$L2.tradeid.value的字符集是utf8mb4。trade_detail的tradeid是utf8
优化:使用和索引列一样的编码类型，将编码转换放在输入参数上
mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
这也是两个tradeid字段的join操作，为什么这次能用上被驱动表的tradeid索引呢？我们来分析一下。
假设驱动表trade_detail里id=4的行记为R4，那么在连接的时候（图5的第3步），被驱动表tradelog上执行的就是类似这样的SQL 语句：
select operator from tradelog where traideid =$R4.tradeid.value;
这时候$R4.tradeid.value的字符集是utf8, 按照字符集转换规则，要转成utf8mb4，所以这个过程就被改写成：
select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4);
你看，这里的CONVERT函数是加在输入参数上的，这样就可以用上被驱动表的traideid索引。
总结一下上面的规律: 始终把索引列单独放在比较符号的另一侧。即不对索引列做任何函数操作。
全表扫描更快时（数据很少的时候）
原因是表中数据很少的时候,走索引还要做回表操作。
优化:可以使用强制使用索引或者强制忽略索引
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;
SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;
使用OR关键字的查询
询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。
select * from user where name = '2000' or pos ='cxy';
name列有索引 pos列无索引 索引会失效
原因是
1.即使使用了name索引还是要回表
2.由于pos列无索引,要走全表扫描
如果按上面的执行计划,那不如直接全表扫描判断2个条件是否成立
优化:使用union,2边都用索引
没有使用覆盖索引并且使用不等于（!=、<>）
!=不一定无法使用索引。
# mysql在使用不等于（!=、<>）的时候无法使用索引会导致全表扫描（除覆盖索引外）
explain select * from user where age != 20;
explain select * from user where age <> 20;
优化:使用覆盖索引
is null,is not null可能无法使用索引
is null ，is not null走和不走索引是和数据量或者和其他元素有关系。（这里我只是测试到和数据量有关系） sql优化器在执行的时候会计算成本，其实和基数，选择性，直方图有关，其实就是看你所搜索的部分占全表的比例是走索引还是全表成本低。
总结：并不能一句话说 走和不走，需要看条件，比如数据量，等于“abc”的数据量和不等于“abc”的量，mysql在执行的时候会判断走索引的成本和全表扫描的成本，然后选择成本小的那个
