数据库优化

数据库引擎

InnoDB

mysql中存储数据的地方在安装文件的ibdata1中  ,如果数据过大后会再次生成一个ibdata_xxxx 文件出来

MyISAM

存储数据是在安装文件的中data文件夹下的以数据库名称命名的文件夹下,frm文件后缀结尾

InnoDB与MyISAM的区别

 

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不 支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，

 

 

但是不提供事务支持，而 InnoDB提供事务支持已经外部键等高级数据库功能。

 

数据库做集群搭建，在spring配置文件中搭建两个数据源

 

 

配置两个 sessionfacotry 去分别 ref 两个sessionfactory  一个 叫read，一个叫write

 

 

两个hibernatetemplate引用read和write，这样实现了基本的读写分离

 

 

我们做了集群，两个数据库服务器中的数据是一致的，我们做了两个数据源这样用户使用不同的数据库引擎一个读一个写

 

 

批量处理数据:(代码优化:提高程序执行性能)

 

降低了java程序代码(客户端)和数据库之间的 网络通信的次数。

在jdbc中进行批量插入的核心API为addBatch,executeBatch

 

大数据量的插入问题:（jdbc,hibernate,ibatis）

1.每次只插入一条和数据库交互多次(很耗时间)

2.批量插入和数据库只交互一次(内存溢出)

3.分段批量插入(推荐)

数据库读写分离

mysql的主从复制 读写分离

 

将数据库分为2种

一种是读master  主要用来增删改

一种是写slave   主要用来查询

 

当增加主库的数据的时候会生成二进制日志文件 然后从库会调用日志文件进行和主库相同的操作  这之间会有问题 就是时间差  可以根据工作时候的情况来进行相应的操作来弥补这个问题  

 

在spring的配置文件中配置2个数据源 然后写一个切面类来定义哪些方法进读库哪些进写库 在配置事物的时候来进行配置 默认的数据源是写库 因为写库能生成日志供从库进行操作 而从库不会生成

 

 

mysql主(称master)从(称slave)复制的原理：

1、 master将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin指定的文件(这些记录叫做二进制日志事件，binary log events)

2、 slave将master的binary log events拷贝到它的中继日志(relay log)

3、 slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

 

数据库索引

建立索引

会在索引字段中每条数据加上一个标示，

 

数据库表字段建立索引会占用空间，

 

建立索引建立到select经常查询到的字段上

 

MySQL索引的优化

 

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据还要更新索引。

 

建立索引会占用磁盘空间。一般情 况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立索引，或优化查询语句。

 

 

分表分库

    1.分表

     在以往的开发项目的过程中，如果一张表数据量非常大，已经在操作这表的时候，执行效率已经不符合用户的体验度了，我们就会针对这张表进行分表的操作，分表之后，大大提高了数据库效率

     1877516909

 

switch(qq)

 

case 100000000>&&>200000000 1-2_qq

case 100000000>&&>200000000 2-3_qq

case 100000000>&&>200000000 3-4_qq

 

 

PC_PRODUCT

 

RI_PRODUCT

 

 

session.createQuery("from"+TaELE)

 

 

     2.分库

     分库我们通常会根据我们这个项目的版本或者是时间达到某个点进行分库，比如说，我们在做订单的模块的时候，我们会在每一年的结束的时候来进行分库，

     加入用户去查询2015的消费情况的时候。我们回去在2015的这个库里去查找，如果实在今年我们会在2016库里去查找

读写分离

   在以往的开发项目的过程中我们威力减轻数据库压力我们会在数据库中进行读写分离的操作

      1、首先我们在生成数据源的时候会生成两个数据源，两个数据源的所有字段和主键都一样，唯一不同的两张表的数据库引擎不一样，一张为InnoDB，一张为MyISAM。

      2. 因为引擎为INNODB的数据源的引擎支持事务的处理等高级处理，我们可以让其提供写的功能

      3.因为引擎为MyISAM的数据源强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持 ，我们可以让其提供读的功能

 

※在以往对数据库的操作中，我们通常都是进行单表操作

      

 ==============================李增强sql优化================================

--执行顺序

select t.*,t.lookename aa

from looker t

where aa>='a'

order by aa

--语法错误，where后面的aa不认识，

原因，先执行where，后执行select  

from --》where--》group by   having --》select --》order by

--怎么测试，命一个别名，看他认不认识

--效率问题，我们可以使用sql的执行计划，看出每步的用时

 

--sql的链接查询

select * from emp a,dept b   --交叉查询，结果集就是一个笛卡尔集，左表*右表，另一种写法  a cross join b。交叉查询没有实际使用意义

where a.deptno = b.deptno    --内连接查询，这就是使用比较多的，-- a [inner] join b on a.xx=b.xx

--number[(p,[s])]  number(20,5) []表示参数可选

--左连接查询   a left join b on -----，    select * from emp a,dept b where a.deptno = b.deptno(+)，也是左连接查询的一种写法，左连接+写在右边

--左连接也是使用最大一种，其次就是内连接

--全外连接查询  full join，不可以两边都+号，绝对不可以

--空值，滤空

--1、有空值参与的运算结果都为空

--2、空值不等于空值

--3、可以使用 ==null是吗？不是，绝对不是， 首先sql中，就不能使用==，这是java中语法，sql中使用=，和java中的==一样，sql中的赋值用:=,其次，sql中的判断为空用is [not] null

--4、空值在排序的时候，比数值大的

--5、可以使用nulls last和nulls first来控制空值的前后

select * from emp t  order by comm desc nulls last

--6、我们可以使用nvl函数来滤空  nvl(字段，当字段为空时候的默认值)，不为空是原值

--7、可以使用decode函数来滤空(T-sql)，非标准sql，只有oracle有

--8、case when then来滤空，这个是一个标准sql，在mysql中可以执行

select a.*,

        case age when 2 then '儿童'

                         when 3 then '成年'

                        else '老年'

        end  状态

from looker a

 

--能用>=或者<=的地方就尽量不要使用><,能带=的就尽量带上=号，效率高

sql优化

1、SELECT子句中避免使用*， 尽量应该根据业务需求按字段进行查询

2、尽量多使用COMMIT如对大数据量的分段批量提交释放了资源，减轻了服务器压力

3、在写sql语句的话，尽量保持每次查询的sql语句字段用大写，因为oracle总是先解析      sql语句，把小写的字母转换成大写的再执行

4、用UNION-ALL替换UNION，因为UNION-ALL不会过滤重复数据，所执行效率要快于UNION,并且UNION可以自动排序，而UNION-ALL不会

5、避免在索引列上使用计算和函数,这样索引就不能使用

 

Sql优化精简版：

1.(重点)(必须说) SELECT语句中避免使用*，尽量应该根据业务需求按字段进行查询

举例：如果表中有个字段用的是clob或者是blob这种大数据字段的话，

     他们的查询应该根据业务需要来进行指定字段的查询，切记勿直接用*

 

2.(重点) 删除重复记录(oracle)：

最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID)

FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);

 

3. 用>=替换>

如一个表有100万记录，一个数值型字段A，

      A=0时，有30万条；

      A=1时，有30万条；

      A=2时，有39万条；

      A=3时，有1万记录。

      那么执行 A>2 与 A>=3 的效果就有很大的区别了，因为A>2 时，

      ORACLE会先找出为2的记录索引再进行比较，

      而A>=3时ORACLE则直接找到=3的记录索引。

 

4.(重点)尽量多使用COMMIT

如对大数据量的分段批量提交

 

5. (重点)用NOT EXISTS或（外连接+判断为空）方案 替换NOT IN操作符

    此操作是强列推荐不使用的，因为它不能应用表的索引。

    推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

 

6.(重点 必须说)LIKE操作符(大数据的全文检索使用luncene)(solr)

    因为使用like不当，会导致性能问题，原因是like在左右两边都有

    %的时候，不会使用索引

    如LIKE '%5400%' 这种查询不会引用索引，

    而LIKE 'X5400%' 则会引用范围索引。

 

    一个实际例子：

    查询营业编号 YY_BH LIKE '%5400%' 这个条件会产生全表扫描，

    如果改成     YY_BH LIKE 'X5400%' OR YY_BH LIKE 'B5400%'

    则会利用     YY_BH  的索引进行两个范围的查询，性能肯定大大提高。

 

7.(重点,必须说)避免在索引列上使用计算和函数,这样索引就不能使用

举例:

低效：

SELECT … FROM  DEPT  WHERE SAL * 12 > 25000;

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12;

 

8.(重点 必须说)用UNION-ALL替换UNION，

因为UNION-ALL不会过滤重复数据而且不会自动排序，所执行效率要快于UNION。

 

9. （优化,重点,3个方面a.缓存b.分段批量c.存储过程）减少访问数据库的次数

举例:如果批量删除多条数据，可以用  delete  from tableName where id in (1,2,3)

而不要用多条delete语句进行删除

 

10.（重点 必须说）用TRUNCATE替代DELETE

TRUNCATE不记录日志，DELETE记录日志，所以TRUNCATE要快于DELETE

但是一旦用TRUNCATE进行删除就不能进行恢复,TRUNCATE是删除整张表的数据

不能加where条件。

=================================================================

mysql,sqlserver中如果id为自增类型，那么如果用TRUNCATE删除，则id字段再插入数据时从1开始，如果delete删除的话，则从删除之前的id的值继续增长。