尧图网页设计 
一、索引到底解决什么问题先看没有索引时数据库怎么查数据。假设有一张users表存了 100 万条用户记录。执行SELECT * FROM users WHERE username zhangsan;数据库只能从第 1 行开始逐行扫描直到找到username zhangsan的那一行。这叫全表扫描需要扫描 100 万次。如果username上建了索引数据库就像查字典一样先通过拼音/部首找到字在哪一页直接翻到那一页。索引的本质用额外的存储空间换取查询速度。二、索引的数据结构B 树MySQLInnoDB的索引默认用B 树结构。理解 B 树就理解了索引为什么快。1. B 树长什么样特点所有数据都存在叶子节点非叶子节点只存“导航信息”叶子节点之间用链表连接方便范围查询树的高度通常只有3-4 层即使存上亿条数据2. 为什么 B 树快假设有 100 万条数据B 树高度为 3第 1 层根1 次磁盘 IO 第 2 层中间1 次磁盘 IO 第 3 层叶子1 次磁盘 IO 总共3 次磁盘 IO 就能找到目标相比全表扫描的 100 万次 IO快了 30 万倍。三、聚簇索引 vs 非聚簇索引1. 聚簇索引Clustered Index数据和索引存在一起索引的叶子节点就是完整的数据行。InnoDB 中主键就是聚簇索引。也就是说表数据本身就是按主键顺序组织的一张表只能有一个聚簇索引-- id 是主键它就是聚簇索引 CREATE TABLE users ( id INT PRIMARY KEY, -- 聚簇索引 username VARCHAR(50), age INT );2. 非聚簇索引Secondary Index / 辅助索引索引和数据分开存储索引的叶子节点存的是主键值不是完整数据。-- 在 username 上建索引这是非聚簇索引 CREATE INDEX idx_username ON users(username);3. 回表当用非聚簇索引查询时SELECT * FROM users WHERE username zhangsan;执行过程是在idx_username索引中找到zhangsan拿到它的主键 id用这个 id 去聚簇索引中查完整数据行第 2 步就叫回表。回表会增加 IO是索引优化中要重点关注的问题。四、覆盖索引避免回表如果索引中已经包含了查询需要的所有字段就不用回表了。-- 建一个联合索引 CREATE INDEX idx_username_age ON users(username, age); -- 查询只需要 username 和 age索引中都有 SELECT username, age FROM users WHERE username zhangsan; -- 不需要回表因为索引已经覆盖了查询所需的所有字段这叫覆盖索引是优化查询的常用手段。五、联合索引与最左前缀联合索引是在多个字段上建的索引CREATE INDEX idx_a_b_c ON table(a, b, c);最左前缀原则联合索引(a, b, c)能支持的查询查询条件能否用索引WHERE a 1能WHERE a 1 AND b 2能WHERE a 1 AND b 2 AND c 3能WHERE b 2不能跳过了 aWHERE c 3不能跳过了 a 和 bWHERE a 1 AND c 3只能用 a 的部分规则查询条件必须从索引的最左列开始且不能跳过中间的列。六、索引的类型类型说明示例主键索引聚簇索引唯一且非空PRIMARY KEY (id)唯一索引值不能重复可以有 NULLUNIQUE INDEX (email)普通索引最基础的索引无约束INDEX (name)联合索引多个字段组合的索引INDEX (a, b, c)全文索引用于全文搜索FULLTEXT INDEX (content)前缀索引只索引字符串的前几个字符INDEX (name(10))七、索引的代价索引不是越多越好它有代价代价说明占用存储空间每个索引都要额外存储降低写入速度INSERT/UPDATE/DELETE 时需要维护索引增加优化器负担索引太多优化器选择困难原则只为高频查询的字段建索引不为低频字段建。适合建索引的场景主键必须建外键常用来做 JOIN建议建高频查询条件WHERE 中经常出现的字段排序字段ORDER BY 的字段分组字段GROUP BY 的字段不适合建索引的场景区分度低的字段如性别只有男/女、状态只有几个值很少查询的字段频繁更新的字段大文本字段如 TEXT可以用前缀索引八、查看索引使用情况-- 查看表的索引 SHOW INDEX FROM users; -- 用 EXPLAIN 分析查询 EXPLAIN SELECT * FROM users WHERE username zhangsan;EXPLAIN的关键字段字段说明type访问类型ref、range、index、ALL等ALL表示全表扫描key实际使用的索引rows预估扫描的行数Extra额外信息Using index表示用了覆盖索引 数据库中的索引
一、索引到底解决什么问题先看没有索引时数据库怎么查数据。假设有一张users表存了 100 万条用户记录。执行SELECT * FROM users WHERE username zhangsan;数据库只能从第 1 行开始逐行扫描直到找到username zhangsan的那一行。这叫全表扫描需要扫描 100 万次。如果username上建了索引数据库就像查字典一样先通过拼音/部首找到字在哪一页直接翻到那一页。索引的本质用额外的存储空间换取查询速度。二、索引的数据结构B 树MySQLInnoDB的索引默认用B 树结构。理解 B 树就理解了索引为什么快。1. B 树长什么样特点所有数据都存在叶子节点非叶子节点只存“导航信息”叶子节点之间用链表连接方便范围查询树的高度通常只有3-4 层即使存上亿条数据2. 为什么 B 树快假设有 100 万条数据B 树高度为 3第 1 层根1 次磁盘 IO 第 2 层中间1 次磁盘 IO 第 3 层叶子1 次磁盘 IO 总共3 次磁盘 IO 就能找到目标相比全表扫描的 100 万次 IO快了 30 万倍。三、聚簇索引 vs 非聚簇索引1. 聚簇索引Clustered Index数据和索引存在一起索引的叶子节点就是完整的数据行。InnoDB 中主键就是聚簇索引。也就是说表数据本身就是按主键顺序组织的一张表只能有一个聚簇索引-- id 是主键它就是聚簇索引 CREATE TABLE users ( id INT PRIMARY KEY, -- 聚簇索引 username VARCHAR(50), age INT );2. 非聚簇索引Secondary Index / 辅助索引索引和数据分开存储索引的叶子节点存的是主键值不是完整数据。-- 在 username 上建索引这是非聚簇索引 CREATE INDEX idx_username ON users(username);3. 回表当用非聚簇索引查询时SELECT * FROM users WHERE username zhangsan;执行过程是在idx_username索引中找到zhangsan拿到它的主键 id用这个 id 去聚簇索引中查完整数据行第 2 步就叫回表。回表会增加 IO是索引优化中要重点关注的问题。四、覆盖索引避免回表如果索引中已经包含了查询需要的所有字段就不用回表了。-- 建一个联合索引 CREATE INDEX idx_username_age ON users(username, age); -- 查询只需要 username 和 age索引中都有 SELECT username, age FROM users WHERE username zhangsan; -- 不需要回表因为索引已经覆盖了查询所需的所有字段这叫覆盖索引是优化查询的常用手段。五、联合索引与最左前缀联合索引是在多个字段上建的索引CREATE INDEX idx_a_b_c ON table(a, b, c);最左前缀原则联合索引(a, b, c)能支持的查询查询条件能否用索引WHERE a 1能WHERE a 1 AND b 2能WHERE a 1 AND b 2 AND c 3能WHERE b 2不能跳过了 aWHERE c 3不能跳过了 a 和 bWHERE a 1 AND c 3只能用 a 的部分规则查询条件必须从索引的最左列开始且不能跳过中间的列。六、索引的类型类型说明示例主键索引聚簇索引唯一且非空PRIMARY KEY (id)唯一索引值不能重复可以有 NULLUNIQUE INDEX (email)普通索引最基础的索引无约束INDEX (name)联合索引多个字段组合的索引INDEX (a, b, c)全文索引用于全文搜索FULLTEXT INDEX (content)前缀索引只索引字符串的前几个字符INDEX (name(10))七、索引的代价索引不是越多越好它有代价代价说明占用存储空间每个索引都要额外存储降低写入速度INSERT/UPDATE/DELETE 时需要维护索引增加优化器负担索引太多优化器选择困难原则只为高频查询的字段建索引不为低频字段建。适合建索引的场景主键必须建外键常用来做 JOIN建议建高频查询条件WHERE 中经常出现的字段排序字段ORDER BY 的字段分组字段GROUP BY 的字段不适合建索引的场景区分度低的字段如性别只有男/女、状态只有几个值很少查询的字段频繁更新的字段大文本字段如 TEXT可以用前缀索引八、查看索引使用情况-- 查看表的索引 SHOW INDEX FROM users; -- 用 EXPLAIN 分析查询 EXPLAIN SELECT * FROM users WHERE username zhangsan;EXPLAIN的关键字段字段说明type访问类型ref、range、index、ALL等ALL表示全表扫描key实际使用的索引rows预估扫描的行数Extra额外信息Using index表示用了覆盖索引