【MySQL基础知识】MySQL数据库知识点梳理

迈不过友情╰ 2023-02-24 11:26 59阅读 0赞

### MySQL数据库知识点目录 ###

*  Mysql数据库（端口：3306）
 *   *  1. 创建数据库
     *  2. 用户管理
     *  3. 授权管理
     *  4. 修改表的列
     *  5. 键的操作
     *  6. 设置默认值
     *  7. SQL语句基础
     *  8. 排序
     *  9. 聚合函数
     *  10. 分组
     *  11. 关联查询
     *  12. 组合
     *  13. LIMIT的用法
     *  14. case when
     *  15. mysql 常用数据类型
     *  16. MySQL读取数据的基本单位
     *  17. MySql的事务隔离级别
     *  18. MySQL支持哪些存储引擎
     *   *  (1) InnoDB存储引擎
         *  (2) MyISAM存储引擎
         *  (3) MEMORY存储引擎
         *  (4) MERGE存储引擎
         *  (5) Archive存储引擎
         *  (6) EXAMPLE存储引擎
         *  (7) NDB Cluster存储引擎
         *  (8) FEDERATED存储引擎
         *  (9) CSV存储引擎
         *  (10) BLACKHOLE存储引擎
     *  19. MyISAM和InnoDB的区别
     *  20. 为什么mysql事务回滚后，自增ID依然自增
     *  21. mysql的主从复制的实现过程
     *  22. mysql高并发环境解决方案

# Mysql数据库（端口：3306） #

## 1. 创建数据库 ##

-- utf-8
    CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
    
    -- gbk
    CREATE DATABASE db_name DEFAULT CHARSET gbk COLLATE gbk_chinese_ci;

## 2. 用户管理 ##

-- 创建用户
    create user '用户名'@'IP地址' identified by '密码';
    
    -- 删除用户
    drop user '用户名'@'IP地址';
    
    -- 修改用户
    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
    
    -- 修改密码
    set password for '用户名'@'IP地址' = Password('新密码');
    -- 或者用update更新user
    update user set password=password('123') where user='root' and host='localhost';
    
    -- 修改需要刷新才生效
    flush privileges;

## 3. 授权管理 ##

-- 查看权限
    show grant for '用户名'@'IP地址';
    
    -- 授权
    grant 权限 on database1.table1 to '用户名'@'IP地址';
    
    -- 取消权限
    revoke 权限 on database1.table1 from '用户名'@'IP地址';

## 4. 修改表的列 ##

-- 添加列
    alter table table_name add column int;
    
    -- 默认添加到最后一列
    alter table t1 add age int default 0;
    
    -- 添加到第一列
    alter table t1 add addr char(12) first;
    
    -- 添加到addr列后面
    alter table t1 add phone after addr;
    
    -- 删除列
    alter table t1 drop phone;
    
    -- 修改列，只修改类型
    alter table t1 modify column phone INTEGER;
    
    -- 列名和类型同时修改
    alter table t1 change column name myname varchar(20);

## 5. 键的操作 ##

-- 添加主键
    alter table t1 add primary key(id);
    
    -- 删除主键
    alter table t1 drop primary key(id);
    alter table t1 modify id int, drop primary key;
    
    -- 添加外键
    alter table 从表 add constraint 从键名称 foreign_key 从表(外键字段)
    
    -- 删除外键
    alter table table_name drop foreign_key key_name

## 6. 设置默认值 ##

-- 设置默认值
    alter table t1 alter age set default 18;
    
    -- 删除默认值
    alter table t1 alter age drop default;

## 7. SQL语句基础 ##

**[SQL基础知识][SQL]**

*  select语句

> 格式：select 字段 from 表名； 全部字段可以用 \*

*  where 用于限制查询的结果

> 格式：`where 字段='xxx';` 查询条件> < >= <= = !=

*  与(AND)或(OR)
 *  在(IN)不在(NOT IN)
 *  空(NULL)非空(NOT NULL)
 *  全部(ALL) 任一(ANY)
 *  在\[a,b\]之间

> 格式：between a and b

*  排重DISTINCT

> 格式：`select DISTINCT 字段 from 表名;`

*  insert语句

-- 插入数据
    insert into 表名(列名,列名) values(值,值);
    insert into tb1(id, name) values(1, 'luck');

*  update语句

-- 更新数据
    update tb1 set name = 'zhangsan' where id > 1;

*  delete语句

-- 删除数据
    delete from tb1 where id=1 and name = 'luck';

## 8. 排序 ##

*  ORDER BY语句

> 格式：`select 字段 from 表名 where 条件 ORDER BY 字段;`

*  升序(ASC)与降序(DESC)

> 格式：`select 字段 from 表名 where 条件 ORDER BY 字段 ASC;`

> 格式：`select 字段 from 表名 where 条件 ORDER BY 字段 DESC;`

*  多项排序

> 格式：`select 字段 from 表名 where 条件 ORDER BY 字段 ASC|DESC，字段ASC|DESC;`

-- 升序
    select * from 表 order by 列 asc;
    
    -- 降序
    select * from 表 order by 列 desc;
    
    -- 多项排序
    select * from 表 order by 列1 desc,列2 asc;

## 9. 聚合函数 ##

> 把 select 语句的查询结果汇聚成一个结果，这样的函数叫聚合函数。

*  最大值(MAX)
 *  最小值(MIN)
 *  平均值(SVG)
 *  求和(SUM)
 *  统计数量(COUNT)

## 10. 分组 ##

*  GROUP BY

> 格式：`select 组函数 from 表 where 条件 group by 字段;`  
> 特别的`group by` 必须在where之后,`order by`之前

*  HAVING 组判断条件，它的真假决定一组数据是否返回

> 格式：`select 组函数 from 表 where 条件 group by 字段 having 组判断条件;`

select num from tb1 group by num;
    
    select num, id from tb1 group by num, id;
    
    select num, id from tb1 where id > 5 group by num, id order by id desc;
    
    select num, id count(*), sum(score), max(score), min(score) from tb1 group by num, id;
    
    select num from tb1 group by num having max(id) > 10;

## 11. 关联查询 ##

*  JOIN：如果表中有至少一个匹配，则返回行

> 格式：`select * from a join b on a.id=b.id;`

*  内连接(INNER JOIN)：只返回两个表中联结字段相等的行

> 格式：`select * from a inner join b on a.id=b.id;` 其中inner可以省略，等同于JOIN的用法

*  左外连接(LEFT JOIN 或 LEFT OUTER JOIN)：即使右表中没有匹配，也从左表返回所有的行

> 格式：`select * from a left outer join b on a.id=b.aid;` 其中outer可忽略

*  右外连接(RIGHT JOIN 或 RIGHT OUTER JOIN)：即使左表中没有匹配，也从右表返回所有的行

> 格式：`select * from a right outer join b on a.id=b.aid;` 其中outer可忽略

*  全连接(FULL JOIN 或 FULL OUTER JOIN): 只要其中一个表中存在匹配，就返回行。相当于左外连接+右外链接，注意mysql不支持全连接

> 格式：`select * from a full outer join b on a.id=b.id;` 其中outer可忽略

*  自连接：自连接意思是把自身表当成另外一张表看待，互相关联查询，连接方式可以使用以上的内外连接，这种连接方式可以解决很多奇怪的问题。

> 格式：`SELECT ab.* from a ab,a ac where ab.id>ac.id;`

## 12. 组合 ##

-- 组合，自动处理重合
    select username from A union select name from B;
    
    -- 组合，不处理重合
    select username from A union all select name from B;

## 13. LIMIT的用法 ##

> 格式：`select * from student limit 10;` 查询前10条数据，显示1-10条数据

> 格式：`select * from student limit 1,10;` 查询从第2行开始，累加10条id记录，共显示id为2…11

> 格式：`select * from student limit 5,10;` 查询从第6行开始向前加10条数据，共显示id为6,7…15

> 格式：`select * from student limit i,n;` i: 为查询结果的索引值(默认从0开始),当i=0时可省略i; n: 为查询结果返回的数量

## 14. case when ##

**[CASE WHEN 及 SELECT CASE WHEN的用法][CASE WHEN _ SELECT CASE WHEN]**  
**[SQL Case when 的使用方法][SQL Case when]**

*  简单Case函数

CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
    ELSE '其他' END

*  Case搜索函数

CASE WHEN sex = '1' THEN '男'
    WHEN sex = '2' THEN '女'
    ELSE '其他' END

> 两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

**实例分析：**

(1). 已知数据按照另外一种方式进行分组，分析

/*根据这个国家人口数据，统计亚洲和北美洲的人口数量*/
    SELECT  SUM(population),
    CASE country  WHEN '中国' THEN '亚洲'
      WHEN '印度' THEN '亚洲'
      WHEN '日本' THEN '亚洲'
      WHEN '美国' THEN '北美洲'
      WHEN '加拿大'  THEN '北美洲'
      WHEN '墨西哥'  THEN '北美洲'
    ELSE '其他' END
    FROM    Table_A
    GROUP BY
    CASE country 
    WHEN '中国' THEN '亚洲'
    WHEN '印度' THEN '亚洲'
    WHEN '日本' THEN '亚洲'
    WHEN '美国' THEN '北美洲'
    WHEN '加拿大'   THEN '北美洲'
    WHEN '墨西哥'   THEN '北美洲'
    ELSE '其他' END;

/*判断工资的等级，并统计每一等级的人数*/
    SELECT
    CASE WHEN salary <= 500 THEN '1'
    WHEN salary > 500 AND salary <= 600  THEN '2'
    WHEN salary > 600 AND salary <= 800  THEN '3'
    WHEN salary > 800 AND salary <= 1000 THEN '4'
    ELSE NULL END salary_class, -- 别名命名
    COUNT(*)  FROM    Table_A
    GROUP BY
    CASE WHEN salary <= 500 THEN '1'
    WHEN salary > 500 AND salary <= 600  THEN '2'
    WHEN salary > 600 AND salary <= 800  THEN '3'
    WHEN salary > 800 AND salary <= 1000 THEN '4'
    ELSE NULL END;

(2). 用一个SQL语句完成不同条件的分组

/*按照国家和性别进行分组*/
    SELECT country, 
    SUM( CASE WHEN sex = '1' THEN  population ELSE 0 END),  --男性人口
    SUM( CASE WHEN sex = '2' THEN  population ELSE 0 END)   --女性人口
    FROM  Table_A  GROUP BY country;

(3). 在Check中使用Case函数

/*公司A，这个公司有个规定，女职员的工资必须高于1000块*/
    CONSTRAINT check_salary CHECK
    ( CASE WHEN sex = '2'
    THEN CASE WHEN salary > 1000
    THEN 1 ELSE 0 END
    ELSE 1 END = 1 )

(4). 根据条件有选择的UPDATE

/* 1.工资5000以上的职员，工资减少10% */
    UPDATE Personnel  SET salary = salary * 0.9  WHERE salary >= 5000;
    
    /* 2.工资在2000到4600之间的职员，工资增加15% */
    UPDATE Personnel  SET salary = salary * 1.15 WHERE salary >= 2000 AND salary < 4600;
    
    /* 3.如果满足条件1和条件2，顺序执行则会出现问题。
    假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。
    接下来运行第二个SQL时候，因为这个人的工资是4500在2000到4600的范围之内，需增加15%，
    最后这个人的工资结果是5175,不但没有减少，反而增加了。
    如果要是反过来执行，那么工资4600的人相反会变成减少工资。 
    这里用到case when 进行不同条件的更新*/
    UPDATE Personnel
    SET salary =
    CASE WHEN salary >= 5000  　                THEN salary * 0.9
         WHEN salary >= 2000 AND salary < 4600  THEN salary * 1.15
    ELSE salary END;  -- 这行else必须写，不写会导致不符合这两个条件的工资会变成NULL

/* 把主键a和b相互交换 */
    UPDATE SomeTable
    SET p_key = CASE WHEN p_key = 'a'  THEN 'b'
    WHEN p_key = 'b'  THEN 'a'  ELSE p_key END
    WHERE p_key IN ('a', 'b');

*  检查两个表数据是否一致

> Case函数不同于DECODE函数。在Case函数中，可以使用BETWEEN,LIKE,IS NULL,IN,EXISTS等等。比如说使用IN,EXISTS，可以进行子查询，从而 实现更多的功能。

--使用IN的时候
    SELECT keyCol,
    CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B )  THEN 'Matched'
    ELSE 'Unmatched' END Label
    FROM tbl_A;
    
    --使用EXISTS的时候
    SELECT keyCol,
    CASE WHEN EXISTS ( SELECT * FROM tbl_B  WHERE tbl_A.keyCol = tbl_B.keyCol )  THEN 'Matched'  ELSE 'Unmatched' END Label
    FROM tbl_A;

*  在Case函数中使用合计函数

/* Studentclass
    +------+----------+--------------+-----------------+
    |std_id| class_id |  class_name  |  main_class_flg |
    +------+----------+--------------+-----------------+
    | 100  |    1     |     经济学    |        Y        |
    | 100  |    2     |     历史学    |        N        |
    | 200  |    2     |     历史学    |        N        |
    | 200  |    3     |     考古学    |        Y        |
    | 200  |    4     |     计算机    |        N        |
    | 300  |    4     |     计算机    |        N        |
    | 400  |    5     |      化学     |        N        |
    | 500  |    6     |      数学     |        N        |
    +------+----------+---------------+----------------+
    */
    
    --条件1：只选择了一门课程的学生, 返回那门课程的ID
    SELECT std_id, MAX(class_id) AS main_class  FROM Studentclass  GROUP BY std_id  HAVING COUNT(*) = 1;
    
    --条件2：选择多门课程的学生, 返回所选的主课程ID
    SELECT std_id, class_id AS main_class  FROM Studentclass  WHERE main_class_flg = 'Y';
    
    --使用Case函数,满足以上两个条件
    SELECT  std_id, 
    CASE
    WHEN COUNT(*) = 1                   THEN MAX(class_id)
    ELSE
    MAX(CASE WHEN main_class_flg = 'Y'  THEN class_id  ELSE NULL END)
    END AS main_class
    FROM Studentclass  GROUP BY std_id;

*  select case when

> select 与 case结合使用最大的好处有两点，一是在显示查询结果时可以灵活的组织格式，二是有效避免了多次对同一个表或几个表的访问。

/* 例如表 students(id, name ,birthday, sex, grade)，要求按每个年级统计男生和女生的数量各是多少，统计结果的表头为，年级，男生数量，女生数量。如果不用select case when，为了将男女数量并列显示，统计起来非常麻烦，先确定年级信息，再根据年级取男生数和女生数，而且很容易出错。*/
    
    SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1
                                ELSE NULL
                            END) 男生数,
                   COUNT (CASE WHEN sex = 2 THEN 1
                                ELSE NULL
                           END) 女生数
    FROM students
    
    GROUP BY grade;

## 15. mysql 常用数据类型 ##

*  字符串类型
    
     *  CHAR：0-255 bytes, 存定长字符串
     *  VARCHAR：0-65535 bytes, 存不定长字符串
     *  TINYBLOB: 0-255 bytes, 不超过 255 个字符的二进制字符串
     *  TINYTEXT: 0-255 bytes, 短文本字符串
     *  BLOB: 0-65535 bytes, 二进制形式的长文本数据
     *  TEXT: 0-65535 bytes, 长文本数据
     *  MEDIUMBLOB: 0-16777215 bytes, 二进制形式的中等长度文本数据
     *  MEDIUMTEXT: 0-16777215 bytes, 中等长度文本数据
     *  LONGBLOB: 0-4294967295 bytes, 二进制形式的极大文本数据
     *  LONGTEXT: 0-4294967295 bytes, 极大文本数据
 *  数值类型
    
     *  TINYINT: 1 bytes, 有符号范围(-128，127)、无符号范围(0，255)
     *  SMALLINT: 2 bytes, 有符号范围(-32768，32767)、无符号范围(0，65535)
     *  MEDIUMINT: 3 bytes, 有符号范围(-8388608，8388607)、无符号范围(0，16777215)
     *  INT/INTEGER: 4 bytes, 有符号范围(-2147483648，2147483647)、无符号范围(0，4294967295)
     *  BIGINT: 8 bytes, 有符号范围(-9223372036854775808，9223372036854775807)、无符号范围(0，18446744073709551615)
     *  FLOAT: 4 bytes, 有符号范围\[(-3.402823466E+38，-1.175494351 E-38)，0，(1.175494351E-38，3.402823466351E+38)\]、无符号范围`[0，(1.175494351E-38，3.402823466E+38)]`
     *  DOUBLE: 8 bytes, 有符号范围\[(-1.7976931348623157E+308，-2.2250738585072014E-308)，0，(2.2250738585072014E-308，1.797 6931348623157E+308)\]、无符号范围\[0，(2.2250738585072014 E-308，1.7976931348623157E+308)\]
     *  DECIMAL: DECIMAL(M, D), 若M>D，这为大小为M+2，否则为D+2
 *  日期类型
    
     *  DATE: 3 bytes, `YYYY-MM-DD（1000-01-01/9999-12-31）`
     *  TIME: 3 bytes, `HH:MM:SS（'-838:59:59'/'838:59:59'）`
     *  YEAR: 1 bytes, `YYYY（1901/2155）`
     *  DATETIME: 8 bytes, `YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）`
     *  TIMESTAMP: 4 bytes, `YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）`

## 16. MySQL读取数据的基本单位 ##

> 在 MySQL 中数据读取的基本单位都是页，InnoDB 中页的默认大小是 16KB。

## 17. MySql的事务隔离级别 ##

*  读未提交（Read uncommitted）

> 事物A和事物B，事物A未提交的数据，事物B可以读取到"脏数据"；隔离级别低，一般都高于该级别。

*  读已提交（Read committed）

> 事物A和事物B，事物A提交的数据，事物B才能读取到;隔离级别高于读未提交，可以避免脏读，但是可能会导致不可重复读和幻读。（是Oracle默认隔离级别）

*  可重复读（Repeatable read）

> 事务A和事务B，事务A提交之后的数据，事务B是可重复读取数据这时事务B读取不到；该隔离级别高于读已提交，可以避免不可重复读，但有可能导致幻读，MySQL（也是InnoDB）默认隔离级别。

*  串行化（Serializable）

> 事务A和事务B，事务A在操作数据库时，事务B只能排队等待；这种隔离级别很少使用，吞吐量太低，用户体验差，这种级别可以避免脏读、重复读、幻读，每一次读取的都是数据库中真实存在数据，这时事务A与事务B串行，而不是并发。

**[四个隔离级别可能出现脏读、不可重复读、幻读问题][Link 1]**

<table> 
 <thead> 
  <tr> 
   <th align="center">隔离级别</th> 
   <th align="center"></th> 
   <th align="center">出现情况</th> 
   <th align="center"></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"></td> 
   <td align="center">脏读</td> 
   <td align="center">不可重复读</td> 
   <td align="center">幻读</td> 
  </tr> 
  <tr> 
   <td align="center">读未提交</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
  </tr> 
  <tr> 
   <td align="center">读已提交</td> 
   <td align="center">×</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
  </tr> 
  <tr> 
   <td align="center">可重复读</td> 
   <td align="center">×</td> 
   <td align="center">×</td> 
   <td align="center">√</td> 
  </tr> 
  <tr> 
   <td align="center">串行化</td> 
   <td align="center">×</td> 
   <td align="center">×</td> 
   <td align="center">×</td> 
  </tr> 
 </tbody> 
</table>

## 18. MySQL支持哪些存储引擎 ##

**[MySQL的存储引擎的特点比较][MySQL]**

### (1) InnoDB存储引擎 ###

> MySQL的默认存储引擎，提供事务安全表，该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。用于事务处理应用程序，具有众多特性，包括ACID事务支持。相对于MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

> InnoDB主要特点：

*  InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎；
 *  InnoDB有强大的CPU效率，能够处理巨大数据量；
 *  InnoDB存储引擎完全与MySQL服务器整合，将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件，表可以是任何尺寸；
 *  InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。

### (2) MyISAM存储引擎 ###

> MyISAM存储引擎是管理非事务表，不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表，支持3种不同的存储格式，分别是：静态表（字段长度固定）、动态表（字段长度动态不固定）、压缩表（每个记录单独被压缩，访问开支小）。默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

> MyISAM的主要特点：

*  大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持；
 *  当把删除和更新及插入操作混合使用的时候，动态大小的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成；
 *  每个MyISAM表最大索引数是64，这可以通过重新编译来改变，每个索引最大的列数是16；
 *  NULL被允许在索引的列中，这个值占每个键的0~1个字节；
 *  可以把数据文件和索引文件放在不同目录；

### (3) MEMORY存储引擎 ###

> Memory存储引擎使用存在于内存中的内容来创建表。每个Memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

### (4) MERGE存储引擎 ###

> Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，Merge表本身并没有数据，对Merge类型的表可以进行select、update、delete操作，这些操作实际上是对内部的MyISAM表进行的。允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

### (5) Archive存储引擎 ###

> 存储引擎被用来无索引的、非常小的覆盖存储大量数据。为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

### (6) EXAMPLE存储引擎 ###

> EXAMPLE存储引擎是一个“存根”引擎，可以用这个引擎创建表，但没有数据被存储于其中或从其中检索，这个引擎的目的是服务，可为快速创建定制的插件式存储引擎提供帮助。

### (7) NDB Cluster存储引擎 ###

> MySQL的簇式数据库引擎，是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

### (8) FEDERATED存储引擎 ###

> FEDERATED存储引擎把数据存在远程数据库中。在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

### (9) CSV存储引擎 ###

> CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。

### (10) BLACKHOLE存储引擎 ###

> BLACKHOLE存储引擎接受但不存储数据，并且检索总是返回一个空集。用于临时禁止对数据库的应用程序输入。

## 19. MyISAM和InnoDB的区别 ##

<table> 
 <thead> 
  <tr> 
   <th align="center"></th> 
   <th align="left">MyISAM</th> 
   <th align="left">InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">构成</td> 
   <td align="left">表定义文件：.frm；数据文件：.MYD；索引文件：.MYI；</td> 
   <td align="left">表大小只限制与操作系统文件的大熊啊，一般为2G</td> 
  </tr> 
  <tr> 
   <td align="center">事务处理</td> 
   <td align="left">性能快，但不支持事务和外键</td> 
   <td align="left">支持事务，支持外键</td> 
  </tr> 
  <tr> 
   <td align="center">增删改查</td> 
   <td align="left">适合执行大量的SELECT操作</td> 
   <td align="left">执行大量数据的INSERT或UPDATE；删除不会重新建表，而是一行行删除</td> 
  </tr> 
  <tr> 
   <td align="center">AUTO_INCREMENT操作</td> 
   <td align="left">MyISAM 为 INSERT 和 UPDATE 操作自动更新这一列；</td> 
   <td align="left">在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，仅被存储在主内存中，而不是存在磁盘上</td> 
  </tr> 
  <tr> 
   <td align="center">表行数</td> 
   <td align="left">执行<code>select count(*) from table</code>保存行数</td> 
   <td align="left">执行<code>select count(*) from table</code>且要扫描整表去计算行数</td> 
  </tr> 
  <tr> 
   <td align="center">锁</td> 
   <td align="left">表级锁</td> 
   <td align="left">行级锁</td> 
  </tr> 
  <tr> 
   <td align="center">全文索引</td> 
   <td align="left">支持全文索引</td> 
   <td align="left">支持全文索引(&gt;=5.6)</td> 
  </tr> 
  <tr> 
   <td align="center">多版本并发控制</td> 
   <td align="left">不支持MVCC</td> 
   <td align="left">支持MVCC</td> 
  </tr> 
 </tbody> 
</table>

## 20. 为什么mysql事务回滚后，自增ID依然自增 ##

> 因为`innodb`的`auto_increament`的计数器记录的当前值是保存在存内 存中的，并不是存在于磁盘上，当`mysql server`处于运行的时候，这个计数值只会随着`insert`改增长，不会随着`delete`而减少。

> 当`mysql server`启动时，当我们需要去查询`auto_increment`计数值时，mysql便会自动执行：`SELECT MAX(id) FROM 表名 FOR UPDATE;`语句来获得当前`auto_increment`列的最大值，然后将这个值放到`auto_increment`计数器中。所以就算Rollback MySQL的`auto_increament`计数器也不会作负运算。

## 21. mysql的主从复制的实现过程 ##

**[MySQL主从复制的实现过程][MySQL 1]**

*  主从复制的作用
    
     *  主数据库出现问题，可以切换到从数据库
     *  可以进行数据库层面的读写分离
     *  可以在从数据库上进行日常备份
 *  主从复制的过程
    
     *  (1). master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中.
     *  (2). salve开启一个`I/O Thread`，该线程在master打开一个普通连接，主要工作是`binlog dump process`。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志（Realy log）中。
     *  (3). SQL Thread会读取中继日志（Realy log），并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。
 *  具体步骤
    
     *  准备两台服务器，安装mysql
    
    > 主库是：192.168.x.x master  
    > 从库是：192.168.x.xx slave
    
     *  主库操作：
        
         *  （1）开启binlog功能，修改`vim /etc/my.cnf`配置
        
            [mysqld]
            
            server-id=1
            
            log-bin=/var/log/mysql/mysql-bin
            
            innodb_flush_log_at_trx_commit = 1
            sync_binlog = 1
        
         *  （2）创建日志目录并赋予权限，重启mysql，使得binlog生效
        
            
            mkdir /var/log/mysql
            
            chown mysql.mysql /varlog/mysql
            
            systemctl restart mysqld
        
         *  （3）登录mysql，检查主库的状态
        
            show master status;
        
         *  （4）创建一个用户，用于进行主从同步
        
            create user 'database_backup'@'%' identified by 'database_backup_666';
        
         *  （5）授予账号权限，授予一个从库的身份权限
        
            grant replication slave on . to 'database_backup'@'%';
            flush privileges;
        
         *  （6）锁定mysql的表，防止数据写入
        
            flush table with read lock;
            flush privileges;
        
         *  （7）这一步需要等下面到处数据完成后，然后解除锁表
        
            unlock tables;
            flush privileges;
     *  导出数据
        
         *  导出当前的数据，用于slave机器导入数据，保证在一个起始点
        
            mysqldump -u root -p --all-databases > /data/db.dump;
        
         *  将此db.dump文件远程传输给 slave机器，用于导入
        
            scp /data/db.dump root@192.168.x.x:/tmp/
     *  从库操作：
        
         *  （1）修改`vim /etc/my.cnf`配置
        
            [mysqld]
            server-id=2
        
         *  （2）重启mysql，使得binlog生效
        
            
            systemctl restart mysqld
        
         *  （3）登录slave从库，导入主库的数据信息
        
            source /tmp/db.dump;
        
         *  （4）从数据库上进行复制行为的相关配置
        
            change master to master_host='192.168.x.xx',
            master_user='database_backup',
            master_password='database_backup_666',
            master_log_file='mysql-bin.000001',  -- 根据show master status给出的状态
            master_log_pos=666;  -- 根据show master status给出的状态
        
         *  （5）启动slave线程
        
            slave start;
        
        \-（6）检查slave状态，检查两条参数，如果都是yes，即主从ok
        
            show slave status;

## 22. mysql高并发环境解决方案 ##

*  水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力；
 *  集群方案，解决DB宕机带来的单点DB不能访问问题；
 *  读写分离策略，极大限度提高了应用中Read数据的速度和并发量。无法解决高写入压力；

**持续跟新，更多MySQL基础知识，[详见个人博客归纳][Link 2]**。

**欢迎留言补充，批评指正！**

[SQL]: https://www.runoob.com/sql/sql-tutorial.html
[CASE WHEN _ SELECT CASE WHEN]: https://www.cnblogs.com/aipan/p/7770611.html
[SQL Case when]: https://www.cnblogs.com/cx-zyq/archive/2013/05/16/3082295.html
[Link 1]: https://blog.csdn.net/JIESA/article/details/51317164
[MySQL]: https://blog.csdn.net/holysll/article/details/89197810
[MySQL 1]: https://www.cnblogs.com/cocoxu1992/p/10670589.html
[Link 2]: https://holysll.github.io/2020/05/08/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9F%A5%E8%AF%86/