?
我为提高mysql性能做出调整的配置项(二)
thread implementation. However, if your server sees hundreds of connections per second you should
normally set thread_cache_size high enough so that most new connections use cached threads. By
examining the difference between the Connections and Threads_created status variables, you can
see how efficient the thread cache is. For details, see Section 5.1.6, “Server Status Variables”.
The default value is based on the following formula, capped to a limit of 100:
8 + (max_connections / 100)
This variable has no effect for the embedded server (libmysqld) and as of MySQL 5.7.2 is no longer
visible within the embedded server.
?
query_cache_size= 8M：分配给查询缓存的内存大小。要配合query_cache_type使用，默认是不开启的。只从该参数的表面介绍来看，似乎值设置的越大，带来的效果会更好，但是请注意，查询缓存的工作原理，一个select语句过来后，数据库将查询结果缓存到cache中，等同样的select查询过来后，如果这段时间内该查询结果没有发生变化时，数据库将cache中将缓存结果返回，那么假如查询的相关数据表增删改特别多的话，数据表变更的这段时间内，要将cache失效，然后再更新数据，对于增删改来说，花费的时间就很多了，所以要有所权衡，这个参数我会在将来进行相关测试数据整理。
?
By default, the query cache is disabled. This is achieved using a default value of 1M, with a default for query_cache_type of 0. (To reduce overhead significantly if you set the size to 0, you should also start the server with query_cache_type=0. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0. See Section 8.9.3.3, “Query Cache Configuration”, for more information. The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value of query_cache_size too small, a warning will occur, as described in Section 8.9.3.3, “Query Cache Configuration”.??
query_cache_type = 1：1表示缓存所有查询，2表示缓存select sql_cache的查询，看如下内容。
?
0 or OFF Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0. 1 or ON Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE. 2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE.
?
?
set global max_connections = 500：注意这个是通过命令行设置最大连接数，不是配置在配置文件的，因为我在配置文件里面尝试修改，重启mysql服务后并没有起效，通过该参数设置以后，重启服务后，依然没有起效，如果有朋友知道这个原因的话，请告知。如果说你的项目使用的是spring的连接池的时候，我认为spring个connection就对应的这个连接。根据你项目的需求而定。
?
log-bin=mysql-bin：开启二进制日志，并且日志的名称会按照mysql-bin***之类的依次生成。但是我一直有一些疑问，就是这个二进制日志是否是innodb的事务日志，是传统所说的事务日志吗？
?
binlog_format=mixed：二进制日志的格式为mixed，该中模式是statement和row模式的结合体里面讲解了我们项目在二进制日志设置上遇到的问题和解决办法，如果遇到类似的问题后，会有所帮助。
?
In MySQL 5.7, the default format is STATEMENT. You must have the SUPER privilege to set either the global or session binlog_format value. The rules governing when changes to this variable take effect and how long the effect lasts are the same as for other MySQL server system variables. See Section 13.7.4, “SET Syntax”, for more information. When MIXED is specified, statement-based replication is used, except for cases where only row-based replication is guaranteed to lead to proper results. For example, this happens when statements contain user-defined functions (UDF) or the UUID() function. An exception to this rule is that MIXED always uses statement-based replication for stored functions and triggers.
?
?
innodb_buffer_pool_size