MySql 知识整理

01. 列举常见的关系型数据库和非关系型都有那些？

关系型数据库

Oracle、Mysql、IBM DB2、SQL Server、SQLite、Postgresql等等

非关系型数据库

MongoDB、Redis、NoSql等等

02. MySQL常见数据库引擎及比较？

https://blog.csdn.net/weixin_38120374/article/details/80621528

https://blog.csdn.net/sjyttkl/article/details/76176836

https://blog.csdn.net/u014556057/article/details/70742367

MySQL5.5.8版本开始，默认的存储引擎是InnoDB，像现在企业常用的MySQL5.6、5.7 默认的都是InnoDB引擎

像5.7版本MySQL支持8种存储引擎（MyISAM、InnoDB、MEMORY、CSV、BLACKHOLE、ARCHIVE、PERFORMANCE_SCHEMA、Merge）

InnoDB：

1. 提供事务支持
2. 提供系统崩溃修复能力
3. 支持行级锁，写和更新操作并发高，速度块，适合频繁更新的表
4. 支持自增长列
5. 支持外键
6. 是大容量、事务型数据库的首先引擎

MyISAM：

优点：

1. 主要特点就是为了数据的查询和插入，大量数据查询和插入都非常迅速，
2. 独立与操作系统，方便迁移
3. 单独保存表的行数

缺点：

1. 不支持事务
2. 不支持行级锁
3. 不适用于经常更新的表，效率低

03. 简述数据三大范式？

第一范式（1NF）:原子性 字段不可再分,否则就不是关系数据库;

第二范式（2NF）:唯一性 一个表只说明一个事物;

第三范式（3NF）:每列都与主键有直接关系，不存在传递依赖;

PS：第二范式要遵循第一范式，第三范式要遵循第二范式。

 

简单来说：

1NF:列表字段不可分;

2NF:有主键且非主键依赖主键；

3NF:非主键字段不能相互依赖;

04. 什么是事务？MySQL如何支持事务？

https://blog.csdn.net/zcl_love_wx/article/details/53240998

 

什么是事务：

1.概念：由一步或几步操作序列组成的逻辑执行单无（要么全部成功，要么全部失败）

2.特性：ACID

• 原子性：事务是应用中最小的逻辑执行体
• 一致性：事务的执行结果必须使用数据库的数据保持正确
• 隔离性：各个事务的执行互不干扰
• 持续性：事务提交之后，数据的改变要保存进数据库，也叫持久性

3.提交

• 自动提交：执行DDL或DCL语句或程序正常退出时会自动期缴
• 显示提交：Commit

4.回滚

• 自动回滚：系统错误或强行退出时会自动回滚
• 显示回滚：rollback

MySQL如何支持事务

1. 表结构使用InnoDB，setautocommit=0;//开启事务（关闭自动提交）
2. 临时事务：transaction 或begin 后的DML语句就是临时事务，要在commit后才生效
3. 保存中间点：save point a;
4. 回滚：rollback;
5. 回滚到中间点：rollback to a;
6. 提交：commit
7. 注：普通的提交、回滚都会结束当前事务，而回滚到中间点时仍处于事务中

05. 简述数据库设计中一对多和多对多的应用场景？

图书管理系统：图书（book），作者（author），出版社（publisher）

图书<---->出版社------>多对一（一对多）一个出版社可以出版多个图书，图书只能有一个出版社

图书<---->作者--------->多对多   一本图书可以有多个作者，一个作者可以有多本图书

06. 如何基于数据库实现商城商品计数器？

https://blog.csdn.net/stevendbaguo/article/details/70889449

如果某个应用中存在计数器，例如网站的总访问量、用户的粉丝数、文件下载数等等。如果相关应用在Mysql数据库的表中保存计数器，在更新计数器的时候可能会碰到并发问题。例如在web应用中，记录网站的点击次数。网站的每次点击都会导致对计数器进行更新。问题来了，对于Mysql数据表的更新操作，都会在所操作的记录上添加一个全局的互斥锁，这样就会导致更新操作只能串行执行，效率低。那如何解决此问题呢？有很多方法能够有效的解决此问题，先说一种从Mysql数据库的角度出发的解决方式：

将计数器保存在计数器表的多行中，每次随机选择一行进行更新，想要获得整个网站的点击统计结果，只需要将表中的所有行相加即可。

07. 简述触发器、函数、视图、存储过程？

http://www.cnblogs.com/fixdq/articles/9036147.html

 

08. MySQL索引种类

https://www.cnblogs.com/yangchunchun/p/7371610.html

 

09. 索引在什么情况下遵循最左前缀的规则？

https://www.cnblogs.com/jamesbd/p/4333901.html

 

10. 主键和外键的区别？

 

定义：

主键：唯一标识一条记录，不能有重复的，不允许为空

外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值

索引：是对数据库表中一列或多列的值进行排序的一种结构

作用：

主键：用来保证数据完整性

外键：用来和其他表建立联系用的

索引：是提高查询排序的速度

个数：

主键：主键只能有一个

外键：一个表可以有多个外键

索引：一个表可以有多个唯一索引

添加：

主键：ALTER TABLE “表名” ADD PRIMARY KEY (字段名)

外键：ALTER TABLE “表名” ADD FOREIGN KEY (字段名) REFERENCES “另一张表名”( 字段名)

索引：ALTER TABLE “表名” ADD INDEX (字段名)

11. MySQL常见的函数？

聚合函数
max/sum/min/avg

时间格式化
date_format

字符串拼接
concat（当拼接了null，则返回null）

截取字符串
substring

返回字节个数
length

 

12. 列举 创建索引但是无法命中索引的8种情况。

1.- like '%xx'
    select * from tb1 where name like '%cn';
2.- 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
3.- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
4.- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
5.- !=
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
6.- >
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
7.- order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
 
8.- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

 

13. 如何开启慢日志查询？

1.查询慢查询是否开启（默认是off）

show variables like 'slow_query_log%';

2.设置慢查询日志文件存储位置(位置可自定义)

set global slow_query_log_file='/var/lib/mysql/mysql-slow.log';

3.开启记录查询中不带索引的sql语句

set global log_queries_not_using_indexes=on;

4.设置日志记录sql语句执行时间最小值（小于此值将不被记录到慢查询日志中）

set global long_query_time=1;

//sql语句执行时间大于1秒的将被记录到慢查询日志中

5.以上设置完成后即可以开启慢查询：

set global slow_query_log=on;

 

14. 数据库导入导出命令（结构+数据）？

导出现有数据库数据：（当有提示出入密码。-p就不用加密码）
  mysqldump -u用户名 -p密码 数据库名称 >导出文件路径           # 结构+数据
  mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件    路径       # 结构 

导入现有数据库数据：
    mysqldump -uroot -p密码  数据库名称 < 文件路径

 

15. 数据库优化方案？

1、创建数据表时把固定长度的放在前面（）
2、将固定数据放入内存： 例如：choice字段 （django中有用到，数字1、2、3…… 对应相应内容）
3、char 和 varchar 的区别(char可变, varchar不可变 )
　　
4、联合索引遵循最左前缀(从最左侧开始检索)
5、避免使用 select * 
6、读写分离
　　　　- 实现：两台服务器同步数据
　　　　- 利用数据库的主从分离：主，用于删除、修改、更新；从，用于查；
读写分离:利用数据库的主从进行分离：主，用于删除、修改更新；从，用于查
7、分库
　　　　- 当数据库中的表太多，将某些表分到不同的数据库，例如：1W张表时
　　　　- 代价：连表查询
8、分表
　　　　- 水平分表：将某些列拆分到另外一张表，例如：博客+博客详情
　　　　- 垂直分表：讲些历史信息分到另外一张表中，例如：支付宝账单

9、加缓存
　　　　- 利用redis、memcache （常用数据放到缓存里，提高取数据速度）


如果只想获取一条数据
     - select * from tb where name=‘alex’ limit 1

 

16. char和varchar的区别？

CHAR(N)和 VARCHAR(N) 中的 N 都代表字符长度,而非字节长度，超过的内容会被截取

char(n)：定长，浪费空间，存取速度快

存储：char类型的值时，会往右填充空格来满足长度

检索：检索时，如果右边有空格会自动删除空格

 

varchar(n)：变长，精准，节省空间，存取速度慢

存储：存储数据的真实内容，不会空格填充，像‘ a  ’里面的空格也会保存

检索：正常取出存的值，不会删除空格

 

下面是存储值的比较（varchar有一个前缀长度用于存储正式数据的长度，）

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	'    '	4 bytes	''	1 byte
'ab'	'ab  '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

17. 简述MySQL的执行计划？

https://www.cnblogs.com/xinysu/p/7860609.html

18. 在对name做了唯一索引前提下，简述以下区别：      

 select * from tb where name = ‘Oldboy’
 select * from tb where name = ‘Oldboy’ limit 1

是这样的的，用where条件过滤出符合条件的数据的同时，进行计数，
比如limit 1，那么在where过滤出第1条数据后，他就会直接把结果select出来返回给你，整个过程就结束了。


没做唯一索引的话,前者查询会全表扫描,效率低些
limit 1,只要找到对应一条数据,就不继续往下扫描.
然而 name 字段添加唯一索引了,加不加limit 1,意义都不大;