MySQL索引优化

　　这里仅针对常用的InnoDB存储引擎所支持的BTree索引进行介绍：

一、索引类型

先创建一个新表，用于演示索引类型

<span style="font-size: 15px"><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> index_table (
    id </span><span style="color: #0000ff">BIGINT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> auto_increment COMMENT <span style="color: #ff0000">‘</span><span style="color: #ff0000">主键</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
    NAME </span><span style="color: #0000ff">VARCHAR</span> (<span style="color: #800000; font-weight: bold">10</span>) COMMENT <span style="color: #ff0000">‘</span><span style="color: #ff0000">姓名</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
    age </span><span style="color: #0000ff">INT</span> COMMENT <span style="color: #ff0000">‘</span><span style="color: #ff0000">年龄</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
    phoneNum </span><span style="color: #0000ff">CHAR</span> (<span style="color: #800000; font-weight: bold">11</span>) COMMENT <span style="color: #ff0000">‘</span><span style="color: #ff0000">手机号</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
    </span><span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> (id)
) ENGINE </span><span style="color: #808080">=</span> INNODB AUTO_INCREMENT <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">DEFAULT</span> CHARSET <span style="color: #808080">=</span> utf8;</span>

下图是Col2为索引列，记录与B树结构的对应图，仅供参考：

1、普通索引

这是最基本的索引，没有任何限制。

<span style="font-size: 15px"><span style="color: #008080">--</span><span style="color: #008080">----直接创建索引</span>
<span style="color: #0000ff">create</span> <span style="color: #0000ff">index</span> index_name <span style="color: #0000ff">on</span> index_table(name);</span>

2、唯一索引

索引列的值必须唯一，可以有空值

<span style="font-size: 15px"><span style="color: #008080">--</span><span style="color: #008080">-------直接创建唯一索引</span>
<span style="color: #0000ff">create</span> <span style="color: #0000ff">UNIQUE</span> <span style="color: #0000ff">index</span> index_phoneNum <span style="color: #0000ff">on</span> index_table(phoneNum);</span>

3、主键

是一种特殊的唯一索引，必须指定为 PRIMARY KEY，如我们常用的AUTO_INCREMENT自增主键

4、多列索引

也称为组合索引，就是在多个字段上联合建立一个索引

<span style="font-size: 15px"><span style="color: #008080">--</span><span style="color: #008080">-----直接创建组合索引</span>
<span style="color: #0000ff">create</span> <span style="color: #0000ff">index</span> index_union <span style="color: #0000ff">on</span> index_table(name,age,phoneNum);</span>

这里一个组合索引，相当于在有如下三个索引：

name；

name，age；

name，age，phoneNum；

这里或许有这样一个疑惑：为什么age或者age，phoneNum字段上没有索引。这是由于BTree索引因要遵守最左前缀原则，这个原则在后面详细展开。

 二、索引优化

1、选择索引列

创建索引简单，但是在哪些列上创建索引则需要好好思考。可以考虑在where字句中出现列或者join字句中出现的列上建索引

<span style="color: #0000ff">SELECT</span><span style="color: #000000">
    age</span><span style="color: #008080">--</span><span style="color: #008080">--不使用索引</span>
<span style="color: #0000ff">FROM</span><span style="color: #000000">
    index_union
</span><span style="color: #0000ff">WHERE</span><span style="color: #000000">
    NAME </span><span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">xiaoming</span><span style="color: #ff0000">‘</span><span style="color: #008080">--</span><span style="color: #008080">-考虑使用索引</span>
<span style="color: #808080">AND</span> phoneNum <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">18668247687</span><span style="color: #ff0000">‘</span>;<span style="color: #008080">--</span><span style="color: #008080">-考虑使用索引</span>

2、最左前缀原则

　　联合索引(name,age,phoneNum) ，B+树是按照从左到右的顺序来建立搜索树的。如（‘张三‘,18,‘18668247652‘）来检索数据的时候，B+树会优先匹配name来确定搜索方向，name匹配成功再依次匹配age、phoneNum，最后检索到最终的数据。也就是说这种情况下是有三级索引，当name相同，查找age，age也相同时，去比较phoneNum；但是如果拿 （18,‘18668247652‘）来检索时，B+树没有拿到一级索引，根本就无法确定下一步的搜索方向。（‘张三‘,‘18668247652‘）这种场景也是一样，当name匹配成功后，没有age这个二级索引，只能在name相同的情况下，去遍历所有的phoneNum。

　　B+树的数据结构决定了在使用索引的时候必须遵守最左前缀原则，在创建联合索引的时候，尽量将经常参与查询的字段放在联合索引的最左边。                  

3、like的使用

一般情况下不建议使用like操作，如果非使用不可的话，需要注意:like ‘%abd%‘不会使用索引，而like ‘aaa%’可以使用索引。这也是前面的最左前缀原则的一个使用场景。

4、不能使用索引说明

mysql会按照联合索引从左往右进行匹配，直到遇到范围查询，如：>,<,between,like等就停止匹配，a = 1 and b =2 and  c > 3 and d = 4，如果建立（a,b,c,d）顺序的索引，d是不会使用索引的。但如果联合索引是（a,b,d,c）的话，则a b d c都可以使用到索引，只是最终c是一个范围值。

5、order by

order by排序有两种排序方式：using filesort使用算法在内存中排序以及使用mysql的索引进行排序；我们在部分不情况下希望的是使用索引。

select test_index where id = 3 order by id desc;

如果ID是单列索引，则order by会使用索引

select test_index where id = 3 order by name desc;

如果ID是单列索引，name不是索引或者name也是单列索引，则order by不会使用索引。因为Mysql的一次查询只会从众多索引中选择一个索引，而这次查询中使用的是ID列索引，而不是name列索引。在这种场景下，如果想让order by也使用索引的话，就建立联合索引（id，name），这里需要注意最左前缀原则，不要建立这样的联合索引（name，id）。

最后需要注意mysql对排序记录的大小有限制：max_length_for_sort_data 默认为1024；也就意味着如果需要排序的数据量大于1024，则order by不会使用索引，而是使用using filesort。

MySQL索引优化

标签：数据结构   场景   记录   name   存储   png   tree   支持   演示