mysql数据库管理-参数管理

1 查询参数

mysql> show variables like 'sort%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.04 sec)

 

方式2 

mysql> select @@sort_buffer_size

2 设置参数

mysql> set @@sort_buffer_size =300000;   --会话生效。
Query OK, 0 rows affected (0.01 sec)

mysql> select @@sort_buffer_size
    -> ;
+--------------------+
| @@sort_buffer_size |
+--------------------+
|             300000 |
+--------------------+
1 row in set (0.03 sec)

mysql> use monidb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> ls
    -> 
    -> 
    -> ;
ERROR 1064 (42000): 
mysql> 
mysql> 
mysql> select @@sort_buffer_size
    -> ;
+--------------------+
| @@sort_buffer_size |
+--------------------+
|             262144 |
+--------------------+
1 row in set (0.00 sec)

mysql> 

sql_mode

SET autocommit=1
Set sql_mode='NO_ENGINE_SUBSITUTION,STRICT_TRANS_TABLES'

 

STRICT_TRANS_TABLES 设置它，表示启用严格模式。 
注意 STRICT_TRANS_TABLES 不是几种策略的组合，单独指 INSERT、UPDATE出现少值或无效值该如何处理：

1. 前面提到的把 '' 传给int，严格模式下非法，若启用非严格模式则变成0，产生一个warning

2. Out Of Range，变成插入最大边界值

3. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition

 

max_connections

MySQL的最大连接数，增加该值增加mysqld要求的文件描述符的数量。连接请求量大时，建议调高此值调的越高，内存开销越大。

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|              1500 |
+-------------------+
1 row in set (0.00 sec)

 

每个连接的缓存参数

① Sort_buffer_size

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)，可改为16777208 (16M)。

② Join_buffer_size

联合查询操作所能使用的缓冲区大小。

record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*100。

③ table_open_cache

表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。

mysql> show global status like 'open%tables%';

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

| Variable_name| Value |

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

| Open_tables| 1024 |

| Opened_tables| 1465 |

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

mysql>show variables like 'table_open_cache';

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

|Variable_name|Value|

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

|table_open_cache|1024|

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

④ tmp_table_size

临时表大小。通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。

mysql>showglobal statuslike' created_tmp%';

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

|Variable_name|Value |

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

|Created_tmp_disk_tables|21197|

| Created_tmp_files| 58|

| Created_tmp_tables| 1771587 |

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

mysql> show variables like 'tmp_table_size';

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

| Variable_name| Value |

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

| tmp_table_size| 16777216 |

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

⑤ thread_cache_size

可以复用的保存在缓冲区中的线程的数量。当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。

mysql>show global status like 'Thread%';

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

|Variable_name|Value|

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

|Threads_cached|31|

|Threads_connected|239|

|Threads_created|2914|

|Threads_running|4|

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

mysql>show variables like 'thread_cache_size';

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

|Variable_name|Value|

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

|thread_cache_size|32|

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

 

4）配置InnoDB的参数

① Innodb_buffer_pool_size

InnoDB使用该参数指定大小的内存来缓冲数据和索引，其对InnoDB的重要性等于key_buffer_size对MyISAM的重要性。

② Innodb_log_buffer_size

Innodb_log缓存大小，一般为1-8M，默认为1M，对于较大的事务，可以增大缓存大小。可设置为4M或8M。

innodb_lock_wait_timeout  

等待锁超时回滚事务：

直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。