彰化县网站建设_网站建设公司_字体设计_seo优化

存储引擎

• 索引是在存储引擎实现的，不同的存储引擎索引的实现方式不同
• 存储引擎是基于表的，同一个数据库中的表可以使用不同的存储引擎
• 默认存储引擎是InnoDb

类型

• show engines命令可以查看mysql支持的存储引擎
• ![[Pasted image 20250323141205.png]]

各自特点

• InnoDb引擎
  • 支持事务
  • 行级锁，支持高并发
  • 支持外键，保证数据的一致性和完整性
  • 每张表都对应一个表名.idb的文件，这个文件存储该表的表结构，数据，以及索引
• MyISAM引擎
  • 不支持事务，不支持外键
  • 支持表锁
  • 访问速度快
• memory引擎
  • 数据存放在内存，这些表只能作为临时表或缓存使用
  • 支持hash索引
  • 表锁
  • 只有一个sdi文件，记录表结构数据

选择

• 需要事务，并发条件下数据一致性要求高 选择InnoDb
• MyIsam 业务以读操作和插入操作为主，如存储日志
• Memory 主要是来做缓存

索引

优缺点

• 优点
  • 提高查询效率
  • 提高排序效率
• 缺点
  • 索引需要额外占用存储空间
  • 降低表的更新插入效率，在更新原表的时候还需要更新索引的节点

索引结构

• B+树 最常见的结构
• HASH索引 速度最快，只能精确匹配，不能范围查询
• R树（空间索引）主要用来存储空间地理数据类型
• Full-text索引（全文索引） 搜索文本

使用B+树的优点

• 普通二叉树
  • 一个节点最多有两个子节点
  • 如果存储的数据是有序数据，则会形成一条链表
  • 数据过多，树的深度过大
• 红黑树（自平衡二叉搜索树）存在树的深度过大问题
• B树 一个m阶（m指一个节点中最多包含的子节点数），在排序，范围查询时不方便
• B+树 在B树的基础上，所有的数据全部存储在叶子节点，叶子节点通过指针形成一个有序链表 方便排序以及范围查询

性能分析

• 使用命令查询数据库增删改查操作的数量
• 打开mysql的慢sql日志
• 使用profile来查看某条sql详细的执行时间
• explain（最常用）来查看某条sql的执行计划，用来检查是否使用索引 主要关心的字段就是 type，key，ref，extra
  • type 此次访问的类型
    • system 表中只有一条数据（系统表）
    • const 使用主键或者唯一索引 一次就能获取结果
    • ref 非唯一索引扫描
    • range 索引范围扫描
    • index 全索引扫描
    • ALL 全表扫描
  • possible key 可能会用到的索引
  • key 实际使用的索引
    • NULL 表示没有使用索引
  • ref 显示索引哪一列被使用
  • Extra 额外的重要信息
    • using index 索引覆盖 能从索引中获取所有数据不需要回表查询
    • using temporary 使用临时表
    • Using where：在存储引擎检索后，服务层再过滤
    • Using filesort：使用外部排序（性能较差）
    • Using join buffer：使用连接缓存
    • Impossible WHERE：WHERE 条件永远为 false
    • Select tables optimized away：通过索引优化，可能不必访问表

索引分类

• 主键索引，唯一索引，普通索引，前缀索引，联合索引
• 聚集索引，二级索引
  • 聚集索引中包含所有的数据
  • 二级索引中只包含建立索引的字段，数据则存储的为这条数据的主键
  • 二级索引需要拿到除了索引建立字段以外的字段时需要拿着存储主键去聚集索引中再次查询，这个过程称为回表查询

索引失效常见情况（面试可能会问）

• 联合索引中索引失效
  • 最左匹配法则
    • 联合索引按照建立索引时的顺序从左到右进行匹配，如果跳过某个字段，则后续的字段也不会使用索引 例如 index(a,b,c) 查询时 where a=1 and c=2 跳过了b那么只会用到a=1这个索引
    • 范围查询时 <> 后面的条件则不会使用索引 例如 where a=1 and b>1 and c=1 这种情况下只会用到 a=1 and b>1 而不会用到c=1 在业务允许的情况下尽量使用 >= <=
• 在索引字段使用函数，计算 例如count，substr，+，-等 例如

explain select * from account where id+1 = 2;

• 类型转换时不会使用索引 字符串类型字段没有使用双引号包围或者单引号包围 会进行隐式类型转换 （这个咋感觉新版的mysql修复了呢）
• or 的两侧条件都有索引时才会走索引，如果只有一侧的字段有索引则不会使用索引
• 模糊匹配 like %出现在头部则不会走索引，%在中间会使用索引只不过只用到%前面那部分进行匹配
• mysql认为全表扫描比使用索引快时，会舍弃索引

sql提示

• select * use index（索引名）from ..... 建议使用某个索引
• ignore index 建议忽略某个索引
• force index 强制使用某个索引
• use与ignore 只是建议使用，数据库会进行评估，如果他觉得建议不合理他不会采用

覆盖查询（避免回表查询）

• 一般要尽量避免回表查询的情况，在频繁查询的查询字段建立联合索引，从联合索引中获取所有信息

前缀索引

• 一般用于字段是大文本，或者长字符串，使用前缀索引来节省磁盘空间以及提高查询效率

索引一些注意事项

• 索引不是越多越好，索引会影响插入，修改删除的效率
• 在数据量较大时，新建索引会非常慢，占用数据库资源，最好在表设计时考虑好后续的情况。一般稳健的操作建新表，加索引，导数据，废除旧表
• 注意索引失效的情况
• 尽量避免回表查询

SQL优化

INSERT

• 尽量使用批量插入 insert into 表名 values(....),(.......)
• 手动提交事务
• 按照主键顺序依次插入，表数据都是根据主键顺序组织存放的，按照主键顺序效率比乱序要高
  • 页分裂 insert
  • 页合并 delete
• 超级大的数据量时可以使用load指令来直接加载数据文件

主键优化

• 主键长度尽量小
• 插入数据时尽量顺序插入
• 尽量不要使用UUID，身份证号没有顺序的值来作为主键
• 尽量不要修改主键

order By优化

• 需要排序的字段 尽量是有索引的字段 若有频繁的order by时可以考虑在相关字段新建索引，多字段排序时也适用最左前缀法则
• 尽量覆盖索引，避免回表扫描

group by优化

• 尽量使用索引
• 也适用最左前缀法则

limit 优化

• 数据量很大时，limit越往后耗时越长，limit需要先查出前offset条数据，然后丢弃，然后再取出后面的len长度的数据
• 使用子查询来指定limit的开始位置

count 优化

• 没啥好的优化方法，最多使用一张额外的表来维护经常count表的大小
• 效率 count(*) = count(1) > count(主键) >= count(非主键字段)
• count（*）mysql数据库做了优化，不取值读出记录直接累加
• count（1）也不取值，而是用1来代替这条记录 然后累加
• count（主键） 会先取出主键然后再累加
• count（非主键） 先取值，可能再判断是不是null。然后再累加

update的注意事项

• 行锁是根据索引加的锁，不是针对记录加的锁，所以更新操作时where过滤条件必须使用索引，而且这个索引不能失效，否则行锁就会变为表锁影响并发效率。

视图/触发器/存储过程

视图

• 视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，而是来源与创建视图的基表的数据。
• 视图相当于一个子查询

语法

• 创建/更新视图 create 【or replace】view 视图名称 as select 语句
• 删除视图 drop view if exists 视图名称
• 查看视图创建语句 show create view 视图名称

插入数据

• 只有当视图中的数据和表中的数据是一一对应的关系时才能够更新
• 使用函数，group分组，distinct去重等操作时等不能对视图进行增删改操作

作用

• 将一些常见的select，复杂的语句固定成一个视图，减少操作，比如多表查询，复杂查询
• 有一定的安全性，例如user表的password字段不能展示给开发人员，可以个user表新建一个视图

存储过程

特点

• 封装，复用
• 接受参数，并且返回数据
• 减少网络交互，提高效率

语法

• 创建

create procedure 存储过程名称(参数列表)
begin-- SQL语句;select * from table_a where id =10;
end;

• 调用

CALL 存储过程名称(参数);

• 查看

show create procedure 存储过程名称;
-- 或者直接查看系统表
select * FROM information_schema.ROUTINES where ROUTINE_SCHEMA = '数据库名称';

• 删除

drop procedure if exists 存储过程名称;

变量

• 系统变量
  • 全局变量
  • 回话变量

  -- 查询默认session级别的系统变量
  

show VARIABLES;
-- 查询系统变量
show session VARIABLES;
show GLOBAL VARIABLES;
show GLOBAL VARIABLES like 'auto%';
-- 查询某个具体的系统变量
SELECT @@global.autocommit;
-- 修改系统变量
set SESSION autocommit = 1;
select @@session.autocommit;
-- 全局系统变量 修改之后重启mysql会自动变为原先的默认值
```

用户自定义变量

用户根据需要自己定义的变量，使用时直接使用@变量名使用就可以。作用阈为当前连接

• 赋值
  • set @var_name = expr,@var_name2=expr2……
  • set @var_name := expr,@var_name2=expr2……
  • select @var_name := expr,……
  • select 字段名 INTO @var_name from table where ……
• 使用
  • select @变量名

set @my_age := 10,@my_name='zhangsan';
select @my_age,@my_name;
-- select INTO 赋值变量时最好一条结果，因为多条会取第一个
select phone_number,name INTO @phone,@name from account limit 1;
select @phone,@name;

局部变量

使用declare关键字声明，作用范围就是在其声明的begin……end块中。

• 声明
  • DECLARE 变量名 变量类型 【DEFAULT 默认值】
• 赋值
  • set 变量名 := 值
  • select 字段 into 变量名 from 表；

CREATE PROCEDURE p2()
BEGIN
DECLARE age int DEFAULT 0;
set age := 10;
SELECT count(*) INTO age from account;
select age;
end;call p2();

if条件语句

if 条件1 then
…… sql块
elseif 条件2 then
……
else
……
end if;

CREATE PROCEDURE P2()
BEGIN
DECLARE number INT DEFAULT 0;
DECLARE message VARCHAR(20);
SELECT count(*) INTO number FROM account;IF number >= 3 THENset message := '有三个以上';ELSEIF number >= 2 and count < 3 THENset message := '有两个以上';ELSEset message := '只有一个呢';end if;
SELECT message;
END;call p2();

case when语句

• 有两种方式
• 第一种
  case
  when 条件1 then
  sql语句。。。
  when 条件2 then
  sql语句。。。
  else
  sql语句。。。
  end case；
• 第二种
  case 变量
  when 值1 then
  sql语句。。。
  when 值2 then
  sql语句。。。
  else
  sql语句。。。
  end case；

CREATE PROCEDURE p7(IN age INT,OUT message VARCHAR(30))
BEGINCASEWHEN age > 50 THENset message := '老毕登';WHEN age > 30 and age <= 50 THENset message := '中壁灯';ELSEset message := '小壁灯';	END CASE;ENDCALL p7(10,@message);
select @message;

参数

• IN 入参
  • IN 参数名 参数类型
• OUT 出参
  • OUT 参数名 参数类型
• INOUT 出入参
  • INOUT 参数名 参数类型

CREATE PROCEDURE p3(IN age INT,OUT message VARCHAR(30))
BEGINIF age >= 50 THENSET message := '老毕登';ELSEIF age<50 AND age >= 30 THENSET message := '中壁灯';ELSESET message:= '小壁灯';end if;
END;CALL p3(40,@message);
select @message;

循环

• while do end while;
  • 基本上和编程语言中的while差不多

CREATE PROCEDURE p5(IN n INT)
BEGINDECLARE total int DEFAULT 0;WHILE n > 0 DOSET total := total + n;SET n := n - 1;END WHILE;SELECT total;
END;

• repeat until end repeat
  • 直到满足某个条件才会停止循环

	-- 计算0-n的累加
CREATE PROCEDURE p4(IN n INT)
BEGINDECLARE total INT DEFAULT 0;REPEATset total := total + n;set n := n - 1;UNTIL n<=0 END REPEAT;select total;
END;CALL p4(100);

• loop
  • loop 单纯的就是一个死循环，需要配合跳转指令进行使用
  • leave 相当于break 跳出循环
  • ITERATE 相当于continue 跳过本次循环

CREATE PROCEDURE p6(IN n INT)
BEGINDECLARE total INT DEFAULT 0;sum: LOOPset total := total + n;set n := n -1;IF n <= 0 THENLEAVE sum; END IF; END LOOP sum;select total;
END;CALL p6(100);

游标 + 异常处理

• 游标才是存储过程的精髓
• 游标能够存储一系列查询结果，并结合循环进行遍历
• 游标的使用
  • 定义游标
    • DECLARE 游标名称 CURSOR FOR 查询语句
  • 定义异常 handler
    • declare handler_action handler for 触发条件 后置处理语句（如关闭游标等）
    • handler_action
      • continue 继续执行
      • exit 终止执行
    • 触发条件
      • sql语句执行后状态码 如 02000
      • SQLWARNING 所有01开头的状态码
      • NOT FOUND 所有02开头的状态码
      • SQLEXCEPTION 没有被上面两种捕获的所有状态码
  • 开启游标
    • open 游标名称
  • 获取游标中的数据
    • fetch 游标名称 into 变量
  • 数据处理
  • 关闭游标
    • close 游标名称

CREATE PROCEDURE p8()
BEGINDECLARE account_name VARCHAR(64);DECLARE account_gender INT DEFAULT 0;DECLARE accounts CURSOR FOR select name,gender from account;DECLARE EXIT HANDLER FOR NOT FOUND CLOSE accounts; DECLARE EXIT HANDLER FOR SQLEXCEPTION CLOSE accounts;OPEN accounts;WHILE true DOFETCH accounts into account_name,account_gender;INSERT into account_test(name,sex) value(account_name,account_gender);
END WHILE;CLOSE accounts;
ENDcall p8();

存储过程的优势在哪呢？

• 我不太明白为什么使用存储过程呢？明明使用编程语言能够更好的实现功能
• 可能在非常复杂的业务中存储过程才会有优势

触发器

• mysq只支持行级触发器 触发器类似于spring的intepector 可以在执行插入/更新/删除语句之前或者之后执行一些操作。
• 使用NEW和OLD来代表修改前和修改之后的数据

语法

• CREATE TRIGGER 触发器名称 BEFORE/AFTER INSERT/UPDATE/DELETE ON 表名 FOR EACH ROW
  BEGIN
  sql语句。。。。。；
  END；

CREATE TRIGGER account_insert AFTER INSERT ON account FOR EACH ROW
BEGININSERT INTO account_test (name,sex) VALUES(NEW.name,NEW.gender);END;show TRIGGERS

查询

• show triggers;

锁

全局锁

• 将数据库中的所有表全部锁定，能够查询，但是更新删除新增，以及更改表结构等操作全部被阻塞
• 一般用不到，可能会用的情况是数据库备份前加全局锁来保障数据的一致性

表锁

• 锁整张表，并发冲突高

表锁

• 表共享读锁 （读锁） 加锁客户端与其他客户端都只允许查询，不允许写入操作
• 表独占写锁 （写锁） 加锁客户端可以读写，其他客户端不允许读写操作

元数据锁

• 是系统自动控制的，无需显示使用，在访问一张表的时候系统自动加锁
• 元数据锁主要是读取写入某张表数据时，不能够修改这张表的表结构
• 对一张表进行增删改查操作时自动加MDL读锁，更改表结构时加MDL写锁

意向锁

• 意向锁主要解决行锁与表锁的冲突，在添加表锁时应该检查表中是否存在行锁，表锁是否与行锁冲突，意向锁使得表锁不用检查每行数据是否加锁，减少锁检查
• 某条数据添加行锁时，会自动添加相应的意向锁，当其他客户端添加表锁时会检查这张表是否存在意向锁，如果存在 与将要添加表锁兼容会继续，不兼容会被阻塞。
• 意向共享锁（IS）与表共享读锁兼容，与表互斥写锁互斥
• 意向排他锁（IX）与表共享读锁，互斥写锁都互斥
• 意向锁之间不会相互互斥

行级锁

• 每次加锁操作，锁住的是记录的行，发生并发冲图的概率最小，并发度最高。
• 行锁是通过对于索引上的索引项加锁来实现的，却不是对某条记录加锁

行锁

• 锁定单行记录，防止其他事务对此进行update和delete
• 共享锁 允许其他事务获取共享锁，阻止其他事务获取排他锁
• 排他锁 不允许其他事务获取共享锁以及排他锁
• insert/update/delete 自动加排他锁
• select（正常） 不加任何锁
• 如果where后面的查询条件没有经过索引，行锁会升级为表锁

间隙锁

• 锁定索引记录的间隙（不包含该记录）
• 唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务的间隙锁不会影响其他事务继续添加间隙锁

临键锁

• 行锁和间隙锁的组合，同时锁住间隙以及数据

InnoDb引擎

逻辑存储结构

• 表空间 最终生成idb文件
• 段
• 区 1M
• 页 16K
• 行

架构

• 缓冲区
• 后台线程
• 磁盘

事务原理

• 原子性，一致性，持久性 依靠redolog与undoLog实现
• 隔离性 依靠锁机制与MVCC实现

redolog作用

• 保证事务的持久性
• mysql将缓冲池中的脏页（被修改的页）刷新到磁盘发生错误时按照redolog记录进行恢复
• 在内存缓冲池中和内存中都存在redolog，每次事务提交时都会把缓冲区中的redolog刷新到磁盘

undolog作用

• 回滚保证事务的一致性 逻辑日志，例如事务执行一条插入insert语句时，undolog就会记录一条对应的delete语句，当事务发生错误时就可以根据undolog中的语句将数据库恢复到事务之前。
• MVCC
• insert语句产生的undolog只会用于数据的回滚，update与delete语句产生的undolog不仅用于回滚还用于MVCC的快照读

MVCC（面试可能会问）

• 多版本并发控制，维护一个数据的多个版本，使得读写操作没有冲突，快照读为mysql实现MVCC提供了一个非阻塞的读功能
• 实现依赖于记录中三个隐藏字段，undolog，readReview
• 主要是实现了一个非阻塞的快照读，原理还是蛮简单的。确实还是蛮简单的

实现原理

• 隐藏字段
  • 事务id 最近一次修改这条记录的事务id 自增
  • 回滚指针 指向这条记录的上一个版本 用于配合undolog
  • 隐藏主键 如果表没有指定主键会自动生成隐藏主键，如果指定主键则不会生成
  • 事务id与回滚指针形成一条版本链表，然后使用readReview来决定此次select读哪个版本
  • ![[Pasted image 20250727153443.png]]
• readView
  • 在读已提交的隔离级别 每次快照读都会产生一个readView
  • 在可重复读隔离级别 只有事务第一次读会产生一个readView，事务之后的读都会服用这个readView不会产生新的。

Mysql管理