MySQL优化的五个原则

总的来说mysql的优化都是围绕性能考虑的，针对MySQL优化，无非是涉及到内存、IO、CPU的优化和数据的健壮性。可以从以下几方面进行mysql的优化：
一、SQL语句角度
二、表的设计角度
三、数据库框架角度
四、配置优化
** 四、其他**

一、从SQL语句角度

在这之前先让我们理解sql语句执行的过程：

1. SELECT 
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>
FROM #选取表，将多个表笛卡尔积编成一个表
ON #对笛卡尔积的虚表进行筛选
JOIN #指定join，用于添加数据到on之后的虚表，例如left join会将左表的剩余数据添加到虚表中
where #对上诉虚表进行筛选
group by #1。<分组条件> # 分组 2.<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
having #对分组后的结果进行聚合筛选
select #返回的单列必须在group by子句中，聚合函数除外
distinct #数据去重
order by #排序
limit #函数限制

1.避免索引未命中的情况

  • 避免模糊查询
  • 避免使用in和not in
  • 避免使用or
  • 避免进行null值判断，可以给字段添加默认值0进行判断
  • 避免在等号左边进行计算
  • 使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。
  • 复合索引要坚守最左匹配原则
  • 隐式类型转换造成不适用索引
  • order by 条件要与where中条件一致，否则order by不会利用索引进行排序

2.select语句的优化

  • 避免出现select *
  • 避免出现不确定结果的函数，由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致
  • 多表关联查询时，小表在前，大表在后

增删改 DML 语句优化

  • 大批量插入的时候尽量一次插入，减少网络连接的耗费时间
  • 适当使用commit，可以释放undo数据块，释放在redo log的数据块，释放事物可以减少锁争用，所以得定期commit

表的设计

表结构的设计

  • 尽可能的遵循数据库的三范式，但对于大字段可以拆分出去，或者对于一些常用的字段设计在一个表中
  • 合理设计索引，对where ，orderby，join要用的字段简历索引，还可以设置复合索引，但不是索引越多越好，索引也会降低建表速度，所以尽量构建覆盖索引（尽量不用回表查询）和复用性强的索引（最左前缀原则）
  • 设置表的字段越小越好
  • 灵活使用存储过程，存储过程没有版本控制，版本迭代的时候要更新很麻烦。存储过程如果和外部程序结合起来用，更新的时候很难无感升级，可能需要停服。存储过程不利于将来分库分表。存储过程的功能不一定够强大，业务扩展之后可能会发现无法继续用存储过程实现了。存储过程可能无法和许多中间件、ORM库一起使用
  • 灵活建立视图
  • 不建议使用外键和级联，建议在应用层解决，因为不适合分布式高并发集群，级联更新是强阻塞，存在数据库更新性能风暴的风险
  • 查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下：

架构角度

1.读写分离
为了确保数据库产品的稳定性，很多数据库拥有双机热备功能。也就是，第一台数据库服务器，是对外提供增删改业务的生产服务器；第二台数据库服务器，主要进行读的操作。

2.分库分表

  • 垂直分表 当有些字段太大可以考虑把某些字段单独拆分成表，表和表之间用某些字段关联
  • 水平分表：mysql一个表大概在千万级数据就会出现性能下降，可以考虑动态分表。并且在数据库创建之初就考虑分表，这样就不用进行数据迁移

配置优化

如何定位是内存、IO、CPU当中的哪一个环节的问题。可以参考如下步骤：

1.使用top命令查看mysql进程的CPU以及内存使用情况，如果内存占用低，可以考虑进行内存参数优化；如果CPU使用率低，可以考虑进行CPU参数优化。

bbr mysql 优化 mysql优化教程_字段


2.使用iotop或者iostat分析磁盘IO。关键考虑数据吞吐量和IOPS两个参数，如果吞吐量和IOPS过低，则需要进行磁盘参数优化。数据吞吐量可以通过iotop查看，IOPS可以通过fio测试得出。

使用iotop查看磁盘读写最大的进程

内存优化

在常见的内存优化场景中，一般会涉及到内存大小设置及缓存命中率的问题。 一般的优化方式有：
innodb_buffer_pool_size优化。
Mysql优化之innodb_buffer_pool_size篇

磁盘优化

innodb_io_capacity优化 磁盘优化

CPU优化

在常见的磁盘优化场景中，一般会涉及到MySQL读写线程设置的问题。 一般的优化方式有：
innodb_read_io_threads及innodb_read_io_threads优化 cpu调优

其他角度

事务
尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询，但不是所有的数据库操作，都可以只用一条或少数几条就可以完成的。更多的时候是需要用一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。所以在我们的日常开发中要学会使用事务保证代码的健壮性。
同时也要注意将大事务拆分成小事务，因为简单的SQL容易使用到MySQL的QUERY CACHE； 减少锁表时间特别是使用MyISAM存储引擎的表； 可以使用多核CPU。减小资源的浪费