当前位置:首页 > php > MySQL~从引擎选择、表的设计、索引设计、sql语句和连接池这五个方面优化数据库

MySQL~从引擎选择、表的设计、索引设计、sql语句和连接池这五个方面优化数据库

一叶知秋2024-05-12 04:53:34php2

innodb是一个表对应一个文件(使用innodb存储引擎使用表ibdata1来保存数据和索引, .frm存储表结构)

InnoDB 和 MyISAM区别:

1、InnoDB支持主外键、事务;

2、InnoDB是行锁,操作时候只锁一行数据,适合高并发;MyISAM是表索;

3、InnoDB不仅缓存索引,还缓存真实数据;MyISAM只缓存索引;

4、InnoDB需要表空间大;

5、InnoDB关注事务,MyISAM关注性能(查)

使用选择

  • MYISAM小巧玲珑, 节约空间, 速度快

  • INNODB安全性好, 有事务的处理和多表多用户的操作

行锁升级表锁


事务的隔离级别为:可重复读时,

如果有索引(包括主键索引),以索引列为条件更新数据,会存在间隙锁,行锁,页锁,而锁住一些行。

如果没有索引,更新数据时会锁住整张表。

事务隔离级别为:串行化时,读写数据都会锁住整张表。(一次只能一个连接玩表)

众多资料中都说innodb使用的是行级锁,但实际上是有限制的。只有在你增删改查时匹配的条件字段带有索引时,innodb才会使用行级锁,在你增删改查时匹配的条件字段不带有索引时,innodb使用的将是表级锁。因为当你匹配条件字段不带有所引时,数据库会全表查询,所以这需要将整张表加锁,才能保证查询匹配的正确性。在生产环境中我们往往需要满足多人同时对一张表进行增删改查,所以就需要使用行级锁,所以这个时候一定要记住为匹配条件字段加索引。

提到行级锁和表级锁时我们就很容易联想到读锁和写锁,因为只有触发了读写锁,我们才会谈是进行行级锁定还是进行表级锁定。那么什么时候触发读锁,就是在你用select 命令时触发读锁,什么时候触发写锁,就是在你使用update,delete,insert时触发写锁,并且使用rollback或commit后解除本次锁定。

表的设计


  • 表的设计优化就脱离不开那三个范式

将数据规范化

  • 防止信息重复

  • 防止数据更新异常

  • 防止数据插入异常, 也就是无法显示信息

  • 防止数据的删除异常,也就是丢失有效的信息

第一第二第三范式

  • 第一范式就是原子性, 保存每一列不可再次分割

  • 第二范式是必须先满足第一范式, 增加的是表的原子性, 也就是这张表只能专注于干一件事

  • 第三范式必须满足第一和第二范式, 增加的是每一列中的数据都要和主键直接关联

但是,必须要知道的规范性和性能问题有时候是不可兼得的, 我们一般关联查询的表不能超过三张表

  • 考虑商业化的需求和目标的时候, (成本和用户体验)数据库的性能是更加重要的

  • 所以在规范的时候,需要适当的考虑下性能问题

  • 如果只是单纯的分割表来增加表的数据,会造成一些冗余字段(从单表变成多表查询)

  • 并且有时候在单表中增加索引,会提高查询的效率,但是也让表变得更加的沉重

  • 所以在表的设计上考虑表的范式优化, 又得考虑不能太过分

索引优化


  • 索引是帮助高效获取数据的数据结构

  • 所以索引优化就是在列上使用适当的索引

  • MySql索引类型有:唯一索引,B+树索引(主键(聚集)索引,非聚集索引),全文索引, 联合索引(复合索引)

索引分类


  • 主键索引(Promary Key) , 聚集索引

    • 唯一的标识,主键不可重复, 只能有一个列作为主键
  • 非聚集索引

    • 使用非聚集索引是俩次查, 有回表问题, 可以使用联合索引代理非聚集索引
  • 联合索引

    • 联合索引就是使用索引覆盖, 在非聚集索引上就可以找到数据. 但是会有最左匹配原则, 也就是只要第一个索引没有匹配到, 就会导致索引失效
  • 唯一索引(Unique key)

    • 这个唯一是保证列的唯一,避免重复列的出现,唯一索引可以重复,多个列都可以标识唯一索引
  • 常规索引(key/index)(默认的)

    • 默认的索引, index或者key 关键字设置
  • 全文索引(FullText)

    • 在特定的数据库引擎下才有, 比如MySAM, 但是现在高版本的InnoDB中也有全文索引了
  • 目的是为了快速查找数据比如text数据

索引数据结构

  • 不使用hash表是因为不方便进行范围查找

  • 不使用二叉树的原因是树的深度太高了

  • 不使用b树的原因是有更好的b+树

  • 首先要知道在b树和b+树中, 数据都是放在叶子上的, 而且已过父节点可以有多个子节点

  • b树虽然已过节点可以有多个分支, 树的深度降低不少, 但是在进行范围查找的时候还是不如b+树

  • 因为b+树在叶子节点上是连续的

索引使用原则

  • 索引不是越多越好, 他只会提高查找效率, 删除修改, 增加的效率只会降低

  • 不要给经常变更的数据加索引

  • 小数据量有没有必要加索引

  • 索引一般用在查询上, 在增加删除修改方便索引的效率是降低的, 所以用在差多改少的情况

  • 如果某一个字段选择性很少, 比如性别, 类型, 他们的取值范围很小, 也不适合添加索引

sql语句优化


  1. 排序的索引问题
  • mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
  1. like语句操作
  • 一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 和like “%aaa” 不会使用索引而like “aaa%”可以使用索引。
  1. 在SQL文里面对某个字段经过运算后再与一个常量比较:那么这将会对运行性能产生很大的影响:

请看下面SQL的执行效率:

select * from iroomtypeprice where amount/30< 1000(11秒)

当改写成下面的SQL语句时效率明显提高了:

select * from iroomtypeprice where amount< 1000*30(<1秒)

语句1因为要对没没条记录的字段amount做一个/的运算,所以必须进行全表扫描,表的合适索引不会起作用;而语句二就会用到表上的合适索引。因此效率明显提高。

  • 另外,在where后面尽量少用substring等函数,这样也可以提高执行效率,如:

select * from iroomtypeprice where substring(card_no,1,4)=‘5378’(13秒)改成

select * from iroomtypeprice where card_no like ‘5378%’(<1秒)

  1. 不使用NOT IN和<>操作
  • NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id<>3则可使用id>3 or id<3来代替。
  1. 使用连接(JOIN)来代替子查询(Sub-Queries)
  • 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)…替代。如果使用连接(JOIN)…来完成这个查询工作,速度将会快很多。
  1. 尽量使用覆盖索引,只访问索引的查询(索引列和查询列一致),减少selec *

  2. 存在NULL值条件,我们在设计数据库表时,索引列应该尽力避免NULL值出现,所以给索引类要加not null 或者unique,如果非要不可避免的要出现NULL值,也要给一个DEFAULT值,数值型可以给0、-1之类的, 字符串有时候给空串有问题,就给一个空格或其他。如果索引列是可空的,是不会给其建索引的,索引值是少于表的count(*)值的,所以这种情况下,执行计划自然就去扫描全表了。

最后

自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。

深知大多数Java工程师,想要提升技能,往往是自己摸索成长,自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。

既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,不论你是刚入门Android开发的新手,还是希望在技术上不断提升的资深开发者,这些资料都将为你打开新的学习之门!

如果你觉得这些内容对你有帮助,需要这份全套学习资料的朋友可以戳我获取!!

由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,不论你是刚入门Android开发的新手,还是希望在技术上不断提升的资深开发者,这些资料都将为你打开新的学习之门!**

如果你觉得这些内容对你有帮助,需要这份全套学习资料的朋友可以戳我获取!!

由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!

扫描二维码推送至手机访问。

版权声明:本站部分文章来自互联网采集,请查看免责申明

本文链接:https://blog.yyzq.team/post/338289.html

分享给朋友:

发表评论

访客

看不清,换一张

◎欢迎参与讨论,请在这里发表您的看法和观点。