MySQL （10）MySQL优化

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

要对数据库进行优化，首先我们要明白哪些主要因素会影响数据库的性能。主要有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效率远远高于磁盘。

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

---

2.存储引擎的选择

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

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

1. MyISAM使用的是表级锁，在对表中数据进行修改时要对整个表进行加锁。在对表中数据进行读取时对表中数据加共享锁。所以对MyISAM来说读和写两种操作是互斥的，所以对读写混合的并发性并不好，如果只是只读操作并发性还能接受，因为共享锁并不阻塞共享锁。
2. MyISAM不支持事务
3. 支持空间类应用，如GPS数据

4. MyISAM是将索引存储到内存中，而数据直接存到硬盘

   

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

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

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

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

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

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

---

3.数据库结构优化

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

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

整数类型

实数类型

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的索引

我们应该将它换成如下，就可以正确的使用到该列上的索引

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

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

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

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

---

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）做主从复制或主主复制，读写分离，可以在应用层做，效率高.