mysql flushlogs_MySQL5.7-学习笔记(kylix)-2019.3.25-flushlogs&numbertype

2019.3.25

Flush log

•

FLUSH LOGS

Closes

and reopens any log file to which the server is writing. If binary

logging is enabled, the sequence number of the binary log file is

incremented by one relative to the previous file. If relay logging

is enabled, the sequence number of the relay log file is

incremented by one relative to the previous file.

•

FLUSH ERROR LOGS

Closes

and reopens any error log file to which the server is writing.

•

FLUSH GENERAL LOGS

Closes

and reopens any general query log file to which the server is

writing.

•

FLUSH SLOW LOGS(not )

Closes

and reopens any slow query log file to which the server is

writing.

2019.3.26

MySQL小数类型

MySQL中存在float,double等非标准数据类型，也有decimal这种标准数据类型。

其区别在于，float，double等非标准类型，在DB中保存的是近似值，而Decimal则以字符串的形式保存数值。

float,double超过7/15位精度，就会四舍五入，那么就是不准确的，不精确。

通常，Float值适用于科学计算，但不应用于财务/货币价值。

对于面向业务的数学，始终使用decimal。

MySQL意向锁

InnoBD使用意向锁来实现多个粒度级别的锁定。

意向锁是表级锁。

意向锁分为意向共享锁(IS锁)和意向排它锁(IX锁)。

IS锁表示当前事务意图在表中的行上设置共享锁。

IX锁表示当前事务意图在表中的行上设置排它锁。

MySQL

5.7中如何定位DDL被阻塞的问题

对于DDL被阻塞问题的定位，我们主要是基于MySQL

5.7新引入的performance_schema.metadata_locks表。

对于MySQL

5.6，

网上有kill空闲(Command为Sleep)连接的说法，其实也不无道理，但这样做就太简单粗暴了，难免会误杀。

SELECT

processlist_id,

sql_text

FROM

(

SELECT

c.processlist_id,

substring_index( sql_text, "transaction_begin;",-1 )

sql_text

FROM

information_schema.innodb_trx a,

(

SELECT

thread_id,

group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN

"transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR

";" ) AS sql_text

FROM

performance_schema.events_statements_history

GROUP BY

thread_id

) b,

performance_schema.threads c

WHERE

a.trx_mysql_thread_id = c.processlist_id

AND b.thread_id = c.thread_id

)

t

WHERE

sql_text

LIKE '%t1%';

+----------------+---------------------------------------------------------------------------------------------------------+

| processlist_id |

sql_text |

+----------------+---------------------------------------------------------------------------------------------------------+

|

2 | delete from slowtech.t1 where id=2;select * from

slowtech.t1;update slowtech.t1 set name='c' where id=1 |

+----------------+---------------------------------------------------------------------------------------------------------+

1 row in set (0.01

sec)

从输出来看，确实也达到了预期效果。

开启 performance_schema.metadata_locks

performance_schema.metadata_locks来查看对应metadata

lock的情况，开启方式如下：

UPDATE performance_schema.setup_consumers

SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';

UPDATE performance_schema.setup_instruments

SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl'