MySQL 系统学习 第四阶段：MySQL 高级 第一节：索引（Index）

一、为什么需要索引先看一个问题。假设用户表CREATE TABLE user( id BIGINT, username VARCHAR(50), age INT );数据id username age 1 Tom 20 2 Jack 25 3 Lucy 18 ... 10000000 Mike 30现在执行SELECT * FROM user WHERE usernameTom;没有索引时MySQL 怎么查只能第一条 ↓ 第二条 ↓ 第三条 ↓ ... ↓ 1000万条这种方式叫全表扫描Full Table Scan时间复杂度O(n)数据越多越慢。有索引时例如给 username 创建索引CREATE INDEX idx_username ON user(username);MySQL 会建立类似索引结构 Tom | 位置 | 数据行查询WHERE usernameTom可以快速定位。时间复杂度O(log n)二、什么是索引定义索引是数据库为了提高查询效率而创建的一种数据结构。简单理解索引 数据目录。现实例子没有目录的书找 MySQL章节 翻第一页 翻第二页 ... 翻500页有目录目录 MySQL ↓ 第300页快速定位。数据库索引也是类似思想。三、MySQL 常见索引类型1. 普通索引普通查询语法CREATE INDEX idx_name ON user(name);作用提高查询速度。2. 唯一索引保证数据唯一。例如手机号CREATE UNIQUE INDEX idx_phone ON user(phone);保证13800000001不能重复。3. 主键索引创建主键PRIMARY KEY(id)MySQL 自动创建索引。例如id BIGINT PRIMARY KEY4. 联合索引 ⭐⭐⭐⭐⭐多个字段组成索引。例如CREATE INDEX idx_name_age ON user(name, age);表示同时根据name age建立索引。四、索引的数据结构MySQL 常用BTree这是重点。为什么不用数组链表二叉树而选择BTree1. 数组查询快O(1)但是插入删除慢。2. 链表查询慢O(n)3. 二叉树如果数据1 \ 2 \ 3 \ 4会退化成链表。4. BTree结构50 / \ 20 80 / \ / \ 10 30 60 90 数据存储在叶子节点特点高度低查询快适合磁盘范围查询优秀所以 MySQL InnoDB 使用 BTree。五、索引创建和删除创建CREATE INDEX idx_user_name ON user(username);查看索引SHOW INDEX FROM user;删除索引DROP INDEX idx_user_name ON user;六、什么时候应该创建索引适合1. 经常查询字段例如WHERE usernameTom2. 经常排序例如ORDER BY create_time DESC3. 经常关联例如JOIN user ON order.user_iduser.id4. 外键字段例如order.user_id七、索引是不是越多越好不是。这是面试高频问题。原因① 占用空间索引也是数据。例如10GB数据 多个索引 可能增加几GB② 降低写入速度INSERT原来写数据有索引写数据 更新索引UPDATE修改字段可能需要更新索引。DELETE删除数据同时删除索引记录。所以索引提高查询速度但是降低写入性能。八、本节总结内容说明索引提高查询速度的数据结构无索引全表扫描有索引快速定位MySQL索引结构BTree主键自动创建索引唯一索引保证唯一联合索引多字段索引索引缺点占空间、降低写性能 本节核心一句话索引就是数据库的数据目录通过空间换时间让查询从扫描大量数据变成快速定位数据。