数据库使用心得（持续更新...）

1.offset的使用优化

大部分人在进行数据库分页操作的时候喜欢用limit 10 offset 100，但是当offset值变大的时候，对性能的影响非常严重。例如，在覆盖索引的情况下
select * from table_info where xxxx limit 100 offset 1000 的执行效率为0.017s，
select * from table_info where xxxx limit 100 offset 100000 的执行效率为0.107s，百毫秒级
select * from table_info where xxxx limit 100 offset 1000000 的执行效率为0.513s，百毫秒级
造成这种性能差距的原因是limit 100 offset 1000000时，不是单单取出100行，而是取1000000+100行后，截取后100行进行返回。

一种解决方案是在分页请求时，返回下一页的起始ID，如
select * from table_info where xxxxx and id>${id} limit 100
无论数据量和偏移量多大，该查询性能都不会受到影响。

2.数据冗余对性能的优化

数据库范式要求不能存在依赖传递及数据冗余，但实际上，现在很多项目的数据库设计不再完全地遵循范式，因为在很多场景中数据地适当冗余能够提高查询速度，这也是一种牺牲空间来提高时间效率地案例。例如有这样两个表：
表一：新闻表news 字段：id,title标题,classify_id分类ID
表二：分类表classify 字段：id,name分类名,parent_classify_id父分类ID

假设一个父分类下可以有多个子分类（只考虑两级）,现要查询父分类ID为1的新闻列表及对应的子分类名，sql语句如下：
select n.*,c.name from news n,classify c where n.classify_id=c.id and c.parent_classify_id=1

通过explain语句，我们发现这个操作的执行流程是一个循环：对news表中的每一行去classify表中遍历满足parent_classify_id=1的行，看是否有某一行满足n.classify_id=c.id，如果有，返回该行结果。所以该查询语句的搜索次数为：(news表行数)*(classify表中满足parent_classify_id=1条件的行数)，数据量大时效率显然很低

现在我们把新闻表的结构做个修改，增加parent_classify_id父分类ID这个字段
表一：新闻表news 字段：id,title标题,classify_id分类ID,parent_classify_id父分类ID
表二：分类表classify 字段：id,name分类名,parent_classify_id父分类ID

现在上面的查询语句可以变为
select n.*,c.name from news n,classify c where n.parent_classify_id=1 and n.classify_id=c.id
同时为news表的parent_classify_id列建索引，该查询语句将根据索引查找满足parent_class_id=1的行，并通过该行的classify_id，直接去classify表中查找id=classify的行。注意，这里是直接，时间复杂度为1，不需要遍历查找，因为主键索引存在的关系。所以此时的搜索次数为 (news表中满足查询条件的行数)*1，降低了总数据量对结果集的影响。

3.锁

锁用于保证共享数据的安全，保证业务的正确，广泛用于库存、货币等业务场景。这里简单介绍一个货币的场景：用户小明在某消费系统中拥有余额100元，他通过前端页面同时提交了两个金额均为100元的消费请求，如果没有对数据进行加锁，两个请求可能会同时拿到money=100；money=money-100;update money=100，造成了100元可以完成两笔支付的业务错误。所以对于共享数据的操作，必须要进行加锁操作，原则是：一锁二判三更新！！

不同场景有不同的使用案例，这里介绍几种：

1)服务器程序中通过lock或synchronized加锁，该方案适用于对java进程内存中的共享数据加锁，不适用于对数据库中的共享数据加锁。

2)不遵循“一锁二判三更新”原则，服务器程序中直接将判和更新通过update语句对数据库中的数据进行更新，例如
update account set money=money-100 where id=小明的ID，
现在流行的框架都会返回更新成功的行数，行数为1则支付成功，行数为0则余额不足，同时因为update语句具有原子性，不会对共享数据造成安全问题。

3)使用数据库锁for update，例如
money=select money from account where id=小明的id for update
money=money-100；
update money;
在select操作中增加了for update，对该行数据进行了加锁，其它线程对于该行数据的请求必须等待该锁的释放。缺点；数据库锁对数据库要求很高，增加了数据库的负担和性能开销。
使用for update的时候要注意：
如果select for update有明确的主键，利用到主键索引，是对该行加锁，应避免对某行加锁时间过长
如果select for update无主键索引或未利用到主键索引，是对该表加锁，必须避免对表加锁

ps：说到锁，不得不提下共享数据。我刚工作的时候，对于共享数据一词的概念是理解的，但实际开发的时候经常不知道哪些数据是非线程安全的，这里我就简单的罗列一下。
Struts中Ctroller的成员变量线程安全，Service组件同下。
Spring MVC默认单例时的Controller、Service中的成员变量是否线程安全取决于其类型，如INTEGER非线程安全，ConcurrentHashMap线程安全。
缓存件或数据库，常用的Guava Cache或者Redis或者Mysql都提供了原子操作，使用原子操作时是线程安全的，如果取出来操作后再更新肯定是线程不安全的，需要遵循一锁二盼三更新的原则。

4.INT(N)误区

mysql建表的时候对于int,float,double等数据类型允许类似INT(N)的声明，大家都知道这是声明该字段的长度，可是是什么长度呢，是存储长度还是显示长度呢。之前我一直认为这是指存储长度，实际上，N表示最大显示宽度（字段设置zerofill属性时可查看区别），INT不指定N时，默认为11。N的值跟该字段所占多少存储空间或最大能存储多大长度数据并无任何关系。也就是说 INT(3),INT(4),INT(8)在磁盘上都是占用4字节的存储空间。