0、MySQL的版本5.5 5.78.x(我们用的是)1、内连接和外连接的区别内连接指的是使用左表中的每一条数据分别去连接右表中的每一条数据仅仅显示出匹配成功的那部分外连接有分为左外连接和右外连接左外连接: 首先要显示出左表的全部然后使用连接条件匹配右表能匹配中的就显示匹配不中的显示为null右外连接: 首先要显示出右表的全部然后使用连接条件匹配左表能匹配中的就显示匹配不中的显示为null2、union与union all的区别union和union all都是MySQL中用于合并多条select语句结果的关键字它会将前后两条select语句的结果组合到一个结果集合中区别在于UNION ALL会返回所有结果UNION会去掉重复的记录3、char和varchar的区别char和varchar是MySQL中的字符串类型区别在于下面几方面最大长度char最大长度是255字符varchar最大长度是65535个字符占用长度char是定长的不足的部分用隐藏空格填充varchar是不定长的空间使用char会浪费空间varchar会更加节省空间查找效率char查找效率会很高varchar查找效率会更低因此我们如果存储固定长度的列例如身份证号、手机号建议使用char其它不定长度的建议使用varchar使用varchar的时候也要尽量让声明长度贴近实际长度注意varchar(50)中50的涵义是最多存放50个字符varchar(50)和varchar(200)存储hello所占空间一样4、事务的四大特性事务是有一组操作要不全部成功要不全部失败事务的四大特性指的是原子性、一致性、隔离性、持久性原子性事务是最小的执行单位不允许分割同一个事务中的所有命令要么全部执行要么全部不执行一致性事务执行前后数据的状态要保持一致例如转账业务中无论事务是否成功转账者和收款人的总额应该是不变的隔离性并发访问数据库时一个事务不被其他事务所干扰各并发事务是独立执行的持久性一个事务一旦提交对数据库的改变应该是永久的即使系统发生故障也不能丢失5、并发事务多个事务同事执行带来的问题并发事务下可能会产生如下的问题脏读一个事务读取到了另外一个事务没有提交的数据不可重复读一个事务读取到了另外一个事务修改的数据幻读虚读一个事务读取到了另外一个事务新增的数据6、事务隔离级别事务隔离级别是用来解决并发事务问题的方案不同的隔离级别可以解决的事务问题不一样读未提交 允许读取尚未提交的数据可能会导致脏读、幻读或不可重复读读已提交 允许读取并发事务已提交的数据可以阻止脏读但是幻读或不可重复读仍有可能发生可重复读 对同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改可以阻止脏读和不可重复读但幻读仍有可能发生可串行化 所有的事务依次逐个执行这样事务之间就完全不可能产生干扰该级别可以防止脏读、不可重复读以及幻读。上面的这些事务隔离级别效率依次降低安全性依次升高如果不单独设置MySQL默认的隔离级别是可重复读原理我知道是MVCC多版本并发控制有日志文件和3个隐藏的列处理的https://www.bilibili.com/video/BV1yT411H7YK?spm_id_from333.788.videopod.episodesvd_source75f63c60e63304f7c9b5e105f8586896p327、数据库建表三大范式三大范式是指导设计表的原则第一范式表中的每一列不能再进行拆分也就是每一列都应该是原子的列不可再拆分第二范式一张表只做一件事不要将多个层次的数据列保存到一张表中 一张表只做一件事第三范式数据不能存在传递关系说的通俗点就是一张表拆成两个表使用外键建立关系使用外键比如这是一张表院系电话是依赖院系的不依赖学生id这时可以分成学生表和院系表改造后在现有的程序设计中认为第三范式是可以不遵守的也就是通过添加冗余字段来减少多表联查或计算我们称为反三范式8、索引的分类1、从物理存储角度上分为聚集聚簇索引和非聚集非聚簇索引聚集索引指的是数据和索引存储在同一个文件中非聚集索引指的是数据和索引存储在不同的文件中2、从逻辑角度上分为普通、唯一、主键和联合索引它们都可以用来提高查询效率区别点在于唯一索引可以限制某列数据不出现重复主键索引能够限制字段唯一、非空联合索引指的是对多个字段建立一个索引一般是当经常使用某几个字段查询时才会使用它比对这几个列单独建立索引效率要高9、索引的创建原则我们在建立索引的时候应该遵循下面这些原则1. 主键字段会自动创建主键索引2. 经常作为查询条件在where和order by语句中出现的列需要建立索引3. 查询中与其他表关联的字段外键关系建议建立索引4. 经常使用多个条件查询时建议使用组合索引代替多个单列索引5. 用于聚合函数的列可以建立索引6. 数据量小的表不建议添加索引7. 不要在区分度低的字段建立索引比如性别字段8. 数据变化比较大的表不建议创建索引10、索引失效的情况索引失效指的是虽然在查询的列上添加了索引但是某些情况下查询的时候依旧没有用到索引常见的情况有-- 1.使用like关键字时模糊匹配使用开头将导致索引失效SELECT * FROM USER WHERE username LIKE jack10000000%;SELECT * FROM USER WHERE username LIKE %jack10000000;-- 2.使用连接条件时如果条件中存在没有索引的会导致索引失效SELECT * FROM USER WHERE username jack10000000 OR email jack10000000itcast.cn;SELECT * FROM USER WHERE username jack10000000 OR age 156;-- 3.任何操作计算、函数、自动|手动类型转换将导致索引失效SELECT * FROM USER WHERE age 100;SELECT * FROM USER WHERE id1 101;-- 4.使用 !、not in、is null、is not null时,会导致索引失效SELECT * FROM USER WHERE username is not null11、如何知道索引是否失效MySQL中自带了一个关键字叫explain它可以加在一个sql的前面来查看这条sql的执行计划在执行计划中我们主要观察两列的结果一列是type一列是extra第一个type是重要的列显示本次查询使用了何种类型常见的值从坏到好依次为all、index、range、ref、eq_ref 、const、systemall表示全表扫描数据文件返回符合要求的记录其他值标识用到索引了除了type之外我们需要关注一下extra列如果出现using index最好了它表示列数据仅仅使用了索引中的信息而没有回表查询12、MyISAM和InnoDB的区别MyISAM特点不支持事务和外键操作读取速度快节约资源只支持表锁InnoDB特点支持事务和外键操作支持行锁做大数据量删除时速度比较慢13、索引的数据结构是什么在MySQL中索引使用的数据结构是BTreeB树是基于B树的变种它具有B树的平衡性而且树的高度更低B树非叶子节点不存在数据只存索引因此其内部节点相对B树更小树的高度更小查询产生的I/O更少B树查询效率更高B树使用双向链表串连所有叶子节点区间查询效率更高B树查询效率更稳定B树每次都必须查询到叶子节点才能找到数据而B树查询的数据可能不在叶子节点也可能在这样就会造成查询的效率的不稳定14、什么是覆盖索引什么是回表覆盖索引是指查询使用了索引并且需要返回的列在该索引中已经全部能够找到 。如果没有全部找到会回表查询通过非聚簇索引找到对应的主键值到聚簇索引中查找整行数据这个过程就是回表主键索引就是聚簇索引15、数据库中的锁有哪些MySQL中的锁从不同维度可以分为不同的种类1、从锁的粒度上可以分为表锁和行锁表锁指的是会锁定修改数据所在的整个表开销小加锁快锁定粒度大发生锁冲突概率高lock tables 表名 read | write;select * from 表名 for update也可以锁表行锁指的是会锁定修改数据所在的行记录开销大加锁慢锁定粒度小发生锁冲突概率低2还有两种概念上的锁是悲观锁和乐观锁悲观锁是指一个事务在修改数据的时候总是认为别人也会修改此数据所以强制要使用锁来保证数据安全乐观锁是指一个事务在修改数据的时候总是认为别人不会修改此数据因为不加任何锁其实本质上就是修改数据的时候加一个条件比如update account set moneymoney100 where id1and money1000这种情况下万一在当前事务修改的时候数据被其它事务也修改了机会出现问题此时常用的方案是给数据表中添加一个version列每次更新后都将这个列的值加1读取数据时将版本号读取出来在执行更新的时候会先比较版本号如果相同则执行更新如果不相同说明此条数据已经发生了变化就放弃更新或重试16、MySQL日志类型MySQL的很多功能都是依靠日志来实现的比如事务回滚数据备份主从复制等等常见的日志有下面几个binlog归档日志负责记录对数据库的写操作一般用在主从复制过程中记录日志从库拷贝此日志做重放实现数据同步redolog重做日志用于确保事务的持久性。防止在发生故障的时间点尚有脏页未写入磁盘在重启 mysql 服务的时候根据 redo log 进行重做从而达到事务的持久性这一特性undo log 回滚日志保存了事务发生之前的数据的一个版本可以用于回滚17、MySQL主从复制的流程主从复制用于MySQL主从集群的主节点向从节点同步数据主要是依靠MySQL的binLog实现的大体流程分为三步Master 主库在事务提交时会把数据变更记录在二进制日志文件 BinLog中从库读取主库的二进制日志文件 Binlog 写入到从库的中继日志 RelayLogslave重做中继日志中的事件将改变反映它自己的数据https://www.bilibili.com/video/BV1yT411H7YK?spm_id_from333.788.videopod.episodesvd_source75f63c60e63304f7c9b5e105f8586896p3318、谈谈你对sql的优化的经验1、表设计方面1.1 单表数据量大可以考虑分库分表分库需要借助中间件比如说mycathttps://www.bilibili.com/video/BV1yT411H7YK?spm_id_from333.788.videopod.episodesvd_source75f63c60e63304f7c9b5e105f8586896p34分表的话比如说可以分成热点数据表和历史数据表1.2 可以添加冗余字段以避免表关联查询1.3 建表时选择合适的数据类型和长度2、索引方面2.1 开启慢查询日志定位执行时间比较长的SQL2.2 需要创建索引的尽量创建索引有索引的可以使用explain判断是否走索引3、SQL语句方面尽量不使用select *in 后面不要出现太多的值避免子查询4、选择合适的引擎Innodb MyIsam19、慢日志是什么慢日志是一种定位低效率sql的手段-- mysql有记录sql执行时长的能力它需要设置一个阈值一旦某条SQL执行时间大于了这个阈值mysql就会将其记录到日志文件中-- 1 查看慢查询日志开启情况mysql默认是关闭show variables like %slow_query_log%;-- 2 开启慢查询日志set global slow_query_log on;-- 3 查看慢查询时间配置(MySQL默认慢查询的记录时间为10秒)show variables like %long_query_time%;-- 4 修改慢查询的记录时间为3秒临时修改set long_query_time3;-- 5 运行下面sql然后观察慢日志文件SELECT * FROM USER WHERE id 1