首页  思维导图  详情



 



mysql总结

2026-07-15 14:58:26   0  举报





AI智能生成

MySQL是一个强大的开源关系型数据库管理系统（RDBMS），广泛用于网站开发和数据管理，具有高性能、高可靠性与易用性。其核心内容涉及数据存储、查询优化、事务管理及安全性控制。文件类型通常包括SQL脚本文件（.sql），用于存储SQL语句，以及MySQL数据表和数据库文件。通过索引、存储过程和触发器等高级特性的使用，MySQL在修饰语如“高效”、“稳定”、“可扩展”和“灵活”下表现突出，是互联网应用和企业环境的理想选择。

后端开发

模板推荐

作者其他创作

大纲/内容

索引

二叉树

递增的列创建索引会变成一个链表所以不适合作为innodb的索引实现

Hash表

对索引的key进行一次hash计算就可以定位出数据存储的位置( 链表不太长的情况)

仅能满足"= IN”不支持范围查询

hash冲突问题

B-Tree

叶节点具有相同的深度，叶节点的指针为空

所有索引元素不重复

节点中的数据索引从左到右递增排列

不用B-Tree的原因一个数据页是16Kb ，索引和data(假设1kb)一起存储,一个数据页只能存放16个索引，所以如果存放两千万的数据树会很高(16的n次方=两千万n就是树高)

B+Tree

非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引

叶子节点包含所有索引字段(所有的key，val可以是数据行值(主键索引)或者主键Id(非主键索引))

叶子节点用指针连接，提高区间访问的性能,（比如要找大于30的元素，定位30所在叶子节点根据相邻指针向右找就行

B+Tree从左到右都是有序的，非叶子节点可以理解为冗余索引用于构建B+Tree的，一个B+树的高度是由非叶子节点存放的索引元素个数决定的

一个B+树大概可以存储多少索引  如上图如果数据类型是bigInt的索引，一个索引占用8个byte,指向下一个节点的磁盘地址占用6B,一个数据页是16kb 所以根节点可以存放16kb/(8+6)B约等于1170个索引，所以二级节点也能最多存放1170个索引。子节点假如存放数据data为1kb 因为一个数据页是16kb ，所以一个数据页可以存放16条数据。所以整个树最多存放1170乘上1170乘以16大约两千万

举例现在查找索引的key=30索引的数据，第一次将根节点加载到内存中去，利用折半查找判断出30在15和56之间,根据指针找到15对应的数据页(二级索引的最左侧整体连接的一整块)在磁盘文件上的地址，然后将这一页加载到内存中然后利用折半查找定位到30在20和49之间的位置，然后根据指向叶子节点的地址将对应数据页加载到内存中，在内容中比对找到30索引对应的磁盘地址/行数据/主键id

定义: 索引是帮助MySQL高效获取数据的排好序的数据结构

explain

有几个 select 查询就有几个id，并且id的顺序是按 select 出现的顺序增长的 id列越大执行优先级越高，id相同上往下执行，id为NULL最后执行

selct_type 简单还是复杂的查询

simple：简单查询。查询不包含子查询和union

primary：复杂查询中最外层的 select

subquery：包含在 select 中的子查询（不在 from 子句中）

derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

用这个例子来了解 primary、subquery 和 derived 类型 mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

type 

这一列表示关联类型或访问类型,查找数据行记录的大概范围。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL一般来说，得保证查询达到range级别，最好达到ref

NULL

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。 mysql> explain select min(id) from film;

const, system

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常；量比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system mysql> explain extended select * from (select * from film where id = 1) tmp;

eq_ref

primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。先遍历film_actor(驱动表)每一条去匹配film(最多返回一条)，且film.id也是film(被驱动表)的主键 mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;

ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。 1. 简单 select 查询，name是普通索引（非唯一索引） mysql> explain select * from film where name = 'film1';                                                                                           2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。说的是on 关联条件film_actor.film_id; 不是查询列内容 mysql> explain select film_id from film left join film_actor on film.id = film_actor.film_id; 2的见笔记示例

range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行

index

扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。 name字段是二级索引索引叶子节点存储着主键值这样不用回表可以直接查询*结果；表就两个字段 id name这种属于覆盖索引;为什么不是主键索引因为主键索引叶子节点包含行的值比较大  explain优化要想到B+Tree树  explain select * from film; 表总共两列name是二级索引 id 二级索引包括，所以*  二级索引中全部包含

ALL

即全表扫描，扫描你的聚簇索引(主键索引)的所有叶子节点。通常情况下这需要增加索引来进行优化了。 mysql> explain select * from actor;actor没有建立二级索引

key

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL

key_lengh

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

Extra

Using index：使用覆盖索引 覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖   explain select * from actor where name = 'a';

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

最佳实战

最左前缀法则:如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

is null,is not null 一般情况下也无法使用索引

ike以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作  解决like'%字符串%'索引不被使用的方法？ a）使用覆盖索引，查询字段必须是建立覆盖索引字段

字符串不加单引号索引失效

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

索引实战优化

索引下推

SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'  可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引

为什么范围查找Mysql没有用索引下推优化？计应该是Mysql认为范围查找过滤的结果集过大还不去全表扫描

Using filesort文件排序原理详解

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序

- 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段

索引设计原则

代码先行，索引后上  主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引

不要在小基数字段上建立索引

长字符串我们可以采用前缀索引

where与order by冲突时优先where

in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

联合索引第一个字段用范围不会走索引

分页优化

select * from employees limit 10000,10 表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

用join 让排序时返回的字段尽可能少  所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下 select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

Join查询优化

- Nested-Loop Join 嵌套循环连接算法     小表当驱动表（外层循环），大表当被驱动表（内层循环） 1.遍历驱动表每一行数据 2.拿着这行的关联字段，去被驱动表匹配 3.被驱动表关联列有索引，匹配速度极快

Block Nested-Loop Join 算法  被驱动表没有索引，没法走 NLJ，用内存块批量匹配减少磁盘读取 1.把驱动表多行数据一次性读到join buffer（连接缓冲区） 2.一次性扫描一遍完整被驱动表 3.拿 buffer 里一批数据和当前行批量比对，不用一行扫一次表

1.NLJ：被驱动表关联字段建有索引，循环查表走索引，速度快  2.BNLJ：没索引才会用，批量缓存减少全表扫描次数，性能差很多  全表扫描被驱动表，无索引，大数据量很慢

- 关联字段加索引，让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引 - 小表驱动大表，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

in和exsits优化

in  select * from A where id in (select id from B)  当B表的数据集小于A表的数据集时等价于  for(select id from B){       select * from A where A.id = B.id }

exsits  exists：当A表的数据集小于B表的数据集时  select * from A where exists (select 1 from B where B.id = A.id)   #等价于:for(select * from A){ select * from B where B.id = A.id }

count(*)查询优化

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)    //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)    //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

分支主题