MySQL重要知识点总结
事物的特征
ACID
原子性 Atomicity ：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
一致性 Consistency ：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器)、级联回滚等。
隔离性 Isolation ：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性 Durability ：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
mysql如何实现acid：https://www.cnblogs.com/jimoer/p/13528278.html
隔离级别
脏读：一个事务可以读取到另一个事务未提交的数据。这种隔离级别是最不安全的一种，因为未提交的事务是存在回滚的情况。
不可重复读：一个事务范围内两个相同的查询却返回了不同数据，一新一旧。
一个事务因为读取到另一个事务已提交的修改数据，导致在当前事务的不同时间读取同一条数据获取的结果不一致。
幻读：在第一个事务期间另一个事务操作了数据，但第一个事务始终读到的是旧的。
一个事物读可以读取到其他事务提交的数据，但是在RR隔离级别下，当前读取此条数据只可读取一次，在当前事务中，不论读取多少次，数据任然是第一次读取的值，不会因为在第一次读取之后，其他事务再修改提交此数据而产生改变。因此也成为幻读，因为读出来的数据并不一定就是最新的数据。
原文参考：https://mp.weixin.qq.com/s/CZHuGT4sKs_QHD_bv3BfAQ
索引
1、索引类型
主键索引
二级索引：唯一、普通、前缀和全文索引。二级索引的叶子节点存储的数据是主键。
聚集索引和非聚集索引
- 聚集索引
聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
优点：聚集索引的查询速度非常的快，因为定位到索引的节点，就相当于定位到了数据。
缺点：在修改时，不止需要修改数据，而且需要修改索引位置，不过一般是不允许修改主键的。 - 非聚集索引
二级索引属于非聚集索引，因为二级索引存储的数据是主键，而不是直接的数据。
优点：更新代价比聚集索引要小，只需更新索引。
缺点：可能会二次查询(回表) : 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。
2、索引实现
1) b+树
特征：
1、有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2、所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3、所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
相比b树的优点：
1、io次数更少，因为中间节点不存储卫星数据，所以同样的磁盘大小可以存储更多索引，对于同样大小的数据b+树的高度更低。
2、查询更加稳定，因为查找所有卫星数据都需要遍历到叶子结点，这样每次io的次数是固定的。
3、范围查询效率更高，因为b+树的叶子结点之间有指针连接成链表，且大小为由小到大，范围查询直接遍历链表即可。
2) hash
hash对于等值查询会很快，但对于范围查询要一个一个查，会比较慢。
要点：
1、最左匹配原则：在建立联合索引的情况下，条件中要包含索引中最左的key，这是因为存储时候是根据最左索引顺序来排列的。（参考： https://zhuanlan.zhihu.com/p/109623980
2、联合索引在B+树如何存储：在叶子结点上存储联合索引的信息，叶子结点的数据存储的是主键id。
优化
1、避免使用子查询，可以把子查询优化为 join 操作
子查询性能差的原因：
子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。
2、对应同一列进行 or 判断时，使用 in 代替 or
in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。
3、在明显不会有重复值时使用 UNION ALL 而不是 UNION
UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
UNION ALL 不会再对结果集进行去重操作
4、explain使用（详见：https://www.cnblogs.com/codetiger/p/14876843.html)
字段解释
id：优先级
select_type：select_type表示查询的类型，主要是为了区分普通查询、子查询、联合查询等复杂查询。
table：哪个表，可能是别名或者union这种。
partitions：结果集所在的分区范围。
type：type是查询的访问类型，是较为重要的一个指标，性能从最好到最坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
一般来说，得保证查询至少到达range级别，最好能达到ref。
possible_keys：查询时可能使用的索引，一个或多个。注意是可能，实际查询时不一定会用到。
key：查询时实际使用的索引，没有使用索引则为NULL。查询时若使用了覆盖索引，则该索引只出现在key字段中，不会出现在possible_keys。
ref：显示索引的哪一列被使用了。
rows：rows 列表示 MySQL 认为它执行查询时可能需要读取的行数，一般情况下这个值越小越好！
filtered：filtered 是一个百分比的值，表示符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。
Extra：其他额外的信息，要关注并优化的参数。
要点
1）千万别用IN，使用JOIN或者EXISTS代替它
2）有联合唯一索引的，要把条件都加上，来达到const。
3）使用like时尽量用'keys%'。
4）group by 和 order by的时候，列的数量和顺序尽量和索引的一样。
存储引擎
为什么MySQL默认选择InnoDB，而不是MyISAM？
1、支持事务，在有多个事务的时候可以保证全部成功或全部失败。
2、支持行级锁，在并发的时候效率会更高，MyISAM只支持表级锁。
3、支持数据库异常崩溃后的安全恢复，会利用redo log保证数据库恢复到崩溃前的状态。
手动连接数据库
package learnFromBilibili;
import java.sql.*;
/**
* 手动连接 mysql -> 简单查询 -> 关闭连接
* @author tony fan
*/
public class ManualConnectMysql {
public static void main(String[] args) throws Exception {
// 使用反射创建 DriverManager 类
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false",
"root","123456");
PreparedStatement preparedStatement = connection.prepareStatement("select * from account");
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
System.out.println(resultSet.getString("name"));
}
resultSet.close();
preparedStatement.close();
connection.close();
}
}
