数据库

acid
索引
锁
事物隔离级别
LBCC与MVCC
explain
prepareStatement和statement
mysql存储引擎
mysql插入大量数据

索引

应用层面来说索引包括全文索引、哈希索引、btree索引、rtree索引

全文索引底层是通过分词实现的，不建议使用。

rtree：基本没有使用，不去了解

哈希索引就是存在一张哈希表，表中两个列，一个是哈希值（主键数据通过哈希算法得到的唯一值），另一列存放着数据存放的物理地址。
比如执行sql ：select * from xxx where age=18的时候，首先18哈希算出一个值，然后遍历哈希表，直到找到哈希为18的列。
哈希的缺点，因为哈希值的特性，所以不支持范围查找。
因为表中存的是哈希值，哈希值是无法排序的，所以不支持排序
同理联合索引的时候，表中存的是联合索引算出的哈希值，所以当使用联合索引中的部分所以来查的时候就不行了。
还有当数据量大的时候，哈希表中数据就多了，那么根据哈希值去查某条数据的时候就慢了，所以大数量的时候不建议使用。
优点：因为哈希索引不像btree索引要多次比较，只需要一次哈希就能定位到数据，所以性能会好。
但是数据量大的话就有哈希碰撞的性能问题。

btree：mysql默认采用的结构。b+tree。分两种聚簇索引非聚簇索引。
聚簇索引在叶子节点存储的是实际数据，非聚簇索引叶子节点存储的是数据的地址。

聚簇索引：叶子节点存储的是实际物理数据，采用主键，无主键用唯一索引，在然后就一个数据库生成的rowid，存储顺序和数据在磁盘中的物理地址是一致的，所以一个表只能有一个聚簇索引。
非聚簇索引：索引文件和数据文件是分开的，索引树的叶子节点存储的是实际数据的物理地址。

辅助索引：对于非主键列维护的索引树，叶子节点存储的是主键列的值。通过辅助索引查询的时候，先在辅助索引树定位出主键的值，然后在去主键索引树查询真实数据。

btree、b+tree等都是为磁盘等外存储设备提供的一种优化数据结构，先了解磁盘。

盘片（platter）、磁头（head）、磁道（track）、扇区（sector）、柱面（cylinder）。
磁盘块：一个磁盘块包括若干个扇区。
每次从磁盘读取数据最耗时的就是磁道之间的切换，这就是为什么说顺序读磁盘比读内存还快的原因。
每次读取数据的时候，先看内存中是否存在，如果内存不存在，则会读取磁盘，但并不是用多少数据就读多少数据，每次都会预读取一些数据，这样减少后续查询读取磁盘的几率，每次预读取数据长度是页的整数倍，内存中的页对应磁盘就是一个磁盘块。

在为表创建索引、更新数据的时候，数据库都会在磁盘文件中维护一份索引文件，索引文件存储的数据结构为一个多叉树，如图中，每个节点对应磁盘中的一页（也就是一个磁盘块），那么当执行sql select * from xx where id=29的时候：
1、首先在内存中查看，没有查到29
2、读取索引文件，加载出磁盘快1的数据到内存（数据为17和35以及三个指针指向树的二阶）
2、然后在内存中作比对：比对29大于17小于35，定位到指针p2，然后根据p2记录的磁盘地址查询索引文件，定位出磁盘块3加载到内存
3、同理定位出p1，然后查询出磁盘块8
4、叶子节点的时候就一次比对出自己要查找的值

select * from t where a > 10 and a<20的索引流程.
首先根据a>10定位到叶子节点，然后顺着叶子节点的指针顺序直接找到20，不需要再次遍历树

联合索引 （a,b,c）
每个节点是多维的，先根据a去定位，当a相同时在根据b。
所以使用的时候，必须要有a
where a=1 and b=1 and c=1
where a=1
where a=1 and b=1
顺序乱了没关系，mysql会自动优化

sql优化器
假设有表s1，聚簇索引，key1列建立索引，那么该表就会维护两个索引树，聚簇索引树（id列）+ 辅助索引树

当执行sql select * from s1 where key1>45 and key1<55的时候，是走索引还是全表扫描？
当走索引的时候，首先查询辅助索引树，查询出符合条件的第一条数据为key1=30，然后定位出id列为1，然后在根据id=1查询聚簇索引树。
然后在辅助索引树，根据key1=30这条数据的下个节点属性查询出下个节点数据，然后在查询聚簇索引树，以此类推。
但是当符合条件的数据特别多的时候，mysql优化器会认为这样反复通过辅助索引查询聚簇索引还不如直接全表扫描聚簇索引树，就会进行优化改成扫描全表。

返回顶部

锁

按照粒度分为行锁和表锁。
按照类型分为共享锁/s锁和排它锁/w锁
按照锁定数据的范围又分记录锁、临键锁、间隙锁

共享锁
select * from table where ? lock in share mode;

排它锁
select * from table where ? for update;
insert, update, delete操作

行锁
1、行锁锁的是索引记录，而不是数据记录

BEGIN;
 SELECT * FROM t2 WHERE name = '1' for update;
 COMMIT;

比如执行上面语句，name为主键，首先会将聚簇索引树全表加个x锁，然后全表扫描聚簇索引，挨个比对name。
如果在COMMIT之前又来个事务2

BEGIN;
SELECT * FROM t2 WHERE name = '4' for update;
COMMIT;

在事务1提交释放x锁之前，事务2是被阻塞的。
2、非主键数据加锁的时候，会在辅助索引树和聚簇索引分别加一把锁。
比如数据库有一条数据 id=1 and name=1，id为主键，name非主键但是有索引。

BEGIN;
 SELECT * FROM t2 WHERE name = '1' for update;
 COMMIT;

那么当执行上面语句的时候，首先在辅助索引树给name=1数据加锁，然后定位到叶子节点主键id=1，然后通过id=1查询聚簇索引树，同时给聚簇索引树id=1数据加锁。
此时有来个事务2

BEGIN;
SELECT * FROM t2 WHERE id= 1 for update;
COMMIT;

name在事务1提交之前，事务2也是要被阻塞的。

记录锁
就是行锁
SELECT * FROM table WHERE id = 1 FOR UPDATE
当id为主键或唯一索引的时候，加在一行记录上的锁叫做行锁。

间隙锁
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE
间隙锁锁的是区间。

临键锁
间隙锁+记录锁=临键锁

返回顶部

事物隔离级别

脏读
一个事务读到了另一个事务未提交的数据。比如A事务把数据从1更新到2，这事B事务读取到这条数据值为2，然后A事务又回滚了。此时B事务读取到的数据就为脏数据。

不可重复读：一个事务对同一行记录的两次读取结果不同
一个事务中连续读同一条数据值不一样。比如A事务读取一条数据值为1，然后B事务更新该条数据为2，然后A事务再次读取该数据就变成2了。

幻读：一个事务对同一范围的两次查询结果不同
1、A事务首先select id from t where id=1，没有查询出数据
2、B事务执行insert into t value(1)
3、A事务刚才判断数据id=1不存在，现在准备向库中插入id=1的数据时会发现1又存在了。
SQL标准规定，幻读在 repeatable read 及更低隔离级别下可发生。seraliable 级别不可。但需要注意的是：因为InnoDB在repeatable read隔离级别会使用Next-Key Lock，所以InnoDB的repeatable read级别也可避免幻读

为了解决这几个并发时可能出现的问题，数据库设立了四种隔离级别（四种标准）：

read uncommitted
总是读记录的最新版本数据，无论该版本是否已提交。
在业务中基本不会使用该级别。

read committed
使用乐观锁（MVCC）实现。
是大多数数据库默认的隔离级别

repeatable read
SQL规范下的repeatable read允许出现幻读，但InnoDB依靠范围锁，在repeatable read级别下也可避免幻读。
是InnoDB的默认隔离级别。
使用乐观锁（MVCC）+ 范围锁

seraliable
在操作的每一行数据上都加上锁，读取加S锁，DML加X锁。
使用悲观锁（LBCC）

返回顶部

LBCC与MVCC

LBCC和MVCC是为了实现数据库的隔离级别的技术。

LBCC中，对读会加S锁（共享锁），对写会加X锁（排它锁），即读读之间不阻塞，读写、写写之间会阻塞
LBCC被用在 seraliable 隔离级别中，seraliable级别会对每个select语句后面自动加上lock in share mode。

MVCC：
https://www.cnblogs.com/xuwc/p/13873611.html

返回顶部

explain

explain各个列

先来了解一下mysql select语句分哪几种：简单查询和复杂查询。
复杂查询又分：
简单子查询（子查询在from前边）：explain select (select 1 from actor limit 1) from film;
派生表（子查询在from后边）：explain select id from (select id from film) as der;
union：explain select 1 union all select 1;

id
每行的唯一标识

select_type
简单查询：simple
简单子查询：subquery
派生表：deriverd
例子：

mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | <derived3> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL        |
|  3 | DERIVED     | film       | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL        |
|  2 | SUBQUERY    | actor      | const  | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+

union和union result：直接看例子

mysql> explain select 1 union all select 1;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+

table
sql查询的表

type
sql如何查询的数据
依次从最优到最差分别为：NULL>system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

null:执行阶段不需要访问表。
例子：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

mysql> explain select min(id) from film;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

const, system
通过主键查询时，只有一行数据匹配，非常块

eq_ref
当通过主键进行连表的时候
例子：

mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key               | key_len | ref                     | rows | Extra       |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | film_actor | index  | NULL          | idx_film_actor_id | 8       | NULL                    |    3 | Using index |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY       | PRIMARY           | 4       | test.film_actor.film_id |    1 | NULL        |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+

ref
非主键查询 + 非主键连表

ref_or_null
和ref一样，区别是可以查询为null的行
例子：

mysql> explain select * from film where name = "film1" or name is null;
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type        | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref_or_null | idx_name      | idx_name | 33      | const |    2 | Using where; Using index |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+

index_merge

range
范围

index
扫全表，不需要扫描表，只扫描索引树

all
扫描全表

possible_keys
可能使用的索引。
当sql语句使用了索引，但是当表中数据不多的时候，sql优化器会认为使用索引还不如扫描全表。

key
实际使用的索引

key_len
索引里使用的字节数

Extra

返回顶部

prepareStatement和statement

prepareStatement优点：
1、防止sql注入
2、预编译：prepareStatement第一次向数据库发送sql语句，数据库对sql进行编译，然后会把语句都缓存下来，以后相同的预编译sql过来的时候，不需要在进行编译。statement则每次都需要进行一次编译。

返回顶部

mysql存储引擎

myisam

看重的是性能，不支持事务外键等高级功能
查询量大时选择
select count(*) from t 时，不需要扫描全表
表锁
清空表的时候，直接重新建表
非聚簇索引

innodb

支持事务外键等高级功能
更新量大时选择
select count(*) from t 时，需要扫描全表
支持行锁
当执行update语句多时考虑选择
清空表时，一行一行删除数据
聚簇索引
mysql5.5之后该引擎为默认引擎，优势更大一些

返回顶部

mysql插入大量数据

sql语句耗时分析：
1、建立数据库链接
2、发送sql语句
3、解析sql
4、执行

1、存储过程
预编译，性能高。
不够面向对象，维护差。
2、用prepareStatement替代statement，提供预编译。
3、拼接sql
多条sql拼接成一条，统一发送给数据库，减少链接时间
4、myisam表关闭唯一索引更新（通过命令）
innodb表关闭自动提交
4、使用MYSQL LOCAL_INFILE（大数据量建议采用）

使用MYSQL LOCAL_INFILE（大数据量建议采用）

1、jdbc提供的功能，建议使用该方法。
2、该方法可以非常块的将文本数据导入到mysql表中，但是数据需要先写入到文本中，所以可以采用jdbc提供的工具从内存中将数据导入表中，代码如下：

package com.longfor.ads2.Test.batchInsert;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/***
 * 通过MYSQL LOCAL_INFILE实现批量插入
 */
public class Test {


    public void batchInsert(List<BqLoan> bqLoanList) throws ClassNotFoundException, SQLException {
        //1000条一提交
        int COMMIT_SIZE=1000;
        //一共多少条
        int COUNT=bqLoanList.size();
        Connection conn= null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn= DriverManager.getConnection("","","");
            conn.setAutoCommit(false);
            String exectuteSql = "load data local infile ''into table bq_loan character set utf8 fields terminated by ','";
            PreparedStatement pstmt = conn.prepareStatement(exectuteSql);
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < COUNT; i++) {
                sb.append(getTestDataInputStream(bqLoanList.get(i)));
                if (i % COMMIT_SIZE == 0) {
                    InputStream is = null;
                    try {
                        is = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));
                        ((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
                        pstmt.execute();
                        conn.commit();
                        sb.setLength(0);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            InputStream is = null;
            try {
                is = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));
                ((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
                pstmt.execute();
                conn.commit();
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            conn.close();
        }
    }


    /**
     *  组装需要插入的数据，字段间以","隔开，每条数据间以"/n"隔开
     */
    public static StringBuilder getTestDataInputStream(BqLoan BqLoan) {
        StringBuilder builder = new StringBuilder();
        builder.append(BqLoan.getSeq());
        builder.append(",");
        builder.append(BqLoan.getGetLoanNumber());
        builder.append(",");
        builder.append("
");
        return builder;
    }
}

返回顶部