Mysql 索引优化

1. 创建高性能索引

正确的创建和使用索引是实现高性能查询的基础
MySQL优化(5)：索引失效分析、in与exists使用场合 - 湮天霸神666 - 博客园
① 全值匹配我最爱

② 最左前缀要遵循

• 查询从索引的最左列开始（带头大哥不能死）
• 不能跳过索引中的列（中间兄弟不能断）

③ 不在索引列上做任何的操作

• 导致索引失效而转向全表扫描

④ 范围之后全失效

• 存储引擎不能使用索引中范围条件右边的列
  
• age用到了，用于range操作，确定范围
• pos用不到

⑤ 尽量少用"*"

• 尽量使用覆盖索引

⑥ 不等空值还有or，索引失效要少用

• 使用 != 的时候无法使用索引导致全表扫描
• is not null无法使用索引，所以尽量避免空值，为每个列增加default value
• 用or可能会导致索引失效

⑦ LIKE符号写最右
MySQL 中like的使用对于索引的影响 - 米饭！大米饭 - 博客园
否则会导致索引失效，并且like是范围

如何解决两边加%索引失效的问题
用覆盖索引避免全表扫描，建立的索引和查询的字段对的上，这能避免全表扫描，因为可以直接去索引树查询

⑧ varchar引号不能丢
是类型转换引起的问题，所以在我们java程序中，bean定义的字段类型必须和数据库的字段类型匹配

1.1 独立的列

如果查询中的列不是独立的，则Mysql就不会使用索引，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，也就是上面提到的第三点不在索引列上做任何的操作，它主要包括下面了几种情况

• 计算，函数，自动或手动的类型转换
• varchar引号不能丢，否则可能出现类型转换

所以下面的查询是无法使用actor_id列的索引的

select actor_id from actor where actor_id+1=5

mysql无法自动解析actor_id+1这个方程式，所以无法使用到索引

1.2 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得很大很慢（因为每个页上能存储的数据变少了），为了解决这个问题，我们可以只索引该字符列开始的部分字符，这样可以大大的节约索引空间，从而提高索引效率；

我们要保证我们选择的前缀的大小是合适的且选择性足够高（不重复的索引值/数据表的记录总数，最好能让前缀的选择性接近完整列的选择性）

当然，如果我们使用了前缀索引，就无法使用他们做ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描

1.3 多列索引

多列索引并不是为每个列创建单独的索引，而是将多个列联合起来建立一个索引
有如下的表

create table t(
	c1 int,
	c2 int,
	c3 int,
	key(c1),
	key(c2),
	key(c3)
);

我们为所有的列都建立上单独的单列索引，因为我们每次select查询只能使用到有个索引，索引在大部分情况下实际上多个单列索引是不能优化查询性能的，比如如下查询，哪个单列索引都不是好的选择

select c1, c2 from t where c1 = 1 or c2 = 1;

在老版的mysql中，mysql会对这个查询使用全表扫描，除非写成如下的两个查询union的方式

select c1, c2 from t where c1 = 1
union all 
select c1, c2 from t where c2 = 1 and c1<>1

索引合并
在新版本的mysql中引入了索引合并的策略，一定程度上能使用表上的多个到单列索引来定位指定的行
查询能够同时使用两个单列索引来进行扫描，并将结果合并，这种算法有三个变种

• OR条件联合
• AND条件相交
• 组合前两种情况的联合和相交

索引合并是一种优化结果，但是实际上更多时候说明了表上的索引建立的很糟糕

1.4 选择合适的索引顺序

在一个B树索引中，索引列的顺序意味着首先按照最左列进行排序，其次是第二列等等，所以，索引可以按照升序或降序进行扫描，以满足精确符合列顺序的ORDER BY ，GROUP BY 和DISTINCT等子句的查询需求

• 当不需要考虑排序和分组的时候，将选择性高的列放在前面通常是很好的，这是索引的作用只是用于优化where条件的查找
• 但是有时候这并没有避免随机IO和排序那么重要

1.5 覆盖索引

MySQL 覆盖索引详解
覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能，也就是说，这种情况下索引的叶子节点已经包含了所有需要查询的字段的值

并不是所有类型的所有都能成为覆盖索引的，例如hash索引等他们没有存储索引列的值，自然不能成为覆盖索引

1.6 唯一索引vs普通索引

由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引

2. 索引失效

后端程序员必备：索引失效的十大杂症

① 查询条件包含or，可能导致索引失效

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select * from user where userid = 1 or age = 8;

• 对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程： 全表扫描+索引扫描+合并
• 如果它一开始就走全表扫描，直接一遍扫描就完事。
• mysql是有优化器的，处于效率与成本，遇到or条件，索引可能失效，看起来也合情合理
• 如果or条件的列都加了索引，索引可能会走的

② like通配符可能导致索引失效

并不是用了like通配符，索引一定失效，而是like查询是以%开头，才会导致索引失效
如果非得使用like使用索引的话，就要使用覆盖索引来解决，你建的索引和查询的字段上一样

③ 不再索引列上做任何操作(计算、函数、（自动or手动）类型转换)，会导致索引失效而转向全表扫描

在索引列上使用mysql的内置函数，索引失效
对索引列运算（如，+、-、*、/），索引失效
如何字段类型是字符串，where时一定用引号括起来，否则索引失效，因为他会做隐式类型转换

④ 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，不跳过索引中间的列。（带头大哥不能死，中间兄弟不能丢）

⑤ 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效

⑥ 存储引擎不能使用索引中范围条件右边的列（范围之后全失效）

若中间索引列用到了范围（>、<、like等），则后面的所以全失效

⑦ 尽量使用覆盖索引（只访问索引的查询(索引列和查询列一致)），减少select *

⑧ IS NULL和IS NOT NULL也无法使用索引