MySQL相关知识点整理
1、关系型数据库与非关系型数据的区别？
| 分类 | 关系型数据库 | 非关系型数据库 |
| 概念 | 关系型数据库（SQL）是由二维表及其之间的联系所组成的一个数据组织，最典型的数据结构是表，即所有的数据都通过行和列的二元表现形式表示出来 | 非关系型数据库又成NoSQL(Not only SQL)，意为不仅仅是SQL。其中数据以对象的形式存储在数据库中，而对象之间的联系通过每个对象自身的属性来决定 |
| 常见数据库 | mysql / oracle / sql server / sqlite | 键值数据库Redis、文档数据库MongoDB、图形数据库InfoGrid、列族数据库Bigtable等 |
| 优点 |
1、保持了数据的一致性（最大优势），可以进行事务处理
2、支持通用的SQL（结构化查询语言）语句
3、可以在一个表以及多个表之间进行复杂的数据查询，如join
4、二维表结构非常贴近逻辑世界的概念，更容易理解
|
1、格式灵活，存储数据的格式可以是key-value形式、文档形式、图片形式等，而关系型数据库则只支持基础类型
2、查询速度快，NoSQL将数据存储于缓存之中，而关系型数据库将数据存储在硬盘中
3、数据之间没有耦合性，非常容易进行水平扩展
4、高并发、高稳定性、成本低廉、可以实现数据的分布式处理
|
| 缺点 |
1、高并发读写能力差，一台数据库的最大连接数有限，硬盘 I/O 也有限，不能满足很多人同时连接
2、海量数据情况下读写效率低。对大数据量的表进行读写操作时，需要等待较长的时间
3、数据结构灵活度低，固定的表结构无法快速容纳新的数据类型
|
1、不支持 SQL 语句，学习和使用成本高
2、没有事务处理，无法保证数据的完整性和安全性，适合处理海量数据，但不一定安全
3、复杂表关联查询不容易实现
|
2、主键和外键的区别
主键：主键能唯一标识一条记录，不能重复，不能为空，一张表只能有一个主键
外键：外键用来建立与其他表的连接，是另一张表的主键，可以重复，可以为空，一张表可以有多个外键
3、CHAR与VARCHAR数据类型的区别？
char的长度是不可变的，varchar的长度是可变的。
当定义的是char(10)，输入的是“abc”这三个字符，他们所占空间是10个字节，其中包括7个空字节；当输入的字符长度超过指定的数时，char会截取超出的字符。
在取数据时，char类型的要去除多余的空格，而对于varchar类型则不需要。
char的存取速度比varchar快，因为其长度固定，方便程序的存储与查找。
char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。varchar则是以空间效率为首位。
char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。
4、临时表
临时表只在当前连接可见，当关闭连接时会被自动删除并释放所有空间，因此可以在不同连接中创建同名的临时表
临时表的创建与语法为CREATE TEMPOPARY TABLE tmp_table
临时表也可以手动删除DELETE TEMPOPARY TABLE IF EXISTS tmp_table
5、常见的数据库函数
max()：最大值
min()：最小值
avg()：平均值
sum()：求和
count() : 统计总数
6、事务
事务（transaction）是作为一个单元的一组有序的数据库操作集合，用来保持数据的完整性和一致性。
事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原。
原子性：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么都不执行
一致性：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果一致
隔离性：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
持久性：对于任意已提交事务，笔筒必须保证该事务对数据库的关系不被丢弃
7、自然连接、内连接、外连接和交叉连接的区别
内连接（INNER JOIN）：同时将两表作为参考对象，根据ON后给出的两表的条件将两表连接起来。结果则是两表同时满足ON后的条件的部分才会列出。
左外连接（LEFT OUTTER JOIN，OUTER可以省略）：返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值
右外连接（RIGHT OUTTER JOIN，OUTER可以省略）：返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值
全外连接（FULL OUTTER JOIN，OUTER可以省略，MySQL不支持）：返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值
交叉连接（CROSS JOIN）：相当于笛卡尔积，它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配
8、DROP/TRUNCATE/DELETE的区别
DROP: DROP TABLE 表名，DDL，删除整个表的数据与结构
TRUNCATE: TRUNCATE TABLE 表名, DDL, 清空表中的数据，但是保留表结构
DELETE: DELETE TABLE 表名 WHERE 条件, DML，既可以删除整张表，也可以删除单行
9、count(*)/count(1)/count(column)的区别
count(*) 对行的数目进行计算，包含NULL
count(1) 与 count(*) 效果相同
count(column) 对特定列的行数进行计算，不包含NULL
10、索引是什么
索引是一种数据结构，数据库索引就是对数据库表中一列或多列的值进行排序的结构，可以帮助数据库实现快速的查询与更新。索引可以理解为书的目录，字典的拼音、偏旁部首检索，都是为了方便查找内容而建立的。
11、索引的优点和缺点
优点：
可以大大加快数据的检索速度（最大的优点）
可以加速表与表之间的连接
在使用分组和排序子句进行索引时，同样能显著减少分组与排序的时间
通过建立唯一性索引，可以保证表中数据的唯一性
通过使用索引，可以在查询过程中使用优化隐藏器，提高系统性能
索引的缺点：
时间：创建索引和维护索引都需要耗费时间，具体来说，当对表中数据进行增删查改时，索引也需要动态地维护
空间：索引需要占据一定的物理空间
12、什么情况下应该创建索引？什么情况下不应该创建索引？
需要创建索引的列：
经常需要搜索的列
显著加快搜索的速度
作为主键的列
强制该列的唯一性和组织表中数据的排列结构
经常用作连接的列（通常是外键）
加快表与表之间的连接速度
经常需要根据范围搜索的列
索引已经排序，其指定的范围是连续的
经常需要排序的列
索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
经常使用在WHERE子句上的列
加快条件的判断速度
不应该创建索引的列：
在查询中很少使用的列不应建立索引
如果列很少被使用到，那么有索引并不能提高查询速度，反而会降低系统的维护速度和增加空间需求
只有很少数据值的列不应建立索引
如信息表中的性别列，只有两种结果值，对其建立索引并不能明显加快检索速度
定义为text、image和bit数据类型的列不应该建立索引
这些列的数据要么数据量相当大，要么取值很少
当修改性能远大于检索性能时不应建立索引
修改性能与检索性能是相互矛盾的，当增加索引时会降低修改性能
13、索引失效的情况
1>、使用!=、<>进行判断会导致索引失效 例如select * from table_name where a != 1 注意：<>符号是不等于的意思，和!=的功能是一样的 2>、数据类型的隐式转换会导致索引失效 假设 select * from table_name where a = '1'会使用到索引，如果没有对字符串加引号，写成select * from table_name where a = 1则会导致索引失效。 3>、在索引中使用函数会导致索引失效 例如select * from table_name where abs(a) = 1 4>、在索引上进行计算会导致索引失效 例如select * from table_name where a + 1 = 2 5>、条件中有OR会导致索引失效 例如select * from table_name where a = 1 or b = 3 注意：并不是所有的OR都会导致索引失效，如果OR连接的是同一字段将不会导致失效 6>、使用模糊查询时以%开头会导致索引失效 例如select * from table_name where name LIKE '%表'
14、SQL优化的方法有哪些？
索引使用技巧：
避免在where子句中使用!=或<>操作符
避免数据类型的隐式转换
避免在索引列上使用内置函数
避免在索引列上使用表达式计算
避免使用OR来连接过滤条件
避免在字段开头模糊查询
尽量避免使用IN和NOT IN
尽量避免进行NULL值的判断
索引并不是越多越好，一个表的索引数最好不要超过5个
其他优化方法
尽量避免使用SELECT *：返回无用的字段会降低查询效率
去重DISTINCT过滤字段要少：当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较与过滤，需要占用时间
批量插入数据：插入是DML语句，默认每条都需要事务开启和事务提交；而批量处理只需要一次事务开启和提交
分批删除数据：一次性删除太多数据可能造成锁表，建议分批操作
提高GROUP BY语句的效率
优先使用inner join：三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小，即小表驱动大表，小的数据集驱动大的数据集，从而让性能更优
尽量使用union all替代union：nion和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
去重的过程同样会占用时间，而实际大部分应用中是不会产生重复的记录
15、分析SQL执行情况
在SQL语句前使用EXPLAIN关键字，可以查看该条SQL语句是否使用了索引 扫描类型TYPE性能由低到高依次为： all：全表扫描，没有优化，最慢的方式 index：需要扫描索引上的全部数据，它仅比全表扫描快一点 range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见于between、<、>等查询 ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于多表查询或普通非唯一索引 eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于多表查询时命中后表的主键或唯一索引 const：命中主键或者唯一索引，且查询条件是一个常量值 system：该表只有一行（相当于系统表）
16、数据库死锁
show engine innodb status;
https://www.cnblogs.com/lqmblog/p/8564486.html
