数据库优化

分类

数据库优化主要有4个 方面

1. sql以及索引
   结构良好的sql ，建立有效的索引,适量，结构良好的sql，选择结构最优的sql，是最多的优化方式，

• 数据库表结构
  根据范式设计表结构，尽量减少 冗余，对查询有益，
• 系统配置
  在Linux系统下面，tcp连接数，打开文件数限制，最重要的就是文件数，表都是文件，打开表就是打开文件，如果上限就不行了
• 硬件
  内存越大，数据库性能越好，cpu核数越多不一定越好，IO设备对数据库影响很大

数据库sql优化

1. 如何发现有问题的sql
   使用慢查询日志对有效率问题的sql进行监控，

   
   image.png
   

   有查询执行信息，执行时间，内容，主机信息

• 慢查询日志分析
  mysqldumpslow 工具，pt-query-digest慢查日志分析工具
  其中pt-query-digest分析页面如下

  
  image.png
  • explain 命令
    explain select count(*) from user;
    可以看出加不加索引会明显变化扫描的行数
• 子查询优化，把子查询优化为用join链接的查询，如可以把

select * from t where t.id in (select t1.tid from t1);

变为

select * from t join t1 on t.id=t1.tid;

注意：当t1出现重复的时候，上面会出现两个重复的结果，加上distinct命令即可

• Group By 优化
• limit查询优化
  limit一般会伴随order by从句使用

1. 一般用主键来作为order by排序字段，因为主键一般都有索引，索引已经排好序了

• 记录上次返回的主键，在下次查询时使用主键过滤
• 索引优化

1. 如何选择合适的列建立索引
   1. 在where从句，group by 从句，order by 从句，on从句中出现的列
   • 索引字段越小越好
   • 离散度大的列放在联合索引前面，离散度就是不一样个数占总个数的比重

• 索引的维护及优化---重复及冗余索引
  去除冗余的索引，不需要的索引

数据库表结构优化

• 选择合适的数据类型

• 范式化和反范式化
  范式化是为了减少数据冗余，节省空间
  反范式化是为了用空间换取时间，提高sql查询的效率，节约时间

• 表的垂直拆分
  所谓垂直才分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按照以下原则进行：

  • 把不常使用的字段单独存放到一个表中
  • 把大字段独立存放到一个表中
  • 把经常一起使用的字段放到一起

• 表的水平拆分
  按照id进行hash散列，水平拆分
  这样会造成一个问题，就是有些后台统计业务需要夸分区统计，我们可以用一个汇总表来统计，因为后台业务时效性没有那么急迫，把前后台业务拆分。有助于系统分类处理，统计以及报表操作

系统配置优化

1. 操作系统优化
   打开文件数量的限制等内容
2. Mysql数据库本身的配置优化
   一般使用配置文件的方式来配置数据库
   innodb_buffer_pool_size 参数，是innodb的缓冲池的大小
   innodb_buffer_pool_instances 缓冲池的个数，增加查询并发性，在5.5版本以后的
   innodb_flush_log_at_trx_commit,对IO效率影响很大，缓冲区刷入磁盘的策略
   innodb_file_per_table,关键参数，控制每个表使用独立的表空间

服务器硬件优化

如何选择CPU?

1. Mysql有些工作只能使用单核
2. mysql有最大利用cpu核数限制，超过之后没有用处