MySql系列(一):查询优化

MySql优化


博主从今天开始编写关于《MySql优化系列》的一系列文章,将会持续稳定的更新,感兴趣的小伙伴记得加关注哦!

以下所写内容均与以前的文章有联系可以前往博文查看,陈永佳的博客


数据库的四大特征,数据库的隔离级别


首先说一个老生常谈的话题,也是面试最喜欢问的东西,希望博主的分享对现在的你有所帮助!


数据库的四大特征:

  • 原子性(Atomicity):原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚。

  • 一致性(Consistency):一个事务执行之前和执行之后都必须处于一致性状态。

  • 隔离性(Isolation):隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。

  • 持久性(Durability):持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的。

数据库的隔离级别:

  • Serializable (串行化):可避免脏读、不可重复读、幻读的发生。
  • Repeatable read (可重复读):可避免脏读、不可重复读的发生。
  • Read committed (读已提交):可避免脏读的发生。
  • Read uncommitted (读未提交):最低级别,任何情况都无法保证。

数据库设计范式:

  • 什么是范式:简言之就是,数据库设计对数据的存储性能,还有开发人员对数据的操作都有莫大的关系。所以建立科学的,规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。
  • 什么是三大范式:
  • 第一范式:当关系模式R的所有属性都不能在分解为更基本的数据单位时,称R是满足第一范式的,简记为1NF。满足第一范式是关系模式规范化的最低要求,否则,将有很多基本操作在这样的关系模式中实现不了。
  • 第二范式:如果关系模式R满足第一范式,并且R得所有非主属性都完全依赖于R的每一个候选关键属性,称R满足第二范式,简记为2NF。
  • 第三范式:设R是一个满足第一范式条件的关系模式,X是R的任意属性集,如果X非传递依赖于R的任意一个候选关键字,称R满足第三范式,简记为3NF.

注:关系实质上是一张二维表,其中每一行是一个元组,每一列是一个属性

第一范式:
  • 每一列属性都是不可再分的属性值,确保每一列的原子性
  • 两列的属性相近或相似或一样,尽量合并属性一样的列,确保不产生冗余数据。
第二范式:
  • 每一行的数据只能与其中一列相关,即一行数据只做一件事。只要数据列中出现数据重复,就要把表拆分开来。
  • 一条数据做一件事,不掺杂复杂的关系逻辑。同时对表数据的更新维护也更易操作。
第三范式:
  • 数据不能存在传递关系,即没个属性都跟主键有直接关系而不是间接关系。像:a–>b–>c 属性之间含有这样的关系,是不符合第三范式的。
例如:
  • 比如Student表(学号,姓名,年龄,性别,所在院校,院校地址,院校电话)
  • 这样一个表结构,就存在上述关系。 学号–> 所在院校 --> (院校地址,院校电话)
  • 这样的表结构,我们应该拆开来,如下。
  • (学号,姓名,年龄,性别,所在院校)–(所在院校,院校地址,院校电话)

三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,就要特殊对待,数据库设计最重要的是看需求跟性能,需求>性能>表结构。所以不能一味的去追求范式建立数据库,各位小伙伴要记牢哦!

MySql的优化建议

前言

有人要问博主为什么要对MySql进行优化呢?
这个问题我该怎么说呢,只要是一个有过中型以上项目开发经验的人都知道,一条MySql执行性能关乎了你的系统的稳定性以及性能。所以小伙伴们跟着博主一起来探寻一下MySql的优化吧,请持续关注《MySql优化系列》!

注意:(尽量减少 SELECT * 的出现下文用到请忽略,用具体的字段列表代替 * 号,不要返回用不到的任何字段,用字段能够大大的节省数据传输量,与数据库的内存使用量哟。)


划重点:

EXPLAIN:

  • 首先做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。
  • type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
  • key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。
  • key_len列,索引长度。
  • rows列,扫描行数。该值是个预估值。
  • extra列,详细说明。注意,常见的不太友好的值,如:Using filesort,Using temporary。

SELECT语句务必指明字段名称:

  • SELECT*增加很多不必要的消耗(CPU、IO、内存、网络带宽);
  • 增加了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。
  • 所以要求直接在SELECT后面接上字段名。

一些常见的SQL实践

  • 负向条件查询不能使用索引
    SELECT * FROM order WHERE order_status!=0 AND order_status !=1
    not in/not exists都不是好习惯
    可以优化为in查询:SELECT * FROM order WHERE order_status IN(1,2,3,4,5)

  • SQL语句中IN包含的值不应过多:
    MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:SELECT id FROM t WHER num IN(1,2,3) 对于连续的数值,能用BETWEEN就不要IN或者使用连接来替换。

  • 如果排序字段没有用到索引,就尽量少排序

  • 尽量用union all代替union
    union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。union all的前提条件是两个结果集没有重复数据。

  • 前导模糊查询不能使用索引(会导致索引失效而进行全表扫描)
    SELECT * from order WHERE order_desc LIKE ‘%XX’
  • 而非前导模糊查询则可以:(不会导致索引失效而进行全表扫描)
    SELECT * FROM order WHERE order_desc LIKE ‘XX%’

  • 数据区分度不大的字段不宜使用索引
    SELECT * FROM user WHERE user_sex=1
    原因:性别只有男,女,每次过滤掉的数据很少,不宜使用索引。

只要能过滤80%数据时就可以使用索引。对于订单状态,如果状态值很少,不宜使用索引,如果状态值很多,能够过滤大量数据,则应该建立索引。


  • 在属性上进行计算不能命中索引
    SELECT * FROM order WHERE YEAR(date) < = ‘2019’
  • 即使date上建立了索引,也会全表扫描,可优化为值计算:
    SELECT * FROM order WHERE date < = CURDATE()
    或者:
    SELECT * FROM order WHERE date < = ‘2019-05-08’

并非周知的SQL实践(走起做隐藏人群)

关于JOIN的《MySql系列(三):JOIN的原理和算法 》,如何查询%name%,博主会另起几篇文章和大家一起探讨,敬请关注哦!


  • 分段查询:(扫描的行数成百万级以上的时候就可以使用分段查询)
    在一些用户选择页面中,可能一些用户选择的时间范围过大,造成查询缓慢。
    原因是扫描行数过多,这个时候可以通过程序,分段进行查询,循环遍历,将结果合并处理进行展示。

  • 避免在where子句中对字段进行null值判断
    对于null的判断会导致引擎放弃使用索引而进行全表扫描。

  • 避免隐式类型转换
    WHERE子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定WHERE中的参数类型

  • 避免在where子句中对字段进行表达式操作比如:
    SELECT * FROM user WHERE age*2=36;
    中对字段就行了算术运算,这会造成引擎放弃使用索引,
  • 建议改成:SELECT * FROM user WHERE age=36/2;

  • 注意范围查询语句
    对于联合索引来说,如果存在范围查询,比如between、>、<等条件时,会造成后面的索引字段失效。

  • 如果业务大部分是单条查询,使用Hash索引性能更好,例如用户中心
    SELECT * FROM user WHERE user_id=?
    SELECT * FROM user WHERE login_name=?
  • 原因:
    B-Tree索引的时间复杂度是O(log(n))
    Hash索引的时间复杂度是O(1)

  • 允许为null的列,查询有潜在大坑
    单列索引不存null值,复合索引不存全为null的值,如果列允许为null,可能会得到“不符合预期”的结果集 SELECT * FROM user WHERE user_name != ‘Chenyongjia’
    如果name允许为null,索引不存储null值,结果集中不会包含这些记录。
  • 所以,请使用not null约束以及默认值。

  • 复合索引最左前缀,并不是值SQL语句的WHERE顺序要和复合索引一致
  • 用户中心建立了(login_name, passwd)的复合索引
    SELECT * FROM user WHERE login_name=? AND user_passwd=?
    SELECT * FROM user WHERE user_passwd=? AND login_name=?
  • 都能够命中索引
    SELECT * FROM user WHERE login_name=?
  • 也能命中索引,满足复合索引最左前缀
    SELECT * FROM user WHERE user_passwd=?
  • 不能命中索引,不满足复合索引最左前缀

  • 使用ENUM而不是字符串
    ENUM保存的是TINYINT,别在枚举中搞一些“中国”“上海”“技术部”这样的字符串,字符串空间又大,效率又低。

小众但非常有用的SQL实践


看累了嘛!别松懈跟着博主继续学习。

  • 如果明确知道只有一条结果返回,LIMIT 1能够提高效率,这是为了使EXPLAIN中type列达到const类型
    SELECT * FROM user WHERE login_name=?
  • 可以优化为:
    SELECT * FROM user WHERE login_name=? LIMIT 1
  • 原因:
    你知道只有一条结果,但数据库并不知道,明确告诉它,让它主动停止游标移动

  • 把计算放到业务层而不是数据库层,除了节省数据的CPU,还有意想不到的查询缓存优化效果
    SELECT * FROM order WHERE date < = CURDATE()
  • 这不是一个好的SQL实践,应该优化为:
    $curDate = date(‘Y-m-d’);
    $res = mysql_query(
    ‘SELECT * FROM order WHERE date < = $curDate’);
  • 原因:
    释放了数据库的CPU
  • 多次调用,传入的SQL相同,才可以利用查询缓存

  • 强制类型转换会全表扫描
    SELECT * FROM user WHERE user_phone=15638589820
  • 你以为会命中phone索引么?大错特错了,这个语句究竟要怎么改?

注意:以上SQL语句尽量全部用大写不要问我为什么哈哈!

MySQL无法使用索引的情况总结


  • 字段使用函数,将无法使用索引 (2)Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引
  • 复合索引的情况下,如果查询条件不包含索引列的最左边部分,即不满足最左前缀原则,则不会使用索引
  • 如果mysql估计使用索引扫描比全表扫描更慢,则不使用索引。(扫描数据超过30%,都会走全表)
  • 以%开头的like查询
  • 数据类型出现隐式转换的时候也不会使用索引,特别是当列类型是字符串,那么一定记得在where条件中把字符串常量值用引号引起来,否则即便这个列上有索引,MySQL也不会用到,因为MySQL默认把输入的常量值进行转换以后才进行检索
  • 用or分割开的条件,如果 or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到

总结:

  • 关于MySql的优化需要各位小伙伴们在实际开发过程中去运用去总结。
  • 希望各位小伙伴站在前人的肩膀上可以走的更高更远!
  • 喜欢我的小伙伴记得加关注、点赞哦!
  • 5
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 5
    评论
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

陈永佳

你的鼓励将是我创作的最大动力!

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值