10条关于数据库的技巧和经验_MySQL
程序员文章站
2022-05-02 14:13:13
...
1）字段长度和类型：数据来自外系统的一定要与外系统保持一致，避免插入失败或截断。禁止使用Blob,Clob,Text,Image等大字段。
2）中间表：多对多关联需要添加中间表，多对一加关联字段即可。关联字段为了避免数据迁移麻烦，可以不用自增长ID关联，而是用实际业务关联的值。
3）联接查询：若表字段过多，则根据业务拆出一个或多个字表。若有关联过多，则可在主表冗余一些需要的字段来减少查询时的表关联。
4）索引与性能：（后续）
5）并发访问（读写）：当读操作远大于写操作时，利用中间件分发或在程序中自行配置不同的读和写数据源来实现读写分离。主库的数据修改同步到各个从库上去。以MySQL为例，从库可使用MyISAM引擎提升性能，主从分离后不仅服务器增加，同时还极大缓解排它锁和共享锁的竞争。
6）并发访问（写）：
四种并发问题
丢失或覆盖更新：多个事务读取同一数据，并发更新时发生更新丢失问题。
脏读：一个事务读取到了另一个未提交事务写的数据。
不可重复读：一个事务重新读取前面读过的数据，发现已被另一事务修改。
幻影读：一个事务重新读取前面读过的数据，发现多了或少了几行。
Oracle和SQLServer默认级别为读提交(Read-Committed, RC)，MySQL/InnoDB默认为可重复读(Read-Repeatable, RR)。能解决的并发问题如下：
========================================================================
隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
========================================================================
未提交读（Read uncommitted） 可能 可能 可能
已提交读（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（Serializable ） 不可能 不可能 不可能
串行化事务 最简单策略就是修改事务级别为可串行化，这样某一事务执行时会禁止其他事务执行。但这种方法实际上不可行，因为它使程序对数据库的操作变成单线程的了，不能充分共享数据库资源。 悲观锁控制 当用户执行的操作时会在数据上加锁，直到当前事务释放锁时，其他事务才能执行与该锁相冲突的操作。 以MySQL的事务为例： set autocommit=0; begin; select status from t_goods where id=1 for update ... commit; 默认为行锁，超过一定行数升级为表锁。执行select... for update后，则其他select...for update对此数据的查询会阻塞，但普通select此表不会受影响。 乐观锁控制 用时间戳或版本号控制。用户读数据时不锁定，但查询数据时也一起将控制字段查出。当用户更新时先检查此时数据库中控制字段的值与程序中之前拿到的值是否相同。不同则此条数据已被处理，相同则更新数据，并同时更新控制字段的值为当前时间戳或版本号加1。 7）小事务：避免长时间锁定数据，可以将大数据量的更新或删除分成批次，一个批次是一个小事务。通过记录已成功批次的序号方便意外终止后的恢复。 8）数据删除：删除时可先逻辑删除，更新一个标记列的值表示数据已无效，达到记录留存的作用。日后真正无用时可做物理删除。 9）扩展性：核心表预留出Int,Long,Char等各种类型的扩展字段。 10）分区分库：日均数据增长量大的表，可以按进行分区甚至复合分区。过期数据进行删除或归档到历史库。归档方法可采用：定期请求DBA手动、应用程序(如Java)中实现、服务器上部署Shell脚本实现等。
隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
========================================================================
未提交读（Read uncommitted） 可能 可能 可能
已提交读（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（Serializable ） 不可能 不可能 不可能
串行化事务 最简单策略就是修改事务级别为可串行化，这样某一事务执行时会禁止其他事务执行。但这种方法实际上不可行，因为它使程序对数据库的操作变成单线程的了，不能充分共享数据库资源。 悲观锁控制 当用户执行的操作时会在数据上加锁，直到当前事务释放锁时，其他事务才能执行与该锁相冲突的操作。 以MySQL的事务为例： set autocommit=0; begin; select status from t_goods where id=1 for update ... commit; 默认为行锁，超过一定行数升级为表锁。执行select... for update后，则其他select...for update对此数据的查询会阻塞，但普通select此表不会受影响。 乐观锁控制 用时间戳或版本号控制。用户读数据时不锁定，但查询数据时也一起将控制字段查出。当用户更新时先检查此时数据库中控制字段的值与程序中之前拿到的值是否相同。不同则此条数据已被处理，相同则更新数据，并同时更新控制字段的值为当前时间戳或版本号加1。 7）小事务：避免长时间锁定数据，可以将大数据量的更新或删除分成批次，一个批次是一个小事务。通过记录已成功批次的序号方便意外终止后的恢复。 8）数据删除：删除时可先逻辑删除，更新一个标记列的值表示数据已无效，达到记录留存的作用。日后真正无用时可做物理删除。 9）扩展性：核心表预留出Int,Long,Char等各种类型的扩展字段。 10）分区分库：日均数据增长量大的表，可以按进行分区甚至复合分区。过期数据进行删除或归档到历史库。归档方法可采用：定期请求DBA手动、应用程序(如Java)中实现、服务器上部署Shell脚本实现等。