【总结】mysql调优
一.事务
1.事务的特性
(1)原子性(Atomicity),可以理解为一个事务内的所有操作要么都执行,要么都不执行。
(2)一致性(Consistency),可以理解为数据是满足完整性约束的,也就是不会存在中间状态的数据,比如你账上有400,我账上有100,你给我打200块,此时你账上的钱应该是200,我账上的钱应该是300,不会存在我账上钱加了,你账上钱没扣的中间状态。
(3)隔离性(Isolation),指的是多个事务并发执行的时候不会互相干扰,即一个事务内部的数据对于其他事务来说是隔离的。
(4)持久性(Durability),指的是一个事务完成了之后数据就被永远保存下来,之后的其他操作或故障都不会对事务的结果产生影响。
2.事务的并发问题
(1)脏读:一个事务读取到另一个事务没有提交的数据
(2)不可重复读:同一事务中,两次读取同一数据,得到的内容不同
(3)幻读:同一事务中,用同样的操作读取两次,得到的记录数不同
3.事务的隔离级别
(1)读未提交:事务A可以读取到事务B未提交的数据
(2)读已提交:事务A只能读取其它事务已提交的数据(避免了脏读)
(3)可重复读:事务A只保存另一个事务做更新操作前的状态,保证另一个事务update时,当前事务的查询结果是一致的(避免了不可重复读)(mysql默认)
(4)序列化:事务串行化顺序执行(避免脏读 不可重复读 幻读)
二.锁
1.锁的类型
1.共享锁:(读锁)允许事务读一行数据
2.排他锁:(写锁)允许事务删除或更新一行数据
(共享锁之间是兼容的,而互斥锁与其他任意锁都不兼容)
2.锁的粒度
1.表锁
表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。由于表级锁一次会将整个表锁定,所以可以很好的避免困扰我们的死锁问题
使用表级锁定的主要是MyISAM,MEMORY,CSV等一些非事务性存储引擎
MyISAM在执行查询前,会自动执行表的加锁、解锁操作,一般情况下不需要用户手动加、解锁,但是有的时候也需要显示加锁。比如:检索某一个时刻t1,t2表中数据数量。
LOCK TABLE t1 read, t2 read;
select count(t1.id1) as 'sum' from t1;
select count(t2.id1) as 'sum' from t2;
UNLOCK TABLES;
2.页锁
页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间,页级锁定和行级锁定一样,会发生死锁。
使用页级锁定的主要是BDB存储引擎
3.行锁
行级锁定最大的特点就是锁定对象的粒度很小,所以发生锁定资源争用的概率也最小,并发性能好,但是容易发生死锁
使用行级锁定的主要是InnoDB存储引擎
3.InnoDB中的锁(表锁 行锁 意向锁)
意向锁
innodb支持行锁和表锁。为了支持多粒度锁定,InnoDB 存储引擎引入了意向锁
如果没有意向锁,当已经有人使用行锁对表中的某一行进行修改时,如果另外一个请求要对全表进行修改,那么就需要对所有的行是否被锁定进行扫描,在这种情况下,效率是非常低的;不过,在引入意向锁之后,当有人使用行锁对表中的某一行进行修改之前,会先为表添加意向互斥锁(IX),再为行记录添加互斥锁(X),在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了,只需要通过等待意向互斥锁被释放就可以了。
意向锁其实不会阻塞全表扫描之外的任何请求,它们的主要目的是为了表示是否有人请求锁定表中的某一行数据
意向锁也分为两种:
(1)意向共享锁(IS):事务想要在获得表中某些记录的共享锁,需要在表上先加意向共享锁。
(2)意向互斥锁(IX):事务想要在获得表中某些记录的互斥锁,需要在表上先加意向互斥锁。行锁的算法
innodb有三种行锁的算法,分别是:
(1)行锁(record lock):锁直接加在索引记录上面。锁住的是key(防止不可重复读)
(2)间隙锁(gap lock):锁定索引记录间隙,确保索引记录的间隙不变。作用就是防止其他事务的插入操作,以此防止幻读的发生
(3)后码锁(next-key Lock):行锁和间隙锁组合起来就叫Next-Key Lock。
当InnoDB扫描索引记录的时候,会首先对索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。加上间隙锁之后,其他事务就不能在这个间隙修改或者插入记录
三.sql优化步骤
1.通过show status命令了解各种SQL的执行频率
show status like 'com_%'; //查询(增删改查等执行的次数.com_commit/rollback了解提交和回滚的情况,回滚频繁意味着编码可能存在问题)
show status like 'slow_query'; //查询慢查询的次数
2.定位执行效率较低的sql
在my.cnf 里 通过 log-slow-queries[=file_name]开启慢日志查寻
3.通过EXPLAIN分析较低SQL的执行计划
1.id
select识别符。我理解的事sql执行的顺序的标识
(1)id相同时,执行顺序由上至下
(2)如果是子查询,id序号会递增,id越大优先级越高,越先被执行
2.select_type
表示查询中每个select子句的类型
select_type查询类型 | 说明 |
---|---|
SIMPLE | 简单的 select 查询,不使用 union 及子查询 |
PRIMARY | 最外层的 select 查询 |
SUBQUERY | 在select或者where中包含了子查询,子查询中的第一个 select 查询,不依赖于外部查询的结果集 |
DEPENDENT SUBQUERY | 子查询中的第一个 select 查询,依赖于外部 查询的结果集 |
DERIVED | 用于 from 子句里有子查询的情况。MySQL 会递归执行这些子查询, 把结果放在临时表里 |
UNION | UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集 |
DEPENDENT UNION | UNION 中的第二个或随后的 select 查询,依 赖于外部查询的结果集 |
UNCACHEABLE UNION | UNION 中的第二个或随后的 select 查询,属 于不可缓存的子查询 |
特别关注 DEPENDENT SUBQUERY ,会严重消耗性能,不会进行子查询,会先进行外部查询,生成结果集,再在内部进行关联查询。子查询的执行效率受制于外层查询的记录数
3.table
显示这一步所访问数据库中表名称(显示这一行的数据是关于哪张表的)
4.type
对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”
常用的类型有: ALL、index、range、 ref、eq_ref、const、system(从左到右,性能从差到好)
type | 说明 |
---|---|
ALL | MySQL将遍历全表以找到匹配的行 |
index | 遍历索引树 |
range | 只检索给定范围的行,使用索引来选择行 |
ref | 查找条件列使用了索引而且不为主键和unique。就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描 |
ref_eq | 使用了主键或者唯一性索引进行查找 |
const | 常量连接,表最多只有一行匹配,通用用于主键或者唯一索引比较时(where后面是逐渐或者唯一索引) |
system | 表中只有一行 |
5.possible_keys
预测用到的索引
6.key
实际用到的索引
7.key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
8.ref
哪些列或者常量被用于查找索引列上的值(只有当type为ref的时候,ref这列才会有值)
explain select * from t1, t2 where t1.col1 = t2.col1 and t1.clo2 = 'ab';
ref的值为: t2.col1 ,"ab"
9.rows
估算的找到所需的记录所需要读取的行数
10.Extra
包含MySQL解决查询的详细信息
(1)Using filesort
mysql的排序方法主要分为两大类,一种是排序的字段是有索引的,因为索引是有序的,所以不需要另外排序,另一种是排序的字段没有索引,所以需要对结果进行排序,在这种情况下才会如上图所示显示一个Using filesort
eg:select * from a where a1="" order by a2; //a2没有建索引
(2)using temporary:性能损耗大,用到临时表(常见于group by)
eg:select * from a where a1="" group by a2
(3)using where:表明虽然用到了索引,但是没有索引覆盖,产生了回表。
select name,age from a where age="" //name需要回表查询
(4)using index:索引覆盖,查询的内容可以直接在索引中拿到
select age from a where age="" //索引覆盖
四.sql常用的优化方式
1.索引优化
注意索引失效的情况
(1)or连接的条件不是每一个列都有索引,这时有索引的列也会失效
(2)复合索引遵守“最左前缀”原则,即在查询条件中使用了复合索引的第一个字段,索引才会被使用
(3)like查询是以%开头(以%结尾,索引可以使用)
(4)存在索引列的数据类型隐形转换,则用不上索引(比如列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引)
(5)where 子句里对索引列上有数学运算,用不上索引
(6)where 子句里对有索引列使用函数,用不上索引
(7)如果mysql估计使用全表扫描要比使用索引快,则不使用索引(比如数据量极少的表)
2.设计调优
(1)数据量大,可以分库分表
(2)主从同步,读写分离
3.业务优化
根据具体业务场景进行优化
最新文章
- Tomcat的目录结构
- MySQL数据迁移到SQL Server
- SQL CREATE TABLE 语句\SQL 约束 (Constraints)\SQL NOT NULL 约束\SQL UNIQUE 约束
- devexpress treelist 过滤
- 在Salesforce中处理Email的发送
- js中Dom对象的position属性
- [wikioi1553]互斥的数(数学分析+散列/数学分析+二分)
- 基于矩阵模式的 Web 软件测试手段(转)
- wget 使用技巧
- 2016: [Usaco2010]Chocolate Eating
- 追溯 React Hot Loader 的实现
- div悬浮窗口设计来完成注册页面
- css3 样式过度器 Transition
- ffmpeg的使用说明
- 兼容性问题:backgroud-size支持IE8浏览器的方法
- 在数据库中sql查询很快,但在程序中查询较慢的解决方法
- python 小程序,打印数字
- 从命令行模式运行Windows管理工具。
- [bug]不包含“AsNoTracking”的定义
- Maven是什么?
热门文章
- phpcms v9.6.0任意文件上传漏洞
- modelviewset settings 配置
- 工作流引擎Activiti与SpringBoot2整合--开源软件诞生17
- MacOS如何正确配置Idea自带Maven插件的环境变量?(亲测)
- error C3861: “back_inserter”: 找不到标识符
- 图像sensor的bitdepth
- CF149D Coloring Brackets
- 【题解】【HAOI2011】Problem b
- devops构建IT服务供应链
- node将js中的json对象生成到新的excel表中