Mysql数据库设计学习

1、 存储引擎

　　　　创建表的时候，一般程序中我们都需要支持事务，所以我们一般使用的是InnoDB 存储引擎

2、 命名规则

　　　　a、所有的表应该有统一的项目前缀，表示为同一个项目的表格

　　　　b、尽量保持至少三级，例如 act_hi_identitylink   act第一级别表示是谁的表，hi表示那个模块的表 identitylink 具体功能的表

　　　　c、字段名称与java代码相一致，java实体类保持驼峰原则，表之间用"_"来链接

3、数据库字段类型定义

　　　　a、通常使用varchar 而不使用char

　　　　b、id 一般使用bigint

　　　　c、我认为时间类型的最好也是存储成String类型的 varchar -----这样代码中就可以自由转换，实现自己想要的实现方式。

　　　　d、金额类的还是要用 decimal(16,2)

4、 适当包含的冗余字段，可以减少我们查询的join

5、索引

　　　　 a、普通索引可以增加访问数据库的速度

　　　　 b、唯一索引可以减少数据库的出现重复的脏数据

　　　　c、创建联合索引的数据要注意索引的顺序，sql语句中的匹配条件也要跟索引的顺序保持一致，切注意：索引的顺势不正确也可能导致严重的后果。

　　　　　　联合索引中的字段应尽量满足过滤数据从多到少的顺序，也就是差异最大的字段应该放在第一字段

　　　　　　联合索引中的字段应尽量与SQL语句的顺序一致，使SQL语句尽量以整个索引作为条件，尽量避免以索引的一部分（特别是首个索引与索引的首个字段不一致）作为查询条件

　　　　　　eg：对一张表上的a、b、c 上创建联合索引(a、b、c)

　　　　　　　　 where a=1;  where a >= 12 and a<15; where a =1 and b<5 ;where a =1 and b=7 and c>=40 为条件时用到此联合索引

　　　　　　　 　where b=3；where c=188 ;where b>12 and c =2   首个索引的和查询的首个字段不一致，索引失效

　　　　　　　　order by 和 group  也是无法使用索引的

6、合理构造Query语句

　　　　a、insert 批量插入1000条数据时，效率最高，多于1000条时候，可以考虑分开插入

　　　　b、业务逻辑查询性能 and、or、in   所以应该尽量避免在大集合使用in

　　　　c、join查询要通过小集合驱动大记录集，这个原则来减少嵌套的次数

　　　　d、select * 尽量不要出现

 

7、合理的使用cache来优化数据，减少io流的访问消耗内存。

 

8、为什么用自增列作为主键

　　　　a、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，

　　　　　　则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、

　　　　　　如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

　　　　b、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，

　　　　　　因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

　　　　c、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

　　　　d、 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，

　　　　　　此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，

　　　　　　这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

9、为什么使用数据索引能提高效率

　　　　a、数据索引的存储是有序的

　　　　b、在有序的情况下，通过索引查询一个数据是无需遍历索引记录的

　　　　c、 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)　　

 

转载于:https://www.cnblogs.com/Geyoung/p/9518483.html