Mysql索引介绍及常见索引的区别

小鱼儿 2024-02-19 17:41 93阅读 0赞

##  ##

**Mysql索引概念：**  
说说Mysql索引，看到一个很少比如：索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

**Mysql索引主要有两种结构：B+Tree索引和Hash索引.**

Hash索引

MySQL中，只有Memory（Memory表只存在内存中，断电会消失，适用于临时表）存储引擎显示支持Hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B+Tree索引。hsah索引把数据的索引以hash形式组织起来，因此当查找某一条记录的时候,速度非常快。当时因为是hash结构，每个键只对应一个值，而且是散列的方式分布。所以他并不支持范围查找和排序等功能。

B+树索引

B+tree是mysql使用最频繁的一个索引数据结构，是Inodb和Myisam存储引擎模式的索引类型。相对Hash索引，B+树在查找单条记录的速度比不上Hash索引，但是因为更适合排序等操作，所以他更受用户的欢迎。毕竟不可能只对数据库进行单条记录的操作。

带顺序访问指针的B+Tree

B+Tree所有索引数据都在叶子结点上，并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针。  
这样做是为了提高区间查询效率，例如查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

大大减少磁盘I/O读取

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

为了达到这个目的，在实际实现B- Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

**B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。**一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

1、选择索引的数据类型

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。  
(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。  
(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。  
  
2、选择主键类型  
选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。  
(1) 整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO\_INCREMENT。

(2) 字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

**Mysql常见索引有：**主键索引、唯一索引、普通索引、全文索引、组合索引

PRIMARY KEY（主键索引） ALTER TABLE \`table\_name\` ADD PRIMARY KEY ( \`col\` )

UNIQUE(唯一索引) ALTER TABLE \`table\_name\` ADD UNIQUE (\`col\`)

INDEX(普通索引) ALTER TABLE \`table\_name\` ADD INDEX index\_name (\`col\`)

FULLTEXT(全文索引) ALTER TABLE \`table\_name\` ADD FULLTEXT ( \`col\` )  
组合索引 ALTER TABLE \`table\_name\` ADD INDEX index\_name (\`col1\`, \`col2\`, \`col3\` )

**Mysql各种索引区别：**  
普通索引：最基本的索引，没有任何限制  
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。  
主键索引：它 是一种特殊的唯一索引，不允许有空值。  
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。  
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将**最常用（频率）**作限制条件的列放在**最左边**，依次递减。

组合索引最左字段用in是可以用到索引的，最好explain一下select。

原文链接：[https://www.cnblogs.com/shijianchuzhenzhi/p/6383117.html][https_www.cnblogs.com_shijianchuzhenzhi_p_6383117.html]

[https_www.cnblogs.com_shijianchuzhenzhi_p_6383117.html]: https://www.cnblogs.com/shijianchuzhenzhi/p/6383117.html