MySQL （10）MySQL优化

本是古典何须时尚 2022-06-03 00:29 289阅读 0赞

# **哪些因素对数据库性能造成影响？** #

要对数据库进行优化，首先我们要明白哪些主要因素会影响数据库的性能。主要有4点

> 1.SQL查询速度  
> 2.服务器硬件  
> 3.网卡流量  
> 4.磁盘IO

1.SQL查询速度  
对于数据库来说，一条SQL的执行速度相当重要，因为最直接的影响到QPS.按照经验来说，数据库的性能80%都是由慢查询造成的。也就是说大多数的数据库问题都可以通过对SQL进行优化来解决。

2.服务器硬件  
CPU与内存对数据库的性能影响

3.网卡流量  
网卡IO被占满  
避免使用“select \*”进行查询，查询不必要的数据会消耗大量的网卡流量

4.磁盘IO  
磁盘IO性能突然下降（用更快的设备 如SSD）  
其他大量消耗磁盘性能额计划任务（调整计划任务）

5.大表带来的影响  
大表会造成慢查询，很难在一定的时间内过滤出所需要的数据  
建立索引需要很长的时间

1.  MySQL版本< 5.5 建立索引会锁表
2.  MySQL版本 >= 5.5 虽然不会锁表但是会引起主从延迟

修改表结构需要长时间锁表

6.大事务带来的影响  
锁定太多的数据，造成大量的阻塞和锁超时  
回滚时所需的时间比较长  
执行的时间长，容易造成主从延迟

--------------------

# **1.服务器硬件的优化** #

**（1）我们需要更多的CPU还是更快的CPU？**  
CPU每秒处理SQL语句的数量直接影响QPS  
我们需要频率更高的CPU，因为MySQL不支持多CPU对同一SQL并发处理，所以我们只能用到一个CPU核心对SQL进行处理。  
但是如果我们需要提高MySQL的并发量，我们就需要多个CPU。对于Web类这种高并发的应用来说，CPU的数量就显得比CPU的频率更重要了。

**（2）一定限度内的增加内存**   
内存大小直接影响数据库的性能，内存的IO效率远远高于磁盘。

将热数据放到内存中，读操作可以直接读取缓存中的数据，命中就不用去读取磁盘中的数据。  
对于写操作，可以将多次写入操作变成一次，写入到磁盘中，对写操作进行延缓  
![这里写图片描述][SouthEast]

--------------------

# **2.存储引擎的选择** #

首先，存储引擎是针对于表的，而不是针对于库的。我们要针对不同的业务来对不同的表选择合适的存储引擎。

**（1）MyISAM**  
MySQL 5.5版本之前默认的存储引擎。  
特性：

1.  MyISAM使用的是表级锁，在对表中数据进行修改时要对整个表进行加锁。在对表中数据进行读取时对表中数据加共享锁。所以对MyISAM来说读和写两种操作是互斥的，所以对读写混合的并发性并不好，如果只是只读操作并发性还能接受，因为共享锁并不阻塞共享锁。
2.  MyISAM不支持事务
3.  支持空间类应用，如GPS数据
4.  MyISAM是将索引存储到内存中，而数据直接存到硬盘
    
    ![这里写图片描述][SouthEast 1]

所以对于非事务型应用，只读类应用，空间类应用，可以使用MyISAM, 其他类型应用使用innodb更好

> PS：  
> 共享锁（读锁）相互不会被阻塞，多个线程可以在同意时间读取同一资源而不相互干扰  
> 独占锁（写锁）是排他的，一个写锁会阻塞其他的写锁和读锁，只有一个线程能执行写入，并防止其它线程读取资源，实现了隔离性。

**（2）innodb**  
MySQL 5.58版本之后，innodb为默认的存储引擎

1.  innodb使用的是行级锁，行级锁可以最大程度的支持并发，但是开销就比表级锁要大
2.  支持事务
3.  innodb是将数据和索引都存储到内存中，然后再存到硬盘  
    ![这里写图片描述][SouthEast 2]

所以对于大多数场景，都可以使用Innodb存储引擎。5.7版本之后innodb可以支持空间函数了。

建议不要混合使用存储引擎。

--------------------

# **3.数据库结构优化** #

这也是数据库优化中最重要的一点。  
**（1）逻辑设计 - 反范式化**  
完全遵守3范式有时并不能得到良好的SQL的查询性能，有时候需要关联多个表，反范式化就是对设计范式进行违反，准许少量的数据冗余，也就是空间换取时间。  
完全的范式化和反范式化都不好，需要对具体业务来适当的设计表结构

**（2）物理设计 - 数据类型的选择**  
选择合适的数据类型可以有效的节省内存和磁盘IO。  
对于一个列可以选用多种数据类型时，最优先考虑数字类型，其次时日期或者二进制，最后是字符类型。

整数类型  
![这里写图片描述][SouthEast 3]  
实数类型  
![这里写图片描述][SouthEast 4]

varchar和char类型（注意：单位为字符，比如UTF-8中一个字符=3字节，英文1字节）  
varchar用于存储变长字符串，只占用必要的存储空间。但是我们还是要选符合需求的长度，varchar(5)和varchar（200）存储“MySQL”字符都只需要6个字节就足够了，但是定义得太长就会消耗更多内存。

> PS,为什么时消耗6个字节而不是5个？  
> 列的最大长度小于255则只占用一个额外字节用于记录字符串长度  
> 列的最大长度大于255则要占用两个额外字节用于记录字符串长度

所以如果时很长的字符串应该使用text格式存储。

char类型是定长的，而且char类型列中会删除末尾的空格，而varchar不会。  
char类型的最大宽度为255

varchar适用场景：

*  字符串列的最大长度比平均长度大很多时
 *  字符串很少被更新
 *  使用了多字节字符集存储字符串

char类型的使用场景

*  存储长度近似的值，比如手机号，身份证号。
 *  存储短字符串，因为varchar要一个多余的字节存储字符串长度
 *  适合经常更新的字符串

--------------------

# **4.索引优化** #

> **BTree索引**使用多路搜索树的数据结构，可以减少定位的中间过程；综合效率较高，更适合进行范围查找，MySQL默认使用的索引。  
> **Hash索引**用Hash算法构建索引；精确的等值查询一次定位，效率极高，但特别不适合范围查询；只有匹配hash索引中的所有列时才能使用hash索引；

合理使用索引，在经常查询而不经常增删改操作的字段加索引，加的索引也不能太多，过多的缩影也回影响内存和磁盘IO。

**（1）索引列上不能使用表达式或者函数**  
比如我们想查找出30天内就会过期的产品时，如下就无法用到out\_date的索引  
![这里写图片描述][SouthEast 5]  
我们应该将它换成如下，就可以正确的使用到该列上的索引  
![这里写图片描述][SouthEast 6]

**（2）索引字段也不能重复过多，因为这样区分度就会变小。**  
如果使用索引所命中的数据占数据的大部分时，MySQL的查询优化器可能认为使用全表扫描的方式会更好。

**（3）使用前缀索引**  
有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

CREATE INDEX 索引名称 ON 表名（列名（n））;

我们要保证前缀索引尽可能的小，也要权衡索引选择性不能太差。  
如果我们只选择前两个作为索引，那么不同的值只有两个 ab ,bc。  
我们选择前三个字符作为缩影，那么不同的值就有4个了，选择性更好  
![这里写图片描述][SouthEast 7]

--------------------

# **5.SQL查询优化** #

**（1）对Max() 的优化**  
对max()查询，可以为表创建索引，例如 选出最后的支付时间

select max(payment_date) from payment;

可以对payment建立索引

create index idx_paydate on payment(payment_date);

**（2）对count()的优化**  
count()对多个关键字进行查询，比如在一条SQL中分别查出2016年和2017年电影的数量，语句：  
错误的方式：无法分开计算两年的电影数量

select count(year ='2016' or year = '2017') from film;

正确的方式

select count(year='2016' or null) as '2016年电影数量', count(year='2017' or null) as '2017年电影数量' from film;

为什么要加or null呢？  
count()是不包含空值的，例如id列有 1 ， 2， NULL  
那么count(\*)返回3， count(id) 返回2

因为count只有在遇见null时不计数，即count(null)==0，因此前者单引号内不管输入什么值都会统计出所有记录数，所以如果不加or null,count(year=’2006’)单引号里你填什么年份都是输出所有的记录，因为他们都not null！  
而’year=’2016”是个比较运算符，结果为1或者0，不管是不是2016， count函数都会计数。  
当加上or null以后，当值不为2016，year=’2016’的结果为0, ‘0 or null’结果为null，这样就排除了2006以外的结果。

**(3)group by 优化**  
如果涉及到表联接的过程中有group by这样的语句,可以先通过group by做为子查询，统计出结果后，再与其它表进行关联查询。  
group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效率。  
可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io

改写前

select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id;

改写后

select actor.first_name,actor.last_name,c.cnt from sakila.actor inner join( select actor_id,count(*) as cnt from sakila.film_actor group by actor_id )as c using(actor_id);

**（4）limit优化**  
limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的io问题  
1.使用有索引的列或主键进行order by操作

2.记录上次返回的主键，在下次查询时使用主键过滤  
即将：

select id,description from film order by id limit 50,5;

改为：

select id,description from film where id >55 and id<=60 order by id limit 1,5;

使用这种方式有一个限制，就是主键一定要顺序排序和连续的，如果主键出现空缺可能会导致最终页面上显示的列表不足5条，解决办法是附加一列，保证这一列是自增的并增加索引就可以了

--------------------

# **6.其他优化** #

**（1）缓存，memcached,redis；**

**（2）做主从复制或主主复制，读写分离，可以在应用层做，效率高.**

[SouthEast]: /images/20220603/3f6575a19fb84add9d9285845f9901e9.png
[SouthEast 1]: /images/20220603/d022ca17b8c5466cbaa99d76dfb5b159.png
[SouthEast 2]: /images/20220603/57c4897e97ce484c9f365c57b731b87d.png
[SouthEast 3]: /images/20220603/6f4dcc70238346539ac3e007a2dd936d.png
[SouthEast 4]: /images/20220603/03d50db13bd24a018bb82b64468c81c4.png
[SouthEast 5]: /images/20220603/4ba07c70a5dc4daa845bb7ed1a3b62b1.png
[SouthEast 6]: /images/20220603/2708b77550b74694879eb185c6f40e62.png
[SouthEast 7]: /images/20220603/3bbc79a1295a4525a59771e8df27f799.png