博客

数据库三大范式是什么

• 第一范式(
  
  列的原子性
  
  )：每个列都不可以再拆分，否则会导致操作数据不方便。
• 第二范式(
  
  一张表只能做一件事，消除所有的非主键/部分依赖
  
  )：在第一范式的基础上，非主键列必须依附于主键，否则会导致如下问题
  • 数据冗余， 比如学生信息表和学生成绩表耦合在一起，一个学生会有很多科目的成绩，那这样就会造成学习信息的重复
  • 更新困难：例如一个课程的学分出现变化，那么需要对表中涉及到该学分的全部字段更新，更新代价很大
• 第三范式(
  
  保证表中所有的字段不依赖其它中间字段，只能直接依赖于主键，消除所有的传递依赖
  
  )，将依赖于非主键的字段给拆出来

5 mysql有关权限的表都有哪几个

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

mysql有哪些数据类型

6.1 浮点数与定点数区别

• 定点数指小数点在数中的位置是固定不变的，通常有定点整数和定点小数。在对小数点位置作出选择之后，运算中的所有数均应统一为定点整数或定点小数，在运算中不再考虑小数问题。
• 浮点数中小数点的位置是不固定的，用阶码和尾数来表示。通常尾数为纯小数，阶码为整数，尾数和阶码均为带符号数。尾数的符号表示数的正负；阶码的符号则表明小数点的实际位置。

6.2 datetime和timestamp的区别

• 共同点是都可以表示 YYYY-MM-DD HH:MM:SS 这种年月日时分秒格式的数据。
• 自动更新上，datatime默认为空，当数据插入或修改时不会自动更新，往往需要进行一些列设置（java程序中手动赋值，myabtis中进行配置，mysql端进行配置）。timestamp默认值非空，当出入或修改数据时自动更新
• 存储容量上 datetime是8字节，timestamp的四字节 => 范围上datatime范围大，而timestamp范围小
• datatime不考虑时区，timestamp考虑时区

varchar与 char

• 
  长度是否可变的区别
  
  ，varchar变长 ，即长度可变，比如定义大小为10的varchar字段，如果存储的某个字段值小于10，那么实际占用的空间大小就是这个字段的大小。char类型的字段存储的每个字段类型都是10，如果插入的长度小于定义长度时，则用空格填充；
• 
  速度方面
  
  ：因为char类型的所有存储字段占用的大小都一样，所以char的存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会占据多余的空间，
  
  char是以空间换取时间效率。varchar则刚好相反，以时间换空间
  
• 
  存储上限
  
  ：char最多存储2的8次方，varchar最多存储2的16次方
• 
  实际开发
  
  ：如果字段定长那么选用char，否则选用varchar

6.4 谈论一下整形类型

• 包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。默认都是SINGED有符号的，任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。
• 整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。

MyISAM与InnoDB区别

• 
  查询方面
  
  ：
  • 普通查询：主键索引的查询效率比较高，因为可以一次性查询到不需要再回表，但通过辅助索引查数据时，如果查询的目标字段没有被辅助索引完全覆盖，那么就需要回表，效率较低
  • 聚合查询：
• 
  事务方面
  
  ：
• 
  索引键值
  
  方面
  • InnoDB支持外键，而MyISAM不支持
  • Innodb默认必须要有主键且对主键要求较为严格，满足自增性、有序性、唯一性，最后不要太长(类比uuid，防止聚簇索引频繁的分裂和合并影响性能)，用户没有指定索引Innodb也会去自动创建一个，不能过长(主键过长)而MyIsam是可以没有主键
    
    

10内连接特点


①表的顺序可以调换(不分主表和从表)

②内连接的结果是多表的交接

③ n表链接至少需要n-1个条件(有等值链接，非等值链接，自连接)

11 外连接(左外链接 + 右外连接)


①查询结果以主表为主，从表没有那匹配到么就用null填充

②左右外链接分别以左表和右表作为主表

12 约束


为了保证表数据的合法性对表做出的约束行为,六大约束

• 非空：NOT NULL,保证该字段不为空
• 主键：KEY PRIMARY 保证唯一性和非空性
• 外键：FORIGEN KEY 限制两张表的关系，保证该字段的值必须来自某关联表，即在从表添加外键约束来引用主表的值，从表的外键对应主表的主键，插入数据要先插入主表再插入从表，删除数据先删除从表再删除主表
• 默认：如果该字段没有插入就使用非空值
• 唯一：UNIQUE保证唯一性
• 检查：检查数据的合法性

列级约束：除了外键都支持

表级约束：不支持非空和默认

13 主键 vs 唯一


唯一性：都具备

非空：主键非空，唯一可以为空

重复性：不可重复出现，可以重复出现

组合性：都可以组合

分类：单值索引，唯一索引，复合索引

14 外键注意点

• 必须在是表级约束
• 从表的外键列类型和主表的关联列类型必须一致或兼容,名称无要求
• 主表的关联列必须是主键
• 插入数据时应该先插入主表再插入从表，删除数据时应先删除从表再删除主表

16 索引的优缺点

• 优点：可帮助我们快速
  
  查找
  
  (避免了大量的IO操作)与
  
  排序
  
  (降低了CPU资源的消费)，索引内部的隐藏优化器可以提高系统性能
• 缺点
  • 每创建一个索引意味着mysql底层会创建一颗B+树，树的每一个节点都是一页的空间16k，存储空间开销增大
  • 索引是一个已排好序的数据结构，聚簇索引B+树上的叶子节点都是按照索引列的值由大到小形成了双向列表，每个叶子节点中的数据列也是按有小达到形成了单项列表，但执行删除、插入、更新是，索引内部需要重新维护索引的顺序、这就需要耗费一定的性能了

17 什么情况需要索引

• 索引的前提数据达到一定数量时；主键默认有唯一索引，用主键查询效率较高
• 外键关系推荐使用索引,因为外键意味着从表会通过外键来管理主表，也就是从表的插入，删除，修改及链表查询都需要通过外键查询主表相关信息来进行合法性验证，这就意味需要通过外键频繁的查询来完成，所以有必要加外键
• 频繁查询的字段但增删改少的情况：带索引的结构都是查询容易增删难
• 需要排序的字段：索引结构通常都是已经排好序的了
• 需要分组或统计的字段（分组前需要排序）,分组的前提是先进行排序

18 什么情况避免使用索引

• 表数据量很少的时候 mysql底层可以很好的处理
• 没有出现在where查询条件中的字段没必要建索引
• 更新插入删除操作频繁的数据表
• 频繁重复的字段，如性别 国籍
• 对于定义为text、image和bit的数据类型的列不要建立索引，因为这些字段的值将作为索引的字段，字段长度过大使很致命的
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
• 如果某字段在查询条件中是一个范围字段，如where a>100,那么就不推荐为a字段使用索引

19 索引的类型

• 普通索引：一个索引只有一个字段，可以给任意的字段添加的索引
• 唯一索引：保证表数据的唯一性
• 主键索引：特殊的唯一索引，不需要手动添加，主键默认具体主键索引，一张表只能有一个主键索引，但唯一索引数量没有限制
• 复合索引，一个索引包含多个字段，开发中最常见的

21 索引的使用场景

• 每张表都有默认的主键索引，但如果是其它用字段查询就没有默认的索引可用，需要为普通字段创建索引
• order by xxx：如果xxx字段没有索引，需要从磁盘中取出数据再进行外部排序，反正xxx字段建立了索引，那么只需按顺序一条条取出字段即可，如果有limit，那么就只需要取出范围内数据即可而不必全部取出在进行排序
• join on：如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小
• 尽量的扩展索引而不是新建

select * from t_xxx where id = 1; //索引是常量类型const
select * from t_xxx x join t_yyy y on where x.id = y.id; //索引是字段类型，格式是 tableName.columnName.field	
select * from t_xxx x join t_yyy y on where x.id = y.id and x.name="wzh";//t1索引是字段类型+常量类型，t2的索引为null

分析type

• type为System或者Const案例

explain select * from (select * from t1 where id = 1) d；

• type的eq_ref或ref时

explain select *from t1,t2 where t1.id=t2.id；

①当id为部门id，且这个id=95时正好查询出的是总裁办部门(该部门下只有一个ceo)，此时整张表就全是ceo的基本信息+部门信息了，此时从表对应的是eq_ref，也就是通过外键索引只管理到了一张

②eq_ref非常罕见，管理查询基本上都是主表为ALL,从表为ref这种基级别

• type为range时，以索引字段为条件进行范围查询<>、between and、in范围查询会出现这种情况

• type为index或者all，如果用的查找字段和索引吻合或者是满足最佳左前缀匹配被索引覆盖的情况就是index级别，否则就是all级别

23 创建索引时需要注意什么？

• 索引必须是非空的
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高
• 最好是扩展索引字段而不是新建，这样能节省空间和时间

24 使用索引查询一定能提高查询的性能吗？为什么


通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

25 数据库事物，并非问题，隔离级别



https://blog.csdn.net/wwwwwww31311/article/details/114751493

26 视图

• 视图物理上不存再，是逻辑虚表，只是内容与真实的表相似
• 视图是为了更好的展示数据，并且提高了数据库中数据的安全性。

27 超键、候选键、主键、外键分别是什么？


1）主键：数据库中唯一完整标识存储对象的数据列或数据列组合，主机具有唯一性，非空性，在数据表字段中是不可重复的

2）外键：在一个表中表示另一个表主键的字段称为这个表的外键，带有外键的表是从表，另一张表是主表，注意点如下

• 外键必须是表级约束
• 外键所存储的必须是别的表的主键而不能是别的字段
• 外键的数据类型和它管理表的主键类型必须一致或兼容
• 插入数据时应该先插入主表再插入从表，删除数据时应，删除时先删除从表再删除主表

3 )超键：关系中唯一标识元组的属性，里面可能包含多个唯一标识元组的属性

4) 候选主键：不含多余属性的超键，也就是超键中去掉无关紧要的属性性剩下的属性依然还是超键，任意的候选主键都可以当成主键

28 sql语句分类

• 数据库定义语言DDL(database defination language)：数据定义语言，这涉及对逻辑结构(数据库，表，所有)的增删改查操作
• 数据查询语言DQL(database query language):数据查询语言，基于select的各种各样的查询语句
• 数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE
• 数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK，即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等

29 六种关联查询

30 乐观锁与悲观锁


乐观锁：乐观锁假设数据一般情况下不会造成冲突，所以只有在更新数据提交的时候才会判断该数据是否被别人修改过。如果数据被其他线程修改过，则回滚，如果数据没有被其他线程修改，则进行数据更新，这是是一种技术，原理是是通过一个标识字段version，每次修改都会version+1，且进行判断是否当前version等于oredrVerion，只有相等才意味着没有修改，才能操作成功，否则要进行回退，乐观锁比较适合读多写少的情况

悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，悲观锁的实现依赖数据库本身机制，适合读少写多的场景

31 事物及事物安全问题的解决



https://blog.csdn.net/wwwwwww31311/article/details/114751493

32

select user_id,count(DISTINCT(article_id) as count
fronm article
where view_time = '2020-4-23'
group by user_id
having count > 20
order by count DESC
limit 10;

34 mysql中的锁


①根据锁的属性分为共享s锁，和排他x锁

• 共享锁又叫读锁，上了读锁后其它事物还可以上读锁但不能再上读锁，意义是这样可以并发读数据，并且防止不可重复读问题
• 排他锁有叫写锁，上了写锁那么其它的事物不能再对它上任何锁，意义是当一个事物正在写数据时不允许其它事物读数据与不允许写数据，保证写数据的安全性，解决脏读问题

②根据锁的范围分为行锁，页锁，表锁，从左到右加锁难度降低，并发性也降低，细粒度越大，冲突概率越大

35 mysql中查询速度慢该怎么处理

• 从查询语句入手，看条件字段与查询字段是否太多或者太大，能否去掉些不需要的
• 索引角度，尽快保证条件字段准确命中索引字段
• 看表容量是否过大，如果太大的话就进行分表分库

36案例代码




select * from article where category_id = 1 and comments > 1 order by views desc limit 1;

37 mysql优化

• 按需查询，去掉不需要的字段
• 能通过where查询出来字段的不要通过having筛选，这样的话尽量减少分组数据量，节约cpu资源
• 尽量使用逻辑分页，而不是物理分页

sql优化

过程

1 数据库连接池取代单独的连接管理

2 是否查询了过多不需要的字段，where和having的搭配

3 是否建立索引，索引是否生效

• 连接查询时，让小表驱动大表，索引的话加到从表上，因为加在主表上基本无效，连接查询无论如何都会读取全部的主表
• where后查询是尽量避免<,>,!=这类符合的使用，也不要做任何的操作，使用exist代替in来使用，不要涉及到null值(为字段设置默认约束)，不要使用or来做链接，不要出现聚合函数或操作运算，能使用where赛选的就不要使用having
• 索引最好是全值匹配，至少最佳左前缀匹配法，否则索引无效，而且只要索引要从第一个列开始延续(带头大哥不能死，中间兄弟不能断)
• select的筛选一定要按需取字段，最好是能和索引一样或者和在索引字段范围内，这样性能更优，通常会出现use index这种情况
• like匹配时，最好使用右匹配符，如果使用全匹配符那么最好是覆盖索引或者被索引覆盖
• 字符串类型查询一定要加’”，否则会造成全表扫描索引失效
• where中断order中也补不上，where和order至少连续起来，否则会出现using filesort的提示
• 其它方面，配置数据库连接池,使用varchar代替char，大型字段要慎用
  
  
• 考虑是否需要分表分库，按业务划分，读写分离

limit语句

limit 10:查询前10条数据
limit 10,20:查询数据库10-20条数据
limit 0:查询前0条，为空

mysql分页

• 物理分页：依赖物理实体即数据库mysql进行分页，方法很简单，使用limit批量筛选数据即可
• 逻辑分页：一次性查出数据库全部内容存储在List中，然后根据List索引批量选取展示结果

select * from student limit(curPage-1)*pageSize,pageSize;

sql语句执行顺序


from,join,on,where group by,avg/sum,having,select,distinct,order by,desc/asc,limit

where后面可以用select的字段的别名吗？不可以

mysql性能凭据


1 cpu装入数据或从磁盘读取数据时会发生cpu饱和

2 装入内存内容远大与内存容量时发生IO饱和

3 服务器硬件垃圾

分层优化

1 连接层：应用程序通过本地socket和tcp/ip协议，完成一系列权限认证，资源对接操作链接到mysql的连接池

2 服务层：增删改查核心方法的接口

3 引擎层：可拔插

4 存储层

7 种join理论