您的位置: 首页  >  文章  >  MySQL 索引相关

MySQL 索引相关

分类: 文章 • 2025-04-09 15:27:16

1、MySQL存储引擎MyIsAm与InnoDB的区别

存储引擎Storage engine：MySqL中的数据、索引以及其他对象时如何存储的，是一套文件系统的实现。

常见的存储引擎有：

  • InnoDB引擎：提供了对数据库ACID事务的支持，并且提供了行级锁和外键的约束。他的设计目标是处理大数据容量的数据库系统。
  • MyIsAm引擎：不提供事务的支持，也不支持行锁和外键。
  • MeMory引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

2、MyIsAM与InnoDB的区别

  MyIsAM Innodb
存储结构 每张表都被存放在三个文件内:frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件 所有的表都保存在同一个数据文件中（可能是多个文件，或者独立的表空间文件）Innodb表的大小只受限与操作系统的文件大小，一般为2GB
存储空间 MyISAM可被压缩，存储空间较小 InNodb表需要更多的内存和存储，它会在主内存中建立起专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复 由于MyIsAM的数据是以文件的形式存储，所以在跨平台的数据转移中存储方便，在备份和恢复时可对单独表进行操作 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式 数据和索引是分别存储的，数据.MYD，索引.MYI 数据和索引是集中存储的，.ibd
记录存储顺序 按记录插入顺序保存 按主键大小有序插入
外键、事务 不支持 不支持
锁支持 表级锁定 行级锁定、表级锁定，锁定力度小并发能力高
SELECT MyISAM更优  
INSERT、UPDATE、DELETE   InnoDB更优
select count(*) myisam更快，因为myisam内部维护了一个计数器，可以直接调取  
索引的实现方式 B+树索引，myisam 是堆表 B+树索引，Innodb 是索引组织表
哈希索引 不支持 支持
全文索引 支持 不支持
     

3、MyIsAM索引和InnoDB索引

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

MyISAM索引实现

MyIsAM索引和InnoDb索引都是使用B+ 树作为索引结构，然而在MyISAM引擎中B+树的叶子节点的data域存放的是数据记录的地址。如图是MyISAM的原理图：

MySQL 索引相关

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

MySQL 索引相关 同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB索引实现

 虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同

第一个重大区别是InnoDB的数据文件本身就是索引文件.从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引.

MySQL 索引相关

图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

MySQL 索引相关

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

4、索引有几种类型？

主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引：全文索引：是目前搜索引擎使用的一种关键技术。

  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

 5、创建索引的原则：

  1. 最优前缀匹配原则，组合索引非常重要的原则，mysql会mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  2. 较频繁作为查询条件的字段才去创建索引，更新频繁字段不适合创建索引
  3. 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
  4. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
  5. 定义有外键的数据列一定要建立索引。
  6. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
  7. 对于定义为text、image和bit的数据类型的列不要建立索引。

6、B树和B+树的区别

  • 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

  • B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

MySQL 索引相关

 https://mp.weixin.qq.com/s/_Gd-lBfJnhwczNQA4IJyGg     https://blog.codinglabs.org/articles/theory-of-mysql-index.html

相关推荐