MySQL优化

mysql优化

个人原创，允许转载，请注明出处，作者，否则追究法律责任。
mysql连接数
QPS 每秒查询数

PV page view

峰值QPS=峰值pv数 * 连接数据库的pv占总pv的百分比 * 每个pv的平均请求数
185 * 10% * 20 = 370

1 thread reuse 线程数量
show variables like "%thread%";

thread_cache_size thread cache 中存放的最大连接线程数。（根据内存大小来调）
1G --- 8
2G --- 16
3G --- 32
>3G --- 64

修改值的方法：
临时生效：
set global thread_cache_size=16;

永久生效的方法：
thread_cache_size=16写入my.cnf中的[mysqld]下面

2 connection limit 连接数限制
set global max_connections=300;
补：32G内存的机器可以设置成2000

3 check memory-cache 查询缓存，优化查询速度。
MySQL> show variable like "%cache_size%"
query_cache_size 0 ----- 默认为0

临时设置：
MySQL> set global query_cache_siz=10000000 (10m)

永久生效：
vim my.cnf
在[mysqld]下面添加
query_cache_size=128M （不一定 越大越好，一旦开启，就会每次查询都走缓存，太大反而慢）

thread_cache_size=16
max_connections=1000
query_cache_size=128M

SQL Interface sql接口，用来接受sql请求命令。
PARSER： mysql的解释器，解释sql命令，然后将命令交给优化器。
Optimizer： mysql的优化器，负责优化sql的执行，选择最优的方式进行数据检索。
caches and buffers: mysql自己的缓存缓冲，不同的数据库引擎使用不同的缓存。

innodb 缓冲
innodb_buffer_pool_size innodb缓冲池大小。 这个值越大越好。
如果mysql专用服务器，操作系统内存的70%-80%。
set global innodb_buffer_pool_size=800000000;
ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable
报错，只读变量，只能在配置文件中修改。

myisam 缓冲
key_buffer_size myisam的缓冲池大小。可以通过动态的方式进行修改。
set global key_buffer_size=134217728;

慢速日志：
slow log

开启方法：
vim my.cnf (在mysqld下面添加两行)
log_slow_queries=/tmp/slow.log （定义慢日志的位置）
long_query_time=4 （慢速日志的记录的时间，当查询时间超过4秒时记录到这里）

table_open_cache 用于限制打开的最大的缓存表的数量。（缓存多少张表）
mysql> show variables like "%table%";
里面有个: table_open_cache 64 (默认缓存64张表)
设置：set global table_open_cache=128

清空缓存的表的数量：
flush tables;

临时表：只有用户自己才能看到。作用：提高速度。放在内存中。
tmp_table_size 16777216 默认16M,不超过这个值，临时表放在内存中，如果超过，会放入硬盘。

mysql>show variables like "%timeout%" （单位都是秒）

connect_timeout 10 (默认超时时间，超过10秒连不上就自动断开)
wait_timeout 默认28800秒,8小时，超过则断开连接。
net_read_timeout 网络读超时时间
net_write_timeout 网络写超时时间

innodb的日志级别：
innodb_flush_log_at_trx_commit ： innodb的日志 中的缓存数据是如何写入硬盘的。
0 最不安全，每隔一秒将数据写入硬盘，断电或者数据库损坏数据都会丢失。效率最高。
1 最安全，数据实时写入硬盘。
2 先写入系统的缓存，在写入磁盘，服务器断电数据丢失。一般使用这种。

mysqlreport

./mysqlreport --user root --password 123