MySQL 之索引部分

原创 2018年04月16日 21:45:04

索引概述

索引的本质及作用

索引(也称「键」)是为了加速表中数据行的检索而创建的一种分散的数据结构。

数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为 O(n) 的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search)、二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

索引的优缺点

优点

  1. 大大加快数据的检索速度;
  2. 创建唯一性索引,能保证数据库表中每一行数据的唯一性;
  3. 加速表与表之间的连接;
  4. 在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间。

缺点

  1. 索引需要占用额外的物理空间;
  2. 当对表中的数据进行增加、删除和修改时,索引也需要动态的维护,降低了数据的维护速度。

索引的类型

在 MySQL 中,索引是在存储引擎层而不是服务器层实现的,所以,并没有统一的索引标准。不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层的实现方式也可能不同。

目前 MySQL 提供了常用的四种索引:

  • B-Tree 索引:最常见的索引类型,大部分引擎都支持 B-Tree 索引;
  • Hash 索引:只有 Memory 引擎支持 Hash 索引,基于 Hash 表实现,使用场景简单;
  • R-Tree:空间数据索引,MyISAM 引擎支持,主要用于地理数据存储;
  • 全文索引:一种特殊类型的索引,类似于搜索引擎的功能。

说明: 平时讨论的索引,如果没有特别指明,一般都是指 B-Tree 索引,而且从技术上说,底层实现方式其实是 B+Tree。 B-Tree 索引又分为几种,比如普通索引、唯一索引、主键索引等等。关于全文索引,详见 MySQL 之全文索引

三种常用存储引擎索引支持情况

索引 MyISAM InnoDB Memory
B-Tree Yes Yes Yes
Hash 索引 No No Yes
R-Tree 索引 Yes No No
全文索引 Yes 5.6 之后 No

B-Tree 索引

  • 普通索引:创建普通索引时,不限制任何条件(唯一、非空等限制),该类型的索引可以创建在任何数据类型上。
  • 唯一索引:UNIQUE,唯一索引,就是限制被索引的值必须是唯一的。当为表中某个字段设置主键或唯一性约束时,系统会自动创建关联该字段的唯一索引。
  • 主键索引:PRIMARY,主键索引,一种特殊的唯一索引,不允许有空值。

说明: 关于唯一索引和唯一约束的探讨,详见 谈谈唯一约束和唯一索引

索引的操作

创建

三种创建方式

// 1. 建表时创建
create table 表名 (
    id int(11),
    xxx xxx,
    [索引类型] index|key [索引名] (属性名1 [(长度)] [ASC|DESC]) 
);

// 2. 直接创建
create index 索引名
    on 表名 (属性名 [(长度)] [ASC|DESC]);

// 3. 修改表创建
alter table 表名
    add [索引类型] index|key [索引名] (属性名1 [(长度)] [ASC|DESC]);

注意: 不能使用第二种方式创建主键索引

修改

没必要修改,直接删除重建即可。

删除

两种删除方式

// 1. 直接删除
drop index 索引名
    on 表名;

// 2. 修改表删除
alter table 表名
    drop index 索引名;

查看

show index|keys from 表名 

列的意义:

  • Table:表名
  • Non_unique:如果索引值唯一,则为 0,否则为 1
  • Key_name:索引名
  • Seq_in_index:索引中列的序列号,从 1 开始
  • Column_name:列名
  • Collation:列在索引中的存储形式,在 MySQL 中,值「A」(升序)或 NULL(无分类)
  • Cardinality:索引中唯一值数目的估计值
  • Sub_part:如果列只是被部分地编入索引,则为被编入索引的字符的数目,如果整列被编入索引,则为 NULL
  • Packed:说明关键字如何被压缩,如果没有被压缩,则为 NULL
  • Null:如果列含有 NULL,则值为 YES,如果没有,则该列为 NO
  • Index_type:索引类型(BTREE,FULLTEXT,HASH,RTREE)
  • Comment:更多评论

索引的选择

索引选择原则

  1. 较频繁的作为查询条件的字段应该创建索引;
  2. 唯一性太差的字段(比如性别)不适合单独创建索引,即使频繁作为查询条件;
  3. 更新非常频繁的字段不适合创建索引;
  4. 不会出现在 Where 子句中的字段不该创建索引。

索引选择原则细述

  • 性能优化过程中,选择在哪个列上创建索引是最非常重要的。可以考虑使用索引的主要有两种类型的列:在 where 子句中出现的列,在 join 子句中出现的列, 而不是在 select 关键字后选择列表的列。
  • 索引列的基数越大,索引的效果越好。例如,存放出生日期的列具有不同的值,很容易区分行,而用来记录性别的列,只有「男」和「女」,对此进行索引没有多大用处,因为不管搜索哪个值,都会得出大约一半的行。
  • 使用短索引,如果对字符串列进行索引,应该指定一个前缀长度,可节省大量索引空间,提升查询速度。
  • 利用最左前缀原则。

索引选择注意事项

既然索引可以加快查询速度,那么是不是只要查询语句需要,就创建索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL 在运行时也要消耗资源维护索引,因此索引并不是越多越好。

一般下面两种情况不建议建索引:

  1. 表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了;
  2. 索引的选择性较低,所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:Index Selectivity = Cardinality / #T 显然选择性的取值范围为 (0, 1],选择性越高的索引价值越大,这是由 B+Tree 的性质决定的。

除此之外,还有两个点需要注意:

  1. MySQL 只对以下操作符才使用索引:<、<=、=、>、>=、between、in,以及某些时候的 like(不以通配符 % 或 _ 开头);
  2. 不要过度索引,只保持需要的索引。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。

索引是最好的解决方案?

索引并不总是最好的解决方案。总的来说,只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时,索引才是有效的。对于非常小的表,大部分情况下简单的全表扫描更高效。对于中到大型的表,索引就非常有效。但对于特大型的表,建立和使用索引的代价将随之增长。这种情况下,则需要一种技术可以直接区分出查询需要的一组数据,而不是一条记录一条记录地匹配。这是就要使用分表、分库等分区技术了。

参考文章

MySQL索引背后的数据结构及算法原理
索引的利弊与如何判定,是否需要索引
高性能 MySQL(第三版)

本文原始链接:MySQL 之索引部分

版权声明:本文为博主原创文章,如转载需注明出处。 https://blog.csdn.net/mrzhouxiaofei/article/details/79967162

mysql建立部分索引

-- 计算部分索引平均值 SELECT COUNT(DISTINCT t.show_sentence)/COUNT(1) from t_user_show t; -- 找出部分索引最佳长度(得...
  • ahzxj2012
  • ahzxj2012
  • 2016-06-08 08:09:59
  • 1028

MySQL 第六篇:索引与子查询

一、子查询子查询,从原有的查询语句中 嵌入新的查询 来得到我们想要的结果,也可称为嵌套查询。1、where 型a、查询课程名为“Java”的学生信息-- 使用关联查询实现 SELECT s.*...
  • w1lgy
  • w1lgy
  • 2017-10-28 00:47:16
  • 312

MySQL索引 聚集索引

MySQL索引 聚集索引 如果你想了解MySQL索引查询优化,你首先应该对MySQL数据组织结构、B-Tree索引、聚集索引,次要索引有一定的了解,才能够更好地理解MySQL查询优化行为。这里主...
  • lixiaohuiok111
  • lixiaohuiok111
  • 2011-08-30 15:06:44
  • 8451

Mysql RR隔离更新列没有索引 会锁全表

Session 1: mysql> show index from test; Empty set (0.00 sec) mysql> update test set name='xxxx' whe...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2016-06-07 14:03:47
  • 897

mysql索引命中规则

最左匹配原则 1、先定位该sql的查询条件,有哪些,那些是等值的,那些是范围的条件。 2、等值的条件去命中索引最左边的一个字段,然后依次从左往右命中,范围的放在最后。分析讲解 1...
  • xupeng874395012
  • xupeng874395012
  • 2016-11-29 11:00:16
  • 4715

索引失效&子查询-慢查询的起因

Mysql中子查询和索引失效会带来检索效率下降的问题,这里列出了新手常常会犯的一些错误...
  • baidu_zhongce
  • baidu_zhongce
  • 2015-07-11 22:06:52
  • 3025

mysql的最佳索引攻略

Explain优化查询检测 所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找,而用的最多,并且是mysql默认...
  • GarfieldEr007
  • GarfieldEr007
  • 2017-01-12 23:00:11
  • 1648

mysql可能造成索引失效的场景

1、当使用or的情况下,如果不是每一列的条件都有索引,索引失效 2、当使用多列索引的时候,没有匹配到第一部分,索引失效 3、当使用like的时候,以%开头,索引失效 4、当数据类型是字符串类型的...
  • xupeng874395012
  • xupeng874395012
  • 2017-05-23 10:30:00
  • 1294

Mysql中的联合索引、前缀索引、覆盖索引

Mysql中的联合索引、前缀索引、覆盖索引索
  • Air_hjj
  • Air_hjj
  • 2017-04-16 13:09:01
  • 1740

mysql索引总结----mysql 索引类型以及创建

关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型...
  • xluren
  • xluren
  • 2014-06-20 23:28:29
  • 209571
收藏助手
不良信息举报
您举报文章:MySQL 之索引部分
举报原因:
原因补充:

(最多只允许输入30个字)