时间：2021-07-01 10:21:17 帮助过：18人阅读
D、一个表不要加太多索引，因为索引影响插入和更新的速度，因为 insert 或 update 时有可能会重建索引
（2）表数据字段的冗余（反范式）
（3）表的设计 垂直与水平分表，垂直分库与水平分库
表的垂直拆分
可以参阅文章：一分钟掌握数据库垂直拆分 http://mp.weixin.qq.com/s/ezD0CWHAr0RteC9yrwqyZA
随着需求越来越多，某一张表的列越来越增加，为了控制表的宽度可以进行表的垂直拆分。 将表进行垂直拆分：
原因：数据库以页存储，表越宽，每一行的数据越大，一页中所能存储的行数就会越来越少。拆分成多张窄表，每一张表中所含长度不会大，优化了IO效率。
原则：
原来一张大表有上亿数据，需要减少表中的数据量，为了控制表的大小可以进行表的水平拆分。 将表进行水平拆分：
那么如何把一张大表中的数据，分配到多张小表中呢？拆分可以按照Hash方式，如下图：
每一张表都拥有一个主键值，通过对主键值进行哈希操作，比如说主键按摩取值，把一张大表平均分配到几张小表中，解决了表中数据量的问题。
convert()
cast()
truncate() 截断小数
round() 四舍五入
lower()/upper() 把参数变为大小写
length() 求参数的长度
concat（参数1，参数2）： 把参数1和参数2连接起来
floor（参数）：返回小于或等于参数的最大整数
ceil（参数）：返回大于或等于参数的最小整数
abs（参数）：求参数的绝对值
mod（参数1，参数2）: 求参数1除以参数2后的余数
substr(x,start ,[length]) 取子串
if()
ifnull()
date_format()
聚合函数有：
count() 求该字段的总记录
min()/max() 求字段的最小最大值
sum() 求该字段的和
avg() 求平均
group_concat() 迭代分组后中的每个数据行
MySQL的加锁， 锁是作用于索引的,行级锁都是基于索引的。只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
分析时主要涉及到的点：
（1）WHERE条件的拆分
（2）关于索引与事务隔离级别的组合
参考：
（1）mysql事务和锁InnoDB http://www.cnblogs.com/zhaoyl/p/4121010.html
（2）http://www.cnblogs.com/exceptioneye/p/5450874.html
事务有ACID属性，所以就是如何保证这几个特性就可以实现事务。
（1）隔离性由锁来保证。一个事务在操作过程中看到了其他事务的结果，如幻读。锁是用于解决隔离性的一种机制。事务的隔离级别通过锁的机制来实现。
（2）一致性由undo log来保证，可以做事务回滚和MVCC的功能。
（3）原子性与持久性由redo log来保证。事务在提交时，必须将该事务的所有日志写入重做日志文件进行持久化。
数据库的事务隔离级别有（多个事务并发的情况下）：
1、read uncommitted
#首先，修改隔离级别
set tx_isolation=‘READ-UNCOMMITTED‘;
select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
#事务A：启动一个事务
start transaction;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
#事务B：也启动一个事务(那么两个事务交叉了)
在事务B中执行更新语句，且不提交
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+
#事务A：那么这时候事务A能看到这个更新了的数据吗?
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 | --->可以看到！说明我们读到了事务B还没有提交的数据
| 2 | 2 |
| 3 | 3 |
+------+------+
#事务B：事务B回滚,仍然未提交
rollback;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
#事务A：在事务A里面看到的也是B没有提交的数据
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 | --->脏读意味着我在这个事务中(A中)，事务B虽然没有提交，但它任何一条数据变化，我都可以看到！
| 2 | 2 |
| 3 | 3 |
+------+------+
2、read committed
#首先修改隔离级别
set tx_isolation=‘read-committed‘;
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
#事务A：启动一个事务
start transaction;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
#事务B：也启动一个事务(那么两个事务交叉了)在这事务中更新数据，且未提交
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+
#事务A：这个时候我们在事务A中能看到数据的变化吗?
select * from tx; ------------->
+------+------+ |
| id | num | |
+------+------+ |
| 1 | 1 |--->并不能看到！ |
| 2 | 2 | |
| 3 | 3 | |
+------+------+ |——>相同的select语句，结果却不一样
|
#事务B：如果提交了事务B呢? |
commit; |
|
#事务A: |
select * from tx; ------------->
+------+------+
| id | num |
+------+------+
| 1 | 10 |--->因为事务B已经提交了，所以在A中我们看到了数据变化
| 2 | 2 |
| 3 | 3 |
+------+------+
3、repeatable read
#首先，更改隔离级别 set tx_isolation=‘repeatable-read‘; select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ #事务A：启动一个事务 start transaction; select * from tx; +------+------+ | id | num | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +------+------+ #事务B：开启一个新事务(那么这两个事务交叉了) 在事务B中更新数据，并提交 start transaction; update tx set num=10 where id=1; select * from tx; +------+------+ | id | num | +------+------+ | 1 | 10 | | 2 | 2 | | 3 | 3 | +------+------+ commit; #事务A：这时候即使事务B已经提交了,但A能不能看到数据变化？ select * from tx; +------+------+ | id | num | +------+------+ | 1 | 1 | --->还是看不到的！(这个级别2不一样，也说明级别3解决了不可重复读问题) | 2 | 2 | | 3 | 3 | +------+------+ #事务A：只有当事务A也提交了，它才能够看到数据变化 commit; select * from tx; +------+------+ | id | num | +------+------+ | 1 | 10 | | 2 | 2 | | 3 | 3 | +------+------+
4、serializable
#首先修改隔离界别 set tx_isolation=‘serializable‘; select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | SERIALIZABLE | +----------------+ #事务A：开启一个新事务 start transaction; #事务B：在A没有commit之前，这个交叉事务是不能更改数据的 start transaction; insert tx values(‘4‘,‘4‘); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction update tx set num=10 where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
总结一下：
√: 可能出现 ×: 不会出现
| 事务的隔离级别 | 脏读 事务1更新了记录，但没有提交，事务2读取了更新后的行，然后事务T1回滚，现在T2读取无效。违反隔离性导致的问题，添加行锁实现 | 不可重复读 事务1读取记录时，事务2更新了记录并提交，事务1再次读取时可以看到事务2修改后的记录（修改批更新或者删除）需要添加行锁进行实现 |
幻读 事务1读取记录时事务2增加了记录并提交，事务1再次读取时可以看到事务2新增的记录。需要添加表锁进行实现。InnoDB存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题 |
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable read | × | × | √ |
| Serializable | × | × | × |
对应着Spring中的5个事务隔离级别（通过lsolation的属性值指定）
1、default 默认的事务隔离级别。使用的是数据库默认的事务隔离级别
2、read_uncommitted 读未提交，一个事务可以操作另外一个未提交的事务，不能避免脏读，不可重复读，幻读，隔离级别最低，并发性能最高
3、read_committed（脏读） 大多数数据库默认的事务隔离级别。读已提交，一个事务不可以操作另外一个未提交的事务， 能防止脏读，不能避免不可重复读，幻读
4、repeatable_read（不可重复读） innodb默认的事务隔离级别。能够避免脏读，不可重复读，不能避免幻读
5、serializable（幻读） innodb存储引擎在这个级别才能有分布式XA事务的支持。隔离级别最高，消耗资源最低，代价最高，能够防止脏读， 不可重复读，幻读
1、范式
数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：
(1)第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如 某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。
(2)第二范式(确保表中的每列都和主键相关)
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。
订单信息表
这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。
而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。
(3)第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。
这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。
更高的范式要求这里就不再作介绍了，个人认为，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。
完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。
从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。
2、反范式
通过适当的数据冗余，来提高读的效率
如何查询订单详情信息？
SELECT b.用户名, b.电话, b.地址, a.订单ID, sum(c.商品价格* c.商品数量)AS 订单价格, c.商品价格, d.商品名称 FROM ‘订单表‘ a JOIN ‘用户表‘ b ON a.用户ID = b.用户ID JOIN ‘订单商品表‘ c ON c.订单ID = b.订单ID JOIN ‘商品表‘ d ON d.商品ID = c.商品ID GROUP BY b.用户名,b.电话,b.地址,a.订单ID,c.商品价格,d.商品名称
该查询需要关联多张表，然后再通过sum汇总出价格，查询效率不太高。 如果通过表中部分数据的冗余，进行反范式化设计，如下图：
简化sql的查询
SELECT b.用户名, b.电话, b.地址, a.订单ID, a.订单价格, c.商品价格, c.商品名称 FROM ‘订单表‘ a JOIN ‘用户表‘ b ON a.用户ID = b.用户ID JOIN ‘订单商品表‘ c ON c.订单ID = b.订单ID
互联网项目中，读写比率大概是3:1或是4:1的关系，读远远高于写，写的时候增加数据冗余，增加了读的效率，这样还是很值得的。
反范式的目的是减少读取数据的开销，那么随之带来的就是更多写数据的开销。因为我们需要预先定稿大量的数据副本。
反范式还会带来数据的不一致，可以通过异步的写来进行定期数据整理，修复不一致的数据。
必看文章：细聊冗余表数据一致性（架构师之路） http://www.jianshu.com/p/65743dc5bdea
Java面试05|MySQL及InnoDB引擎
标签：说明 通过 class 参考 为我 length count() column 产生