Mysql数据库优化

男娘i 2022-05-31 13:25 238阅读 0赞

优化方式:( 建议性能优化顺序)

1.查询优化(包括索引和一些sql的写法)

2.数据库表计较

3.系统配置

4.硬件

5.缓存机制

**第一部分:sql优化**

首先查找有问题的的sql?用慢查日志查询有问题的sql.  
查找步骤:  
使用MySQL慢日志对有效率问题的sql进行监控,就要看慢查日志监控是否开启.  
查询慢日志是否开启SHOW VARIABLES LIKE 'slow\_query\_log',一般为off,需要开启.  
查询所有日志状态:show VARIABLES LIKE 'log';  
开启慢查询日志记录 set global slow\_query\_log=on;  
记录没有使用索引的查询 set global log\_queries\_not\_using\_indexes=on  
记录慢查询日志的文件地址 set global slow\_query\_log\_file="/var/lib/mysql/localhost-slow.log";  
查询时间超过0.5秒的sql语句会被记录set global long\_query\_time=0.5;  
通过借助工具分析慢查日志,对结果进行分析,从而优化sql语句  
mysql自带慢查日志分析工具mysqldumpslow/或者pt-query-digest  
  
查询有问题的sql  
1.查询次数多而且每次查询占用时间较长,通常为pt-query-digest:分析的前几个表  
2.io大的sql,注意pt-query-digest:分析中的rows和examine项  
3.没有命中索引的sql,注意:pt-query-digest:分析中rows examine和rows send的对比,(扫描行,发送行)  
数据库的瓶颈是io  
4.分析sql查询,使用explain查询sql的执行计划,explain返回各列的含义:  
table:显示这一行的数据是关于哪张表的,  
type:这是重要的列,显示链接使用了何种类型,从最好到最差的链接类型为const,eq\_req,ref,range,index和all.  
possible\_keys:显示可能用到其他表的索引,  
key:实际使用的索引;  
key\_len;使用的索引长度,  
ref:显示索引的哪一列使用了  
rows:mysql认为必须检查用来返回请求链接的行数,  
注意:using file sort,using temporary 看到即需要优化  
5.优化max和count:建立索引,索引是顺序排列,优化max,count(\*)和count (id)不一样,因为count\* 包null.  
6.子查询优化,通常优化为join链接,注意是否产生重复数据(一对多时候),  
用distinct去重.  
7.优化group by (文件排序,临时表)可用子查询等改写.  
8.limit优化,先根据主键排序再分页,记录上次返回的主键,下次查询再使用主键过滤.

一些sql写法建议:

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where以及 order by 涉及的列上建立索引。  
2.应尽量避免在 where 子句中使用 或 操作符，否则将引擎放弃使用索引而进行全表扫描。  
3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。  
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，  
5.xx% 和 %xx 查询也将导致全表扫描 若要提高效率，可以考虑全文检索或者 xx% 模糊 查询 。  
6.in和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用between 就不要用 in 了  
7.如果在 where 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时它必须在编译时进行选择。  
8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。  
9.应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。  
10.不要在 where 子句中的“ “==”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。  
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才 能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。  
12 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。  
13 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并 会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。  
14 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。  
15 任何地方都不要使用 select \* from t ，用具体的字段列表代替 “\*\*”，不要返回用不到的任何字段。  
16尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限 只有主键索引 。  
17在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table ，避免造成大量 log ，以提高速度 如果数据量不大，为了缓和系统表的资源，应先 create table ，然后 insert 。

**第二部分:索引优化:**

1.首先知道哪些列适合建建索引?where 从句,group by,order by,on 从句,出现的列    
2.索引字段越小越好  
3.离散度大的列,放到联合索引的前面  
4.覆盖索引（covering index）指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。也可以称之为实现了索引覆盖。那么，优点显而易见。辅助索引不包含一整行的记录，因此可以大大减少IO操作。覆盖索引是mysql dba常用的一种SQL优化手段  
5.利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。  
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处  
6.查询列的离散程度:  
离散度更高的索引应该放在联合索引的前面，因为离散度高索引的可选择性高。考虑一种极端的情况，数据表中有100条记录，若INDEX(a,b)中a只有两种情况，而b有100种情况。这样对于查询唯一记录a = …，b = …时，先遍历全部索引看满足a条件的有50个索引节点，接下来还要再一个个遍历这50个索引节点。如果是INDEX(b,a)，先遍历全部索引发现满足b条件的索引节点只有一个，再遍历这个节点发现也满足a条件。虽然最后都能找到那个唯一的索引节点，但是第二种索引顺序对引擎遍历索引效率有很大的提高（用电话薄的思想去思考问题）  
7.含有"IN"、"OR"的Where子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。  
8.优化重复索引和冗余索引:  
重复索引：表示一个列或者顺序相同的几个列上建立的多个索引。   
冗余索引：两个索引所覆盖的列重叠

索引常用规则

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

5、索引应该建在选择性高的字段上；

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

8、频繁进行数据操作的表，不要建立太多的索引；

9、删除无用的索引，避免对执行计划造成负面影响；

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

**第三部分:数据库结构(表结构)优化**

1.对于字段较多的表,如果有些字段使用率较低,可以将这些字段分离出来成新表,因为当查询频繁的时候,会因为这些字段影响查询速度

2.对于经常需要联合查询的表,可以建立中间表提高查询效率.

3.增加冗余字段,假如需要关联查询的表太多,考虑适当增加一个字段,避免过多关联查询.

4.优化插入速度:影响插入效率的主要因素是索引,唯一校验,一次插入多条记录等.比如插入之前关闭唯一校验,使用load data infile批量导入要比insert效率快.

**第四部分:mysql系统配置优化**

mysql配置文件:

1.首先找到配置文件位置:  vi  /etc/my.cnf

2.mysq配置文件--常用参数:

wait\_timeout表示服务器关闭资源时等待的秒数，闲置连接会占用内存资源。可以从默认的8小时减到半小时  
key\_buffer\_size 表示索引缓冲区的大小(默认值：402653184,即384M)、通过检查状态值Key\_read\_requests和Key\_reads，可以知道key\_buffer\_size设置是否合理比例,key\_reads /key\_read\_requests应该尽可能的低，至少是1:100，1:1000更好  
innodb\_buffer\_pool\_size 表示InnoDB类型的表和索引的最大缓存(默认值：134217728即：128M)、InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,太大影响系统性能.  
query\_cache\_size 表示缓冲区大小(默认值：33554432即：32M)  
max\_connections 指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示，则需要增大该参数值。  
table\_cache表示同时打开表的个数,值越大打开表的速度越大,但是打开的太多会影响系统性能.  
sort\_buffer\_size:表示排序缓存区的大小,值越大排序越快.  
innodb\_log\_buffer\_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB  
thread\_concurrency：并发线程数，设为CPU核数的两倍  
back\_log：back\_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max\_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back\_log，如果等待连接的数量超过back\_log，将不被授予连接资源。可以从默认的50升至500

innodb\_buffer\_pool\_instanes(mysql5.5),控制缓冲池个数,一般四份,总数除份数  
innodb\_flash\_log\_at\_trx\_commit.关键参数,对innodb的io影响较大,一般默认值为1,可以配置为2,如果对安全要求高,可以配置为1不改.  
innodb\_read\_io\_threads读,innodb\_write\_io\_threads写,读写的线程数,默认为4,根据cpu的读写配置.  
innodb\_file\_per\_table关键参数,默认为off.即所有表都会建立在共享表空间中,日志无法收缩,影响读写,建议为on,独立表空间增加读写效率.  
innodb\_stats\_on\_mefadata.决定了什么时候刷新,innodb表的统计信息,设为off,自己刷新

**第五部分  服务器硬件优化**

内存优化

配置较大的内存,足够大的内存,是提高mysql数据库性能的方法之一,内存的io比磁盘快得多,可以增加系统的缓冲区容量,使数据内存停留的时间更长,以减少磁盘的io.

cpu选择

如何选择cpu 单核更快的cpu  还是多核cpu，mysql一些工作只能用到一个cpu ，不能并发用到多个cpu,基于这些特点考虑用单核更快的cpu.

cpu配置参数更改:在服务器的BIOS设置中,可调整下面的几个配置:

CPU选择Maximum Performance，发挥最大功耗性能。  
Memory Frequency（内存频率）选择Maximum Performance（最佳性能）。  
C1E，处理器处于闲置状态时启用或禁用处理器切换至最低功耗状态，建议关闭（默认启用）。  
C States（C状态），启用或禁用处理器在所有可用电源状态下运行，建议关闭（默认启用）

磁盘选择

磁盘寻道能力（磁盘I/O）,以目前高转速SCSI硬盘(7200转/秒)为例，这种硬盘理论上每秒寻道7200次，这是物理特性决定的，没有办法改变。MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一，对于日均访问量在100万PV以上的系统，由于磁盘I/O的制约，MySQL的性能会非常低下！解决这一制约因素可以考虑以下几种解决方案： 建议使用RAID-0+1磁盘阵列.

RAID是什么呢?  
RAID最初全称为『 Redundant Arrays of Inexpensive Disks 』“容错式廉价磁盘阵列”，后来改为『 Redundant Arrays of Independent Disks 』“容错式独立磁盘阵列”，它被用来提高整个磁盘系统IO能力、冗余能力和可用空间。磁盘阵列是由数个磁盘，组合成一个较大的磁盘组，根据选择的RAID等级不同，提供不同的功能.说白了就是多块硬盘,处理数据的磁盘多了,处理能力变快了.上面说的RAID-0+1磁盘阵列就是一种级别磁盘列阵.  
raid0:又叫条带.IO级别最好,数据安全不好  
raid1:镜像,至少两个磁盘,安全性较高  
raid5:最少三个磁盘

raid1+0:同时结合1+0;一般建议使用

**第六部分:分区**

(1)分区的概念:  
分区就是将一张表的数据按照一定的规则分到不同的区来保存,这样在查询数据的时候,如果数据的范围在同一个区内,那么只对一个区的数据进行操作,这样操作数据量更少,速度更快,而且这种方法对程序透明,程序员不需要改动.  
(2)分区的原理:  
mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了，如果表的数据太大，可能一个磁盘放不下，这个时候，就可以把数据分配到不同的磁盘里面去.另外,在创建分区时可以指定分区的索引文件和数据文件的存储位置，所以可以把数据表的数据分布在不同的物理设备上，从而高效地利用多个硬件设备。  
(3)分区的限制  
1.在5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。  
2.分区表中无法使用外键约束。  
3.主表的所有唯一索引列（包括主键）都必须包含分区字段  
(4)分区的类型及操作  
RANGE分区(范围分区，一般按月份分12个区即可，逻辑分区): 原理：mysql将会根据指定的拆分策略，把数据放在不同的表文件上，相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的  
List分区：原理是:MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值。而RANGE分区是从属于一个连续区间值的集合  
hash分区:主要用来确保数据在预先确定数目的分区中平均分布，Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。  
还有Key分区，子分区。

第七部分:分表

项目开发中，我们的数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询书读变慢，而且由于表的锁机制导致应用操作也搜到严重影响，出现了数据库性能瓶颈。  
当出现这种情况时，我们可以考虑分表，即将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法，让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。  
(1)水平分表:就是把一张表的数据分多个表存储,可以按照时间分割,或者每100万条数据切割一次.  
(2):垂直分割指的是：表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。

第八部分:读写分离

简单来说，读写分离就是只在主服务器上写，只在从服务器上读，基本的原理是让主数据库处理事务性查询，而从数据库处理select查询，数据库复制被用来把事务性查询导致的改变更新同步到集群中的从数据库.注意保持两个数据库的数据一致性.

第九部分:存储过程

在操作过程而且使用频率高的业务中,可以使用存储过程代替直接操作来提高效率,以为存储过程只需要编译一次,而且可以在存储过程中写一些复杂的业务逻辑.

第十:使用缓存策略减少数据库的压力

Redis 等nosql数据库 缓存策略 减少数据库交互 ,从而减数据库压力