mysql实战总结_mysql update 和 delete耗时
赞
踩
MySQL 处理重复数据
@see MySQL处理重复数据 https://www.runoob.com/mysql/mysql-handling-duplicates.html
子查询
子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。
1.连接语句join，from中如：
<select id="getExtendedGoodsReport" resultMap="extendedGoodsResultMap" parameterType="Object"> SELECT t1.*, CASE WHEN t2.assessmentSale = 0.00 THEN NULL ELSE round(t1.extendedSale / t2.assessmentSale,6) END extensionRate, CASE WHEN t3.extendedSaleTotal = 0.00 THEN NULL ELSE round(t1.extendedSale / t3.extendedSaleTotal,6) END rate FROM ( SELECT mpc.category_name AS NAME, SUM(amount_ybxsje) AS extendedSale FROM t_sold_yb yb RIGHT JOIN m_product_category mpc ON yb.produt_pl = mpc.product_pl <include refid="extendedGoodsReportWhereClause" /> GROUP BY mpc.category_no, mpc.category_name ) t1 INNER JOIN ( SELECT mpc.category_name AS NAME, SUM(amount_khxssr) AS assessmentSale FROM t_sold_yb yb RIGHT JOIN m_product_category mpc ON yb.produt_pl = mpc.product_pl WHERE zdeptmnt=#{zdeptmnt} <if test="categoryList != null and categoryList.size()!=0"> and produt_pl IN <foreach collection="categoryList" item="item" index="index" open="(" close=")" separator=","> #{item.categoryNo} </foreach> </if> <![CDATA[ AND yb."DATE" >= #{startTimeStr,jdbcType=VARCHAR} AND yb."DATE" <= #{endTimeStr,jdbcType=VARCHAR} ]]> GROUP BY mpc.category_no, mpc.category_name ) t2 ON t1.NAME = t2.NAME NATURAL JOIN ( SELECT SUM(amount_ybxsje) AS extendedSaleTotal FROM t_sold_yb yb <where> zdeptmnt=#{zdeptmnt} <if test="zplant != null and zplant != ''"> and zplant=#{zplant} </if> <![CDATA[ AND yb."DATE" >= #{startTimeStr,jdbcType=VARCHAR} AND yb."DATE" <= #{endTimeStr,jdbcType=VARCHAR} ]]> </where> ) t3 ORDER BY t1.extendedSale DESC </select>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
2.in，>,=等表达式中
in中：
SELECT ename,job,sal
FROM EMP
WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%')；
- 1
- 2
- 3
"="中：
select ename,deptno,sal
from emp
where deptno=(select deptno from dept where loc='NEW YORK')；
- 1
- 2
- 3
思考
1.高并发下，如何做到安全的修改同一行数据。
1.乐观锁，通过版本号比较
2.悲观锁
1）业务层加锁
2）数据库层加锁，如使用select…for update或lock in share mode。
一、高并发下，如果出现大量锁表，你该如何解决？
1.判断是否出现锁表
以下情况则出现了大量的锁表，导致表无法正常读写
2.解决办法
执行以下sql,查找锁表的进程
select concat('kill ',id,';') from information_schema.processlist
where `State` = 'Waiting for table metadata lock'
- 1
- 2
找到对于的进程后，执行kill语句，杀掉对应的进程
3.产生锁表的原因
通常情况下，数据库锁表大多是因为程序设计不合理导致的，在写代码的时候，我们要对业务场景充分考虑，尽量做到以下两点：
- 减少程序中 DML（insert，update，delete） 操作所花费的时间，对此类操作做好隔离控制，防止阻塞。
- 如果事务产生异常，确保事务可以正常回滚。
- 不要轻易对线上数据库进行 DDL 操作，尤其是使用量大且频繁的数据表，强行修改可能会引发灾难性后果
4.后续注意事项
不要轻易DDL，目前修改线上表结构主要有以下几种方式：
- 等到使用量小的时候（例如半夜）修改，但有一定风险。
- 暂时停止服务，修改完毕再上线，但会影响用户体验。
二、线上执行DDL
一般使用Online DDL相关的工具去执行DDL，一般建议由专业的DBA去负责执行，而不是后端研发去执行。
MySQL毫无疑问是最流行的开源单机数据库。在5.5版本以前，MySQL不支持Online DDL。需要做schema变更时（例如：修改列、加索引等），要么锁表变更(禁写)，要么通过主备切换的方式来进行。第二种方式的缺陷在于：需要DBA人工介入，而且主备的schema变更先后生效，无法严格一致。
既然数据库内部搞不定，那就在数据库外部做。pt-online-schema-change, gh-ost等变更工具，通过建立目标schema的影子表，借助触发器双写或者拉取binlog双写，最终通过rename影子表操作来达到变更的效果。
5.6版本以后，MySQL内部开始支持Online DDL。主要原理是将数据分为基线和增量两部分，开启一个单独线程变更基线数据，同时增量实时记录到row-log里。基线变更结束后，通过回放row-log，实现增量同步。整个过程中有几个关键点：第一，开始变更时获取快照，这个阶段需要禁写，确保获取snapshot对应的基线，和后续增量(row-log)是一份完整的数据；第二，在基线变更完成后，开始回放row-log，由于row-log随着业务的写入在不断追加，因此这里基于一个前提：row-log的回放速度高于业务写入的速度，否则可能一直追不上，schema变更也就无法完成；第三，schema生效阶段同样需要禁写，确保不会有新的写进来，新的schema开始生效。
目前，MySQL8.0在对于部分加列等schema变更操作做了优化，支持instant ddl，有点类似X-DB Fast DDL。其余Online DDL的基本原理仍保持不变。对于MySQL的Online DDL方案，需要说明的是：MySQL主备副本之间通过binlog同步，主的schema变更成功后，才会写binlog同步给备库，然后备库才开始做ddl。假设一个ddl变更需要1个小时，那么备库最多可能会延迟2倍的变更时间。若变更期间，主库发生故障，备库数据还未追平，则无法提供服务的。
gh-ost工具
gh-ost工具的使用相关文档可参考：
gh-ost原理及简单使用 http://www.linzhoukai.com/?p=246
三、慢sql治理解决方案
1、什么是慢 SQL
什么是慢SQL？顾名思义，运行时间较长的 SQL 语句即为慢 SQL！
那问题来了，多久才算慢呢？
这个慢其实是一个相对值，不同的业务场景下，标准要求是不一样的。
我们都知道，我们每执行一次 SQL，数据库除了会返回执行结果以外，还会返回 SQL 执行耗时，以 MySQL 数据库为例，当我们开启了慢 SQL 监控开关后，默认配置下，当 SQL 的执行时长大于 10 秒，会被记录到慢 SQL 的日志文件中
2、慢 SQL 危害
这里要从慢 SQL 的危害谈起，以 MySQL 数据库为例，总结起来有以下几点：
1.当出现慢查询，DDL 操作都会被阻塞，也就是说创建表、修改表、删除表、执行数据备份等操作都需要等待，这对实时备份重要数据的系统来说是不可容忍的。
2.慢查可能会占用 mysql 的大量内存，严重的时候会导致服务器直接挂掉，整个系统直接瘫痪
3.慢 SQL 的执行时间过长，可能会导致应用的进程因超时被 kill，无法返回结果给到客户端
4.造成数据库幻读、不可重复读的概率更大，假设该慢 SQL 是一个更新操作但因执行时间过长未提交，而另一条 SQL 也在更新数据并且已提交，用户再次查询的时候，看到的数据可能与实际结果不符
5.严重影响用户体验，SQL 的执行时间越长，页面加载数据耗时也就越长
6.直接造成大量的数据库连接超时，服务无法正常响应。
如常见的连接池被耗尽的情况：
遇到这种问题快速的解决方案有
1.kill掉慢sql的进程
2.重启应用服务器
根本上解决方案则是定位慢sql并优化解决。
3.如何定位慢 SQL
1.开启慢sql监控
以 MySQL 为例，我们可以通过如下方式，查询是否开启慢 SQL 的监控。
show variables like 'slow_query_log%';
- 1
通过如下命令，开启慢 SQL 监控，执行成功之后，客户端需要重新连接才能生效。
-- 开启慢 SQL 监控
set global slow_query_log = 1;
- 1
- 2
- 3
以上的操作，当服务器不重启会一直有效，但是当服务器一单重启之后，配置就会失效，如果想永久生效，可以通过修改全局配置文件my.cnf使之永久生效。
以 CentOS 为例，打开my.cnf配置文件，添加如下配置变量。
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/ecs-203056-slow.log
long_query_time = 1
- 1
- 2
- 3
- 4
- 5
最后，重启 mysql 服务器
2.配置慢 SQL 阀值
默认的慢 SQL 阀值是10秒，可以通过如下语句查询慢 SQL 的阀值。
-- 查询慢 SQL 的阀值
show variables like "long_query_time";
-- 修改慢 SQL 的阀值
set global long_query_time = 3;
- 1
- 2
- 3
- 4
3.查看慢sql
假如慢sql的阀值是3s,慢sql日志文件是在/mnt/cp/mysql/log/下面。
则执行下面命令即可抓起到对应的慢sql
日志内容详解：
Time：表示客户端查询时间
root[root]：表示客户端查询用户和IP
Query_time：表示查询耗时
Lock_time：表示等待 table lock 的时间，注意InnoDB的行锁等待是不会反应在这里的
Rows_sent：表示返回了多少行记录（结果集）。
Rows_examined：表示检查了多少条记录。
4.分析慢 SQL的产生原因
其中影响成本开销值的计算，主要是I/O成本和CPU成本这两个指标。
从I/O成本视角看：
当表的数据量越大，需要的 I/O 次数也就越多
从磁盘读取数据比从缓存读取数据，I/O 消耗的时间更多
全表扫描比通过索引快速查找，I/O 消耗的时间和次数更多
从CPU成本视角看：
当 SQL 中有排序、子查询等复杂的操作时，CPU 需要先把数据存到临时表中，再对数据进行加工，需要的 CPU 资源更多
全表扫描相比于通过索引快速查找，需要的 CPU 资源也更多
因此我们不难发现，在没有开启缓存的情况下，当表的数据量越大，如果 SQL 又没有走索引，很容易发生查询慢的问题。
参考资料
慢SQL，压垮团队的最后一根稻草！https://blog.csdn.net/dxflqm_pz/article/details/126360562
参考资料
1.http://blog.csdn.net/buptzhengchaojie/article/details/50992923
2.mysql事务，select for update，及数据的一致性处理 https://www.cnblogs.com/houweijian/p/5869243.html
