配置优化
[mysqld]
#是否启用bin log
skip-log-bin
# 设置3306端口
port=3388
# 设置mysql的安装目录
basedir=F:\\mysql8\\data
# 切记此处一定要用双斜杠\\，单斜杠我这里会出错
# 设置mysql数据库的数据的存放目录
datadir=F:\\mysql8\\data
# 允许最大连接数
max_connections=20
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#InnoDB#
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#缓存池
innodb_buffer_pool_size=4G
# 双1模式
# 按事务刷盘，刷日志 [0:最快模式,1:安全模式,2:比0安全但比0要慢]看需求，服务器较为稳定可以选择2，自己搭建的最好选择1
innodb_flush_log_at_trx_commit=1
# 提交1次事务刷1次，可以为n
sync_binlog=1
# 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘
# 25%~50%
innodb_max_dirty_pages_pct=30
# 后台进程最大IO性能指标
# 默认200，如果SSD，调整为5000~20000
innodb_io_capacity=200
# 默认10M。防止高并发下，数据库受影响
innodb_data_file_path=ibdata1:1024M:autoextend
# 默认2，单位s。慢查询时间。建议0.1~0.5
long_qurey_time=0.3
# 8.0默认row。记录格式，让数据安全可靠
binlog_format=row
# 默认8小时。交互等待时间和非交互等待时间
# 建议300~500s,两参数值必须一致，且同时修改
interactive_timeout=500
wait_timeout=500
#日志大小
innodb_log_file_size=256M
#日志缓存大小
innodb_log_buffer_size=12M
innodb_flush_log_at_trx_commit=2
#innodb_flush_method
#thread_cache=8
#innodb_autoextend_increment=128M
#这里确认是否起用压缩存储功能
innodb_file_per_table=1
#innodb_file_format=barracuda #mysql 8 不支持该功能
#决定压缩程度的参数，如果你设置比较大，那么压缩比较多，耗费的CPU资源也较多；
#相反，如果设置较小的值，那么CPU占用少。默认值6，可以设置0-9#
innodb_compression_level=6
#指定在每个压缩页面可以作为空闲空间的最大比例，
#该参数仅仅应用在设置了innodb_compression_failure_threshold_pct不为零情况下，并且压缩失败率通过了中断点。
#默认值50，可以设置范围是0到75
innodb_compression_pad_pct_max=50
server-id=1180
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3388
default-character-set=utf8
优化更需要优化的Query
一般来说，高并发低消耗（相对）的Query 对整个系统的影响远比低并发高消耗的Query 大(io次数高cpu消耗大)。
- 定位优化对象的性能瓶颈
PROFILING 功能很清楚的找出一个Query 的瓶颈所在。
1、 开启 profiling 参数set profiling=1; 2、 执行 Query
3、show profiles;获取系统中保存的所有 Query 的 profile 概要信息
4、 针对单个 Query 获取详细的 profile 信息show PROFILE cpu ,block io for query 116
详细的 profile 信息
- 从Explain 入手
Explain 功能中给我们展示的各种信息的解释：
ID：Query Optimizer 所选定的执行计划中查询的序列号；
Select_type：所使用的查询类型，主要有以下这几种查询类型
◇ DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；◇ DEPENDENT UNION：子查询中的UNION，且为UNION 中从第二个SELECT 开始的后面所有SELECT，同样依赖于外部查询的结果集；◇ PRIMARY：子查询中的最外层查询，注意并不是主键查询；◇ SIMPLE：除子查询或者UNION 之外的其他查询；◇ SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；◇ UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；◇ UNION：UNION 语句中第二个SELECT 开始的后面所有SELECT，第一个SELECT 为PRIMARY◇ UNION RESULT：UNION 中的合并结果；
Table：显示这一步所访问的数据库中的表的名称；
Type：告诉我们对all：全表扫描表所使用的访问方式
◇ const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；◇ eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；◇ fulltext：◇ index：全索引扫描；◇ index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读取表数据；◇ index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引；◇ rang：索引范围扫描；◇ ref：Join 语句中被驱动表索引引用查询；◇ ref_or_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；◇ system：系统表，表中只有一行数据；◇ unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束
Possible_keys：该查询可以利用的索引. 如果没有任何索引可以使用，就会显示成null
Key：MySQL Query Optimizer 从possible_keys 中所选择使用的索引；
Key_len：被选中使用索引的索引键长度；
Ref：列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）的；
Rows：MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数；
Extra：查询中每一步实现的额外细节信息
◇ Distinct：查找distinct 值，所以当mysql 找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询；◇ Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用使用；◇ Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果；◇ No tables：Query 语句中使用FROM DUAL 或者不包含任何FROM 子句；◇ Not exists：在某些左连接中MySQL Query Optimizer 所通过改变原有Query 的组成而使用的优化方法，可以部分减少数据访问次数；◇ Range checked for each record (index map: N)：通过MySQL 官方手册的描述，当MySQL Query Optimizer 没有发现好的可以使用的索引的时候，如果发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL 检查是否可以使用range 或index_merge 访问方法来索取行。◇ Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的时候；◇ Using filesort：当我们的Query 中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。◇ Using index：所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据；◇ Using index for group-by：数据访问和Using index 一样，所需数据只需要读取索引即可，而当Query 中使用了GROUP BY 或者DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是Using index for group-by；◇ Using temporary：当MySQL 在某些操作中必须使用临时表的时候，在Extra 信息中就会出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中。◇ Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where 信息；◇ Using where with pushed condition：这是一个仅仅在NDBCluster 存储引擎中才会出现的信息，而且还需要通过打开Condition Pushdown 优化功能才可能会被使用。控制参数为engine_condition_pushdown 。
a) 永远用小结果集驱动大的结果集。因为在MySQL 中的Join，只有Nested Loop 一种Join 方式，也就是MySQL 的Join 都是通过嵌套循环来实现的。
b）只取出自己需要的Columns。对于任何Query，返回的数据都是需要通过网络数据包传回给客户端，如果取出的Column 越多，需要传输的数据量自然会越大。如果是需要排序的Query 来说，影响就更大了。在MySQL 中存在两种排序算法，一种是在MySQL4.1 之前的老算法，实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在我们所设定的排序区（通过参数sort_buffer_size 设定）中进行排序，完成排序之后再次通过行指针信息取出所需要的Columns，也就是说这种算法需要访问两次数据。第二种排序算法是从MySQL4.1 版本开始使用的改进算法，一次性将所需要的Columns 全部取出，在排序区中进行排序后直接将数据返回给请求客户端。改行算法只需要访问一次数据，减少了大量的随机IO，极大的提高了带有排序的Query 语句的效率。但是，这种改进后的排序算法需要一次性取出并缓存的数据比第一种算法要多很多，如果我们将并不需要的Columns 也取出来，就会极大的浪费排序过程所需要的内存。在MySQL4.1 之后的版本中，我们可以通过设置max_length_for_sort_data 参数大小来控制MySQL 选择第一种排序算法还是第二种排序算法。当所取出的Columns 的单条记录总大小max_length_for_sort_data 设置的大小的时候，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种优化后的算法。为了尽可能提高排序性能，我们自然是更希望使用第二种排序算法，所以在Query 中仅仅取出我们所需要的Columns 是非常有必要的。
c)仅仅使用最有效的过滤条件。 因为不同的索引键长度，如果多个索引键都能与表一一对应，那么应该只使用键长度较短的索引作为过滤条件。
d)尽可能避免复杂的Join 和子查询。 Query 语句所涉及到的越复杂的Join 语句，所需要锁定的资源也就越多，所表越多，所需要锁定的资源就越多。也就是说，阻塞的其他线程也就越多。如果我们将比较复杂的Query 语句分拆成多个较为简单的Query 语句分步执行，每次锁定的资源也就会少很多，所阻塞的其他线程也要少一些。（牺牲响应时间提高整体处理能力）
3.合理利用索引
如何判定是否需要创建索引?
◆ 较频繁的作为查询条件的字段应该创建索引；
◆ 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
◆ 更新非常频繁的字段不适合创建索引；
◆ 不会出现在WHERE 子句中的字段不该创建索引；
使用组合索引
只要不是其中如果某个过滤字段在大多数场景下都能过滤出90%以上的数据，而且其他的过滤字段会存在频繁的更新，都建议创建组合索引。因为当我们的并发量较高的时候，即使我们为每个Query 节省很少的IO 消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。
强制使用索引：
在有些情况下，可能是由于我们的系统统计信息的不够准确完整，也可能是MySQL Query Optimizer 自身功能的缺陷，会造成他并没有选择一个真正最优的索引而选择了其他查询效率较低的索引。这时可以使用 FORCE INDEX(index_name)强制使用索引。
- MySQL 中索引的限制
1. MyISAM 存储引擎索引键长度总和不能超过1000 字节；
2. BLOB 和TEXT 类型的列只能创建前缀索引；
3. MySQL 目前不支持函数索引；
4. 使用不等于（!= 或者<>）的时候MySQL 无法使用索引；
5. 过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引；
6. Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引；
7. 使用LIKE 操作的时候如果条件以通配符开始（ '%abc...'）MySQL 无法使用索引；
8. 使用非等值查询的时候MySQL 无法使用Hash 索引；
