一、基础知识1、select 语句的查询流程是怎样的客户端发送这个语句到mysql服务器mysql服务器的连接器开始处理这个需求并和客户端建立连接解析器对sql语句进行解析检查语句有没有语法错误引用的数据库表都存在权限正确优化器找到最高效的执行计划索引执行器调用存储引擎InnoDB支持事务MyISAM不支持事务的api来进行数据读写客户端收到查询结果完成查询请求sql的执行顺序select - from - where - group by - having - order by - limit2、常用命令# 创建数据库 create database database_name; # 删除数据库 drop database database_name; # 选择数据库 use database_name; # 创建表 create table table_name ( column1 datatype, column2 datatype, ... ); # 删除表 drop table table_name; # 显示所有表 show tables; # 查看表结构 describe table_name; # 修改表添加列 alter table table_name add column_name datatype; # 插入数据 insert into table_name (column1, column2, ...) values (value1, value2, ...); # 查询数据 selet column_name from table_name where condition; # 更新数据 update table_name set column1 value1, column2 value2 where condition; # 删除数据 delete from table_name where conditon; # 创建索引 create index index_name on table_name(column_name); # 添加主键约束 alter table_name add primary key (column_name); # 添加外键约束 alter table table_name add constraint fk_name foreign key (column_name) references parent_table (parent_column_name); # 创建用户 create user usernamehost identified by password; # 授予权限 grant all privileges on database_name.table_name to usernamehost; # 删除权限 revoke all privileges on database_name.table_name from usernamehost; # 删除用户 drop userusernamehost; # 事务 # 开始事务 start transaction; # 提交事务 commit; # 回滚事务 rollback;3、存储引擎Innodb 支持事务行级锁高并发写入性能更高外键MyISAM不支持事务和外键支持表级锁查询速度快只适合只读的静态报表4、日志-错误日志-慢查询日志记录超时的 sql 语句设置 long query time-一般查询日志记录 mysql 服务器的连接信息以及 sql 语句-二进制日志bin log记录所有修改数据库状态的 sql 语句insert, update, delete主从复制用到的就是二进制日志两个 Innodb 存储引擎的日志文件-重做日志redo log记录 Innodb 表的每个写操作主要用于崩溃修复物理日志。当事务进行写操作时Innodb 会首先写入 redo log 并不会立刻修改数据文件这种写入方式被称为 write ahead logging先写日志后续 Innodb 将这些更改异步更新到数据文件中从而这种方式能够预防系统崩溃导致的数据未写入确保数据的持久性。-回滚日志undo log记录数据被修改前的值用于事务的回滚通过回滚日志可以实现 MVCC多版本并发控制bin log 和 redo log 的区别- bin log 记录所有与数据库相关的日志记录包括Innodb和MyISAM等存储引擎的日志而redo log 只记录 Innodb 存储引擎的日志- bin log 记录关于一个事务的具体操作语句为逻辑日志redo log 记录的是每页内容具体更改的情况物理日志- 写入时间不同bin log 在事务提交前提交在事务进行时不断有 redo log 写入- 写入方式不同bin log 为循环写入和删除redo log 为追加写入不覆盖已有文件redo log 什么时候刷入磁盘- log buffer 空间不足- 事务提交时- 后台线程输入通过后台线程设置过一段时间刷新 log buffer 到磁盘中- 正常关闭服务器- 触发 checkpoint 规则循环覆盖写入5、索引性能优化mysql 的数据结构b 树索引为什么会加快查询索引相当于给数据加目录避免全表扫描索引的分类按功能- 主键索引唯一 非空- 唯一索引唯一- 普通索引- 全文索引只用于文本数据索引的分类按数据结构- b 树索引索引对应数值存在二叉树中每次查询从根节点开始遍历叶子节点查询效率为 O(logN)- 哈希索引只适合 和 in 查询不适合范围查询查询效率为 O(1)索引的分类按存储位置- 聚簇索引索引的叶子节点保存一行的所有列的信息- 非聚簇索引叶子节点只包含一个主键值通过非聚簇索引先找到主键再通过主键找到聚簇索引得到对应记录行内容这整个过程被称为回表选择什么列作为索引列- 经常作为查询条件排序条件分组条件的列where 子句order by 子句group by 子句什么列最好不作为索引列- 频繁更新的列- 列中的唯一值较少的列性别要避免过多的索引- 因为每个索引会占用额外的磁盘空间同时维护索引也需要成本前缀索引能减少索引的大小什么情况下索引会失效范围查询 函数- 使用函数表达式的列无法添加索引- 使用 或 not 操作符因为这两个操作会扫描全表导致索引失效- 使用 or 操作符Innodb 选择 b 树的原因b 树 和 b 树 的区别- b 树所有值存在叶子节点并且叶子节点通过指针连接形成一个有序的链表。因此有更高的查询效率- b 树每个节点根节点中间节点都存数据而 b 树非叶子节点不存储数据比 b 树分叉更多导致树的高度较低降低查询过程中对磁盘IO的占用- 同时查询效率更加稳定查询速度快总结b 树形状 “矮胖”且适合范围查询聚簇索引和非聚簇索引的区别- 每个表只有一个聚簇索引Innodb 中逐渐就是聚簇索引- 聚簇索引中表中的行按照索引顺序存储- 非聚簇索引中索引和数据分开存储什么是回表- 使用非聚簇索引查找数据时数据库先找索引位置再根据索引位置定位数据行最左前缀匹配原则联合索引- 在使用联合索引时查询条件从索引的最左列开始并不跳过中间的列什么是覆盖索引- 如果查询的所有字段都在索引中无需回表查询6、事务事务的四大特性-原子性事务中所有操作要么全部提交成功要么全部失败通过undo log保证-一致性事务确保数据库状态从一个一致性状态变为另一个一致性状态通过其他三个特性来确保-隔离性多个并发事务相互隔离通过MVCC保证-持久性一旦事务提交修改将永久保留在数据库中。即使系统崩溃数据也不会丢失通过redo log保证隔离级别- 读未提交事务可读取未被其他事务提交的数据会出现脏读、不可重复读、幻读的问题- 读已提交事务可读取已经被其他事务提交的数据可避免脏读但不可重复读和幻读仍然存在- 可重复读确保同一事物读取相同记录的结果为一致的即使其他事务对这条记录进行修改可避免脏读和不可重复读大程度减少幻读- 串行化最高的隔离级别强制事务串行执行但会导致超时和锁竞争读已提交可重复读可以通过 MVCC 中的ReadView 来实现- 读已提交在每次读取数据前都生成一个readview保证每次读取操作为最新的数据- 可重复读只在第一次读操作时生成一个readview后续都使用这个readview从而保证一致串行化的实现- 事务在读操作时必须先加表级共享锁直到事务提交后才释放- 事务在写操作时必须先加表级排他锁直到事务结束才释放并发的三大问题- 脏读事务A,B并发执行事务A读到B的未提交的数据- 不可重复读一个事物范围内两个相同查询读取同一条记录却返回了不同的数据- 幻读事务A查询结果集并发事务B往这个结果集中进行插入和删除数据并提交事务A再次查询相同结果集却得到了不同的数据结果MVCC多版本并发控制- 在支持 MVCC 的数据库中当多个用户同时访问数据库时每个用户都可以看到再某一个时间点之前的数据库快照。从而保证多个用户之间不会相互干扰同时能无阻塞的执行查询和修改操作- 实现读写操作并行进行ReadView读视图- 主要用来处理可重复读和读已提交- 在事务刚开始执行时创建ReadView该ReadView会包含以下信息1、已开始但是未提交的事务ID列表2、所有活跃事务的最小事务ID3、活跃事务的最大ID14、创建该 ReadView 的事务ID7、锁锁的种类- 表锁- 行锁- 页锁- 共享锁 读锁- 排他锁 写锁- 乐观锁通过数据表中使用版本号或时间戳来实现。每次读取记录时同时获取版本号或时间戳更新时检查这两个是否发生变化- 悲观锁适合锁冲突常见的情况。直接用表锁、行锁来锁定被访问的数据如select for update 语句加排他锁Innodb 行锁的实现- 记录锁直接锁定某行数据。如当使用唯一性索引进行查询时会将查到的记录锁定- 间隙锁锁定两个记录之间的间隙。如使用范围查询时如果没有命中任何记录此时就会将对应的间隙区间锁定为一个左开右开的区间- 临键锁 间隙锁左开右闭区间 又记住记录行也记住间隙。如查到一条记录临键锁记录锁没查到记录则变为间隙锁意向锁- 意向锁为表级锁目的就是去判断表里面有没有行锁- 事务A锁住了某一行事务B想要锁这整张表。如果没有意向锁B需要遍历表中每行检查是否已经加了行锁。而有了意向锁后他会在A锁住行之前先给整张表添加一个表级的意向锁从而直观地告诉B排查死锁的步骤- 查看死锁的日志 show engine innodb status- 找出死锁 sql- 分析 sql- 分析死锁日志- 分析死锁结果8、sql 优化定位慢 sql慢查询日志- 找到对应慢 sql 后使用explain查看sql语句避免不必要的列- 避免 select *- 分页优化两种方法1、延迟关联通过先检索索引再根据索引id关联行内容2、书签通过记住上次查询返回的最后一行的值下次查询直接从这个值开始索引优化- 覆盖索引避免回表- 避免使用 ! 或 or 操作符- 避免对列上使用函数- 联合索引join 优化- 优化子查询- join 时用小表来连大表- 适当增加冗余字段- 避免join太多表排序优化- 设计索引时考虑排序的需求Union 优化- 用 Union all 代替 UnionUnion all 能去重学会看 explainselect 语句中添加 explain 关键词重要列名核心作用 面试高频考点 避坑指南id查询的执行顺序id 越大越先执行id 相同则从上到下执行。若 id 为 NULL代表是UNION合并后的结果集最后执行。select_type查询的复杂类型①SIMPLE简单查询无子查询/UNION②PRIMARY最外层主查询③SUBQUERY子查询④DERIVED派生表即FROM子句里的子查询⑤UNIONUNION后面的查询type⭐访问性能最重要性能从高到低排序systemconsteq_refrefrangeindexALL优化目标至少要达到range级别理想是ref或const。看到ALL全表扫描必须优化。possible_keys可能用到的候选索引有值不代表一定用只是 MySQL 的备选名单。key⭐实际选用的索引如果possible_keys有值但key为NULL说明索引失效如用了LIKE %xx、隐式类型转换、函数操作。key_len⭐索引使用的字节长度例如(name, age)索引key_len显示只用了name的长度说明age没走索引可能被范围查询截断了。公式varchar(n)≈ 3n2 字节int≈ 4 字节null额外 1 字节。rows预估扫描的行数越小越好。这是优化器估算的值虽然不精确但突然从百级跳到百万级说明 SQL 有问题。Extra⭐⭐⭐额外信息✅Using index——覆盖索引数据直接从索引取不回表性能优秀加分项。✅Using index condition——索引下推ICP5.6 后的优化减少回表次数。⚠️Using where—— 存储引擎层返回数据后Server 层再过滤通常意味着索引利用不充分。❌Using filesort——文件排序内存/磁盘代表ORDER BY没走索引必须优化。❌Using temporary——临时表常见于GROUP BY或DISTINCT不走索引性能极差必须重构 SQL。9、主从复制主服务器上所有修改数据的语句insert, update, delete被记录到二进制日志中主服务器上的一个线程二进制日志转储线程负责读取二进制日志的内容并发送给从服务器。从服务器接收到该数据将这些改动写入自己的中继日志relay log中从服务器上有一个sql线程会读取中继日志并将这些修改异步应用到从服务器中10、分库分表分表大表拆分为小表减轻单表的压力- 垂直分字段水平分数据分表策略- 范围路由根据某个字段的值的范围进行分表- 哈希路由通过对分片进行哈希计算取模来确认数据存储的表。优点数据均匀分布- 配置路由通过新建一个配置表来决定数据划分分库把数据分散到多台机器上- 垂直分库按业务模块划分- 水平分表按一定策略将一个表中的数据拆分到多个库中分库分表的代价- 分布式ID的问题不能依赖自增主键了- 跨库查询二、真题练习找出连续三天及以上活跃的用户提示row_number()代码select uid from ( select uid, dt, row_number() over (partition by uid order by dt) rn, date_sub(dt,row_number() over (partition by uid order by dt)) ds from useractive ) tmp group by uid,ds having count(*) 3;其中临时表长这样