数据库优化方面

数据库优化手段：

1. 创建索引:创建合适的索引，我们就可以现在索引中查询，查询到以后直接找对应的记录。
2. 分表 ：当一张表的数据比较多或者一张表的某些字段的值比较多并且很少使用时，采用水平分表和垂直分表来优化
3. 读写分离：当一台服务器不能满足需求时，采用读写分离的方式进行集群。
4. 添加缓存机制：使用redis来进行缓存

 

说说对SQL语句优化有哪些方法？（选择几条）
1、Where 子句中：where 表之间的连接必须写在其他 Where 条件之前，那些可 以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING 最后。
2、用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。
3、避免在索引列上使用计算
4、避免在索引列上使用 IS NULL 和 IS NOT NULL
5、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉 及的列上建立索引。
6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃 使用索引而进行全表扫描
7、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用 索引而进行全表扫描

 

查找慢查询并定位慢查询？

       在项目自验项目转测试之前，在启动mysql数据库时开启慢查询，并且把执行慢的语句写到日志中，在运行一定时间后。通过查看日志找到慢查询语句

要找出项目中的慢Sql时：

1.  关闭数据库服务器(关闭服务)
2.  把慢查询记录到日志中
3.  设置慢查询时间
4.  找出日志中的慢查询SQL

使用explain 慢查询语句，来详细分析语句的问题.

 

选择合适的存储引擎

在开发中，我们经常使用的存储引擎 myisam / innodb/ memory

MyISAM存储引擎

如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. 比如 bbs 中的 发帖表，回复表.

INNODB存储引擎:

对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

Memory 存储

我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.

问 MyISAM 和 INNODB的区别(主要)

1. 事务安全 myisam不支持事务而innodb支持

2. 查询和添加速度 myisam不用支持事务就不用考虑同步锁，查找和添加和添加的速度快

3. 支持全文索引 myisam支持innodb不支持

4. 锁机制 myisam支持表锁而innodb支持行锁(事务)

5. 外键 MyISAM 不支持外键， INNODB支持外键. (通常不设置外键，通常是在程序中保证数据的一致)

 

数据库优化之创建合适的索引?

索引（Index）是帮助DBMS高效获取数据的数据结构。

分类：普通索引/唯一索引/主键索引/全文索引

普通索引:允许重复的值出现

唯一索引:除了不能有重复的记录外，其它和普通索引一样(用户名、用户身份证、email,tel)

主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引.唯一且没有null值

全文索引:用来对表中的文本域(char，varchar，text)进行索引， 全文索引针对MyIsam

explain select * from articles where match(title,body) against(‘database’);【会使用全文索引】

 

索引使用小技巧？

索引弊端：

1.占用磁盘空间。

2.对dml(插入、修改、删除)操作有影响，变慢。

使用场景：

a: 肯定在where条件经常使用,如果不做查询就没有意义

b: 该字段的内容不是唯一的几个值(sex)

c: 字段内容不是频繁变化.

具体技巧：

1.对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引。

alter table dept add index my_ind (dname,loc); // dname 左边的列,loc就是右边的列

explain select * from dept where dname='aaa'\G 会使用到索引

explain select * from dept where loc='aaa'\G 就不会使用到索引

2.对于使用like的查询，查询如果是’%aaa’不会使用到索引而‘aaa%’会使用到索引。

   explain select * from dept where dname like '%aaa'\G不能使用索引

   explain select * from dept where dname like 'aaa%'\G使用索引.

所以在like查询时，‘关键字’的最前面不能使用 % 或者 _这样的字符.，如果一定要前面有变化的值，则考虑使用 全文索引->sphinx.

3.如果条件中有or，有条件没有使用索引,即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段,都必须单独使用时能使用索引.

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。

expain select * from dept where dname=’111’;

expain select * from dept where dname=111;（数值自动转字符串）

expain select * from dept where dname=qqq;报错

也就是，如果列是字符串类型，无论是不是字符串数字就一定要用 ‘’ 把它包括起来.

5.如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

   表里面只有一条记录

 

 数据库优化之分表

分表分为水平(按行)分表和垂直(按列)分表。

根据经验，Mysql表数据一般达到百万级别，查询效率会很低，容易造成表锁，甚至堆积很多连接，直接挂掉；水平分表能够很大程度较少这些压力。这时要按行数据进行分表。

如果一张表中某个字段值非常多(长文本、二进制等)，而且只有在很少的情况下会查询。这时候就可以把字段多个单独放到一个表，通过外键关联起来。这时要按列数据进行分表。

 

数据库优化之读写分离

什么时候使用?     

       一台数据库支持的最大并发连接数是有限的，如果用户并发访问太多。一台服务器满足不要要求是就可以集群处理。Mysql的集群处理技术最常用的就是读写分离。

主从同步

        数据库最终会把数据持久化到磁盘，如果集群必须确保每个数据库服务器的数据是一直的。能改变数据库数据的操作都往主数据库去写，而其他的数据库从主数据库上同步数据。

读写分离

      使用负载均衡来实现写的操作都往主数据去，而读的操作往从服务器去。

 

数据库优化之缓存

       在持久层(dao)和数据库(db)之间添加一个缓存层，如果用户访问的数据已经缓存起来时，在用户访问时直接从缓存中获取，不用访问数据库。而缓存是在操作内存级，访问速度快。

作用：减少数据库服务器压力，减少访问时间。

Java中常用的缓存有，

   1、hibernate的二级缓存。该缓存不能完成分布式缓存。

   2、可以使用redis(memcahe等)来作为中央缓存。

        对缓存的数据进行集中处理

 

jdbc批量插入几百万数据怎么实现？

1、变多次提交为一次

2、使用批量操作

 

 

MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

1、设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。
2、选择合适的表字段数据类型和存储引擎，适当的添加索引。
3、MySQL 库主从读写分离。
4、找规律分表，减少单表中的数据量提高查询速度。
5、添加缓存机制，比如 memcached，apc 等。
6、不经常改动的页面，生成静态页面。
7、书写高效率的 SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.