Basis-MySQL Optm

配置优化

文件位置 /etc/my.cnf 或 %MYSQL_HOME%/my.ini
根据实际情况适当调整配置项

• 公共参数默认值

  #同时处理最大连接数，推荐设置最大连接数是上限连接数的80%左右
  max_connections = 151
  
  #查询排序时缓冲区大小，只对order by和group by起作用，可增大此值为16M
  sort_buffer_size = 2M
  
  #打开文件数限制，如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或卡死
  open_files_limit = 1024
  

• MyISAM 引擎参数默认值

  #索引缓存区大小，一般设置物理内存的30-40%
  key_buffer_size = 16M
  
  #读操作缓冲区大小，推荐设置16M或32M
  read_buffer_size = 128K
  
  #打开查询缓存功能
  query_cache_type = ON
  
  #查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖
  query_cache_limit = 1M
  
  #查看缓冲区大小，用于缓存SELECT查询结果，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值
  query_cache_size = 16M
  

• InnoDB 引擎参数默认值

  #索引和数据缓冲区大小，一般设置物理内存的60%-70%
  innodb_buffer_pool_size = 128M
  
  #缓冲池实例个数，推荐设置4个或8个
  innodb_buffer_pool_instances = 1
  
  #关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。
  innodb_flush_log_at_trx_commit = 1
  
  #默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。
  innodb_file_per_table = OFF
  
  #日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M
  innodb_log_buffer_size = 8M
  

数据库设计优化

• 数字类型没有负数时，最好添加 UNSIGNED 标识
• 为每张表添加一个 UNSIGNED INT 类型的主键字段
• 尽量使用 NOT NULL 定义字段，节省空间；可以给定默认值，以确保非空
• IP 地址定义为 UNSIGNED INT 类型，节省空间（INET_ATON() 函数)
• 为字段指定合适的长度和类型，如能使用 TINYINT 就不要使用 INT
• 经常搜索的字段建立索引
  对于存在模糊查询使用场景的字段，添加索引，可提高like的查询速度； 同时要确保该字段NOT NULL才会有效；
• 索引不是越多越好，太多会影响 INSERT 、UPDATE 和 DELETE 的效率
• 索引、分区
  对于数据量较大的表，通过时间+标识（如果存在）等多个字段建立索引，同时建立分区；
  对于历史数据，考虑定时备份到历史表（可新增一张历史表），可加快实时数据的查询
• 固定精度字段使用 DECIMAL 而不是 DOUBLE，如果对存储空间要求更高，建议乘以固定倍数转换成整数存储，可以大大节省存储空间
• 定长字符使用 CHAR，非定长字符使用 VARCHAR
• 时间日期类型选择合适的定义，节省存储空间，例如：如果只是存储到天，则使用 DATE；如果精确到时间，则使用 TIMESTAMP 而不是 DATETIME
• 外键约束
  系统中存在软删除（表中存在删除标识）， 建立外键约束存在冲突，即记录以软删除形式存在（删除标识为已删除），此时操作主表中的记录会报错，被其他表引用。
  对于这种情况，手动维护主从表之间的关系

SQL 执行效率分析

• 开启慢查询日志

  • 临时修改变量

  set global slow_query_log=on;  #开启慢查询功能
  set global slow_query_log_file=' /var/run/mysqld/mysqld-slow.log';  #指定慢查询日志文件位置
  set global log_queries_not_using_indexes=on;   #记录没有使用索引的查询
  set global long_query_time=1;   #只记录处理时间1s以上的慢查询
  

  • 修改配置文件

  slow_query_log=on
  slow_query_log_file=' /var/run/mysqld/mysqld-slow.log'
  log_queries_not_using_indexes=on
  long_query_time=1
  

  查看配置是否生效

  show variables like '%slow%';
  

  slow-variables.PNG

  分析慢查询日志，使用 MySQL 自带的 mysqldumpslow 工具

  mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log    #查看最慢的前三个查询
  

• EXPLAIN
  用于解释 MySQL 是如何处理 SQL 语句，有助于分析语句和表结构，如主键、索引、搜索、排序等等。可配合慢查询日志，找到需要分析的语句，通过解释结果定位问题。

EXPLAIN SELECT ...

![image](https://img.haomeiwen.com/i1447479/60fe47ec7fdd7b1c.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

结果字段说明

- select_type
    - simple 简单表
    - primary 主查询
    - union 第二个或者后面的查询
    - dependent union union中的第二个或后面的select语句,取决于外面的查询
    - union result union的结果
    - subquery 子查询中的第一个select
    - dependent subquery 子查询中的第一个select,取决于外面的查询
    - derived 导出表的select(from子句的子查询)
- table 输出结果的表
- type 表示mysql在表中查询方式
    - all 扫描全表
    - index 索引扫描
    - range 索引扫描范围，常见于<,<=,>,>=,between
    - ref 非唯一索引扫描
    - eq_ref 唯一索引扫描
    - const,system 最多只有一行匹配，查询非常快，例如主键primary key/唯一索引unique index/表中只有一条记录
    - null 不用访问表或者索引，直接就能得到结果 `explain select 1 from test where 1`
- EXTRA
    - Using filesort: **出现时需要优化**。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
    - Using temporary  **出现时需要优化**。MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
- `explain extened select count(1) from test where 1=1` 可以查看执行sql前，mysql做了哪些优化
- `explain partitions select id from test where id = 15` 可以查看分区名称

• 查询缓存
  查询缓存可以跳过SQL解析优化查询等阶段，直接返回缓存结果给用户，MySQL 已默认开启

对于频繁更新的表，查询缓存是不适合的，而对于不常改变数据且有大量相同sql查询的表，查询缓存能提升性能。

查询缓存不自动处理空格，因此 SQL 语句中要尽量减少空格的使用

• 命中条件

  缓存存在一个hash表中，通过查询SQL、数据库、客户端协议等作为key
  在判断是否命中前，MySQL不会解析SQL，而是直接使用SQL去查询缓存

  1. 查询必须完全一致，包括大小写、空格、注释
  2. 缓存不存储不确定结果的查询。如 NOW() 这类不确定函数的使用

SQL 语句优化

• 尽量满足查询缓存条件来编写 SQL，提高缓存命中率
• 非必要场景不要使用 SELECT *，只查询需要的字段
• 只返回一条记录时使用 LIMIT 1
• 拆分大量的 INSERT 或 DELETE 语句，分批执行
• 尽量避免在 where 子句中使用 != 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描
• 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
• 尽量避免在 where 子句中对字段进行 NULL 操作
• 使用 EXISTS 代替 IN
• 使用 JOIN 代替子查询
• 使用 UNION ALL 代替 UNION
• 对于存在分区的表，查询时一定要带上分区列
• 不要在索引上进行下列操作：
  • 计算索引字段
  • 使用 NOT、<>、!=
  • 使用 IS NULL 和 IS NOT NULL
  • 转换数据类型
  • 使用空值