mysql常用命令及配置说明

常用命令和设置

1、general_log 

开启 general log 将所有到达MySQL Server的SQL语句记录下来。

2、max_execution_time

这个参数是控制select的时间,能有效控制在主库的慢查询情况

3、innodb_undo_tablespaces

用于设定创建的undo表空间的个数，在mysql_install_db时初始化后，就再也不能被改动了；默认值为0，表示不独立设置undo的tablespace，默认记录到ibdata中；否则，则在undo目录下创建这么多个undo文件，例如假定设置该值为4，那么就会创建命名为undo001~undo004的undo tablespace文件，每个文件的默认大小为10M。修改该值会导致Innodb无法完成初始化，

4、innodb_undo_directory

如果想转移undo文件的位置，只需要修改下该配置，并将undo文件拷贝过去就可以了。

5、innodb_undo_logs

定义在一个事务中innodb使用的系统表空间中回滚段的个数

6、innodb_change_buffer_max_size

设置change buffer的大小，在buffer pool中的占比

7、innodb_io_capacity

设置innodb引擎刷盘的能力，如果设置很小的话，会导致脏页刷盘很慢，导致更新堵塞

8、innodb_flush_neighbors

脏页刷盘时是否也带上相邻也是脏页的数据一起，0或1，如果是ssd盘可以不用打开，提升必要的刷盘操作，提升sql响应时间。

9、innodb_file_per_table

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；

这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

10、sort_buffer_size

每个线程排序使用的内存大小，如果不够，会使用辅助外存，影响性能。

11、OPTIMIZER_TRACE

是MySQL5.6添加的新功能，可以看到大量的内部查询计划产生的信息,针对当前线程有效, 先打开设置，然后执行一次sql,最后查看`information_schema`.`OPTIMIZER_TRACE`的内容.

查看OPTIMIZER_TRACE方法：

1.set optimizer_trace='enabled=on';    --- 开启trace

2.set optimizer_trace_max_mem_size=1000000;    --- 设置trace大小

3.set end_markers_in_json=on;    --- 增加trace中注释

4.select * from information_schema.optimizer_trace\G;

12、max_length_for_sort_data

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

binlog_format=row&binlog_row_image=FULL
如果是 binlog_format=row 并且 binlog_row_image=FULL 的时候，由于 MySQL 需要在 binlog 里面记录所有的字段，所以在读数据的时候就会把所有数据都读出来了。

13、tmp_table_size

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

14、show processlist

查看数据库在执行的命令

15、查询sys.schema_table_lock_waits可以查询到等待mdl锁的进程

16、set long_query_time=0设置慢查询时间阈值为0

 17、max_connections

     max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。

    max_connections 的计算，不是看谁在 running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通 过 kill connection 主动踢掉。这个行为跟事先设置 wait_timeout 的效果是一样的。设置 wait_timeout 参数表示的是，一个线程空 闲 wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。

 18、sync_binlog

1. c=0 的时候，表示每次提交事务都只 write，不 fsync；
2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
3. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

19、innodb_flush_log_at_trx_commit

1. 设置为 0 的时候，表示每次事务提交时都只是把 redo og 留在 redo og buffer 中 ;
2. 设置为 1 的时候，表示每次事务提交时都将 redo og 直接持久化到磁盘；
3. 设置为 2 的时候，表示每次事务提交时都只是把 redo og 写到 page cache。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

20、binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count

       binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;

      binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

21、show slave status

展示slave和master直接数据同步延迟的时间

22、slave_parallel_workers

设置slave接收主的binlog日志后执行binlog的工作线程数

23、set sql_log_bin=on

开启binlog

24、innodb_thread_concurrency

设置并发线程数，超过了会等待。等待锁的线程不在统计范围内。

25、innodb_thread_sleep_delay

设置线程数超后休眠等待的时间

26、查看performance_schema.file_summary_by_event_name

可以查看mysql的磁盘io情况

27、show engine innodb status

查看innodb的运行情况

28、sql_safe_updates

On时表示会对sql进行安全监测，如果delete不带条件会报错

29、net_buffer_length

设置mysqlserver层边读边发送数据的buffer大小

30、innodb_buffer_pool_size

InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。

31、innodb_old_blocks_time

设置innodb引擎buffer pool中使用lru算法淘汰最久使用数据页时配置的old区的数据多久间隔内被重复访问可以放置到young区。默认一秒。Young区和old区大小比时5:3.

32、join_buffer_size

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。

33、大表join的优化方法有哪些？

1）先从大表查询到临时表，在去join临时表

2) Multi-Range Read

开启mrr的方式set optimizer_switch="mrr_cost_based=off"。

转去聚簇索引随机读数据转成顺序读，先从索引读取部分数据获取id，放入read_rnd_buffer中排序，然后在按id顺序去聚簇索引读取数据。

3) Batched Key Access

开启bka方式optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

从驱动表取出部分数据放入join buffer中，在一次性把数据拿join buffer数据去被驱动表获取数据，解决了一条条从驱动表获取数据去被驱动表查询的性能问题。

 

34、read_rnd_buffer_size

设置read_rnd_buffer_size的大小

35、innodb_autoinc_lock_mode

 设置自增id的加锁模式，默认1.

1. 这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁；
2. 这个参数的值被设置为 1 时：

普通 insert 语句，自增锁在申请之后就马上释放；

类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

1. 这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁。

36、flush privileges

刷新权限内存数据，让数据表里的权限和内存中的一致。