浅谈mysql索引优化
mysql优化分为很多种：包括基础设施优化、 配置优化、表结构优化、sql语句优化等，其中sql语句优化的成本是最低的，因此我们优化mysql时最先考虑的就是mysql语句优化。说到mysql语句优化，就离不开mysql索引，mysql优化器也会对sql与进行大量的优化，当然这个也是依据我们的配置来的。
mysql索引优化的种类
where条件优化、范围优化、索引合并优化、哈希连接优化、索引条件下推优化（ICP）等。
where条件优化
sql优化器会根据联合索引的顺序对where条件进行重排序；
sql优化器会将多个条件合并，如：where b>2 and b=5 and b<6 or b>3 and b=4 会被优化为where b>2，我们只需要按照易于阅读的方式写sql即可。
执行计划时，在extra字段里的：using where，可能指的就是这个，即用到了条件过滤。
范围优化
如果用了范围优化，type字段里是ref，extra字段里可能是：Using index for skip scan，前提是系统变量中设置：skip_scan=on。 range_optimizer_max_mem_size 这个配置可以设置优化器执行范围优化时可用 的内存大小，设置0则无限制。
索引合并优化
如果用到了索引合并优化，则在extra中值为：Using intersect、Using union、Using sort_union,当用到两个交叉索引时为Using intersect，当用到了两个不交叉的索引时为：Using union，当条件中包含范围并且用到了两个并集索引则值为：Using sort_union。 要想让优化器可以进行索引合并优化，前提要开启系统变量：index_merge, index_merge_intersection, index_merge_union, index_merge_sort_union 。默认情况下，这些参数都是开启的。
哈希连接优化
如果用到了哈希连接 优化，在extra中值为：Using join buffer (hash join)。在mysql8.0以及以后，join（outer join）,left join（outer left join）,right join,Inner non-equi-join,Semijoin,Antijoin都有机会走哈希连接优化。如果想让优化器使用哈希连接优化，需要在系统参数重开启：hash_join=on 。join_buffer_size可以控制 可用的哈希连接的内存大小，如果超过了内存大小，则open_files_limit 可以控制可打开的文件数。 （用EXPLAIN FORMAT=TREE语句可以看到每个步骤执行时的代价）
索引条件下推优化（ICP）(回表)
ICP只可以用在二级索引上，如果使用了ICP，则在extra上的值是：Using index condition.
执行的步骤：1.先通过索引条件在二级索引树上找到对应的节点范围；2.在通过其他条件过滤出真正需要的数据行；3.通过这些行节点中的id在聚簇索引树上获取所有的数据（全行扫描）。
如果要使用ICP机制，需要开启：SET optimizer_switch = ‘index_condition_pushdown=on’; 默认是开启的。
如：SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;
在这个表中有一个联合索引：zipcode_lastname
则会按照先前的步骤执行。如果索引改为zipcode，则二级索引树上没有lastname字段，则需要先在聚簇索引树上拿到所有数据，在根据lastame字段过滤所需要的数据行。
为什么ICP只可以在二级索引上用，而不可以在主键索引上用呢，我的理解：ICP的本质是为了减少IO次数，而主键索引中可以获取到所有的字段，并不能减少IO，这个于设计初衷相违背（设计初衷是为了减少IO次数）。
优化场景
1.创建表test(id,code,user_id,name,address), 索引字段为idx_code_userId(code , user_id), 进行查询:
select * from test where code in (‘2’,‘3’,‘15’); 此时执行计划发现key=‘idx_code_userId’,type=‘range’,extra=‘using index condition’，说明发生了回表，用到了范围优化。
select * from test where code in (‘2’);此时执行计划发现：key=‘idx_code_userId’,type=‘ref’,ref=‘const’,extra=null，此时查询语句等价于:select * from test where code=‘2’; 即用到了索引。
注：华为云das和ddm执行计划时表现情况不一致，das输出的执行计划是对的。