事物(ACID)
是什么?
- 原子性: 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
- 一致性: 执行事务前后,数据保持一致,多个事务对同一个数据读取的结果是相同的;
- 隔离性: 并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的;
- 持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。
为什么?
安全。
怎么做?
- start transcation; commit;rollback;
- @Transcation
隔离级别
是什么
- READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。(演示我们系统的mysql是不是理论所说的这样。)
- SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。
为什么?
事物的隔离性理论上来说事务之间的执行不应该相互产生影响,其对数据库的影响应该和它们串行执行时一样。
然而完全的隔离性会导致系统并发性能很低,降低对资源的利用率,因而实际上对隔离性的要求会有所放宽,这也会一定程度造成对数据库一致性要求降低。
不同隔离级别带来的问题
相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题,主要包括以下几种情况。
怎么做?
- 设置隔离级别:set global transaction_isolation =‘read-committed’;
- 查询隔离级别 SELECT @@tx_isolation;
我们系统的MYSQL隔离级别是?
SELECT @@tx_isolation;
锁
常见的并发控制技术
- 悲观锁(select for update的使用)
- 乐观锁(版本号,已知条件校验)
索引
类型
- 普通索引
- 唯一索引
- 主键索引
- 组合索引(普通,唯一)
- 全文索引
- 空间索引(MYISAM专属)
索引失效
- like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
- or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效
- 组合索引,不是使用第一列索引,索引失效。
- 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。
- 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的,所以这样的操作不能使用索引,可以用其他的办法处理,例如:数字类型,判断大于0,字符串类型设置一个默认值,判断是否等于默认值即可。
- 在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 优化方法: key<>0 改为 key>0 or key<0。
- 对索引字段进行计算操作、字段上使用函数。
- not in ,not exist.
- 左连接查询或者右连接查询查询关联的字段编码格式不一样
- 当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效。(数据量小的表)
- 连接查询中,按照优化器顺序的第一张表不会走索引
(解释:例如左/右连接查询,A表关联B表,要在将优化器顺序的第二张表上关联的字段上加索引,而第一张表则无需加索引,无用的索引也会影响性能。而第内连接就不一样了;
如果是内连接优化器本身就会根据索引情况,连接表的大小等去选择执行顺序了。)
如:
#A表的name字段加了索引,B表的name字段没加索引,则下面的sql不走索引
select a.* from A a left join B b on a.name = b.name
建立索引的几个原则
-
尽量使用唯一索引。对于有唯一值的列索引效果最好,对于像性别只有很少的值的列索引效果就不明显。
-
索引长度尽量短。这样做有几个好处,首先短的索引可以节省索引空间,也会使查找的速度得到提升。对于较短的键值,索引高速缓存中的块能容纳更多的键值,MySQL也可以在内存中容纳更多的值。
-
太长的列,可以选择只建立部分索引(短索引)
-
更新非常频繁的数据不适宜建索引
-
利用最左前缀原则,比如建立了一个联合索引(a,b,c),那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
-
不要过多创建索引,首先过多的索引会占用更多的空间,而且每次增、删、改操作都会重建索引,而且过多索引也会增加之后的优化复杂度
-
尽量扩展索引,比如现有索引(a),现在我又要对(a,b)进行索引,不需要再建一个索引(a,b)
-
请注意,一次查询是不能应用多个索引的
-
<,<=,=,>,>=,BETWEEN,IN 可用到索引,<>,not in ,!= 则不行
-
like “xxxx%” 是可以用到索引的,like “%xxxx” 则不行(like “%xxx%” 同理)
-
NULL会使索引的效果大打折扣
sql
- 批量操作数据 <–扩展文章
##建表建议
- flag字段(0=失效;1=有效),方便软删除
- 对外或对接外部的接口,增加请求报文和响应报文两字段
- 表注释一定要写(状态,类型等)
- 根据业务,选择加索引
- 冷热数据分离
- 字符集统一
- 不存文件二进制数据
- 与钱相关的使用decimal类型
- 等。。。