Mysql数据库调优

什么是索引索引是一种数据结构他将数据提前按照一定的规则进行排序和组织加快数据库表中数据的查找和访问速度。一句话概括能实现快速定位数据的一种存储结构其设计思想是以空间换时间索引常见种类按功能、结构、约束来分一共这几类1. 按约束性质分主键索引PRIMARY KEY唯一且非空一张表只能一个自带索引。唯一索引UNIQUE列值不能重复但允许 NULL通常只允许一个 NULL。普通索引INDEX / KEY仅加速查询无任何约束。全文索引FULLTEXT用于长文本模糊搜索如文章、内容检索。2. 按列数量分单列索引只基于一个字段创建。联合索引 / 复合索引多个字段组合成一个索引遵循最左前缀原则。3. 按数据物理存储分聚簇索引Clustered Index索引顺序 数据物理存储顺序InnoDB 的主键索引就是聚簇索引。非聚簇索引Secondary Index索引和数据分开存储查到索引后再回表找数据。4. 按使用场景覆盖索引查询所需字段全部在索引里不需要回表效率极高。前缀索引只对字符串前 N 个字符建索引节省空间。B 树和B 树1. 数据存储位置B 树所有节点内部节点 叶子都存键和数据B 树仅叶子节点存真实数据上层只做索引2. 查询稳定性B 树最好最坏差别大可能在根节点就查到B 树任何查询都要走到叶子IO 次数稳定3. 范围查询B 树范围查要反复回溯父节点很慢B 树叶子链表直接遍历范围查询极快4. 空间利用率B 树每个节点都带数据一页能存的 key 更少B 树非叶子只存 key一页能存更多 key→ 树更矮、IO 更少5. 插入删除B 树逻辑简单但节点分裂 / 合并更频繁B 树只在叶子操作结构更稳定6.总的来说B 树节点存数据适合内存结构范围查询弱。B 树只有叶子存数据带叶子链表IO 更少、范围查询更强因此是MySQL InnoDB 索引的标准结构。如果一个表没有主键索引还会创建b树吗1. 前提你用的是 InnoDBMySQL 默认InnoDB 是聚簇索引组织表必须有且只有一个聚簇索引数据本身就存在 B 树的叶子节点里。所以只要是 InnoDB 表就一定有一棵聚簇 B 树不可能没有。2. 没有主键时MySQL 会怎么做按优先级依次找有主键→ 用主键做聚簇索引无主键但有非空唯一索引→ 选第一个非空唯一索引做聚簇索引既无主键也无非空唯一索引 →MySQL 自动生成一个 6 字节的隐藏列ROW_ID列名DB_ROW_ID单调递增用它作为聚簇索引构建 B 树Hash 索引是什么底层不是树是哈希表key-value 结构对索引字段做hash 运算得到哈希值定位数据行特点等值查询极快查询几乎 O (1)MySQL 里哪里能用 Hash 索引只有两个地方Memory 引擎默认就是 Hash 索引InnoDB 自适应哈希索引内部自动优化你不能手动建InnoDB 普通表不能手动创建 Hash 索引你写CREATE INDEX ... USING HASH也没用会自动转成 B 树。特性Hash 索引B 树索引查询方式只支持等值查询 支持 、、、between、like 前缀排序完全不支持排序天然有序可范围、可排序模糊查询不支持 like支持左前缀 like abc%多字段联合索引必须全匹配才生效最左前缀即可部分生效速度等值极快稳定、综合性能强冲突可能哈希冲突无冲突结构稳定聚集索引和非聚集索引特性聚集索引 (Clustered Index)非聚集索引 (Non-Clustered Index)数据存储数据行直接存放在索引的叶子节点叶子节点只存主键值 书签指针物理顺序物理顺序与索引顺序完全一致物理顺序与索引顺序无关数量一张表只能有一个一张表可以有多个别称聚簇索引二级索引、辅助索引深入理解聚集索引在 MySQL 的InnoDB引擎中聚集索引就是主键索引。它是表的 “本体”表中的数据行就是按照聚集索引的顺序主键排序物理存储在磁盘上的。查询最快因为找到索引就直接找到了数据不需要再进行一次 “回表” 操作。选择原则首选主键 (PRIMARY KEY)。如果没有主键InnoDB 会选择第一个非空唯一索引 (UNIQUE NOT NULL)。如果都没有InnoDB 会自动创建一个隐藏的6 字节 ROW_ID作为聚集索引。深入理解非聚集索引非聚集索引二级索引是在聚集索引的基础上建立的用于加速非主键字段的查询。它是 “目录”叶子节点里存的不是完整数据而是主键值书签。需要回表通过非聚集索引查询到数据后必须拿着这个主键值再去聚集索引主键索引里查一遍完整的数据。这个过程叫“回表”。用途广泛可以为任意频繁查询的字段如姓名、手机号建立索引一张表可以建多个。关键操作覆盖索引既然非聚集索引需要 “回表”有没有办法避免有覆盖索引。原理如果我们查询的字段都包含在非聚集索引的叶子节点里也就是只查索引列就不需要回表了。例子select id, name from user where name 张三;如果name上有索引且查询的id(主键) 和name都在这个索引里就能直接拿到结果效率极高。索引下推ICPIndex Condition Pushdown一句话把本来要回表才能判断的条件提前在索引上就过滤掉减少回表次数。1. 它解决了什么问题在没有索引下推之前存储引擎根据索引查到一批主键 ID全部回表查出完整数据再由 MySQL 服务器层过滤剩下的条件结果大量无效回表慢得要死开启索引下推后存储引擎在索引里先过滤掉不满足条件的行只把符合条件的少量 ID 拿去回表直接返回结果核心过滤动作从「服务器层」下推到「存储引擎层」2. 用个最简单的例子秒懂假设有表user(id PK, name, age) index idx_name_age (name, age) -- 联合索引查询select * from user where name like 张% and age 20;没有 ICP索引只能用name like 张%找到所有姓张的 ID全部回表再在内存里判断age20回表次数 姓张的总人数开启 ICP索引里同时包含 name 和 age引擎在索引上直接过滤姓张且 age20只把符合条件的少量 ID 回表回表次数大幅减少3. 适用场景联合索引查询条件中能用到索引前缀但后面字段无法用于索引查找只能用于过滤InnoDB / MyISAM 都支持4. 不适用场景查询使用了覆盖索引本来就不用回表ICP 没用条件里用了函数、表达式子查询、存储过程里的部分逻辑没有 ICP索引只能用name like 张%找到所有姓张的 ID全部回表再在内存里判断age20回表次数 姓张的总人数开启 ICP索引里同时包含 name 和 age引擎在索引上直接过滤姓张且 age20只把符合条件的少量 ID 回表回表次数大幅减少5. 开关命令-- 查看是否开启 show variables like optimizer_switch; -- 开启 set optimizer_switchindex_condition_pushdownon;MySQL 5.6默认开启。单列索引和联合索引单列索引只给一个字段建索引INDEX idx_name(name)联合索引复合索引给多个字段一起建索引INDEX idx_name_age(name, age)核心规则最左匹配原则联合索引必须遵循最左匹配否则索引失效。比如索引(a, b, c)能用到索引的情况where a ?where a ? and b ?where a ? and b ? and c ?where a ? and c ? 只用到 a用不到索引的情况where b ?where c ?where b ? and c ?一句话必须从最左边开始连续匹配断了就不行。单列索引 vs 联合索引 怎么选1. 经常多个字段一起查询where name ? and age ?建联合索引(name, age)✅ 最好建两个单列索引(name)、(age)❌ 只能用到一个2. 只有单个字段经常查询where phone ?建单列索引(phone)✅ 足够没必要建联合索引3. 范围查询 between like联合索引里范围列后面的字段用不上索引where name 张 and age 20 and sex 1索引(name, age, sex)name 精确匹配 → 用到age 范围 → 用到sex → 用不到经典面试题Q已有联合索引 (a,b,c)还需要单独建索引 (a) 吗→不需要因为最左匹配已经包含 a。Q已有索引 (a,b)再建 (b,a) 有用吗→有用完全是两个不同索引查询顺序不同效果不同。极简总结单列索引单个字段查询快联合索引多字段组合查询快遵循最左匹配建立联合索引 同时拥有多个前缀索引建立原则等值放前范围放后