在实际测试过程中对产品进行性能分析时，经常发现因缺少索引导致上层业务性能出现问题，甚至有的表一个索引都没有。
这种情况往往都是因为在设计表时，没有根据实际业务应用、数据体量等进行分析、设计。同时由于在产品开发阶段，由于开发、测试环节数据量少，索引的创建与否对于性能的影响并不明显，容易忽略其中性能风险。然而一旦发布到生产环境，随着时间推移，数据量、用户基数不断增加，暴露性能问题的风险也逐渐增大。
同时，索引创建并且用到了索引字段，但并不意味着真正使用了索引，本文主要从如何避免索引失效的角度，介绍SQL性能优化。
因索引失效，导致全表扫描的可能原因有以下几点：
- 索引列进行计算、函数、类型转换等操作。
- 索引列使用不等于，如!= 或<>。
- 索引列使用 IS NULL ，IS NOT NULL。
- 模糊查询LIKE 以通配符开头如，%ab。
- 索引列使用使用OR来连接条件。
- 索引列使用IN和NOT IN 。
- 类型错误，如字段NUM类型为varchar，WHERE条件用number，NUM = 1。
- WHERE子句和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者降序，否则不会使用索引。
- 复合索引不符合最佳左前缀原则或存在断点。
- 如果MYSQL评估全表扫描快于索引扫描，则不使用索引，一般数据量极少时，可能不会走索引。
- 索引被禁用，开启索引ALTER TABLE TESTOPS ENABLE KEYS 。
对于复合索引失效的可能原因有以下几点：
在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
同时，复合索引的生效原则是从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用，造成断点的原因一般有：
- 前边的任意一个索引没有参与查询，后面的不生效。
- 前边的任意一个索引失效，当前索引及后面全部不生效。
- 前边的任意一个索引字段参与的是范围查询，后面的不生效。
防止索引失效的优化方法
应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描。MySQL只有对以下操作符才使用索引：，>=，BETWEEN，IN，以及某些方式的模糊查询，如 LIKE 'a%' 。
WHERE 子句中使用 LIKE 进行模糊查询时，在关键词前加通配符或者前后都加通配号都无法使用索引，从而引发全表扫描。解决LIKE '%abc%' 时索引不被使用的方法就是添加覆盖索引(只访问索引的查询，索引和查询列一致，只需扫描索引而无须回表)。
应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个默认值，如 0 作为默认值。
例如，性别，使用1表示男，2表示女，0表示未知或者是用户没有选择，默认值设置为 0，因为大部分编程语言的数字类型的默认值0。
空值和NULL是有区别的，以一个杯子为例：
- 空值代表杯子是真空的
- NULL代表杯子中装满了空气
如果字段允许为空，可能会有以下问题：
- 查询条件就必须处理为空的情况，否则会出现一些很奇怪的问题，比如NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果，查询容易出错。
- 在部分数据库中会导致索引失效。
- 可空列需要更多的存储空间，导致空间变大，进而导致数据库系统查询分析变的复杂。
- 在程序中也需要每次都判断是不是空，导致程序复杂了。
但凡事没有绝对的，使用默认值的思路可以解决很大一部分可为空的问题，但不是所有都需这样做，具体还是要根据具体业务进行分析。
应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。使用 OR 的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用UNION ALL执行的效率更高。
应尽量避免在 WHERE 子句中使用 IN 和 NOT IN ，否则将导致全表扫描，对于连续的数值，能用 BETWEEN AND 尽量避免使用 IN。一般，用 EXISTS 代替 IN 。若需要使用 IN，在 IN 后面值的列表中，按照值的分布数量降序排列，减少判断的次数。
使用BETWEEN AND 替换 IN ，如下：
使用EXISTS 替代IN，用NOT EXISTS 替代 NOT IN，无论在哪种情况下， NOT IN效率都是最低的，如下：
使用LEFT JOIN 替换 IN，如下：
如上，我们使用了一下方式优化了IN 和 NOT IN：
- 使用between 替换 in。
- 使用exists替代in、用not exists替代 not in。
- 使用left join 替换 in。
应尽量避免在 WHERE 子句中对 “=” 左边的字段进行函数、算术运算及其他表达式运算，可以将表达式运算移至“=”右边，否则将导致引擎放弃使用索引而进行全表扫描。
对索引字段使用函数，如下：
对索引字段进行计算，如下：
如果在 WHERE 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时。它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项，可以改为强制查询使用索引。
例如我们建立了一个这样复合索引key index (col1,col2,col3)，那么其实相当于创建了(col1)，(col1,col2)，(col1,col2,col3)三个索引，即最佳左前缀特性。
