学习笔记：MySql

MYSQL

基础知识：

sql注入：

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

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

数据库中间件技术：

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

sql执行的步骤：

from join on where groupby having select disInct orderby limit

mysql的索引优化：

索引就是一个用来排序+查找的数据结构，它底层是用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：多版本并发控制，在多线程操作数据库时保证事务隔离的机制，可以降低锁竞争的压力，保证较高的并发量

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

• 读未提交：读取到了还没有提交事务的数据
• 读已提交：读取到了已经提交的新增数据
• 可重复度：可以读到已提交的修改数据
• 可序列化

事务并发带来的问题：

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

事务的传播机制：

• 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.6后推出的索引下推

可以减少回表次数，它会先在索引表（二级索引）中对索引表中的字段进行判断，直接过滤掉不满足条件的记录，减少回表次数。
eg：查询3个字段，2个在二级索引，1个在聚簇索引中，5.6之前它会根据最左匹配原则只判断第一个条件，得到2个记录然后做两次回表
5.6之后它会判断2个条件，可能只得到1个记录然后做一次回表

常见问题

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

唯一索引无法使用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集合来对连接进行存放）
当有连接请求到连接池，如果有连接处于空闲状态则直接返回一个空闲连接并标记为忙。
如果当前没有空闲连接，就会新建一定数量的连接，新建连接的数量由配置参数决定