JAVA常用知识总结（十三）——数据库（三）

  • Mysql的主键选择（主键自增，UUID，snowflake）?

使用自增长做主键的优点：
1、很小的数据存储空间
2、性能最好
3、容易记忆
使用自增长做主键的缺点：
1、如果存在大量的数据，可能会超出自增长的取值范围
2、很难（并不是不能）处理分布式存储的数据表，尤其是需要合并表的情况下
3、安全性低，因为是有规律的，容易被非法获取数据
使用GUID做主键的优点：
1、它是独一无二的，出现重复的机会少
2、适合大量数据中的插入和更新操作，尤其是在高并发和分布式环境下
3、跨服务器数据合并非常方便
4、安全性较高
使用GUID做主键的缺点：
1、存储空间大（16 byte），因此它将会占用更多的磁盘空间
2、会降低性能
3、很难记忆

ps:

UUID：

是一个由4个连字号(-)将32个字节长的字符串分隔后生成的字符串，总共36个字节长。比如：550e8400-e29b-41d4-a716-446655440000

GUID：

是微软对UUID这个标准的实现。UUID是由开放软件基金会（OSF）定义的。UUID还有其它各种实现，不止GUID一种。比如我们这里在Java中用到的。

总结：

项目是单机版的，并且数据量比较大（百万级）时，用自增长的，此时最好能考虑下安全性，做些安全措施。

  • MySQL分布式架构的ID的选择？

分布式架构，意味着需要多个实例中保持一个表的主键的唯一性。这个时候普通的单表自增ID主键就不太合适，因为多个mysql实例上会遇到主键全局唯一性问题。

自增ID主键+步长，适合中等规模的分布式场景

        在每个集群节点组的master上面，设置（auto_increment_increment），让目前每个集群的起始点错开 1，步长选择大于将来基本不可能达到的切分集群数，达到将 ID 相对分段的效果来满足全局唯一的效果。

优点是：实现简单，后期维护简单，对应用透明。

缺点是：第一次设置相对较为复杂，因为要针对未来业务的发展而计算好足够的步长;

规划：

比如计划总共N个节点组，那么第i个节点组的my.cnf的配置为：

auto_increment_offset  i

auto_increment_increment  N

假如规划48个节点组，N为48，现在配置第8个节点组，这个i为8，第8个节点组的my.cnf里面的配置为：

auto_increment_offset  8

auto_increment_increment  48

UUID，适合小规模的分布式环境

对于InnoDB这种聚集主键类型的引擎来说，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，而且由于索引和数据存储在一起，字符串做主键会造成存储空间增大一倍。

在存储和检索的时候，innodb会对主键进行物理排序，这对auto_increment_int是个好消息，因为后一次插入的主键位置总是在最后。但是对uuid来说，这却是个坏消息，因为uuid是杂乱无章的，每次插入的主键位置是不确定的，可能在开头，也可能在中间，在进行主键物理排序的时候，势必会造成大量的 IO操作影响效率，在数据量不停增长的时候，特别是数据量上了千万记录的时候，读写性能下降的非常厉害。

优点：搭建比较简单，不需要为主键唯一性的处理。

缺点：占用两倍的存储空间（在云上光存储一块就要多花2倍的钱），后期读写性能下降厉害。

雪花算法自造全局自增ID，适合大数据环境的分布式场景

总结

　　（1）单实例或者单节点组：

　　 经过500W、1000W的单机表测试，自增ID相对UUID来说，自增ID主键性能高于UUID，磁盘存储费用比UUID节省一半的钱。所以在单实例上或者单节点组上，使用自增ID作为首选主键。

　　（2）分布式架构场景：

         20个节点组下的小型规模的分布式场景，为了快速实现部署，可以采用多花存储费用、牺牲部分性能而使用UUID主键快速部署；

         20到200个节点组的中等规模的分布式场景，可以采用自增ID+步长的较快速方案。

         200以上节点组的大数据下的分布式场景，可以借鉴类似twitter雪花算法构造的全局自增ID作为主键。

  • MySQL中count()函数的思考与总结？

　　为什么要使用count(*),而避免使用指定具体的列名？

　　count()函数里面的参数是列名的的时候,那么会计算这个字段有值项的次数。也就是,该字段没有值的项并不会进入计算范围(就是网上常说的值为null的项不纳入统计)，

这样的话，你想统计的行数并不准确。更重要的是,还会增加消耗。因为，需要判断扫描所有行才知道值是否有值。

一般是建议以count(字段名)替代count(*)。实际使用中，我的理解是,你要统计那个列的总数，比如我想统计会员的总数，那么我可以以uid作为参考，uid是null的都不去扫描了

于是查询就是select count(uid) from members，如果使用count(*)，它会计算总行数。不管你字段是否有值都会列入计算范围

　　innodb和myisam在使用count(*)的差别？

myisam引擎很容易获得总行数的统计。查询速度变得更快。因为myisam存储引擎已经存储了表的总行数。

应该是每次新增加一行,这个计数器就加1。英文资料是这样子说的：

 On MyISAM, doing a query that does SELECT COUNT(*) FROM {some_table}, is very fast, since MyISAM keeps the information in the index

也就是说，把表的总数缓存在索引中了

针对Innodb表,尽量不执行 SELECT COUNT(*) 语句,因为Innodb表没有类似MyISAM那样的内部计数器来记录表记录总量,执行这个操作将会全表扫描,速度很慢。所以呢，表的行数越多，扫描的时间就越多。当你表行数还是小数量的时候体会不出速度差距。比如百万也感觉不出明显。上千万就会很明显速度差别了。（涉及到总数操作，专门维护一个总数。新注册一个会员,总数值加1,需要总数的时候直接拿这个总数,比如分页时。

方案二的扩展性更好，随着会员表数量增大,水平切分会员表,要获取用户总数。可以专门看这张表。总数也可能是存在内存中,这样分页计算的时候速度很快。累加操作的时候将内存中的值加1。总数这个值要持久化，还是要存到磁盘上的，也就是数据库中(可以是关系型数据库，也可以是mongdb这样的数据库很适合存储计数)。把总数放在内存中，只是避免频繁的磁盘i/0操作(操作数据库就要涉及到磁盘读写)。

ps:这种计数器的思想在计算机的很多地方都会用到。比如mysql数据库他自己的information_schema库维护每个表的row总数，就是这样的方式。） 

　　oralce数据库有专门对count(1)和count(*)的区别？

count(1)其实这个1，并不是表示第一个字段，而是表示一个固定值。

count(1)，其实就是计算一共有多少符合条件的行。
1并不是表示第一个字段，而是表示一个固定值。
其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1.
同理，count(2)，也可以，得到的值完全一样，count('x')，count('y')都是可以的。一样的理解方式。在你这个语句理都可以使用，返回的值完全是一样的。就是计数。
count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。

count(1)就是不指定具体字段，固定值。

　　mysql数据库有专门对count(1)和count(*)的区别？

1.如果在开发中确实需要用到count()聚合，那么优先考虑count(*)，因为mysql本身对于count(*)做了特别的优化处理。

有主键或联合主键的情况下，count(*)略比count(1)快一些。 
没有主键的情况下count(1)比count(*)快一些。 
如果表只有一个字段，则count(*)是最快的。
2.使用count()聚合函数后，最好不要跟where age = 1；这样的条件，会导致不走索引，降低查询效率。除非该字段已经建立了索引。使用count()聚合函数后，若有where条件，且where条件的字段未建立索引，则查询不会走索引，直接扫描了全表。 
3.count(字段),非主键字段，这样的使用方式最好不要出现。因为它不会走索引

  • sql中exists和in的区别？

exists和in

in 是把外表和内表作hash join，而exists是对外表作loop，每次loop再对内表进行查询。
如：

A：select * from t1 a where exists (select * from t2 b where b.id = a.id)
B：select * from t1 a where a.id in (select b.id from t2 b)

对于A，用到了t2上的id索引，exists执行次数为t1.length，不缓存exists的结果集。
对于B，用到了t1上的id索引，首先执行in语句，然后将结果缓存起来，之后遍历t1表，将满足结果的加入结果集，所以执行次数为t1.length*t2.length次。
因此对t1表大t2表小的情况使用in，t2表小t1表大的情况使用exists

not exists和not in

A：select * from t1 a where not exists (select * from t2 b where b.id = a.id)
B：select * from t1 a where a.id not in (select b.id from t2 b)

对于A，和exists一样，用到了t2上的id索引，exists()执行次数为t1.length，不缓存exists()的结果集。
而对于B，因为not in实质上等于!= and != ···，因为!=不会使用索引，故not in不会使用索引。
因此，不管t1和t2大小如何，均使用not exists效率会更高。

  • 库表设计的问题?

　　引擎选择

在 mysql 5.1 中，引入了新的插件式存储引擎体系结构，允许将存储引擎加载到正在运行的 mysql 服务器中。使用 mysql 插件式存储引擎体系结构，允许数据库专业人员或者设计库表的软件开发人员为特定的应用需求选择专门的存储引擎，完全不需要管理任何特殊的应用编码要求，也无需考虑所有的底层实施细节。因此，尽管不同的存储引擎具有不同的能力，应用程序是与之分离的。此外，使用者可以在服务器、数据库和表格三个层级中存储引擎，提供了极大的灵活性。 

mysql 常用的存储引擎包括 MYISAM、Innodb 和 Memory，其中各自的特点如下：

  • MYISAM : 全表锁，拥有较高的执行速度，一个写请求请阻塞另外相同表格的所有读写请求，并发性能差，占用空间相对较小，mysql 5.5 及以下仅 MYISAM 支持全文索引，不支持事务。

  • Innodb：行级锁（SQL 都走索引查询），并发能力相对强，占用空间是 MYISAM 的 2.5 倍，不支持全文索引（5.6 开始支持），支持事务。

  • Memory : 全表锁，存储在内存当中，速度快，但会占用和数据量成正比的内存空间且数据在 mysql 重启时会丢失。

基于以上特性，建议绝大部份都设置为 innodb 引擎，特殊的业务再考虑选用 MYISAM 或 Memory ，如全文索引支持或极高的执行效率等。

　　分表方法

在数据库表使用过程中，为了减小数据库服务器的负担、缩短查询时间，常常会考虑做分表设计。分表分两种，一种是纵向分表（将本来可以在同一个表的内容，人为划分存储在为多个不同结构的表）和横向分表（把大的表结构，横向切割为同样结构的不同表）。

其中，纵向分表常见的方式有根据活跃度分表、根据重要性分表等。其主要解决问题如下：

  • 表与表之间资源争用问题；

  • 锁争用机率小；

  • 实现核心与非核心的分级存储，如UDB登陆库拆分成一级二级三级库；

  • 解决了数据库同步压力问题。

横向分表是指根据某些特定的规则来划分大数据量表，如根据时间分表。其主要解决问题如下：

  • 单表过大造成的性能问题；

  • 单表过大造成的单服务器空间问题。

　　　索引问题 

索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于更快地获取信息。 mysql 有四种不同的索引类型：

  • 主键索此 ( PRIMARY )

  • 唯一索引 ( UNIQUE )

  • 普通索引 ( INDEX )

  • 全文索引（FULLTEXT , MYISAM 及 mysql 5.6 以上的 Innodb ）

建立索引的目的是加快对表中记录的查找或排序，索引也并非越多越好，因为创建索引是要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间维护索引。

在设计表或索引时，常出现以下几个问题：

  • 少建索引或不建索引。这个问题最突出，建议建表时 DBA 可以一起协助把关。

  • 索引滥用。滥用索引将导致写请求变慢，拖慢整体数据库的响应速度（5.5 以下的 mysql 只能用到一个索引)。

  • 从不考虑联合索引。实际上联合索引的效率往往要比单列索引的效率更高。①

  • 非最优列选择。低选择性的字段不适合建单列索引，如 status 类型的字段。

  • 慢 SQL 问题?

　　 导致慢 SQL 的原因

在遇到慢 SQL 情况时，不能简单的把原因归结为 SQL 编写问题(虽然这是最常见的因素)，实际上导致慢 SQL 有很多因素，甚至包括硬件和 mysql 本身的 bug。根据出现的概率从大到小，罗列如下：

  • SQL编写问题

  • 锁

  • 业务实例相互干绕对 IO/CPU 资源争用

  • 服务器硬件

  • MYSQL BUG

　　由 SQL 编写导致的慢 SQL 优化

针对SQL编写导致的慢 SQL，优化起来还是相对比较方便的。正如上一节提到的正确的使用索引能加快查询速度，那么我们在编写 SQL 时就需要注意与索引相关的规则：

  • 字段类型转换导致不用索引，如字符串类型的不用引号，数字类型的用引号等，这有可能会用不到索引导致全表扫描；

  • mysql 不支持函数转换，所以字段前面不能加函数，否则这将用不到索引；

  • 不要在字段前面加减运算；

  • 字符串比较长的可以考虑索引一部份减少索引文件大小，提高写入效率；

  • like % 在前面用不到索引；

  • 根据联合索引的第二个及以后的字段单独查询用不到索引；

  • 不要使用 select *；

  • 排序请尽量使用升序 ;

  • or 的查询尽量用 union 代替 （Innodb）；

  • 复合索引高选择性的字段排在前面；

  • order by / group by 字段包括在索引当中减少排序，效率会更高。

除了上述索引使用规则外，SQL 编写时还需要特别注意一下几点：

  • 尽量规避大事务的 SQL，大事务的 SQL 会影响数据库的并发性能及主从同步；

  • 分页语句 limit 的问题；

  • 删除表所有记录请用 truncate，不要用 delete；

  • 不让 mysql 干多余的事情，如计算；

  • 输写 SQL 带字段，以防止后面表变更带来的问题，性能也是比较优的 ( 涉及到数据字典解析，请自行查询资料)；

  • 慎用 Order by rand()。 

注释一：

我们来想象一下当数据库有N个索引并且查询中分别都要用上他们的情况：
查询优化器（用大白话说就是生成执行计划的那个东西）需要进行N次主二叉树查找[这里主二叉树的意思是最外层的索引节点]，此处的查找流程大概如下：
查出第一条column1主二叉树等于1的值，然后去第二条column2主二叉树查出foo的值并且当前行的coumn1必须等于1，最后去column主二叉树查找bar的值并且column1必须等于1和column2必须等于foo。
如果这样的流程被查询优化器执行一遍，就算不死也半条命了，查询优化器可等不及把以上计划都执行一遍，贪婪算法（最近邻居算法）可不允许这种情况的发生，所以当遇到以下语句的时候，数据库只要用到第一个筛选列的索引（column1），就会直接去进行表扫描了。

select count(1) from table1 where column1 = 1 and column2 = ‘foo’ and column3 = ‘bar’

所以与其说是数据库只支持一条查询语句只使用一个索引，倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。
所以如上条的情况，最佳推荐是使用index(column1,column2,column3） 这种联合索引，此联合索引可以把b+tree结构的优势发挥得淋漓尽致：
一条主二叉树（column=1），查询到column=1节点后基于当前节点进行二级二叉树column2=foo的查询，在二级二叉树查询到column2=foo后，去三级二叉树column3=bar查找。

posted @ 2018-12-25 10:48  用心记录每一天  阅读(409)  评论(1)  编辑  收藏  举报