首页 / MYSQL / MySQL 索引及其分类

MySQL 索引及其分类

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 索引及其分类，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6381字，纯文字阅读大概需要10分钟。

内容图文

概述

用过 mysql 的童鞋都知道建立索引的必要性，可是不少人对建立索引的目的仅仅停留于建立索引可以让查询变快

然而，为了达到这个目的，科学合理的建立索引也是非常有必要的

索引能够轻易将查询性能提高几个数量级，而一个“最优”索引有时比一个“好的”索引性能要高两个数量级

在 MySQL 中，索引可以包含一个活多个列的值，因为 MySQL 只能高效地使用索引的最左前缀列，所以包含多个列的索引中列的顺序也十分重要

而创建一个包含两个列的索引和创建两个分别包含一列的索引是大不相同的

索引的类型

MySQL 中，索引的类型有很多类型，能够为不同场景提供更好的性能

索引是在存储引擎层实现的，不同存储引擎的索引工作方式不同，也并不是所有引擎都支持全部的索引类型，而对于同一类索引，不同引擎的底层实现也可能是不同的

B-Tree 索引

大多数 MySQL 存储引擎都支持 B-Tree 索引，也因此，B-Tree 索引是最常用的索引类型，如果未加说明，索引一般都指的是 B-Tree 索引

然而，虽然在创建表时关键字都是 B-Tree，但是各个存储引擎的底层实现可能是不同的，如 NDB 集群存储引擎内部实际上使用了 T-Tree 结构，而 innoDB 使用的 B+ Tree

磁盘 IO 与预读

由于磁盘读取靠的是机械运动，每次都要花费寻道时间、旋转延迟、传输时间三部分时间才能读取数据，总计时间是非常长的，如果针对数据库的动辄十万百万乃至千万级的数据查询，每次几毫秒的时间，结果将会是灾难性的

因此操作系统对此进行了一些优化，每次读取时并不仅仅读取需要的数据，而是把相邻数据全部读取到内存缓冲区中，这样，每次都读取一页数据（4KB 或 8KB），而针对一页上数据的读取，事实上仅进行了一次磁盘 IO 操作

B-Tree 的特性

B-Tree 的结构如下图：

MySQL 索引及其分类 - 文章图片

由于 B 树的多分支结构特性，导致树的高度可以大幅下降，这样，如果每个节点都存储一页数据，如果需要访问第三层数据，则只需要进行三次磁盘 IO，这显然大幅的节省了时间

B+ 树与 B 树的区别在于只有叶子节点存储真实数据，其余非叶子结点仅作为指引搜索方向的数据项

这样存储引擎不再需要全表扫描，而是根据每个节点的指引可以快速找到需要的数据

同时，由于 B 树的结构特性，也导致所有的值通常都是按顺序存储的，因此在使用 ORDER BY 操作时，这个索引也可以满足对应的排序需求

多列索引的匹配规则

CREATE TABLE People (
   a   varchar(50)   not null,
   b   varchar(50)   not null,
   c   date       not null,
   d   date       not null,
   e   enum(‘m‘, ‘f‘)   not null,
   key(a, b, c, d)
);

对于上面这个表，创建了四列索引，他们遵循下列规则

最左前缀匹配原则

这是一个非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询（>、<、between、like）

比如查询 a="" and b="2" and c >= 3 and d = 4

在这个查询中，d 是用不到索引的，而如果建立 (a, b, d, c) 则是可以的

同时 where 语句中查询的顺序是可以任意调整的，即 a、b、c、d 的顺序可以任意调整，MySQL 总是按照索引建立的顺序进行查询

最大区分度原则

尽量选择区分度高的列作为索引，或是将其放置在左端，区分度越高，即选出的结果行越少，则实际查询的次数就会越少

索引列不能参与计算

对于 from_unixtime(a) = ‘2014-05-29‘ 这样的查询是不能应用索引的，而应该优化成 a = from_unixtime(‘2014-05-29‘)

比如 a+1>5 只有优化为 a > 4 才会应用索引

必须以最左列开始查询

如果查询 b = 5 and c < 2014 则不会应用索引，这也正是最左前缀匹配原则

不能跳过索引中的列

对于查询 a=5 and c > 2015，由于跳过了 b 列，所以 c 不会应用索引

说明

上述限制存在于 MySQL 5.5 及以前的数据库版本中，未来的版本可能会取消某些限制

然而，可以看到，创建表时怎样选取索引的列，以及他们的排列顺序是非常重要的

哈希索引

简介

CREATE TABLE testhash (
   a varchar(50) not null,
   b varchar(50) not null,
   KEY USING HASH(a)
) ENGINE=MEMORY;

上面创建表的过程中创建了一个哈希索引

顾名思义，哈希索引的底层数据结构是用哈希表实现的，只有精确匹配索引所有列的查询才有效

索引会为每一行数据建立一个很小的哈希码，因此哈希索引占用空间小，执行效率高，但只支持等值查询，而不支持范围查询

同时，由于哈希表并不按照值的大小顺序存储，因此在 ORDER BY 操作中并不会应用该索引，也不支持仅使用索引中部分列进行查找

但是，如果是某些特定适合使用哈希索引的场合，索引所带来的性能提升将非常显著，如经典的“星型” schema，需要关联很多查找表，哈希索引就非常适合查找表的需求

哈希索引与存储引擎

哈希索引是 MEMORY 存储引擎的默认索引方式，MEMORY 引擎同时也支持 B-Tree 索引，目前，在 MySQL 中，只有 MEMORY 引擎显式支持哈希索引

InnoDB 引擎有一个特殊的功能 -- 自适应哈希索引，对于被频繁使用的索引值，InnoDB 引擎会自动在内存中创建一个哈希索引，用户只能通过配置选择是否启用这一特性，一旦启用，该过程将是完全自动，用户无法察觉的

InnoDB 创建的自适应哈希索引和真正的哈希索引并不是一回事，而是在原有的 B-Tree 索引的基础上，将检索的值变成哈希码，以降低磁盘使用

自定义哈希索引

针对不支持哈希索引的存储引擎，用户也可以采用类似 InnoDB 的思路去自定义哈希索引

典型的如将 url 变成 CRC32，可以有效节省磁盘使用，并且提高查询速度

如针对下面的查询：

SELECT id FROM url WHERE url = ‘http://www.techlog.cn/article/list/10182793‘;

这样的查询显然是很耗时的，且如果为 url 创建索引，索引也将非常庞大

优化成以下这样：

SELECT id FROM url WHERE crc32_url = CRC32(‘http://www.techlog.cn/article/list/10182793‘);

这样，我们为 crc32_url 字段创建索引，索引的大小、查询效率都会有显著的提升

但是，这样又需要维护一个新的字段 crc32_url，通过创建触发器，可以自动的添加该字段：

CREATE TABLE pseudohash (
   id   int unsigned NOT NULL auto_increment,
   url   varchar(255) NOT NULL,
   url_crc   int unsigned NOT NULL DEFAULT 0,
   PRIMARY KEY(id)
   KEY(url_crc);
);

DELIMITER //

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc = crc32(NEW.url);
END;
//

CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc = crc32(NEW.url);
END;
//

DELIMITER ;

这样，每当添加或修改 url 字段，触发器会自动更新 url_crc 字段

由于可能存在的哈希冲突，所以直接查询可能会出现多条记录，可以优化为：

SELECT id FROM url WHERE crc32_url = CRC32(‘http://www.techlog.cn/article/list/10182793‘) and url = ‘http://www.techlog.cn/article/list/10182793‘;

空间数据索引（R-Tree）

MyISAM 表支持空间索引，可以用作地理数据存储

与 B-Tree 索引不同，空间数据索引无需前缀查询，他会从所有维度索引数据，可以任意组合查询

但是必须使用 MySQL 的 GIS 相关函数，如 MBRCONTAINS() 来维护数据，然而 MySQL 对 GIS 支持并不完善，所以大部分人不会使用这个特性

PostgreSQL 的 PostGIS 对 GIS 支持很好

全文索引

全文索引查找的是文本中的关键词，而不是比较索引中的值，类似于搜索引擎

使用 MATCH AGAINST 操作进行索引，目前不支持中文

其他索引

还有很多第三方存储引擎使用其他不同类型的数据结构来存储索引，他们各自有不同的适用场景和优势

MySQL 索引及其分类

标签：

本文系统来源：http://my.oschina.net/u/658658/blog/475975

内容总结

以上是互联网集市为您收集整理的MySQL 索引及其分类全部内容，希望文章能够帮你解决MySQL 索引及其分类所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/451368.html

来源：【匿名】

【上一篇】Niagara AX连接MySQL数据库【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL 索引及其分类】教程文章相关的互联网学习教程文章

mysql索引

什么是索引排好序的快速查找数据结构。索引的用途索引用来快速寻找那些具有特定值的记录。索引会影响where后面的查找，和order by 后面的排序索引的原理就是把无序的数据变成有序的查询1.把创建了索引的列的内容进行排序2.对排序结果生成倒排表3.在倒排表内容上拼上数据地址链4.在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据数据结构是b+树聚集索引和非聚集索引都是b+树的数据结构聚集索引：把索引和数据...

MySQL 联合索引测试【代码】

搭建测试环境1：创建表CREATE TABLE tab_index (id int(5), age int(3), dte datetime); 2：插入测试数据INSERT INTO tab_index VALUES(1,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(2,‘2012-05-13‘,23); INSERT INTO tab_index VALUES(3,‘2012-05-13‘,31); INSERT INTO tab_index VALUES(4,‘2012-05-13‘,32); INSERT INTO tab_index VALUES(5,‘2012-05-13‘,33); INSERT INTO tab_index VALUES(6,‘2012-06-13‘,3...

MySQL查询优化之索引的应用详解

糟糕的SQL查询语句可对整个应用程序的运行产生严重的影响，其不仅消耗掉更多的数据库时间，且它将对其他应用组件产生影响。如同其它学科，优化查询性能很大程度上决定于开发者的直觉。幸运的是，像MySQL这样的数据库自带有一些协助工具。本文简要讨论诸多工具之三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。MySQL允许对数据库表进行索引，以此能迅速查找记录，而无需一开始就扫描整个表，由此显著地加快查询速度。...

Mysql管理表和索引

1.创建数据库:mysql> help create database;Name: ‘CREATE DATABASE‘Description:Syntax:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name[create_specification] ...create_specification:[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_nameCREATE DATABASE creates a database with the given name. To use thisstatement, you need the CREATE privilege for the database. CREATESCHEMA...

mysql 索引与优化like查询

详见：http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt1011. like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。 2. like keyword% 索引有效。 3. like %keyword% 索引失效，也无法使用反向索引。 ==================================================================== 1. 使用下面的函数来进行模糊查询，如果出现的位...

MySQL索引背后的之使用策略及优化(高性能索引策略)【图】

本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。示例数据库　　为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图(引用自MySQL官方手册)：　　图12　　MySQL官方文档中关于此数据库的页面...

MySQL 8.0 隐藏索引【图】

隐式索引最明显的一个作用类似索引回收站。例如数据库长时间运行后，会积累很多索引，做数据库优化时，想清理掉没什么用的多余的索引，但可能删除某个索引后，数据库性能下降了，发现这个索引是有用的，就要重新建立。对于较大的表来说，删除、重建索引的成本是很高的，如果在清理索引时能先放入回收站，确认没影响后再彻底删除，有影响的话就恢复回来，这样就方便多了，把索引设置为隐藏/显示就可以实现这个需求。索引的隐藏不...

MySQL之left join表查询中发生字符集转换导致表索引失效【代码】【图】

一、出现的场景研发leader突然给了1条sql说这条sql在测试环境库db-stage执行非常的慢，放到线上db-read从库上执行非常的快。而且线上库从库的表的数据远多于测试环境库的表数据。让我分析下是什么问题？？二、sql内容如下 select odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id left join db_order.t_orders o on oid.order_id=o.order_id w...

MySQL索引背后的数据结构及算法原理

摘要本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。为了避免混乱，本文将只关注于BTree索引，因为这是平常使用MySQL时主要打交道的索引，至于哈希索引和全文索引本文暂不讨论。文章主要内容分为三个部分。第一部分主要从数据结构及算法理论层面讨论M...

MYSQL索引

mysql的4种常用索引类型：唯一索引，主键索引，全文索引，以及普通索引。普通索引（INDEX）：普通索引为索引的核心部分，无限制，目的就是加快数据的访问速度，而主键索引，唯一索引，全文索引则是在普通索引延伸出来的有规则限制的索引，方便用于某些特定的业务场景。主键索引（PRIMARY）：可以理解主键（常用的那个id主键），每一张表都必须有一个主键，且不允许为空。唯一索引（UNIQUE）：内容具备唯一性，像手机号，身份证这种...

mysql测试索引在表中的作用【代码】

实验内容单表中的索引使用1．建表 create table t_book(id int,a1 varchar(200),a2 varchar(200),a3 varchar(200),a4 varchar(200),a5 varchar(200),a6 varchar(200),a7 varchar(200),a8 varchar(200),a9 varchar(200)) 2．写存储过程向表中插入较大数据//或其他方式使数据增大（1）绑定变量： PREPARE stmt1 FROM‘insert into t_book(id,a1,a2,a3,a4,a5,a6,a7,a8,a9)values(?,?,?,?,?,?,?,?,?,?)‘; （2）存储过程创建：delimite...

mysql中索引的使用【图】

索引是加速查询的主要手段，特别对于涉及多个表的查询更是如此。本节中，将介绍索引的作用、特点，以及创建和删除索引的语法。13.4.1 使用索引优化查询索引是快速定位数据的技术，首先通过一个示例来了解其含义及作用，详细的介绍请参考第14章。1．索引示例假设对于10.3节所建的表，各个表上都没有索引，数据的排列也没有规律，如表13.3所示。表13.3 没有索引的students表...

Mysql如何为表字段添加索引？？？

1.添加PRIMARY KEY（主键索引）：ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) ：ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) ：ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) ：ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `col...

MySql分区后创建索引加速单表查询和连表查询【代码】【图】

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。本文链接：https://blog.csdn.net/konkon2012/article/details/96482548为了加快查询，我们通常根据Where条件创建索引！那么分区后再创建索引，那就应该更快了！我们依据订单表和订单商品表举例，先创建表结构：CREATE TABLE `zstb_orders` ( `order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `org_id` INT(10) UNSIGNED NOT NU...

mySql---剖析InnoDB索引原理【图】

摘要：本篇为参考别人的文章（http://blog.csdn.net/voidccc/article/details/40077329）1 各种树形结构本来不打算从二叉搜索树开始，因为网上已经有太多相关文章，但是考虑到清晰的图示对理解问题有很大帮助，也为了保证文章完整性，最后还是加上了这部分。先看看几种树形结构： 1 搜索二叉树：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数据存储的基础结...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / MySQL 索引及其分类

MySQL 索引及其分类

内容导读

内容图文

概述

索引的类型

B-Tree 索引

磁盘 IO 与预读

B-Tree 的特性

多列索引的匹配规则

哈希索引

简介

哈希索引与存储引擎

自定义哈希索引

空间数据索引（R-Tree）

全文索引

其他索引

内容总结

内容备注

内容手机端

【MySQL 索引及其分类】教程文章相关的互联网学习教程文章

mysql索引

MySQL 联合索引测试【代码】

MySQL查询优化之索引的应用详解

Mysql管理表和索引

mysql 索引与优化like查询

MySQL索引背后的之使用策略及优化(高性能索引策略)【图】

MySQL 8.0 隐藏索引【图】

MySQL之left join表查询中发生字符集转换导致表索引失效【代码】【图】

MySQL索引背后的数据结构及算法原理

MYSQL索引

mysql测试索引在表中的作用【代码】

mysql中索引的使用【图】

Mysql如何为表字段添加索引？？？

MySql分区后创建索引加速单表查询和连表查询【代码】【图】

mySql---剖析InnoDB索引原理【图】

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程