mysql 索引使用及优化详情_Mysql_

前言

索引对有一定开发经验的同学来说并不陌生，合理使用索引，能大大提升sql查询的性能，可以这么讲，随着业务数据量的不断增长，优化系统的响应速度，很大程度上可以说就是集中在索引的优化上；

mysql索引原理

在正式了解与学习mysql索引之前，先对mysql的索引原理再次回顾下；

我们知道，目前大多数使用的mysql引擎为 innodb，而innodb引擎使用的是 B+ Tree，下面通过几张图快速了解下 B+ Tree的结构，

假如存在下面这张表：

那么通过 B+ Tree构建出来的 “ 以ID为主键索引 ” 的树形结构如下：

说明：

• 叶子节点存放的是ID对应的一条完整的记录；
• 查找一条记录时，如果是按照ID搜索，则会采用类似二叉树的方式，最终定位到叶子节点的ID对应的那条记录；

也就是说，如果查询的时候，以ID为参数，则效率是最高的，反之，如果以非主键字段，建立索引，则B+Tree的索引结构将会如下，

说明：

• 主键索引构建出来的 B+Tree 结构保持不变；
• 再以主键ID之外的字段，构建出一个B+Tree结构，其叶子节点存储的是字段的值以及对应的主键值；

以上关于mysql 的inndb的索引结构原理先介绍到这儿，后文中将会用到里面的东西，还请留意；

mysql索引分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等；

• 从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引；
• 按照 物理实现方式 ，可以分为 2 种：聚簇索引和非聚簇索引；
• 按照 作用字段个数 进行划分，分成单列索引和联合索引；、

常规分类

• 主键索引，针对表的主键创建的索引，默认建表的时候，自动创建，有且只能有一个；
• 唯一索引，为了避免一个表中的某列数据出现重复的值，可以有多个，关键字：UNIQUE；
• 常规索引，用于快速定位特定字段的数据，可以有多个；全文索引，
• 全文索引常用于查找文本中的关键词，而不是比较索引中的值，可以有多个，关键字FULLTEXT；

补充说明

不同的存储引擎支持的索引类型也不一样

• InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash索引；
• MyISAM ： 支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
• NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
• Archive ：不支持 B-tree、Hash、Full-text 等索引；

索引创建语法

数据准备，有如下建表sql

CREATE TABLE `user` ( `user_id` VARCHAR (32) NOT NULL COMMENT '用户ID', `user_name` VARCHAR (64) DEFAULT NULL COMMENT '用户姓名', `passwd` VARCHAR (64) NOT NULL COMMENT '密码', `email` VARCHAR (64) DEFAULT NULL COMMENT '邮箱', `mobile` VARCHAR (32) DEFAULT NULL COMMENT '手机号', `address` VARCHAR (128) DEFAULT NULL COMMENT '地址', `ID` VARCHAR (18) DEFAULT NULL COMMENT '身份证号', `sex` INT (11) DEFAULT NULL COMMENT '用户性别 1:男 2:女', PRIMARY KEY (`user_id`) ) ENGINE = INNODB DEFAULT CHARSET = utf8;

1、创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

• UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
• index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
• col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
• length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC 或 DESC 指定升序或者降序的索引值存储；

比如给上表的user_name创建一个唯一索引

CREATE UNIQUE INDEX user_name_idx on `user`(user_name); 

2、查看索引

SHOW INDEX FROM table_name;

3、删除索引

DROP INDEX index_name ON table_name;

删除上面的表创建的唯一索引：

4、为 username和password创建联合索引

create index name_passwd_index on `user`(user_name, passwd);

5、给user表添加一个info的字段，并为这个字段添加全文索引

ALTER TABLE `user` ADD FULLTEXT ( `info` );

全文索引用match+against方式查询：

SELECT * FROM `user` WHERE MATCH(字段名称) AGAINST (‘查询字符串');

注意点：

使用全文索引前，搞清楚版本支持情况，不同的版本可能对全文索引支持不一样；全文索引比 like + % 快 N 倍，但是可能存在精度问题；如果需要全文索引的是大量数据，建议先添加数据，再创建索引；

已经存在的表创建、删除索引等

现实中，如果之前已经给表创建过相关的索引，现在需要修改或删除，或重新创建，该如何做呢？

1、使用ALTER TABLE语句创建索引

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],…) [ASC | DESC]

比如给 user表的mobile字段添加索引

ALTER TABLE `user` ADD INDEX `idx_mobile` (`mobile`);

2、使用ALTER TABLE语句删除索引

ALTER TABLE table_name DROP INDEX index_name;

或者直接使用drop语句

DROP INDEX index_name ON table_name;

说明

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除；

常用的索引设计原则

在实际开发中，索引并不是越多越好，创建索引需结合业务情况进行综合考量，下面结合实际经验列举出一些常用的索引设计原则，作为创建索引时的参考；

1、字段值在业务中具备唯一性

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引，比如用户表中，可以通过身份证号或微信号作为 唯一索引；

2、频繁作为 WHERE 查询条件的字段

某字段在SELECT语句WHERE 条件中频繁使用，就需要给这个字段创建索引。尤其是数据量大时，创建索引就可以大幅提升查询的效率；比如用户表的 user_id

3、UPDATE、DELETE 的 WHERE 条件列

我们知道，如果更新的这个列创建了索引，在真正执行数据更新的时候，不仅要更新数据，还要更新这个列的索引信息，在这种情况下，建议只对查询的那个字段创建索引（非索引字段更新的时候速度更快）；

4、经常 GROUP BY 和 ORDER BY 的列

索引是让数据按某种顺序进行存储或检索，因此使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多个，那么可以在这些列上建立 组合索引 ，以提升速度；

5、对DISTINCT 字段创建索引

有时候需要使用DISTINCT对某字段进行去重，那么对这个字段创建索引，也会提升查询效率。这是因为索引会对数据按照某种顺序进行排序，所以有了索引在去重的时候会快很多。

6、使用列的类型小的创建索引

7、使用字符串前缀创建索引

举个例子来说，在user表中存在一个邮箱eamil字段，通常来说，对于一个稳定的业务系统，user标准的email格式可以说格式上是固定的，比如 数字@qq.com，名称@163.com等；

我们知道索引也是要占用存储空间的，字段的长度越长，创建的索引最终占用的空间也越大，当表的数据量大到一定程度，查询时就算是检索走索引也会花费较长时间，这时候就可以考虑使用字符串前缀创建索引了；

可以使用下面的语句创建字符串前缀创建索引：

alter table useradd index(email(6));

这里的问题是，截取多少呢？截取多了，达不到节省索引存储空间的目的；截取少了，重复内容太多，字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢？下面给出一个经验公式作为参考，

先看一下字段在全部数据中的选择度：

select count(distinct email) / count(*) from user;

通过不同长度去计算，与全表的选择性对比:

count(distinct left(列名, 索引长度))/count(*)

8、使用最频繁的列放到联合索引的左侧

这样可以尽可能的向查询时的最佳左前缀原则靠拢；

9、在多字段都要创建索引的情况下，联合索引优于单值索引

试想当user表中，当user_name，mobile都需要创建索引时，分别创建两个单列索引带来的开销，与创建一个联合索引带来的开销哪个更大呢？

10、 区分度高(散列性高)的列适合作为索引

如果表中存在性别这样的字段，就不太适合创建索引，这个需要注意；

11、多表 JOIN 时，创建索引注意事项

• 连接表数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率；
• 尽可能对 WHERE 后面的条件字段创建索引 ，因为 WHERE 才是对数据条件的过滤；
• 对于连接字段创建索引 ，并且该字段在多张表中的类型必须一致 ，字段类型不一致将会带来较大的查询性能损耗；

12、有大量重复数据的列上不要建立索引

在这样的列上一旦创建了索引，比如表中有50万数据，你需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大；

一个经验值

当数据重复度大，比如高于 10% 的时候，就不需要对这个字段创建索引；

13、避免对经常更新的表创建过多索引

这个算是常识性的经验了，更新数据时候，不经要更新数据本身，还需要更新索引；

14、不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等；

15、不要定义冗余或重复的索引

即对同一个字段而言，不要创建多个不同名称的索引，这样只会增加维护的成本，并不会对搜索有什么好处；

16、及时删除不再使用或者很少使用的索引

减少索引存储空间对整个表空间的开销

索引失效情况总结

1、不要在索引列上进行操作计算

计算、函数、类型转换(自动或手动)导致索引失效，上面的user表中，我们给phone创建了索引，但如果使用下面的函数进行查询，索引

提示： 本文由神整理自网络，如有侵权请联系本站删除！
本站声明：
1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！