⑴ 设计大型数据库需要注意哪些地方
一个面试问题，关于数据库的设计。 面试开始那位仁兄直接的说了他所面临的问题，公司数据库数据到达百万级别，以后可能会到达千万，需要一个好的设计人员对数据库进行优化设计，这里指的是不光设计符合功能需求，更加要符合性能需求，就是说数据库设计上面需
一个面试问题，关于数据库的设计。
面试开始那位仁兄直接的说了他所面临的问题，公司数据库数据到达百万级别，以后可能会到达千万，需要一个好的设计人员对数据库进行优化设计，这里指的是不光设计符合功能需求，更加要符合性能需求，就是说数据库设计上面需要兼顾到效率。
他给我出了一道题目， 一个信息表，一个类别表。类别表中的类别成树形结构的，这个树可能会非常深，就是说类别会很多。信息表中有所有类别的信息。现在需要设计下类别表和信息表，使得信息表和类别表在查询的效率能够承受千万级别的数据。
我用比较正常的思维去设计，类别表中有id，name，parentid。这时候他说如果以这种方式设计那在查询的时候不断的用嵌套的方式查询效率不行，他让我想下，我说可以将类别表分为几个小表和信息表联结查询，他说这个方法不行。他就直接给我讲了他的方法，但是他说这个方法百万级可以，但是千万级的不行，他的方法也简单，设第一个大类为1，第一个大类下面的一个类别为2，那么在类别表中存储
id name category_id
1 第一大类下的一个小类 Ƈ,2'
那么在查询的时候 select * from category where category_id like Ƈ%'
只要like后面不要写'%1%'。 1的前面不要写%，在效率上面还是能够承受的，这个和索引类似。
他也指出虽然这种方法提高了一定效率但是每次有一个新类别加入时候总要再次遍历整个树形类别，在适合的位置插入，这样子的方式给维护类别表格带来一定麻烦。
一个好的数据库产品不等于就有一个好的应用系统，如果不能设计一个合理的数据库模型，不仅会增加客户端和服务器段程序的编程和维护的难度，而且将会影响系统实际运行的性能。一般来讲，在一个MIS系统分析、设计、测试和试运行阶段，因为数据量较小，设计人员和测试人员往往只注意到功能的实现，而很难注意到性能的薄弱之处，等到系统投入实际运行一段时间后，才发现系统的性能在降低，这时再来考虑提高系统性能则要花费更多的人力物力，而整个系统也不可避免的形成了一个打补丁工程。笔者依据多年来设计和使用数据库的经验，提出以下一些设计准则，供同仁们参考。
命名的规范
不同的数据库产品对对象的命名有不同的要求，因此，数据库中的各种对象的命名、后台程序的代码编写应采用大小写敏感的形式，各种对象命名长度不要超过30个字符，这样便于应用系统适应不同的数据库。
游标（Cursor）的慎用
游标提供了对特定集合中逐行扫描的手段，一般使用游标逐行遍历数据，根据取出的数据不同条件进行不同的操作。尤其对多表和大表定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等特甚至死机，笔者对某市《住房公积金管理系统》进行日终帐户滚积数计息处理时，对一个10万个帐户的游标处理导致程序进入了一个无限期的等特（后经测算需48个小时才能完成）（硬件环境：Alpha/4000 128Mram，Sco Unix ，Sybase 11.0），后根据不同的条件改成用不同的UPDATE语句得以在二十分钟之内完成。示例如下：
Declare Mycursor cursor for select count_no from COUNT
Open Mycursor
Fetch Mycursor into @vcount_no
While (@@sqlstatus=0)
Begin
If @vcount_no=’’ 条件1
操作1
If @vcount_no=’’ 条件2
操作2
??
Fetch Mycursor into @vcount_no
End
??
??
改为
Update COUNT set 操作1 for 条件1
Update COUNT set 操作2 for 条件2
??
??
在有些场合，有时也非得使用游标，此时也可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，可时性能得到明显提高。笔者在某地市〈电信收费系统〉数据库后台程序设计中，对一个表（3万行中符合条件的30多行数据）进行游标操作（硬件环境：PC服务器，PII266 64Mram ,NT4.0 Ms Sqlserver 6.5）。 示例如下：
Create #tmp /* 定义临时表 */
(字段1
字段2
??
)
Insert into #tmp select * from TOTAL where
条件 /* TOTAL中3万行 符合条件只有几十行 */
Declare Mycursor cursor for select * from #tmp
/*对临时表定义游标*/
??
索引(Index)的使用原则
创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据的策略。大型数据库有两种索引即簇索引和非簇索引，一个没有簇索引的表是按堆结构存储数据，所有的数据均添加在表的尾部，而建立了簇索引的表，其数据在物理上会按照簇索引键的顺序存储，一个表只允许有一个簇索引，因此，根据B树结构，可以理解添加任何一种索引均能提高按索引列查询的速度，但会降低插入、更新、删除操作的性能，尤其是当填充因子（Fill Factor）较大时。所以对索引较多的表进行频繁的插入、更新、删除操作，建表和索引时应设置较小的填充因子，以便在各数据页中留下较多的自由空间，减少页分割及重新组织的工作。
数据的一致性和完整性
为了保证数据库的一致性和完整性，设计人员往往会设计过多的表间关联（Relation），尽可能的降低数据的冗余。表间关联是一种强制性措施，建立后，对父表（Parent Table）和子表(Child Table)的插入、更新、删除操作均要占用系统的开销，另外，最好不要用Identify 属性字段作为主键与子表关联。如果数据冗余低，数据的完整性容易得到保证，但增加了表间连接查询的操作，为了提高系统的响应时间，合理的数据冗余也是必要的。使用规则（Rule）和约束（Check）来防止系统操作人员误输入造成数据的错误是设计人员的另一种常用手段，但是，不必要的规则和约束也会占用系统的不必要开销，需要注意的是，约束对数据的有效性验证要比规则快。所有这些，设计人员在设计阶段应根据系统操作的类型、频度加以均衡考虑。
事务的陷阱
事务是在一次性完成的一组操作。虽然这些操作是单个的操作，SQL Server能够保证这组操作要么全部都完成，要么一点都不做。正是大型数据库的这一特性，使得数据的完整性得到了极大的保证。
众所周知，SQL Server为每个独立的SQL语句都提供了隐含的事务控制，使得每个DML的数据操作得以完整提交或回滚，但是SQL Server还提供了显式事务控制语句：
BEGIN TRANSACTION 开始一个事务
COMMIT TRANSACTION 提交一个事务
ROLLBACK TRANSACTION 回滚一个事务
事务可以嵌套，可以通过全局变量@@trancount检索到连接的事务处理嵌套层次。需要加以特别注意并且极容易使编程人员犯错误的是，每个显示或隐含的事物开始都使得该变量加1，每个事务的提交使该变量减1，每个事务的回滚都会使得该变量置0，而只有当该变量为0时的事务提交（最后一个提交语句时），这时才把物理数据写入磁盘。
数据库性能调整
在计算机硬件配置和网络设计确定的情况下，影响到应用系统性能的因素不外乎为数据库性能和客户端程序设计。而大多数数据库设计员采用两步法进行数据库设计：首先进行逻辑设计，而后进行物理设计。数据库逻辑设计去除了所有冗余数据，提高了数据吞吐速度，保证了数据的完整性，清楚地表达数据元素之间的关系。而对于多表之间的关联查询（尤其是大数据表）时，其性能将会降低，同时也提高了客 户端程序的编程难度，因此，物理设计需折衷考虑，根据业务规则，确定对关联表的数据量大小、数据项的访问频度，对此类数据表频繁的关联查询应适当提高数据冗余设计。
数据类型的选择
数据类型的合理选择对于数据库的性能和操作具有很大的影响，有关这方面的书籍也有不少的阐述，这里主要介绍几点经验。
Identify字段不要作为表的主键与其它表关联，这将会影响到该表的数据迁移。
Text 和Image字段属指针型数据，主要用来存放二进制大型对象（BLOB）。这类数据的操作相比其它数据类型较慢，因此要避开使用。
日期型字段的优点是有众多的日期函数支持，因此，在日期的大小比较、加减操作上非常简单。但是，在按照日期作为条件的查询操作也要用函数，相比其它数据类型速度上就慢许多,因为用函数作为查询的条件时，服务器无法用先进的性能策略来优化查询而只能进行表扫描遍历每行。
例如：要从DATA_TAB1中（其中有一个名为DATE的日期字段）查询1998年的所有记录。
Select * from DATA_TAB1 where datepart(yy,DATE)=1998
⑵ EasyExcel快速导出 100W 数据
导出是后台管理系统中的常见功能。当数据量巨大时，内存溢出和页面卡顿问题便成为挑战。曾尝试封装一个导出工具，采用分批查询数据的方法来防止内存溢出，以及使用SXSSFWorkbook缓存数据至文件，以解决下载大型EXCEL文件时页面卡死的问题。然而，这些方法存在封装不够友好、POI操作内存占用过大、空循环和整除数据不完整以及内存溢出隐患等问题。
偶然间发现了阿里开源的EasyExcel框架，发现其内存占用量控制在KB级别，并且完全避免了内存溢出问题（具体实现细节待进一步研究）。其速度极快，大约100万条记录，包含多个字段的情况下，仅需70秒即可完成下载。因此，决定弃用原有封装，转而研究EasyExcel。尽管自己的封装具有一定技术含量，包括外观模式、模板方法模式、委托和组合思想，但EasyExcel提供了更为高效和便捷的解决方案。关注Java技术栈，搜索设计模式，可以获取Java设计模式实战教程。
EasyExcel的GitHub地址为：github.com/alibaba/easy...
案例
根据不同数据量，EasyExcel提供了以下策略：
- 数据量较小（20万以内）：一次查询导出一个SHEET；
- 数据量适中（100万以内）：分批查询导出一个SHEET；
- 数据量巨大（几百万条记录）：分批查询导出多个SHEET。
在生产环境中，对于常量类的编写应确保数据整除以简化逻辑。数据量较小的场景下，一次查询即可完成导出；适中的数据量下，分批查询一个SHEET；而大量数据时，则需采用多个SHEET分批查询。
总结
进行了100万条记录、18个字段的数据导出测试，结果显示耗时约70秒。具体使用情况还需考虑SQL性能。在进行分页时，推荐采用单表查询并一次性获取所需外键对应冗余字段，使用@MapKey注解进行映射，之后遍历时从map中获取对应名称。秉持少发查询SQL的原则，以加速导出过程。额外提示：数据量过大时，应避免使用传统分页策略，转而采用where id> #{lastMaxId} order by id limit 100的方法解决分页效率问题。
⑶ 对比一些MQTT的代理（broker）
broker的主要职责是接受发布者发布的所有消息，并将其过滤后分发给不同的消息订阅者。
如今有很多的broker，下面就是一张关于各种broker对比的图片：
目前我用过的有mosquitto和emqttd（2.0版本后改叫EMQ），因为目前的需求是希望做每秒10万以上的数据接入，所以需要考虑建立集群。但是在使用mosquitto的过程中发现他不支持集群，所以就放弃了，转投emqttd。
在使用mosquitto过程中发现了一些问题：
在使用mosquitto时，如果想使用集群的话，可能会需要进行二次开发。目前只支持桥接。并且他在遍历时的效率非常低，使得他无法支持大量的客户端或者操作过于频繁的操作（比如十万或百万级别的客户端同时发送数据）
emqttd有以下优点：
EMQ 2.0 (Erlang/Enterprise/Elastic MQTT Broker) 是基于 Erlang/OTP 语言平台开发，支持大规模连接和分布式集群，发布订阅模式的开源 MQTT 消息服务器。(抄自 EMQ官方文档 )
⑷ mysql 千万级别的 in 查询优化
这个主键ID其实已经是有建立了索引的了，而在IN查询当中并没有用到而已，其实你可以试试IN里的id少些时，是会用到索引的，但当IN里的id占据全表的大部分数据量时，mysql采用的时全表扫描。在这个时候可以考虑：1.split返回临时表进行表连接，2.使用缓存遍历