mysql索引及优化

1.索引的类型

mysql的索引可以从不同的维度来进行区分，如下：

• 从索引存储结构划分：B Tree索引，Hash索引， FULLTEXT索引，R Tree索引
• 从应用层次划分：普通索引，唯一索引，主键索引，复合索引
• 从索引键值划分: 主键索引，辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：聚簇索引，非聚簇索引

1.1 普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

• CREATE INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
• CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名) );

1.2 唯一索引

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一
约束，就会自动创建对应的唯一索引。

• CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
• CREATE TABLE tablename ( […], UNIQUE [索引的名字] (字段名) ;

1.3 主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主
键。

• CREATE TABLE tablename ( […], PRIMARY KEY (字段名) );
• ALTER TABLE tablename ADD PRIMARY KEY (字段名);

1.4 复合索引

用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合
索引所需的开销更小。

• CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2…);
• ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2…);
• CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名1，字段名2…) );

1.5 全文索引

如果使用全文索引，查询速度会比like快很多倍。（mysql5.6之前只有myisam引擎支持，5.6之后innodb支持）

• CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
• CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;

2.索引原理

索引涉及的理论知识：二分查找，Hash和B Tree。 这里我们不讨论二分查找，来分析一下hash和B Tree

2.1 Hash

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询（此时的时间复杂度最好可以达到O(1)）,但是对于范围查询， 就需要全表扫描了。其结构如下所示：

Hash索引在mysql中Hash结构主要应用在InnoDB自适应哈希索引。

InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当

InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。

2.2 B+Tree结构

mysql采用的是B+Tree结构， 是对BTree结构上做了优化

• B-Tree结构
  1. 索引值和data数据分布在整棵树结构中
  2. 每个节点可以存放多个索引值及对应的data数据
  3. 树节点中的多个索引值从左到右升序排列

BTree的搜索是采用二分查找。

• B+Tree结构
  1. 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
  2. 叶子节点包含了所有的索引值和data数据
  3. 叶子节点用指针连接，提高区间的访问性能

相当于BTree，在进行范围查找时，只需要查找定位2个节点的索引值，然后利用叶子节点的指针进行遍历即可。

3. 索引优化

3.1 使用EXPLAIN查看索引使用情况

主要查看以下这几个字段

属性名	属性解释	建议
type	连接类型	至少要达到range级别。杜绝出现all, index
key	使用到的索引名	如果没有选择索引，值是NULL。可以采取强制索引方式
key_len	索引长度
rows	扫描行数
extra	详细说明	常见的不太友好的值，如下：Using filesort，Using temporary

3.2 SQL语句中IN包含的值不应该过多

mysql对于IN做了优化，会把IN中的数据全部存储在一个数组里面，而且这个数组是会拍好序的，如果数值过多，消耗也会过大。建议值在1000以内。

3.3 SELECT语句务必指明字段名称

select * 会增加多余的消耗（CPU ，IO，内存，网络带宽），减少了使用覆盖索引的可能性。

3.4 当只需要一条数据的时候，使用limit 1

limit是可以停止全表扫描

3.5 排序字段加索引

order by 后面的字段加索引， 可以消除extra中的Using filesort， 如果可以的话， 可以和查询条件一起加一个复合索引。

3.6 如果where条件中有字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，会造成该查询不走索引的情况。

3.7 尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，
增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

3.8 不使用ORDER BY RAND()

order by 后面使用 rand()函数， 会不走索引

3.9 区分in和exists、not in和not exists

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为
驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合
于外表小而内表大的情况。

3.10 使用合理的分页方式以提高分页的效率

分页使用 limit m,n 尽量让m 小， 如果m足够大， 就会走出查询效率降低即使有索引的存在下，可以先使用id定位到m记录之前来消除m过大的问题。

3.11 分段查询

如果查询的范围过大的话， 需要把这条语句查询的结果分为几次查询。 主要是不要让一条sql语句扫描表记录过多。

3.12 不建议使用%前缀模糊查询

使用%开头， 就会使得索引失效

3.13 避免在where条件上对字段进行表达式操作

会使得索引失效

3.14 避免隐式类型转换

比如一个字段为int型， 写入sql语句时， 明确为where aa=111 而表示aa=‘111’

3.15 对于复合索引，要遵循最左原则

比如有个符合索引（a,b,c） 如果条件出现>,<,!=,between,in等范围操作符，都会造成从该字段开始索引的后半部分失效。 比如 where a=1 and b>2 and c=3， 此时只会用到索引(a)

3.16 必要时可以使用force index来强制查询走某个索引

有时候一条sql语句经过mysql查询优化之后， 不会走索引， 可以在sql语句后面加上force index来强制走索引

1.17 使用JOIN优化

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

当你不太清楚， 应该哪张表为驱动表时， 可以使用inner join来进行sql查询， 让mysql自动帮你选择。总而言之，就是使用join时， 需要让小表来驱动大表