MySQL
三大范式
- 第一范式:数据库表的每一列都是不可分割的基本数据项
- 第二范式:所有非主属性都完全依赖于主码
- 第三范式:所有非主属性不传递依赖于任何候选码
引擎
MySQL有哪些引擎
- MyISAM
- InnoDB
- MEMORY
- MERGE
myisam和innodb的区别
- InnoDB支持外键,MyISAM不支持
- InnoDB底层B+树使用聚簇索引,索引数据放在一起;MyISAM底层B树使用非聚簇索引,索引数据分开
- InnoDB支持行级锁,表级锁,MyISAM支持表级锁
- InnoDB支持事务,MyISAM不支持
事务
事务四个特性
ACID
事务隔离级别
哪种隔离级别会造成幻读
-
读未提交,脏读,幻读,不可重复读,更新丢失都会发生
-
读已提交(
事务提交后才可读
),解决了脏读和更新丢失仍会不可重复读和幻读 -
可重复读(
事务开启后不允许对本事务修改操作
),解决了不可重复读,仍会幻读 -
序列化(
事务串行化执行,防止事务执行过程其他事务数据干扰
),解决了幻读问题 -
MySQL默认可重复读;Oracle默认为读已提交
如何在项目里设置事务的隔离级别
-
Spring建议使用的隔离级别是DEFAULT即数据库本身的隔离级别,可以在配置文件中进行修改
-
springboot在启动类上加上@EnableTransactionManagement 开启事务支持后,在访问数据库的service上添加 @Transactional ,并指定默认属性及指定isolation和propagation属性,代表DEFAULT,即使用数据库默认级别
@Transactional(isolation = Isolation.DEFAULT,propagation = Propagation.REQUIRED)
事务控制的话在redis还是mysql,如果在mysql的话,项目的瓶颈就在mysql服务器上,有没有考虑过(MYSQL高并发优化)
项目中事务的控制在MySQL上,项目的瓶颈出现在MySQL
- SQL优化
- 数据库增加索引,优化索引
- 分库分表(需要分布式事务控制)
- 在应用层和DB间加一层cache减少服务器负担,可以搭建cache集群
水平分表/垂直分表
- 垂直拆分:把主码和一些列放到一个表,然后把主码和另外的列放到另一个表中
- 按列拆成多表,热点数据放在一张表提高查询效率
- 数据行变小,一个数据页能存放更多数据
- 需要管理冗余列,查询数据需要join操作
- 水平拆分:根据一列或多列数据的值把数据行放到两个独立的表中
- 大表在水平分割后降低查询时需要读的数据和索引的页数 ,提高单表的并发读写能力,和磁盘IO性能
- 可以将数据存放在多个介质上
- 水平分割查询时需要多个表名并且查询所有数据需要联合查询多次union
查询慢如何解决
索引
数据库索引有哪些
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
- 聚簇索引/非聚簇索引
mysql哪些字段适合做索引,那些不适合(索引选择原则)
适合
- 表的主键,外键
- 经常作为查询条件的字段 where
- 经常与做多表查询的连接字段上
- 字段较少
不适合
- 字段太长
- 没有过滤性的字段
- 频繁更新的字段
扫描全表(比如100万行数据),使用普通索引快还是不使用索引快
- 数据量大的时候需要使用索引
- 数据量小的时候直接全表扫描更快
聚簇索引和非聚簇索引
聚簇索引:数据存储和索引放在一起,MyISAM使用聚簇索引
非聚簇索引:数据存储和索引分开,InnoDB使用非聚簇索引
索引失效情况(索引优化)
-
数据量小或者mysql判断下,全表扫描更快则不会使用索引
-
like以%开头,前导模糊查询无法命中索引
-
数据类型出现隐式转换不会命中索引,特别当列为字符串,一定要将字符串常量用引号包裹起来
-
- 复合索引查询条件不包含索引列最左边部分(
不满足最左前缀原则
),无法命中索引
- 复合索引查询条件不包含索引列最左边部分(
-
条件有or分隔除非让or条件中每个列都有索引,部分有索引将失效
-
负向条件!=、<>(不等于)、not in、not exists、not like等不能使用索引,可以优化为in
-
范围查询可以命中索引
-
对索引字段做了运算
-
对索引字段使用了聚合函数
聚簇索引/非聚簇索引(高频)
主键使用不重复的字符串会出现什么问题
插入的时候由于字符串不能像自增长的id一样连续,所以在磁盘上的分布需要不断调整
SQL
连接
- mysql左连接
返回lef join左边全部记录和右表中符合搜索条件的记录,右表记录不足的字段值为NULL - mysql右连接
返回right join 右表所有记录和左表中符合搜索条件的记录,左边记录不足字段为NULL
-内连接就是等值连接
外连接时全部记录查出
联合查询
MySQL怎么分页
mysql提供了limit做分页查询
Limit怎么用
- 在查询语句后加上limit子句,加上两个参数且必须为整数常量
- 第一个参数指定第一个返回记录行的偏移量(初始偏移量为0),第二个参数指定返回记录行的最大树木
explain关键字
通过在sql前加上explain可以模拟优化器执行SQL的过程
- id:查询序号,多表查询时决定了访问表的顺序
- select_type:查询类型,常见的SIMPLE.PRIMARY,DERIVED,UNION
- table:用得到的表
- type:连接类型,
- const:使用主键索引查询出一条数据的语句
- system:查询系统表并满足条件会使用
- eq_ref:关联拆线呢用到了主键或唯一索引
- ref:用到非唯一索引
- range:对索引进行了范围扫描
- index:对索引全部扫描(即拿到全部索引数据)
- ALL:全表扫描
- possile_keys
- key
- key_length
数据库通配符 %能匹配NULL吗
%不能匹配NULL
mysql查询一亿数据怎们办
- 正确涉及索引避免全局扫描且每个索引键不要太多,要避免外连接
- 采用分表技术
- 垂直分表:将热点字段分离出来,根据主表的主键关联
- 水平分表:将相同字段的记录按照某种hash算法拆成多个表
- 采用分库技术
- 垂直分库
- 水平分库
- 采用redis等cache在应用和DB之间进行缓存数据,减少直接对数据库读取,还可以搭建缓存集群
- 主从复制,读写分离
drop,delete与truncate
- drop直接删掉表
- truncate删除表中所有数据,再插入时自增长id又从1开始 ,有
外键约束
的表不能使用truncate而应该使用不带where的delete - delete配合where删除表中指定行数据,不带where删除全表数据,
- 效率drop>truncate>delete
SQL优化
- SQL优化
- 避免在where字句使用!=,<>,or操作符,否则会使得引擎放弃索引全表扫描
- 避免在字段开头模糊查询即like进跟%,放弃索引全表扫描
避免进行null值判断
,放弃索引全表扫扫描用 exists 代替 in,用Where子句替换HAVING 子句
- 索引优化
数据库优化
-
SQL优化
-
索引优化
-
数据库结构优化
- 范式化优化:消除冗余数据节省空间
- 反范式化优化:适当冗余减少join操作
- 水平拆表,垂直拆表
-
对不常用的数据迁移备份避免每次都在海量数据检索
-
减少数据库操作次数,尽量使用数据库访问驱动的批处理方式(如JDBC批处理)
-
服务器参数配置,如在主机上配置单机连接的最大数
-
硬件优化,如搭建数据库集群
-
在应用和DB之间加缓存,缓存热点数据,提高查询效率
手写SQL
- 找出分数>60的每个分数的个数
- 查找一个成绩表中,一个同学没有及格科目数
- 写SQL语句:查询表中不同性别的平均年龄
日志
- 介绍一下MySQL的日志
数据结构
bigint和varchar会选哪种,为什么
手机号码用什么类型存储
- 使用了varchar存储,手机号可能存在英文
性能
回表查询
Redis
单线程的Redis为什么这么快(Redis为什么使用单线程)
持久化机制
Redis过期策略和内存淘汰机制
定期删除+惰性删除策略
jedis是否线程安全
- jedis继承了redis的一些命令操作,封装了redis的java客户端是redis数据库的java语言的驱动jar包(类似于mysql与jdbc的关系),提供了连接池管理
- 一般不直接使用jedis而是再封装一层作为业务使用;spring中的Redistemplate又在jedis上又封装一层,相当于mybatis又在jdbc上的封装
- jedis不是线程安全的,不应该再多线程下共用一个Jedis实例,但也应该避免创建过多实例导致创建过多的socket连接性能降低
- 为了保证线程安全并且获得更好的性能,可以使用JedisPool,线程安全的连接池
redis,你是怎么保证双写一致性的
采用了Cache Aside Pattern
- 读的时候先读缓存,缓存没有读数据库,然后取出数据库放入缓存同时返回相应
- 更新时先更新数据库再删除缓存(用到缓存采取算缓存)
redis 为什么使用单线程
- 主要是为了原子性操作
- 避免多线程频繁的上下文切换同时采用IO多路复用处理并发的连接
redis的zset如何实现
(.Redis和Mybatis源码看过吗?说了Redis的底层数据结构 Zset)
redis的zset底层是由跳表实现的
Redis分布式锁
介绍一下、画一下基本结构,搜索插入数据过程,时间复杂度
其他
高并发访问一个接口,如何检测传入相似度很高的字符串
布隆过滤器
数据库查询较慢如何排查
-
expalin慢查询,查看是否使用索引,索引使用级别,优化索引
-
在mysql中执行 show processlist命令,查看当前所有连接,连接
如果从数据库或者redis中取出一些key-value对象,使用HashMap还是TreeMap存储
- TreeMap底层是使用红黑树,Key值是要求实现java.lang.Comparable,所以迭代的时候TreeMap默认是按照Key值升序排列,对于热点数据,如果设置得当使其靠近根节点,则存取的效率会跟高
- HashMap的key值通过hashcode()散列均匀分布,存取效率相对就低