MYSQL优化

本文主要参考官网的优化 https://dev.mysql.com/doc/refman/5.7/en/optimization.html

优化SQL语句

没得完美的优化方案, 要么牺牲写性能(多数情况是这样), 要么牺牲读性能.

select 语句优化

select语句占大头.

基本上是 where又索引, 统计信息保持最新, 90%的问题就解决了

select语法格式

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [into_option]
    [FOR UPDATE | LOCK IN SHARE MODE]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

where子句优化

去除恒定条件

比如 where a>10 and 5=5 后面这个5=5就没得必要, 白白浪费数据库的计算能力(虽然浪费得不多)

having合并到where里面, 如果没使用count() min()等函数的话

例子:

select * from t1 where id>1 having age>10

等效于

select * from t1 where id>1 and age>10

join里面尽量每张表都有where来过滤

尽可能的返回更少的数据,这不是废话么....

每个表都建议有索引

以前mysql查询数据量如果大于30% 就走全表扫描, 现在更加复杂了,还要考虑表大小, 行数 和 IO块大小等

一些查询速度很快的例子

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

范围优化 (range)

见官网: https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html

不以通配符(%)开头

索引合并(index merge)

Index Merge访问方法检索具有多个扫描的行 并将range其结果合并为一个。此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以生成其底层扫描的并集、交集或交集并集

详情见官网: https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

####一些使用索引合并的例子:

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

引擎条件下推(Engine Condition Pushdown Optimization)

只支持NDB引擎

这种优化提高了非索引列和常量之间直接比较的效率。在这种情况下，条件被“下推”到存储引擎进行评估。此优化只能由NDB存储引擎使用。

###索引条件下推( Index Condition Pushdown Optimization)

索引条件下推 (ICP) 是针对 MySQL 使用索引从表中检索行的情况的优化

限制挺多的:

• 当需要访问整个表行时 ， ICP 用于 range、 ref、 eq_ref和 访问方法。ref_or_null
• ICP可以用于InnoDB andMyISAM表，包括分区表InnoDB和 MyISAM表。
• 对于InnoDB表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB聚集索引，完整的记录已经读入InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O。
• 在虚拟生成列上创建的二级索引不支持 ICP。InnoDB 支持虚拟生成列的二级索引。
• 引用子查询的条件不能下推。
• 引用存储功能的条件不能被按下。存储引擎不能调用存储的函数。
• 触发条件不能下推。（有关触发条件的信息，请参阅 第 8.2.2.3 节，“使用 EXISTS 策略优化子查询”。）

嵌套循环连接算法

Nested-Loop Join Algorithms

嵌套连接优化

mysql的cross join 等价于 inner join (标准sql不是这样的)

###(左右)外连接

避免全表扫描

优化DML

insert 建议写多个value (比分开写快一些)

load data 比 insert 快20倍左右

默认值就不用写.

经常update的话, 建议偶尔 OPTIMIZE TABLE 一下

权限优化

权限越细, 越安全, 但是验证越复杂(开销大)

建议减少对表级和字段级的权限控制. 尤其是数据库压力大的时候

其它优化

数据库尽量不要存储二进制文件. 只存二进制文件的路径就行.

如果对mysql的速度要求很高, 可以直接访问innodb/mysiam存储引擎, 跳过sql解析接口 (难啊)

可以配置只读库

索引优化

总的来说, 索引不要太多.

建议每张表都要有主键, 没得必要的字段作为主键的主键的话, 随便整个id字段自增都行

建议不要有外键, 但是不常用的字段可以拆分出去.

如果索引字段太长, 可以考虑使用前缀索引 create index id2 on t101(name(10));

复合索引建议把常用的字段放在前面 (最多16个字段)

可以偶尔收集下索引的统计信息

多数情况(范围)用btree, 要快速响应 k=v 这种情况可以用hash索引(无需的,排序很麻烦)

不要在索引字段使用函数或者其它计算, 比如 where id+1 > 10 改为 where id>9

优化数据库结构

尽可能分配最小的空间, 比如身份证就16位, 没必要分几十位

能确定not null的就设置not null , 能节省1bit空间 1000W行数据就能节省9.5MB空间 (好像也不是很多)

主键索引尽可能短, 尤其是很多二级索引的时候

如果涉及到迁移的话, 表名不要超过18个字符.

能用数字表示的字段就别用字符类型

能用布尔的就用布尔, 反正就是减少空间

对于小于8KB的字段可以用binary , 不要用blob

尽量不要使用随机值作为主键, 可以用个自增id作为主键

尽量将blob单独作为一张表, 能不放在数据库最好. 比如放在单独的服务器上,hdd就行, 反正主要是顺序读

优化innodb表

定期优化表(OPTIMIZE TABLE) innodb可以用alter table t202 engine=innodb;来替代. 优化之后全表扫描更快(因为物理文件更大了)

OPTIMIZE TABLE copies the data part of the table and rebuilds the indexes

主键字段太长(二级索引会太大), 建议换成自增字段作为主键, 也可以使用前缀索引

建议使用varchar(变长, 可以减少IO) 代替char(固定长,空间浪费多, 但是速度快) 典型的时间换空间

大表/重复数据多/数字字段多 的表可以使用COMPRESSED(压缩)格式 比如 CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED;

以查询为主的数据库 建议设置 AUTOCOMMIT=1; (默认)

服务器有大量insert/update/delete操作的, 建议不要执行回滚,会很慢, 比插入的时候慢好几倍(重启没得用, 启动的时候还是会执行回滚的)

建议设置innodb_change_buffering=all; (默认, 缓存insert delete update)

对于大量的insert 考虑下批量提交, 比如每5W行提交一次(客户端控制的)

设置innodb_force_recovery=3 可以快速启动(不运行事务回滚)

如果为了效率, 可以忍受丢一部分最新数据的话, 可以设置innodb_flush_log_at_trx_commit=0 (默认是1)

不同会话的隔离级别尽量保持一致

redo 大小建议 1GB

批量插入自增表的时候 可以设置 innodb_autoinc_lock_mode=2 (用得少,区别不大)

innodb查询表建议汇总: 每张表都有主键 , 主键不要太长, 自增最好 , 联合索引好过多个单独字段的索引, 确定为not null的字段就设置为not null

innodb DDL操作:

建议使用online ddl (比如: ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;)

清空表用truncate

每张表要有主键. 说了无数次了

如果CPU负载不够(<70%), 但是Mysql又很慢的话,就是IO的问题了:

增加 innodb_buffer_pool_size 建议50%-75% 如果是专门的数据库服务器的话, 可以使用80% 如果服务器内存很大(>64GB)的话, 建议留10-20GB 内存就行了

刷新日志可以设置 innodb_flush_method=O_DSYNC (默认为空, 因为有的unix上有问题)

建议设置AIO innodb_use_native_aio=ON(默认) 仅linux支持 只读变量

有条件的使用raid 和SSD 或者其它存储设备 很高的IOPS环境下, 可以增大 innodb_io_capacity (ssd 建议>2500 存储建议>10000)

当对压缩数据进行修改的时候, 可能会重新压缩,就会写大量的redo, 可以禁用innodb_log_compressed_pages (默认为ON)

为了减少并发线程之间的切换, 可以设置innodb_thread_concurrency=64 (默认0 表示无限制) innodb_concurrency_tickets=5000(默认)

建议统计信息持久化到磁盘上 innodb_stats_persistent=ON (默认)

#优化myisam

建议换成innodb....

优化内存表

建议只放数据量少 且 不重要的, 因为重启后数据就没了(表结构还在). 也可以用redis

explain

查看表结构方法:

EXPLAIN t201 ;
DESCRIBE t202 ;
SHOW COLUMNS FROM t201;
show create table t201;

explain 优点复杂, https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

还可以查看其它连接的执行计划

explain for connection 5;

估算查询性能

需要这么多次查找才能找到一行： log(row_count) / log(index_block_length / 3 2 / (index_length + data_pointer_length*)) + 1

在 MySQL 中，索引块通常为 1,024 字节，数据指针通常为 4 字节。对于一个 500,000 行的表，键值长度为 3 个字节（大小为 MEDIUMINT），公式表示 log(500,000)/log(1024/3*2/(3+4)) + 1= 4seeks

该索引需要大约 500,000 7 3/2 = 5.2MB 的存储空间（假设典型的索引缓冲区填充率为 2/3），因此您可能在内存中有很多索引，因此只需要一两次调用读取数据以查找行。

但是，对于写入，您需要四个查找请求来查找放置新索引值的位置，通常需要两次查找来更新索引并写入行

控制查询优化器

建议optimizer_prune_level=1(默认) 这是告诉优化器根据对每个表访问的行数的估计跳过某些计划

建议optimizer_search_depth=0 (默认62, 最大62 自动:0) 越小的话, 时间越短, 但是可能性能不行

优化器选项optimizer_switch:

选项很大, 都是默认打开的, 详情:https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html

设置hints (优化器提示)

例子: SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;

官网详情: https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

索引提示:

可以指定优化器使用啥索引

例子 SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;

详情: https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

优化器成本模型 相当于oracle的 CBO

主要跟两个表有关: mysql.server_cost 和 mysql.engine_cost

如果值非空(默认NULL)的话, 计算的时候就会考虑这个值, 直接修改表就行, 改完后执行FLUSH OPTIMIZER_COSTS

例子:

UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

Buffering和Caching

innodb_buffer_pool_size可以动态设置

SET GLOBAL innodb_buffer_pool_size=402653184;

建议关闭查询缓存 have_query_cache=NO

注:使用标准 MySQL 二进制文件时，此值始终为 YES，即使禁用查询缓存也是如此

所有关闭查询缓存也可以设置 query_cache_type=0 (默认) 也可以设置query_cache_size=0

#锁优化:

内部锁

在 MySQL 服务器内部执行锁定以管理多个会话对表内容的争用。这种类型的锁定是内部的，因为它完全由服务器执行，不涉及其他程序

行级锁优化

高并发环境下, mysql的自动死锁检测会影响性能, 可以关闭死锁自动检测innodb_deadlock_detect=off

注: myisam不支持行级锁

表级锁优化

当以select/insert为主的情况下, 表锁的性能比行锁更高 总的来说, 行锁更好, 不建议表锁

查看表锁情况

(root@127.0.0.1) [(none)]> SHOW STATUS LIKE 'Table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 117   |     可以立即授予表锁请求的次数
| Table_locks_waited    | 0     |     必须等待的次数
+-----------------------+-------+

锁相关的函数: https://dev.mysql.com/doc/refman/5.7/en/locking-functions.html#function_get-lock

##元数据锁(metadata lock)

MySQL 使用元数据锁定来管理对数据库对象的并发访问并确保数据一致性

##外部锁

外部锁定是使用文件系统锁定来管理MyISAM多个进程对数据库表的争用 innodb不涉及

mysql服务器的优化

系统优化:

尽量不要使用swap, 性能确实比不上内存, (内存不够的除外)

避免myisam外部锁(默认禁用, 也可以启动的时候加--skip-external-locking), 最好不要用myisam

磁盘IO优化:

提高磁盘IO, 使用寻道时间短的磁盘 比如: raid 01(先条带化, 再镜像)

还可以将库/表放到不同的磁盘上 ( 软链接)

内存优化:

https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

每个连接有需要的固定空间有

• 一个堆栈 ( thread_stack)
• 连接缓冲区 ( net_buffer_length)
• 结果缓冲区 ( net_buffer_length)

可以配置内存监控, 需要修改配置文件 performance-schema-instrument='memory/%=COUNTED' 然后重启就行了

(root@127.0.0.1) [(none)]> SELECT * FROM performance_schema.memory_summary_global_by_event_name
    ->        WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 44
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 6046875648
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 44
             HIGH_COUNT_USED: 44
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 6046875648
   HIGH_NUMBER_OF_BYTES_USED: 6046875648
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
    ->        code_area, sys.format_bytes(SUM(current_alloc))
    ->        AS current_alloc
    ->        FROM sys.x$memory_global_by_current_bytes
    ->        GROUP BY SUBSTRING_INDEX(event_name,'/',2)
    ->        ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 6.08 GiB      |
| memory/performance_schema | 131.70 MiB    |
| memory/mysys              | 8.25 MiB      |
| memory/sql                | 3.54 MiB      |
| memory/memory             | 174.03 KiB    |
| memory/myisam             | 106.06 KiB    |
| memory/client             | 10.02 KiB     |
| memory/csv                | 512 bytes     |
| memory/blackhole          | 512 bytes     |
| memory/vio                | 496 bytes     |
+---------------------------+---------------+
10 rows in set (0.01 sec)

可以考虑启用大页(huge page)

先保证操作系统上启用(cat /proc/meminfo | grep -i huge) 然后修改Mysql的配置文件(加上 large-pages),并重启mysql

#基准测试

压测mysql (benchmark)

(root@127.0.0.1) [testdb]> SELECT BENCHMARK(5000000,1+1);
+------------------------+
| BENCHMARK(5000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.17 sec)

表示mysql可以在0.17秒内计算 500 0000次简单的加法,  (注意是跟cpu频率相关, 本结果为i5-5300u) 

也可以用mysqlslap来压测 https://dev.mysql.com/doc/refman/5.7/en/mysqlslap.html

还有其它工具 tpcc-mysql/osdb/sysbench/dbt2等

服务器进程/线程信息

注意涉及 performance_schema.threads (这张表包含了 information_schema.processlist)

PROCESSLIST_COMMAND/command字段的所有值对于的描述为: https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html

PROCESSLIST_STATE/state字段对应: https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

常见的比如 Writing to net 表示正在发送数据到客户端

查询缓存状态: https://dev.mysql.com/doc/refman/5.7/en/query-cache-thread-states.html

复制源端线程(dump线程)状态https://dev.mysql.com/doc/refman/5.7/en/source-thread-states.html

复制目标端IO线程状态 https://dev.mysql.com/doc/refman/5.7/en/replica-io-thread-states.html

复制目标端SQL线程状态: https://dev.mysql.com/doc/refman/5.7/en/replica-sql-thread-states.html

复制目标端连接线程状态: https://dev.mysql.com/doc/refman/5.7/en/replica-connection-thread-states.html

NDB集群线程状态 https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-thread-states.html

事件调度线程状态 https://dev.mysql.com/doc/refman/5.7/en/event-scheduler-thread-states.html