Mysql索引优化1

关闭查询缓存

set global query_cache_size = 0;

set global query_cache_type = 0;

force index（索引）where 条件 强制走索引  一般不推荐，因为mysql结构中会通过cost计算出最优sql路线

索引下推

5.6之前 会先从辅助索引表也就是二级索引表中根据联合索引的第一个索引字段找出主键id，然后回表通过主键id再找出排好序的索引，再根据联合索引中后面的字段进行筛选

5.6之后，索引下推就会根据联合索引的第一个字段范围查找出主键id的同时还会去比较联合索引中后面的字段，会先排好序，然后减少回表次数

Cost成本计算（查询成本）

mysql中通过cost成本计算决定这段sql是否走索引还是全表扫描。

trace工具

set session optimizer_trace=“enabled=on”,end_markers_in_json=on; 开启trace   — 一般不要开启，会影响性能

cost计算大概流程

第一阶段：准备SQL，格式化sql

第二阶段：SQL优化（1=1 无意义sql会去掉、会按照联合索引的顺序进行sql优化）预估表的访问成本，走索引和不走索引的情况，分析各个索引的使用成本，扫描的行数并不一定决定性能。

通过cost 找出最优访问路径

第三阶段：执行SQL

order by 和 group by 优化

遵照索引创建顺序的最左前缀法则

如果group by不需要排序的话可以加上order by null禁止排序，这样可以优化group by 语句，且where性能高于having

单路排序和双路排序

双路排序：即会回表查询

单路排序：效率比较高，会都放在内存排序

查询数据太大，临时文件，内存中存放不下的话，有可能会放在磁盘文件排序

单路排序会把所有需要查询的字段都放到sort buffer中，而双路排序只会把主键和需要排序的字段放到sort buffer中进行排序，然后通过主键回到原表中查询所需要的字段

MySQL通过max_length_for_sort_data这个参数来控制排序 小于1024字节 单路  大于1024字节  双路

索引设计原则

1、代码现行，索引后上

2、联合索引尽量覆盖条件（联合索引可以建2-3个）

3、不要在小基数字段上建立索引

4、长字符串可以采用前缀索引（order by 和 group by 不能使用）

5、where 与 order by 冲突时 优先where

6、基于慢sql查询做优化

建议

尽量利用一两个复杂的多字段联合索引，覆盖80%以上的查询

读多写少可以多建两个索引

读多写多就需要少建索引