mysql索引介绍

久梦歌行

已于 2023-10-21 09:42:28 修改

阅读量666

点赞数 1

文章标签： mysql 数据库

于 2019-03-31 09:29:06 首次发布

本文链接：https://blog.youkuaiyun.com/z83986976/article/details/88924331

版权

mysql索引介绍

为什么要用索引

索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。

索引为什么是（B+/B-）树

查询不仅仅是只查一条，有很多需求会返回多个：

分组：group by
排序：order by
比较：<、>

满足局部性原理：

局部性原理的逻辑是这样的：

(1)内存读写块，磁盘读写慢，而且慢很多；

(2)磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘IO，提高效率；

通常，一页数据是4K。

(3)局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO；

索引有哪些种类

普通索引（单列索引）

单列索引：单列索引是最基本的索引，它没有任何限制。

创建一个单列索引，例如：

ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');

组合索引

组合索引：组合索引是在多个字段上创建的索引。组合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

创建一个复合索引，例如

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);

唯一索引

唯一索引：唯一索引和单列索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许有空值。对于多个字段，唯一索引规定列值的组合必须唯一。

创建一个复合索引，例如：

ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');

主键索引

主键索引：主键索引是一种特殊的唯一索引，不允许有空值。此外， CREATE INDEX 不能创建主键索引，需要使用 ALTER TABLE 代替，例如：

ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name' ('column');

全文索引

在一般情况下，模糊查询都是通过 like 的方式进行查询。但是，对于海量数据，这并不是一个好办法，在 like "value%" 可以使用索引，但是对于 like "%value%" 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情,所以 like 进行模糊匹配性能很差。

这种情况下，需要考虑使用全文搜索的方式进行优化。全文搜索在 MySQL 中是一个 FULLTEXT 类型索引。 FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表。示例：

alter table app_full_text add fulltext key app_name_intro (app_name_full_text);

聚集索引与非聚集索引

聚集索引

聚集索引决定数据在物理磁盘上的物理排序，一个表只能有一个聚集索引，如果定义了主键，那么InnoDB会通过主键来聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。

聚集索引可以很大程度的提高访问速度，因为聚集索引将索引和行数据保存在了同一个B-Tree中，所以找到了索引也就相应的找到了对应的行数据，但在使用聚集索引的时候需注意避免随机的聚集索引（一般指主键值不连续，且分布范围不均匀），如使用UUID来作为聚集索引性能会很差，因为UUID值的不连续会导致增加很多的索引碎片和随机I/O，最终导致查询的性能急剧下降。