首页 > 代码库 > MySQL索引详解

MySQL索引详解

2024-08-11 04:30:33   219人阅读

什么是索引

索引是存储引擎用于快速找到记录的一种数据结构，索引类似一本书的目录，我们可以快速的根据目录查找到我们想要的内容的所在页码，索引的优化应该是对查询性能优化最有效的手段了。

索引的类型

索引是在MYSQL的存储引擎层中实现的，而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持同一种类型的索引，其底层实现也可能不同。

B-Tree索引B-Tree索引是最常见的索引类型,也是创建索引时默认的类型。B-Tree是一种多叉平衡树，B-Tree 结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。一般用于数据库的索引，综合效率较高。

等值匹配 可用于

=
!=
<>
IN
NOT IN
<=>

查询语句的优化 范围匹配

可用于

>
>=
<
<=
BTEWEEN AND

等范围查询语句的优化 匹配最左前缀

name like bai%

这种语句，是可以使用name字段上建立的索引来优化查询的，但是对于

name like %bai

则没有办法使用索引了 覆盖索引

覆盖索引是指所有需要查询的字段都在索引已经存在了，那么就不需要再去查询数据了，这种查询效率很高。

select id where id >100

排序

BTREE索引还可以用于查询中的order by 操作。

哈希索引哈希索引基于哈希表实现,只有Memory引擎显示支持哈希索引，使用哈希索引可以一次定位，所以 Hash 索引的查询效率要远高于 B-Tree 索引。但是哈希索引是有很多限制的:

  • 只有精确匹配索引所有列的查询才有效，因为哈希索引是利用索引的所有列的字段值来计算哈希值的，
  • 只支持等值比较查询，不能用于范围查询。
  • 哈希索引的只包含索引字段的哈希值he和指向数据的指针，所以不能使用索引中的值来避免读取行。
  • 哈希索引的数据并不是顺序存储的，无法用于排序。

全文索引

聚集索引&非聚集索引

聚集索引聚集索引并不是一种单独的索引类型，而是一种数据存储方式，Innode的聚集索引实际上是将主键(PRIMARY kEY )与数据行存放在同一个文件的,一张表只能有一个聚集索引。技术分享InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会用一个唯一且不为空的索引列做为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键。

INNODB的普通索引(二级索引)的叶子节点中存放的是PRIMARY KEY的值,所以需要先查询普通索引(二级索引)的叶子节点找到对应的主键值，然后再根据主键值去聚集索引中查询到对应的数据。技术分享

InnoDB将主键与数据聚集在一起的方式，使得按主键顺序的插入和查询效率会很高，更新主键或者不按主键的顺序插入数据的代价会比较高，所以主键的选取很重要(使用AUTO INCREMENT字段或者程序自己生成的顺序字段要比无序的UUID好的多) 二级索引会保存主键的值，所以主键的值不要太大。

非聚集索引非聚集索引的索引与数据是存在在不同文件的，对于MyISAM引擎的一张表，会有三种文件，FRM(表结构)、MYD（数据，就是数据库中的每个行）、MYI（索引）。 MySQl使用索引查询数据时，先到MYI文件中找出数据存储的位置指针，然后再到MYD文件中读取数据。技术分享MyISAM中主键索引和其他索引在结构上没有什么不同，主键索引就是一个名为PRIMARY的唯一非空索引。

索引操作

创建在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

  • CREATE TABLE

    hell

    `

  • ALTER TABLE

    ALTER TABLE table_name ADD INDEX|KEY index_name (column1,[column2]...)ALTER TABLE table_name ADD UNIQUE [KEY|INDEX] (column1,[column2]...)ALTER TABLE table_name ADD PRIMARY KEY|INDEX (column1,[column2]...)

    `

  • CREATE INDEX

    CREATE KEY|INDEX index_name ON table_name (column_list)CREATE UNIQUE KEY|INDEX index_name ON table_name (column_list)

    ` 删除

查看 SHOW INDEX FROM

tableName

高效索引策略

参考文献

MySQL索引背后的数据结构及算法原理 MYSQL-索引 Mysql索引分析

http://www.tuicool.com/articles/zmiM3mB

 

MySQL索引详解


联系
我们