视频网站建设成本,长沙seo公司排名,汽车租赁企业网站源码,wordpress难学吗#x1f4aa; 目录#x1f4aa;1、什么是ER图2、数据库范式了解吗3、超键、候选键、主键、外键分别是什么#xff1f;4、为什么不推荐使用外键与级联5、什么是存储过程6、drop、delete与truncate区别7、数据库设计通常分为那几步8、什么是关系型数据库9、什么是SQL10、MySQL… 目录1、什么是ER图2、数据库范式了解吗3、超键、候选键、主键、外键分别是什么4、为什么不推荐使用外键与级联5、什么是存储过程6、drop、delete与truncate区别7、数据库设计通常分为那几步8、什么是关系型数据库9、什么是SQL10、MySQL有什么优点11、一个SQL语句在MySQL中的执行流程12、MySQL支持哪些存储引擎默认使用哪个13、MyISAM和InnoDB有什么区别14、MyISAM和InnoDB如何选择15、MySQL查询缓存16、MySQL事务17、SQL标准定义了哪些事务隔离级别18、并发事务带来了哪些问题什么是脏读、幻读、不可重复读19、事务的实现原理20、MySQL的 redo logundo logbin log 都是干什么的21、并发事务的控制方式有哪些22、什么是MVCC23、MVCC的实现原理24、按照锁的粒度分数据库锁有哪些25、从锁的类别上分MySQL都有哪些锁呢26、数据库的乐观锁和悲观锁是什么怎么实现的27、行级锁的使用有什么注意事项28、InnoDB有哪几类行锁29、意向锁有什么作用30、索引介绍31、索引的底层数据结构31、MySQL有哪几种索引类型32、主键索引和二级索引(辅助索引)33、聚簇索引和非聚簇索引34、非聚簇索引一定会回表查询吗35、覆盖索引和联合索引36、讲一讲MySQL的最左前缀匹配原则37、怎么知道创建的索引有没有被使用到或者说怎么才可以知道这条语句运行很慢的原因38、如何正确使用索引39、什么情况下索引会失效即查询不走索引40、能用MySQL直接存储文件(比如图片)吗41、MySQL如何存储IP地址42、如何分析SQL的性能43、查询性能的优化方法44、MySQL中的varchar和char有什么区别45、varchar(10)和int(10)代表什么含义46、关心过业务系统里面的sql耗时吗统计过慢查询吗对慢查询都怎么优化过47、select语句的优化48、什么是索引下推 1、什么是ER图
ER 图 全称是 Entity Relationship Diagram实体联系图提供了表示实体类型、属性和联系的方法。 实体 通常是现实世界的业务对象当然使用一些逻辑对象也可以。比如对于一个校园管理系统会涉及学生、教师、课程、班级等等实体。在 ER 图中实体使用矩形框表示。 属性 即某个实体拥有的属性属性用来描述组成实体的要素对于产品设计来说可以理解为字段。在 ER 图中属性使用椭圆形表示。 联系 即实体与实体之间的关系这个关系不仅有业务关联关系还能通过数字表示实体之间的数量对照关系。例如一个班级会有多个学生就是一种实体间的联系。
2、数据库范式了解吗
首先回答范式的作用
消除重复数据减少冗余数据从而让数据库内的数据能划分的更合理让磁盘空间得到更有效利用的一种标准化标准消除潜在的异常插入异常更新异常删除异常
数据库范式有 3 种
1NF(第一范式)属性不可再分。要求数据库表的每一列都是不可分割的原子数据项。2NF(第二范式)1NF 的基础之上消除了非主属性对于码的部分函数依赖。在满足第一范式的前提下每张表只描述一件事情3NF(第三范式)3NF 在 2NF 的基础之上消除了非主属性对于码的传递函数依赖 。在满足第一和第二范式的前提下第三范式需要确保数据表中的每一列数据都和主键直接相关而不能间接相关。
第一范式1NF
属性对应于表中的字段不能再被分割也就是这个字段只能是一个值不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 也就是说关系型数据库中创建的表一定满足第一范式。
第二范式2NF
第二范式在第一范式的基础上增加了一个列这个列称为主键非主属性都依赖于主键。
第三范式3NF
3NF 在 2NF 的基础之上消除了非主属性对于码的传递函数依赖 。符合 3NF 要求的数据库设计基本上解决了数据冗余过大插入异常修改异常删除异常的问题。
最后回答性能与规范
阿里规范关联查询的表不得超过三张表越符合三大范式则表会越来越多表多了不一定是好事查询时需要连接多个表增加查询复杂度降低数据库的查询性能。因此并不是应用的范式越高越好要看实际情况而定。我个人观点认为大多数情况应用到第三范式已经足够在一定情况下第二范式或第一范式也是可以的。甚至有时为了提高运行效率可以让数据冗余(比如数据表里存放了语文数学英语成绩但是如果在某个时间经常要得到它的总分每次都要进行计算会降低性能不如加上总分这个冗余字段)
3、超键、候选键、主键、外键分别是什么 超键在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键多个属性组合在一起也可以作为一个超键。超键包含候选键和主键 候选键是最小超键即没有冗余元素的超键 主键(主码) 主键用于唯一标识一个元组(行)不能有重复不允许为空。一个表只能有一个主键。 外键(外码) 外键用来和其他表建立联系用外键是另一表的主键外键是可以有重复的可以是空值。一个表可以有多个外键
4、为什么不推荐使用外键与级联
对于外键和级联阿里巴巴开发手册这样说到 【强制】不得使用外键与级联一切外键概念必须在应用层解决。 说明: 以学生和成绩的关系为例学生表中的 student_id 是主键那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id同时触发成绩表中的 student_id 更新即为级联更新。外键与级联更新适用于单机低并发不适合分布式、高并发集群; 级联更新是强阻塞存在数据库更新风暴的风险; 外键影响数据库的插入速度
为什么不要用外键呢
增加了复杂性 a. 每次做 DELETE 或者 UPDATE 都必须考虑外键约束会导致开发的时候很痛苦。外键的主从关系是定的假如哪天需求有变化数据库中的这个字段根本不需要和其他表有关联的话就会增加很多麻烦。对分库分表不友好 因为分库分表下外键是无法生效的。
但是外键存在即合理外键也是有好处的比如级联操作方便减轻了程序代码量所以如果系统不涉及分库分表并发量不是很高的情况还是可以考虑使用外键的。
5、什么是存储过程
存储过程是一些预编译的 SQL 语句。
更加直白的理解存储过程可以说是一个记录集它是由一些 T-SQL 语句组成的代码块这些 T-SQL 语句代码像一个方法一样实现一些功能对单表或多表的增删改查然后再给这个代码块取一个名字在用到这个功能的时候调用他就行了存储过程是一个预编译的代码块执行效率比较高,一个存储过程替代大量 T_SQL 语句 可以降低网络通信量提高通信速率,可以一定程度上确保数据安全
但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java 开发手册》中禁止使用存储过程。我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员流动相比于传统的项目也更加频繁,在这样的情况下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在服务层那么好。
6、drop、delete与truncate区别
drop(丢弃数据): drop table 表名 直接将表都删除掉在删除表的时候使用truncate (清空数据) : truncate table 表名 只删除表中的数据再插入数据的时候自增长 id 又从 1 开始在清空表中数据的时候使用delete删除数据 : delete from 表名 where 列名值删除某一行的数据如果不加 where 子句和truncate table 表名作用类似
truncate 和不带 where 子句的 delete、drop 都会删除表内的数据但是 truncate 和 delete 只删除数据不删除表的结构、执行 drop 语句此表的结构也会删除也就是执行 drop 之后对应的表不复存在。
此外truncate 和 drop 属于 DDL(数据定义语言)语句操作立即生效而 delete 语句是 DML (数据库操作语言)语句事务提交之后才生效。
DeleteTruncateDrop类型属于DML属于DDL属于DDL回滚可回滚不可回滚不可回滚删除内容表结构还在删除表的全部或者一部分数据行表结构还在删除表中的所有数据从数据库中删除表所有的数据行、索引和权限也会被删除删除速度删除速度慢需要逐行删除删除速度快删除速度最快
DML 语句和 DDL 语句区别
DML 是数据库操作语言Data Manipulation Language的缩写是指对数据库中表记录的操作主要包括表记录的插入、更新、删除和查询是开发人员日常使用最频繁的操作。DDL Data Definition Language是数据定义语言的缩写简单来说就是对数据库内部的对象进行创建、删除、修改的操作语言。DDL 语句更多的被数据库管理员DBA所使用一般的开发人员很少使用
另外由于select不会对表进行破坏所以有的地方也会把select单独区分开叫做数据库查询语言 DQLData Query Language
一般来说执行速度上drop truncate delete
drop命令会把表占用的空间全部释放掉truncate命令执行的时候不会产生数据库日志因此比delete要快。除此之外还会把表的自增值重置和索引恢复到初始大小等。delete命令执行的时候会产生数据库的binlog日志而日志记录是需要消耗时间的但是也有个好处方便数据回滚恢复
7、数据库设计通常分为那几步
需求分析 : 分析用户的需求包括数据、功能和性能需求概念结构设计 : 主要采用 E-R 模型进行设计包括画 E-R 图逻辑结构设计 : 通过将 E-R 图转换成表实现从 E-R 模型到关系模型的转换物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径数据库实施 : 包括编程、测试和试运行数据库的运行和维护 : 系统的运行与数据库的日常维护
8、什么是关系型数据库
关系型数据库 RDBMSRelational Database Management System就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系一对一、一对多、多对多。关系型数据库中我们的数据都被存放在了各种表中比如用户表表中的每一行就存放着一条数据比如一个用户的信息。
常见的关系型数据库MySQL、Oracle、SQL Server
9、什么是SQL
SQL 是一种结构化查询语言(Structured Query Language)专门用来与数据库打交道目的是提供一种从数据库中读写数据的简单有效的方法。几乎所有的主流关系数据库都支持 SQL 适用性非常强。并且一些非关系型数据库也兼容 SQL 或者使用的是类似于 SQL 的查询语言。
10、MySQL有什么优点
成熟稳定功能完善开源免费文档丰富既有详细的官方文档又有非常多优质文章可供参考学习开箱即用操作简单维护成本低兼容性好支持常见的操作系统支持多种开发语言社区活跃生态完善事务支持优秀 InnoDB 存储引擎默认使用 repeatable-read 并不会有任何性能受损失并且InnoDB 实现的 repeatable-read 隔离级别其实是可以解决幻读问题发生的。支持分库分表、读写分离、高可用
11、一个SQL语句在MySQL中的执行流程 大体来说MySQL 可以分为 Server 层和存储引擎两部分。
连接器 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存 执行查询语句的时候会先查询缓存MySQL 8.0 版本后移除因为这个功能不太实用。
分析器 没有命中缓存的话SQL 语句就会经过分析器分析器说白了就是要先看你的 SQL 语句要干嘛再检查你的 SQL 语句语法是否正确。
优化器 按照 MySQL 认为最优的方案去执行。
执行器 执行语句然后从存储引擎返回数据。 执行语句之前会先判断是否有权限如果没有权限的话就会报错。
插件式存储引擎 主要负责数据的存储和读取采用的是插件式架构支持 InnoDB、MyISAM、Memory 等多种存储引擎
12、MySQL支持哪些存储引擎默认使用哪个
MySQL 支持多种存储引擎你可以通过 show engines 命令来查看 MySQL 支持的所有存储引擎。MySQL 当前默认的存储引擎是 InnoDB。并且所有的存储引擎中只有 InnoDB 是事务性存储引擎也就是说只有 InnoDB 支持事务。
13、MyISAM和InnoDB有什么区别
MySQL 5.5 之前MyISAM 引擎是 MySQL 的默认存储引擎可谓是风光一时。MySQL 5.5 版本之后InnoDB 是 MySQL 的默认存储引擎。 InnoDB 支持事务MyISAM 不支持 InnoDB 支持外键而 MyISAM 不支持 InnoDB 是聚集索引数据文件是和索引绑在一起的必须要有主键通过主键索引效率很高MyISAM 是非聚集索引数据文件是分离的索引保存的是数据文件的指针主键索引和辅助索引是独立的。 Innodb 不支持全文索引而 MyISAM 支持全文索引查询效率上 MyISAM 要高 InnoDB 支持行级别的锁粒度MyISAM 不支持只支持表级别的锁粒度。也就说MyISAM 一锁就是锁住了整张表这在并发写的情况下是多么滴憨憨啊这也是为什么 InnoDB 在并发写的时候性能更牛皮了 MyISAM 不提供事务支持。InnoDB 提供事务支持实现了 SQL 标准定义了四个隔离级别具有提交(commit)和回滚(rollback)事务的能力。并且InnoDB 默认使用的 REPEATABLE-READ可重读隔离级别是可以解决幻读问题发生的。 InnoDB 的性能比 MyISAM 更强大。
14、MyISAM和InnoDB如何选择
大多数时候我们使用的都是 InnoDB 存储引擎在某些读密集的情况下使用 MyISAM 也是合适的。不过前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点可是~我们一般都会介意啊。
一般情况下我们选择 InnoDB 都是没有问题的但是某些情况下你并不在乎可扩展能力和并发能力也不需要事务支持也不在乎崩溃后的安全恢复问题的话选择 MyISAM 也是一个不错的选择。但是一般情况下我们都是需要考虑到这些问题的。
因此对于咱们日常开发的业务系统来说你几乎找不到什么理由再使用 MyISAM 作为自己的 MySQL 数据库的存储引擎
15、MySQL查询缓存
执行查询语句的时候会先查询缓存。不过MySQL 8.0 版本后移除因为这个功能不太实用。缓存虽然能够提升数据库的查询性能但是缓存同时也带来了额外的开销每次查询后都要做一次缓存操作失效后还要销毁。 因此开启查询缓存要谨慎尤其对于写密集的应用来说更是如此。
16、MySQL事务
事务是逻辑上的一组操作要么都执行要么都不执行。数据库事务可以保证多个对数据库的操作也就是 SQL 语句构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循要么全部执行成功,要么全部不执行 。
事务具有ACID特性
原子性Atomicity 事务是最小的执行单位不允许分割。事务的原子性确保动作要么全部完成要么完全不起作用一致性Consistency 执行事务前后数据保持一致例如转账业务中无论事务是否成功转账者和收款人的总额应该是不变的隔离性Isolation 并发访问数据库时一个用户的事务不被其他事务所干扰各并发事务之间数据库是独立的持久性Durability 一个事务被提交之后。它对数据库中数据的改变是持久的即使数据库发生故障也不应该对其有任何影响。
只有保证了事务的持久性、原子性、隔离性之后一致性才能得到保障。也就是说 A、I、D 是手段C 是目的 MySQL怎么保证原子性的 我们知道如果想要保证事务的原子性就需要在异常发生时对已经执行的操作进行回滚在 MySQL 中恢复机制是通过 回滚日志undo log 实现的所有事务进行的修改都会先记录到这个回滚日志中然后再执行相关的操作。如果执行过程中遇到异常的话我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可并且回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况当用户再次启动数据库的时候数据库还能够通过查询回滚日志来回滚之前未完成的事务 17、SQL标准定义了哪些事务隔离级别
SQL 标准定义了四个隔离级别
READ-UNCOMMITTED(读取未提交) 最低的隔离级别允许读取尚未提交的数据变更可能会导致脏读、幻读或不可重复读READ-COMMITTED(读取已提交) 允许读取并发事务已经提交的数据可以阻止脏读但是幻读或不可重复读仍有可能发生REPEATABLE-READ(可重复读) 对同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改可以阻止脏读和不可重复读但幻读仍有可能发生SERIALIZABLE(可串行化) 最高的隔离级别完全服从 ACID 的隔离级别。所有的事务依次逐个执行这样事务之间就完全不可能产生干扰也就是说该级别可以防止脏读、不可重复读以及幻读
MySQL 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别
18、并发事务带来了哪些问题什么是脏读、幻读、不可重复读
在典型的应用程序中多个事务并发运行经常会操作相同的数据来完成各自的任务多个用户对同一数据进行操作。并发虽然是必须的但可能会导致以下的问题
脏读Dirty read
事务 A 读取了事务 B 更新的数据然后 B 回滚操作那么 A 读取到的数据是脏数据
例如事务 1 读取某表中的数据 A20事务 1 修改 AA-1事务 2 读取到 A 19,事务 1 回滚导致对 A 的修改并为提交到数据库 A 的值还是 20
丢失修改
在一个事务读取一个数据时另外一个事务也访问了该数据那么在第一个事务中修改了这个数据后第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失因此称为丢失修改。
例如事务 1 读取某表中的数据 A20事务 2 也读取 A20事务 1 先修改 AA-1事务 2 后来也修改 AA-1最终结果 A19事务 1 的修改被丢失。
不可重复读
事务 A 多次读取同一数据事务 B 在事务 A 多次读取的过程中对数据作了更新并提交导致事务 A 多次读取同一数据时结果 不一致。
例如事务 1 读取某表中的数据 A20事务 2 也读取 A20事务 1 修改 AA-1事务 2 再次读取 A 19此时读取的结果和第一次读取的结果不同。
幻读
系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级但是系统管理员 B 就在这个时候插入了一条具体分数的记录当系统管理员 A 改结束后发现还有一条记录没有改过来就好像发生了幻觉一样这就叫幻读。
例如事务 2 读取某个范围的数据事务 1 在这个范围插入了新的数据事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。 不可重复读侧重于修改幻读侧重于新增或删除多了或少量行脏读是一个事务回滚影响另外一个事务。 19、事务的实现原理
事务是基于重做日志文件(redo log)和回滚日志(undo log)实现的。
每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化数据库就可以通过重做日志来保证事务的原子性和持久性。
每当有修改事务时还会产生 undo log如果需要回滚则根据 undo log 的反向语句进行逻辑操作比如 insert 一条记录就 delete 一条记录。undo log 主要实现数据库的一致性。
20、MySQL的 redo logundo logbin log 都是干什么的
redo log是InnoDB引擎特有的只记录该引擎中表的修改记录。bin log是MySQL的Server层实现的会记录所有引擎对数据库的修改。redo log是物理日志记录的是在具体某个数据页上做了什么修改binlog是逻辑日志记录的是这个语句的原始逻辑。redo log是循环写的空间固定会用完binlog是可以追加写入的
redo log记录修改内容哪一页发生了什么变化写于事务开始前用于数据未落磁盘但数据库挂了后的数据恢复。bin log记录修改SQL写于事务提交时可用于读写分离。undo log记录修改前记录用于回滚和多版本并发控制
21、并发事务的控制方式有哪些
MySQL 中并发事务的控制方式无非就两种锁 和 MVCC。锁可以看作是悲观控制的模式多版本并发控制MVCCMultiversion concurrency control可以看作是乐观控制的模式。MySQL 中主要是通过 读写锁 来实现并发控制。
共享锁S 锁 又称读锁事务在读取记录的时候获取共享锁允许多个事务同时获取锁兼容。排他锁X 锁 又称写锁/独占锁事务在修改记录的时候获取排他锁不允许多个事务同时获取。如果一个记录已经被加了排他锁那其他事务不能再对这条事务加任何类型的锁锁不兼容
读写锁可以做到读读并行但是无法做到写读、写写并行。另外根据根据锁粒度的不同又被分为 表级锁(table-level locking) 和 行级锁(row-level locking)
InnoDB 不光支持表级锁还支持行级锁默认为行级锁。行级锁的粒度更小仅对相关的记录上锁即可对一行或者多行记录加锁所以对于并发写入操作来说 InnoDB 的性能更高。不论是表级锁还是行级锁都存在共享锁Share LockS 锁和排他锁Exclusive LockX 锁这两类。
MVCC 是多版本并发控制方法即对一份数据会存储多个版本通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号版本号是唯一的。
22、什么是MVCC
MVCC 即多版本并发控制。MVCC 的实现是通过保存数据在某个时间点的快照来实现的。根据事务开始的时间不同每个事务对同一张表同一时刻看到的数据可能是不一样的。
23、MVCC的实现原理
InnoDB 每一行数据都有一个隐藏的回滚指针用于指向该行修改前的最后一个历史版本这个历史版本存放在 undo log 中。如果要执行更新操作会将原记录放入 undo log 中并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时就是查询 undo log 中这行数据的最后一个历史版本。
MVCC 最大的好处是读不加锁读写不冲突极大地增加了 MySQL 的并发性。通过 MVCC保证了事务 ACID 中的 I隔离性特性。
24、按照锁的粒度分数据库锁有哪些
在关系型数据库中可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。
行级锁
行级锁是MySQL中锁定粒度最细的一种锁表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小但加锁的开销也最大。行级锁分为共享锁 和 排他锁。开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低并发度也最高。
表级锁 表级锁是MySQL中锁定粒度最大的一种锁表示对当前操作的整张表加锁它实现简单资源消耗较少被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁共享锁与表独占写锁排他锁 开销小加锁快不会出现死锁锁定粒度大发出锁冲突的概率最高并发度最低
页级锁
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。开销和加锁时间界于表锁和行锁之间会出现死锁锁定粒度界于表锁和行锁之间并发度一般
MyISAM和InnoDB存储引擎使用的锁
MyISAM采用表级锁InnoDB支持行级锁和表级锁默认为行级锁
25、从锁的类别上分MySQL都有哪些锁呢
从锁的类别上来讲有共享锁和排他锁。
共享锁: 又叫做读锁。 当用户要进行数据的读取时对数据加上共享锁。共享锁可以同时加上多个排他锁: 又叫做写锁。 当用户要进行数据的写入时对数据加上排他锁。排他锁只可以加一个他和其他的排他锁共享锁都相斥。
多个人同时读是可以接受的但是写只能一个人写。锁的粒度取决于具体的存储引擎InnoDB实现了行级锁页级锁表级锁。他们的加锁开销从大到小并发能力也是从大到小。
26、数据库的乐观锁和悲观锁是什么怎么实现的
数据库管理系统DBMS中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制乐观锁和悲观并发控制悲观锁是并发控制主要采用的技术手段。
悲观锁假定会发生并发冲突屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来直到提交事务。实现方式使用数据库中的锁机制乐观锁假设不会发生并发冲突只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来通过version的方式来进行锁定。实现方式乐观锁一般会使用版本号机制或CAS算法实现悲观锁适用于写比较多的情况乐观锁适用于写比较少的情况。
27、行级锁的使用有什么注意事项
InnoDB 的行锁是针对索引字段加的锁表级锁是针对非索引字段加的锁。
当我们执行 UPDATE、DELETE 语句时如果 WHERE条件中字段没有命中唯一索引或者索引失效的话就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到一定要多多注意
28、InnoDB有哪几类行锁
InnoDB 行锁是通过对索引数据页上的记录加锁实现的MySQL InnoDB 支持三种行锁定方式
记录锁 Record Lock属于单个行记录上的锁间隙锁 Gap Lock锁定一个范围不包括记录本身临键锁 Next-Key LockRecord Lock Gap Lock锁定一个范围包含记录本身主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录为了避免插入新记录需要依赖间隙锁
在 InnoDB 默认的隔离级别 REPEATABLE-READ 下行锁默认使用的是 Next-Key Lock。但是如果操作的索引是唯一索引或主键InnoDB 会对 Next-Key Lock 进行优化将其降级为 Record Lock即仅锁住索引本身而不是范围
29、意向锁有什么作用
如果需要用到表锁的话如何判断表中的记录没有行锁呢一行一行遍历肯定是不行性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。
意向锁是表级锁共有两种 意向共享锁Intention Shared LockIS 锁事务有意向对表中的某些记录加共享锁S 锁加共享锁前必须先取得该表的 IS 锁。 意向排他锁Intention Exclusive LockIX 锁事务有意向对表中的某些记录加排他锁X 锁加排他锁之前必须先取得该表的 IX 锁。
30、索引介绍
**索引是一种用于快速查询和检索数据的数据结构其本质可以看成是一种排序好的数据结构。**索引的作用就相当于书的目录。打个比方: 我们在查字典的时候如果没有目录那我们就只能一页一页的去找我们需要查的那个字速度很慢。如果有目录了我们只需要先去目录里查找字的位置然后直接翻到那一页就行了。
索引底层数据结构存在很多种类型常见的索引结构有: B 树 B树 和 Hash、红黑树。在 MySQL 中无论是 Innodb 还是 MyIsam都使用了 B树作为索引结构
索引优点
使用索引可以大大加快 数据的检索速度大大减少检索的数据量, 这也是创建索引的最主要的原因通过创建唯一性索引可以保证数据库表中每一行数据的唯一性
索引缺点
创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候如果数据有索引那么索引也需要动态的修改会降低 SQL 执行效率索引需要使用物理文件存储也会耗费一定空间
但是**使用索引一定能提高查询性能吗?**大多数情况下索引查询都是比全表扫描要快的。但是如果数据库的数据量不大那么使用索引也不一定能够带来很大提升。
31、索引的底层数据结构
Hash表哈希表是键值对的集合通过键(key)即可快速取出对应的值(value)因此哈希表可以快速检索数据。通过哈希算法我们可以快速找到 key 对应的 index找到了 index 也就找到了对应的 value。但是哈希算法有个 Hash 冲突 问题也就是说多个不同的 key 最后得到的 index 相同。通常情况下我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。
既然哈希表这么快为什么 MySQL 没有使用其作为索引的数据结构呢 主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询那 Hash 索引可就不行了。并且每次 IO 只能取一个。
SELECT * FROM tb1 WHERE id 500;在这种范围查询中优势非常大直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的难不成还要把 1 - 499 的数据每个都进行一次 hash 计算来定位吗?这就是 Hash 最大的缺点了
B树和B树B 树全称为 多路平衡查找树B 树是 B 树的一种变体。目前大部分数据库系统及文件系统都采用 BTree 或其变种 BTree 作为索引结构
B树和B树二者有何异同呢为什么InnoDB存储引擎选用B树而不是B树呢
B 树的所有节点既存放键(key) 也存放 数据(data)而 B树只有叶子节点存放 key 和 data其他内节点只存放 key。B 树的叶子节点都是独立的;B树的叶子节点有一条引用链指向与它相邻的叶子节点。B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找可能还没有到达叶子节点检索就结束了。而 B树的检索效率就很稳定了任何查找都是从根节点到叶子节点的过程叶子节点的顺序检索很明显。
31、MySQL有哪几种索引类型
从数据结构角度
树索引Hash索引
从物理存储角度
聚集索引并不是一种单独的索引类型而是一种数据存储方式。具体细节取决于不同的实现InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引和数据行非聚集索引不是聚簇索引就是非聚簇索引
从逻辑角度
普通索引即一个索引只包含单个列一个表可以有多个单列索引唯一索引索引列的值必须唯一但允许有空值复合索引多列值组成一个索引专门用于组合搜索其效率大于索引合并联合索引全文索引
32、主键索引和二级索引(辅助索引)
数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键并且主键不能为 null不能重复。
在 MySQL 的 InnoDB 的表中当没有显示的指定表的主键时InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段如果有则选择该字段为默认的主键否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
**二级索引又称为辅助索引是因为二级索引的叶子节点存储的数据是主键。也就是说通过二级索引可以定位主键的位置。**唯一索引普通索引前缀索引等索引属于二级索引。 唯一索引(Unique Key) 唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据但是允许数据为 NULL一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性而不是为了查询效率。 普通索引(Index) 普通索引的唯一作用就是为了快速查询数据一张表允许创建多个普通索引并允许数据重复和 NULL 前缀索引(Prefix) 前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引相比普通索引建立的数据更小 因为只取前几个字符 全文索引(Full Text) 全文索引主要是为了检索大文本数据中的关键字的信息是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引5.6 之后 InnoDB 也支持了全文索引
33、聚簇索引和非聚簇索引
聚簇索引聚集索引聚簇索引即索引结构和数据一起存放的索引并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引
聚簇索引的优点
查询速度非常快 聚簇索引的查询速度非常的快因为整个 B树本身就是一颗多叉平衡树叶子节点也都是有序的定位到索引的节点就相当于定位到了数据。相比于非聚簇索引 聚簇索引少了一次读取数据的 IO 操作。对排序查找和范围查找优化 聚簇索引对于主键的排序查找和范围查找速度非常快
缺点
依赖于有序的数据 因为 B树是多路平衡树如果索引的数据不是有序的那么就需要在插入时排序如果数据是整型还好否则类似于字符串或 UUID 这种又长又难比较的数据插入或查找的速度肯定比较慢更新代价大 如果对索引列的数据被修改时那么对应的索引也将会被修改而且聚簇索引的叶子节点还存放着数据修改代价肯定是较大的所以对于主键索引来说主键一般都是不可被修改的
非聚簇索引非聚簇索引即索引结构和数据分开存放的索引并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎不管主键还是非主键使用的都是非聚簇索引。非聚簇索引的叶子节点并不一定存放数据的指针因为二级索引的叶子节点就存放的是主键根据主键再回表查数据
优点更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了非聚簇索引的叶子节点是不存放数据的
缺点
依赖于有序的数据 跟聚簇索引一样非聚簇索引也依赖于有序的数据可能会二次查询(回表) 这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后可能还需要根据指针或主键再到数据文件或表中查询
注意MyISAM无论主键索引还是二级索引都是非聚簇索引而InnoDB的主键索引是聚簇索引二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引
34、非聚簇索引一定会回表查询吗
**非聚簇索引不一定回表查询。**这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回 表查询.
举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.
35、覆盖索引和联合索引
覆盖索引覆盖索引即需要查询的字段正好是索引的字段那么直接根据该索引就可以查到数据了而无需回表查询。
如主键索引如果一条 SQL 需要查询主键那么正好根据主键索引就可以查到主键。
再如普通索引如果一条 SQL 需要查询 namename 字段正好有索引 那么直接根据这个索引就可以查到数据也无需回表。
联合索引MySQL可以使用多个字段同时建立一个索引叫做联合索引。在联合索引中如果想要命中索引需要按照建立索引时的字段顺序挨个使用否则无法命中索引。
具体原因为MySQL使用索引时需要索引有序假设现在建立了nameageschool的联合索引那么索引的排序为: 先按照name排序如果name相同则按照age排序如果age的值也相等则按照school进行排序。
36、讲一讲MySQL的最左前缀匹配原则
最左前缀匹配原则在使用联合索引时MySQL 会根据联合索引中的字段顺序从左到右依次到查询条件中去匹配如果查询条件中存在与联合索引中最左侧字段相匹配的字段则就会使用该字段过滤一批数据直至联合索引中全部字段匹配完成或者在执行过程中遇到范围查询如 、、between 和 以%开头的like查询 等条件才会停止匹配。 所以我们在使用联合索引时可以将区分度高的字段放在最左边这也可以过滤更多数据 37、怎么知道创建的索引有没有被使用到或者说怎么才可以知道这条语句运行很慢的原因
使用 Explain 命令来查看语句的执行计划MySQL 在执行某个语句之前会将该语句过一遍查询优化器之后会拿到对语句的分析也就是执行计划其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引例如possilbe_key、key、key_len 等字段分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。
38、如何正确使用索引
首先选择合适的字段创建索引
不为 NULL 的字段 索引字段的数据应该尽量不为 NULL因为对于数据为 NULL 的字段数据库较难优化。如果字段频繁被查询但又避免不了为 NULL建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。被频繁查询的字段 我们创建索引的字段应该是查询操作非常频繁的字段被作为条件查询的字段 被作为 WHERE 条件查询的字段应该被考虑建立索引频繁需要排序的字段 索引已经排序这样查询可以利用索引的排序加快排序查询时间被经常频繁用于连接的字段 经常用于连接的字段可能是一些外键列对于外键列并不一定要建立外键只是说该列涉及到表与表的关系。对于频繁被连接查询的字段可以考虑建立索引提高多表连接查询的效率
被频繁更新的字段应该慎重建立索引虽然索引能带来查询上的效率但是维护索引的成本也是不小的。 如果一个字段不被经常查询反而被经常修改那么就更不应该在这种字段上建立索引了
尽可能的考虑建立联合索引而不是单列索引因为索引是需要占用磁盘空间的可以简单理解为每个索引都对应着一颗 B树。如果一个表的字段过多索引过多那么当这个表的数据达到一个体量后索引占用的空间也是很多的且修改索引时耗费的时间也是较多的。如果是联合索引多个字段在一个索引上那么将会节约很大磁盘空间且修改数据的操作效率也会提升
注意避免冗余索引冗余索引指的是索引的功能相同能够命中索引(a, b)就肯定能命中索引(a) 那么索引(a)就是冗余索引。如name,city 和name 这两个索引就是冗余索引能够命中前者的查询肯定是能够命中后者的 在大多数情况下都应该尽量扩展已有的索引而不是创建新索引
考虑在字符串类型的字段上使用前缀索引代替普通索引前缀索引仅限于字符串类型较普通索引会占用更小的空间所以可以考虑使用前缀索引带替普通索引
39、什么情况下索引会失效即查询不走索引
避免索引失效索引失效也是慢查询的主要原因之一常见的导致索引失效的情况有下面这些
使用 SELECT * 进行查询创建了组合索引但查询条件未准守最左匹配原则在索引列上进行计算、函数、类型转换等操作以 % 开头的 LIKE 查询比如 like %abc;使用不等于查询
40、能用MySQL直接存储文件(比如图片)吗
可以是可以直接存储文件对应的二进制数据即可。不过还是建议不要在数据库中存储文件会严重影响数据库性能消耗过多存储空间。可以选择使用云服务厂商提供的开箱即用的文件存储服务成熟稳定价格也比较低。数据库只存储文件地址信息文件由文件存储服务负责存储。
41、MySQL如何存储IP地址
可以将 IP 地址转换成整形数据存储性能更好占用空间也更小。
MySQL 提供了两个方法来处理 ip 地址
INET_ATON() 把 ip 转为无符号整型 (4-8 位)INET_NTOA() :把整型的 ip 转为地址
插入数据前先用 INET_ATON() 把 ip 地址转为整型显示数据时使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。
42、如何分析SQL的性能
我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后具体的执行方式。EXPLAIN 并不会真的去执行相关的语句而是通过 查询优化器 对语句进行分析找出最优的查询方案并显示对应的信息。
43、查询性能的优化方法
减少请求的数据量
只返回必要的列最好不要使用 SELECT * 语句只返回必要的行使用 LIMIT 语句来限制返回的数据缓存重复查询的数据
减少服务器端扫描的行数
最有效的方式是使用索引来覆盖查询
44、MySQL中的varchar和char有什么区别 char(n)固定长度类型比如订阅 char(10)当你输入”abc”三个字符的时候它们占的空间还是 10 个字节其他 7 个是空字节。 优点效率高缺点占用空间适用场景存储密码的 md5 值固定长度的使用 char 非常合适。 varchar(n)可变长度存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。
在检索效率上来讲,char varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char
45、varchar(10)和int(10)代表什么含义 varchar的10代表了申请的空间长度也是可以存储的数据的最大长度 int的10只是代表了展示的长度,不足10位以0填充。也就是说int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的只是在展示时按照长度展示
46、关心过业务系统里面的sql耗时吗统计过慢查询吗对慢查询都怎么优化过
在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们。
慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?
所以优化也是针对这三个方向来的
首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.
47、select语句的优化 避免出现 select * 首先select * 操作在任何类型数据库中都不是 一个好的SQL编写习惯使用select * 取出全部列会让优化器无法完成 索引覆盖扫描这类优化会影响优化器对执行计划的选择也会增加网络带宽消耗更会带来额 外的I/O,内存和CPU消耗 避免出现不确定结果的函数 特定针对主从复制这类业务场景。由于原理上从 库复制的是主库执行的语句使用如now()、rand()、sysdate()、current_user()等不确定结果 的函数很容易导致主库与从库相应的数据不一 致。另外不确定值的函数,产生的SQL语句无法利用query cache 多表关联查询时小表在前大表在后 使用表的别名当在SQL语句中连接多个表时请使用表的别名 并把别名前缀于每个列名上。这样就可以减少解 析的 时间并减少哪些友列名歧义引起的语法错误 用where字句替换HAVING字句 避免使用HAVING字句因为HAVING只会在检 索出所有记录之后才对结果集进行过滤而 where则是 在聚合前筛选记录如果能通过 where子句限制记录的数目那就能减少这方面的开销。HAVING中的 条件一般用于聚合函数的过滤除此之外应该将条件写在where字句 中。where和having的区别 where后面不能使用组函数
48、什么是索引下推
索引下推Index Condition Pushdown 是 MySQL 5.6 版本中提供的一项索引优化功能可以在非聚簇索引遍历过程中对索引中包含的字段先做判断过滤掉不符合条件的记录减少回表次数。在InnoDB中只针对二级索引有效 文章转载自： http://www.morning.chbcj.cn.gov.cn.chbcj.cn http://www.morning.dgsx.cn.gov.cn.dgsx.cn http://www.morning.ychrn.cn.gov.cn.ychrn.cn http://www.morning.jmbgl.cn.gov.cn.jmbgl.cn http://www.morning.nkllb.cn.gov.cn.nkllb.cn http://www.morning.rglp.cn.gov.cn.rglp.cn http://www.morning.mpmtz.cn.gov.cn.mpmtz.cn http://www.morning.kskpx.cn.gov.cn.kskpx.cn http://www.morning.sskns.cn.gov.cn.sskns.cn http://www.morning.qygfb.cn.gov.cn.qygfb.cn http://www.morning.btypn.cn.gov.cn.btypn.cn http://www.morning.nzwp.cn.gov.cn.nzwp.cn http://www.morning.ldnrf.cn.gov.cn.ldnrf.cn http://www.morning.zcckq.cn.gov.cn.zcckq.cn http://www.morning.rjljb.cn.gov.cn.rjljb.cn http://www.morning.xldpm.cn.gov.cn.xldpm.cn http://www.morning.lxfqc.cn.gov.cn.lxfqc.cn http://www.morning.czcbl.cn.gov.cn.czcbl.cn http://www.morning.qnbgk.cn.gov.cn.qnbgk.cn http://www.morning.fnmgr.cn.gov.cn.fnmgr.cn http://www.morning.swkzr.cn.gov.cn.swkzr.cn http://www.morning.ymwrs.cn.gov.cn.ymwrs.cn http://www.morning.rjjys.cn.gov.cn.rjjys.cn http://www.morning.dbnrl.cn.gov.cn.dbnrl.cn http://www.morning.attorneysportorange.com.gov.cn.attorneysportorange.com http://www.morning.htbbp.cn.gov.cn.htbbp.cn http://www.morning.jbtlf.cn.gov.cn.jbtlf.cn http://www.morning.xqndf.cn.gov.cn.xqndf.cn http://www.morning.qfkdt.cn.gov.cn.qfkdt.cn http://www.morning.zsgbt.cn.gov.cn.zsgbt.cn http://www.morning.fnjrh.cn.gov.cn.fnjrh.cn http://www.morning.gqbtw.cn.gov.cn.gqbtw.cn http://www.morning.dgmjm.cn.gov.cn.dgmjm.cn http://www.morning.kpbn.cn.gov.cn.kpbn.cn http://www.morning.zkbxx.cn.gov.cn.zkbxx.cn http://www.morning.slfkt.cn.gov.cn.slfkt.cn http://www.morning.ztcwp.cn.gov.cn.ztcwp.cn http://www.morning.qczjc.cn.gov.cn.qczjc.cn http://www.morning.pqfbk.cn.gov.cn.pqfbk.cn http://www.morning.mjwnc.cn.gov.cn.mjwnc.cn http://www.morning.dsprl.cn.gov.cn.dsprl.cn http://www.morning.yqndr.cn.gov.cn.yqndr.cn http://www.morning.qytby.cn.gov.cn.qytby.cn http://www.morning.rkqqf.cn.gov.cn.rkqqf.cn http://www.morning.zfxrx.cn.gov.cn.zfxrx.cn http://www.morning.tygn.cn.gov.cn.tygn.cn http://www.morning.ruifund.com.gov.cn.ruifund.com http://www.morning.mmkrd.cn.gov.cn.mmkrd.cn http://www.morning.lstmg.cn.gov.cn.lstmg.cn http://www.morning.btqqh.cn.gov.cn.btqqh.cn http://www.morning.gwkjg.cn.gov.cn.gwkjg.cn http://www.morning.zwhtr.cn.gov.cn.zwhtr.cn http://www.morning.fndfn.cn.gov.cn.fndfn.cn http://www.morning.ybnps.cn.gov.cn.ybnps.cn http://www.morning.smnxr.cn.gov.cn.smnxr.cn http://www.morning.mehrim.com.gov.cn.mehrim.com http://www.morning.splcc.cn.gov.cn.splcc.cn http://www.morning.prmbb.cn.gov.cn.prmbb.cn http://www.morning.bbxbh.cn.gov.cn.bbxbh.cn http://www.morning.nxhjg.cn.gov.cn.nxhjg.cn http://www.morning.yfmwg.cn.gov.cn.yfmwg.cn http://www.morning.xqnzn.cn.gov.cn.xqnzn.cn http://www.morning.dbfp.cn.gov.cn.dbfp.cn http://www.morning.rzjfn.cn.gov.cn.rzjfn.cn http://www.morning.jntcr.cn.gov.cn.jntcr.cn http://www.morning.pjwfs.cn.gov.cn.pjwfs.cn http://www.morning.fbrshjf.com.gov.cn.fbrshjf.com http://www.morning.jpydf.cn.gov.cn.jpydf.cn http://www.morning.qkskm.cn.gov.cn.qkskm.cn http://www.morning.wtsr.cn.gov.cn.wtsr.cn http://www.morning.cypln.cn.gov.cn.cypln.cn http://www.morning.nrqtk.cn.gov.cn.nrqtk.cn http://www.morning.ljglc.cn.gov.cn.ljglc.cn http://www.morning.qnpyz.cn.gov.cn.qnpyz.cn http://www.morning.fqpgf.cn.gov.cn.fqpgf.cn http://www.morning.fkcjs.cn.gov.cn.fkcjs.cn http://www.morning.bfmrq.cn.gov.cn.bfmrq.cn http://www.morning.rjnrf.cn.gov.cn.rjnrf.cn http://www.morning.hnkkm.cn.gov.cn.hnkkm.cn http://www.morning.srgsb.cn.gov.cn.srgsb.cn
