MySQL常规(总结)

1. exist和in的区别

1.1 select * from A where id in(select id from B)

in 先查询子表，再查询主表，不管子查询是否有数据，但是in只执行一次，查出B表中的所有id字段并缓存起来，检查A表的id是否与B表中的id相等，直到遍历完A表所有记录，

所以当子查询数据较大时不适合使用in，因为它会将子查询的表数据全部遍历一次.

如：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次，效率很差.

如：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升.

1.2 select a.* from A a where exists(select 1 from B b where a.id=b.id)

exists先主查询，再子查询，主查询会执行A.length次，然后去子查询中匹配，如果匹配到就退出子查询返回true，将结果放到结果集

当子查询的数据大时适合使用exists，因为它没有将B表都遍历一遍，而是根据A表的数据来决定查询次数

如：A表有10000条记录，B表有1000000条记录，那么exists会执行10000次去判断A表中的id是否与B表中的id相等.

如：A表有10000条记录，B表有100000000条记录，那么exists还是执行10000次

理解：A表有10000条记录，B表有100条记录，那么exists还是执行10000次，不如使用in遍历10000*100次，因为in是在内存里比较，而exists需要查询数据库

1.3 结论

exists适合B表比A表数据大的情况，in适合B表比A表数据小的情况，当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用

2、truncate和delete和drop

2.1 truncate命令：永久地从表中删除所有数据

2.2 delete命令：从一个表中删除某一行或多行数据 

2.3 drop命令：删除整个表（包括结构）

truncate和delete都可以将数据实体删掉，truncate的操作并不记录到rollback日志，操作速度较快，删除数据不能恢复

drop删除表结构；truncate和delete只删除数据，不删除表结构；delete操作不释放表空间

truncate不能对视图等进行删除；delete可以删除单表的视图数据(本质是对表数据的删除) 

删除数据的速度：drop > truncate > delete

delete属于DML语言，需要事务管理，commit之后才能生效；drop和truncate属于DDL语言，操作立刻生效，不可回滚

使用场合：不再需要表时使用drop语句; 保留表删除所有记录用truncate语句; 删除部分记录用delete语句

3、SQL语句的执行顺序

select * from 表名

①-- where 条件1

②-- group by 依据列

③-- having 条件2

④-- order by 依据列

⑤-- limit 0,1

limit对结果进行分页，永远是最后一个（所有的结果里取几个）

order by排序，前提是要首先得到一个查询结果（已经不过滤结果了）

where对全表数据筛选，总是是放在表名的后面

having过滤永远是放在group后面

理解：先全表筛选、再根据筛选结果分组、再对分组进行函数计算、计算结果进行排序、对排序结果进行分页

注：where关键字无法与聚合函数一起使用，having子句可以让筛选分组后的各组数据

group：根据一个或多个列对结果集进行分组

4、日期格式

DATETIME：YYYY-MM-DD HH:MM:SS

TIMESTAMP：YYYY-MM-DD HH:MM:SS

DATETIME的日期范围是1001~9999年，4字节存储；TIMESTAMP的时间范围是1970~2038年，8字节存储

DATETIME存储时间与时区无关；TIMESTAMP存储时间与时区有关，插入时将时间从当前时区转化为UTC（世界标准时间）。查询时,又转化为当前时区进行返回

对于跨时区的业务，TIMESTAMP更为合适

4.1 为什么timestamp到2038年就截止了

timestamp在MySQL中定义的是int类型的数据，int的范围是-2147483648~2147483647,然后1970年到2038年的秒数刚好21亿，为了限制，所以只能截止到2038年

4.2 如何解决2038问题

a. 使用64位二进制数字表示时间的系统

b. 使用DATETIME表示时间，使用时注意转换时区

4.3 timestamp(N)含义

N取值范围为0-6，默认为0，如需要精确到毫秒则设置为timestamp(3)，如需要精确到微秒则设置为timestamp(6)

4.4 高并发环境下并不适合使用timestamp

虽然通过timestamp可以自动转换时区，代价是当MySQL参数time_zone=system时每次都会尝试获取一个全局锁，

在高并发的环境下可能会导致线程上下文频繁切换，CPU使用率暴涨，系统响应变慢甚至假死。

5、连接

左连接(left join)：返回包括左表中的所有记录和右表中连接字段相等的记录。

右连接(right join)：返回包括右表中的所有记录和左表中连接字段相等的记录。

内连接(inner join)：只返回两个表中连接字段相等的记录。

全外连接(full join)：返回左右表中连接字段相等的记录和剩余所有记录。

6、int(10)和char(10)

6.1 int(10)最大显示长度，最大值为255。一般会在int(10)后面设置zerofill属性

如果是int(10)，字段存的值是10，则，显示会自动在之前补8个0，显示为0000000010

注意：在int(M)中，M的值跟int(M)所占多少存储空间并无任何关系。int(1)、int(4)、int(10)在磁盘上都是占用4bytes的存储空间

问题：int(5)存储123456会怎么显示

解析：显示123456，如果字符少于设置宽度，左侧会统统补0；但是如果超出则不受任何影响。

6.2 char(10)和varchar(10)表示存储数据的大小，即表示存储多少个字符

char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间，而varchar(10)表示存储10个变长的字符，存储多少个就是多少个

6.3 char(10)和varchar(10)对比

char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找

char的长度固定，会有多余的空格占位符占据空间

6.4 char和varchar的存储方式

char的存储方式：对英文字符(ASCII)占用1个字节，对一个汉字占用两个字节；

varchar的存储方式：对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。

7、％和_

％代表0或更多字符、_代表1个字符

8、MySQL的存储过程

一段SQL语句集，被编译保存在数据库中供外部程序调用的一种数据库对象

a. 可命名并传入参数来调用执行

b. 可在存储过程中加入业务逻辑和流程

c. 可在存储过程中创建表，更新数据，删除数据等

d. 可通过把SQL语句封装在容易使用的单元中，简化复杂的操作

9、分页查询

使用关键字limit实现分页，limit、offset、size

公式：要显示的页数page，每页的条目数size

select 查询列表 from 表 limit (page-1)*size,size;

9.1 如何优化

数据表中的记录默认使用主键（一般为id）排序

先定位偏移位置的id，然后往后查询，这种方式适用于id递增的情况（两个查询嵌套）

如：

select * from orders_history where type=8 and
id>=(select id from orders_history where type=8 limit 100000,1)
limit 100;

10、数据库并发策略

并发控制一般采用三种方法，分别是乐观锁和悲观锁以及时间戳。

乐观锁：认为一个用户读数据的时候，别人不会去写自己所读的数据；

悲观锁：刚好相反，觉得自己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；

时间戳：不加锁，通过时间戳来控制并发出现的问题。