MySQL持续更新

发表于 | 更新于 | 分类于

MySQL是我比较常用的数据库，要持续刷新对它的认知呀

数据库事务的四个属性（ACID）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

数据库隔离级别

脏读是指一个线程中的事务读取到了另外一个线程中未提交的数据。

不可重复读（虚读）是指一个线程中的事务读取到了另外一个线程中提交的update的数据，两次读的不一样

幻读是指一个线程中的事务读取到了另外一个线程中提交的insert或delete的数据，两次读的不一样

第1类丢失更新：事务A撤销时，把已经提交的事务B的更新数据覆盖了。

第2类丢失更新：事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失。

隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read） 第一类丢失更新 第二类丢失更新
未提交读（Read uncommitted） √ √ √ × √
已提交读（Read committed） × √ √ × √
可重复读（Repeatable read） × × √ × ×
可串行化（Serializable ） × × × × ×

级别越高，数据越安全，但性能越低，×表示不允许，√表示允许

在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)，有可能发现幻读；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。

不可重复读（虚读）和幻读的差别

从总的结果来看, 似乎两者都表现为两次读取的结果不一致.
但如果你从控制的角度来看, 两者的区别就比较大：
对于前者, 只需要锁住满足条件的记录
对于后者, 要锁住满足条件及其相近的记录

数据库范式

第一范式：强调的是列的原子性，即列不能够再分成其他几列

第二范式：一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的其中一部分。

第三范式：非主键列必须直接依赖于主键，不能存在传递依赖

sql优化的经验

  • SELECT子句中避免使用 *
  • 用TRUNCATE替代DELETE
  • sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

触发器优点和缺点

优点：自动执行，级联更新

缺点：不好维护，性能一般

存储过程优点和缺点

优点：可以封装数据逻辑和业务规则，减少sql传输的时间，存储过程在第一次执行时进行语法检查和编译，快速执行

缺点：代码可复用性差

触发器和存储过程在复杂的程序里尽量不要多用

JDBC操作数据库步骤

下面的代码以连接本机的Oracle数据库为例，演示JDBC操作数据库的步骤。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");

创建连接
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

创建语句
PreparedStatement ps = con.prepareStatement("select * from emp where sal between ? and ?");
ps.setInt(1, 1000);
ps.setInt(2, 3000);

执行语句
ResultSet rs = ps.executeQuery();

处理结果
while(rs.next()) {
    System.out.println(rs.getInt("empno") + " - " + rs.getString("ename"));
}

关闭资源
finally {
    if(con != null) {
        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Statement和PreparedStatement有什么区别？哪个性能更好？

①PreparedStatement接口代表预编译的语句，它主要的优势在于可以减少SQL的编译错误并增加SQL的安全性，防止注入

  1. 在使用参数化查询的情况下，数据库系统（eg:MySQL）不会将参数的内容视为SQL指令的一部分来处理，而是在数据库完成SQL指令的编译后，才套用参数运行，因此就算参数中含有破坏性的指令，也不会被数据库所运行
  2. 数据库系统会对sql语句进行预编译处理（如果JDBC驱动支持的话），预处理语句将被预先编译好，这条预编译的sql查询语句能在将来的查询中重用，这样一来，它比Statement对象生成的查询速度更快。

②PreparedStatement中的SQL语句是可以带参数的，采用占位符，避免了用字符串连接拼接SQL语句的麻烦和不安全

③当批量处理SQL或频繁执行相同的查询时，PreparedStatement有明显的性能上的优势，由于数据库可以将编译优化后的SQL语句缓存起来，下次执行相同结构的语句时就会很快（不用再次编译和生成执行计划）

http://www.importnew.com/5006.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public class PreparedStmtExample {
 
    public static void main(String args[]) throws SQLException {
        Connection conn = DriverManager.getConnection("mysql:\\localhost:1520", "root", "root");
        PreparedStatement preStatement = conn.prepareStatement("select distinct loan_type from loan where bank=?");
        preStatement.setString(1, "Citibank");
 
        ResultSet result = preStatement.executeQuery();
 
        while(result.next()){
            System.out.println("Loan Type: " + result.getString("loan_type"));
        }       
    }
} 
Output:
Loan Type: Personal Loan
Loan Type: Auto Loan
Loan Type: Home Loan
Loan Type: Gold Loan

联合（复合）索引

最左前缀原则

索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c或a,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

要注意的是a，b，c的顺序可以乱调，mysql会调成合适的顺序，但是复合索引的最前面的字段a最好是经常出现在where条件中的

mysql的索引如果单索引在联合索引之前定义，那么只会用单索引而不会用联合索引了

OR操作是不能用联合索引的

使用场景

  • MySQL只对<，<=，=，>，>=，BETWEEN，IN使用索引
  • MySQL对LIKE ‘%name%’不使用索引，对LIKE ‘name%’使用索引

优化索引

  • 索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

  • 使用短索引

    对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

  • 索引列排序

    MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

  • like语句操作

    一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

  • 不要在列上进行运算

    1
    SELECT * FROM student WHERE YEAR(adddate)<2007;

    将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:

    1
    SELECT * FROM student WHERE adddate<'2007-01-01';

  • 不使用NOT IN和<>操作

    这个很简单，因为这两个操作不会使用索引。

缺点

索引虽然能提高查询性能，但是泛滥使用也会导致一些问题：

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

参考路径

数据库索引

松散索引

实际上就是当MySQL 完全利用索引扫描来实现GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。

要利用到松散索引扫描实现GROUP BY，需要至少满足以下几个条件：

  • GROUP BY 条件字段必须在同一个索引中最前面的连续位置；
  • 在使用GROUP BY 的同时，只能使用MAX 和MIN 这两个聚合函数；
  • 如果引用到了该索引中GROUP BY 条件之外的字段条件的时候，必须以常量形式存在；

为什么松散索引扫描的效率会很高

因为在没有WHERE 子句，也就是必须经过全索引扫描的时候， 松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多。而在WHERE 子句包含范围判断式或者等值表达式的时候， 松散索引扫描查找满足范围条件的每个组的第1 个关键字，并且再次读取尽可能最少数量的关键字。

紧凑索引

紧凑索引扫描实现GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取的数据来完成GROUP BY 操作得到相应结果。

索引合并

index merge：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并

MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

在mysql 5.1版本之前只要用了or，sql就不会用任何索引；
5.1之后依旧不能用联合索引，但是两个字段都建了索引的话，会用index_merge，但是如果只有其中一个字段建了索引，那就只会用那个索引

在使用or的时候，如果那两个字段都有索引，那么mysql会自动选择是不是索引合并比全表扫描好

index intersect merge

index intersect merge就是多个索引条件（单索引和联合索引）扫描得到的结果进行交集运算。显然在多个索引提交之间是 AND 运算时，才会出现 index intersect merge. 但是一般都可以用联合索引替代。

index uion merge

index uion merge就是多个索引条件扫描，对得到的结果进行并集运算，显然是多个条件之间进行的是 OR 运算

辅助索引

辅助索引又叫二级索引，他是指除了主键索引之外的索引，innodb的主键索引是聚簇索引，数据和索引放在一起，但是辅助索引是非聚簇索引，myisam的主键索引和辅助索引一样都是非聚簇索引