mysql索引优化-收藏

原文章：https://www.cnblogs.com/eryuan/p/17488732.html

https://www.cnblogs.com/eryuan/p/17430377.html

 

in/or到底能不能用索引

参数range_optimizer_max_mem_size

要控制范围优化器可用的内存，使用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条数据是不必要耗时操作。

解决深度分页的方式:

1 利用覆盖索引延迟关联: 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条数据去关联一次拿到其它字段

2 记录上次的位置

3 通过子查询

 

全文索引

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

布尔模式的逻辑运算符：

1  select * from t_user where match(phone) AGAINST('0797 +12345' in boolean mode)  表示同时包含0797和12345

2  select * from t_user where match(phone) AGAINST('0797 -12345' in boolean mode)  表示0797必须包含，但不包含12345

3  select * from t_user where match(phone) AGAINST('0797(>94649 <12345)' in boolean mode)  表示匹配0797,同时包含94649的列往前排，包含12345的往后排 ，> < 提高/降低该条匹配数据的权重值  () 表达式分组

4  select * from t_user where match(phone) AGAINST('"0797-1789"' in boolean mode)  完全匹配，被双引号包起来的单词必须整个被匹配。

5  select * from t_user where match(phone) AGAINST('"0797-1789" "0797-1234"' in boolean mode)  空格表示 or