当前位置 博文首页 > zydybaby：MySQL数据库基础知识及优化
MySQL数据库基础知识及优化必会的知识点，你掌握了多少？
推荐阅读：
这些必会的计算机网络知识点你都掌握了吗
关于数据库事务和锁的必会知识点，你掌握了多少？
关于数据库索引，必须掌握的知识点
子查询：把一个查询的结果在另一个查询中使用
子查询可以分为以下几类：
标量子查询：指子查询返回的是一个值，可以使用 =,>,<,>=,<=,<>等操作符对子查询标量结果进行比较，一般子查询会放在比较式的右侧。
SELECT * FROM user WHERE age = (SELECT max(age) from user) //查询年纪最大的人
列子查询：指子查询的结果是n行一列，一般应用于对表的某个字段进行查询返回。可以使用IN、ANY、SOME和ALL等操作符，不能直接使用
SELECT num1 FROM table1 WHERE num1 > ANY (SELECT num2 FROM table2)
行子查询：指子查询返回的结果一行n列
SELECT * FROM user WHERE (age,sex) = (SELECT age,sex FROM user WHERE name="zhangsan")
表子查询：指子查询是n行n列的一个数据表
SELECT * FROM student WHERE (name,age,sex) IN (SELECT name,age,sex FROM class1) //在学生表中找到班级在1班的学生
MySQl的连接查询主要可以分为外连接，内连接，交叉连接
外连接
外连接主要分为左外连接(LEFT JOIN)、右外连接(RIGHT JOIN)、全外连接。
左外连接：显示左表中所有的数据及右表中符合条件的数据，右表中不符合条件的数据为null。
右外连接：显示左表中所有的数据及右表中符合条件的数据，右表中不符合条件的数据为null。
MySQL中不支持全外连接。
交叉连接：使用笛卡尔积的一种连接。
笛卡尔积，百度百科的解释：两个集合X和Y的笛卡尔积表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 。例如：A={a,b}，B={0,1,2}，A × B = {(a,0)，(a,1)，(a,2)，(b,0)，(b,1)，(b,2)}
举例如下：有两张表分为L表和R表。
L表
| A | B |
|---|---|
| a1 | b1 |
| a2 | b2 |
| a3 | b3 |
R表
| B | C |
|---|---|
| b1 | c1 |
| b2 | c2 |
| b4 | c3 |
左外连接 ：select L.`*`,R.`*` from L left join R on L.b=R.b
| A | B | B | C |
|---|---|---|---|
| a1 | b1 | b1 | c1 |
| a2 | b2 | b2 | c2 |
| a3 | b3 | null | null |
右外连接：select L.`*`,R.`*` from L right join R on L.b=R.b
| B | C | A | B |
|---|---|---|---|
| b1 | c1 | a1 | b1 |
| b2 | c2 | a2 | b2 |
| b4 | c3 | null | null |
内连接：select L.`*`,R.`*` from L inner join R on L.b=R.b
| A | B | B | C |
|---|---|---|---|
| a1 | b1 | b1 | c1 |
| a2 | b2 | b2 | c2 |
交叉连接：select L.`*`,R.`*` from L,R
| A | B | B | C |
|---|---|---|---|
| a1 | b1 | b1 | c1 |
| a1 | b1 | b2 | c2 |
| a1 | b1 | b4 | c3 |
| a2 | b2 | b1 | c1 |
| a2 | b2 | b2 | c2 |
| a2 | b2 | b4 | c3 |
| a3 | b3 | b1 | c1 |
| a3 | b3 | b2 | c2 |
| a3 | b3 | b4 | c3 |
in和exists一般用于子查询。
int(10)中的10表示的是显示数据的长度，而char(10)和varchar(10)表示的是存储数据的大小。
| drop | delete | truncate | |
|---|---|---|---|
| 速度 | 快 | 逐行删除，慢 | 较快 |
| 类型 | DDL | DML | DDL |
| 回滚 | 不可回滚 | 可回滚 | 不可回滚 |
| 删除内容 | 删除整个表，数据行、索引都会被删除 | 表结构还在，删除表的一部分或全部数据 | 表结构还在，删除表的全部数据 |
一般来讲，删除整个表，使用drop，删除表的部分数据使用delete，保留表结构删除表的全部数据使用truncate。
union和union all的作用都是将两个结果集合并到一起。
MySQL在执行SQL语句的时候会临时创建一些存储中间结果集的表，这种表被称为临时表，临时表只对当前连接可见，在连接关闭后，临时表会被删除并释放空间。
临时表主要分为内存临时表和磁盘临时表两种。内存临时表使用的是MEMORY存储引擎，磁盘临时表使用的是MyISAM存储引擎。
一般在以下几种情况中会使用到临时表：
慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志。
相关参数：
如何对慢查询进行优化？
主键是唯一区分表中每一行的唯一标识，如果没有主键，更新或者删除表中特定的行会很困难，因为不能唯一准确地标识某一行。
使用自增ID的好处：
使用自增ID的缺点：
UUID：通用唯一标识码，UUID是基于当前时间、计数器和硬件标识等数据计算生成的。
使用UUID的优点：
使用UUID的缺点：
最后说下结论，一般情况MySQL推荐使用自增ID。因为在MySQL的InnoDB存储引擎中，主键索引是一种聚簇索引，主键索引的B+树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增ID，只需要按顺序往后排列即可，如果是UUID，ID是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降。
首先说一点，NULL和空值是不一样的，空值是不占用空间的，而NULL是占用空间的，所以字段设为NOT NULL后仍然可以插入空值。
字段设置成not null主要有以下几点原因：
NULL值会影响一些函数的统计，如count，遇到NULL值，这条记录不会统计在内。
B树不存储NULL，所以索引用不到NULL，会造成第一点中说的统计不到的问题。
NOT IN子查询在有NULL值的情况下返回的结果都是空值。
例如user表如下
| id | username |
|---|---|
| 0 | zhangsan |
| 1 | lisi |
| 2 | null |
select * from `user` where username NOT IN (select username from `user` where id != 0)，这条查询语句应该查到zhangsan这条数据，但是结果显示为null。
MySQL在进行比较的时候，NULL会参与字段的比较，因为NULL是一种比较特殊的数据类型，数据库在处理时需要进行特数处理，增加了数据库处理记录的复杂性。
从减少数据访问方面考虑：
从返回更少的数据方面考虑：
从减少服务器CPU开销方面考虑：
从增加资源方面考虑：
在LIMIT偏移量较大的时候，查询效率会变低，可以记录每次取出的最大ID，下次查询时可以利用ID进行查询
建立复合索引
如果不需要对结果集进行去重或者排序建议使用UNION ALL，会好一些。
SELECT DISTINCT
select_list
FROM
left_table
LEFT JOIN
right_table ON join_condition
WHERE
where_condition
GROUP BY
group_by_list
HAVING
having_condition
ORDER BY
order_by_condition
执行顺序如下：
FROM：对SQL语句执行查询时，首先对关键字两边的表以笛卡尔积的形式执行连接，并产生一个虚表V1。虚表就是视图，数据会来自多张表的执行结果。
ON：对FROM连接的结果进行ON过滤,并创建虚表V2
JOIN：将ON过滤后的左表添加进来，并创建新的虚拟表V3
WHERE：对虚拟表V3进行WHERE筛选，创建虚拟表V4
GROUP BY：对V4中的记录进行分组操作，创建虚拟表V5
HAVING：对V5进行过滤，创建虚拟表V6
SELECT：将V6中的结果按照SELECT进行筛选，创建虚拟表V7
DISTINCT：对V7表中的结果进行去重操作，创建虚拟表V8，如果使用了GROUP BY子句则无需使用DISTINCT，因为分组的时候是将列中唯一的值分成一组，并且每组只返回一行记录，所以所有的记录都h是不同的。
ORDER BY：对V8表中的结果进行排序。
垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。
垂直分表的优势：
避免IO竞争减少锁表的概率。因为大的字段效率更低，第一数据量大，需要的读取时间长。第二，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多。
可以更好地提升热门数据的查询效率。
垂直分库：按照业务对表进行分类，部署到不同的数据库上面，不同的数据库可以放到不同的服务器上面。
垂直分库的优势：
垂直拆分（分库、分表）的缺点：
水平分表：在同一个数据库内，把同一个表的数据按照一定规则拆分到多个表中。
水平分表的优势：
水平分库：把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。
水平分库的优势：
水平拆分（分表、分库）的缺点：
在系统设计时应根据业务耦合来确定垂直分库和垂直分表的方案，在数据访问压力不是特别大时应考虑缓存、读写分离等方法，若数据量很大，或持续增长可考虑水平分库分表，水平拆分所涉及的逻辑比较复杂，常见的方案有客户端架构和恶代理架构。
分库分表后不能每个表的ID都是从1开始，所以需要一个全局ID，设置全局ID主要有以下几种方法：
UUID：优点：本地生成ID，不需要远程调用；全局唯一不重复。缺点：占用空间大，不适合作为索引。
数据库自增ID：在分库分表表后使用数据库自增ID，需要一个专门用于生成主键的库，每次服务接收到请求，先向这个库中插入一条没有意义的数据，获取一个数据库自增的ID，利用这个ID去分库分表中写数据。优点：简单易实现。缺点：在高并发下存在瓶颈。系统结构如下图（图片来源于网络）
Redis生成ID：优点：不依赖数据库，性能比较好。缺点：引入新的组件会使得系统复杂度增加
Twitter的snowflake算法：是一个64位的long型的ID，其中有1bit是不用的，41bit作为毫秒数，10bit作为工作机器ID，12bit作为序列号。
1bit：第一个bit默认为0，因为二进制中第一个bit为1的话为负数，但是ID不能为负数.
41bit：表示的是时间戳，单位是毫秒。
10bit：记录工作机器ID，其中5个bit表示机房ID，5个bit表示机器ID。
12bit：用来记录同一毫秒内产生的不同ID。
美团的Leaf分布式ID生成系统，美团点评分布式ID生成系统
MySQL复制：为保证主服务器和从服务器的数据一致性，在向主服务器插入数据后，从服务器会自动将主服务器中修改的数据同步过来。
主从复制的原理：
主从复制主要有三个线程：binlog线程，I/O线程，SQL线程。
复制过程如下（图片来源于网络）：
这里补充一个通俗易懂的图。
主从复制的作用：
读写分离主要依赖于主从复制，主从复制为读写分离服务。
读写分离的优势：
