mysql优化详解

  1. 系统层面(基本不用动，看了下，买的云服务器基本都已经优化过了)

内核相关参数(/etc/sysctl.conf)

网络相关

  • net.core.somaxconn = 65535
  • net.core.netdev_max_backlog = 65535
  • net.ipv4.tcp_max_syn_backlog = 65535
  • net.ipv4.tcp_fin_timeout = 10
  • net.ipv4.tcp_tw_resue = 1
  • net.ipv4.tcp_tw_recycle = 1

缓冲区

  • net.core.wmem_default = 87380
  • net.core.wmem_max = 16777216
  • net.core.rmem_default = 87380
  • net.core.rmem_max = 16777216

失效链接

  • net.ipv4.tcp_keepalive_time = 120
  • net.ipv4.tcp_keepalive_intvl = 30
  • net.ipv4.tcp_keepalive_probes = 3

内存

  • kernel.shmmax = 4294967295

linux内核参数中最重要的参数之一,用于定义单个共享内存段的最大值.

  • vm.swappiness = 0

交换分区

增加资源限制(/etc/security/limit.conf)

* soft nofile 65535
* hard nofile 65535

加到limi.conf文件末尾

*     表示对所有用户有效
soft  指的是当前系统生效的设置
hard  表明系统中所能设定的最大值
nofile表示所限制的资源是打开文件的最大数目
65535 限制的数量

磁盘调度策略(/sys/block/devname/queue/scheduler)

cat /sys/block/sda/queue/scheduler

noop anticipatory deadline [cfq]

  1. mysql优化

    1. 优化思路(数据来自localhost,centos7,2G RAM,1cpu)

      1. 连接 Connections
      mysql> show variables like 'max_connections';
+-----------------+-------+
| variable_name　 | value |
+-----------------+-------+
| max_connections | 151　 |
+-----------------+-------+ 
查一下服务器响应的最大连接数
mysql> show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 14    |
+----------------------+-------+
可见我使用的连接数没有达到最大连接数,比较理想的是

max_used_connections / max_connections * 100% ≈ 85% 
最大连接数占上限连接数的85％左右，如果发现比例在10%以下，mysql服务器连接数上限设置的过高了。
      1. 线程 Thread
      mysql> show global status like 'thread%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 0     |
| Threadpool_threads      | 0     |
| Threads_cached          | 0     |
| Threads_connected       | 11    |
| Threads_created         | 1818  |
| Threads_running         | 1     |
+-------------------------+-------+
如果我们在mysql服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。
threads_created表示创建过的线程数，如果发现threads_created值过大的话，表明mysql服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，

查询服务器 thread_cache_size 配置：
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 0     |
+-------------------+-------+
说明我需要配置thread_cahce_size的值
      1. 缓存cache
        1. 文件打开数
        mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 23    |
+---------------+-------+
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
比较合适的设置：open_files / open_files_limit * 100% <= 75%
        1. 数据表
        # 打开数 open_tables

mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 47    |
| Opened_tables | 28    |
+---------------+-------+
open_tables: 打开表的数量
opened_tables: 打开过的表数量

如果 opened_tables 数量过大，说明配置中 table_open_cache值可能太小，我们查询一下服务器table_open_cache值:
mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 400   |
+------------------+-------+
比较合适的值为： 
open_tables / opened_tables * 100% >= 85% 
open_tables / table_cache * 100% <= 95%

        # 临时表 tmp_table

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 151   |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1260  |
+-------------------------+-------+
每次创建临时表，created_tmp_tables 增加，如果是在磁盘上创建临时表，created_tmp_disk_tables也增加,created_tmp_files表示mysql服务创建的临时文件文件数，比较理想的配置是： 
created_tmp_disk_tables / created_tmp_tables * 100% <= 25% 

比如上面的服务器 created_tmp_disk_tables / created_tmp_tables * 100% ＝ 11.98%，应该相当好了。我们再看一下mysql服务器对临时表的配置： 
mysql> show variables where variable_name in ('tmp_table_size','max_heap_table_size');
只有 256mb 以下的临时表才能全部放内存，超过的就会用到硬盘临时表。

        表级锁

mysql> show global status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 28141 |
| Table_locks_waited    | 0     |
+-----------------------+-------+
table_locks_immediate 表示立即释放表锁数， 
table_locks_waited 表示需要等待的表锁数， 

如果 table_locks_immediate / table_locks_waited > 5000，最好采用innodb引擎，因为innodb是行锁而myisam是表锁，对于高并发写入的应用innodb效果会好些。 

        表扫描

mysql> show global status like 'handler_read%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Handler_read_first       | 1078     |
| Handler_read_key         | 65237    |
| Handler_read_last        | 0        |
| Handler_read_next        | 11517    |
| Handler_read_prev        | 0        |
| Handler_read_rnd         | 5257     |
| Handler_read_rnd_deleted | 200      |
| Handler_read_rnd_next    | 10240972 |
+--------------------------+----------+

服务器完成的查询请求次数:
mysql> show global status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 24328 |
+---------------+-------+
计算表扫描率： 

表扫描率 ＝ handler_read_rnd_next / com_select 

如果表扫描率超过 4000，说明进行了太多表扫描，很有可能索引没有建好，增加 read_buffer_size 值会有一些好处，但最好不要超过8mb。

        key_buffer_size

key_buffer_size是对myisam表性能影响最大的一个参数，下面一台以myisam为主要存储引擎服务器的配置:
mysql> show variables like 'key_buffer_size';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+
分配了 128mb 内存给 key_buffer_size ，我们再看一下 key_buffer_size 的使用情况:
mysql> show global status like 'key_read%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 56    |
| Key_reads         | 2     |
+-------------------+-------+
一共有 56个 索引读取请求，有 2个 请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率： 
key_cache_miss_rate ＝ key_reads / key_read_requests * 100%

比如上面的数据，key_cache_miss_rate为3.6%，100个索引读取请求才有3个直接读硬盘，已经很bt了，key_cache_miss_rate在0.1%以下都很好（每1000个请求有一个直接读硬盘），如果key_cache_miss_rate在0.01%以下的话，key_buffer_size分配的过多，可以适当减少。 

【注意】key_read_buffer 默认值为 8M 。在专有的数据库服务器上，该值可设置为 RAM * 1/4

mysql服务器还提供了key_blocks_*参数：
mysql> show global status like 'key_blocks_u%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Key_blocks_unused | 107169 |
| Key_blocks_used   | 2      |
+-------------------+--------+
key_blocks_unused 表示未使用的缓存簇(blocks)数
key_blocks_used 表示曾经用到的最大的blocks数

我这台缓存完全没用到，因为是本机，数据量极少

假如所有的缓存都用到了，要么增加 key_buffer_size，要么就是过渡索引了，把缓存占满了。比较理想的设置： 
key_blocks_used / (key_blocks_unused + key_blocks_used) * 100% ≈ 80%

        排序使用情况 sort_buffer

mysql> show global status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 9486  |
| Sort_scan         | 1449  |
+-------------------+-------+
sort_merge_passes 包括两步。mysql 首先会尝试在内存中做排序，使用的内存大小由系统变量 sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，mysql 就会把每次在内存中排序的结果存到临时文件中，等 mysql 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 sort_merge_passes。实际上，mysql 会用另一个临时文件来存再次排序的结果，所以通常会看到 sort_merge_passes 增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 sort_buffer_size 会减少 sort_merge_passes 和 创建临时文件的次数。但盲目的增加 sort_buffer_size 并不一定能提高速度，见 搜索how fast can you sort data with mysql?（需要翻墙） 

另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的好处。

        查询缓存

mysql> show global status like 'qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
mysql 查询缓存变量解释： 

qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。flush query cache会对缓存中的碎片进行整理，从而得到一个空闲块。 
qcache_free_memory：缓存中的空闲内存。 
qcache_hits：每次查询在缓存中命中时就增大 
qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是命中比率。 
qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况） 
qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 select 语句或者用了now()之类的函数。 
qcache_queries_in_cache：当前缓存的查询（和响应）的数量。 
qcache_total_blocks：缓存中块的数量。 

我们再查询一下服务器关于query_cache的配置： 

mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_strip_comments   | OFF     |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
各字段的解释： 

query_cache_limit：超过此大小的查询将不缓存 
query_cache_min_res_unit：缓存块的最小大小 
query_cache_size：查询缓存大小 
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate：当有其他客户端正在对myisam表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4kb，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。 

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100% 

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (qcache_hits - qcache_inserts) / qcache_hits * 100% 
我本机没有设置缓存
      2. 其他
      慢查询

在mysqld下加入慢查询配置
log-slow-queries = /var/lib/mysql/mysql-slow.log
long_query_time = 2
日志文件一定要有写权限,配置需要重启数据库

mysql> show variables like '%slow%';
+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name       | Value                                                                                                        |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter     | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries    | ON                                                                                                           |
| log_slow_rate_limit | 1                                                                                                            |
| log_slow_verbosity  |                                                                                                              |
| slow_launch_time    | 2                                                                                                            |
| slow_query_log      | ON                                                                                                           |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log                                                                                |
+---------------------+--------------------------------------------------------------------------------------------------------------+

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 0     |
+---------------------+-------+
可以查看日志，分析有问题的sql语句

    2. 基本配置优化(针对innodb存储引擎)(必须要优化的，默认配置有问题)

    # innodb缓冲池，保证数据是从内存而不是硬盘读取，越大越好，一般设置成内存的80%
innodb_buffer_pool_size = 100000M

# 此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间,一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6，你可以一开始就把它设置成4G
innodb_log_file_size = 4096M

# 连接数大小，如果程序出现'Too many connections'的错误就需要调整这个值,max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。
max_connections = 3000

# 0为off，1为on，这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里,或者为每张表的数据单独放在一个.ibd文件,表多的时候关闭，要不然文件太多，表少开启
innodb_file_per_table = 1

# 默认值为1，表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致不太可靠（reliable）因为提交的事务仅仅每秒才flush一次到redo日志，但对于一些场景是可以接受的，比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了，但在系统崩溃时可能丢失一些数据：只适用于备份节点。
innodb_flush_log_at_trx_commit = 1

# 数据和日志写入硬盘的方式，默认值就行，如果你有硬件RAID控制器，并且其独立缓存采用write-back机制，并有着电池断电保护，那么应该设置配置为O_DIRECT
innodb_flush_method = fdatasync

# 这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果它不是0，增加innodb_log_buffer_size
innodb_log_buffer_size = 1M

# 设置query_cache_size = 0（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。
query_cache_size = 0

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
skip-name-resolve

    1. 配置详解

    # 在MYSQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。
back_log = 600

# 设置每个主机的连接请求异常中断的最大次数，当超过该次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。
max_connect_errors = 6000

# 指示表调整缓冲区大小。table_cache 参数设置表高速缓存的数目。每个连接进来，都会至少打开一个表缓存。因此， table_cache 的大小应与 max_connections 的设置有关。例如，对于 200 个#并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是应用可以执行的查询#的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。
table_open_cache = 614

# 设置在网络传输中一次消息传输量的最大值。系统默认值 为1MB，最大值是1GB，必须设置1024的倍数。
max_allowed_packet = 32M 

# Sort_Buffer_Size 是一个connection级参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
# Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。例如：500个连接将会消耗 500*sort_buffer_size(8M)=4G内存
# Sort_Buffer_Size 超过2KB的时候，就会使用mmap() 而不是 malloc() 来进行内存分配，导致效率降低。
# explain select*from table where order limit；出现filesort
# 属重点优化参数
sort_buffer_size = 2M

# 用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
join_buffer_size = 2M 

# 服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
thread_cache_size = 300

# 设置thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那么thread_concurrency的应该为4; 2个双核的cpu, thread_concurrency的值应为8
# 属重点优化参数
thread_concurrency = 8

# 对于使用MySQL的用户，对于这个变量大家一定不会陌生。前几年的MyISAM引擎优化中，这个参数也是一个重要的优化参数。但随着发展，这个参数也爆露出来一些问题。机器的内存越来越大，人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一下 query_cache_size的工作原理：一个SELECT查询在DB中工作后，DB会把该语句缓存下来，当同样的一个SQL再次来到DB里调用时，DB在该表没发生变化的情况下把结果从缓存中返回给Client。这里有一个关建点，就是DB在利用Query_cache工作时，要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时，Query_cache里的数据又怎么处理呢？首先要把Query_cache和该表相关的语句全部置为失效，然后在写入更新。那么如果Query_cache非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建议把该功能禁掉。
# 重点优化参数(主库 增删改-MyISAM)
query_cache_size = 64M

# 指定单个查询能够使用的缓冲区大小，缺省为1M
query_cache_limit = 4M

# 默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费
# 查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
# 如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
# 查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
# 查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
# 查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
query_cache_min_res_unit = 2k

# 设置MYSQL每个线程的堆栈大小，默认值足够大，可满足普通操作。可设置范围为128K至4GB，默认为192KB。
thread_stack = 192K  

# 设定默认的事务隔离级别.可用的级别如下:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED-读未提交2.READ COMMITTE-读已提交3.REPEATABLE READ -可重复读4.SERIALIZABLE -串行
# 重要概念
transaction_isolation = READ-COMMITTED

# tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，如果你做很多高级 GROUP BY 查询，增加 tmp_table_size 值。如果超过该值，则会将临时表写入磁盘。
tmp_table_size = 256M

# 批定用于索引的缓冲区大小，增加它可以得到更好的索引处理性能，对于内存在4GB左右的服务器来说，该参数可设置为256MB或384MB。
key_buffer_size = 256M

# MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
read_buffer_size = 1M

# MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
read_rnd_buffer_size = 16M

# 批量插入数据缓存大小，可以有效提高插入效率，默认为8M
bulk_insert_buffer_size = 64M

# MyISAM表发生变化时重新排序所需的缓冲
myisam_sort_buffer_size = 128M

# 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.
# 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1

# 自动检查和修复没有适当关闭的 MyISAM 表
myisam_recover

# 这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小，类似于Oracle的library cache。这不是一个强制参数，可以被突破。   
innodb_additional_mem_pool_size = 16M

# 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70~80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了
innodb_buffer_pool_size = 2048M

# 表空间文件 重要数据
innodb_data_file_path = ibdata1:1024M:autoextend

# 文件IO的线程数，一般为 4，但是在 Windows 下，可以设置得较大。
innodb_file_io_threads = 4   

# 服务器有几个逻辑CPU就设置为几，建议用默认设置，一般为8.
innodb_thread_concurrency = 8   

# 如果将此参数设置为1，将在每次提交事务后将日志写入磁盘。为提供性能，可以设置为0或2，但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写入日志文件，而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志，但日志文件每次刷新到磁盘一次。
innodb_flush_log_at_trx_commit = 2   

# 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据.MySQL开发人员建议设置为1－8M之间
innodb_log_buffer_size = 16M  

# 此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间
innodb_log_file_size = 128M   

# 为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3M
innodb_log_files_in_group = 3   

# Buffer_Pool中Dirty_Page所占的数量，直接影响InnoDB的关闭时间。参数innodb_max_dirty_pages_pct 可以直接控制了Dirty_Page在Buffer_Pool中所占的比率，而且幸运的是innodb_max_dirty_pages_pct是可以动态改变的。所以，在关闭InnoDB之前先将innodb_max_dirty_pages_pct调小，强制数据块Flush一段时间，则能够大大缩短 MySQL关闭的时间。
innodb_max_dirty_pages_pct = 90   

# InnoDB 有其内置的死锁检测机制，能导致未完成的事务回滚。但是，如果结合InnoDB使用MyISAM的lock tables 语句或第三方事务引擎,则InnoDB无法识别死锁。为消除这种可能性，可以将innodb_lock_wait_timeout设置为一个整数值，指示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)
innodb_lock_wait_timeout = 120   

# 指定一个请求的最大连接时间，对于4GB左右的内存服务器来说，可以将其设置为5-10。
wait_timeout = 10

  2. 表结构优化

    1. 数据类型选择
      数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量，可以在很大程度上提高数据库操作的性能。我们无法改变数据库中需要存储的数据，但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多，数据量较大的场景，因为精细化的数据类型设置可能带来维护成本的提高，过度优化也可能会带来其他的问题：
      1. 数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。
      2. 字符类型：非万不得已不要使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。
      3. 时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。
      4. ENUM & SET：对于状态字段，可以尝试使用 ENUM 来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝试使用SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。
      5. LOB类型：强烈反对在数据库中存放 LOB 类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的工具做他擅长的事情，才能将其发挥到极致。在数据库中存储 LOB 数据就像让一个多年前在学校学过一点Java的营销专业人员来写 Java 代码一样。
    2. 字符编码
      字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。
      1. 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。
      2. 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。
      3. MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。
    3. 适当拆分
      有些时候，我们可能会希望将一个完整的对象对应于一张数据库表，这对于应用程序开发来说是很有好的，但是有些时候可能会在性能上带来较大的问题。
      当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。
      上面几点的优化都是为了减少每条记录的存储空间大小，让每个数据库中能够存储更多的记录条数，以达到减少 IO 操作次数，提高缓存命中率。下面这个优化建议可能很多开发人员都会觉得不太理解，因为这是典型的反范式设计，而且也和上面的几点优化建议的目标相违背。
    4. 适度冗余
      为什么我们要冗余？这不是增加了每条数据的大小，减少了每个数据块可存放记录条数吗？
      确实，这样做是会增大每条记录的大小，降低每条记录中可存放数据的条数，但是在有些场景下我们仍然还是不得不这样做：
      被频繁引用且只能通过 Join 2张（或者更多）大表的方式才能得到的独立小字段
      这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。
    5. 尽量使用 NOT NULL
      NULL 类型比较特殊，SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。
      很多人觉得 NULL 会节省一些空间，所以尽量让NULL来达到节省IO的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL，也是一个很好的表结构设计优化习惯。

  3. 慢查询优化

    1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
    2. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描
    3. 应尽量避免在 where 子句中对字段 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
    select id from t where num is null;
# 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num = 0;
    1. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
    select id from t where num = 10 or num = 20;
# 可以这样查询:
select id from t where num = 10
union all
select id from t where num = 20;
    1. 下面的查询也将导致全表扫描：
    select id from t where name like '%abc%';
# 若要提高效率，可以考虑全文检索
    1. in 和 not in 也要慎用，否则会导致全表扫描，如：
    select id from t where num in(1,2,3);
# 对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3;
    1. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时，它必须在编译时进行选择，然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如以下语句将进行全表扫描：
    select id from t where num=@num;
# 可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num;
    1. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
    select id from t where num/2=100;
应改为：
select id from t where num=100*2;
    1. 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
    select id from t where substring(name,1,3)='abc'; 查询name以abc开头的id
select id from t where datediff(dat,createdate,'2005-11-30')=0 查询'2005-11-30'生成的id应改为：
select id from t where name like'abc%';
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1';
    1. 不要在 where 子句中的"="左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。
    2. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
    3. 不要写一些没有意义的查询，如需要生成一个空表结构：
    select col1,col2 into #t from t where 1=0;
# 这类代码不会返回任何结果集，但是会消耗系统资源，应改成这样：
create table #t(...);
    1. 很多时候用 exists 代替 in 是一个好的选择：
    select num from a where num in(select num from b);
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num);
# 如果只是检查表中是否有数据效率上 1>anycol>*
    1. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
    2. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 和 update 的效率，因为 insert 和 update 时有可能会重建索引，所以怎么建索引也需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建立索引是否有必要。
    3. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
    4. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中的每一个字符，而对于数字型而言只需要比较一次就够了。
    5. 尽可能地使用 varchar/nvarchar 代替 char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
    6. 任何地方都不要使用 select * from t;用具体的字段列代替"*"，不要返回用不到的任何字段。
    7. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引).
    8. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
    9. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
    10. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table,避免造成大量log。
    11. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显示删除，先 truncate table,然后 drop table,这样可以避免系统表的较长时间锁定。
    12. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过一万行，那么就应该考虑改写。
    13. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
    14. 与临时表一样，游标并不是不可使用。对于小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包含“合计”的例程通常要比使用游标执行的速度快，如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
    15. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
    16. 尽量避免向客户端返回大量数据，若数据量过大，应该考虑相应需求是否合理。
    17. 尽量避免大事务操作，提高系统并发能力。

    注意事项：

    select Count (*)和Select Count(1)以及Select Count(column)区别
一般情况下，Select Count (*)和Select Count(1)两着返回结果是一样的
假如表沒有主键(Primary key), 那么count(1)比count(*)快，
如果有主键的話，那主键作为count的条件时候count(主键)最快
如果你的表只有一个字段的话那count(*)就是最快的
count(*) 跟 count(1) 的结果一样，都包括对NULL的统计，而count(column) 是不包括NULL的统计

    索引列上计算引起的索引失效及优化措施以及注意事项

创建索引、优化查询以便达到更好的查询优化效果。但实际上，MySQL有时并不按我们设计的那样执行查询。MySQL是根据统计信息来生成执行计划的，这就涉及索引及索引的刷选率，表数据量，还有一些额外的因素。
Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.
简而言之，当MYSQL认为符合条件的记录在30%以上，它就不会再使用索引，因为mysql认为走索引的代价比不用索引代价大，所以优化器选择了自己认为代价最小的方式。事实也的确如此

是MYSQL认为记录是30%以上，而不是实际MYSQL去查完再决定的。都查完了，还用什么索引啊？！
MYSQL会先估算，然后决定是否使用索引。
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 159,015评论 4赞 362
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,262评论 1赞 292
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 108,727评论 0赞 243
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,986评论 0赞 205
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,363评论 3赞 287
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,610评论 1赞 219
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,871评论 2赞 312
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,582评论 0赞 198
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,297评论 1赞 242
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,551评论 2赞 246
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,053评论 1赞 260
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,385评论 2赞 253
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,035评论 3赞 236
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,079评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,841评论 0赞 195
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,648评论 2赞 274
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,550评论 2赞 270

推荐阅读更多精彩内容