数据库优化方案

一、sql语句优化和索引优化

参考文章：http://www.cnblogs.com/tangyanbo/p/4462734.html

                  https://tech.meituan.com/mysql-index.html

　　　　　http://www.jizhuomi.com/wap.asp?act=View&id=315

1.mysql嵌套子查询效率确实比较低，如下面sql语句，查询时间需要30248.271s。

select * from Student s where s.id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

mysql是先执行外层查询，再执行里层的查询。

2.可以将其优化成连接查询

CREATE index sc_c_id_index on SC(c_id);

CREATE index sc_score_index on SC(score);

SELECT * from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100

3.连接表时，可以先用where条件对表进行过滤，然后做表连接，在用where条件过滤时，先对其中的字段建立索引

（mysql会对连表语句做优化）

4.建立合适的索引，必要时建立多列联合索引（最左前缀匹配）

在大数据量的情况下，若建立单列索引区分度不高，可以考虑多列联合索引。

5.学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要

下面是一些优化总结：

1. 列类型尽量定义成数值类型，且长度尽可能短（列长度越短，B+树高度越低，减少磁盘IO次数）

2. 建立单列索引

3. 根据需要建立多列联合索引

当单个列过滤之后还有很多数据，那么索引的效率将会比较低，即列的区分度较低，

那么如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高。

4. 根据业务场景建立覆盖索引（如果一个索引包含，或者说覆盖所有需要查询的字段的值，我们就称之为”覆盖索引”）

只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率

5. 多表连接的字段上需要建立索引

这样可以极大的提高表连接的效率

6. where条件字段上需要建立索引

7. 排序字段上需要建立索引

8. 分组字段上需要建立索引

9. where条件字段上不要使用运算、函数，以免索引失效

二、缓存

ｒｅｄｉｓ

是一个高性能的ｋｅｙ／ｖａｌｕｅ数据库，基于内存存储。可以支持丰富的数据类型，例如Ｓｔｒｉｎｇ、Ｌｉｓｔ、Ｓｅｔ、ＳｏｒｔＳｅｔ、Ｈａｓｈ等。单线程，操作原子性。订阅／发布模型，支持数据过期机制。

ｒｅｄｉｓ的PUB/SUB机制，即发布／订阅模式。这种模式生产者和消费者是１－Ｍ的关系，

三、主从复制与读写分离

主从复制过程：

1） 在每个事务更新数据完成之前，master在二进制日志记录这些改变。写入二进制日志完成后，master通知存储引擎提交事务。

2） Slave将master的binary log复制到其中继日志。首先slave开始一个工作线程（I/O），I/O线程在master上打开一个普通的连接，然后开始binlog dump  process。binlog  dump  process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件，I/O线程将这些事件写入中继日志。

3） Sql  slave  thread（sql从线程）处理该过程的最后一步，sql线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会位于os缓存中，所以中继日志的开销很小。

读写分离：

读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。可以通过Amoema（变形虫）第三方代理实现。

优点：

1、写操作都在主服务器上，实现数据的统一更新，从服务器只负责读取，负载均衡分担了数据库压力。

2、写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，从另一个方面有效的提高了读的效率，保证了系统的高可用性。

缺点：

1、当写操作也很多以至于超出了单台主机的处理能力，就无能为力了。

2、主库插入记录后，无法从从库中及时读取（在数据库层之上架构redis分布式缓存）。

四、分区表

对于大数据(如10TB)而言,索引起到的作用相对小,因为索引的空间与维护成本很高,另外如果不是索引覆盖查询,将导致回表,造成大量磁盘IO.那么对于这种情况的解决策略是:
1.全量扫描数据,不要任何索引
通过分区表表达式将数据定位在少量的分区表,然后正常访问这些分区表的数据
2.分离热点,索引数据
将热点数据分离出来在一个小的分区,并对分区建立索引,对热点数据的查询提高效率.

1、分区功能不是在存储引擎层完成的，分区允许根据指定的规则，跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。

2、MySQL数据库支持的分区类型是水平分区，此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。

3、分区列必须是唯一索引的一个组成部分。

４、ＭｙＳＱＬ数据库分区总是视ｎｕｌｌ值小于任何非ｎｕｌｌ值。

５、对于ＯＬＡＰ的应用，分区可以提高查询性能。对于ＯＬＴＰ应用，分区表不一定能够使得查询性能提升。

６、如果索引列和分区列不匹配,且查询中没有包含过滤分区的条件,会导致无法进行分区过滤,那么将会导致查询所有分区，使得磁盘ＩＯ次数上升，查询性能下降。

７、表和分区之间的交换技术对于历史记录的归档具有很好的作用。

分区类型

1、range分区

主要用于日期列的分区

create table sales(

money int unsigned not null,

date DATETIME)engine=innodb

PARTITION BY RANGE (YEAR(date))(

PARTITION p2008 VALUES LESS THAN (2009),

PARTITION p2009 VALUES LESS THAN (2010),

PARTITION p2010 VALUES LESS THAN (2011)

);

这样创建表，便于对sales这张表的管理，比如删除数据。也可以加快某些查询操作。

2、list分区

分区列的值是离散的

create table t(

a int,

b int)engine=innodb

PARTITION BY LIST (b)(

PARTITION p0 VALUES IN (1,3,5,7,9),

PARTITION p1 VALUES IN (0,2,4,6,8)

);

3、hash分区

需要指定hash函数和分区数量

create table t(

a int,

b DATETIME)engine=innodb

PARTITION BY HASH (YEAR（b）)

PARTITIONS 4；

4、key分区

与hash分区类似，不同在于KEY分区的hash函数由数据库提供

5、columns分区

可以直接使用非整型数据进行分区

create table sales(

money int unsigned not null,

date DATETIME)engine=innodb

PARTITION BY RANGE COLUMNS (date)(

PARTITION p2008 VALUES LESS THAN (2009),

PARTITION p2009 VALUES LESS THAN (2010),

PARTITION p2010 VALUES LESS THAN (2011)

);

6、子分区

在分区的基础上再进行分区，MySQL数据库允许在range和list的分区上再进行hash或key分区

create table sales(

money int unsigned not null,

date DATE)

PARTITION BY RANGE (YEAR(date))

SUBPARTITION　BY HASH (ＴＯ＿ＤＡＹＳ（b）)(

PARTITION p2008 VALUES LESS THAN (2009)（

ＳＵＢＰＡＲＴＩＴＩＯＮ　ｓ０，

ＳＵＢＰＡＲＴＩＴＩＯＮ　ｓ１

）,

PARTITION p2009 VALUES LESS THAN (2010)（

ＳＵＢＰＡＲＴＩＴＩＯＮ　ｓ２，

ＳＵＢＰＡＲＴＩＴＩＯＮ　ｓ３

）,

PARTITION p2010 VALUES LESS THAN (2011)（

ＳＵＢＰＡＲＴＩＴＩＯＮ　ｓ４，

ＳＵＢＰＡＲＴＩＴＩＯＮ　ｓ５

）

);

五、垂直切分

垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，应用中配置多个数据源或者使用中间件来访问拆分后的多个库。

存在的问题：

１、跨库ｊｏｉｎ

通过使用字段冗余方法来解决，这是一种典型的反范式设计，在互联网行业中比较常见，通常是为了性能来避免join查询。举个电商业务中很简单的场景：“订单表”中保存“卖家Id”的同时，将卖家的“Name”字段也冗余，这样查询订单详情的时候就不需要再去查询“卖家用户表”。字段冗余能带来便利，是一种“空间换时间”的体现。

２、分布式事务

使用分布式事务，ｉｎｎｏＤＢ存储引擎事务隔离级别必须设置为ＳＥＲＩＡｌＩＺＡＢＬＥ。

Ｊａｖａ的ＪＴＡ可以支持ＭｙＳＱＬ的分布式事务

３、存在单库性能瓶颈

六、水平拆分

水平拆分是把单表按某个规则把数据分散到多个表的拆分方式，可以分到一个库中的多个表中，也可以分到多个数据库中。

实际应用中，一般通过对用户编号进行ｈａｓｈ切分