说出一些数据库优化方面的经验？

数据库优化

1、有外键约束的话会影响增删改的性能，如果应用程序可以保证数据库的完整性那就去除外键

2、Sql语句全部大写，特别是列名大写．因为数据库的机制是这样的．sql语句发送到数据库服务器，数据库首先就会把sql编译成大写再执行，如果一开始就编译成大写就不需要了把Sql编译成大写这个步骤了。

3、如果应用程序可以保证数据库的完整性．可以不需要按照三大范式来设计数据库

4、其实可以不必要创建很多索引护索引可以加快查询速度，但是索引会消耗磁盘空间

5、如果是jdbc的话，使用Preparedstatement不使用Statement，来创建SQL,Preparedstatement的性能比Statement的速度要快．使用Preparedstatement对象SQL语句会预编译在此对象中，preParedstatement对象可以多次高效的执行

6、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

7、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

 

优化SQL查询语句

1．对查询进行优化，应尽量避免全表扫描，首先应考虑在where及orderby涉及的列上建立索引

2、用索引可以提高查询

3、select子句中避免使用*号，尽量全部大写SQL

4、应尽量避免在where子句中对字段进行is null 判断，否则将导致引擎放弃使用索引二进行全局扫描

5、where子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

6、避免在 where 子句中使用 != 或 <> 操作符，也会导致引擎放弃使用索引而进行全表扫描

7、in和not in也要慎用，否则会导致全表扫描，能用between不用in，用exist代替in

     OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内

 

SELECT ID FROM TABLEA WHERE ID IN (SELECT ID FROM TABLEB)

        如上SQL，IN执行的时候是在内存中遍历比较，IN(SELECT ID FROM TABLEB)括号中语句只执行一次，把TABLEB表中的所有ID字段缓存起来，之后检查TABLEA表的ID是否与TABLEB表中的ID相等，如果ID相等则将TABLEA表中的记录加入到结果集中，直到遍历完TABLEA表的所有记录。

SELECT ID FROM TABLEA WHERE ID EXISTS(SELECT ID FROM TABLEA.ID= TABLEB.ID)

        如上SQL，EXISTS查询是遍历TABLEA中的数据，TABLEA中的每一条数据与TABLEB连表查询，如果有返回结果，则把该记录添加到结果集中，所以当TABLEB的数据量远大于TANLEA时，EXISTS效率大大优于IN.当TABLEA表数据与TABLEB表数据一样大时，IN与EXISTS效率差不多。

 

in和exists一般用于子查询。

• 使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。
• in在内表查询或者外表查询过程中都会用到索引。
• exists仅在内表查询时会用到索引
• 一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询寻得结果集较小，外表较大时，使用in效率更高。
• 对于not in和not exists，not exists效率比not in的效率高，与子查询的结果集无关，因为not in对于内外表都进行了全表扫描，没有使用到索引。not exists的子查询中可以用到表上的索引。

8、UNION All能满足业务需求不要使用UNION

        如果我们需要将两个或者多个SELECT语句的结果作为合并为一个整体显示出来，我们可以用UNION或者UNION ALL关键字。UNION(联合)和UNION ALL的作用是将多个结果合并在一起显示出来。

两者的区别是：

        UNION会自动压缩多个结果集合中的重复结果，而UNION ALL 则将所有的结果全部显示出来，不管是不是重复。所以当UNION ALL能满足业务需求的时候，尽量使用UNION ALL而不用UNION。

 

 

大表数据查询，怎么优化

1、优化shema、sql语句＋索引；

2、加缓存，Memcached,Redis;

3、主从复制，读写分离

4、垂直拆分，根据你模块的藕合度，将一个大的系统分为多个小的系统，也就是分布式系统；

5、水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个白理的sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带harding key，将数据定位到限定的表上去查，而不是扫描全部的表；

6、对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

7、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

 

 

 

MyISAM与InnoDB的特点比较

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

•不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁

•不支持事务

•不支持外键

•不支持崩溃后的安全恢复

•在表有读取查询的同时，支持往表中插入新纪录

•支持BLOB和TEXT的前500个字符索引，支持全文索引

•支持延迟更新索引，极大提升写入性能

•对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

 

InnoDB

InnoDB在MySQL 5.5后成为默认索引，它的特点是：

•支持行锁，采用MVCC来支持高并发

•支持事务

•支持外键

•支持崩溃后的安全恢复

•不支持全文索引

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表。

 

分区

分区的好处是：

•可以让单表存储更多的数据

•分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作

•部分查询能够从查询条件确定只落在少数分区上，速度会很快

•分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备

•可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争

•可以备份和恢复单个分区

 

分区的限制和缺点：

•一个表最多只能有1024个分区

•如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

•分区表无法使用外键约束

•NULL值会使分区过滤无效

•所有分区必须使用相同的存储引擎

 

分区的类型：

•RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区

•LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择

•HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式

•KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

 

分区适合的场景有：

•最适合的场景数据的时间序列性比较强，则可以按时间来分区，如下所示：

CREATE TABLE members (

firstname VARCHAR(25) NOT NULL,

lastname VARCHAR(25) NOT NULL,

username VARCHAR(16) NOT NULL,

email VARCHAR(35),

joined DATE NOT NULL

)

PARTITION BY RANGE( YEAR(joined) ) (

PARTITION p0 VALUES LESS THAN (1960),

PARTITION p1 VALUES LESS THAN (1970),

PARTITION p2 VALUES LESS THAN (1980),

PARTITION p3 VALUES LESS THAN (1990),

PARTITION p4 VALUES LESS THAN MAXVALUE

);

 

垂直拆分#

        垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联。

垂直拆分的优点是：

•可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)

•可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起

•数据维护简单

 

缺点是：

•主键出现冗余，需要管理冗余列

•会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力

•依然存在单表数据量过大的问题（需要水平拆分）

•事务处理复杂

 

 

水平拆分

        水平拆分是通过某种策略将数据分片来存储，分库内分表和分库两部分，每片数据会分散到不同的MySQL表或库，达到分布式的效果，能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表

库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决

水平拆分的优点是:

•不存在单库大数据和高并发的性能瓶颈

•应用端改造较少

•提高了系统的稳定性和负载能力

 

缺点是：

•分片事务一致性难以解决

•跨节点Join性能差，逻辑复杂

•数据多次扩展难度跟维护量极大

 

分片

分片原则

•能不分就不分，参考单表优化

•分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量

•分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容

•尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题

•查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。

•通过数据冗余和表分区赖降低跨库Join的可能

        这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。

 

        总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会遍历所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。