mysql优化篇详解

灰太狼 2022-06-04 00:45 168阅读 0赞

mysql优化的原则：

减少系统的瓶颈，减少资源占用，增加系统的反映速度

mysql数据库的性能参数:

使用 show status语句可以查看mysql数据库的性能参数

show status like 'value'

常用的参数:

slow\_queries  慢查询的次数

Com\_(CRUD)  操作的次数

Uptime  上线时间

在mysql的查询语句之前添加上  explain ,使用这句话可以查询mysql的执行计划 这个语句

例如下面的表:

explain select \* from test\_user where id = 1223;

![Image 1][]

表中每个字段的含义如下:

id:  查询的序列号

select\_type:  select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询

table:输出的行所引用的表

type:联合查询所使用的类型，它显示的是访问类型,是比较重要的一个指标，结果从好到坏依次是

system>const>eq\_ref>ref>fulltext>ref\_or\_null>index\_merge>unique\_subquery>index\_subquery>range>index>all  一般来说，得保证查询至少到达range级别以上，最好能够到达 ref级别

possible\_keys

指出mysql能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这是要提高性能，可以通过检验where子句，看是否引用某些字段，或者检查字段不是适合索引。

key

显示mysql时决定使用的键，如果没有索引被选择，键是null

key\_len

显示mysql决定使用的键长度，如果是null，长度就是null，文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分

ref

显示哪个字段或尝试与key一起被使用

rows

这个数表示mysql要遍历多少数据才能找到在innodb上是不准确的。

Extra

如果是Only index,这就意味着信息只用索引树中的信息检索出的，这比扫描整个表要快

如果是where used,就是使用上了where限制。

如果是impossible where表示用不着where，一般就是没查出来啥

如果此信息显示Using filesort或者Using temporary的话会很吃力，where和order by的索引经常无法兼顾，如果按照where来确定索引，那么在order by时，就必然会引起using

一般来说查看 mysql执行计划的时候要查看他的 type字段。

如果是 system、const、eq\_ref、ref、ref\_or\_null则是比较好的类型，否则这个sql语句需要进行优化，特别是类型是ALL的一定要进行优化。

想要优化mysql的数据库查询效率第一先要给数据库建立索引

建立索引的语句如下:

create index test\_user\_id\_name on test\_user(id);

create unique index index\_name on table\_name(name);

或者

alter table test\_user add unique(name);

alert table test\_user add primary key(id);

建立联合的索引

create index test\_user\_id\_name on test\_user(id,name);

删除数据库索引的语法如下：

drop index test\_user\_id\_name on test\_user;

索引可以提供查询的速度，但是并不是使用了带有索引的字段查询都会有效，有些情况下是不生效的，需要注意 如当使用  like模糊查询的时候 当第一个字符是 %的时候不生效。

还有一个要注意的点是当在使用数据库的or查询语句的时候，要保证or的左右两边的查询字段都是建立过索引的，这时候才能够生效。

对于mysql中的子查询，也必须要进行优化

mysql从4.1版本开始支持子查询，使用子查询进行select语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的sql操作，子查询虽然很灵活，但是其效率却并不高。

在执行子查询的时候，mysql需要创建临时表，查询完毕之后再删除这些临时表，所以子查询的速度会受到一定的影响。

优化:可以使用连接查询join来代替子查询，连接查询时不需要建立临时表，其查询速度比子查询快。

数据库的结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。需要考虑数据冗余，查询和更新的速度，字段的数据类型是否合理等多方面的内容。

1.将字段很多的表分解成多个表：

对于字段较多的表，如果有些字段的使用频率很低，则可以将这些字段分离出来形成新表。

2.增加中间表

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

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

3.增加冗余字段：

设计数据库时应尽量准寻范式理论的规约，尽可能的减少冗余字段，让数据库看起来精致，优雅，但是合理的增加冗余字段可以提高查询速度。表的规范化程度越高，需要连接查询的情况也就越多，性能越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在另外其他表中更新，否则就会导致数据不一致的问题

服务器方面的优化

服务器的硬件性能直接决定着mysql数据库的性能,硬件的性能瓶颈，直接决定mysql数据库的运行速度和效率

需要从以下几个方面去考虑:

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

2.配置高速磁盘，如SSD。

3.合理分配磁盘io，把磁盘io分散到多个设备上，以减少资源的竞争，提高并行操作能力

4.配置多核处理器，mysql是多线程的数据库，多处理器可以提高同时执行多个线程的能力。

优化mysql的参数

通过优化mysql的参数可以提高资源利用率，从而达到提高mysql服务器性能的目的。

mysql的配置参数都在  my.conf或者my.ini文件的mysqld组当中,常用的参数如下:

key\_buffer\_size:表示索引缓冲区的大小，索引缓冲区所有的线程共享，增加索引缓冲区可以得到更好的索引(对所有读和多重写)。当然，这个值也不是越大越好，它的大小取决于内存的大小，如果这个值太大，导致系统频繁换页，也会降低系统性能。

table\_cache:同时打开表的个数，这个值越大，能够同时打开的表的个数越多，这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。

query\_cache\_size:表示查询缓冲区的大小，该参数需要和query\_cache\_type配合使用，当query\_cache\_type的值是0 的时候，所有的查询都不使用查询缓冲区，但是query\_cache\_type=1时，所有的查询都使用查询缓冲区，除非在查询语句中指定SQL\_NO\_CACHE，如select SQL\_NO\_CACHE \* from tbi\_name  当query\_cache\_type=2时，只有在查询语句中使用SQL\_CACHE关键字，查询才会使用查询缓冲区,使用查询缓冲区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。

sort\_buffer\_size:表示排序缓存区的大小，这个值越大，进行排序的速度越快。

read\_buffer\_size:表示每个线程连续扫描时为扫描的每个表分配的缓冲区大小(字节)。当线程从表中连续读取记录时需要用到这个缓冲区，SET SESSION read\_buffer\_size=n,可以临时设置该参数的值。

read\_md\_buffer\_size:表示为每个线程保留的缓冲区大小，与read\_buffer\_size相似，但主要用于存储按特定顺序读取出来的记录，也可以用set session read\_rnd\_size=n

innodb\_buffer\_pool\_size:表示InnoDB类型的表和索引的最大缓存，这个值越大，查询的速度就越快，但是这个值太大会影响操作系统的性能。

max\_connections:表示数据库的最大连接数，这个连接数不是越大越好，因为这些连接会浪费内存的资源，过多的连接可能会导致mysql服务器僵死。

innodb\_flush\_log\_at\_trx\_commit:表示何时缓冲区的数据写入日志文件，并且将日志文件写入磁盘中，该参数对于Innodb引擎非常重要，该参数有3个值，分别为0，1，2值为0时表示每隔1s将数据写入日志文件并且将日志文件写入磁盘；值为1时表示每次提交事务时候将数据写入日志文件并将日志文件写入磁盘；值为2表示每次提交事务时将数据写入日志文件，每隔1s将日志文件写入硬盘，该参数的默认值为1，默认值1安全性最高，但是每次事务提交或事务外的指令都需要把日志写入(flush)硬盘，是比较浪费时间的，0值更快一点，但安全方面比较差；2值日志仍然会每秒写入到硬盘中，即使出现故障。

back\_log:表示在mysql暂停停尸回答新请求之前的短时间内，多少个请求可以被存在堆栈中，换句话说，该值表示对到来的TCP、ip连接的侦听队列的大小，只有期望在一个短时间内有很多连接，才需要增加该参数的值。操作系统在这个队列大小上也有限制，设定back\_log高于操作系统的限制将是无效的。

interactive\_timeout:表示服务器在关闭连接钱等待行动的秒数

sort\_buffer\_size:表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER by或GROUP by操作的速度，默认数值是 2097144（2MB）

thread\_cache\_size:表示可以复用的线程的数量，如果有很多新的线程，为了提高性能可以增大该参数的值。

wait\_timeout:表示服务器在关闭一个连接时等待行动的秒数，默认数值是28800.

mysql的插入优化

对于数据库引擎是MyISAM的来说,它进行优化的时候可以进行下面几个方面的操作

1.当在进行批量加入数据之前禁用索引，当数据插入完成之后再开启索引。

开启索引的语句    alter table table\_name enable keys

禁用索引的语句   alter table table\_name disable keys

2.禁用唯一性检查

数据库的唯一性检查会降低记录的速度，可以在插入记录之前禁用唯一性检查。

禁用唯一性检查的语句:set unique\_checks=0;