Skip to content

Latest commit

 

History

History
164 lines (128 loc) · 7.03 KB

93.MySQL性能优化.md

File metadata and controls

164 lines (128 loc) · 7.03 KB
Raw

MySQL性能优化

使用索引

在前面《关系型数据库MySQL》一文中，我们已经讲到过索引的相关知识，这里我们做一个简单的回顾。

索引的设计原则

  1. 创建索引的列并不一定是select操作中要查询的列，最适合做索引的列是出现在where子句中经常用作筛选条件或连表子句中作为表连接条件的列。
  2. 具有唯一性的列，索引效果好；重复值较多的列，索引效果差。
  3. 如果为字符串类型创建索引，最好指定一个前缀长度，创建短索引。短索引可以减少磁盘I/O而且在做比较时性能也更好，更重要的是MySQL底层的高速索引缓存能够缓存更多的键值。
  4. 创建一个包含N列的复合索引（多列索引）时，相当于是创建了N个索引，此时应该利用最左前缀进行匹配。
  5. 不要过度使用索引。索引并不是越多越好，索引需要占用额外的存储空间而且会影响写操作的性能（插入、删除、更新数据时索引也需要更新）。MySQL在生成执行计划时，要考虑各个索引的使用，这个也是需要耗费时间的。
  6. 要注意可能使索引失效的场景，例如：模糊查询使用了前置通配符、使用负向条件进行查询等。

使用过程

过程，通常也称之为存储过程，它是事先编译好存储在数据库中的一组SQL的集合。调用存储过程可以简化应用程序开发人员的工作，减少与数据库服务器之间的通信，对于提升数据操作的性能是有帮助的，这些我们在之前的《关系型数据库MySQL》一文中已经提到过。

数据分区

MySQL支持做数据分区，通过分区可以存储更多的数据、优化查询，获得更大的吞吐量并快速删除过期的数据。关于这个知识点建议大家看看MySQL的官方文档。数据分区有以下几种类型：

  1. RANGE分区：基于连续区间范围，把数据分配到不同的分区。

    CREATE TABLE tb_emp (
    eno INT NOT NULL,
    ename VARCHAR(20) NOT NULL,
    job VARCHAR(10) NOT NULL,
    hiredate DATE NOT NULL,
    dno INT NOT NULL
)
PARTITION BY RANGE( YEAR(hiredate) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

  2. LIST分区：基于枚举值的范围，把数据分配到不同的分区。

  3. HASH分区 / KEY分区：基于分区个数，把数据分配到不同的分区。

    CREATE TABLE tb_emp (
    eno INT NOT NULL,
    ename VARCHAR(20) NOT NULL,
    job VARCHAR(10) NOT NULL,
    hiredate DATE NOT NULL,
    dno INT NOT NULL
)
PARTITION BY HASH(dno)
PARTITIONS 4;

SQL优化

  1. 通过show status了解各种SQL的执行频率。

    

    2. 

  2. 

    定位低效率的SQL语句 - 慢查询日志。
    

    show processlist
    

    3. 

  3. 

    通过explain了解SQL的执行计划。例如：
    

    explain select ename, job, sal from tb_emp where dno=20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_emp
         type: ref
possible_keys: fk_emp_dno
          key: fk_emp_dno
      key_len: 5
          ref: const
         rows: 7
        Extra: NULL
1 row in set (0.00 sec)
    

      

    • select_type：查询类型（SIMPLE - 简单查询、PRIMARY - 主查询、UNION - 并集、SUBQUERY - 子查询）。
      • 

    • table：输出结果集的表。
      • 

    • type：访问类型（ALL - 全表查询性能最差、index、range、ref、eq_ref、const、NULL）。
      • 

    • possible_keys：查询时可能用到的索引。
      • 

    • key：实际使用的索引。
      • 

    • key_len：索引字段的长度。
      • 

    • rows：扫描的行数，行数越少肯定性能越好。
      • 

    • extra：额外信息。
      • 

    

    4. 

  4. 

    通过show profiles和show profile for query分析SQL。
    

    5. 

  5. 

    优化CRUD操作。
    

      

    • 优化insert语句

        

      • 在insert语句后面跟上多组值进行插入在性能上优于分开insert。
        • 

      • 如果有多个连接向同一个表插入数据，使用insert delayed可以获得更好的性能。
        • 

      • 如果要从一个文本文件装载数据到表时，使用load data infile比insert性能好得多。
        • 

      

      • 

    • 优化order by语句

        

      • 如果where子句的条件和order by子句的条件相同，而且排序的顺序与索引的顺序相同，如果还同时满足排序字段都是升序或者降序，那么只靠索引就能完成排序。
        • 

      

      • 

    • 优化group by语句

        

      • 在使用group by子句分组时，如果希望避免排序带来的开销，可以用order by null禁用排序。
        • 

      

      • 

    • 优化嵌套查询
      • 

    • 优化or条件

        

      • 如果条件之间是or关系，则只有在所有条件都用到索引的情况下索引才会生效。
        • 

      

      • 

    • 优化分页查询
      • 

    • 使用SQL提示

        

      • USE INDEX
        • 

      • IGNORE INDEX
        • 

      • FORCE INDEX
        • 

      

      • 

    

    6. 



配置优化


可以使用下面的命令来查看MySQL服务器配置参数的默认值。


show variables;
show variables like 'key_%';
show variables like '%cache%';
show variables like 'innodb_buffer_pool_size';


通过下面的命令可以了解MySQL服务器运行状态值。


show status;
show status like 'com_%';
show status like 'innodb_%';
show status like 'connections';
show status like 'slow_queries';


    

  1. 调整max_connections
    2. 

  2. 调整back_log
    3. 

  3. 调整table_open_cache
    4. 

  4. 调整thread_cache_size
    5. 

  5. 调整innodb_lock_wait_timeout
    6. 

  6. 调整innodb_buffer_pool_size：InnoDB数据和索引的内存缓冲区大小，以字节为单位，这个值设置得越高，访问表数据需要进行的磁盘I/O操作就越少，如果可能甚至可以将该值设置为物理内存大小的80%。
    7. 



架构优化


    

  1. 通过拆分提高表的访问效率。

      

    • 垂直拆分
      • 

    • 水平拆分
      • 

    

    2. 

  2. 逆范式理论。数据表设计的规范程度称之为范式（Normal Form），要提升表的规范程度通常需要将大表拆分为更小的表，范式级别越高数据冗余越小，而且在插入、删除、更新数据时出问题的可能性会大幅度降低，但是节省了空间就意味着查询数据时可能花费更多的时间，原来的单表查询可能会变成连表查询。为此，项目实践中我们通常会进行逆范式操作，故意降低范式级别增加冗余来减少查询的时间开销。

      

    • 1NF：列不能再拆分
      • 

    • 2NF：所有的属性都依赖于主键
      • 

    • 3NF：所有的属性都直接依赖于主键（消除传递依赖）
      • 

    • BCNF：消除非平凡多值依赖
      • 

    

    3. 

  3. 使用中间表提高统计查询速度。
    4. 

  4. 主从复制和读写分离，具体内容请参考《项目部署上线和性能调优》。
    5. 

  5. 配置MySQL集群。
    6.