tmachc's notes

Stay Hungry, Stay Foolish.

0%

MySQL性能优化概述

发表于 更新于 分类于
本文字数： 3.4k 阅读时长 ≈ 12 分钟

SQL优化主要有SQL语句及索引优化，数据库结构优化，系统配置优化以及服务器硬件优化

慢查询

  1. 查看mysql是否开启慢查询日志
    1
    2
    show variables like 'slow_query_log';
    set global slow_query_log=on;

  1. 设置没有索引的记录到慢查询日志

    1
    2
    show variables like 'log_queries_not_using_indexes';
    set global log_queries_not_using_indexes=on;

  2. 查看超过多长时间的sql进行记录到慢查询日志

    1
    2
    show variables like 'long_query_time';
    set global long_query_time = 1; # (1秒)

  3. 查看慢查询记录文件位置

    1
    2
    show variables like 'slow_query_log_file';
    set global slow_query_log_file='D:/mysql/data/mysql-slow.log';

查看数据库系统属性值： show variables like ‘%log%’;

explain

EXPLAIN的每个输出行提供一个表的相关信息，并且每个行包括下面的列:

  1. id： MySQL Query Optimizer选定的执行计划中查询的序列号。表示查询中执行select子句或操作表的顺序，id值越大优先级越高，越先被执行。id相同，执行顺序由上至下。

  2. select_type： 查询类型
    SIMPLE： 简单的select查询，不使用union及子查询
    PRIMARY： 最外层的select查询
    UNION： UNION中的第二个或随后的select查询，不依赖于外部查询的结果集
    DEPENDENT UNION： UNION中的第二个或随后的select查询，依赖于外部查询的结果集
    SUBQUERY： 子查询中的第一个select查询，不依赖于外部查询的结果集
    DEPENDENT SUBQUERY： 子查询中的第一个select查询，依赖于外部查询的结果集
    DERIVED： 用于from子句里有子查询的情况，MySQL会递归执行这些子查询，把结果放在临时表里
    UNCACHEABLE SUBQUERY： 结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估
    UNCACHEABLE UNION： UNION中的第二个或随后的select查询，属于不可缓存的子查询

  3. table： 输出行所引用的表

  4. type： 重要的项，显示连接使用的类型，按最优到最差的类型排序
    system： 表仅有一行(=系统表)。这是const连接类型的一个特例。
    const： const用于用常数值比较PRIMARY KEY时。当查询的表仅有一行时，使用System。
    eq_ref： const用于用常数值比较PRIMARY KEY时。当查询的表仅有一行时，使用System。
    ref： 连接不能基于关键字选择单个行，可能查找到多个符合条件的行。叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自一个表里的多表查询的结果值
    ref_or_null： 如同ref，但是MySQL必须在初次查找的结果里找出null条目，然后进行二次查找
    index_merge： 说明索引合并优化被使用了
    unique_subquery： 在某些IN查询中使用此种类型，而不是常规的ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)
    index_subquery： 在某些IN查询中使用此种类型，与unique_subquery类似，但是查询的是非唯一性索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
    range： 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range
    index： 全表扫描，只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序，但是开销仍然非常大
    all： 最坏的情况，从头到尾全表扫描。

  5. possible_keys： 指出MySQL能在该表中使用哪些索引有助于查询。如果为空，说明没有可用的索引

  6. key： MySQL实际从possible_key选择使用的索引
    如果为NULL，则没有使用索引，很少的情况下，MYSQL会选择优化不足的索引
    这种情况下，可以在SELECT语句中使用USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引

  7. key_len： 使用的索引的长度，在不损失精确性的情况下，长度越短越好

  8. ref： 显示索引的哪一列被使用了

  9. rows： MYSQL认为必须检查的用来返回请求数据的行数

  10. extra： 出现以下2项意味着MYSQL根本不能使用索引，效率会受到重大影响，应尽可能对此进行优化
    Using filesort： 表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序，MySQL中无法利用索引完成的排序操作称为“文件排序”
    Using temporary： 表示MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

优化方法

数据库结构的设计

  • 数据行的长度不要超过8020字节，超过这个长度会在物理页中占用两行从而造成存储碎片，降低查询效率。
  • 能够用数字类型的字段尽量选择数字类型而不用字符串类型。
  • 变化不大的字段可以选择CHAR，对于评论等长度变化大的字段可以选择VARCHAR。
  • 字段的长度在尽可能的设得短一些，这样可以提高查询的效率，而且在建立索引的时候可以减少资源的消耗。

优化法则

优化法则 性能提升效果 优化成本 具体实现途径
减少数据访问 1~1000 低 索引(不等操作、列操作、前导模糊查询、null、数据类型不同、等式右边数据确定)
返回更少数据 1~100 低 分页、只返回需要的字段
减少交互次数 1~20 低 batch DML、In List（in数量不宜过大）、Fetch Size、存储过程、优化业务逻辑、ResultSet
减少服务器CPU开销 1~5 低 绑定变量、合理使用排序、减少比较操作、大量复杂运算在客户端处理
利用更多资源 @~10 高 客户端多进程并行访问、数据库并行处理

当一条SQL发送给数据库服务器后，系统首先会将SQL字符串进行hash运算，得到hash值后再从服务器内存里的SQL缓存区中进行检索，如果有相同的SQL字符，并且确认是同一逻辑的SQL语句，则从共享池缓存中取出SQL对应的执行计划，根据执行计划读取数据并返回结果给客户端。如果在共享池中未发现相同的SQL则根据SQL逻辑生成一条新的执行计划并保存在SQL缓存区中，然后根据执行计划读取数据并返回结果给客户端。

SQL语句注意事项

  1. 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描(设0)
  2. 应尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行。
  3. 应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
  4. in(?)和not in也要慎用，因为in会使系统无法使用索引，而只能直接搜索表中的数据。
  5. 尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。
  6. 必要时强制查询优化器使用某个索引，如在where子句中使用参数，也会导致全表扫描。（select id from t with(index(索引名)) where num=@num）
  7. 应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。(任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。)
  8. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
  9. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
  10. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
  11. 很多时候用exists是一个好的选择，因为不会产生大量锁定的表扫描或是索引扫描。
  12. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
  13. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
  14. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
  15. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替createtable，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
  16. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table ，然后drop table ，这样可以避免系统表的较长时间锁定。
  17. 在所有的存储过程和触发器的开始处设置SET NOCOUNT ON ，在结束时设置SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。
  18. 尽量避免大事务操作，提高系统并发能力。
  19. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
  20. 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。
  21. 充分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在WHERE子句中将连接条件完整的写上，有可能大大提高查询速度。
  22. 使用视图加速查询，把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。
  23. 能用DISTINCT的就不用GROUP BY
  24. 能用UNION ALL就不要用UNION
  25. 尽量不要用SELECT INTO语句，SELECT INOT语句会导致表锁定，阻止其他用户访问该表。

算法的优化

尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。封装存储过程

建立高效的索引

创建索引一般有以下两个目的： 维护被索引列的唯一性和提供快速访问表中数据的策略。

大型数据库有两种索引聚簇索引和非簇索引，一个没有簇索引的表是按堆结构存储数据，所有的数据均添加在表的尾部，而建立了簇索引的表，其数据在物理上会按照簇索引键的顺序存储，一个表只允许有一个簇索引。

因此，根据B树结构，可以理解添加任何一种索引均能提高按索引列查询的速度，但会降低插入、更新、删除操作的性能，尤其是当填充因子（Fill Factor）较大时。所以对索引较多的表进行频繁的插入、更新、删除操作，建表和索引时因设置较小的填充因子，以便在各数据页中留下较多的自由空间，减少页分割及重新组织的工作。

动作描述 使用聚簇索引 使用非聚簇索引
列经常被分组排序 应 应
返回某范围内的数据 应 不应
一个或极少不同值或者枚举值 不应 不应
少量的不同值 应 不应
大量的不同值 不应 应
频繁更新的列 不应 应
外键列 应 应
主键列 应 应
频繁修改索引列 不应 应

聚簇索引确定表中数据的物理顺序。
非聚簇索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引： 索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。