mysql 查某个时间字段 35秒之内的数据_（面试）大型网站应用之海量数据、高并发解决方案...

面试时会遇到这样的提问，就记录下来，加深印象；以后工作中也可以用到。

海量数据的解决方案：

使用缓存
页面静态化技术
数据库优化
分类数据库中活跃的数据。
批量读取和延迟修改
读写分离
适应nosql和Hadoop技术
分布式部署数据库
应用服务和数据服务分离
使用搜索引擎搜索数据库中的数据
进行业务拆分

一、使用缓存

网站访问数据的特点大多数呈现为“二八定律”：80%的业务访问集中在20%的数据上。例如：在某一段时间内百度的搜索热词可能集中在少部分的热门词汇上；新浪微博某一时期也可能大家广泛关注的主题也是少部分事件。

总的来说就是用户只用到了总数据条目的一小部分，当网站发展到一定规模，数据库IO操作成为性能瓶颈的时候，使用缓存将这一小部分的热门数据缓存在内存中是一个很不错的选择，不但可以减轻数据库的压力，还可以提高整体网站的数据访问速度。

使用缓存的方式可以通过程序代码将数据直接保存到内存中，例如通过使用Map或者ConcurrentHashMap；另一种，就是使用缓存框架：Redis、Ehcache、Memcache等。使用缓存框架的时候，我们需要关心的就是什么时候创建缓存和缓存失效策略。

注意：使用缓存的时候还要考虑到缓存服务器发生故障时候如何进行容错处理，是使用N多台服务器缓存相同的数据，通过分布式部署的方式对缓存数据进行控制，当一台发生故障的时候自动切换到其他的机器上去；还是通过Hash一致性的方式，等待缓存服务器恢复正常使用的时候重新指定到该缓存服务器。Hash一致性的另一个作用就是在分布式缓存服务器下对数据进行定位，将数据分布在不用缓存服务器上。

二、页面静态化技术

使用传统的JSP界面，前端界面的显示是通过后台服务器进行渲染后返回给前端游览器进行解析执行。现在提倡前后端分离，前端界面基本都是HTML网页代码，通过Angular JS或者NodeJS提供的路由向后端服务器发出请求获取数据，然后在游览器对数据进行渲染，这样在很大程度上降低了后端服务器的压力。还可以将这些静态的HTML、CSS、JS、图片资源等放置在缓存服务器上或者CDN服务器上，一般使用最多的应该是CDN服务器或者Nginx服务器提供的静态资源功能。

优化前端应该遵循以下几点：

尽量减少HTTP请求
使用cdn(Content Delivery Network，即内容分发网络)
添加expire头，控制缓存的失效日期
采用Gzip压缩组件
将样式表放在头部
将脚本放在底部
避免使用css表达式
使用外部的JavaScript和css
减少DNS查询
精简JavaScript
避免重定向
使用ajax可以缓存

三、数据库优化

大多数网站性能的瓶颈都是开在数据库IO操作上。对于数据库的优化来说，是一种用技术换金钱的方式。数据库优化的方式很多，常见的可以分为：

数据库表结构优化
SQL语句优化
分区
分表
索引优化
使用存储过程代替直接操作

1、表结构优化

1.1、命名规范

1.库名、表名、字段名必须使用小写字母，并采用下划线分割。

MySQL有配置参数lower_case_table_names，不可动态更改，linux系统默认为 0，即库表名以实际情况存储，大小写敏感。如果是1，以小写存储，大小写不敏感。如果是2，以实际情况存储，但以小写比较。
如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱。
字段名显示区分大小写，但实际使⽤用不区分，即不可以建立两个名字一样但大小写不一样的字段。
为了统一规范，库名、表名、字段名使用小写字母。

2、库名、表名、字段名禁止超过32个字符

库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符。

3、使用INNODB引擎

INNODB引擎是MySQL5.5版本以后的默认引擘，支持事务、行级锁，有更好的数据恢复能力、更好的并发性能，同时对多核、大内存、SSD等硬件支持更好，支持数据热备份等，因此INNODB相比MyISAM有明显优势。

InnoDB支持事务，MyISAM不支持。
InnoDB支持行级锁，MyISAM支持表级锁。
InnoDB支持MVCC，MyISAM不支持。(MVCC (Multiversion Concurrency Control)，即多版本并发控制技术,它使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，取而代之的是把数据库的行锁与行的多个版本结合起来，只需要很小的开销,就可以实现非锁定读，从而大大提高数据库系统的并发性能。)
InnoDB支持外键，而MyISAM不支持。
InnoDB不支持全文索引，而MyISAM支持。

innodb引擎的4大特性：

插入缓冲(insert buffer)。
二次写(double write)。
自适应哈希索引(ahi)。
预读(read ahead)

4、库名、表名、字段名禁止使用MySQL保留字

当库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得⾮非常复杂。

5、禁止使用分区表

分区表对分区键有严格要求；分区表在表变大后，执⾏行DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使用分区表，并建议业务端手动SHARDING。

6、建议使用UNSIGNED存储非负数值。

同样的字节数，非负存储的数值范围更大。如TINYINT有符号为 -128-127，无符号为0-255。

7、建议使用INT UNSIGNED存储IPV4

用UNSINGED INT存储IP地址占用4字节，CHAR(15)则占用15字节。另外，计算机处理整数类型比字符串类型快。使用INT UNSIGNED而不是CHAR(15)来存储IPV4地址，通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数，需要使用DECIMAL或两个BIGINT来存储。

8、强烈建议使用TINYINT来代替ENUM类型

ENUM类型在需要修改或增加枚举值时，需要在线DDL，成本较高；ENUM列值如果含有数字类型，可能会引起默认值混淆。

9、使用VARBINARY存储大小写敏感的变长字符串或二进制内容

VARBINARY默认区分大小写，没有字符集概念，速度快。

10、INT类型固定占用4字节存储

例如INT(4)仅代表显示字符宽度为4位，不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示，python、java客户端等不具备这个功能。

11、区分使用DATETIME和TIMESTAMP

存储年使用YEAR类型。存储日期使用DATE类型。存储时间(精确到秒)建议使用TIMESTAMP类型。

DATETIME和TIMESTAMP都是精确到秒，优先选择TIMESTAMP，因为TIMESTAMP只有4个字节，而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及⾃自动更新的特性。注意：在5.5和之前的版本中，如果一个表中有多个timestamp列，那么最多只能有一列能具有自动更新功能。

12、所有字段均定义为NOT NULL

对表的每一行，每个为NULL的列都需要额外的空间来标识。
B树索引时不会存储NULL值，所以如果索引字段可以为NULL，索引效率会下降。
建议用0、特殊值或空串代替NULL值。

2、SQL优化

当只要一行数据时使用LIMIT 1
为搜索字段建索引
在Join表的时候使用相当类型的列，并将其索引
千万不要ORDER BY RAND()
SELECT只获取必要的字段、避免SELECT *
用IN代替OR。SQL语句中IN包含的值不应过多，应少于1000个。
SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
避免使用存储过程、触发器、视图、自定义函数等。(这些高级特性有性能问题，以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、SHARDING等变得更加困难。)
不要在MySQL数据库中存放业务逻辑。

3、索引优化

MySQL的优化主要分为结构优化(Scheme optimization)和查询优化(Query optimization)。

3.1、联合索引及最左前缀原理

联合索引(复合索引)

首先介绍一下联合索引。联合索引其实很简单，相对于一般索引只有一个字段，联合索引可以为多个字段创建一个索引。它的原理也很简单，比如，我们在(a,b,c)字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段，因此，联合索引的特点就是：

第一个字段一定是有序的
当第一个字段值相等的时候，第二个字段又是有序的，比如下表中当A=2时所有B的值是有序排列的，依次类推，当同一个B值得所有C字段是有序排列的、

| A | B | C |

| 1 | 2 | 3 |

| 1 | 4 | 2 |

| 1 | 1 | 4 |

| 2 | 3 | 5 |

| 2 | 4 | 4 |

| 2 | 4 | 6 |

| 2 | 5 | 5 |

其实联合索引的查找就跟查字典是一样的，先根据第一个字母查，然后再根据第二个字母查，或者只根据第一个字母查，但是不能跳过第一个字母从第二个字母开始查。这就是所谓的最左前缀原理。

最左前缀原理

我们再来详细介绍一下联合索引的查询。还是上面例子，我们在(a,b,c)字段上建了一个联合索引，所以这个索引是先按a 再按b 再按c进行排列的，所以：

以下的查询方式都可以用到索引：

select * from table where a=1；select * from table where a=1 and b=2；select * from table where a=1 and b=2 and c=3；

上面三个查询按照 (a ), (a，b ),(a，b，c )的顺序都可以利用到索引，这就是最左前缀匹配。

如果查询语句是：

select * from table where a=1 and c=3；那么只会用到索引a。

如果查询语句是：

select * from table where b=2 and c=3；因为没有用到最左前缀a，所以这个查询是用不到索引的。

如果用到了最左前缀，但是顺序颠倒会用到索引码？

比如：

select * from table where b=2 and a=1；select * from table where b=2 and a=1 and c=3；

如果用到了最左前缀而只是颠倒了顺序，也是可以用到索引的，因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。但我们还是最好按照索引顺序来查询，这样查询优化器就不用重新编译了。

3.2、索引优化策略

最左前缀匹配原则
主键外检一定要建索引
对 where,on,group by,order by 中出现的列使用索引
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
为较长的字符串使用前缀索引
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建
对于like查询，”%”不要放在前面。
查询where条件数据类型不匹配也无法使用索引，字符串与数字比较不使用索引;

高并发情况下的解决方案