常见的mysql优化_sql语句里面的opti是什么意思-CSDN博客

本文链接：https://blog.csdn.net/Bobdragery/article/details/99282325

一、首先要了解什么影响数据库查询速度

1.1影响数据库查询速度的四大因素

1、服务器硬件限制

2、磁盘io

3、网卡流量

4、sql查询速度

1.2、两个名词

QPS：每秒查询率QueriesPerSecond，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。

TPS：事务/每秒，TransactionsPerSecond的缩写，它是软件测试结果的测量单位。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务数。

1.3、风险分析

最好不要在主数据库备份，大型活动前取消这样的计划

原因：

1、效率低下的sql：超高的QPS和TPS

2、大量的并发：数据连接数被占满(max_connection默认三100，一般把链接数设置得大一些)

3、超高的cpu使用率：cpu的资源耗尽出现宕机

4、磁盘io：磁盘io性能突然下降，大量消耗磁盘性能的计划任务。解决：更快磁盘设备，调整计划任务，做好磁盘维护

二、优化方案

2.1性能优化顺序

数据库结构设计和SQL语句优化

数据库存储引擎的选择和参数配置

系统选择及优化

硬件升级

2.2、网卡流量：如何避免无法连接数据库的情况

1、减少服务器的数量（从服务器会从主服务器复制日志）

2、进行分级缓存（避免前端大量缓存失效）

3、避免使用select进行查询

4、分离业务网络和服务器网络

2.3影响Mysql性能的因素

2.3.1服务器硬件

2.3.2 服务器系统（系统参数优化）

2.3.3、存储引擎。

MylSAM：不支持事物，表级锁
InnoDB：支持事物，支持行级锁，事务ACID

2.3.4、数据库的参数配置

2.3.2.1内存配置相关参数
内存的使用上限不能超过物理内存，否则容易造成内存溢出；（对于32位操作系统，MySQL只能试用3G以下的内存。
确定MySQL的每个连接单独使用的内存。
sort_buffer_size
定义了每个线程排序缓存区的大小，MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存（直接分配该参数的全部内存）；
join_buffer_size
定义了每个线程所使用的连接缓冲区的大小，如果一个查询关联了多张表，MySQL会为每张表分配一个连接缓冲，导致一个查询产生了多个连接缓冲；
read_buffer_size
定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小，MySQL有查询需要时会为其分配内存，其必须是4k的倍数
read_rnd_buffer_size
索引缓冲区大小，MySQL有查询需要时会为其分配内存，只会分配需要的大小。
**注意：**以上四个参数是为一个线程分配的，如果有100个连接，那么需要×100。

缓存池分配内存：

Innodb_buffer_pool_size
定义了Innodb所使用缓存池的大小，对其性能十分重要，必须足够大，但是过大时，使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中；
总内存-（每个线程所需要的内存*连接数）- 统保留内存
key_buffer_size
定义了MyISAM所使用的缓存池的大小，由于数据是依赖存储操作系统缓存的，所以要为操作系统预留更大的内存空间；
select sum(index_length) from information_schema.talbes where engine='myisam’

注意：即使开发使用的表全部是Innodb表，也要为MyISAM预留内存，因为MySQL系统使用的表仍然是MyISAM表。

max_connections
控制允许的最大连接数，一般2000更大，不要使用外键约束保证数据的完整性

2.3.5、数据库结构设计和SQL语句（重点优化）

必须掌握的30种sql优化
 sql优化

大概总结：

1、不要有超过5个以上的表连接（JOIN）
2、考虑使用临时表或表变量存放中间结果。
3、少用子查询
4、视图嵌套不要过深,一般视图嵌套不要超过2个为宜。

主要原则：

尽量避免全表扫描，考虑在where及order by涉及的列上建立索引

索引：

一个表的索引最多别超过6个
在这里插入图片描述

三、数据库结构设计

3.1 MySQL体系结构：

分三层：客户端->服务层->存储引擎

在这里插入图片描述

1、mysql是插件式的存储引擎

2、所有的跨存储引擎的功能都是在服务层实现的

3、mysql的存储引擎是针对表的，不是针对库的，也就是说在一个数据库中可以使用不同的存储引擎，但是不建议

3.2InnoDB存储引擎

Mysql5.5及以后版本的默认存储引擎是：InnoDB

3.2.1 InnoDB使用表空间进行数据存储。

show variables like’innodb_file_per_table
如果innodbfileper_table 为 ON 将建立独立的表空间，文件为tablename.ibd；
如果innodbfileper_table 为 OFF 将数据存储到系统的共享表空间，文件为ibdataX（X为从1开始的整数）；
.frm：是服务器层面产生的文件，类似服务器层的数据字典，记录表结构。

3.2.2 (MySQL5.5默认)系统表空间与( MySQL5.6及以后默认)独立表空间

1、系统表空间无法简单的收缩文件大小，造成空间浪费，并会产生大量的磁盘碎片。
2、独立表空间可以通过 optimeze table 收缩系统文件，不需要重启服务器也不会影响对表的正常访问
3、如果对多个表进行刷新时，实际上是顺序进行的，会产生IO瓶颈
4、独立表空间可以同时向多个文件刷新数据。

3.2.3 系统表空间的表转移到独立表空间中的方法

1、使用mysqldump 导出所有数据库数据（存储过程、触发器、计划任务一起都要导出）可以在从服务器上操作。
2、停止MYsql 服务器，修改参数（my.cnf加入innodbfileper_table），并删除Inoodb相关文件（可以重建Data目录）
3、重启MYSQL，并重建Innodb系统表空间。
4、重新导入数据。
或者Altertable 同样可以的转移，但是无法回收系统表空间中占用的空间

3.2.4InnoDB存储引擎的特性

特性一：事务性存储引擎及两个特殊日志类型：Redo Log 和 Undo Log

1、 Innodb是一种事务性存储引擎。
2、完全支持事务的 ACID特性。
3、支持事务所需要的两个特殊日志类型：RedoLog 和 UndoLog
Redo Log：实现事务的持久性(已提交的事务)。
Undo Log：未提交的事务，独立于表空间，需要随机访问，可以存储在高性能io设备上。
Undo日志记录某数据被修改前的值，可以用来在事务失败时进行 rollback；Redo日志记录某数据块被修改后的值，可以用来恢复未写入 data file的已成功事务更新的数据。