mysql5.7优化

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)

 

发布了233 篇原创文章 · 获赞 147 · 访问量 162万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览