sql优化经验漫谈

1. 当用sql查找是否“存在”场景

原sql：

select count(1) from table where a = 1

改为

select 1 from table where a = 1 limit 1

当确定查询结果就是一条时，勇敢的加上limit 1,使数据库查询到一条就返回；
这种判断是否存在的场景，不要再用count（1）、count(*)、count（字段）了

2. where 条件 in的限制

在oracle中，in（）中的数量限制为1000；
在mysql中，没有对in（）中数量进行限制，只是限制了sql语句的大小不能超过4MB,但是in中的数量较大时会使查询语句放弃索引，为了使用到索引，可以分多次查，比如一万条数据，分十次查，每次in中的数量为1000，虽然与数据库的连接次数变多了，但总体查询速度还是变快的，而且还有数据库连接池的存在，所以连接次数变大的影响很弱

3. updade语句要不要考虑索引的命中情况

update语句会先查询到更新的语句然后才进行更新，所以也是需要索引命中情况良好的

4. mysql 预编译 多线程

相同的语句，即时多次连接数据库执行，数据库也只会预编译一次，所以相同的语句一般不会使用多线程处理，而是不同的语句可以考虑使用多线程处理

5. 函数与索引失效的关系

<!-- 这种情况不会失去索引 -->
select name from table where name = upper(#{vo.name})

<!-- 这种情况会失去索引 -->
select name from table where upper(name) = #{vo.name}

6. mysql隐性类型转换与索引失效的关系

前情：id类型是bigint，name类型是varchar（注意引号的添加与否）

<!-- 这种情况不会失去索引 -->
select name from table where id = '1';

<!-- 这种情况会失去索引 -->
select name from table where name = xiaohua;

7. 联合索引

index(a, b, c)其实相当于创建了三个索引：
index(a)、index(a, b)、index(a, b, c)，
所以创建了联合索引index(a, b, c)，就没必要重复创建index(a)这种索引了，还会增加维护索引的开销

8. 联合索引最左匹配原则(乘上)

• a. where条件是select name from table where b = 2, 这种时候不会命中索引；
• b. select name from table where b = 2 and a =3，这个时候就会命中索引，注意最左匹配原则的最左所代表的顺序是索引定义时的顺序，而不是执行语句的顺序，因为mysql优化器会调整sql语句顺序的，语句真正执行时候的样子并不是你写的样子；
• c. 遇上判断等，索引会失效，但不是全部失效，而是定义时排在后面的索引会失效,比如：select name from table where a = 2 and b > 3 and c =4 那么a、b条件会命中索引，c不会;
  -d. 定义联合索引时，重复值最少(即辨别率最高的)在前面，这样索引能最大发挥效率，而不是像一些博客说的重复率低的放前面可以先排除掉大量数据，这是错误的说法，重复率高的列原则上不建议使用索引，重复率高于30%，就应该放弃索引，而走全表扫描，因为这种情况下查询时索引的作用大大降低，却会导致insert和update时维护索引的工作量增加，大大降低效率，而且索引占据磁盘，造成空间的浪费

9. 回表 索引覆盖 聚簇索引 非聚簇索引

回表：索引中的数据不够你用的，需要再去库中查；
索引覆盖：索引中的数据够你用的了，不需要回表到库中查了；
聚簇索引：回表时其实是根据主键去查数据，也就是说，索引中存储的是主键的地址和其他一些索引值，当你需要的值不在这个范围内，则需要根据主键地址回表查数据，聚簇索引指的就是索引中存储的是主键地址，需要更多数据时再根据主键去查数据的这种关系，可以认为聚簇索引就是主键相关
非聚簇索引：非主键索引

10. 前缀索引

• 前缀索引仅仅是选择一个字段的部分字符作为索引，这样一方面可以节约索引空间，另一方面则可以提高索引效率，当然很明显，这种方式也会降低索引的选择性
• 全列选择性 = 非重复记录/所有记录
  SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
• 计算不同前缀长度时的选择性，取出最接近全列选择性的那一个前缀长度：
  SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;
• 前缀系数（前缀长度）注意点：前缀索引的前缀系数的计算要注意字段的数据类型，当是varchar时，前缀系数指的就是字符数，但当字段类型是二进制，前缀系数指的就是字节数了，这个时候一般一个汉字三个字节，一个英文字母一个字节，当然不同的字符集也有区别

11. collate 可以设置mysql区分大小写

不建议使用，最好使用mysql默认的不区分大小写

12. explain cost

相比于explain 中的type来判断索引的使用情况，cost所指代的io和cpu花费情况更能反应一个sql语句的好坏

13. 几种count()区别

count(*): 统计所有的记录，包含null；
count(1): 统计不为null的记录,可以认为是主键的个数
count(column)：统计字段值不为null的记录
count(distinct column): 统计该字段去重且不含null的的记录

14. 索引失效的场景

• 联合索引最左匹配原则：如果左边的值未确定，比如使用了>符号，那么大于符号后面的索引就无法命中
• 计算、函数、类型转换(自动或手动)导致索引失效
• 范围条件右边的列索引失效，跟第一点相同
• 反向/负向条件使索引失效：不等于(!= 或者<>)导致索引失效；
• is null 和 is not null无法使用索引
• like以通配符%开头索引失效
• OR 前后只要存在非索引的列，都会导致索引失效，索引or条件最好转为in,不过in在数据量较大的情况下也会使索引失效，索引in中的集合最好有一定范围，若数量很大，可以分多次查，可以用google的Lists.patition来拆分集合作为入参
• 数据库的字符集不同，进行比较前需要转换，会造成索引失效

15. 什么情况下不推荐使用索引？

• 数据唯一性差的字段不要使用索引
• 频繁更新的字段不要使用索引
• 字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引。
• 尽量使用覆盖索引（只访问索引的查询(索引列和查询列一致)），减少select * 这种全字段查询（会增大数据库压力和io开销）

16. 尽量避免使用hint(查询优化器提示)

作用：使mysql的执行计划参照hint执行，但是数据量的变化会导致sql不会按我们预想的执行，所以hint语句并不会一直生效，在数据量的变化中，有时起作用，有时不起作用，所以在正式环境中，我们应该相信mysql优化器，而不是使用hint
常见的hint：

1、强制索引 FORCE INDEX
SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。
2、忽略索引 IGNORE INDEX
SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
在上面的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。
3、关闭查询缓冲 SQL_NO_CACHE
SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;
有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）,这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。
4、强制查询缓冲 SQL_CACHE
SELECT SQL_CACHE * FROM TABLE1;
如果在my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。
5、优先操作 HIGH_PRIORITY
HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。
SELECT HIGH_PRIORITY * FROM TABLE1;
6、滞后操作 LOW_PRIORITY
LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。
update LOW_PRIORITY table1 set field1= where field1= …
7、延时插入 INSERT DELAYED
INSERT DELAYED INTO table1 set field1= …
INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。坏处是，不能返回自动递增 的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。
8、强制链接顺序 STRAIGHT_JOIN
SLECT TABLE1.* FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
通过hint强制按 TABLE1/TABLE2顺序连接表
9、强制使用临时表 SQL_BUFFER_RESULT
SELECT SQL_BUFFER_RESULT * FROM tbale1
当查询结果集数据比较多时，可以通过SQL_BUFFER_RESULT 强制结果集放到临时表中
————————————————
版权声明：本文为CSDN博主「Two手揣兜」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u013820054/article/details/52159998

17. 覆盖索引

查询出来的列在索引树中，就是覆盖索引

18. 避免回表

InnoDB是通过B+Tree结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键成为聚集索引；而普通索引叶子节点存储的是该记录的主键，然后再通过主键索引找到对应的记录，叫做回表，而聚集索引直接从叶子节点就拿到数据不用回表，所以查询条件最好使用主键，有些时候可以先查出主键（这时候用到了覆盖索引），然后再通过主键查询信息，这时候又避免了回表，而不是一步查出数据，既没有用到覆盖索引还没避免回表，所以有时候一个sql可以先查出主键再根据主键查询信息，只是多了一次IO而已。