MySQL优化 索引

没有索引，就像书没有目录一样。

1、  使用explain 查看SQL运行性能；

2、  索引的理解：数据量越大，索引越重要，可以解决大部分问题。添加索引的列要在where语句后有用到才有效，否则为无效索引。

3、  单列索引：

4、  组合索引：多个查询条件时，前导列定位的数据量越小越有效。有组合索引的列，不需要再添加单列索引。

5、  组合索引的传递性：在条件查询中，条件中的列都要添加上索引，若有列没有添加索引，则会导致索引中断，失效。。。（不知道）

6、  MySQL：服务引擎，存储引擎，服务引擎用于处理连接请求，优化等，存储引擎用于存储数据，索引等。

7、  索引的存储：聚集索引，非聚集索引。

8、  主键：最好选择无意义的自增长的列。

9、  加索引的原则：a：where后的条件列需要加索引；a：索引会影响插入效率，索引越多，影响越大；c：分区表查询需要带上分区键；d：键索引的列长度有要求，不能超过一定长度，否则建不了。

10、             慢SQL排查方法：a：是不是使用不合适的索引；b：数据是否正确；c：逻辑是否合适，MySQL不适合数据转换，高并发等情况，适合简短查询的情形。

11、             SQL的效率，explain查看sql效率，explain的sql语句不会真实执行，只会生成执行计划用于查看sql的效率。Explain中的type=all是全表扫描。

12、             Explain首先要看rows看扫描的行数，越少越好，小于千行可以接受的，key表示实现使用的索引，extra，执行情况的描述，有没有发生文件排序，用到临时表，回表等，type看索引的扫描方式，type显示访问类型，是较为重要的一个指标，结果值：

     System > const > eq_ref > ref >ref_or_null > index_merge > unique_subquery > index_subquery >range > index > all

   Type 在range及range以前都是可以接受的。

13、             当数据量达到千以上时，就需要添加索引了。

14、             如果select的段在索引里面都有，则不需要回表，可以直接从索引中取得。

15、             索引的合理：列中的数据区分度越大越好，比如：若列只有男、女两个值，则是不合理的索引，数据区分度太小，如果列的值是身份证ID，则是合理的索引。

16、             子查询没有join的效率好，in或者or的数据项若太多，也不好。用or的地方可以使用union替代。

17、             Limit的偏移量不要太大，太大也是不合理的。

18、             如果数据需要排序，最好在索引中排序，在组合索引中，将order by的列，添加上去。

19、             过滤字段，不要使用函数。

20、             分区表需要带分区键。

21、             案例：分页优化a：第一种优化在索引上完成分页，减少回表。

22、             当在where、on等条件查询中，当两个比较的字段类型不一样时，会发生转义，会进行全表扫描，优化的方法是，使用cast进行数据转换。

23、             案例：SQL逻辑有问题：select * from table_abc where XXX limit 1000000, 100; 这样的逻辑没有办法优化。

24、             案例：or改成union，结果集合并：select * from table_abc where XXX and (col_a = xx or col_b =xxx)  改成 select * from (

     (Select* from table_abc XXX and col_a = xxx;)

    Union

    (Select * form table_abc XXX and col_b = xxx;)

)

25、             案例，关联查询：两张表进行关联时，最好，一张大表与一张小表进行关联，关联的字段要有索引。

26、             案例，查询分区表，但没有加分区键，产生大量sql，优化方式，查询添加分区键。

27、             案例，SQL中用到两次in，就不太好了：select * form table_abc where col_a in (xxx), col_b in (xxx);

28、             案例：in的数据量太在, delete from table_abc where id in (select id from table_defXXXX),  这样的语句会很慢，子查询会产生大量的数据在in中，优化方式，使用关联查询。

29、             案例，组合索引，排的顺序不合理，前导列，不合适，前导列，过滤出的结果集越少越好。

30、             案例，索引中断，导致的慢SQL。

31、             案例，MySQL没有使用索引，优化方式，强制使用索引：select * from table_abc where time > xxx and time < xxx andstatus = xxx;  索引在status上，但没有使用，优化方式： select * from table_abc abc join  (select * from table_abcwhere status = xxx) def  on abc.id =def.id and time < xxx and time > xxx;

 

分库分表的弱式：汇总、聚合的时候是一个大问题。

因为utf8和utf8mb4两种字符集每个字符占用的字节数不一样，utf8占用3个字节，utf8mb4占用4个字节，

 

Mysql 建立索引，无论是单列索引或者组合索引，对字段长度有要求，不能超过767个字节，所以你们要查下，转换到utf8mb4后，加索引的字段中有没有长度超过 767/4=191,也就是字段长度超过190的，这个你们要排查一遍，有的要提前处理好，我们这边现在提供了新的测试环境，用的是utf8mb4，你们可以测下客户端连接：