mysql索引优化-收藏

in/or 到底能不能用索引

要控制范围优化器可用的内存，使用 range_optimizer_max_mem_size 系统变量：值为 0 表示没有限制&。当值大于 0 时，优化器将跟踪在考虑范围访问方法时所消耗的内存。如果即将超过指定的限制，则放弃范围访问方法，转而考虑其他方法，包括 全表扫描。这可能不太理想。如果发生这种情况，会出现以下警告(其中 N 是当前的 range_optimizer_max_mem_size 值)。

in 两种情况会走全表扫描：

• in 后面条件导致 sql 大小超过 range_optimizer_max_mem_size。
• in 后面条件个数接近或者等于表记录数量，执行引擎认为此时全表扫描更加合适。

or 也是一样的道理。其它 >、>=、<、<=、BETWEEN AND应该也是同样的道理。归根结底都是 范围查询 。

统计排序

一旦 where、having、order by 里的字段是通过 max、min、count 等计算出来的虚拟字段，那么肯定会产生 Using temporary; Using filesort 临时表和 IO 文件排序。

解决办法：适当的建立冗余字段，或者宽表。

深度分页

select * from a_table order by num limit 700000,15

通过执行计划，可以明显的看出，mysql 会将前 700015 条数据取出来，然后丢掉前 700000 条，只取后 15 条数据。前面读取的 700000 条数据是不必要耗时操作。

解决深度分页的方式:<

• 利用覆盖索引延迟关联: select p.name from a_table p inner join (select id from a_table order by num limit 700000,15) p2 on p.id=p2.id; 先通过覆盖索引把 id 拿到，再把这 15 条数据去关联一次拿到其它字段
• 记录上次的位置
• 通过子查询

全文索引

全匹配-使用布尔模式的逻辑运算符

布尔模式的逻辑运算符：

• select * from t_user where match(phone) AGAINST('0797 +12345' in boolean mode) 表示同时包含 0797 和 12345
• select * from t_user where match(phone) AGAINST('0797 -12345' in boolean mode) 表示 0797 必须包含，但不包含 12345
• select * from t_user where match(phone) AGAINST('0797(>94649 <12345)' in boolean mode) 表示匹配 0797,同时包含 94649 的列往前排，包含 12345 的往后排 ，> 、< 提高/降低该条匹配数据的权重值 () 表达式分组
• select * from t_user where match(phone) AGAINST('"0797-1789"' in boolean mode) 完全匹配，被双引号包起来的单词必须整个被匹配。
• select * from t_user where match(phone) AGAINST('"0797-1789" "0797-1234"' in boolean mode) 空格表示 or