MySQL查询优化——使用索引和SQL优化
如何提高MySQL数据库的查询效率，可以从两个方面入手：使用索引和使用JOIN，本文主要讲使用索引的一些原则和优化方法。以及如何设计数据库和SQL语句，来避免一些会导致性能差的操作。
关于索引的原理层面的东西，本文暂不细讲。
本篇包括：
- 索引类型
- 使用索引的原则
- 索引优化的方式
- SQL优化方式
索引类型
在MySQL中，索引分为量大类型：聚簇索引 和 非聚簇索引。
聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同；聚簇索引可以提高多行的检索速度，而非聚簇索引对单行的检索速度很快。
（PS：聚簇和非聚簇索引的原理部分也很重要，可以去参考其他文章）
在这两大索引类型下，又细分为四个类型：
1）普通索引：最基本的索引，没有任何限制，我们大多数情况下使用到的索引。
2）唯一索引：与普通索引不同的是，唯一索引的列值必须唯一，但是允许为空值。
3）全文索引：仅适用于MyISAM引擎的数据表；作用于CHAR，VARCHAT，TEXT数据的列。
4）联合索引：将几个列作为一条索引进行检索，适用最左匹配原则。
建立索引的原则
1）最左匹配原则。
这是非常，非常，非常重要的原则：MySQL使用联合索引时，会从左边一直向右匹配，直到遇到范围查询（>, <, between, like 操作）就停止匹配。
比如：where a=1 and b=2 and c>3 and d=4，如果建立的是(a,b,c,d)的联合索引，那么 d 是用不到索引的。而如果建立成(a,b,d,c)那么d的索引就会用到了。因为MySQL优化器将d=4的条件前置到了c>3前面。
MySQL创建联合索引的规则是，首先会对联合索引最左边的字段进行数据排序，在第一个字段的基础上，对第二个字段进行排序。按上面的例子来说，就相当于实现了 order by a,b,c,d 的规则。
还是拿上面例子来说，a索引是天然有序的。当满足了a=1 这个条件之后，b就有序了，而确定了a=1 and b=2 以后，第三个索引也是有序的了。
所以，mysql索引规则中要求，要想使用联合索引的第二个索引，必须首先使用第一个索引（而且必须是等值匹配），这也是最左匹配原则的根本原因。
2）= 和 in 可以乱序。
因为MySQL优化器会判断纠正这条SQL语句，并使用效率最高的方式利用索引，生成执行计划。
3）尽量选择区分度高的列作为索引。
区分度的公式是 count(distinct col) / count(*)，表示字段的不重复比率，结果是 [0,1] 的范围。
比率越大，我们扫描的数据越少，唯一索引的区分度是1。而一些状态位，性别字段，区分度就很低，当数据量很大时，区分度就无限趋近于0了。也就是说，即使使用了这些字段做索引，那匹配出来的数据量也会很大，几乎没有作用。
那这个比率一般是多少呢？根据使用场景做具体判断，不过一般需要JOIN的字段我们要求在0.1以上，也就是平均1条数据，扫描10条记录。
</br>
4）索引不要参与计算
比如FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引。
原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有数据进行计算以后才能进行比较。显然代价太大。
5）尽可能扩展索引，而不是新建索引
比如表中已经有了 a 的索引，现在要使用 (a,b) 索引，那么只要将 a 索引 改为 (a,b) 索引即可，不需要新加一个索引。
而且执行SQL时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。所以说，索引的数量不需要太多。
索引的优化方法
1）索引不会包含有NULL值的列
只要列中包含有NULL值，都将不会被包含在索引中，组合索引中只要有一列有NULL值，那么这一列对于此条组合索引就是无效的。所以我们在数据库设计时，不要让索引字段的默认值为NULL。
例如：select id from table where num is NULL 可以在num上设置默认值0，确保列中没有NULL值，这样查询可以变为：select id from table where num=0
2）索引列排序
MySQL查询只使用一个索引，因此如果WHERE子句中已经使用了索引的话，那么ORDER BY中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下，不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列也创建组合索引。
3）使用短索引
如果一个索引列，只在前10~20个字符是唯一的，那么就不要堆全部字段做索引。
4）尽可能使用varchar代替char
因为varchar是变长字符串，存储空间相对较小，节省存储空间。对于查询来说，在一个相对较小的字符串上查询效率也会较高。
5）尽量使用数字字段
若只含数值信息的字段不要设置为字符型，这会降低查询和连接的性能，并会增加内存开销。
这是因为MySQL引擎在处理查询和连接时，需要从左到右逐个比对字符串的每个字符，而对于数字，只要比较一次就够了。
6）LIKE语句操作
一般情况下，不建议使用LIKE操作；如果非使用不可，如何使用也是一个研究的课题。
例如：LIKE "%aaaaa%"不会使用索引，但是LIKE "aaa%"却可以使用索引。
7）不要在索引列上进行运算
在建立索引的原则中，提到了索引列不能进行运算，这里就不再赘述了。
SQL 语句的优化
1）尽量避免在 where 子句中使用 > < != 操作符，否则数据库引擎将放弃索引使用全表扫描。
2）尽量避免在 where 子句中使用 or 条件，否则数据库引擎将放弃索引进行全表扫描。（可以使用 union 来代替or连接查询结果）
举栗： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20
3）慎用 in 和 not in。对于一组连续的数据，可以使用 between代替。或者可以考虑使用 exists 代替 in
4）like %aaa% 语句会导致全表扫描。
5）尽量避免在 where 子句中进行表达式操作，这将导致放弃索引使用全表扫描。
举栗：select id from t where num/2=100 应改为: select id from t where num=200
6）尽量避免在 where 子句中对字段进行函数操作，可以在 = 右边进行结果匹配。
举栗：select id from t where substring(name,1,3)=’abc’ 应改为：select id from t where name like ‘abc%’
7）任何地方都不要使用 select * from table ，查询应该指定具体的字段来代替 *，不要返回用不到的字段。
8）应尽量避免向客户端返回大数据量。
如果结果条数过多，考虑在业务端使用分页请求。
如果有大字段(BLOB)，则最好在业务端提供剔除大字段的接口请求（例如 selectById 和 selectByIdwithBlob）。
（好吧这一条不属于SQL优化，但是也需要牢记）
（如果有什么错误或者建议，欢迎留言指出）
（本文内容是对各个知识点的转载整理，用于个人技术沉淀，以及大家学习交流用）
参考资料：
在一个千万级数据库查询中，如何提高查询效率
Mysql学习之索引
MySQL最左匹配原则的理解
SQL优化-索引——掘金
SQL优化——简书
SQL优化核心思想
