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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值