MySQL索引学习笔记

  • Z时代
  • 2024-01-10
  • 分类：综合

编程

索引是帮助MySQL高效获取数据的排好序的数据结构

一.存储引擎

这里只讨论使用最多的两种引擎【MyISAM】和【InnoDB】

1. MyISAM 引擎（非聚集）

上图是是使用myisam引擎的文件，可以看出：MyISAM索引文件和数据是分离的（非聚集）。

当一个查询带有索引，得先通过MYI文件（B+TREE）读取到该条数据的磁盘文件指针，因此再在MYD文件中获取到该条数据。如果查询条件不是索引，则直接去MYD文件去找，做全表扫描，效率低下。

2. InnoDB引擎（聚集）

表数据文件本身就是按B+Tree组织的一个索引结构文件如上图
此为聚集索引-叶节点包含了完整的数据记录


1、为什么InnoDB表必须有主键，并且推荐使用整型的自增主键?（整型比字符串大小判断效率更高，自增会不容易造成B+Tree分裂）
2、为什么非主键索引结构叶子节点存储的是主键值? (一致性和节省存储空间)

简单总结一下这两个引擎：

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

二. 索引结构(方法、算法)

在mysql中常用两种索引结构(算法)BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样

1.HASH通过hash算法得到磁盘文件指针，然后找到数据。

查询单条快，范围查询慢，相当于全表扫描

2.B+Tree

B+Tree中的非叶子结点不存储数据，只存储键值；

B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；

B+Tree的每个非叶子节点由n个键值key和n个指针point组成（顺序访问指针，提高区间访问的性能）；
 

三. 索引类型

MySQL目前主要有以下几种索引类型：

  • 普通索引

  • 唯一索引

  • 主键索引

  • 组合索引

  • 全文索引

 

普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

（1）直接创建索引

CREATE INDEX index_name ON table(column(length))  

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,     `title` char(255) CHARACTER NOT NULL ,     `content` text CHARACTER NULL ,     `time` int(10) NULL DEFAULT NULL ,     PRIMARY KEY (`id`),     INDEX index_name (title(length)) )

唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,     `title` char(255) CHARACTER NOT NULL ,     `content` text CHARACTER NULL ,     `time` int(10) NULL DEFAULT NULL ,     UNIQUE indexName (title(length)) );

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,     `title` char(255) NOT NULL ,     PRIMARY KEY (`id`) );

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

例如联合索引有三个索引字段（A,B,C）

查询条件：

（A，，）---会使用索引

（A，B，）---会使用索引

（A，B，C）---会使用索引

（，B，C）---不会使用索引

（，，C）---不会使用索引

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,     `title` char(255) CHARACTER NOT NULL ,     `content` text CHARACTER NULL ,     `time` int(10) NULL DEFAULT NULL ,     PRIMARY KEY (`id`),     FULLTEXT (content) );

 

暂时到这里，接下来学习索引优化。。

 

 

以上是 MySQL索引学习笔记 的全部内容， 来源链接： utcz.com/z/514423.html

回到顶部