数据库优化

数据库优化目的

• 避免出现页面访问错误

  • 由于数据库连接超时产生页面5xx错误
  • 慢查询造成页面无法加载
  • 由于阻塞造成数据无法提交

• 增加数据库稳定性

  • 低效查询造成

• 优化用户体验

  • 流畅页面的访问速度
  • 良好的网站功能体验

• 如何发现有问题的SQL

  • 使用MySQL慢查日志对效率问题的SQL进行监控
    • show variables like 'slow_query_log' //查询 慢查询 信息
    • set global show_query_log_file = '/home/mysql/sql_log/mysql-sql-shwo.log'
    • set global log_queries_not_using_indexes = on //开启没有使用索引 查询;
    • set global long_query_time = 1; //将超过1秒查询语句进行存放到指定文件中
    • set global slow_query_log = on ; //开启慢查询日志

• 慢查询日志包含的内容

  • 执行SQL的主机信息
  • #User@Host:root[root] @localhost []
  • SQL的执行信息
  • Query_time:0.000024 Lock_time:0.000000 Rows_sent:0 Rows_examined:0
  • SQL执行时间
  • SET timestamp=1402389235;
  • SQL的内容
  • select *from zzz;

• 如何分析SQL查询

  • explain
  • explain 查询语句
  • table：显示这一行的数据是关于那张表的
  • type：显示连接使用了何种类型。最好到最差（const、eq_reg、ref、range、index、ALL）
  • possible_key：显示可能应用在这张表中的索引，如果为空，表示没有可能的索引
  • key：实际使用的索引，为null则没有使用索引
  • key_len:使用索引的长度。不损失精确性情况下，越短越好
  • ref：显示索引的那一列被使用了。可能的话是一个常数
  • rows：MySQL认为必须检查的用来返回请求数据的行数
  • Using filesort：出现该字段后，查询则需要优化。MySQL需要进行额外的步骤来发现如何对返回的行排序。他根据连接类型以及存储键值和匹配条件的全部行的行指针来排序全部行
  • Using temporary ：出现该字段，查询需要优化。这里，MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by上。

• 索引

  • create index index_name on tbl_name(字段);

  • 如何选择索引？

    • 在where从句，group by从句，order by从句，on从句中出现的列
    • 索引字段在越小越好
    • 离散度大的列放到联合索引的前面

  • 删除重复索引

    • 使用pt-duplicate-key-checker 工具检查重复以及冗余索引
      • pt-dublicate-key-checker
      • -uroot
      • -p "password"
      • -h 127.0.0.1

  • 删除无用索引

    • 在perconMySQL和mariaDB中可以通过INDEX_STATISCS表来查看那些索引未使用，但是在MySQL中只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析
    • pt-index-usage
    • -uroot -p
    • mysql-slow.log

• count() 函数优化

  • select count(expr or null ) from tbl_name ;

• 子查询优化

  • 通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否是一对多的关系，要注意重复数据。

• limit查询优化

  • 使用有索引的列或主键进行order by 操作。
  • 记录上一次返回的主键，在下次查询时使用主键过滤

• 结构优化

  • 使用可以存下你的数据最小的数据类型
  • 使用简单的数据类型
  • 尽可能使用not null定义字段
  • 尽量少用text类型，非用不可时最好考虑分表
  • 用int来存储时间戳[FROM_UNIXTIME()时间戳转换为yyyy-mm-dd UNIX_TIMESTAMP() yyyy-mm-dd转换为时间戳 ]
  • 使用bigint来存储ip地址[INET_ATON()ip地址转整型 INET_NTOA()整型转ip地址]

• 操作系统配置优化

  • 网络配置

    • 修改/etc/sysctl.conf文件

    • 增加tcp支持的队列数

      • net.ipv4.tcp_max_syn_backlog = 65535

    • 减少断开连接时，资源回收

      • net.ipv4.tcp_max_tw_bukets = 8000
      • net.ipv4.tcp_tw_reuse = 1
      • net.ipv4.tcp_tw_recycle = 1
      • net.ipv4.tcp_fin_timeout =10

    • 文件数限制

      • 修改/etc/security/limits.conf文件，增加内容
        • • soft nofile 65535
        • • hard nofile 65535
      • MySQL服务器上应该关闭iptables，selinux等防火墙软件

• MySQL配置文件

  • linux配置文件：etc/my.cnf``etc/mysql/my.cnf

    • 查找配置文件命令：/usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

  • Windows配置文件：C:/windows/my.ini

     #重要，缓冲池的大小 推荐总内存量的75%，越大越好。
     	innodb_buffer_pool_size
     #默认只有一个缓冲池，如果一个缓冲池中并发量过大，容易阻塞，此时可以分为多个缓冲池；
     	innodb_buffer_pool_instances
     #log缓冲的大小，一般最常1s就会刷新一次，故不用太大；
     	innodb_log_buffer_size
     #重要，对io效率影响较大。0:1s刷新一次到磁盘；1：每次提交都会刷新到磁盘；2：每次提交刷新到缓冲区，1s刷新到磁盘；默认为1。
     	innodb_flush_log_at_trx_commit
     #读写的io进程数量，默认为4
     	innodb_read_io_threads
     	innodb_write_io_threads
     #重要，控制每个表使用独立的表空间，默认为OFF，即所有表建立在一个共享的表空间中。
     	innodb_file_per_table
     #mysql在什么情况下会刷新表的统计信息，一般为OFF。
     	innodb_stats_on_metadata