三大范式
- 第一范式（1NF）：字段具有原子性，不可再分；
- 第二范式（2NF）：数据库表中的每个实例或行必须可以被唯一区分，即有主键；
- 第三范式（3NF）：要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
经常使用的函数
- count(*/column)：返回行数
- sum(column)： 返回指定列中唯一值的和
- max(column)：返回指定列或表达式中的数值最大值
- min(column)：返回指定列或表达式中的数值最小值
- avg(column)：返回指定列或表达式中的数值平均值
- date（Expression）: 返回指定表达式代表的日期值
varchar与char有什么区别？
定长和变长：
char的长度是不可变的，而varchar的长度是可变的
字段b：类型char(10)， 值为：abc，存储为：abc （abc+7个空格）
字段d：类型varchar(10)， 值为：abc，存储为：abc （自动变为3个的长度）
因为其长度固定，char的存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar则刚好相反，以时间换空间。
存储的容量不同：
对 char 来说，最多能存放的字符个数 255，和编码无关。 而 varchar 呢，最多能存放 65532 个字符
如何选择float，double，decimal
1 如果你要表示的浮点型数据转成二进制之后能被32位float存储，或者可以容忍截断，则使用float，这个范围大概为要精确保存6位数字左右的浮点型数据 比如10分制的店铺积分可以用float存储，小商品零售价格(1000块之内)
2 如果你要表示的浮点型数据转成二进制之后能被64位double存储，或者可以容忍截断，这个范围大致要精确到保存13位数字左右的浮点型数据 比如汽车价格,几千万的工程造价
3 相比double，已经满足我们大部分浮点型数据的存储精度要求，如果还要精益求精，则使用decimal定点型存储 比如一些科学数据，精度要求很高的金钱
MyISAM 与 InnoDB 的区别？
存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。
在5.5版本之前默认采用MyISAM存储引擎，从5.5开始采用InnoDB存储引擎。
-
事务和外键
InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作
MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作
-
锁机制
InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现。
MyISAM支持表级锁，锁定整张表
-
索引结构
InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。
MyISAM使用非聚集索引（非聚簇索引），索引和记录分开。
-
并发处理能力
MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。
InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发
mysql 的内连接、左连接、右连接有什么区别？
内连接
通过指定的条件去匹配两张表中的数据,匹配上就显示,匹配不上就不显示
# 隐式内连接
SELECT p.`pname`, p.`price`, c.`cname` FROM products p , category c WHERE p.`category_id` = c.`cid`;
# 显式内连接 inner 可以省略
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;
外连接
左外连接
- 使用 LEFT OUTER JOIN , OUTER 可以省略
- 以左表为基准,匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
- 如果匹配不到, 左表中的数据正常展示, 右边的展示为null
# 左外连接查询
SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;
右外连接
- 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
- 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
- 如果匹配不到，右表中的数据正常展示, 左边展示为null
-- 右外连接查询
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;
什么是脏读？幻读？不可重复读？
- 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
- 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
- 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
什么是事务的隔离级别？MySQL的默认隔离级别是什么？
- READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
MySQL默认隔离级别：可重复读
Oracle、SQLServer默认隔离级别：读已提交
一般使用时，建议采用默认隔离级别，然后存在的一些并发问题，可以通过悲观锁、乐观锁等实现处理。
什么是MVCC？
InnoDB支持的隔离性有4种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。
锁和多版本控制（MVCC）技术就是用于保障隔离性的。
MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。
如何生成的多版本？每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号，该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。
MVCC已经实现了读读、读写、写读并发处理，如果想进一步解决写写冲突，可以采用下面两种方案：乐观锁、悲观锁
MySQL都有哪些锁呢？
从锁的类别上来讲，有共享锁和排他锁。
- 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁就是让多个线程同时获取一个；针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排它锁也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。
按照锁的粒度分数据库锁
-
行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
-
表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
-
页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
MySQL中InnoDB引擎的行锁是怎么实现的？
InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起
使用排他锁的方法是在SQL末尾加上for update，innodb引擎默认会在update，delete语句加上for update。行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么会锁住全表记录。
索引类型
MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。
索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下：
- 从应用层次划分：普通索引、唯一索引、主键索引、复合索引
- 从索引存储结构划分：BTree索引、Hash索引、FULLTEXT全文索引、R Tree索引
- 从索引键值类型划分：主键索引、辅助索引（二级索引）
- 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）
索引的工作机制：
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树
普通索引
这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。
CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
唯一索引
与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。
CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
主键索引
它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。
CREATE TABLE tablename ( [...], PRIMARY KEY (字段名));
ALTER TABLE tablename ADD PRIMARY KEY (字段名);
复合索引
单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。
索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。
CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
全文索引
查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL5.6以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL5.6开始MyISAM和InnoDB存储引擎均支持。
CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字
select * from user where match(name) against('aaa');
全文索引必须在字符串、文本字段上建立。
全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*
select * from user where match(name) against('a*' in boolean mode);
索引结构
InnoDB的索引类型目前只有两种：B+TREE（B树）索引和HASH索引。默认索引实现为 ：B+TREE索引。
哈希索引底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择B+Tree索引。
B+TREE索引
- 树节点中的多个索引值从左到右升序排列
- 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
- 叶子节点包含了所有的索引值和data数据
- 叶子节点用指针连接，提高区间的访问性能
哈希索引
Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找 value值，也就是单个key查询，或者说等值查询。
聚簇索引
聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。
主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。
在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。 主键索引就是聚集索引
InnoDB的表要求必须要有聚簇索引：
- 如果表定义了主键，则主键索引就是聚簇索引
- 如果表没有定义主键，则第一个非空unique列作为聚簇索引
索引使用场景?
- 当数据多且字段值有相同的值得时候用普通索引。
- 当字段多且字段值没有重复的时候用唯一索引。
- 当有多个字段名都经常被查询的话用复合索引。
- 普通索引不支持空值，唯一索引支持空值。
- 若是这张表增删改多而查询较少的话，就不要创建索引了，会对增删改的执行减缓速度
- 更新太频繁地字段不适合创建索引。
创建索引的原则
- 最左前缀匹配原则
- 最左前缀顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。如果从索引的第二列开始查找，索引将失效。
- mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序。
- 较频繁作为查询条件的字段才去创建索引
- 更新频繁字段不适合创建索引
- 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
- 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
- 对于定义为text、image和bit的数据类型的列不要建立索引。
索引分析与优化
EXPLAIN 命令
EXPLAIN SELECT * from user WHERE id < 3;
查询结果有个type字段，查询数据时采用的方式，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。
- ALL：表示全表扫描，性能最差。
- index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
- range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
- ref：表示使用非唯一索引进行单值查询。
- eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
- const：表示使用主键或唯一索引做等值查询，常量查询。
- NULL：表示不用访问表，速度最快。
什么是回表查询？
辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。
所谓的回表其实就是，当我们使用索引查询数据时，检索出来的数据可能包含其他列，但走的索引树叶子节点只能查到当前列值以及主键ID，所以需要根据主键ID再去查一遍数据，得到SQL 所需的列
举个例子，我这边建了给订单号ID建了个索引，但我的SQL 是：select orderId,orderName from orderdetail where orderId = 123
SQL都订单ID索引，但在订单ID的索引树的叶子节点只有orderId和Id，而我们还想检索出orderName，所以MySQL 会拿到ID再去查出orderName给我们返回，这种操作就叫回表
想要避免回表，也可以使用覆盖索引（能使用就使用，因为避免了回表操作）。
什么是索引覆盖？
只需要在一棵索引树上就能获取SQL所需的所 有列数据，无需回表，速度更快，这就叫做索引覆盖。
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。
MySQL在使用like模糊查询时，索引能不能起作用？
MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。
如何判断是否为慢查询？
首先要开启慢查询功能
MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可 以根据自己的业务需要进行调整。
如何判断是否应用了索引？
explain命令分析查看，检查结果中的 key 值，是否为NULL。
说是否使用了索引和是否是慢查询两者之间没有必然的联系。 我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果 扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快。
提高索引过滤性？
尽量少用select *，使用覆盖索引
分页优化？
select * from user limit 10000,100;
select * from user where id>= (select id from user limit 10000,1) limit 100;
使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。
Mysql优化
系统配置优化
首先有系统配置优化，保证从内存中读取数据，数据预热，降低磁盘写入次数等等。
修改 my.cnf innodb_buffer_pool_size = 750M 默认为128M
数据预热：数据库刚刚启动，须要进行数据预热（执行数据预热的脚本），将磁盘上的全部数据缓存到内存中
表结构设计优化
- 对于统计分析功能，或者实时性不高的需求，设计中间表
- 设计冗余字段，减少关联查询，创建合理的冗余字段，（注意数据一致性问题。)
- 对于字段太多的大表，考虑拆表
- 主键优化：每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下 雪花算法）。
- 字段的设计：
- 尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
- 能用数字的用数值类型：例如“性别”。
雪花算法
世界上没有完全相同的两朵雪花
- 能满足高并发分布式系统环境下ID不重复
- 基于时间戳，可以保证基本有序递增（有些业务场景对这个又要求）
- 依赖第三方的库或者中间件
- 生成效率极高
SQL语句及索引优化
使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句 3秒-5秒
使用explain查看有问题的SQL的执行计划，重点查看索引使用情况
1.SQL语句中IN包含的值不应过多
2.SELECT语句务必指明字段名称
3.当只需要一条数据的时候，使用limit 1 (limit是可以停止全表扫描)
4.排序字段加索引
5.少用or
如果限制条件中其他字段没有索引，尽量少用or(or两边的字段中，如果有一个不是索引字段，会造成该查询不走索引的情况)
6.不使用ORDER BY RAND()（不走索引）
select * from tbiguser order by rand() limit 10;
select * from tbiguser t1 join (select rand()*(select max(id) from tbiguser) nid ) t2 on t1.id>t2.nid limit 10;
7.区分in和exists、not in和not exists
原：
select colname … from A表 where a.id not in (select b.id from B表)
高效的SQL语句：
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
8.合理的分页方式：
分页使用 limit m,n 尽量让m 小
9.不建议使用%前缀模糊查询：
例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE“name%”。
那么如何解决这个问题呢，答案：使用全文索引或ES全文检索
10.避免在where子句中对字段进行表达式操作
select user_id,user_project from user_base where age*2=36;
中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：
select user_id,user_project from user_base where age=36/2;
对于联合索引来说，要遵守最左前缀法则
11.注意范围查询语句
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效
MySQL集群架构
主从模式
MySQL主从模式是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，从节点可以复制主数据库中的所有数据库，或者特定的数据库，或者特定的表。
主从部署必要条件：
- 从库服务器能连通主库
- 主库开启binlog日志（设置log-bin参数）
- 主从server-id不同
主从复制整体分为以下三个步骤：
- 主库将数据库的变更操作记录到Binlog日志文件中
- 从库读取主库中的Binlog日志文件信息写入到从库的Relay Log中继日志中
- 从库读取中继日志信息在从库中进行Replay,更新从库数据信息
双主模式
很多企业刚开始都是使用MySQL主从模式，一主多从、读写分离等。但是单主如果发生单点故障，从库切换成主库还需要作改动。因此，如果是双主或者多主，就会增加MySQL入口，提升了主库的可用性。因此随着业务的发展，数据库架构可以由主从模式演变为双主模式。双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。
分库分表
互联网系统需要处理大量用户的请求。比如微信日活用户破10亿，海量的用户每天产生海量的数量；美团外卖，每天都是几千万的订单，那这些系统的用户表、订单表、交易流水表等是如何处理呢？
数据量只增不减，历史数据又必须要留存，非常容易成为性能的瓶颈，而要解决这样的数据库瓶颈问题，“读写分离”和缓存往往都不合适，目前比较普遍的方案就是使用NoSQL/NewSQL或者采用分库分表。
分库分表中间件:
ShardingSphere
