MySQL总结

零碎知识点总结，主要来源于《深入浅出MySQL》

SQL分类：
DDL（Data Definition Languages）数据定义语言：create drop alter
DML（Data Manipulation Language）数据操纵语句：insert delete update select
DCL（Data Control Language）数据控制语句：grant revoke
change 和 modify change需要写两次列名，可以修改列名称
with rollup是可选语法，表明是否对分类聚合后的结果进行再汇总
having和where的区别：having对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤
内连接：选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录
左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
auto_increment primary key/unique key not null 只允许有一列，如果是组合索引，也必须是第一列
char和varchar：char定长，末尾用空格填充 varchar不定长，保存字符串及长度
注意：char和varchar后面的数字代表字符数，而不是字节数，例如varchar（4）最多可保存4个英文字母，最多可保存4个汉字
InnoDB存储引擎，建议使用varchar类型，内部的行存储没有区分固定长度和可变长度列（所有数据行都使用指向数据列值得头指针）
MyISAM：不支持事务，不支持外健
InnoDB：支持事务，支持外键
外键的定义：constraint 外键名 foreign key（字段名） references 表名（字段名）
在创建索引时，可以指定在删除，更新父表时，对子表进行相应操作
restrict，no action 限制在子表有关联记录下父表不能更新，删除
cascade：父表在更新或删除时，更新或删除子表对应记录
set null：父表在更新或者删除时，子表的对应记录被set为null
锁表时，开启新事务，会隐含执行unlock
commit，rollback只能对事务类型的表进行处理，对lock方式加的表锁，不能通过rollback进行回滚。
通过explain分析：
all：全表扫描 index：索引全扫描 range：索引范围扫描
ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
eq_ref ： 唯一索引，对于每一个索引键值，表中只有一条记录匹配。
常用的SQL优化：
1.大批量插入数据：
1）按主键排序 2）导入前执行set unique_checks = 0 导入后执行 set unique_checks = 1 3）导入前执行 set auto_commit = 0 导入后执行 set auto_commit = 1
2.优化insert语句
使用多个值表的insert语句
3.优化order by 语句
必须满足两个条件才能避免使用filesort 1）覆盖索引 2）满足最左前缀
4.优化group by语句
指定order by null
5.优化嵌套查询
改为连接查询
6.优化or条件
两个都要有索引
7.优化分页查询
1）按照索引分页后回表
2） 将limit m，n改为limit n
第一个举例：
select film_id,description from film order by title limit 50,5 改为：
select a.film_id,a.description from film a inner join(select film_id from film order by title limit 50,5) b on a.film_id = b.film_id
第二个举例：
select * from payment order by rental_id desc limit 410,10 改为：
select * from payment where rental_id < 15640 order by rental_id desc limit 10
order by和rollup互斥，limit在rollup后面
事务是由一组SQL语句组成的逻辑处理单元
事务ACID属性：
原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态，这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性，事务结束时，所有的内部数据结构（如B数索引或双向链表）也必须都是正确的
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境中执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
事务并发处理带来的问题：
更新丢失（Lost update）：最后的更新覆盖了由其他事务所做的更新
脏读（Dirty Reads）：读取了其他事务未提交的数据
不可重复读：按相同的查询条件读取以前检索过的数据，发现被更新或者删除
幻读：按相同的查询条件读取以前检索过的数据，发现出现了新数据
隔离级别：

隔离级别	脏读	不可重复读	幻读
未提交读（Read uncommitted）	是	是	是
已提交读（Read committed）	否	是	是
可重复读（Repeatable read）	否	否	是
可序列化（Serializable）	否	否	否

**Next_Key 可以防止幻读**

索引总结，主要来源于《深入浅出MySQL》和《MySQL技术内幕》

B+树索引原理：
非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中，树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。
为什么使用B+树索引而不是B树索引（面试如果问到B+树索引，这个必问），我的回答一般是便于遍历，便于进行范围查询。
索引的分类：
聚集索引，非聚集索引 联合索引，单列索引 唯一索引，非唯一索引
联合索引举例，加深对联合索引的理解：

索引的设计：
1）使用唯一索引 2）使用短索引 3）使用区分度高的索引 4）经常在where，order by，join中出现的可以考虑添加索引 5）如果多个字段经常在一起出现，可以使用联合索引，这时候顺序一定要注意，要把经常使用的，区分度高的放在前面
可以使用索引：
1）匹配全值，对索引中所有列都指定具体值 2）匹配值的范围查询 3）匹配最左前缀 4）匹配列前缀 5）覆盖索引
不可以使用索引：
1）在使用like时，%不能放在前面 2）联合索引不满足最左匹配，不会使用索引 3）or条件要两个都要有索引，才会使用索引 4）出现隐式类型转换，不会使用索引 5）是否使用索引，由MySQL决定
ICP（Index Condition Pushdown）优化：
在支持ICP后，MySQL数据库会在取出索引的同时，对where条件进行过滤，可以大大减少上层SQL层对记录的索取，从而提高数据库的整体性能。举例：
假设某张表有联合索引（code，name），并且查询语句如下：select * from people where code = ‘1001’ and name like ‘%peng%’;
那么name肯定不可以使用索引，因为前面有%，但是这里可以把对name的过滤操作提前。
如何查看索引的使用情况：
1.explain possible keys key
2.show index from cardinality越大，区分度越高
3.使用 show status like 'Handler_read%'命令来查看索引的使用情况
如果索引正在工作，Handler_read_key的值将会很高，这个值代表了一个行被索引值读的次数，Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。
如何解决慢查询的问题：
慢查询日志，设置临界时间： set long_query_time = （以秒为单位）
然后可以通过explain查看执行计划，也可以通过profile来查看详细过程：
show profiles show profile for query Query_ID

锁总结，主要来源于《深入浅出MySQL》《MySQL技术内幕》以及博客

意向锁意味着事务希望在更细粒度上进行加锁
意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
意向排他锁（IX Lock），事务想要获得一张表中某几行的排它锁
锁的兼容性：

锁	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

为什么需要意向锁：

在InnoDB引擎中，既支持行级锁，又支持表级锁。这两种锁定类型的锁共存的问题，考虑这个例子：事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。数据库要怎么判断这个冲突呢？step1：判断表是否已被其他事务用表锁锁表，step2：判断表中的每一行是否已被行锁锁住。注意step2中通过遍历查询，这样的判断方法效率实在不高，因为需要遍历整个表。于是就有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。在意向锁存在的情况下，上面的判断可以改成step1：不变，step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。
行锁分为以下三种：
Record_Lock：对记录加锁
Gap_Lock：对范围加锁，不对记录加锁
Next_Key_Lock：对范围加锁，也对记录加锁
在Repeatable Read隔离情况下，采用Next_Key Lock的方式来加锁
在Read Committed下，采用Record Lock的方式来加锁
只有在唯一索引且使用等值查询时，使用Record Lock，其他情况下使用Next_Key Lock
加锁其实是对索引加锁
1.如果不使用索引检索数据，会对全部记录加锁
2.访问不同的记录，使用相同的索引，会冲突
3.是否使用索引，由MySQL决定
乐观锁和悲观锁：
悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁
乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁。对数据库来说，一般可以使用版本号机制来实现。
乐观锁适用于写比较少的情况，悲观锁适用于写比较多的情况。
一般是在数据库中加上一个数据版本号version字段，表示数据被修改的次数。当数据被修改时，version值会加1，当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，将读取到的version值加1，提交更新。提交更新的条件是：提交版本号必须大于记录当前版本。若不满足条件，提交操作将被驳回。

数据库优化总结 主要来源于博客

字段设计：尽可能使用not null
范式：
第一范式：字段不可分割
第二范式：消除非主属性对码的部分函数依赖
第三范式：消除非主属性对码的传递函数依赖
BCNF：消除主属性对码的部分函数依赖和传递函数依赖
不满足第二范式举例：学号，课程号，成绩，姓名 部分函数依赖（学号，课程号）->姓名
不满足第三范式举例：学号，公寓，位置 传递函数依赖：学号->位置
不满足BCNF举例：仓库，管理员，物品，数量 主属性对码的部分函数依赖 （仓库，物品）->管理员
索引（上面写过，这里略）
分表：
当一张表数据量太多时，需要进行分表操作
水平分割：通过建立结构相同的几张表分别存储数据（横着切）
垂直分割：将经常一起使用的字段放在一个单独的表中，分割后的表记录之间是一一对应的关系（竖着切）

MySQL的MVCC（多版本并发控制） 主要来源于博客

MVCC的全称：（Multi_Version Concurrency Control）
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的删除时间（存储的并不是时间值，而是系统的版本号）。每开始一个新的事务，系统版本号都会递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询列的每行记录的版本号进行比较。在Repeatable Read隔离级别下，MVCC的具体操作如下：
INSERT：记录的创建版本号就是事务版本号
UPDATE：标记旧的那一行为已删除，删除版本号为事务版本号，插入一行新的记录，创建版本号为事务版本号
DELETE：把事务版本号作为删除版本号
SELECT：只有同时满足以下两个条件的记录，才能作为查询结果返回：
1）行的创建版本号小于或等于事务的版本号
2）行的删除版本号要么未定义，要么大于当前事务版本号
需要注意两点！！！：
1）事务ID并非在事务begin时分配，是延迟到需要分配时才分配的（首次执行非快照读操作：update delete insert…）
2）事务在首次快照读（普通select）时创建快照，并将快照的可见范围信息记录在read_view中
read view：
1）up_limit_id 当前活跃事务中的最小ID
2）low_limit_id 当前最大的事务号+1
3）trx_ids 创建该read_view时，正活跃的其他事务的集合。降序排序，便于二分查找
如果id<up_limit_id||id==当前事务id，返回true，可见
如果id>=low_limit_id 不可见
如果id属于trx_ids 不可见