• 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html
  • 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html

MySQL索引优化

技术杂谈 勤劳的小蚂蚁 3个月前 (01-12) 80次浏览 已收录 0个评论 扫描二维码

MySQL官方对索引的定义:索引是帮助MySQL高效获取数据的数据结构。索引是在存储引擎中实现的,所以每种存储引擎中的索引都不一样。如MYISAM和InnoDB存储引擎只支持BTree索引;MEMORY和HEAP储存引擎可以支持HASH和BTREE索引。

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

一、索引类型
先创建一个新表,用于演示索引类型
CREATETABLE index_table (
  idBIGINTNOTNULL auto_increment COMMENT‘主键’,
  NAMEVARCHAR (10) COMMENT‘姓名’,
  age INTCOMMENT‘年龄’,
  phoneNum CHAR (11) COMMENT‘手机号’,
  PRIMARY KEY (id)
) ENGINE = INNODB AUTO_INCREMENT = 1DEFAULTCHARSET = utf8;

下图是Col2为索引列,记录与B树结构的对应图,仅供参考:
1、普通索引
这是最基本的索引,没有任何限制。
——直接创建索引
createindex index_name on index_table(name);

2、唯一索引
索引列的值必须唯一,可以有空值
———直接创建唯一索引
createUNIQUEindex index_phoneNum on index_table(phoneNum);

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

4、多列索引
也称为组合索引,就是在多个字段上联合建立一个索引
——-直接创建组合索引
createindex index_union on index_table(name,age,phoneNum);

这里一个组合索引,相当于在有如下三个索引:
name;
name,age;
name,age,phoneNum;
这里或许有这样一个疑惑:为什么age或者age,phoneNum字段上没有索引。这是由于BTree索引因要遵守最左前缀原则,这个原则在后面详细展开。

 二、索引优化
1、选择索引列
创建索引简单,但是在哪些列上创建索引则需要好好思考。可以考虑在where字句中出现列或者join字句中出现的列上建索引
SELECT
   age—-不使用索引
FROM
   index_union
WHERE
   NAME = ‘xiaoming’—考虑使用索引
AND phoneNum = ‘18668247687’;—考虑使用索引

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 whereid = 3orderbyiddesc;

如果ID是单列索引,则order by会使用索引
select test_index whereid = 3orderbynamedesc;

如果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。

丨极客文库, 版权所有丨如未注明 , 均为原创丨
本网站采用知识共享署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议进行授权
转载请注明原文链接:MySQL索引优化
喜欢 (0)
[247507792@qq.com]
分享 (0)
勤劳的小蚂蚁
关于作者:
温馨提示:本文来源于网络,转载文章皆标明了出处,如果您发现侵权文章,请及时向站长反馈删除。

您必须 登录 才能发表评论!

  • 精品技术教程
  • 编程资源分享
  • 问答交流社区
  • 极客文库知识库

客服QQ


QQ:2248886839


工作时间:09:00-23:00