mysql5.7优化

最新推荐文章于 2023-08-03 19:25:15 发布
最新推荐文章于 2023-08-03 19:25:15 发布
阅读量438 收藏
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wind520/article/details/103825918
版权

1：查询显示当前的隔离级别

mysql> show global variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | REPEATABLE-READ|
| tx_isolation          | REPEATABLE-READ|
+-----------------------+----------------+
2 rows in set (0.00 sec)

2：设置隔离级别

mysql> set global transaction_isolation ='read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.00 sec)

3：优化批量删除

    <delete id="delHisOrders" >
		DELETE FROM re_detail_his where 
		order_id in (select order_id from re_orders where shop_id = #{shopId} and sale_date between #{startDate} and #{endDate} )
	</delete> 	  

    <delete id="delHisOrders" >
     delete a from  re_detail_his a   join re_orders b on a.order_id=b.order_id  where  b.shop_id = #{shopId} AND b.sale_date BETWEEN #{startDate} and #{endDate}
	</delete>

第2条是优化后的，效率大大提升

3：查询锁

SELECT     'Blocker' role     , p.id     , p.user     , left(p.host, locate(':', p.host) - 1) HOST     , tx.trx_id     , tx.trx_state     , tx.trx_started     , lo.lock_table     , tx.trx_query     , timestampdiff(SECOND, tx.trx_started, now()) duration     , lo.lock_mode     , lo.lock_type     , lo.lock_index     , tx.trx_tables_in_use     , tx.trx_tables_locked     , tx.trx_rows_locked FROM     information_schema.innodb_trx tx     JOIN information_schema.innodb_lock_waits lw         ON lw.blocking_trx_id = tx.trx_id     JOIN information_schema.innodb_locks lo         ON lo.lock_trx_id = tx.trx_id     JOIN information_schema.processlist p         ON p.id = tx.trx_mysql_thread_id UNION SELECT     'Blockee' role     , p.id     , p.user     , left(p.host, locate(':', p.host) - 1) HOST     , tx.trx_id     , tx.trx_state     , tx.trx_started     , lo.lock_table     , tx.trx_query     , timestampdiff(SECOND, tx.trx_started, now()) duration     , lo.lock_mode     , lo.lock_type     , lo.lock_index     , tx.trx_tables_in_use     , tx.trx_tables_locked     , tx.trx_rows_locked FROM     information_schema.innodb_trx tx     JOIN information_schema.innodb_lock_waits lw         ON lw.requesting_trx_id = tx.trx_id     JOIN information_schema.innodb_locks lo         ON lo.lock_trx_id = tx.trx_id     JOIN information_schema.processlist p         ON p.id = tx.trx_mysql_thread_id;

mysql> SELECT     'Blocker' role     , p.id     , p.user     , left(p.host, locate(':', p.host) - 1) HOST     , tx.trx_id     , tx.trx_state     , tx.trx_started     , lo.lock_table     , tx.trx_query     , timestampdiff(SECOND, tx.trx_started, now()) duration     , lo.lock_mode     , lo.lock_type     , lo.lock_index     , tx.trx_tables_in_use     , tx.trx_tables_locked     , tx.trx_rows_locked FROM     information_schema.innodb_trx tx     JOIN information_schema.innodb_lock_waits lw         ON lw.blocking_trx_id = tx.trx_id     JOIN information_schema.innodb_locks lo         ON lo.lock_trx_id = tx.trx_id     JOIN information_schema.processlist p         ON p.id = tx.trx_mysql_thread_id UNION SELECT     'Blockee' role     , p.id     , p.user     , left(p.host, locate(':', p.host) - 1) HOST     , tx.trx_id     , tx.trx_state     , tx.trx_started     , lo.lock_table     , tx.trx_query     , timestampdiff(SECOND, tx.trx_started, now()) duration     , lo.lock_mode     , lo.lock_type     , lo.lock_index     , tx.trx_tables_in_use     , tx.trx_tables_locked     , tx.trx_rows_locked FROM     information_schema.innodb_trx tx     JOIN information_schema.innodb_lock_waits lw         ON lw.requesting_trx_id = tx.trx_id     JOIN information_schema.innodb_locks lo         ON lo.lock_trx_id = tx.trx_id     JOIN information_schema.processlist p         ON p.id = tx.trx_mysql_thread_id;
Empty set, 4 warnings (0.01 sec)

有锁的情况

mysql> SELECT
    ->     'Blocker' role
    ->     , p.id
    ->     , p.user
    ->     , left(p.host, locate(':', p.host) - 1) HOST
    ->     , tx.trx_id
    ->     , tx.trx_state
    ->     , tx.trx_started
    ->     , lo.lock_table
    ->     , tx.trx_query
    ->     , timestampdiff(SECOND, tx.trx_started, now()) duration
    ->     , lo.lock_mode
    ->     , lo.lock_type
    ->     , lo.lock_index
    ->     , tx.trx_tables_in_use
    ->     , tx.trx_tables_locked
    ->     , tx.trx_rows_locked
    -> FROM
    ->     information_schema.innodb_trx tx
    ->     JOIN information_schema.innodb_lock_waits lw
    ->         ON lw.blocking_trx_id = tx.trx_id
    ->     JOIN information_schema.innodb_locks lo
    ->         ON lo.lock_trx_id = tx.trx_id
    ->     JOIN information_schema.processlist p
    ->         ON p.id = tx.trx_mysql_thread_id
    -> UNION
    -> SELECT
    ->     'Blockee' role
    ->     , p.id
    ->     , p.user
    ->     , left(p.host, locate(':', p.host) - 1) HOST
    ->     , tx.trx_id
    ->     , tx.trx_state
    ->     , tx.trx_started
    ->     , lo.lock_table
    ->     , tx.trx_query
    ->     , timestampdiff(SECOND, tx.trx_started, now()) duration
    ->     , lo.lock_mode
    ->     , lo.lock_type
    ->     , lo.lock_index
    ->     , tx.trx_tables_in_use
    ->     , tx.trx_tables_locked
    ->     , tx.trx_rows_locked
    -> FROM
    ->     information_schema.innodb_trx tx
    ->     JOIN information_schema.innodb_lock_waits lw
    ->         ON lw.requesting_trx_id = tx.trx_id
    ->     JOIN information_schema.innodb_locks lo
    ->         ON lo.lock_trx_id = tx.trx_id
    ->     JOIN information_schema.processlist p
    ->         ON p.id = tx.trx_mysql_thread_id;
+---------+-----+----------+-----------+-----------+-----------+---------------------+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-----------+-----------+--------------+-------------------+-------------------+-----------------+
| role    | id  | user     | HOST      | trx_id    | trx_state | trx_started         | lock_table                    | trx_query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | duration | lock_mode | lock_type | lock_index   | trx_tables_in_use | trx_tables_locked | trx_rows_locked |
+---------+-----+----------+-----------+-----------+-----------+---------------------+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-----------+-----------+--------------+-------------------+-------------------+-----------------+
| Blocker | 121 | jiangxin | localhost | 125439453 | RUNNING   | 2020-01-03 11:38:18 | `jxx_order`.`re_orders`       | DELETE FROM re_discount_his WHERE order_id IN (SELECT order_id FROM re_orders WHERE shop_id = 50352 AND sale_date BETWEEN '2019-12-01' AND '2020-01-01')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |        3 | S,GAP     | RECORD    | idx_order_id |                 2 |                 2 |          230233 |
| Blocker | 121 | jiangxin | localhost | 125439453 | RUNNING   | 2020-01-03 11:38:18 | `jxx_order`.`re_discount_his` | DELETE FROM re_discount_his WHERE order_id IN (SELECT order_id FROM re_orders WHERE shop_id = 50352 AND sale_date BETWEEN '2019-12-01' AND '2020-01-01')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |        3 | X         | RECORD    | PRIMARY      |                 2 |                 2 |          230233 |
| Blockee | 129 | jiangxin | localhost | 125439863 | LOCK WAIT | 2020-01-03 11:38:21 | `jxx_order`.`re_orders`       | INSERT INTO re_orders (location_id, shop_id, shop_name, sale_date, order_id, order_time, check_time, amount, fact_amount, ds_amount, direct_amount, addfee, chargeback, is_chargeback, hall_id, hall_name, table_id, table_name, people_num, discount_type, discount, palm_id, is_payoff, tipa, pay_way, shop_order_id, islive, order_type, renum, cashier_code, cashier_name, pos_id, sum_mfact, sum_dfact, sum_pay, dept_id, tenant_id) VALUES ('18', 50784, '龙子湖店', '2020-01-03', '5078400181625', '2020-01-03 11:17:33.0', '2020-01-03 11:17:49.0', 25, 25, 0, 0, 0, 0, 0, 1, 'A区', 7, '东C7', 1, 0, 1, '刘桂珍', 1, 2, '金石支付:25;', '00181625', 0, 0, 0, '收银员', '001', '0', 25, 0, 25, 106, 1020)                                  |        0 | X,GAP     | RECORD    | idx_order_id |                 1 |                 1 |               1 |
| Blockee | 132 | jiangxin | localhost | 125439832 | LOCK WAIT | 2020-01-03 11:38:20 | `jxx_order`.`re_discount_his` | DELETE FROM re_discount_his WHERE shop_id = 50379 AND shop_order_id = '00022921'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |        1 | X         | RECORD    | PRIMARY      |                 1 |                 1 |               2 |
| Blockee | 119 | jiangxin | localhost | 125439823 | LOCK WAIT | 2020-01-03 11:38:20 | `jxx_order`.`re_orders`       | INSERT INTO re_orders (location_id, shop_id, sale_date, order_id, order_time, check_time, amount, fact_amount, ds_amount, direct_amount, addfee, chargeback, is_chargeback, hall_id, hall_name, table_id, table_name, people_num, discount_type, discount, palm_id, is_payoff, tipa, pay_way, shop_order_id, islive, order_type, renum, cashier_code, cashier_name, pos_id, sum_mfact, sum_dfact, sum_pay, dept_id, tenant_id) VALUES ('1', 50746, '2020-01-03', '5074600005468', '2020-01-03 11:24:50.0', '2020-01-03 11:25:38.0', 16, 16, 0, 0, 0, 0, 0, 1, '大厅', 2, '2', 1, 0, 1, '收银员', 1, 2, '微储值:16;', '00005468', 0, 0, 0, '收银员', '收银员', '0', 16, 0, 16, 1, 1)                                                                |        1 | X,GAP     | RECORD    | idx_order_id |                 1 |                 1 |               1 |
| Blockee | 131 | jiangxin | localhost | 125439717 | LOCK WAIT | 2020-01-03 11:38:19 | `jxx_order`.`re_orders`       | INSERT INTO re_orders (location_id, shop_id, shop_name, sale_date, order_id, order_time, check_time, amount, fact_amount, ds_amount, direct_amount, addfee, chargeback, is_chargeback, hall_id, hall_name, table_id, table_name, people_num, discount_type, discount, palm_id, is_payoff, tipa, pay_way, shop_order_id, islive, order_type, renum, cashier_code, cashier_name, pos_id, sum_mfact, sum_dfact, sum_pay, dept_id, tenant_id) VALUES ('1', 50751, '嘉味扬州万象汇店', '2020-01-03', '5075100004338', '2020-01-03 11:26:46.0', '2020-01-03 11:27:03.0', 59, 59, 0, 0, 0, 0, 0, 4, '外卖区', 69, '美团9', 1, 0, 1, '收银员', 1, 0, '微支一键付:59;', '00004338', 0, 0, 0, '收银员', '101', '0', 59, 0, 59, 1, 1)                         |        2 | X,GAP     | RECORD    | idx_order_id |                 1 |                 1 |               1 |
| Blockee | 123 | jiangxin | localhost | 125439636 | LOCK WAIT | 2020-01-03 11:38:19 | `jxx_order`.`re_orders`       | INSERT INTO re_orders (location_id, shop_id, shop_name, sale_date, order_id, order_time, check_time, amount, fact_amount, ds_amount, direct_amount, addfee, chargeback, is_chargeback, hall_id, hall_name, table_id, table_name, people_num, discount_type, discount, palm_id, is_payoff, tipa, pay_way, shop_order_id, islive, order_type, renum, cashier_code, cashier_name, pos_id, sum_mfact, sum_dfact, sum_pay, dept_id, tenant_id) VALUES ('18', 50784, '龙子湖店', '2020-01-03', '5078400181626', '2020-01-03 11:19:00.0', '2020-01-03 11:19:13.0', 2, 2, 0, 0, 0, 0, 0, 2, 'B区', 111, '外卖1', 0, 0, 1, '刘桂珍', 1, 2, '金石支付:2;', '00181626', 0, 0, 0, '收银员', '001', '0', 2, 0, 2, 106, 1020)                                    |        2 | X,GAP     | RECORD    | idx_order_id |                 1 |                 1 |               1 |
+---------+-----+----------+-----------+-----------+-----------+---------------------+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-----------+-----------+--------------+-------------------+-------------------+-----------------+
7 rows in set, 4 warnings (0.03 sec)

4： 自动清理binlog

[mysqld]
...
#开启binary log
log-bin=mysql-bin

#日志超过3天自动过期
expire_logs_days = 3

手动清理：

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000020 | 1073742332 |


mysql> purge binary logs to 'mysql-bin.000060';
Query OK, 0 rows affected (1.54 sec)

 

wind520
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql5.7优化
ranran_fish的博客
10-22 363
目录修改mysql 配置文件 my.cnf 修改mysql 配置文件 my.cnf [root@instance ~]# cd /etc [root@instance etc]# vim my.cnf lower_case_table_names=1 //数据库忽略大小写（解决数据库因大小写敏感重新创建表的问题） #skip-locking #skip-name-resolve #back_...
MySQL 5.7优化不求人.pdf
07-21
技术文档分享。
MySQL5.7 配置优化
12-14
示例 登录mysql 首先要登录到mysql，用下述指令：mysql -h localhost -u root -p -P 3306 查询当前状态 输入show variables like “max_connections”; 修改配置文件 在文件/etc/my.cnf中设置Mysql 最大连接数 max_connections =900 重启mysql systemctl restart mysqld.service 查询状态 输入show variables like “max_connections”; 配置详解 max_connections 最大并发连接数。当MySQL的并发连
Mysql5.7 性能优化
技术改变世界
06-25 1714
文章目录简介优化查询分析查询语句使用索引查询索引失效的情况优化子查询优化数据库结构将字段很多的表分解成多个表增加中间表增加冗余字段优化插入记录的速度MyISAM引擎的表InnoDB引擎的表分析表、检查表和优化表分析表检查表优化表优化MySQL服务器优化服务器硬件优化MySQL的参数Mysql 5.7的新特性临时表性能优化服务器语句超时处理支持创建全局通用表空间参考链接 简介 MySQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如： 通过优化文件系统提高磁盘I/O的读
mysql5.7 优化 一
LOST_9的博客
04-29 1351
8.1 优化概述 数据库性能取决于数据库级别的几个因素，例如表、查询和配置设置。这些软件结构会导致硬件级别的 CPU 和 I/O 操作，您必须将其最小化并尽可能提高效率。在处理数据库性能时，首先要学习软件方面的高级规则和指南，并使用挂钟时间来衡量性能。当您成为专家时，您会更多地了解内部发生的事情，并开始测量诸如 CPU 周期和 I/O 操作之类的东西。 典型用户的目标是从他们现有的软件和硬件配置中获得最佳的数据库性能。高级用户寻找机会改进 MySQL 软件本身，或开发自己的存储引擎和硬件设备以扩展 My
MySQL 5.7数据库参数优化
篱笆女人和狗的博客
02-21 502
连接相关参数max_connections：允许客户端并发连接的最大数量，默认值是151，一般将该参数设置为500-2000 max_connect_errors：如果客户端尝试连接的错误数量超过这个参数设置的值，则服务器不再接受新的客户端连接。可以通过清空主机的缓存来解除服务器的这种阻止新连接的状态，通过FLUSH HOSTS或mysqladmin flush-hosts命令来清空缓存。这个参数的默认值是100，一般将该参数设置为100000。 interactive_timeout：Mysql关闭交互
mysql 5.7 中文文档
02-16
文档中详述了MySQL 5.7的主要特性，如性能优化、新的SQL语法、安全改进等。此外，还介绍了在5.7版本中新增、废弃或移除的服务器变量、状态变量和选项，这对于保持系统最新和兼容性至关重要。 文档还提供了获取MySQL...
MySQL 5.7优化不求人
12-30
MySQL 5.7的优化技术不仅仅是对数据库性能的提升，更是一种对数据库管理和维护模式的变革。它让数据库管理员能够更加高效和灵活地应对日益增长的数据处理需求，同时降低了数据库管理的复杂度，提升了系统稳定性和...
MySQL 5.7 优化
堂哥码财的博客
06-25 605
1、开启bin-log [mysqld] 下增加： server-id = 1 log-bin=/data/db/mysql/mysql-bin 重启MySQL，检查 show variables like '%log_bin%'; log-bin为ON即开启了bin-log 2、参数优化 #允许客户端并发连接的最大数量，默认值是151，一般将该参数设置为500-2000 max_connec...
mysql5.7 my.cnf配置文件优化
11-20
mysql5.7优化后的配置文件，mysql5.7主从架构配置环境，生产环境直接使用，推荐生产环境5.7版本使用最稳定版本5.7.21。
mysql5.7的优化
m0_46845770的博客
03-22 299
1.MySQL针对子查询select的优化 EXPLAIN SELECT * from user_workshop_role where role_id in (SELECT role_id from user_role where user_id = 417849709665865730); 看下直接用=的执行计划 EXPLAIN SELECT * from user_workshop_role where role_id = 432772745738485760 5.7之前用 in 时就
mysql5.7 配置优化
搞怪小新
03-27 313
以前参考过的，记录一下，如果用的是共用的docker-mysql镜像，可以将配置参考加进容器，当然就没有配置文件， 参考：度娘，https://blog.csdn.net/qq_34605594/article/details/70756842 [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/m...
Mysql优化
fen_dou_shao_nian的博客
03-17 516
主要内容 mysql优化 mysql框架 存储引擎 sql优化 mysql日志支持 执行计划 mysql中的索引 一、mysql优化 1.1 硬件级别优化 a、磁盘寻找：换硬盘，换固态 b、硬盘读写：换硬盘，换固态 c、CPU和缓存：提高cpu的缓存 1.2 数据库级别优化 导致数据库应用最高效的手段是：基本设计 a、表结构是否正确，字段类型，默认值，能not null的就not null，优先使用数值类型。 b、正确设置索引达到查询高效，需要考虑的是什么sql会使索引失效。 c、对
mysql5.7 优化 三
LOST_9的博客
04-29 823
8.8 了解查询执行计划 8.8.1 使用 EXPLAIN 优化查询 8.8.1 使用 EXPLAIN 优化查询 该EXPLAIN语句提供有关 MySQL 如何执行语句的信息： EXPLAIN与SELECT,DELETE,INSERT,REPLACE, 和UPDATE语句一起使用。 当EXPLAIN与可解释语句一起使用时，MySQL 显示来自优化器的有关语句执行计划的信息。也就是说，MySQL 解释了它将如何处理该语句，包括有关表如何连接以及以何种顺序连接的信息。有关使用EXPLAIN...
MySQL5.7 高可用高性能配置调优
最新发布
Andy12306的博客
08-03 837
【代码】MySQL5.7 高可用高性能配置调优。
mysql 5.7基本优化_mysql 5.7.24 优化小结（会继续完善）
weixin_39595008的博客
01-19 284
MySQL5.7 优化为什么要优化？1、系统的吞吐量瓶颈往往出现在数据库的访问速度上2、随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢3、数据是存放在磁盘上的，读写速度无法和内存相比4、数据库表设计者在建表的时候设计水平一般。如何优化？选择数据库引擎：如果没有特别的需求或者一般来说，使用默认的Innodb即可。MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。...
MySQL5.7优化测试方案
u010947098的专栏
07-26 527
MySQL5.7优化测试方案MySQL5.7优化测试方案系统相关信息:系统环境:硬件信息:系统参数调整目前磁盘使用情况系统磁盘IO测试使用脚本观测:运行结果脚本运行结果iostat观测结果(部分)iopp观测结果MySQL 优化Mysql插入数据优化mysql配置文件(修改后)插入:每秒平均插入数据条数is_norm_data_his表结构插入测试用sql语句50并发, 5220条/s100并发,...
mysql5.7优化配置
05-30
MySQL 5.7 优化配置可以从以下几个方面入手： 1. 内存配置优化 MySQL 5.7 的内存配置主要涉及以下几个参数： - innodb_buffer_pool_size：InnoDB 存储引擎缓存池大小，建议设置为物理内存的 50%-80%。 - innodb_...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值