搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > 程序媛工作日记 > 这一份MySQL文章,可以帮你搞定99%以上的面试题!

这一份MySQL文章,可以帮你搞定99%以上的面试题!

程序媛工作日记 2020-05-23

提起mysql,首先必不可少的就是图形化开发工具navicat了。俗话说工欲善其事,必先利其器。对于Navicat Premium12下载安装及汉化过程如下。

开发工具navicat

安装:

搜索https://xclient.info/网址下载Navicat Premium12安装即可。

汉化:

安装TNT版主程序

下载 https://www.pipipan.com/fs/13114864-217250713 中文包

安装成功之后复制到汉化文件夹到

 /Applications/Navicat Premium.app/Contents/Resources

汉化完成。

数据库设计规范

  • 数据库对象名称使用小写字母,如果是有两个单词组成的,要用下划线将两个单词连接起来

  • 所有存储相同数据的列名和类型必须一致,比如user表的id和order表的user_id要一致

  • 所有字段都要注释

数据库设计三范式

  • 第一范式1NF:字段原子性,字段原子性,字段不可再分割。

  • 第二范式:消除对主键的部分依赖,即在表中加上一个与业务逻辑无关的字段(id)作为主键

依赖:A字段可以确定B字段,则B字段依赖A字段。比如知道了下一节课是数学课,就能确定任课老师是谁。于是周几下一节课和就能构成复合主键,能够确定去哪个教室上课,任课老师是谁等。但我们常常增加一个id作为主键,而消除对主键的部分依赖。

  • 第三范式:消除对主键的传递依赖,独立数据独立建表

传递依赖:B字段依赖于A,C字段又依赖于B。比如上例中,任课老师是谁取决于是什么课,是什么课又取决于主键id。因此需要将此表拆分为两张表日程表和课程表(独立数据独立建表):

索引

数据结构角度

  1. B+tree索引:多路平衡查找树木,是在b+tree基础上延伸来的,是基于233树的,顶级节点是存储在页上的,主键key的长度越短,能存放的key越多,key越多,分叉就越多,层级结构就越低,查找的效率就越高;数据都放在叶子节点中,其他节点放的都是索引,这样能存放的索引就越多,层级结构就越低,io操作就越少,查找效率就越高;叶子节点形成有序链表,范围查找方便,从而减少IO次数。

  2. hash索引:等值查询,hash值找到指针,然后根据指针找到具体的值。

  3. fulltext索引:关键词查找。

存储角度

  • 聚集索引:逻辑顺序和物理顺序相同。MySQL里面的主键就是聚集索引。

  • 非聚集索引:逻辑顺序和物理顺序不同。首先检索非聚集索引获得主键,然后用主键到聚集索引中获得记录

逻辑角度

  1. primary key 主键索引

  2. unqiue key  唯一索引

  3. normal key  索引

  4. 单列索引

  5. 组合索引

  6. 覆盖索引

tip

唯一索引和普通索引对查询和插入的效率有什么影响?

查询:唯一索引命中1条数据;普通索引命中n条数据,查询效率不高。

插入:插入的时候并不是直接到数据库,先到缓存。唯一索引,先判断索引是否唯一,索引所在的数据页找到,如果这个数据页不在内存里还得先把数据页加载到内存,然后再拿出来比较如果没有的话写进去;普通索引直接写到内存里面去。

建立索引

select,update,delete语句中的where从句中的列
包含在
order by,group by,distinct中的字段
多表
join的关联列

多个单列索引再多个查询条件时只会生效一个索引。多条件查询时最好建立组合索引

避免索引失效

  • 最左前缀法则

  • 不在索引上做任何操作,函数,类型转换(自动or手动)

  • 尽量使用覆盖索引,索引列和查询列一致

  • like%开头会索引失效 如果非要加在左边,用覆盖索引解决

  • 字符串不加单引号索引失效,会自动类型转换

  • or,is null,!=会导致索引失效

索引优化

explain,先看访问类型type一般来说,得保证查询至少达到range级别,最好能达到ref。all为全表扫描,再看key列,是否用到索引,再看 rows列,扫描的行数

  • 用连接查询代替子查询

  • or可以用union all来代替

  • 不要用select *

  • in先查询子表,当子表大的时候可以用exists代替,小表驱动大表

  • not exists速度更快,not in没有用到索引

  • 建表,用更小的符合要求的数据类型

项目中的sql 调优

1.加索引,单个索引改成复合索引,单个索引是多个索引取交集,复合索引是在一个索引的基础上再按照其他索引的要求取。

2.索引列上加函数,索引失效

惨例:在索引列上加函数操作,导致不走索引;(有个需求是保留最近7天的数据,时间为索引列,我是在时间那加了7天的函数导致索引失效)

解决方法: 分成两步来查,将运算放在service层,先查出最近7天第7天的那个最大的id,(主键id);然后根据id删除对应的数据。

3.force index强制走索引

4.not in 和not exists错误使用

当你想对两个表进行差集操作的时候,可以配合子查询,使用not exists或not in,但是有个很重要的区别是,如果在子查询的结果里返回了null,not in 子句会执行失败,因为NULL和任何值都不相等。

事务

并发事务带来的问题

  • 丢失更新:当两个事务基于最初选定的值进行更改,最后的更新就覆盖了前一个

  • 脏读:A读到了B已经修改但未提交的数据。如果此时B事务回滚,则A读取的数据无效,不符合一致性

  • 不可重复读:一个事务在读取某些数据后的某个时间,再此读取以前读过的数据,却发现其读出的数据已经发生了变化,或某些记录已经被删除。

  • 幻读:一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满 足其查询条件的新数据

事务的隔离级别

  • 读未提交

  • 读已提交:保证对读取到的记录加锁(记录锁),存在幻读现象。

  • 可重复读:同时保证对读取的范围加锁,新的满足条件的记录不能插入(间隙锁),不存 在幻读

  • 串行化: 读写冲突

  • 乐观锁:每次获取数据的时候,都不会担心数据被修改,所以每次获取数据的时候都不会 进行加锁,但是在更新数据的时候需要判断该数据是否被别人修改过。如果数据被其他线程修改,则不进行数据更新,否则,更新数据。由于数据没有进行加锁,期间该数据可以被其他线程进行读写操作。

判断数据是否更改过一般使用versionCAS操作方法

1. version方式:在数据库表中加一个数据版本号字段,标识数据被修改的次数,当数据被修改时,version的值会加1,当线程A要更新数据值时,在读取数据的同时也会读取version值,在提交更新时若刚才读取到的version值为当前数据库中的version值相等时才更新,否则重试直至更新成功。

2. 适用于读取操作比较频繁的场景

  • 悲观锁:每次获取数据的时候,都会担心数据被修改,所以每次获取数据的时候都会进行加锁,确保在自己使用的过程中数据不会被别人修改,使用完成后进行数据解锁。比如synchronize

  • 共享锁:又称读锁,简称S锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。

  • 排他锁:又称写锁,简称X锁,排他锁就是不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据进行读取和修改。

  • update,delete,insert都会自动给涉及的数据加上排他锁,select语句默认不会加任何锁类型,如果加共享锁可以使用select...lock in share mode语句,加排他锁可以使用select...for update。加过排他锁的数据行在其他事务中是不能修改数据的,也不能通过for updatelock in share mode锁的方式查询数据,但可以直接通过select ...from...查询 数据,因为普通的查询没有任何锁机制。

  • 意向锁:InnoDB为了让表锁和行锁共存使用了意向锁。

例子:事务A锁住表中的一行(写锁)。事务B锁住整个表(写锁)。很明显这是冲突的,所 以,没有意向锁的时候,行锁和表锁共存就会存在问题。有了意向锁,事务A在申请行锁(写 锁)之前,数据库自动先给事务A申请表的意向排他锁。当事务B去申请表的写锁时就会失败, 因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。意向锁是表锁。





版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《这一份MySQL文章,可以帮你搞定99%以上的面试题!》的版权归原作者「程序媛工作日记」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注程序媛工作日记微信公众号

程序媛工作日记微信公众号:gh_9f015846d44b

程序媛工作日记

手机扫描上方二维码即可关注程序媛工作日记微信公众号

程序媛工作日记最新文章

精品公众号随机推荐