@[toc]

一. Mysql数据库

1.Mysql数据类型

1.1 字段长度

• mysql的字段可以通过类似 char(10)的方法来指定字段长度， 如果你不设置mysql会根据所选字段选择默认长度的，例如char为255，造成空间的浪费。
• 对数值类型设置最大长度不影响储存的空间，仅仅只能约束字段的最大值，因为数值类型占用的空间是固定的
• 非空标记占一个长度
• varchar标记占一个长度
• 数值类型超过最大长度电话会溢出错误，字符类型超过最大长度会被截断

1.2 字段类型

下面列出mysql常用字段类型

1.2.1 数值类型

1.2.2 字符串类型

• char存放定长字符串，不足补空（与java中的char不同）
• varchar存放不定长字符串，节省空间，但会多一个长度的损耗

1.2.3 日期和时间类型

java中通常用bigint(22)储存的时间戳，免去时区转换

1. 三大范式和五大约束

1.1 三大范式

范式级别越高,对数据表要求的越严格

• 第一范式(最低)
  所有关系型数据库自动满足;列不可分割,原子性
• 第二范式
  表中所有非主键字段完全依赖主键,不能部分依赖;
• 第三范式
  非主键字段不能依赖其他的非主键字段(依赖传递的过程)在依赖

问题：
三大范式是为了减少数据库文件的存储空间,减少维护数据库的开销，具体来说就是多做关联,多做外键,表拆分，带来的问题就是对会对性能造成影响，于是出现了反范式。

1.2 反范式

但是现在的系统对性能要求高， 对存储要求低（空间换时间） ， 所以出现了一套反范式
反范式： 只要违反了第二范式和第三范式 ， 就能做到空间换时间 ， 获的最大的效率。
实现：
单表设计;复合主键;冗余字段

锁设计

增删：排它锁

1.3 数据库五大约束

1. primary KEY:设置主键约束；

   alter table tb1 add primary key(id)

2. UNIQUE：设置唯一性约束，不能有重复值；

   tel varchar（20） unique

3. DEFAULT ：默认值约束

   tel varchar（20） DEFAULT '13968903652'

4. NOT NULL：设置非空约束，该字段不能为空；

   name varchar(10) not null

5. FOREIGN key :设置外键约束 （现在基本不使用）

   alter table xuesheng add foreign key(ban_id) refrences banji(id)

2 索引

通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件

• 性能：可大幅增加数据库的查询性能,适合读多写少的场景
• 代价：需要额外空间保存索引,插入更新删除时,由于更新索引增加额外的开销

2.1 索引的分类

主键：
索引列中的值唯一,允许出现空值，只有一个

alter table tb1 add primary key(id)   -- 主键
alter table tb1 add primary key(id,name)  -- 联合主键

唯一索引:
特殊的唯一索引不允许出现空值

alter table xuesheng add unique(name)

索引/联合索引：
索引列中的值不唯一

create index id_index on kecheng(id)
create index id_index on kecheng(id,name)

全文索引：
只有 char、varchar、text 可以建立全文索引，优于模糊查询like

create fulltext index content_tag_fulltext on fulltext_test(content,tag);

2.2 索引在什么情况下失效：

1. 模糊右查询

select * from test where user_name like “jack%”`

1. 数据类型转换，Where约束的字段为字符串时，不加引号

select * from test where tu_mdn=13333333333;

1. 对索引进行位运算，函数运算(+，-，*，/，! 等)

select * from test where id-1=9;

1. where使用比较查询时（>,< ,!=，not exists） 由于索引依靠b+tree等值查找
2. order by中没有用到where中的索引字段，则索引失效
3. 联合索引
   联合索引必须按顺序执行，已知联合索引（a，b，c）
   用到了索引：a->b->c，b->c->a （mysql的组合优化），a->b，
   无法使用索引：a->c，b->c，（其他导致索引不连续的情况）

2.1 B+ Tree

索引使用B+Tree二分查找

一个磁盘块内能保存2个数据，和3个指向分子磁盘块的指针，一个小于，一个之间，一个大于

3 事务

3.1 事务的ACID

A：Atomicity原子性

通过undo log 来实现 事务中的所有操作要么全部完成，要么就像全部没有发生一样

C：Consistency一致性

一致性通过隔离性来实现 无论事务成功和失败，数据的完整性不会被破坏，例如 a+b=2000 依然相等。

I：Isolation隔离性

通过锁来实现 事务在未提交时在各自的空间操作，互不可见，有4个隔离级别

D：Durability持久性

通过redo log 来实现 数据一旦被事务提交，其状态就保持不变，数据被持久化下来

3.2 事务的 redo 和 undo 操作

为了保证事务的ACID，引入了redo和undo操作，在事务执行过程中

1. 从磁盘读取数据，加载到内存
2. undo 会将修改前的数据记录到undo log 中（用于事务回滚，一致性保证）
3. redo 会将修改后的数据记录到redo log中（用于断电数据恢复，持久性保证）
4. 然后提交事务，从内存持久化到磁盘中

要点：

• redo log 本不是单独写入，而是存入redo log buffer，然后一起写入
• 并发事务共享redo log储存空间，按顺序交替记录在一起节省空间（一个写入可能会导致其他事务的写入磁盘）
• redo log 使用了顺序IO，加快了写入速度（分配在连续空间上，节省磁盘寻址时间）
• redo log 在事务回滚后不会删除，mysql会自行判断

3.3 数据的四大隔离级别和三大问题

四大隔离级别：

• read-uncommitted 脏读，不可重复读，幻读
• read-committed 不可重复读，幻读
• repeatale-read 幻读（数据库默认类型）
• serializable 线性进程，数据安全但性能差

可能导致的三大问题：

• 脏读：事务提交前可读，那么别的事务可能读取到别的事务修改后的值
• 不可重复读，事务提交后刻度，杜绝了脏读，但是如果事务进行了多次读取，依然会读到其他线程修改后的数据
• 幻读：通过添加行锁解决了不可重复读，但是2次读取整表的数据量依然会出现不同，必须添加表锁，性能进一步降低

4 SQL语句

4.1 数据表管理

添加字段

alter table user add id int first                        字段添加到最前
alter table user add email varchar(100) after name    字段添加在X之后

修改字段

alter table user change gender sex char(1)                修改字段名
alter table user modify sex varchar(2)                    修改字段类型
alter table user modify email varchar(100) after id    修改字段排序在x之后
alter table user modify age int first                    修改字段排在首位

删除字段

alter table user drop height                                删除字段

删除和重建表

drop table if exists user                                删除表
truncate table user                                        重建表，相比删除数据效率高

4.2 数据的增删改查

insert into user（id,name） values(1,‘zhangsan’)
insert into user values(‘zhangsan’)
insert into user values(1,‘张三’,18)
delete from user where id=4; 删除一条数据
update user set id=999,name=’lisi’ where id=1 更新一条数据
select * from user 查询数据

4.3 Select sql排序

Select employee_id from employees 
where employee_id<50 
Group by department_id 
having salary>5000
order by employee_id asc
limit 1

4.2 Like，Distinct，Commit，注释

Like/模糊搜索

_：通配符，匹配一个任意字符
%：通配符，匹配多个任意字符

SELECT * FROM student WHERE NAME  LIKE '%陈%'

Distinct/去重

select distinct a from ……            去除a的重复值
select distinct a,b from ……            去除ab字段的组合重复值

Commont/备注

create table tb1(
id int(10) primary key auto_increment commont ‘添加备注’,
name varchar(10),    
gender char(1),
)engine=innodb charset=uft8  ## 我是注释

注释

第一种  #我是注释1  
第二种  /*我是注释2   */
第三种  -- 我是注释3，前面要加空格

4.4 多行/聚合函数

多行/聚合函数会忽略null的值，null值不参与运算

• sum() 和
• avg() 平均数
• max() 最大值
• min() 最小值
• count() 计数

4.5 多表连接查询

表之间的关系

• 一对一： 两个表为平行关系，可以看做一张大表的拆分
• 一对多： 两个表为从属关系，副表作为主表的选项附加
• 多对多： 主表下有两个从表，这两个表构成互质组合

内连接
仅保留符合条件的数据

select
 e.employee_id,e.first_name,e.salary,
 d.department_id,d.department_name
from
   employees e,departments d
where
   e.department_id=d.department_id;

左连接（外连接）
保留左边的的主表数据，同时添加右表符合条件的数据

Select * from a left join b on a.id = b.id

右连接（外连接）
与左连接相反

全外连接

Select * from a full join b on a.id = b.id  //保存笛卡尔积X * Y = X + Y

4.6 hash连接和loop连接的区别

mysql中可以通过in和exist判断元素是否存在表内，具体如下

select * from A where cc in(select cc from B)　　-->效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)　　-->效率高，用到了B表上cc列的索引。

exist使用loop嵌套循环，就是先扫描一个驱动表，然后再拿着扫描出来的记录一条一条去另外一个表中去查找匹配。如果驱动表上有索引，那样查找速度更快些。如果驱动表的返回的记录特别的大，那么效率不是很高。这种方式适合返回少量行的查询。

in使用哈希连接(Hash Join)，先扫面一张表，将表中的Key进行哈希，根据哈希结果装进不同的“桶”中，然后再扫描另外一个表的Key，进行Hash之后对应之前的桶。这样就能快速在内存中对记录进行匹配。如果查询返回的记录很少，则效率不高，因为他是对每个Key进行扫描并做Hash处理。如果返回大量行，则这种方式的处理效率是很高的

5. Mysql小技巧

5.1 Y/N排序

mysql默认按第一个字段正序排序，如果多字段排序，则从指定的第一个字段起依次排序。如果相同，从下个字段开始继续拍。如果字段都相同，那么从第一个字段开始排序。

由此可以得到一个技巧，如果说需要排序Y和N，由于字符再排序时会解析成ascⅡ码，Y在N后面，所以只要DESC就能把Y放到前面来

5.2 实现推荐功能

在不添加字段和不使用createdtime字段的情况下，可以把推荐状态视为createtime+100年，那么在DESC排序时推荐字段就自动排到前面来了

5.3 查询结果纵向显示

sql中select 如果太长,可以在后面放\G，竖行显示~~~~

mysql> select * from tf_product\G;
结果为

*************************** 2. row ***************************
               id: 2
            title: 亲子照片
     title_remark: 0
            price: 22900
        old_price: 0
       title_img1: site_upload/title_img1/20170831/1504180526_253355237.png
       title_img2: site_upload/title_img2/20170831/1504180526_156898435.png
       high_light: 
           is_new: 0
           is_rec: 1
is_coupon_enabled: 1
           status: 1
           is_del: 1
          addtime: 1504180526

6. MySQL调优

表结构与索引

• 分库分表,读写分离
• 为字段选择合适的数据类型（比如）
• 将字段多的表分拣成多个表,增加中间表
• 混合范式与反范式,适当冗余
• 为查询创建必要索引,但避免滥用
• 尽可能地是以哦那个NOT NULL

SQL语句优化

• 避免使用左侧模糊查询
• 寻找最需要优化的语句:分析慢查询日志
• 使用频繁或效率最低的
• 利用查询工具:explain,profile
• 避免使用SELECT (主要是加载内存和网络传输的消耗，``解析为具体字段消耗忽略不计)
• 尽可能使用prepared statements
• 使用索引扫描来排序，并排除索引失效的语句
• 不要使用not in 而是使用not exist来查询
• 外表大时使用in查询快，内表小时使用exist查询快
• 避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序
  MySQL参数优化
• 硬件及系统配置
• 从1到4优化成本增加,优化效果降低

二. 加分项

了解新特性
知道数据可表设计原则,有设计经验
有过数据可调优经验
消息队列使用经验,不同场景下的取舍

三. 真题汇总

使用过消息队列吗,在什么场景使用,用来解决什么问题
使用队列是如何保证可靠性
MQ有可能发生重复消费吗,如何解决
数据库查询语句很慢,如何优化
数据库事务有哪些特性,事务隔离级别有哪几种
如何随SQL语句进行优化

1.4 查询员工数量大于10的部门名称
departments部门表

employee员工表

语句：

• e.department_id ：写明统计的department_id是哪张表的
• INNER JOIN ：使用内连接去除部门人数为0的结果并连接两表
• 使用Group BY + HAVING ：筛选员工数量大于10的部门

SELECT 
department_name ,COUNT(e.department_id) AS num
FROM departments AS d
INNER JOIN employees AS e
ON d.department_id=e.department_id
GROUP BY e.department_id
HAVING num >10

结果

1.5 部门表添加了员工人数统计字段empCount，要求用sql更新该字段

• 使用子查询创建虚拟表 e，统计每个部门的员工人数
• 使用update 同时更新两表

UPDATE 
departments,
(SELECT department_id,COUNT(1) as num 
FROM employees
GROUP BY department_id)AS emp
SET empCount=num 
WHERE departments.department_id=emp.department_id