MySQL安装、配置调优（二）

MyISMA是MySQL的默认存储引擎。MyISMA不支持事务，不支持外键，优势是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用MyISMA引擎。比较适合Web、数据仓储等场景。
InnoDB存储引擎提供具有提交、回滚和崩溃恢复的事务安全，支持外键。对数据一致性要求比较高或更新比较频繁的的应用可以选择InnoDB。比较适合类似计费和财务系统等准确度要求比较高的系统。
MEMORY存储引擎-内存数据库，服务重启数据会丢失。适用于那些内容变化不频繁的代码表（常量表），或者作为统计结果的中间结果表。修改的数据不会写入磁盘。
MERGE存储引擎是一组MyISMA表的组合，这些MyISMA表的结构必须完全相同，MERGE表本身没有数据，对MERGE表的操作实际上是对内部的MyISMA表进行的。较适合数据仓储。

-- 查看数据库支持的存储引擎
show engines;

4.数据库配置参数说明

配置参数	配置说明
skip-grant-tables	禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！注：如果用winform连接mysql，加入此句速度会有很大的提升
back_log	指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求，该参数生效，主线程花费很短的时间检查连接并且启动一个新线程。 back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。
log_bin	如果你想让数据库服务器充当主节点的备份节点，那么开启二进制日志是必须的。如果这么做了之后，还别忘了设置server_id为一个唯一的值。就算只有一个服务器，如果你想做基于时间点的数据恢复，这（开启二进制日志）也是很有用的：从你最近的备份中恢复（全量备份），并应用二进制日志中的修改（增量备份）。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽，你可以用 PURGE BINARY LOGS 来清除旧文件，或者设置 expire_logs_days 来指定过多少天日志将被自动清除。配置log_bin时必须指定server-id，否则无法启动
innodb_log_file_size	这是redo日志的大小。（前提是打开log_bin）redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1，它都难于调整，因为一方面你想让它更大来提高性能，另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5，redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6，你可以一开始就把它这是成4G
max_connections	如果你经常看到‘Too many connections'错误，是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接，你需要比默认的151连接数更大的值。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。 -- 最大连接数 show variadles like 'max_connections' -- 相应连接数 show variables like 'max_used_connection' max_used_connection/max_connections*100%（理想值约等于85%）如果max_used_connections和max_connections相同，那么就是max_connections值设置过低或者超过服务器的负载上限了，低于10%则设置过大了。
skip_name_resolve	当客户端连接数据库服务器时，服务器会进行主机名解析，并且当DNS很慢时，建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了，因此在添加这项设置到一个已有系统中必须格外小心。加上这个配置就可以不通过用户直接连接数据库了
innodb_log_buffer_size	log缓存大小：此参数确定日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 – 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常8-16MB就足够了。越小的系统它的值越小。一般为1-8M，默认为1M，对于较大的事务，可以增大缓存大小。可设置为4M或8M。 innodb_log_buffer_size =16M
innodb_log_file_size	此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能 innodb_log_file_size = 512M
innodb_log_files_in_group	为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
innodb_buffer_pool_size	这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)，最大可以把该值设置成物理内存的80%。对于innodb表来说，innodb_buffer_pool_size的作用相当于key_buffer_size对于MyISAM表的作用一样。Innodb使用该参数指定大小的内存来缓冲数据和索引。最大可以把该值设置成物理内存的80%。
innodb_flush_log_at_trx_commit	主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0，1，2. 实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要两秒，设置为0时只需要一秒，设置为1时，则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提高速度。

wait_timeout	。
tmp_table_size	通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞
back_log	MySQL能够暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，他就会起作用。如果MySQL的连接数据达到max_connections时，新的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将不被接受连接资源。
thread_cache_size	线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。 thread_cache_size = 64
thread_concurrency	#该参数取值为服务器逻辑CPU数量×2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4 × 2 ＝ 8 thread_concurrency = 8
wait_timeout和interative_timeout	wait_timeout：指的是MySQL再关闭一个非交互的连接之前所需要等待的秒数。 interative_timeout：指的是关闭一个交互的连接之前所需要等待的秒数。对性能的影响 wait_timeout （1）如果设置太小，那么连接关闭的很快，从而使一些持久的连接不起作用（2）如果设置太大容易造成连接打开时间过长，在show processlist时，能够看到太多的sleep状态的连接，从而造成too many connections错误。（3）一般希望wait_timeuot尽可能的低 interative_timeout的设置将对你的web application没有多大的影响对于4GB左右内存的服务器可以设置为5-10。 wait_timeout = 10
key_buffer_size	全局缓冲： key_buffer_size指定索引缓冲区的大小，他决定索引的处理速度，尤其是索引读的速度。通过检查状态值 key_read_requests和key_reads，可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，至少是1：100，1：1000更好（上述状态值可以使用show status like ‘key_read%'获得）合理的值取决于索引大小、数据量以及负载未命中缓存的概率： key_cache_miss_rate = key_reads/key_read_requests*100% key_buffer_size只对MAISAM表起作用。如何调整key_buffer_size的值默认的配置数时8388608（8M），主机有4G内存可以调优值为268435456（256M） key_buffer_size = 32M
query_cache_size（查询缓存简称QC)
query_cache_size（查询缓存简称QC)	使用查询缓存，MySQL将查询结果存放在缓冲区中，今后对同样的select语句（区分大小写），将直接从缓冲区中读取结果。一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用查询缓存。注：两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样：多一个空格等），那么两个SQL将使用不同的cache。通过 show ststus like ‘Qcache%' 可以知道query_cache_size的设置是否合理
Qcache_free_blocks	缓存中相邻内存块的个数。如果该值显示过大，则说明Query Cache中的内存碎片较多了。注：当一个表被更新后，和他相关的cache block将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用 flush query cache语句来清空free blocks。
Qcache_free_memory	Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察当前系统中的Query Cache内存大小是否足够，是需要增多还是过多了
Qcache_hits	表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询能缓存的效果。数字越大缓存效果越理想。
Qcache_inserts	表示多少次未命中而插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert带查询缓存中。这样的情况次数越多，表示查询缓存应用到的比较少，效果也就不理想。
Qcache_lowmen_prunes	多少条Query因为内存不足而被清除出Query Cache，通过Qcache_lowmem_prunes和Qcache_free_memory 相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好是长时间来看，如果这个数字在不断增长，就表示可能碎片化非常严重，或者内存很少。
Qcache_queries_in_cache	当前Query Cache 中cache的Query数量
Qcache_total_blocks	当前Query Cache中block的数量
查询服务器关于query_cache的配置
query_cache_limit	超出此大小的查询将不被缓存
query_cache_min_res_unit	缓存块的最小大小，query_cache_min_res_unit的配置是一柄双刃剑，默认是 4KB ，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size	查询缓存大小（注：QC存储的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。）
query_cache_type	缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置必须设置为数字，可选值以及说明如下： 0：OFF 相当于禁用了 1：ON 将缓存所有结果，除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存 2：DENAND 则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。
query_cache_wlock_invalidate	当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成在读表获取结果。
查询缓存碎片率：Qcache_free_block/Qcache_total_block100% 如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。查询缓存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size100% 查询缓存利用率在25%以下的话说明query_cache_size设置过大，可以适当减小：查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50的话说明query_cache_size可能有点小，要不就是碎片太多查询缓存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)*100%
Query Cache的限制: a）所有子查询中的外部查询SQL 不能被Cache： b）在p'rocedure，function以及trigger中的Query不能被Cache c）包含其他很多每次执行可能得到不一样的结果的函数的Query不能被Cache
max_connect_errors	MySQL中与安全有关的计数器值，他负责阻止过多尝试失败的客户端以防止暴力破解密码的情况，当超过指定次数，MySQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hotos命令清空此host的相关信息。（与性能并无太大的关系。
sort_buffer_size	每个需要排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或 GROUP BY操作 sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。 sort_buffer_size：并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(2M)=1G
max_allowed_packet	根据配置文件限制server接受的数据包大小。不影响性能 max_allowed_packet=500M
join_buffer_size	用于表示关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。 join_buffer_size=2M
thread_cache_size服务器线程缓存
服务器线程缓存，这个值表示可以重新利用保存在缓存中的线程数量，当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁（前提时缓存数未达上限），如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，这个线程将被重新请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能，通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。设置规则如下：1G内存配置为8，2G内存为16.服务器处理此客户的线程将会缓存起来以响应下一个客户而不是被销毁（前提是缓存数未到达上限）
thread_cache_size	代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected	代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created	代表最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，说明MySQL服务器一直在创建线程，这也比较消耗资源，可以适当增加配置文件中thread_cache_size值
Threads_running	代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

read_buffer_size	MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配到一个读入缓冲区MySQL会为他分配一段内存缓冲区 read_buffer_size=1M
read_rnd_buffer_size	MySQL 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时（例如，按照排序顺序），将分配到一个随机都缓冲区。进行排序查询时，MySQL会首先扫描一遍该缓冲区，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但是MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存消耗过大。注：顺序读是根据索引的叶节点数据就能顺序的读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找侍其巷进行数据，而辅助索引和主键所在的数据端不同，因此访问方式是随机的。 read_rnd_buffer_size=16M
bulk_insert_buffer_size	批量插入数据缓存大小，可以有效的提高插入效率，默认为8M bulk_insert_buffer_size=64M
binary log	binlog_cache_size=2M //为每个session分配的内存，在事务过程中用来存储二进制日志的缓存，提高记录bin-log的效率。 max_binlog_cache_size=8M //表示的是binlog能够使用的最大cache内存大小 max_binlog_size=512M //指定binlog日志文件的大小。不能将变量设置为大于1G或小于4096字节。默认值为1G.在导入大容量的sql文件时，建议关闭，sql_log_bin，否则硬盘扛不住，而且建议定期做删除。 expire_logs_days=7 //定义了mysql清除过期日志的时间
sort_buffer_size	#查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。 max_allowed_packet = 4M thread_stack = 256K table_cache = 128K sort_buffer_size = 6M
join_buffer_size	读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！ read_buffer_size = 4M
join_buffer_size	读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！ join_buffer_size = 8M
myisam_sort_buffer_size	联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享 myisam_sort_buffer_size = 64M table_cache = 512

MySql官方已经决定在以后的版本中取消缓存技术，可以使用第三方缓存技术（ehcache或者redis等），所以缓存配置就不要看了

5.Mysql建议开启功能

5.1.慢查询

slow_query_log 启动停止技术慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志SQL执行时间得阈值（单位：秒，默认10秒）
log_queries_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

配置了慢查询后，它会记录符合条件的SQL包括：

查询语句
数据修改语句
已经回滚得SQL

5.1.1.查看慢查询相关参数

show variables like '%slow%';

show global status like '%slow%';

配置中打开了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有6593个慢查询

5.1.2.设置方法

将 slow_query_log 全局变量设置为“ON”状态

set global slow_query_log='ON';

设置记录方式为table，该表位于mysql数据库slow_log

set global log_output='TABLE';

设置查询记录时间范围

set global long_query_time=1;

5.1.3.重启MySQL服务

service mysqld restart

5.1.4.测试

 select sleep(10);

如果表记录存在，MySQL开启慢查询设置成功！

5.2.连接数

遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL服务器抗不住；另外一种情况是MySQL配置文件中max_connections值过小：最大可设置16384,超过没用

5.2.1.查看参数

查询最大连接数，这台MySQL服务器最大连接数是5000

 show variables like 'max_connections';

查询一下服务器响应的最大连接数：

 show global status like 'Max_used_connections';

MySQL服务器过去的最大连接数是295，没有达到服务器连接数上限，理想配置为
Max_used_connections / max_connections * 100% ≈ 85%

5.3.Key_buffer_size

　key_buffer_size是对MyISAM表性能影响最大的一个参数，下面一台以MyISAM为主要存储引擎服务器的配置：

 show variables like 'key_buffer_size';

分配了290MB内存给key_buffer_size=（304087040/1024平方）

查询key_buffer_size的使用情况：

show global status like 'key_read%';

一共有398943个索引读取请求，有4672个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
　　key_cache_miss_rate = Key_reads / Key_read_requests * 100%

key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘)，若key_cache_miss_rate在0.01%以下的话，key_buffer_size分配的过多，可以适当减少。

MySQL服务器还提供了key_blocks_*参数：

show global status like 'key_blocks_u%';

Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数，比如这台服务器，所有的缓存都用到了，要么增加key_buffer_size，要么就是过渡索引了，把缓存占满了。比较理想的设置：
　　Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

5.4.临时表

show global status like 'created_tmp%';

每次创建临时表，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% = 1.20%，应该相当好了。我们再看一下MySQL服务器对临时表的配置：

 show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');

只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表。

5.4.表锁情况

show global status like 'table_locks%';

Table_locks_immediate表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。示例中的服务器Table_locks_immediate / Table_locks_waited = 235，MyISAM就足够了。

5.5.索引优化

5.5.1.索引设计原则

最适合索引的列是在where子句中的列，或连接子句中的列，而不是出现在select关键字后的列
使用唯一索引。考虑某列中值的分布。索引列的基数越大，效果越好（一列中相同的数据越少，索引越好）
使用短索引。如果对字符串列进行索引，应该指定一个前缀长度。这样可以节省索引空间和磁盘IO。（alter tableName add key indexName (columnName(7)) --给表tableName的columnName字段的前7位建立前缀做引，索引名字为indexName）
利用最左前缀。比如创建了一个多列索引 index_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)单列索引，(c1,c2)的组合做引以及(c1,c2,c3)的组合索引。根据这个原则，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列要放在索引的最左边。
不要过度索引。索引过多，会导致磁盘占用较高，insert和update操作耗时增加，查询优化效率会变低。

5.5.2.以下不会使用索引的几种情况

以%开头的like查询不能使用索引
数据类型出现隐式转换的不能使用索引。数据INT类型，而用varchar查询
复合索引的情况下，假如查询条件不包含索引列最左边部分，不使用索引
如果MySQL估计使用索引比全表扫描慢，不使用索引
用or分隔开的条件，如果or前的列中有索引，而后边的列中没有索引，不会使用索引。（or的所有条件必须全部使用索引字段才会走索引

5.5.3.索引对查询速度的影响。

        mysql中提高性能的最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快了查询的速度。因此，索引对查询的速度有着至关重要的影响。使用所以可以快速的定位到表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。如果查询的时候没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询的记录，从而减少查询的记录数，达到提高查询速度的目的。

举例说明，使用索引和不使用索引的区别：

不使用索引rows列的值是26，说明此查询语句扫描了26条记录。

使用索引rows列的值是1，说明此查询语句扫描了1条记录。查询速度自然变快了。这种方法也可以判断自己添加的所以是否起了作用。

5.5.4 使用索引查询，几种特殊情况

　　索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起到作用。下面是几种比较特殊的情况。在这些情况下，有可能使用带有索引的字段查询时，索引并没有起到作用。

1）使用like关键字的查询语句。

在使用like关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%"，索引不会起作用。只有"%"不在第一个位置，索引才会起到作用。

使用模糊查询，”%“在前面的结果，显示索引没有起了作用了的。

2）使用模糊查询，”%“在后面的结果，显示索引起了作用了的。

5.5.5.使用多列索引的查询语句

mysql可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中第1个字段的时候，索引才会被使用。

多列所有的第一个字段作为查询条件的时候，发现索引起了作用了的:

5.5.6.使用or关键字的查询语句

使用语句的查询条件中只有or关键字，且or前后的两个条件中的列都有索引时，查询中才使用索引。否则，查询将不适用索引。

使用name索引，sex索引，查询出符合条件的。

6.查看MySQL执行sql历史记录

开启&关闭日志模式（可选择输出到表或文件中）

SET GLOBAL log_output = 'FILE'; 
SET GLOBAL general_log = 'ON'; //日志开启（日志输出到文件） 
SET GLOBAL log_output = 'FILE'; 
SET GLOBAL general_log = 'OFF'; //日志关闭 或者 
SET GLOBAL log_output = 'TABLE'; 
SET GLOBAL general_log = 'ON'; //日志开启（日志输出到表：mysql.general_log）
SET GLOBAL log_output = 'TABLE'; （推荐） 
SET GLOBAL general_log = 'OFF'; //日志关闭

6.1.查看当前配置

show variables like '%log_output%'; 
show variables like '%general_log%';

6.2.查看历史记录

SELECT * from mysql.general_log ORDER BY event_time DESC;

7.排序优化

MySql排序算法的执行方式：将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个块进行排序，然后将各个块合并成有序的结果集。

7.1.优化方案

尽量减少额外的排序，通过索引直接返回有序数据
适当加大max_length_for_sort_data系统变量，让更多的SQL可以在内存中完成排序，减少磁盘I/O操作。（因为排序区是每个线程独占的，设置过大会导致服务器SWAP严重）
尽量只使用必要的字段，select具体的字段名字，而不是select *，这样可以减少排序区的使用，提高SQL性能
MySQL会对GROUP BY后的所有字段排序，group by a1,a2,a3相当于后边默认加了order by a1,a2,a3 ，如果要避免排序带来的消耗，可以使用order by null禁止排序

8.读写分离配置

8.1.修改master配置文件：

log-bin=mysql-bin        #slave会基于此log-bin来做replication
server-id=1    #master的标示
binlog-do-db = amoeba_study        #用于master-slave的具体数据库

8.2.添加专门用于replication的用户

mysql> GRANT REPLICATION SLAVE ON *.* TO username@host IDENTIFIED BY 'password';

8.3.如果库中已有数据，需要记下file和position

mysql> flush tables with read lock;
mysql> show master status;   # 得到file和position
mysql> unlock tables;

8.4.编辑slave的配置文件，添加server-id

server-id=2    #slave的标示,需要唯一

8.5.配置生效后，配置与master的连接

mysql> CHANGE MASTER TO
-> MASTER_HOST='masterhost',
-> MASTER_USER='2中的username',
-> MASTER_PASSWORD='2中的password',
-> MASTER_LOG_FILE='3中的file',
-> MASTER_LOG_POS='3中的posiition';

mysql> start slave;  # 启动从库，开始同步数据

8.6.安装amoeba，按照官方文档进行配，配置服务端直接连接到amoeba即可

9.表结构优化

9.1、垂直拆分

把主键和一些常用的字段放到一个表中，把主键和其他的字段放到另一个表中。
优点：垂直拆分可以使一个数据页放更多的数据，可以较少IO次数。
缺点：查询所需的数据可能需要通过JOIN来查询。
适用场景：表过宽，包含text或blob字段，可以将不常用的列或text/blob列放到另外的表中存储。比如文章表可以将文章内容拆分到另外的表中。

9.2.水平拆分

根据某一列的值把数据放到多个独立的表中，比如历史数据放到另一张表里。
优点：减少大多数查询读取的数据量，降低索引层数，提高查询速度。
缺点：增加查询复杂度，查询多个表需要使用UNION，或者通过MERGE表。
适用场景：表中数据量过大，历史数据查询次数很少，比如订单信息、操作记录等。

9.3.逆规范化

增加冗余列：在多个表中具有相同的列，避免联合查询
增加派生列：增加的列来自其他表的计算结果，可避免使用函数
重新组表：将经常联合查询的表组成一个表，减少联合查询

参考资料

MySql性能优化 - 简书

月半花开

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL安装、配置调优（二）

优化MySQL所在服务器内核(此优化一般由运维人员完成)。硬件设备优化：硬件升级：是用RAID10磁盘阵列，RAID10兼具RAID1的可靠性和RAID0的优良并发读写性能对MySQL配置参数进行优化（my.cnf）此优化需要进行压力测试来进行参数调整。对SQL语句以及表优化：性能瓶颈定位、show status命令、慢查询日志、explain分析查询、
复制链接

扫一扫