MySQL总结
MySQL
为什么使用数据库
数据保存在内存
- 优点：存取速度快
- 缺点：数据不能永久存取
数据保存在文件
- 优点：数据可以持久化
- 缺点：速度比内存操作慢，频繁的IO操作，查询数据数据不方便
数据保存在数据库
- 优点：数据可以持久化，使用SQL语句，查询方便效率高，管理数据方便
- 缺点：数据库本身需要内存来管理，需要保证数据库的稳定性，增加了系统的复杂性
范式
- 第一范式：每列都不可再拆分
- 第二范式：在第一范式的基础上，非主键列完全依赖于主键，不能是依赖于主键的一部分
- 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键
1.引擎
查询mysql版本
select version();
查询当前mysql支持的引擎：
show engines;
InnoDB是默认的引擎
查询某个表使用的引擎
SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_NAME = 'sys_user';
Innodb
Innodb引擎提供了对数据库ACID事务的支持，还提供了行级锁和外键的约束，设计目标是处理大数据容量的数据库系统
特性：
- 插入缓冲:通过将插入操作的数据存储在缓存区中实现，当缓存区满时，Innodb会将缓冲区中的数据写入磁盘，以便将数据永久保存
- 二次写：通过将更新操作的日志记录到缓冲池中，然后将缓冲池中的日志记录写入磁盘上的日志文件中来实现，当更新操作完成后，Innodb会将更新操作的日志记录写入磁盘上的数据文件中，从而实现二次写的功能
- 自适应哈希索引：当插入新行时，Innodb会根据行中的值计算出一个哈希值，然后将该哈希值插入到索引中，当查询时根据查询值计算出一个哈希值，然后搜索索引中的哈希值
- 预读：Innodb预读是通过在查询时将查询结果的下一页数据预先读取到内存中，以便用户需要时可以立即提供给用户，从而提高查询效率的
MyIASM
不提供事务支持，不支持行级锁和外键
MEMORY
所有的数据在内存中，速度处理快，安全性不高
MyISAM与InnoDB区别
| MyISAM | InnoDB | |
|---|---|---|
| 存储结构 | 每张表三个文件：frm:表定义,MYD:数据文件，MYI:索引文件 | 一个或多个文件，表大只受操作系统文件大小限制，一般为2G |
| 存储空间 | 可被压缩，存储空间小 | 会建立缓冲池用于高速缓冲数据和索引 |
| 文件格式 | 数据和索引分开存储，数据.MYD,索引.MYI | 数据和索引集中存储，.idb |
| 存储顺序 | 插入顺序保存 | 主键大小保存 |
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 锁支持 | 表级锁定 | 行级锁定，表级锁定 |
| 索引实现 | B+树索引，堆表 | B+树索引，索引组织表 |
| 哈希索引 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
引擎选择
默认Innodb
MyISAM:以读写插入为主的应用程序
Innodb:更新删除操作频率较高，要保证数据的完整性，并发量高，需要支持事务
2.索引
一种数据结构，通常是使用B树及其B+树实现
索引类型
-
主键索引
数据列不允许重复，不允许为null,一个表只能创建一个索引
-
唯一索引
数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引
创建一个唯一索引：alter table [table_name] add unique (column)
创建一组唯一索引：alter table [Etable_name] add uniqe (column1,column2)
-
普通索引
基本的索引类型，没有唯一的限制，允许为NULL值
创建普通索引：alter table table_name add index index_name (column)
创建组合索引：alter table table_name add index index_name(column1,column2,column3)
创建全文索引：alter table table_name add full text(column);
-
聚簇索引：将数据和索引存储在一块，找到索引也就找到了数据
-
非聚簇索引：将数据和索引分开存储，索引的叶子节点指向数据的对应行
-
联合索引：多个字段同时建立索引的索引，要命中该索引需要按照建立索引时的字段顺序挨个使用，否则无法命中
创建索引的原则
- 左前缀原则，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的顺序可以任意调整
- 频繁作为查询条件的字段适合创建索引
- 频繁更新的字段不适合创建索引
- 尽量扩展索引，不要新建索引
创建索引的注意点
- 非空字段应该指定列为NOTNULL，在mysql中，含有空值的列很难进行查询优化，因为他使得索引，索引的统计信息变得更加复杂，应该用0或者一个特殊的值来代替空值
- 取值离散大的字段，（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查询字段的差异值，返回值越大说明字段的唯一值越多，字段的离散程度越高
- 索引字段越小越好，因为查询索引的时候需要把索引列转换成一个关键字来查询，字段越小，转换的时间越短
Hash索引原理
将数据存在一个哈希表中，哈希表中的每一个元素都有一个唯一的键，该键由哈希函数计算得出，当要查找一个元素时，只需要使用同样的哈希函数计算得出该元素的键，然后就可以直接从哈希表中获取到该元素，hash表是一种以键值对存储数据的数据结构，数组中每个元素都是一个链表，链表中存储了具有相同哈希值的所有关键字和相关联的值，MyIASM使用MurmurHsh3算法来计算Hash值
使用HasH索引的好处：
只需要使用一个Hash函数就可以获取到相应的键值，之后进行回表查询得到实际的数据
B+树索引原理
B+树是一种多叉树，每个节点都包含一个或多个键值，每个键值都有一个指向另一个节点的指针，B+树索引的叶子节点存储数据，非叶子节点存储关键字，关键字是由表中索引列的值组成，每个索引列的值都会被转换成一个关键字，所以索引列的值不建议太大，太大会使这个转换时间太长，这些关键字按照升序排列，构成B+树的结构，在查询数据的时候从根节点开始搜索，搜索节点中的关键字，如果没有则搜索下一个节点，因为关键字是有序的，所以查找速度很快
使用B+树的好处
由于B+树内部节点只存放键，不存放值，因此一次读取，可以在内存页中获取更多的键，有利于更快速的缩小查找范围，因为B+树的叶子节点由一条链相连，所以当需要进行一次全数据遍历时，B+树只需要使用0Log(N)时间找到最小的一个节点，然后通过链进行0(N)的顺序遍历即可
B树和B+树的区别
B树中键和值存放在内部节点和叶子节点，叶子节点之间各自独立，因此B树适合随机检索
B+树中内部节点都是键，没有值，叶子节点同时存放键和值，叶子节点有一条链相连，因此B+树适合随机检索和顺序检索
使用B树的好处
B树在内部节点同时存储键和值，因此把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率，这种特性使得B树在特定数据重复多次查询的场景中更有效
3. 事务
事务种类
- 本地事务：普通事务，独立一个数据库，能保证在该数据库的ACID
- 分布式事务：涉及到两个或多个数据库源的事务，跨域多台同类或异类数据库的事务，保证每台数据库事务所组成的所有操作的ACID
事务的四大特性
- 原子性：不允许分割，要么执行成功，要么执行失败
- 一致性：事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的
- 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
- 持久性：一个事务被提交之后，对数据库来说是持久的，即使数据库发生故障也不应该对其有任何影响
脏读，幻读，不可重复读
脏读：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的
不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据
幻读：在一个事务的两次查询中，数据笔数不一致，例如有一个事务查询了几列数据，而另一个事务却在此时插入了新的几列数据，先前的 事务在接下的查询中，就会发现有几列数据是它先前没有的
事务的隔离级别
为达到事务的四大特性，数据库定义了4种不同事务的隔离级别，由低到高依次为read uncommitted,read cpmmitted,repeatable read,
serializable,四个级别可以依次逐个解决脏读，不可重复读，幻读这几类问题
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| read_uncommitted | √ | √ | √ |
| read_committed | × | √ | √ |
| repeatable-read | × | × | √ |
| serializable | × | × | × |
- read uncommitted(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读，幻读和不可重复读
- read committed(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读和不可重复读仍有可能会发生
- repea table read(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但是有幻读仍有可能会发生
- serializable(可串行化)：最高的隔离级别，完全服从ACID的隔离级别，所有的事务依次逐个执行，事务之间完全不可能会产生干扰，可以完全防止脏读，不可重复读，幻读的问题
MySQL默认采用repeatable read隔离级别，oracle默认采用read_committed隔离级别
事务的隔离机制基于锁机制和并发调度，其中并发调度使用的是MVVC多版本并发控制，通过保存修改的旧版本信息来支持并发一致性读和回滚等特性
事务的隔离级别越低，请求的锁越少，在分布式事务的情况下一般会用到serializble(可串行化)隔离级别
4. 锁
按照粒度分锁
- 行级锁：mysql中锁粒度最小的一种锁，表示只对当前操作的行进行加锁，虽然粒度最小，但是开销最大，行级锁又分为共享锁和排它锁，开销大，加锁慢，容易出现死锁，发生锁冲突的概率最低，并发度最高，注意行级锁是基于索引的，如果一条SQL用不到索引是不会使用行级锁的，会使用表级锁
- 表级锁：mysql中粒度最大的一种锁，开销小，加锁快，不会出现死锁，发生锁冲突的概率最高，表级锁又分为表共享读锁（共享锁）与表独占写锁（排他锁），并发度最低
- 页级锁：介于行级锁与表级锁中间的一种锁，一次锁定相邻的一组记录，开销介于表锁和行锁之间，会出现死锁，并发度一般
MyISAM采用表级锁
Innodb支持行级锁和表级锁，默认是行级锁
按照类别分锁
- 共享锁（读锁）：当读取数据时，对数据加上共享锁，此时所有的事务只能执行读操作不能执行写操作，可以同时加多个共享锁
- 排他锁（写锁）：对数据进行写入时，对数据加上排他锁，排他锁只能加一个，和其他排它锁和共享锁互斥，其他进程只能等待其释放
锁的算法
- Record lock:单个行记录上的锁
- Gap lock:间隙锁。锁定一个范围，不包括记录本身
- Next-key lock:record+gap 锁定一个范围，包含记录本身
死锁及其解决方法
死锁是两个事务在同一个资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象
产生死锁的条件：
- 互斥条件：一个资源每次只能被一个进程使用
- 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
- 不剥夺条件：进程已获得资源，在未使用完之前，不能强行剥夺
- 循环等待条件：若干个进程之间形成一种头尾相接，循环等待的关系
解决方法：
- 尽量约定以相同的顺序访问表
- 尽可能做到一次锁定所需要的资源，减少死锁的产生概率
- 降低隔离级别
- 使用绑定连接
- 避免事务中的用户交互
- 对容易产生死锁的业务部分，升级锁的粒度
第一种
查询是否锁表
show open tables where In_use > 0;
查询进程
show processlist
杀死进程Id
kill id
第二种
查询当前事务
select * from information_schema.innodb_trx;
查看当前锁定的事务
select * from information_schema.innodb_locks;
查看当前等锁的事务
select * from information_schema.innodb_lock_waits;
杀死进程
kill 线程ID
乐观锁和悲观锁的实现
- 乐观锁：假定不会发生并发冲突，只在提交时检查是否违反数据完整性，在修改数据的时候把事务锁起来，用数据版本（Version）记录机制实现，在表中添加一个数字类型的“version”字段，读取数据时将version字段的值一同读出，数据更新一次对version值加1,提交更新的时候判断对应记录的当前版本与第一次取出来的version值进行对比，相等则更新，否则就是过期数据
- 悲观锁：假定会发生冲突，屏蔽一切可能违反数据完整性的操作，在查询数据的时候就把事务锁起来，直到提交事务，悲观锁是由数据库自己实现的，用的时候直接使用相关语句就可以了，共享锁和排他锁都是悲观锁的实现
乐观锁适用于读多写少的场景，悲观锁适用于读少写多的场景
5.SQL优化
优化长难的语句
分解关联查询，让缓存的效率更高，执行单个查询，减少锁的竞争，在应用层做关联，对数据库进行拆分，减少冗余记录的查询
优化特定类型的查询语句
count()会忽略所有的列，直接统计所有列数，不要使用count(列名)，在MyISAM中没有where条件的count()非常快，当有where条件时MyISAM的count统计不一定比其他引擎快，可以使用explain查询近似值，用近似值替代count(*)增加汇总表使用缓存
优化关联查询
确定ON或者USING子句中是否有索引
确保GROUP BY和ORDER BY只有一个表中的列，这样MYSQL才有可能使用索引
优化子查询
使用关联查询替代
优化GROUP BY和DISTINCT,这两种可以根据索引来优化，使用索引列分组的效率更高，如果不需要ORDER BY 进行GROUP BY时加ORDER BY NULL,mysql不会再进行文件排序
优化LIMIT分页
LIMIT偏移量大的时候，查询效率较低，记录上次查询的大ID,下次查询时直接根据该ID来查询
优化UNION查询
UNION ALL的效率高于UNION
优化WHERE子句
- 避免对字段进行null判断，否则将导致引擎放弃使用索引而进行全表扫描
- 避免使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
- 避免使用or连接条件，否则将导致引擎使用索引而进行全表扫描
- 避免使用参数，因为sql只有在运行时才会解析局部变量，所以在编译时如果变量的值是未知的，那么将无法作为索引来检索，最终导致全表扫描
- 避免对where字段进行表达式操作，这也会导致全表扫描
- 避免对字段进行函数操作，这也会导致全表扫描
- 避免在where子句中的“=”左边进行函数，算术运算法或者其他表达式运算，否则也会导致全表扫描
6.数据结构优化
- char:定长字符串，存取速度比varchar速度快，但是因为长度是固定的，所以会占多余的空间，是空间换时间的做法
- varchar:变长字符串，最多存放65535个字符，数据有多长就按照多长存储，因为长度不固定，所以不会占据多余的空间，是时间换空间的做法
- 主键使用自增ID,不使用UUID,因为B+树的叶子节点是升序排序的，自增ID只需要往后排序，而UUID因为大小不确定，会造成非常多的数据移动和插入，导致产生很多内存碎片，造成性能下降
- 垂直分区：对数据列进行拆分，把一张表分成两张单独的表或者放到另外一个数据库，优点是行数据变小，减少了I/O次数，简化了表结构，易于维护，缺点是主键出现冗余，引起join操作，让事务变得更加复杂，适用于如果一个表中某些列常用，另一些不常用
- 水平分区：保存数据表结构不变，通过一种策略存储数据分片，优点是降低了需要读的数据和索引的页数和层数，提高了查询速度，缺点是给应用增加了复杂度，增加了磁盘的IO次数，查询时需要多个表明，查询所有数据都需UNION操作，适用于表中的数据有独立性，如分表记录各个地区的数据或者记录不同时期的数据
