性能优化

性能优化简介

  • 找出系统的瓶颈，提高MySQL数据库整体的性能
  • 合理的结构设计和参数调整，以提高用户操作响应的速度，同时尽可能的节省资源

使用SHOW STATUS LIKE ‘value’;查看MySQL数据库的性能参数

常用的性能参数值：

  • connections: 连接MySQL服务器的次数
  • uptime： MySQL服务器上线时间
  • slow_queries: 慢查询次数

优化查询

  • EXPLAIN语句 EXPLAIN [EXTENDED] SELECT select_options
  • DESCRIBE语句 DESCRIBE SELECT select_options

索引对查询速度的影响

  • 有索引时，查询语句会根据索引快速定位到待查询记录
  • 无索引时，会扫描表中的所有记录

    使用索引的几种特殊情况

  • 使用LIKE关键字查询的语句中，如果匹配字符串的第一个字符为”%”,索引不起作用，只有”%”不在第一个位置，索引才会起作用。
  • MySQL可以为多个字段创建索引，对于多列索引，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
  • 查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引，否则，查询将不使用索引

子查询

子查询虽然可以使查询语句很灵活，但是执行效率不高，在执行子查询时，MySQL会为内层查询语句的查询结果建立一个临时表，然后外层查询从临时表中查询记录，查询完毕后，再撤销这些临时表。 建议使用连接（JOIN）查询来替代子查询，连接查询不需要建立临时表，速度要比子查询快

优化数据库结构

  1. 将字段很多的表分解成多个表 原因：当一个表数据量很大时，会由于使用频率低的字段的存在而变慢
  2. 增加中间表 原因：通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询的表数据插入到中间表，以此来提高查询效率。
  3. 增加冗余字段
  4. 优化插入记录的速度
    1. 禁用唯一性检查 使用set unique_checks=0来禁止对唯一索引的检查
    2. 禁用外键检查 使用set foreign_key_checks=0来禁止对外键的检查
    3. 禁止自动提交 使用set autocommit=0来禁止自动提交
  5. 分析表，检查表和优化表
    1. 分析表 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb_name,tb_name
    2. 检查 CHECK TABLE tb_name [, tb_name]…[option]… option={QUICK|FAST|MEDIUM|EXTENDED|CHNGED}```
    3. 优化表 OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tb_name[,tb_name] 消除删除和更新造成的文件碎片

优化MySQL服务器

  • 优化服务器硬件
    • 大内存
    • 高IO
    • 多处理器
  • 优化MySQL的参数
    • key_buffer_size: 表示索引缓冲区的大小，索引缓冲区所有的线程共享，增加索引缓冲区可以得到更好处理的所用
    • table_cache:表示同时打开表的个数，值越大同时打开表的个数越多
    • query_cache_size:表示查询缓冲区的大小，需要和query_cache_type配合使用，当query_cache_type为0时，所有查询不使用查询缓冲区，当query_cache_type为1时，所有查询使用查询缓冲区，当query_cache_type为2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓冲区，使用查询缓冲区可以提高查询速度。
    • sort_buffer_size:表示排序缓存区的大小，值越大排序速度越快
    • read_buffer_size: 表示每个线程连续扫描时为扫描某个表分配的缓冲区大小
    • innodb_buffer_pool_size：表示InnoDB类型的表和索引的最大缓存，值越大查询速度越快。
    • max_connections:数据库的最大连接数
    • innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并将日志文件写入磁盘中，该参数有3个值分别为0，1和2
      • 0表示每隔一秒将数据写入日志文件，并将日志文件写入磁盘
      • 1表示每次提交事务时将数据写入日志文件，并将日志文件写入磁盘，默认值为1
      • 2表示每次提交事务时将数据写入日志文件，每个1秒将日志文件写入磁盘
    • interactive_timeout: 表示服务器在关闭连接前等待行动的秒数
    • sort_buffer_size:表示每个需要进行排序的线程分配的缓冲区的大小，增加这个参数可以提高ORDER BY或Group BY的操作速度
    • thread_cache_size: 表示可以复用的线程的数量
    • wait_timeout: 表示服务器在关闭一个连接时等待行动的秒数