7、mysql的事务,视图,索引优化,引擎

最新推荐文章于 2024-04-26 13:29:49 发布

天阳在线€

最新推荐文章于 2024-04-26 13:29:49 发布

阅读量223

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/yangbo_CSDN/article/details/120655775

版权

Mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

事务

事务的ACID属性

原子性(Atomicity): 一个事务是不可分割的,其中的操作要么全部执行,要么全部不执行（由DBMS的事务管理子系统来实现）
一致性(Consistency): 事务执行前后数据库必须保持一致性 (由DBMS的完整性子系统执行测试任务）
隔离性(Isolation):并发执行的事务互相隔离,互不干扰（由DBMS的并发控制子系统实现）
持久性(Durability): 事务一旦提交,它对于数据库的影响就是永久性的,后续数据库的故障或其他操作等都不会影响本次事务的结果。（由DBMS的恢复管理子系统实现的）

事务的分类

隐性事务: 在mysql默认自动提交开启的情况下,一条insert,update,delete等DML语句都是隐性事务,没有明显的开启和结束标志
显性事务: 关闭mysql自动提交(set autocommit = 0)的情况下书写的DML语句,有明显开启和结束标记的事务

delete 和 truncate的区别

delete有where字句,属于dml, truncate属于ddl
delete支持事务(dml),当然支持回滚, truncate(ddl)不支持
delete每次删除一行,并在日志中记录,数据量时效率低,安全性高, truncate一次性删除所有表数据,效率较高
delete不能重制高水位线,truncate可以重制(造成的影响例如使用delete语句删除所有表记录自增字段还是从删除前的最大数开始, 使用truncate则从1开始)
- 高水位线: 数据库被使用过的最大空间

事务并发问题

脏读: 读取了其他事务未提交的对数据库的更改
不可重复读: 重复读取某字段, 因另一并发事务提交的此字段的更新导致前后数值不相同
幻读: 重复读取同一个表, 因另一并发事务的插入或删除等操作导致前后读取的数据条数不相同

事务的隔离级别

读未提交: 允许事务读取其他事务还未提交的修改, 最低级别,以上问题都会出现
读已提交: 只允许事务读取已经被其他事务提交的修改, 可以避免脏读
可重复读: (MySql默认隔离级别) 在事务访问某个字段时,禁止其他事务对此字段的更新,(加锁)保证同一事务多次读取此字段的数据一致, 可以避免脏读, 不可重复读
串行化: 在一个事务执行期间, 禁止其他并发事务的增、删、改操作, 可以避免脏读, 不可重复读, 幻读等所有并发问题, 效率低下

视图

虚拟表,能和普通表一样使用,MySql从5.0.1版本开始提供

应用场景

查询结果复用
查询结果使用的sql语句较为复杂,可以用于简化复杂sql的操作
保护数据,提高安全性

语法

创建:create view 视图名 as (查询语句)
创建或者替代: create or replace view 视图名 as (查询语句)
修改: alter view 视图名 as 查询语句
删除: drop view 视图名, 视图名….
查看视图结构: desc 视图名
查看视图DDL等属性: show create view 视图名

视图的更新

简单视图可以允许进行和表一样的增删改操作, 这些操作同时也会更改原表的数据, 非常危险
以下视图不能进行dml其中的某一些操作:
- 包涵以下关键字: 分组函数, group by having, distinct, union, union all
- 常量视图
- select中包涵子查询
- join
- from一个不能更新的视图
- where 子句的子查询引用了from字句的表
视图一般都会设置为只读以保护数据安全, 仅供查询使用

视图和表的区别

视图只保存的sql的逻辑,几乎不占空间
视图一般只用于查询

变量

系统变量

全局变量
- 跨连接共享(即多个连接共享同一个全局变量)
- 服务器每次重新启动则初始化所有全局变量(变为默认值)
会话变量
- 修改仅对当前连接(会话)有效

语法

模糊查询全局(会话)变量, 默认为会员变量
- show global | [session] variables [like 模糊查询字符串];
查看指定系统变量值
- select @@global | [session].系统变量名;
为某个系统变量赋值(两种方式)
- set @@global | [session].系统变量名 = 值;
- set global | [session] 系统变量名 = 值;

自定义变量

用户变量
- 对于当前会话(连接)有效
局部变量
- 仅仅在定义他的begin end中有效
- 只能在begin end中的第一句话定义

语法

用户变量
- 声明并初始化(赋值修改)(三种方式)
  - set @用户变量名 = 值;
  - set @用户变量名 := 值;(冒号用于区别于比较)
  - select @用户变量名 := 值;
- 通过select into赋值(查询出的结果必须只有一个)
  - select 字段 into @变量名 from 表;
- 查看
  - select @用户变量名;
局部变量
- 声明
  - declare 变量名类型 [default 默认值];
- 赋值
  - set 局部变量名 = 值;
  - set 局部变量名 := 值;(冒号用于区别于比较)
  - select @局部变量名 := 值;
  - select 字段 into 局部变量名 from 表;

存储过程

概念

一组预先编译好的sql语句集合, 类似批处理语句

优点

提高代码复用性
简化操作
减少编译次数并且减少了和数据库服务器的连接次数,提高效率

语法

创建
- create procedure 存储过程名(参数列表)
begin

存储过程体(一组合法的sql语句)

end
调用
- call 存储过程名(实参列表);
删除
- drop procedure 存储过程名; #只能一次删除一个
查看创建信息
- show create procedure 存储过程名;

注意:

参数列表包涵三部分
1. 参数模式参数名参数类型
2. 举例: in stuname varchar(20)
当存储过程体只有一句话时,begin end可以省略
存储过程体中的每条sql语句都必须加分号
delimiter设置结束标记

参数模式

in: 该参数可以作为输入,即该参数需要调用方传入值
out: 该参数可以作为输出,即该参数作为存储过程的返回值
inout: 该参数既可以作为输入,作为输出, 即既需要传入值也可以作为返回值返回

实例

#in和out模式存储过程
delimiter $
create procedure myp3(in account varchar(20), out telephone varchar(20), out rent varchar(20))
begin 
select u.telephone, u.rent into telephone, rent from userlist u
where u.account = account;
end $
call myp3("AAAA", @telephone, @rent);
select @telephone, @rent;

delimiter $
create procedure myp4(inout a int, inout b int)
begin 
set a=a*2;
set b=b*2;
end $
#调用inout模式存储过程需要先定义并初始化传入的变量
set @m = 10;
set @n = 20;
call myp4(@m, @n);
select @m, @n;

函数

概念

一组预先编译好的sql语句集合, 类似批处理语句

优点

提高代码复用性
简化操作
减少编译次数并且减少了和数据库服务器的连接次数,提高效率

区别

存储过程: 可以有0个或多个返回值, 适合批处理(增删改)
函数: 有且仅有一个返回值, 适合处理数据并得出一个结果

语法

创建
- create function 函数名(参数列表) returns 返回类型
  
  begin
```
		函数体
```
  end
调用
- select 函数名(参数列表) #和mysql内置函数调用方式相同比如count(*)
查看创建信息
- show create function 函数名;
删除
- drop function 函数名;

注意:

参数列表包涵两个部分: 参数名参数类型
函数体: 必须包涵return语句, 如果没有会报错, return语句后的语句没有意义
函数体只有一句话可以省略begin end
使用delimiter语句设置结束标记
创建函数时报错:

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

解决方法:

set global log_bin_trust_function_creators=TRUE;

或

明确指明函数的类型(将以下参数写在函数签名的末尾)
```
create function 函数名(参数列表) returns 返回类型 函数类型
```
1 DETERMINISTIC 确定的

2 NO SQL 没有SQl语句，当然也不会修改数据

3 READS SQL DATA 只是读取数据，当然也不会修改数据

4 MODIFIES SQL DATA 要修改数据

5 CONTAINS SQL 包含了SQL语句

实例

set global log_bin_trust_function_creators=TRUE;

create function myf1() returns int 
begin 
declare c int default 0; #定义局部变量
select count(*) into c #赋值
from users;
return c;
end ;
select myf1();

create function myf2(username varchar(20)) returns int 
begin 
set @`password`=""; #定义用户变量
select u.`password` into @`password` #赋值
from users u where u.`username` = username;
return @`password`;
end ;
select myf2("hzt");
select @`password`;

流程控制结构

分为顺序结构, 分支结构, 循环结构

分支结构

if函数
- 简单双分支
- if(表达式1,表达式2,表达式3)
- 执行顺序:如果表达式1成立, if函数返回表达式2的值,否则返回表达式3的值
- 应用: 任何位置
if结构
- 实现多重分支
- 语法
  - if 条件1 then 语句1;
    
    [elseif 条件2 then 语句2;]
    
    …
    
    [else 语句n;]
    
    end if;
- 应用: 只能在begin end中使用
case结构
- 类似switch语句,一般用于实现等值判断和区间判断
- 语法:
  - case 变量|表达式|字段;
    
    when 要判断的条件1 then 返回的值1或者语句;
    
    when 要判断的条件2 then 返回的值2或者语句;
    
    …
    
    else 要返回的值n或者语句;
    
    end case;
- 特点
  - 可以作为表达式,嵌套在其他语句中使用,可以放在任何地方,begin end里面或者外面
  - 可以作为独立语句,只能在begin end 中
  - 满足when条件直接跳出case判断,返回对应结果,如果都不满足则执行else语句
  - else语句可以省略,如果when条件都不满足且没有else语句则返回null
实例
```
create function test_if(score int) returns char reads sql data #reads sql data为只读函数类型 
begin 
if score >= 90 and score <= 100 then return 'A';
elseif score >= 80 then return 'B';
elseif score >= 60 then return 'C';
else return 'D';
end if;
end; 
select test_if(55);

create procedure test_case(in score int, out slevel varchar(20))
begin 
case 
when score >= 90 and score <= 100 then select 'A' into slevel;
when score >= 80 then select 'B' into slevel;
when score >= 60 then select 'C' into slevel;
else select 'D' into slevel;
end case;
end;

call test_case(80, @slevel);
select @slevel;
```

循环结构

循环控制

iterate: 结束本次循环,继续下一次
leave: 结束当前所在循环

实例

truncate users;#清空表 水位线归零

begin 
declare i int default 0;
w1:#标签
while i <= insertCount do 
set i = i + 1;
if mod(i,2) != 0 then iterate w1;#只插入偶数用户
end if;
if i > 20 then leave w1;#如果次数大于20次则跳出循环 必须使用循环结构的标签来跳出
end if;
insert into users(username, `password`) values(concat("批量用户",i), "666");
end while w1;
end;
call pro_while2(100);#调用函数

MySql架构介绍

文件类型

配置文件

mysql文件目录:C:\ProgramData\MySQL\MySQL Server 8.0

mysql bin目录:C:\Program Files\MySQL\MySQL Server 8.0\bin

配置文件:目录下的my.ini

日志文件

二进制日志文件log-bin:用于主从复制,默认关闭
错误日志log-error:默认关闭的,记录严重警告和错误信息,每次启动关闭的详细信息
查询日志log: 默认关闭, 记录查询的sql语句,开启会降低mysql的整体性能,记录日志需要消耗系统资源
数据文件:
- 数据库文件:C:\ProgramData\MySQL\MySQL Server 8.0\Data
- frm文件: 存放表结构 8.0已取消
- myd文件:存放表结构(MyISAM引擎)
- myi文件:存放索引(MyISAM引擎)
- ibd: innoDB引擎数据库单个表和关联的索引数据
- ibdata: InnoDB系统表空间信息文件

MySql逻辑架构

插件式的存储引擎架构将查询处理和其他的的系统任务以及数据的存储提取相分离

四层架构

连接层
- 一些客户端和连接服务(socket通信和tip/ip通信),主要完成一些连接处理、授权认证以及相关的安全方案（SSL）,在该层中引入了线程池的概念
服务层
- 大多数核心服务功能,使用sql接口sql语句进行分类,解析查询事务和对象权限,自动优化sql执行顺序,sql的缓存或者缓冲查询(提升查询速度)
引擎层
- 负责mysql中的数据的存储和提取,服务器通过api与引擎通信.不同引擎功能不同,我们可以根据自己的需要选取.
存储层
- 将数据存储在裸设备的文件系统之上,完成与存储引擎的交互

MySql存储引擎

查看命令

show ENGINES;
show variables like "%storage_engine%";

常用引擎
- MyISAM
- InnoDB

索引优化分析

SQL性能下降的原因

(执行时间长, 等待时间长)

查询语句写的烂
索引失效(create index 索引名 on 表名(字段名1,字段名2…))
- 单值
- 复合
表关联查询太多join(数据库结构设计有问题或者不得以的需求)
服务器调优以及各个参数配置(缓冲,线程数等)

索引简介

是什么

官方定义: 索引(index)是帮助mysql快速获取数据的一种数据结构
索引用于提高查找效率,类似于字典
详解
索引文件本身也很大,不能全部存储在内存中,往往也会存储在磁盘上
索引一般指B树(多路搜索树,并不一定是二叉的)结构组织索引.其中聚集索引,次要索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引.除B+树索引外还有哈希索引(hash index)等

优势

类似图书馆建立书目索引,提高数据检索的效率,降低数据库IO成本
通过索引对数据进行排序,降低数据排序的成本,降低了cpu的消耗

劣势

实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引也是要占空间的
虽然索引提高了查询速度,但是会降低更新表的速度,因为更新表数据的同时也要更新对应的索引信息
索引只是提高效率的一个因素,需要花时间建立最优秀的索引,或者优化查询方式

索引的分类

单值索引
- 即一个索引只包涵单个列,一个表可以有多个单列索引
唯一索引
- 索引列的值必须唯一,允许有空值
复合索引
- 一个索引包涵多个列
隐藏索引
- mysql支持更改索引的见性
降序索引
- mysql支持降序索引
基本语法
- 创建
  - create [unique] index inedexName on mytable(columname(length));
  - alter mytable add [unique] index [indexName] on(columname(length));
- 删除
  - drop index[indexName] on mytable;
- 查看
  - show index from table_name;
- alter命令添加索引

索引结构

BTree索引
- 一颗b+树,浅蓝色的为一个磁盘块,深蓝色为数据项,黄色为指针,例如磁盘块1包涵数据项17,35, 包涵指针p1,p2,p3
- p1表示小于17的磁盘块,p2表示17到35之间的磁盘块,p3表示大于35的磁盘块
- 真实的数据都存储在叶子节点即3、5、9、10…
- 非叶子节点不存储真实的数据,只存储搜索方向(边界)的数据,如17和35并不存在于数据表中
- 查找过程:如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
- 真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
Hash索引
full-text全文索引
R-Tree索引
深入理解MySql索引底层原理

那些情况需要建立索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段,外键关系建立索引
频繁更新的字段不适合建立索引(因为要同时更新索引)
where条件里用不到的字段不创建索引
单键/组合索引的选择(高并发倾向于创建组合索引)
查询中排序的字段,排序字段若通过索引访问将大大提高排序速度
查询中统计或者分组字段

那些情况不要建立索引

表记录太少(300万左右性能下降)
经常增删改的表
数据重复且分布平均的表字段(不同数据在此字段中出现的概率越接近1,建立索引效率提升的越大)

性能分析

MySql常见瓶颈
- CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候。
- IO: 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈: top,free,isotat和vmstat来查看系统的性能状态
Explain
- 是什么(查看执行计划)
  - 使用explain关键字可以模拟优化器执行sql查询语句,从而知道mysql是如何处理你的sql语句的.分析你的查询语句或者是表结构的性能瓶颈
  - 官网介绍:https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
- 能干嘛
  - 表的读取顺序(id)
  - 数据读取操作的操作类型(select_type)
  - 那些索引可以使用(possible_keys)
  - 那些索引被实际使用(keys)
  - 表之间的引用(ref)
  - 每张表有多少行被优化器查询(rows)
- 怎么玩
  - explain + sql语句
  - 执行计划包含的信息
- 各字段解释
  - id:select查询的序列号,包含一组数字,表示查询中执行select字句或操作表的顺序
    - 三种情况
      - id相同,执行顺序由上至下
      - id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
      - id相同不同,同时存在
  - select_type
    - 查询的类型,主要是用于区别普通查询、联合查询、子查询等复杂查询
      - simple: 简单的select查询,查询中不包含子查询或者union
      - primary: 查询中若包含任何复杂的子部分,最外层查询则被标记为primary(最后执行的部分)
      - subquery: 在select或where列表中包含了子查询
      - derived: 在from列表中包含的子查询被标记为derived(衍生)MySql会递归执行这些子查询,把结果妨碍临时表里
      - union: 若第二个select出现在union之后,则被标记为union;若union包涵在from字句的子查询中,外层select将被标记为derived
      - union result: 从union表获取结果的select
      - 等等…
  - table
    - 显示这一行的数据是关于那张表的
  - partitions
    - 查询将匹配记录的分区。该值NULL用于非分区表。
  - type
    - 显示查询使用了何种类型
    - 从最好到最差依次是: system > const > eq_ref > ref > range > index >ALL
      - system: 表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计
      - const: 表示通过索引一次就找到了,const用于比较priamry key或者 unique索引.因为只匹配一行数据,所以很快.如将主键置于where列表中,MySql就能将该查询转换为一个常量
      - eq_ref: 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配.常见于使用主键或唯一索引作为表连接条件的扫描.
      - ref: 非唯一性索引扫描,返回匹配某个单独值的所有行.本质上也是索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体.
      - range: 只检索给定范围的行,使用一个索引来选择行.key列显示了使用那个索引,一般就是在你where语句中出现了between、<、>、in等的查询,这种范围扫描扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引
      - index: Full Index Scan(全索引扫描), index与ALL的区别为index类型只遍历索引数.这通常比ALL快,因为索引文件比通常数据文件小(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)
      - all: 全表扫描(Full Index Scan)
    - 一般来说,得保证查询至少达到range级别,最好能达到ref
  - posssible_keys
    - 显示可能应用这张表上的索引,一个或多个.查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
  - key
    - 实际使用的索引.如果为null,则没有使用索引
    - 查询中若使用覆盖索引,则该索引仅出现在key列表中
  - key_len
    - 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度.在不损失精确性情况下,长度越短越好(即同样的查询结果,用到的索引越少越好)
    - key_len显示的值为索引字段的最大可能长度,并非实际使用的长度,即key_len是根据表定义计算而得,不是通过表内检索出的
  - ref
    - 该ref列显示哪些列或常量与条件列中指定的索引进行比较以从表中选择行。
    - 如果值为func，则使用的值是某个函数的结果。
  - rows
    - 根据表统计信息及索引选用情况,大致估算出找到所需记录所需要读取的行数
  - filtered
    - 该filtered列指示按表条件过滤的表行的估计百分比。最大值为 100，这意味着没有发生行过滤。从 100 开始减小的值表示过滤量增加。 rows显示估计的检查行数，rows× filtered显示与下表连接的行数。
  - Extra
    - 包涵不适合在其他列显示但十分重要的额外信息
      - Using filesort: 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取.mysql中无法利用索引完成的排序操作称为"文件排序"(尽快优化)
      - Using temporary: 使用了临时表保存中间结果,MySql在对查询结果排序时使用临时表.常见于排序order by和分组查询group by(必须马上立刻优化)
      - Using index: 表示相应的select操作中使用了覆盖索引(Covering Index),避免了访问表的数据行,效率不错.如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
        
        覆盖索引(Covering Index): 使用多个字段建立复合索引,而查找的字段正好与建立的复合索引顺序,数量相同.这样就只需要从索引中查询数据,不需要根据索引再次读取数据文件.即查询列要被所键的索引覆盖.
        select 语句中只取出需要的列
      - Using where: 表明使用where进行过滤
      - Using join buffer: 使用了连接缓存
      - Impossible where: where字句的值总是false
      - Select tables optimized away: 在没有group by字句的情况下,基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作,不必等到执行阶段在进行计算,查询执行计划生成的阶段即完成优化
      - Distinct: 优化distinct操作,在找到第一个匹配的元组后即停止寻找同样值的动作

索引优化

索引分析

双表连接索引优化

左连接索引建右表, 因为左表数据必须全部查询,建立索引没有意义
右连接索引建左表,遇上同理
如果索引字段已经确定,我们可以交换连接方向或者表位置以达到优化的目的

三表连接索引优化

根据表连接方向,在方向相反的字段建立索引
尽可能减少join语句中的NestedLoop的循环总次数: “永远用小结果集驱动大的结果集”
保证join语句中被驱动表上的join条件字段已经被索引(第一句话的抽象版)
当无法保证被驱动表的join字段被索引且内存资源充足的前提下,不要太吝啬joinBuffer的设置(my.ini配置大小)

索引失效

全值匹配最好
最佳左前缀法则
- 如果索引了多列,查询必须从索引的最左端前列开始并且不跳过索引中的列
不在索引列上做任何操作(计算,函数,(自动or手动)类型转换)会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列包含查询列或与查询列一致)),减少select *
mysql 在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描
is null, is not null 也无法使用索引
like以通配符开头("%abc")mysql索引失效会变成全表扫描,不以通配符开头则不会失效(“abc%”)
- 如何解决like “%str%”的索引失效问题: 使用覆盖索引,覆盖需要查询的列
字符串不加引号索引失效(隐式类型转换)
少用or,用它来连接时索引会失效

总结

对于单键索引,尽量选择对当前查询过滤性最好的索引
在选择复合索引时,过滤性越好的字段在复合索引的位置越靠前越好
在选择复合索引时,尽量选择能够包含更多当前查询语句中where条件字段的索引
尽可能通过分析统计信息和调整查询语句的写法来达到选择合适索引的目的

查询截取分析

跑一天查看慢sql情况
开启慢查询日志,设置阈值(几秒算慢sql)
explain+慢sql分析
show profile
运维经理 or DBA,进行SQL数据库服务器的参数调优

查询优化

永远小表驱动大表
- in和exist的使用
- exists语法:
  - select … from table where exists(subquery)
  - 该语法可以理解为: 将子查询的数据,放到子查询中做条件验证,根据验证结果(ture 或 false)决定主查询的结果是否得以保留
order by 关键字优化
- order by 字句,尽量使用index方式排序, 避免使用filesort方式排序
- 尽可能在索引列完成排序操作,遵照索引键的最佳左前缀
- 如果不在索引列上,filesort有两种算法(双路排序和单路排序):双路排序只需要两次I/O获取排序后的数据(一次查询,一次排序),单路排序是将数据存在buffer中进行排序然后直接取出,这样就只需一次I/O,但是如果buffer不足以存储查出的数据就会导致更多次的I/O,得不偿失
- 优化策略:
  - 增大sort_buffer_size参数
  - 增大max_length_for_sort_data参数
group by 关键字优化
- group by 实质是先排序后分组, 遵循索引建的最佳左前缀原则
- 当无法使用索引列,增大sort_buffer_size参数和max_length_for_sort_data参数
- where 高于 having, 能写在 where 限定的条件就不要去having限定了

慢查询日志

是什么

mysql的慢查询日志是mysql提供的一种日志记录,它用来记录在mysql中响应时间超过阈值的语句,具体指运行时间超过long_query_time值的SQL
long_query_time的默认值为10,意思是运行10秒以上的语句

怎么玩

说明
- mysql默认关闭慢查询日志
- 如果不是调优需要一般不建议启动该参数,因为开启慢查询日志或多或少会对性能有一些影响
查看是否开启:
- show variables like ‘%slow_query_log%’;
- 查看参数: show variables like ‘%long_query_time%’;
- 开启: set global slow_query_log=1
- 设置阈值时间: set global long_query_time = 3; (单位秒)

MySql锁机制

概述

定义
- 锁是计算机协调多个进程或线程并发访问某一资源的机制
锁的分类
- 按操作类型
  - 读锁(共享锁): 针对同一份数据,多个读操作可以同时进行而不会互相影响,写操作会被阻塞
  - 写锁(排他锁): 当前操作没有完成前, 它会阻断其他写锁和读锁
- 按数据操作粒度:
  - 表锁(偏读)
    - 偏向MyISAM引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突概率最高,并发度最低
  - 行锁(偏写)
    - 偏向InnoDB存储引擎, 开销大, 加锁慢; 会出现死锁; 锁定粒度最小, 发生冲突的概率最低, 并发度也最高
    - innoDB与MyISAM的最大不同有两点: 支持事务; 采用行锁(行锁支持事务)
  - 页锁
语法
- 手动增加表锁: lock table 表名 read/write, 表名2 read/write, …
- 查看表上加过的锁: show open tables;
- 释放所有表锁: unlock tables;

注意: 索引失效会导致行锁变表锁

间隙锁的危害

什么是间隙锁
- 当我们用范围条件而不是等值条件检索数据,并请求共享或者排他锁时,innoDB会给符合条件的已有数据记录的索引加锁;对于键值在条件范围内但并不存在的记录,叫做"间隙(gap)"
- innoDB也会对"间隙"加锁,这种锁机制就是所谓的间隙锁(next-key锁)
危害:
- 因为query执行过程中进行范围查找会锁定范围内的所有索引键值,即使这个键值不存在.所以会导致无法插入锁定键值范围内的任何数据.在某些场景可能会对性能造成较大损害

优化建议

尽可能让所有数据检索都通过索引来完成,避免无索引行升级为表锁
合理设计索引,尽量缩小锁的范围
尽可能减少检索条件,避免间隙锁
尽量控制事务大小,减少锁定资源量和时间长度
尽可能降低事务隔离级别

主从复制

复制的基本原理

slave会从master读取binlog来进行数据同步

复制的基本原则

每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个slave

复制的最大问题

延时

天阳在线€

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
7、mysql的事务,视图,索引优化,引擎

文章目录事务事务的ACID属性事务的分类delete 和 truncate的区别事务并发问题事务的隔离级别视图应用场景语法视图的更新视图和表的区别变量系统变量自定义变量存储过程概念优点语法参数模式实例函数概念优点区别语法实例流程控制结构分支结构实例循环结构分类循环控制实例MySql架构介绍文件类型配置文件日志文件MySql逻辑架构四层架构MySql存储引擎索引优化分析SQL性能下降的原因索引简介是什么优势劣势索引的分类索引结构那些情况需要建立索引那些情况不要建立索引性能分析索引优化索引分析双表连接索引优化三
复制链接

扫一扫