mysql 知识搜集

explain 查看sql语句运行情况
explain extended 查看语句运行情况
show warnings 查看优化后的语句

ACID

原子性Atomicity：事务中的语句不可分割
一致性Consistency：事务必须使数据库从一个一致性状态切换到另一个一致性状态，比如两个人之间转钱
隔离性Isolation：多线程情况下，事务不能彼此干扰
持久性Durability：事务一旦提交，应该被永久保存

mysql被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打个删除标记而已，而且这部分存储空间之后还可以重用，也就是说之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。
如果您想彻底的从磁盘上移除这些被删除的记录，可以使用这个语句：

optimize table '表名';
alter table '表名' engine=InnoDB;

mysql 引擎

MyISAM 
innodb 
ISAM
Memory
Falcon
Archive
MRG_MyISAM
CSV
HEAP HEAP允许只驻留在内存里的临时表格

数据库隔离级别

读未提交（read-uncommitted）(脏读)
不可重复读（read-committed），一个事物中第一次查询的结果和第二次查询的结果不一致 
可重复读（repeatable-read）默认设置
串行化（serializable）

聚簇索引，非聚簇索引，覆盖索引，主键索引，非主键索引，索引下推，回表，索引跳跃扫描

聚簇索引：命中了索引就找到了需要的数据，例如主键索引（数据和索引在一起存储的索引方式，一张表只能有一个聚簇索引）
非聚簇索引：找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引

覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询。

主键索引 除叶子节之外的其他节点都存储的是主键值。而叶子节点上存储的是整行的数据
非主键索引 叶子节点上存储的是主键的值
非主键索引上查找一行数据时，此时的查找方式是先搜索非主键索引树，拿到对应的主键值，再到主键索引树上查找对应的行数据，这种操作就叫作回表操作。（前提条件是，所要查找的字段不存在于非主键索引树上，需要先定位非主键索引，再去定位主键索引，最后定位数据）

索引下推就是只有符合条件再进行回表，（主要针对联合索引，5.6以上版本才有）没有索引下推的情况下，存储引擎层会查出所有索引匹配的记录然后在服务层中过滤，启用索引下推后，把where条件由MySQL服务层放到了存储引擎层去执行，带来的好处就是存储引擎根据id到表格中读取数据的次数变少了

回表，mysql 存储 索引和数据是分开的，索引选择器会选择建字段的索引，找出rowid, 然后用rowID去非空唯一索引树里面找数据，这个过程就是回表

索引跳跃扫描 mysql8.0 当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。就是匹配的时候遇到第一列索引就跳过，直接匹配第二列索引的值，这样就可以用到联合索引了

共享锁，排他锁，读锁，写锁

mysql锁分为共享锁和排他锁，也叫做读锁和写锁
读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。
间隙锁是可重复读级别下才会有的锁，结合MVCC和间隙锁可以解决幻读的问题。

唯一索引是不会有间隙索引的。

脏读，幻读, 虚读

幻读说的是存不存在的问题：原来不存在的,现在存在了,则是幻读(可重复读和串行化下，行级锁，读锁定，乐观锁下可减少)
1.事务 A 根据条件查询得到了 N 条数据，但此时事务 B 删除或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候真实的数据集已经发生了变化，但是A却查询不出来这种变化，因此产生了幻读。
2.A事务select 某记录是否存在，结果为不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。产生这样的原因是因为有另一个事务往表中插入了数据

脏读，A事物修改了数据，B也读取了修改后的数据，A失败回滚，B读的数据就是脏数据 （读已提交和可重复读下使用事物可避免）

虚读 一个线程中的事务读取到了另外一个线程中提交的update的数据（读取之后再操作，发现数据被更改了）
不重复读 一个线程中的事务中，遇到另一个线程中事务提交了新数据，该事务会两次读取到的数据结果不一致

mysql主从同步的原理

1.master提交完事务后，写入binlog
2.slave连接到master，获取binlog
3.master创建dump线程，推送binglog到slave
4.slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中
5.slave再开启一个sql线程读取relay log事件并在slave执行，完成同步
6.slave记录自己的binglog

mysql 主从切换数据丢失问题

1.全同步复制：主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，比较耗性能
2.半同步复制：从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成

mysql主从延迟解决方案

1. 针对特定的业务场景，读写请求都强制走主库
2. 读请求走从库，如果没有数据，去主库做二次查询

mysql规范：

explain语句
delete 和 update 加limit
表和字段加注释
关键字大小一致
insert添加对应字段名称
sql先在测试环境测试
where、order by、group by 后面的列添加索引
 尽量把所有列定义为 NOT NULL
索引命名要规范，主键索引名为 pk_ 字段名；唯一索引名为 uk _字段名 ；普通索引名则为 idx _字段名
查询不对索引列进行转换，对入参进行转换
数据量过多，批量处理
适量冗余字段
不建议超过20个字段，常用字段提炼，非常用字段单独设置表
分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;
Op:表示执行的操作.
Msg_type:信息类型,有status,info,note,warning,error.
Msg_text:显示信息.
检查表: 使用 CHECK关键字,如CHECK TABLE user [option]
option 只对MyISAM有效,共五个参数值:
QUICK:不扫描行,不检查错误的连接.
FAST:只检查没有正确关闭的表.
CHANGED:只检查上次检查后被更改的表和没被正确关闭的表.
MEDIUM:扫描行,以验证被删除的连接是有效的,也可以计算各行关键字校验和.
EXTENDED:最全面的的检查,对每行关键字全面查找.
优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;
LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,
优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.
优化数据库参数
key_buffer_size:索引缓冲区大小
table_cache:能同时打开表的个数
query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
sort_buffer_size:排序缓冲区
分库分表（按主键hash分，按业务隔离级别分，按时间分）
innodb不支持hash索引，myisam不支持btree索引
时间戳用整型存储，ip转整型存储
字符字段只建前缀索引
少用外键和唯一索引，由逻辑实现


避免使用OR
避免使用select *
不要使用like '%xx' %在左边时索引失效
使用复合索引时没有遵循最左匹配原则
不要让数据类型出现隐式转化
不要在索引字段上使用not，<>，!=，一样会导致索引失效
不要在索引字段上进行计算操作和使用函数
使用JOIN优化，减少子查询
使用事务，事务中减少长计算和不要添加CURL交互
批量操作，减少逐条写入
字符串编码要保持一致
数据集太少也有可能不走索引
用 exists 代替 in 是一个好的选择
不要全表扫描

小表驱动大表 即小的数据集驱动大的数据集

尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
单表不要有太多字段，建议在15以内
尽量使用TIMESTAMP而非DATETIME
使用枚举或整数代替字符串类型
VARCHAR的长度只分配真正需要的空间
用整型来存IP

select * 查询效率低下原因

增加查询分析器解析成本。
增减字段容易与 resultMap 配置不一致。
无用字段增加网络 消耗，尤其是 text 类型的字段
1. 不需要的列会增加数据传输时间和网络开销
用“SELECT * ”数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。
增大网络开销；* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。如果DB和应用程序不在同一台机器，这种开销非常明显
即使 mysql 服务器和客户端是在同一台机器上，使用的协议还是 tcp，通信也是需要额外的时间。
2. 对于无用的大字段，如 varchar、blob、text，会增加 io 操作
准确来说，长度超过 728 字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次 io 操作。（MySQL InnoDB）
3. 失去MySQL优化器“覆盖索引”策略优化的可能性
SELECT * 杜绝了覆盖索引的可能性，而基于MySQL优化器的“覆盖索引”策略又是速度极快，效率极高，业界极为推荐的查询优化方式。
联合索引 (a,b,c) 实际建立了 (a)、(a,b)、(a,b,c) 三个索引
数据量小的表不需要建立索引，建立会增加额外的索引开销
不经常引用的列不要建立索引，因为不常用，即使建立了索引也没有多大意义
经常频繁更新的列不要建立索引，因为肯定会影响插入或更新的效率
数据重复且分布平均的字段，因此他建立索引就没有太大的效果（例如性别字段，只有男女，不适合建立索引）
数据变更需要维护索引，意味着索引越多维护成本越高。
更多的索引也需要更多的存储空间

binlog, relaylog, errorLog, slowQueryLog, general log, redo log，undoLog

binlog的主要作用是记录数据库中表的更改，它只记录改变数据的sql，不改变数据的sql不会写入，不是循环使用，会新建
relay-log中继日志,主要是主从同步用
errorlog 错误日志文件
slowQueryLog 慢查询日志
general log日志记录功能，它可以记录所有对数据库的操作和查询语句，调试和性能分析时才开启
redo log 事物日志，数据库崩溃或意外故障 恢复数据库时使用，循环使用
undoLog（innodb引擎专有）undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录

mysql查询出现次数大于1的记录

select ename,count(ename) as num from emp group by ename having num >1 order by num asc ;

having 后跟的筛选条件所包含的字段必须是select后展示的字段或者group by 的条件字段
where是对查询结果之前的字段进行筛选，having是对查询结果之后的字段进行筛选
where可以和group by having结合使用，但是where要在group by 之前使用

mysql 删除多表记录

从数据表t1中把那些id值在数据表t2里有匹配的记录全删除掉
DELETE t1 FROM t1,t2 WHERE t1.id=t2.id    
DELETE FROM t1 USING t1,t2 WHERE t1.id=t2.id
从两个表中找出相同记录的数据并把两个表中的数据都删除掉
DELETE t1,t2 from t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25

mysql 设置步长(自增间隔)

ALTER TABLE my_table AUTO_INCREMENT = 1 INCREMENT = 2;
SET GLOBAL auto_increment auto_increment=你的值;

replace into 和 on duplicate key update

1、当插入数据主键或者unique索引没有冲突：
replace into和on duplicate key update的做法都是直接插入数据
2、出现主键或者unique索引冲突时：
replace into ：先删除此行数据，然后插入新的数据
on duplicate key update：只是执行update后面的语句属性的更新，其余属性不变。


innodb_autoinc_lock_mode中有3种模式,0,1,2，数据库默认是1的情况下,每次使用insert into … on duplicate key update 的时候都会把简单自增id增加,不管是发生了insert还是update
innodb_autoinc_lock_mode参数详解
tradition(innodb_autoinc_lock_mode=0) 模式：
1、它提供了一个向后兼容的能力
2、在这一模式下，所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的，一个事务可能包涵有一个或多个语句。
3、它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。
4、由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。
consecutive(innodb_autoinc_lock_mode=1) 模式：
1、这一模式下去simple insert 做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的(它保证了基于语句复制的安全)
2、这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁
interleaved(innodb_autoinc_lock_mode=2) 模式：
由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

B树和B+树区别

B树
  树内的每个节点都存储数据
  叶子节点之间无指针相邻
B+
  数据只出现在叶子节点
  所有叶子节点增加了一个链指针

Mysql中数据遍历操作比较多，所以用B+树作为索引结构
Mongodb是做单一查询比较多，数据遍历操作比较少，所以用B树作为索引结构