012 mysql性能优化

最新推荐文章于 2022-07-03 18:18:43 发布

诸般世界

最新推荐文章于 2022-07-03 18:18:43 发布

阅读量147

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/zxylwj/article/details/106487156

版权

mysql 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

一、服务器层面优化

1.将数据保存在内存中，保证从内存读取数据

mysql的内存空间buffer pool默认128M，可以通过扩大buffer pool来实现内存读取，buffer pool理论上能够扩大到内存的3/4或4/5；

--检查buffer pool是否够用？怎样确定 innodb_buffer_pool_size 足够大，数据是从内存读取而不是硬盘？
mysql>show global status like 'innodb_buffer_pool_pages_%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_data | 8190 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 12646 |
| Innodb_buffer_pool_pages_free | 0 |    --0 表示已经被用光
| Innodb_buffer_pool_pages_misc | 1 |
| Innodb_buffer_pool_pages_total | 8191 |
+----------------------------------+-------+

--调整buffer pool大小，修改my.cnf
vim /etc/my.cnf
添加innodb_buffer_pool_size = ...
innodb_buffer_pool_size = 750M

2.内存预热

mysql> select count(id) from tuser;
+-----------+
| count(id) |
+-----------+
| 10000000 |
+-----------+
1 row in set (5.03 sec)
mysql> select count(id) from tuser;
+-----------+
| count(id) |
+-----------+
| 10000000 |
+-----------+
1 row in set (2.85 sec)

3.降低磁盘写入次数

redo log 容量足够大，那么就会降低落盘次数少，修改redo log 容量 innodb_log_file_size 设置成 innodb_buffer_pool_size * 0.25
通用查询日志、慢查询日志不开(bin-log 必须开)
写redolog策略 innodb_flush_log_at_trx_commit 0 1 2应用

4.提高磁盘读写

SSD

注：核心的服务器端优化是读写分离、集群、分库分表；

二、SQL设计层面优化

设计中间表，一般针对于统计分析功能，或者实时性不高的需求(OLTP、OLAP)
为减少关联查询，创建合理的冗余字段（考虑数据库的三范式和查询性能的取舍，创建冗余字段还需要注意数据一致性问题）
对于字段太多的大表，考虑拆表（比如一个表有100多个字段）人和身份证
对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联）
每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下）。

三、SQL语句优化

1.索引优化(应用索引)

where :频繁作为查询条件的字段应该创建为索引；
应用组合索引(最左前缀);
索引下推（非选择行不加锁）
覆盖索引：应用覆盖缩影不需要回表,通过组合索引来创建覆盖索引；
多表关联查询中，关联字段应该创建索引 on 两边都要创建索引；
查询中排序的字段，应该创建索引， B + tree 有顺序，其插入就是以小到大，取出的时候依据排序的字段查出就不用在排序了；
统计或者分组字段，应该创建索引；
不要用 *
oder by (索引)，不用重排序，索引插入时就有顺序；

2.LIMIT优化

--原SQL
mysql> select * from tuser limit 9999999,1;
+----------+------------+-----------------+------+------+------+---------+
| id | loginname | name | age | sex | dep | address |
+----------+------------+-----------------+------+------+------+---------+
| 10000000 | zy10000000 | zhaoyun10000000 | 23 | 1 | 1 | beijing |
+----------+------------+-----------------+------+------+------+---------+
1 row in set (15.70 sec)

--优化 (倒序),原理是limit可以停止全表扫描；
mysql> select * from tuser order by id desc limit 1;
+----------+------------+-----------------+------+------+------+---------+
| id | loginname | name | age | sex | dep | address |
+----------+------------+-----------------+------+------+------+---------+
| 10000000 | zy10000000 | zhaoyun10000000 | 23 | 1 | 1 | beijing |
+----------+------------+-----------------+------+------+------+---------+
1 row in set (0.10 sec)

--定位id(非索引和主键字段优化明显)
mysql> select * from tuser where id>9999999 limit 1;
+----------+------------+--
---------------+------+------+------+---------+
| id | loginname | name | age | sex | dep | address |
+----------+------------+-----------------+------+------+------+---------+
| 10000000 | zy10000000 | zhaoyun10000000 | 23 | 1 | 1 | beijing |
+----------+------------+-----------------+------+------+------+---------+
1 row in set (0.00 sec)

3.其他优化