MySQL优化

一、优化方向

1. 存储引擎的选择：INnoDB MyISAM
2. 设计数据库的设计，尽量的遵循三范式
3. 适当的建立索引
4. 查询数据的时候，注意 ... 这些
5. 数据量大的时候，主从分离、分库分表、垂直/水平分割
6. 尽量采用 贵的，SSD硬盘。不选择机械硬盘

二、MySQL优化常问的问题有哪些

• 什么是MySQL的慢查询？
  • MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。
    资料来源：https://blog.csdn.net/qq_40884473/article/details/89455740
• 如何去分析 慢查询的日志？
  • 查询slow log的状态，如示例代码所示，则slow log已经开启

• mysql> show variables like ‘%slow%‘;
  +---------------------+------------------------------------------+
  | Variable_name       | Value                                    |
  +---------------------+------------------------------------------+
  | log_slow_queries    | ON                                       |
  | slow_launch_time    | 2                                        |
  | slow_query_log      | ON                                       |
  | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log |
  +---------------------+------------------------------------------+
  rows in set (0.00 sec)

• • • slow log的日志相关参数详解

  • slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
    
    log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
    
    slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
    
    long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
    
    log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
    
    log_output：日志存储方式。log_output=‘FILE‘表示将日志存入文件，默认值是‘FILE‘。log_output=‘TABLE‘表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。
    MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE‘。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，
    又需要能够获得更高的系统性能，那么建议优先记录到文件。

    　　如何在线安全的清空慢查询日志

    • 停止slow log

    mysql> set global slow_query_log=0;
    Query OK, 0 rows affected (0.27 sec)

    

    mysql> show variables like ‘%slow%‘;
    +---------------------+------------------------------------------+
    | Variable_name       | Value                                    |
    +---------------------+------------------------------------------+
    | log_slow_queries    | OFF                                      |
    | slow_launch_time    | 2                                        |
    | slow_query_log      | OFF                                      |
    | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log |
    +---------------------+------------------------------------------+
    4 rows in set (0.00 sec)
    #检查慢查询日志的状态

    
    • 为慢查询日志重新设置path路径

    mysql> set global slow_query_log_file=‘/mysqllog/slow_log/slow_queries_3306_new.log‘;
    Query OK, 0 rows affected (0.03 sec)

    • 开启慢查询日志，并设置long_query_time。

    mysql> set global slow_query_log=1;
    Query OK, 0 rows affected (0.01 sec)
    mysql>set global long_query_time=1;

    

    #检查状态是否成功开启
    mysql> show variables like ‘%slow%‘;
    +---------------------+----------------------------------------------+
    | Variable_name       | Value                                        |
    +---------------------+----------------------------------------------+
    | log_slow_queries    | ON                                           |
    | slow_launch_time    | 2                                            |
    | slow_query_log      | ON                                           |
    | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306_new.log |
    +---------------------+----------------------------------------------+
    4 rows in set (0.00 sec)

    
    • 检查slow sql 在新的日志文件中
    

    mysql> select sleep(10) as a, 1 as b;
    +---+---+
    | a | b |
    +---+---+
    | 0 | 1 |
    +---+---+
    1 row in set (10.00 sec)
    
    
    mysql> 
    [mysql@xxx-xxx ~]$ more /mysqllog/slow_log/slow_queries_3306_new.log
    ......
    Time                 Id Command    Argument
    # Time: 140213  6:44:24
    # User@Host: root[root] @ localhost []
    # Query_time: 10.000365  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    SET timestamp=1392273864;
    select sleep(10) as a, 1 as b;

    
    • 备份之前的慢查询日志

    mv /mysqllog/slow_log/slow_queries_3306.log /mysqlbackup/slow_log/slow_queries_3306.log.bak.20140213

• 遇到了慢查询如何解决？（最好有故事）

  • 0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
    1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
    2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
    3.order by limit 形式的sql语句让排序的表优先查
    4.了解业务方使用场景
    5.加索引时参照建索引的几大原则
    6.观察结果，不符合预期继续从0分析

• 索引的建立是越多越好？为什么不是越多越好？
  • 当然不是，
    关于建立索引的几个准则：
    1、合理的建立索引能够加速数据读取效率，不合理的建立索引反而会拖慢数据库的响应速度。
    2、索引越多，更新数据的速度越慢。
    不要在选择的栏位上放置索引，这是无意义的。应该在条件选择的语句上合理的放置索引，比如where，order by。
    例子：
    SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;
    上面这个语句，你在id/title/content上放置索引是毫无意义的，对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一个索引，那作用就相当大了。

MySQL优化

原文：https://www.cnblogs.com/errenjune/p/12498094.html