MYSQL : 索引

放风筝的鸭脚木
27 阅读8分钟

索引

索引相关SQL

索引创建 ： create ， alter ， 建表时创建

# create 
    # 普通索引
CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);# INDEX 关键字可以 改成 KEY
    # 唯一索引的创建 
CREATE UNIQUE INDEX indexName ON tableName (columnName(length));
    # 主键索引
​
​
# alter
     # 普通索引
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
    # 唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX indexName(columnName);
    # 主键索引
ALTER TABLE tableName ADD PRIMARY KEY indexName(columnName);
​
# 建表时创建
CREATE TABLE tableName( 
 columnName1 INT(8) NOT NULL, 
 columnName2 ....,
 .....,
 INDEX [indexName] (columnName(length)) # 普通索引
 UNIQUE INDEX [indexName] (columnName(length)) # 唯一索引
 PRIMARY KEY [indexName] (columnName(length)) # 主键索引
);
​
# 主键和唯一索引还可以这样定义
CREATE TABLE users (
    id INT PRIMARY KEY,                    -- 主键约束
    username VARCHAR(50) UNIQUE,           -- 列级唯一约束
    email VARCHAR(100),                    -- 普通列
);
​

查询索引 ： show

SHOW INDEX FROM tableName;
​

image-20251123095247113.png

删除索引 drop

DROP INDEX indexName ON tableName;

强制SELECT使用索引 FORCE INDEX(index_name)

SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;

索引类型

按照数据结构划分

  • B+Tree 类型 ： 最常用，innodb默认
  • Hash 类型
  • R-Tree 类型
  • T-Tree 类型

字段数量

  • 单列索引：唯一索引，主键索引，普通单列索引（通过key，index创建）
  • 多列索引：通常称为组合索引

功能划分

  • 唯一索引
  • 主键索引
  • 普通索引
  • 全文索引：用于文章检索
  • 空间索引

存储方式划分

  • 聚簇索引：索引的叶子节点直接就是存储的具体数据，索引数据和表数据是存放在同一个文件（.ibd）内。查询时，先获取所有索引，当沿着树结构查询到叶子节点就得到了对应的数据。

    查看索引时，key_name为PRIMARY说明就是聚簇索引

    聚簇索引：的索引值最好是自增，避免页分裂 页分裂是指当一个数据页（Page）无法容纳新插入的数据时，数据库系统将该页拆分成两个页的过程。

    InnoDB 只支持主键索引作为聚簇索引，一般默认主键索引作为聚簇索引，没有主键选择首个非空的唯一索引作为主键，都没有默认创建一个隐藏的字段作为主键索引，一个表只有一个聚簇索引。

  • 非聚簇索引：索引节点存放的是聚簇索引字段的值，使用非聚簇索引，在需要其他字段的情况下，会通过聚簇索引查询具体数据。

如何选择索引?

索引的好处

  • 数据越大,查询效果越明显
  • 分组和排序,速度更快
  • 连表查询速度快
  • 范围查询速度快(B+)
  • 速度快,数据库整体吞吐量更高

索引的缺点

  • 额外的磁盘空间
  • 影响增删改的性能,尤其是增加

选择何种索引?

聚簇索引

聚簇索引也就是主键索引

特点

  • Inodb中和数据是存放在一起的
  • 索引的叶子节点直接指向数据位置

聚簇索引建议使用自增id作为主键,非自增id容易引起页分裂降低性能

也容易导致B+树的频繁调整

联合索引

最左匹配原则: 在B+树的进行搜索时,会按照索引字段先后顺序进行匹配搜索

前缀索引

优点:只选n个字符作为索引,节省空间

缺点:无法通过前缀索引完成分组和排序

全文索引 : 规模比较大的项目用ES

基于分词实现:索引文件量很大

修改字段时需要通过自定义的存储过程更新索引

对中文支持不够友好

唯一索引

查询比普通索引效率高:因为找到需要的就停止扫描

插入需要检查完整性

哈希索引

速度比B+树更快:一次hash就能找到数据位置

缺点:执行排序,分组时效率低

永远不会进行排序分组的字段适合hash索引,查询性能会比B+高不少

回表机制

针对非聚簇索引,需要查询索引字段以外的其他字段时,会通过找到的聚簇索引(主键索引)再次进行搜索

  • 为什么不能使用*? 网络io角度回答,回表问题回答

索引覆盖机制

查找的字段就在索引中,不回表了

索引下推机制

通过索引获取到数据,会经过索引的数据,再次进行筛选,减少回表次数

where user_name like "竹%" and user_sex="男";
#user_name和user_sex都是索引,user_name在前,所以不会user_sex用不到索引,会通过user_name的索引找到所有数据符合要求的数据,经过索引下推后,在回表时会对user_sex进行过滤,不符合要求的过滤掉,减少回表的记录数量

需要开启,5.6之后默认开启

MMR机制

通过普通索引查询到聚簇索引的id不会拿到一个id就去回表查询,而是放到缓存中,所有id都准备好,再一次性查询结果,从而减少io次数

5.6之后默认开启

索引跳跃式扫描

8.0后才有这个机制,当查询条件出现不符合最左前缀匹配规则时,根据情况补充上对应字段的数据

对那些字段建立索引?

什么时候考虑索引?

  • 频繁用于查询到字段
  • 频繁分组,排序,范围查询的字段
  • 连表字段必须建立索引,最好是主键索引,性能更好
  • 索引字段区分度要住够高,索引才有效果,如对性别建立索引没有意义
  • 索引字段不应该过长,适当选用前缀索引
  • 联合索引:按照优先级排列,避免在使用时过多造成索引失效问题
  • 不分组,排序,范围查询的字段:尽量用哈希索引,当然唯一与否也是一个考量是否使用Hash的维度
  • 充分利用索引覆盖的:普通索引建议考虑是否能使用联合索引

需要注意的地方

  • 频繁增删改查的不适合建立索引
  • 重复度过高的字段不适合索引
  • 索引数量不是越多越好:3-5个最佳,多了增删改的维护成本会很大
  • 联合索引在建立时一定要考虑好最左匹配的问题:也就是优先级
  • 数据量小时,维护索引的成本更大
  • 无序的字段不建议建立索引,尤其是主键索引

索引失效

  • 没有遵循最左匹配原则
  • 优化器判断走索引性能会更低
  • 使用OR会导致索引失效:or的有一个条件不是索引
  • 对范围查询取反,不等查询
  • 模糊查询like以%开头:一定会失效 非要用%开头,非要用数据库如何解决呢?对字段进行取反冗余存储,%开头就用取反的字段
  • 对索引字段进行运算 : 普通的字符串作为索引时,匹配条件写成和数值比较 where str=123,数据库会优化成 where number(str)=123,本质上是对运算索引所以失效了 为什么不优化成where str=string(123)? 因为"00123"在逻辑上和123值是相等的,str=string(123)无法处理这个逻辑上的问题

索引原理

目的:减少io次数

为什么使用b+树?

不用顺序二叉树:极端(全表)情况查询性能差

不用平衡二叉树:调整频繁

不用红黑树:调整不那么频繁,但是一般来说存储红黑树节点采用数组的结构存储,红黑树的层数和io次数成正比,尽管有调整功能,但是数据量少的情况下树的高度还是有些高了

不用b树:b树单节点能存放很多数据,高度比红黑树低很多,但是数据库查询会有范围查询,b树不适合做范围查询

为什么用b+树:高度合适,叶子节点通过链表连接方便做范围查询

千万级别的表B+树层数多高?

innoDB一次IO的单页大小为16KB=>树的单节点最大16KB

树的节点指针大小:6B

以int类型为索引字段:4B

单页(单节点):能存放16KB/10B(约1600)个字段

最佳情况下:高度为3的节点叶子节点能有 1600 *1600*单页存放数据数量

  • 假设行数据为1.6k,则1600*1600*10=2560,0000

大部分情况下,B+树的层数不会超过5,也就是说io次数不会超过5

前缀索引为什么性能好? 单页能存放的索引数量更大

不同存储引擎索引实现方式

MyISAM:没有聚簇索引,查完索引必然有一次额外的io开销,插入数据时只要调整索引数据即可

  • zz_myisam_index.frm：该⽂件中存储表的结构信息。
  • zz_myisam_index.MYD：该⽂件中存储表的⾏数据。
  • zz_myisam_index.MYI：该⽂件中存储表的索引数据。

InnoDB:支持聚簇索引,插入数据时,可能会额外触发聚簇索引的调整

  • zz_innodb_index.frm：该⽂件中存储表的结构信息。
  • zz_innodb_index.ibd：该⽂件中存储表的⾏数据和索引数据。
avatar
文章
阅读
粉丝