MySql索引

用户35807748432
55 阅读6分钟

​

什么是索引？

它类似于书籍的目录，通过创建索引，可以快速定位和访问数据库表中的特定数据

索引存储了表中数据的某个列的值及其所在的物理位置， 使得在查询时可以直接定位到匹配的行，而不需要逐行扫描整个表。这样可以大大减少查询所需的时间和资源消耗。

所以，要记住的关键点是索引包含一个表中列的值

B树索引、哈希索引、全文索引

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

如何创建索引？

  1. 使用CREATE INDEX语句：根据不同的DBMS，语法可能会有所不同，但通常的格式是：其中，index_name是索引的名称，table_name是要创建索引的表名，col_name[length]等是要创建索引的列名。
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON  table_name (col_name[length],...) [ASC|DESC]


ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

  1. 使用数据库管理工具：大多数DBMS提供了可视化的数据库管理工具，这些工具通常提供了创建索引的图形界面操作。通过选择要创建索引的表和列，并指定索引的名称，可以方便地创建索引。

需要注意的是，创建索引可能会影响插入、更新和删除操作的性能，因为每次修改数据时都需要更新索引。因此，在创建索引时需要仔细考虑索引的列和数量，避免过度索引或不必要的索引。

索引的类型

InnoDB存储引擎索引只支持BTREE（树）类型的索引

主键，唯一约束列，外键这些都自动会生成索引

  • Primary Key（聚集索引） ：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分

  • 单列索引：单列索引即一个索引只包含单个列
  • 组合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合
index idx1(id,score_num,username)
  • Unique（唯一索引） ：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
 unique index idx1(username)
  • Key（普通索引） ：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
 index idx1(username)
  • FULLTEXT（全文索引） ：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
fulltext index idx1(username)
  • SPATIAL（空间索引） ：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL
spatial index idx1(position)

删除索引

alter table 删除

ALTER TABLE table_name DROP INDEX index_name

orop index 删除

DROP INDEX index_name ON table_name;

添加AUTO_INCREMENT约束字段的唯一索引不能被删除

主键索引和普通索引的查询有什么区别？

主键索引查询：select * from T where ID = 500 , 只需要搜索ID这个B+树

普通索引查询：select * from T where k = 5,需要先搜索k索引树，得到 ID = 500,再到 ID 索引树搜索一次，这个过程叫回表

结果：非主键索引的查询需要多扫描一棵索引树

索引维护

如果新插入的值为 400，那么需要逻辑上挪动后面的数据，空出位置。

页分裂：如果R5所在的数据页已经满了，这时候就需要申请一个新的数据页，挪动部分数据过去

页合并：当相邻两个页由于删除了数据，利用率很低，会将数据页做合并

自增主键的插入数据模式：递增插入

定义：NOT NULL PRIMARY KEY AUTO_INCREMENT

性能

每次插入新记录，都是追加操作，不会涉及到挪动其他记录，也就不会触发叶子节点的分裂

业务逻辑的字段做主键，不容易保证有序插入

存储空间

比如：身份证号（string类型）做主键

那么每个二级索引的叶子节点占用约20个字节

如果用整型做主键，则要4个字节。用长整型则8个字节

什么场景适合用业务字段做主键？

1.只有一个索引

2.该索引必须是唯一索引 （KV场景）

索引优化，避免回表

1.覆盖索引

select ID from T where k between 3 and 5

因为 ID 就在 K 索引树上，索引不需要回表

在查询中 索引 K  已经 覆盖了我们的查询需求，就叫做覆盖索引

2.最左前缀原则

如果要查的第一个字是“张”，会查找到第一个符合记录时ID3，然后向后遍历，直到不满足条件为止

where name like '张%'

建立索引如何安排索引内的字段顺序？

标准：索引的复用能力，有（a,b）联合索引后，不需要在 a 上建立索引

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

索引下推

检索表中 名字第一个字是张，而且年龄是10

select * from tuser where name like '张%' and age=10 and ismale=1;

InnoDB 在（name,age）索引内部就判断了 age 是否等于 10，对不等于 10 的直接跳过，所以只需要回表2次

 

​

avatar
文章
阅读
粉丝