java学习-数据库

1.sql调优

1.索引：

1：explain 输出执行计划，用于判断sql语句是否命中索引(mysql5.6)。

type列，表示mysql查询中所使用的关联类型或访问类型，mysql决定如何查询符合条件的行。

        All:全表扫描。表示sql执行时，从头到尾扫描整张表，查询sql匹配到的行，性能极差。

        index:也会全表扫描。只是会按索引次序进行全表扫描。避免了排序但是承担了索引次序读取整张表。

        range:范围扫描。开始于索引的某一点，返回一定范围内的的数据所匹配到的行。比如：1：where后面带between * and * 、>、<、>= 、<=；2：in()、 or 、 like前缀 **%（后缀会索引失效）

        ref:索引访问，小范围扫描。使用的索引不唯一且允许重复。查询到结果后，还会在一个小范围内继续查找，不用扫描全表

        eq_ref：索引访问。连表查询时，查询命中唯一索引或者主键索引

        const：扫描一条。查询知道结果集最多只有一条时，查询会使用const，查询非常快。

2：创建索引注意事项：

1：经常用到的列，需要创建索引。

2：需要排序的列，需要创建索引。

3：查询用到的列，可以创建索引。

4：不能过多的创建索引，过多的索引也会影响表的查询效率。

2：sql语句优化

1：in和 not in 慎用

2：少用 select *

3:可以使用 limit 1。

4：count使用效率：count(字段) < count(主键) < count (1) < count(*);

5: 避免使用 is null 、 is not null 、 != 、<> 、or、%***

6: 分页时，可以的情况下，将 select id,name,age from user limit 1000,20替换为 select id,name,age from user where id>1000 limit 20;因为limit 10000,20会查询10020条，然后舍弃前10000条。

7：join的表尽量少，尽量小表驱动大表，join的字段尽量建立索引。

3：数据库参数优化：

1：内存设置

2：线程设置

3：日志设置

4：数据库架构优化：

1：数据库结构设计

2：分区分表

3：缓存数据库

2.存储引擎:

1.查看存储引擎：

sql语句：show engines

2.innoDB和MyISAM的区别

1：innodb支持事务，每条sql都会默认封装到一个事务里，自动提交。多条sql执行效率差，最好都封装到一个begin commit 里；MyISAM不支持事务。

2：innoDB支持外键，MyISAM不支持。

3：innoDB是聚集索引，MyISAM非聚集索引。聚集索引是索引和数据文件是绑定的，可以通过索引快速检索到数据文件，必须要有主键，主键索引效率快。但是主键不能太大，辅助索引需要先获取主键，再通过主键检索数据文件。非聚集索引的文件和索引是分离的。索引保存的是数据文件的指针。

4：innodb不保存数据行数，count时会全表扫描，而MyISAM会使用一个变量保存具体行数，count时直接返回。

3：数据库三范式

第一范式：属性/列不可再分，每一列都是不可再分的原子项。

第二范式：行内每一个数据都必须和主键直接相关。

第三范式：非主属性不能依赖其他非主属性。

4：事务

1：什么是事务：

多条sql语句，要么全部成功，要么全部失败。

2：事务的特性：ACID

原子性：所有在事务内的sql语句都是一个整体，视为一个最小的执行单元，要么全部成功，如果其中一个失败，其他的操作也要回到数据操作前的状态。

一致性：事务执行后，数据需要保持一致，不会被破坏。

隔离性：事务执行时，必须保证对其他数据不相关。不同的事务之间要有不同的操作空间，不相互干涉

持久性：事务执行后，数据的改变是持久化到数据库的。

3：事务的隔离级别及引起的问题。

1：可串行化（SERIALIZABLE）：严格遵守ACID特性，不会引起任何事务问题。

2：可重复读（REPEATABLE-READ）：对同一字段的多次读取结果保持一致，除非是被本身事务所修改。只限制了字段内容一致，不同的事务对数据进行的增删操作不限制，导致不同事务读取到的结果数目不至于，也就是幻读。当前隔离级别是mysql存储引擎InnoDB的默认级别。

3：读取已提交（READ-COMMINT）: 允许不同的事务读取其他事务已经提交的数据。如果一个事务对同一个数据进行了两次读取，这两次读取时，另一事务对当前数据进行了修改并提交，第二次读取到的和第一次读取到的就不一样了，也就是会导致不可重复读，同样，幻读的情况也可能出现。

4：读取未提交（READ-UNCOMMINT）：最低的隔离级别，甚至允许事务读取其他事务已修改但是未提交的数据，这种情况叫做脏读。 同样，不可重复读、幻读都限制不了