mysql优化总结

mysql优化总结

为什么开发中经常会提到优化mysql
1.磁盘IO过高
2.查询返回数据量大，带宽过载
3.mysql处理逻辑过于复杂，导致服务器cpu，内存过载
通俗一点就是SQL的执行效率对程序的运行效率的影响逐渐增大的情况下，此时对SQL的优化就很有必要。

mysql优化目标

1.降低磁盘IO
2.减少垃圾信息查询，交互次数
3.降低cpu的消耗

优化的方法

1.优化查询
查看sql是否需要优化方法：慢查询记录、explain执行计划、相关日志文件
1）.避免使用select * 查询
2）.用union all代替union
3）.小表驱动大表(in 适用于左边大表，右边小表。exists 适用于左边小表，右边大表。不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。)
4）.批量操作，数据量不能过大。不能循环一条一条插入，减少磁盘io
5）.多用limit
6）.in中值太多，可以分批或者多线程操作，防止超时
7）.高效分页，例如：limit 1000,20这类查询，会搜索全表。增加条件，id>1000这样
8）.用连接查询代替子查询
9）.join的表不宜过多
10）.控制索引的数量
11）.选择合理的字段类型
12）.提升group by的效率
13）.索引优化

2.调整缓冲区大小
1）.确定当前缓冲池大小：
在调整缓冲池大小之前，我们需要了解当前的缓冲池大小。可以通过执行以下SQL语句来获取当前缓冲池的大小：
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
这将返回当前的InnoDB缓冲池大小。

2）.确定可用内存：
在调整缓冲池大小之前，我们还需要了解服务器上可用的内存。可以通过执行以下SQL语句来获取服务器上可用的内存：
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
这将返回当前的InnoDB缓冲池大小。可以根据实际情况来确定可用的内存大小。

3）.计算新的缓冲池大小：
在确定可用的内存大小后，我们可以根据实际情况来计算新的缓冲池大小。通常推荐将缓冲池大小设置为可用内存的70-80%。可以将可用内存大小乘以0.7或0.8来计算新的缓冲池大小。

4）.配置MySQL的缓冲池大小：
可以通过修改MySQL的配置文件来调整缓冲池大小。在Linux系统上，通常配置文件位于/etc/my.cnf或/etc/mysql/my.cnf。在Windows系统上，通常配置文件位于C:Program FilesMySQLMySQL Server X.Xmy.ini。
打开MySQL的配置文件，在[mysqld]部分添加以下配置行：
innodb_buffer_pool_size = 新的缓冲池大小
将新的缓冲池大小替换为上一步计算出的新的缓冲池大小。
保存配置文件并重启MySQL服务使配置生效。

5）.验证缓冲池大小：
在重启MySQL后，可以再次执行以下SQL语句来验证新的缓冲池大小：
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
确保新的缓冲池大小与预期设置的大小相符。

代码示例：
以下是一个示例的MySQL配置文件my.cnf，展示了如何调整InnoDB缓冲池大小：
[mysqld]
innodb_buffer_pool_size = 1G
这将将InnoDB缓冲池大小设置为1GB。

3.使用分区表
分区表将大数据表分解成多个小表，从而减少查询时的数据量和锁定冲突。在MySQL中，可以使用RANGE、HASH、LIST等多种分区方式。
RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
分区
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
分表
分表和分区类似，区别是，分区是把一个逻辑表文件分成几个物理文件后进行存储，而分表则是把原先的一个表分成几个表。进行分表查询时可以通过union或者视图。分表又分垂直分割和水平分割，其中水平分分割最为常用。水平分割通常是指切分到另外一个数据库或表中。

4.使用合适的存储引擎
MySQL提供了多种存储引擎，包括InnoDB、MyISAM、Memory、NDB等。这些存储引擎各有特点，可以根据实际情况选择合适的存储引擎。例如，InnoDB适合用于事务处理，而MyISAM适合用于全文索引。