MySQL知识点梳理

全面整理MySQL基础知识点，在学习过程中加深自己的理解，同时也希望能帮助到大家~

增删改查

增

格式：INSERT INTO 表名(字段1, 字段2, ...) VALUES (值1, 值2, ...);

INSERT INTO table(id, name) VALUES (1, 'jackkang01');

删

DELETE FROM 表名 WHERE 条件语句;

DELETE FROM table WHERE name='jackkang01';

改

UPDATE 表名 SET 字段1=值1, 字段2=值2, ... WHERE 条件语句;

UPDATE table SET id=1, name='jackkang01';

查

分组查询（GROUP BY）

1. 单独使用

查询结果只会显示每个分组的第一条记录

2. 与GROUP_CONCAT()函数一起使用

GROUP_CONCAT(字段1)：将每个分组的字段1的值均显示出来

3. 数据统计时与聚合函数一起使用

聚合函数包括：

• COUNT()：统计记录的条数
• SUM()：计算字段值的总和
• AVG()：计算字段值的平均值
• MAX()：查询字段的最大值
• MIN()：查询字段的最小值

4. 与WITH ROLLUP一起使用

在查询结果的最后添加一条记录，内容为其他字段的所有记录

连接查询（多表查询）

1. 交叉连接

SELECT 字段1 FROM table1 CROSS JOIN table2 WHERE table1.字段1=table2.字段2

使用交叉连接查询时，会先生成两个表的笛卡尔积，然后再筛选满足条件的语句，当表的数据量非常大时，查询过程会非常慢，因此不建议使用，而建议使用内连接和外连接

2. 内连接

SELECT 字段1 FROM table1 INNER JOIN table2 ON table1.字段1=table2.字段2

多表查询时，在SELECT后要指定字段来源于哪一张表，写法为（表名.字段名）或者（表的别名.字段名）

3. 外连接

内连接的查询结果为符合查询条件的记录，而外连接先将连接的表分为基表和参考表，再以基表为依据返回满足条件的记录，外连接分为左外连接和右外连接

左外连接：SELECT 字段1 FROM table1 LEFT OUTER JOIN table2 ON table1.字段1=table2.字段2

其中，表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）

右外连接与左外连接相反

左外连接和右外连接中的OUTER关键字可省略，简写为LEFT JOIN或者RIGHT

select语句执行顺序

from，where，group by，聚合函数，having，计算表达式，select，order by

from：组装来自不同数据源的数据，所以为表创建别名，别的地方可以用
where：基于指定的条件对记录行进行筛选
group by：将数据划分为多个分组
聚合函数：例如SUM()、COUNT()等，执行顺序在group by后having之前
having：筛选分组
计算表达式
select：字段
order by：对结果集进行排序
           

连接查询

外连接：
    左连接（左外连接）：以左表作为基准进行查询，左表数据会全部显示出来，右表如果和左表匹配的数据则显示相应字段的数据，如果不匹配则显示为null。
    右连接（右外连接）：以右表作为基准进行查询，右表数据会全部显示出来，左表如果和右表匹配的数据则显示相应字段的数据，如果不匹配则显示为null。
    全连接：先以左表进行左外连接，再以右表进行右外连接。
内连接：
    显示表之间有连接匹配的所有行。
           

 存储引擎

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等，数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。 存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型） 在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎 不同的应用软件处理不同类型的数据，MySQL5.5版本及以上默认的存储引擎是InnoDB，mysql之前使用的存储引擎是myisam，但是现在换成了InnoDB，相对于前者，后者使得数据更加安全，但是查询的效率有所降低，也就是牺牲了效率换取安全

• MyISAM：默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

• InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、 删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低 由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback）,对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

• MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

• MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象 引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同 的表分布在多个磁盘上，可以有效地改善 MERGE 表的访问效率。这对于诸如数据仓储等 VLDB 环境十分适合。

MyISAM和InnoDB

MyISAM

默认表类型，不支持事务安全和外键，只支持表锁，不会出现死锁，如果执行大量的select和insert，MyISAM比较适合。select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

参考

InnoDB

支持事务安全，支持外键、行锁、表锁、事务。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。

行锁模式：共享锁，排他锁，意向共享锁(表锁)，意向排他锁(表锁)，间隙锁

死锁：两个事务都需要获得对方持有的排他锁才能完成事务，导致循环锁等待。例如，如果事务A锁住了记录1并等待记录2，而事务B锁住了记录2并等待记录1，这样两个事务就产生死锁现象。

解决死锁的方法：

• 数据库参数
• 应用中尽量约定程序读取表的顺序一样
• 应用中处理一个表时，尽量对处理的顺序排序
• 调整事务隔离级别（避免两个事务同时操作一行不存在的数据，容易发生死锁）

COUNT()

MyISAM有优势，因为MyISAM存储了表中的行数记录，执行SELECT COUNT() 的时候可以直接获取到结果，而InnoDB需要扫描全部数据后得到结果。

注意：对于带有WHERE 条件的 SELECT COUNT()语句，两种引擎的表执行过程是一样的，都需要扫描全部数据后得到结果

全文索引

• MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引
• MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
• 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

性能调优

• 当只要一行数据时使用 limit 1查询时如果已知会得到一条数据，这种情况下加上 limit 1 会增加性能。因为 mysql 数据库引擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。
• 选择合适的数据库引擎，mysql 中有两个引擎 MyISAM 和 InnoDB，每个引擎有利有弊。MyISAM 适用于一些大量查询的应用，但对于有大量写功能的应用不是很好。甚至你只需要update 一个字段整个表都会被锁起来。而别的进程就算是读操作也不行要等到当前 update 操作完成之后才能继续进行。另外，MyISAM 对于 select count(*)这类操作是超级快的。InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用会比 MyISAM 还慢，但是支持“行锁”，所以在写操作比较多的时候会比较优秀。并且，它支持很多的高级应用，例如：事物。
• 用not exists代替not in，not exists用到了连接能够发挥已经建立好的索引的作用，not in不能使用索引，Not in是最慢的方式要同每条记录比较，在数据量比较大的操作时不建议使用这种方式。
• 尽量不采用不利于索引的操作符，如in /not in /is null /is not null /<> 等
• 某个字段总要拿来搜索，为其建立索引，mysql 中可以用 alter table 语句为表中的字段添加索引，语法：alter table 表名 add index 字段名
• 分库分表，主从配置。
• 对于多张大数据量（这里几百条就算大了）的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，性能很差。
• 存储过程执行比一条一条地执行其中的各条语句快
• 决不要检索比需求还要多的数据
• 导入数据前应该关闭自动提交，最好删除索引然后导入完成后新建
• 索引改善数据检索的性能，但是损害了数据插入删除更新的性能
• like很慢，一般来说最好使用fulltext而不是like
• 如果从一个表中删除大量数据，应该使用optimize table回收所用的空间，从而优化表性能
• char比varchar效率高很多，因此确定字符长度字段最好使用char字段
• 随机获取数据时， 避免使用order by rand() limit, 效率会很慢， 建议使用join
• 为查询缓存优化你的查询
• EXPLAIN你的SELECT查询
• 在Join表的时候使用相当类型的例，并将其索引
• 避免 SELECT *
• 永远为每张表设置一个ID
• 使用 ENUM 而不是 VARCHAR
• 从 PROCEDURE ANALYSE() 取得建议
• 尽可能的使用 NOT NULL
• Prepared Statements
• 无缓冲的查询
• 把 IP 地址存成 UNSIGNED INT
• 固定长度的表会更快
• 垂直分割
• 拆分大的 DELETE 或 INSERT 语句
• 越小的列会越快
• 使用一个对象关系映射器(Object Relational Mapper)
• 小心“永久链接”

事务的四大特征

原子性：事务中整个操作要么全部完成，要么全部不完成。
一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性：事务间的操作互不干扰。
持久性：事务一旦完成，就永久保存。
           

事务并发问题

原因：在多用户环境中，在同一时间可能会有多个用户更新相同的记录，这会产生冲突。这就是著名的并发性问题。

实现：最常用的处理多用户并发访问的方法是加锁。当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁的粒度上。锁粒度越小，并发访问越好，同时开销相对越大。数据库的锁粒度，例如：数据行、数据表、数据库实例。

丢失更新：撤销一个事务时，把其他事务已提交的更新数据覆盖（A和B事务并发执行，A事务执行更新后，提交；B事务在A事务更新后，B事务结束前也做了对该行数据的更新操作，然后回滚，则两次更新操作都丢失了）。
脏读：一个事务读到另一个事务未提交的更新数据（A和B事务并发执行，B事务执行更新后，A事务查询B事务没有提交的数据，B事务回滚，则A事务得到的数据不是数据库中的真实数据。也就是脏数据，即和数据库中不一致的数据）。
不可重复读：一个事务读到另一个事务已提交的更新数据（A和B事务并发执行，A事务查询数据，然后B事务更新该数据，A再次查询该数据时，发现该数据变化了）。
覆盖更新：这是不可重复读中的特例，一个事务覆盖另一个事务已提交的更新数据（即A事务更新数据，然后B事务更新该数据，A事务查询发现自己更新的数据变了）。
幻读：一个事务读到另一个事务已提交的新插入的数据（A和B事务并发执行，A事务查询数据，B事务插入或者删除数据，A事务再次查询发现结果集中有以前没有的数据或者以前有的数据消失了）。
           

事务隔离级别

mysql默认的事务隔离级别是'REPEATABLE-READ'，即可重复读

DEFAULT
使用数据库设置的隔离级别（默认），由DBA 默认的设置来决定隔离级别。
READ_UNCOMMITTED
这是事务最低的隔离级别，它充许别外一个事务可以看到这个事务未提交的数据。
会出现脏读、不可重复读、幻读 （隔离级别最低，并发性能高）。
READ_COMMITTED
保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据。
可以避免脏读，但会出现不可重复读、幻读问题（锁定正在读取的行）。
REPEATABLE_READ
可以防止脏读、不可重复读，但会出幻读（锁定所读取的所有行）。
SERIALIZABLE
这是花费最高代价但是最可靠的事务隔离级别，事务被处理为顺序执行。会导致大量超时现象和锁竞争。
保证所有的情况不会发生（锁表）。
           

事务隔离级别	脏读	不可重复读	幻读
未提交读（read-uncommitted）	是	是	是
提交读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

临时表

临时表，顾名思义是一个临时的表，数据库不会将其序列化到磁盘上(有些也会序列化到磁盘上)而是存在于数据库服务器的内存中(因此会增加数据库服务器内存的消耗)，在使用完之后就会销毁。

临时表分为两种：会话临时表和全局临时表，区别在于可用的作用域和销毁的时机不同。

会话临时表只在当前会话(连接)内可用，且在当前会话结束(断开数据库连接)后就会销毁；全局临时表创建后在销毁之前所有用户都可以访问，销毁的时机是在创建该临时表的连接断开且没有其他会话访问时才销毁，实际上在创建全局临时表的会话断开后，其他用户就已经不能在访问该临时表了，但此时该临时表并不会立即销毁，而是等所有正在使用该全局临时表的会话(或者说连接)断开之后才会销毁。当然有时考虑到内存占用的问题，也可以手动销毁(DROP)临时表。

B+Tree索引和Hash索引

索引类型的确定和查询优化

性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一，可以考虑使用索引的主要有两种类型的列：在where子句中出现的列，在join子句中出现的列。
考虑列中值的分布，索引的列的基数越大，索引的效果越好。
使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。
利用最左前缀,顾名思义，就是最左优先，在多列索引，有体现:(ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);)，所谓最左前缀原则就是先要看第一列，在第一列满足的条件下再看左边第二列，以此类推
不要过度建索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。
在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。
MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in
以及某些时候的like(不以通配符%或_开头的情形)。
           

聚集索引和非聚集索引区别？

有哪些锁（乐观锁悲观锁），select 时怎么加排它锁？

非关系型数据库和关系型数据库区别，优势比较？

数据库三范式，根据某个场景设计数据表？

数据库的读写分离、主从复制，主从复制分析的 7 个问题？

使用explain优化sql和索引？

MySQL慢查询怎么解决？

什么是 内连接、外连接、交叉连接、笛卡尔积等？

mysql都有什么锁，死锁判定原理和具体场景，死锁怎么解决？

varchar和char的使用场景？

mysql 高并发环境解决方案？

数据库崩溃时事务的恢复机制（REDO日志和UNDO日志）？