MYSQL

基础知识：

sql注入：

当sql语句绑定有参数输入时，可通过外部更改URL的方式，将sql语句进行更改（union）,这样就可以在未获取正确数据的情况下访问系统，对系统的安全性造成破坏

mybatis如何在插入数据后获取主键值：xml中useGeneratekeys属性设置为true

数据库中间件技术：

mycat，当mysql设为集群架构时，项目连接mycat，mycat连接mysql集群，这样项目就感觉直接连了一个mysql，而不是mysql集群。

mysql的执行流程

客户端(jdbc,navicat)操作mysql->连接器->分析器->优化器->执行器->存储引擎（存储引擎就是一种持久化数据的工具、数据的存储格式）

sql执行的步骤：

from join on where groupby having select disInct orderby limit

MySql中的索引

常见的也就两种：hash和B+Tree

hash：hash索引的话，它的存储过程其实和hashmap很像，根据索引列对应的值计算出hash后存储起来，不过hash会有hash冲突，比较占用内存且不支持范围查找
B+Tree：使用B+Tree比较多，每个结点存放16KB数据页的数据，非叶子结点的数据页中包括了索引值和指向下一个结点的指针，所有数据都是在叶子节点中按从小到大的顺序存放的。

对于myIsam存储引擎来说，叶子结点存放的其实是索引对应的数据所在磁盘文件的地址。

对于InnoDB存储引擎来说，叶子结点存放的就是索引对应的数据。

mysql的索引优化：

索引就是一个用来排序+查找的数据结构减少io次数从而提高效率，它底层是用B+树实现，一个表中最多可建立16个索引

创建索引的方式：createIndex xxx on tableName（字段1，字段2）
删除索引的方式：dropIndex xxx on tableName
查看索引的方式：show Index from tableName

mysql中的存储引擎：InnoDB MyIsam

InnoDB和MyIsam的区别：

一：InnoDB支持行锁（innodb只有在使用索引时采用行锁，其他情况使用表锁），MyIsam是表锁
二：InnoDB支持事务，MyIsam不支持事务
三：InnoDB支持外键，MyIsam不支持外键
四：InnoDB支持聚集索引，MyIsam不支持
五：InnoDB的数据文件：.frm：表结构，.ibd：索引+数据，MyIsam的数据文件：.frm：表结构，.MYI：索引，.MYD：数据

索引：

索引的种类

唯一索引：所建立的索引所属列的值必须是唯一的，且可以为null
复合索引：一个索引中包含多个列
单值索引：一个索引只包括单个列，一个表中可以有多个单值索引
聚集索引：也就是主键索引，每个表中只要有主键那么该主键就自带聚集索引，若没有主键那么mysql会选择一个值不重复的列作为聚集索引，若不存在则它会建立一个隐藏的自增字段作为聚集索引

聚集索引和非聚集索引的区别？

聚集索引要求字段的值唯一，聚集索引在叶子节点存放的是表中所有的数据，非聚集索引在叶子节点存放的是主键id和索引列对应的数据（非聚集索引不采用覆盖索引时要进行回表操作）

覆盖索引：

指要查询的字段都属于一个索引中的字段，就称为覆盖索引，无需做回表操作，（回表：当要查询的字段大于索引的字段时，需要回表到聚簇索引中再查询二级索引中不包括的字段对应的值）

哪些列适合作为索引？

经常被查询的列
数据量大
值大多都不重复的
外连接查询时On条件后面的字段，经常用于排序的列

哪些情况不适合建立索引？

列中有很多重复的值
经常用于增删改的列
表记录太少

索引失效的原因：

一：未遵循最佳左前缀原则，where条件查询时，应该按照索引的顺序进行查询，不能缺少索引中的字段（缺少的字段后面的索引字段将失效），全职匹配我最爱
二：范围查找后失效(> < !=)，范围查找会导致索引失效，范围超找后面的条件会用不到索引
三：不在索引列上做操作，如在where条件后的字段做（函数，类型转换）会导致索引失效
四：like查询时%最好用在右边，否则会导致索引失效（除非采用覆盖索引（要查询的列和索引的列正好匹配））
五：is null 和is not null和or 会导致索引失效
六：尽量不要使用select *多使用覆盖索引，否则会造成回表操作（当用到我们自己创建的索引后，由于索引中存放的只有对应列的数据，select * 查询的是所有数据，因为还需要到主键索引中去获取对应的所有数据）
七：在使用左连接时，要为右边的表建立索引，在使用右连接时，要为左边的表建立索引

sql优化：

①：当大批量插入数据时，可以采用foreach标签批量插入而不是在JAVA中使用for循环插入
②：优化limit分页，当查询的数据很靠后eg：limit：10000，10，可以采用：where id>10000 limit 10
③：删除冗余索引，eg：索引1（age）索引2（age，name）就可以删除掉索引1，因为索引2包括索引：（age）和（age，name）
④：where子句中考虑使用默认值代替null：where age is not null可以替换成where age >0

索引优化：

Explain+sql语句对sql进行分析所显示的字段有

id（决定查询出来的多个表要执行的顺序）：id相同时：执行顺序从上至下，id不同时：id越大的越先执行
select_type（显示查询的类型）：simple：简单的sql查询 primary：最外层的查询 subquery：子查询

table（显示该行数据属于哪张表）

type（显示该行数据所用的类型）：从好到坏 systetm>const>eq_Ref>ref>range>index>all 至少要达到range级别
possible_keys:显示可能用到的索引 key:实际用到的索引
key_len:索引用到的字节数
ref:哪些列用到了索引
rows：根据表的信息及索引的信息，估计出需要查找多少行可以找到对应的数据

慢查询日志

在开启慢查询日志后可以借助mysqlDrumpslow工具对慢查询日志进行分析。SQL执行时间超过long_query_time将会被记录到慢查询日志当中，可以通过explain关键字对其进行分析

show profile（Explain的升级版）记录最近执行的15条sql

mysql的事务：ACID

原子性：事务内的操作要么都成功，要么都失败（mysql用undo log实现）
一致性：事务提交前和提交后，数据库的完整性约束没有被破坏
隔离性：多个事务间是相互隔离的互不影响
永久性：事务提交后，所修改的数据是永久生效的

MVVC：

多版本并发控制，行锁的变种，在多线程操作数据库时保证事务隔离的机制，可以降低锁竞争的压力，保证较高的并发量。

在InnoDB存储引擎中，MVVC会在每行数据后保存两个隐藏的列来实现，分别是数据创建时间和删除时间，当然这个时间并不是真正的创建、删除时间，而是系统版本号，没开启一个新的事务版本号就会递增，事务开始时的版本号作为事务的版本号，用来和查询到的数据的版本号进行比较。

redoLog：

是InnoDB存储引擎的，用于保证事务的安全，在mysql重启后，会根据redolog进行重做从而达到事务的永久性

undoLog：

记录了事务提交前的数据的一个版本，可用于回滚，同时也保证了多版本并发控制下的读

事务并发带来的问题：

更新丢失：A和B同时更新，后面更新的数据会覆盖掉之前更新的数据
脏读：A读到了B未提交的数据
不可重复度：A读到了B已提交的修改数据
幻读：A读到了B已提交的新增数据

事务的隔离级别（通过mysql锁的机制实现）

读未提交：读取到了还没有提交事务的数据
读已提交：读取到了已经提交的新增数据（可避免脏读）
可重复度：可以读到已提交的修改数据（可避免脏读，不可重复度）
可序列化（都可以避免）

事务的传播机制：

Required：B方法在A方法中被调用，若A开启事务，则B就和A用同一事务
RequiresNew：B方法在A方法中被调用，若A开启事务，则B开启一个新事务，B和A的事务相互独立
Nested：B方法在A方法中被调用，若A开启事务，则B开启一个新事务，当A事务回滚则B事务也回滚，若B事务回滚A没事

主从复制：

将mysql进行主从复制，主库（master）负责写，从库（slave）负责读，实现读写分离和故障切换的高可用原理：主库将改变记录到二进制日志中（binary log）从库读取主库的binary log 日志并拷贝到它的中继日志当中从库重做中继日志中的事件，将改变应用到自己的数据库中问题：延时，从库无法第一时间读取到主库新增的数据解决方法：读取新增数据时，让他强制到主库中进行读取

三大范式

第一范式：数据库中的各个字段，都是不可分割的。（地区：中国山西，这个就可以分割为中国和山西）
第二范式：在满足第一范式的基础上，保证各个字段都是依赖于主键的。（若该字段离开主键照样可以知道值，也不算第二范式）
第三范式：在满足第二范式的基础上，各个字段不能有依赖关系（单价：10 数量：5 总价：50，这个总价就可以通过单价*数量算出来，就不符合第三范式了）

分表技术

分为水平分表和垂直分表（用数据库中间件技术mycat实现）水平分表：当表中数据太多时，可按行分表（原来200行的表分为2个100行数据的表）垂直分表：当表中字段太多时，可将一些不常用的字段分离到新的表上，通过外键关联（5个字段的表，分为一个4个字段一个1个字段的表）

mysql5.7推出的索引下推

可以减少回表次数，它会先在索引表（二级索引）中对索引表中的字段进行判断，直接过滤掉不满足条件的记录，减少回表次数。

eg：select age，name，email from user where age=18 and name=’guo’

此时拥有索引：(age,name)

在5.7之前的查询：根据最左匹配原则直接根据age=18做数据过滤，然后获取到2条记录，再回表获取到email的数据，然后再到server层根据name=’guo’做数据的过滤最终获取到结果。

在5.7之后的查询：现在在执行引擎中直接会对age=18，name=’guo’来作为条件进行过滤查询，得到一个记录后回表获取email的数据，最终获取到结果。

简单来说索引下推就是将server层的数据过滤下推到了存储引擎层，来减少回表

常见问题

*唯一索引普通索引选择难题

唯一索引无法使用changebuffer，普通索引可以使用执行更新操作，判断数据页是否在内存，在内存直接更新，不在内存则将其缓存到changebuffer执行merge操作

一个很长的字段，想做索引我们怎么去优化它

因为字段很长，所以要建立B+树索引的话，索引所占的磁盘空间就越大，搜索效率就不高可以使用hash索引，把字段hash为另一个字段存起来，每次校验hash就可以了

select *和 select 全部字段的区别

select *要解析数据字典，select 全部字段不需要 select 全部字段更具有可读性 select全部字段可以按指定字段顺序输出，select *按照建表顺序输出

having和where的区别

having是在分组group by时使用 having在where后执行 having无法用到索引，where可以 having可以用到聚集函数，where不可以

PreparedStatment和Statment区别

statment会频繁编译sql PreparedStatment可以对sql预编译，提高效率当sql中有条件时，PreparedStatment可以使用占位符，statment则需要进行拼接，因此statment有SQL注入问题

数据库连接池：

因为每次获取和释放连接都很耗时，因此有了数据库连接池，在项目启动时就建立一定数量的池连接，获取连接时从池中获取，释放连接时，不是将连接直接释放掉，而是将连接再归还到连接池当中。（底层通过list集合来对连接进行存放）当有连接请求到连接池，如果有连接处于空闲状态则直接返回一个空闲连接并标记为忙。如果当前没有空闲连接，就会新建一定数量的连接，新建连接的数量由配置参数决定。