MySQL索引

MySQL索引

索引类型（逻辑分类）

• 主键索引
• 唯一索引
• 普通索引
• 复合索引
  • 也叫联合索引。基于多个列创建的索引
  • 遵循最左前缀原则
• 前缀索引
  • 索引字符串过长时，可以只对前N个字符建立索引

InnoDB的聚簇索引和非聚簇索引

• 聚簇索引
  • 叶子结点包含了完整的数据行
  • 在InnoDB中，主键索引即为聚簇索引
• 非聚簇索引
  • 叶子结点不包含完整的数据行，而是该行的主键值

索引概念与优化策略

• 索引覆盖
  • 一个查询中所有的列都包含在索引中。此时无需回表查询。
• 索引下推
  • MySQL5.6+，在where子句中包含索引相关的列的判断条件。在索引引擎层判断，减少回表次数
• 最左前缀原则

索引失效场景

• 违反最左前缀原则（复合索引）
• 在索引列使用前缀或表达式。如where year(create_time)=2024
• 使用like以通配符开头。 如where name like '%三'
• 对索引列进行隐式类型转换。
• 使用or连接条件。并且 or前后的列并非都有索引

查看和分析索引使用情况

使用explain命令。示例

构造测试数据

CREATE DATABASE if not exists test;

USE test;

CREATE TABLE if not exists testuser(
id bigint PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
email VARCHAR(255),
creator_id bigint,
create_time TIMESTAMP DEFAULT NOW(),
INDEX idx_name_email(NAME,email),
INDEX idx_creator(creator_id)
);

INSERT INTO testuser(NAME, email, creator_id) VALUES 
('张三','zhangsan@xxx.com', null), 
('李四','lisi@xxx.com', 1);

分析索引

explain select * from testuser where name like '张%';
explain select * from testuser where creator_id in (1,3,5);
EXPLAIN SELECT * from testuser where id in (1,3,5);
explain select name from testuser where name like '张%';

关键字段

• type
  • 从好到坏： system > const > eq_ref > ref > range > index > all
  • 一般来说至少到range，最好到ref
  • 在上面的测试结果中，类型都为range
• key
  • 使用到的索引名称
• rows
  • 预估需要扫描的行数
• extra
  • 重要信息。
  • 例子explain select name from testuser where name like '张%';
    • 结果为using index（索引覆盖）
  • 例子explain select * from testuser where name like '张%';
    • 结果为using index condition（索引下推）

tag

• MySQL
• 索引