Java——mysql数据库相关知识点

数据库优化

1.prepareStatement一般来说比statement性能高；

2.有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，设计数据库时去掉外键；

3.关联查询比子查询效率高，使用join代替子查询；

4.将复杂的表拆分成几个结构简单的表，例如，将姓名和密码单独从用户表中独立出来；

5.sql语句全部大写，特别是列名和表名。特别是SQL命令的缓存功能，更加需要统一大小写；

6.把数据库表中字段的宽度设的尽可能小；

7.尽量把字段设置为NOT NULL；

8.使用索引，但不是索引越多越好，一个表中的索引数最好不要超过6个；

9.索引失效情况：where子句中对字段进行表达式操作；使用！=，<>，NOT等负向查询；使用函数；or 前后字段存在有没加索引的字段；使用like模糊搜索时%在前面；组合索引没有用到最左边的字段；字段隐式转换；全表扫描比使用索引快；

10.用exists，少用in和not in；

11.使用视图加速查询；

12.锁定表；

13.尽量使用表变量代替临时表，避免频繁创建和删除临时表。

14.覆盖索引，索引下推（在存储引擎层对索引进行过滤后再下推到server层对数据进行过滤，减少IO操作）

 

索引

一、磁盘IO与预读

一般来说，索引本身较大，不会全部存储在内存中，会以索引文件的形式存储在磁盘上。所以索引查找数据过程中就会产生磁盘IO操作，而磁盘IO相对于内存存取非常缓慢，因此索引结构要尽量减少磁盘IO的存取次数。为了减少磁盘IO，磁盘往往会进行数据预读，会从某位置开始，预先向后读取一定长度的数据放入内存，即局部性原理。因为磁盘顺序读取的效率较高，不需要寻道时间，因此可以提高IO效率。预读长度一般为页的整数倍，主存和磁盘以页作为单位交换数据。当需要读取的数据不在内存时，触发缺页中断，系统会向磁盘发出读取磁盘数据的请求，磁盘找到数据的起始位置并向后连续读取一页或几页数据载入内存，然后中断返回，系统继续运行。而一般数据库系统设计时会将B+树节点的大小设置为一页，这样每个节点的载入只需要一次IO。

二、索引种类

从数据结构角度

1、B+树索引：B+树是为了磁盘或其它直接存取设备设计的一种平衡多路查找树。在B+树里，所有记录节点都是键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接，非叶子节点只保存索引，不保存实际的数据，数据都保存在叶子节点中。B+树索引在数据库中有高扇出性的特点，因此在数据库中，B+树的高度一般在2~4层，也就是说查找某一键值的行记录时最多只需要2~4次IO。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘I/O的消耗，所以评价一个索引的优劣的重要指标就是I/O的操作次数。I/O的操作次数与树的高度有关。相对于B-Tree而言，B+树更适合外部存储(一般指磁盘存储)，由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，树的高度更小，需要I/O的次数也更少，每个节点能索引的范围更大更精确，使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

2、hash索引：只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能，仅仅能满足"=","IN"和"<=>"查询。重复值过多的情况下会发生大量的hash碰撞，影响性能。

3、FULLTEXT索引：仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引

4、R-Tree索引（用于对GIS数据类型创建SPATIAL索引）

从物理存储角度

MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

每个MyISAM在磁盘上存储成三个文件。数据和索引是分开的。

（1）frm文件：存储表的定义数据

（2）MYD文件：存放表具体记录的数据

（3）MYI文件：存储索引

InnoDB在磁盘上只有两个文件。索引和数据在一个文件里面。

（1）frm文件：存储表的定义数据

（2）idb文件：存放数据和索引

1、聚簇索引：InnoDB的聚簇索引在同一个B-Tree中保存了索引列和具体的数据，在聚簇索引中，实际的数据保存在叶子页中，中间的节点页保存指向下一层页面的指针。以主键为索引来组织数据，索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（InnoDB里面，主键索引就是聚簇索引，其他的索引都是非聚簇索引）

2、非聚簇索引：非聚簇索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。（MyISAM的主键索引和普通索引数据结构上没有任何区别，都是非聚簇索引，叶子节点仅仅保存数据记录的地址）

聚簇索引的优点

1. 聚簇索引将索引和数据行保存在同一个B-Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。
2. 聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的，

聚簇索引的缺点

1. 聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
2. 插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入，最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
3. 聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题。
4. 聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。

从逻辑角度

1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值

2、普通索引

3、多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

4、唯一索引

5、全文索引：用大文本对象的列构建的索引

 

一、事务的基本要素（ACID）

　　1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

　　 2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

　　 3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

　　 4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

二、事务的并发问题

　　1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

　　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

　　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

　　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

三、MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

MyISAM和innoDB引擎对比

	MyISAM	InnoDB
索引类型	非聚簇	聚簇
支持事务	否	是
支持表锁	是	是
支持行锁	否	是（默认）
支持外键	否	是
支持全文索引	是	是（5.6.4以后支持）