MYSQL相关

三大范式

1.每个列都不可以被拆分

2.非主键列必须完全依赖于主键

3.非主键列不能依赖其他非主键

三大范式的存在保证了数据的原子性，数据必须且只能跟主键有直接的关系

MyISAM引擎和InnoDB引擎

MyISAM：查询速度更优，但是不支持事务，不支持行级锁和外键，使用聚簇索引

InnoDB：写入速度更优，支持事务，行级锁以及外键，使用非聚簇索引，主键索引和覆盖索引速度很高效

InnoDB四大特性

1.插入缓冲

2.二次写

3.自适应哈希索引

3.预读

索引

一种数据结构，是表空间的一个组成部分，因此会占用物理空间，包含数据表里面所有记录的引用指针（每个表中做多建立16个索引）

优点：优化查询速度，提高系统的性能

缺点：占用物理空间，维护成本很高，会降低写入的速度

tip:

        索引覆盖：如果所有的查询字段都建立过索引，那么在查询额时候不会访问原始数据而是访问索引表中的数据，因此我们会在select之后只写必要查询的字段，增加索引覆盖的几率

        大批量无用数据的删除：先删除所有建立的索引，然后再删除无用的数据，最后讲删除的索引重新建立

原则

        1.最左匹配原则：所有按照=,<,>between,like的顺序进行匹配，即如果在<之后添加=索引是不会生效的

        2.查询频繁而更新不频繁的列，且具有很高的辨识度跟唯一性

        3.外键一定要建立索引

        4.text,image和bit数据类型不能建立索引

hash索引，B树索引，B+树索引优劣区分

hash索引：以hash值为索引，在单条，=数据查询时速度最快，但是无法进行除此以外的范围查询，其他条件查询，而且hash值为索引时很容易发生hash冲突，会让查询的速度大大降低

B+树索引：可以进行随机检索跟顺序检索，空间利用率更高，磁盘读写打低价更低，查询速度更快更稳定，增删节点是效率也更高

B树索引：弱化版B+树索引

事务ACID

原子性：事务时最小的执行单位，其操作要么全部完成，要么全部不起作用

一致性：执行事务前后数据将保持一致性，不同事务对一个数据的读取结果是一样的

隔离性：并发时，一个用户的事务不会被其他事务所干扰，各自独立

持久性：一个事务提交后，所产生的效果是持久性的，即使数据库发生故障仍然没有影响

脏读，不可重复读，幻读

脏读：事务1更新了数据，而事务2正在读取数据，此时事务1失败进行了数据回滚，那么事务2就没有读取到正确得到数据；或者是事务1更新但是没有提交，事务2就开始了读取，此时读取的还是原始数据

不可重复读：一个事务的俩次查询中数据不相同，可能是查询过程中有事务更新了该数据，关键在于update跟delete

幻读：一个事务的俩次查询中的数据不相同，可能是查询过程中有事务增加了新数据,关键在于Insert

隔离级别（从小到大）

读未提交：

读已提交：

可重复读：

可串行化：分布式事务用的比较多

行级锁，表级所，页级所

行级锁：只针对当前操作加锁，但是锁定粒度最小，所以开销很大，会出现死锁，并发度高

表级所：只针对当前操作的整张表加锁，锁定粒度最大，开销很小，不会出现死锁，并发度低

页级所：介于行级锁和表级所之间，会出现死锁，并发度一般

存储过程

本质上是一个预编译的SQL语句，为了提高重复代码的执行效率，保证代码的安全性，减少开发量而存在的，但是由于是预编译，所以移植和重新编写较为麻烦

触发器

触发器本质上也是一种特殊的存储过程，不过是由事件推动的，也就是当某个事件触发时，会自动执行代码，但是滥用也会照成数据库维护困难

tip:MySQL默认触发器

        1.Before Insert

        2.After Insert

        3.Before Update

        4.After Update

        5.Before Delete

        6.After Delete

SQL约束

        1.NOT NULL: 用于控制字段的内容一定不能为空（NULL）

        2.UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束

        3.PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个

        4.FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一

        5.CHECK: 用于控制字段的值范围

关联查询

        1.交叉连接

        2.内连接（inner join）

        3.外连接(left join,right join)

        4.联合查询(union , union all)

        5.全连接

为什么MySQL主键一般是自增id而不是UUID：UUID是随机而不连续的，在增加删除过程中很容易产生很多无用的碎片空间，而且在顺序查询中也较慢，不如自增ID适合大数据

char和varchar

char：固定长度的字符串，存取速度较快，但是很容易占用多余的空间（适合使用于身份证号这类固定长度的字段，这既能保证存取速度，也不会占用多余的空间）

varchar：非固定长度字符串，存取速度较慢，但是不会多占用空间

SQL优化

如何发现问题SQL：开启慢查询日志，自己进行配置，如何根据日志记录查看哪些SQL语句查询速度过慢，然后explain该语句查看具体消息，进行分析，选择最好的优化方法

1.优化查询的选择，连接和排序操作

2.优化查询中使用的索引，包括创建新索引，删除无用索引个，调整索引的顺序等

3.优化查询中使用的表连接方式

4.优化查询中使用的子查询，包括对子查询进行优化，使用连接代替子查询等

5.优化查询中使用的聚合函数，包括对索引进行优化，使用分组代替聚合函数等

大数据查询优化：

                1.添加索引，重写SQL语句，优化SQL速度（诸如写详细的查询字段而不是*，这样或许可以利用到索引覆盖，讲查询的字段全部加入索引，从索引表中查询而不是通过数据表，很多时候因为会访问到很多无用的数据，会导致数据查询速度降低）

                2.引入缓存，redis

                3.主从复制，读写分离

        主从复制：讲数据库在原有的基础上分为主库和从库，讲原始数据库作为主表，只负责写入操作，然后将原始数据库复制作为从库，只复制读操作，然后再主库中写一个触发器，去记录所有的数据库写入语句，然后将语句复制到从库中，更新从库中的数据，这就是主从复制，本质上就是将主库的日志变成中继日志，让从库去读取

        读写分离：使用AbstractRoutingDataSource+aop+annotation，在dao层或者service层做拦截，所有的insert/update/delete语句都作用于主库，而select语句作用于从库，不过在dao层拦截是不会触发事务，而在service层进行拦截时需要使用特殊方法去处理内部调用的问题

                4.垂直拆分跟水平拆数据库表

        垂直分表：改变表结构，将数据库中的表垂直划分成多个数据表，如可以将表中的字段按照常用字段跟不常用字段进行划分，然后分成俩个表，用主键进行关联

        水平分表：保持表的结构不变，通过某种策略存储分片，这样每一片数据都会分到不同的表或者库中，但是会给应用增加复杂度

                5.优化数据库和表的结构，修改数据表范式

        拆分字段较多的表，增加中间表存储数据，增加冗余字段