数据库优化介绍

深碍√TFBOYSˉ_ 2022-02-12 10:45 175阅读 0赞

**数据库优化方面的**  
定位: 查找 定位慢查询.  
优化方式：  
创建索引 ：创建合适的索引，我们就可以在索引中查询，查询到以后就直接找对应的记录、  
分表：一张表的数据比较多或者是一张表的某些字段值比较多并且很少使用时，采用水平分表和垂直分表来优化、  
读写分离：当一台服务器不能满足需求时，采用读写分离的方式进行集群、  
缓存：使用Redis来进行缓存  
一些常用的sql优化技巧  
**查找并定位慢查询**  
在项目自验项目转测试之前，在启动MySQL数据库时开启慢查询，并且把执行慢的语句写到日志中，在运行一定的时间后，通过查看日志找到慢查询的语句。  
使用explain慢查询语句，来详细分析语句的问题  
例如 explain select \* from dual where id=3;

**数据库表的优化需要遵循范式**  
第一范式：表的列具有原子性，不可再分解  
第二范式：表中的记录是唯一的，通常我们设计一个主键来实现  
第三范式：表中不要有冗余数据，表的信息，如果能被推导出来，就不应该单独的设计一个字段来存放（外键）；

反三范式：没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据，具体做法：在概念数据模型设计时遵守第三范式。降低范式标准的工作放到物理数据模型设计时考虑，降低范式就是增加字段，允许冗余，订单和订单项。相册浏览次数和照片的浏览次数。

**选择合适的存储引擎**  
在开发中，我们经常使用的存储引擎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支持外键(通常不设置外键，通常是在程序中保证数据的一致)  
    **创建合适的索引**  
    索引：是帮助DBMS高效获取数据的数据结构  
    分类:  
    普通索引：允许重复的值出现。  
    唯一索引：除了不能重复的记录外，其它和普通索引一样（用户名。用户名身份证。email，tel）  
    主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会给该列创建索引，这就是主键索引，唯一没有null值  
    全文索引：用来对表中的文本域（char,varchar text)进行索引 全文索引针对myisam  
    explain select \* from articles where match(title,body) against(‘database’);【会使用全文索引】  
    **索引使用的小技巧**  
    索引弊端：占用磁盘空间，对dml（插入、修改、删除）操作有影响，变慢  
    使用场景：  
    肯定在where条件经常使用，如果不做查询就没有意义  
    该字段的内容不是唯一的几个值  
    字段内容不是频繁变化。  
    具体：  
    对于创建的多索引（复合索引）不是使用的第一部分就不会使用索引  
    alert table dept addc index my\_ind(dname,loc);//dname 左边的列，loc就是右边的列  
    explain select \* from dept where dname=‘aaa’\\会使用到索引  
    explain select \* from dept where loc=‘aaa’\\不会使用索引  
    对于使用like的查询，查询如果是’%aaa’ 不会使用索引而’aaa%'会使用到索引。  
    explain select \* from dept where dname like ‘%aaa’不能使用索引  
    explain select \* from dept where dname like ‘aaa%’ 使用索引  
    所以在like查询时，关键字的最前面不能使用%或者是\_这样的字符，如果一定要前面有变化的值，则考虑使用全文索引->sphinx  
    如果条件中有or，有条件没有使用索引，即使其中有条件带索引也不会使用，换言之就是要求使用的所有字段都必须单独使用时能使用索引。  
    如果列类型是字符串，那一定要在条件中将数据使用引号引起来，否则不使用索引。  
    explain select \* from dept where dname=‘111’  
    explain select \* from dept where dname=111;(数值自动转换字符串)  
    也就是，如果列是字符串类型无论是不是字符串数字就一定要用’'把它括起来  
    如果MySQL估计使用全表扫描要比使用索引快，则不使用索引。  
    表里只有一条记录。  
    **数据库优化的分表**  
    水平分表（按行）和垂直分表（案列）  
    MySQL表数据一般达到百万级别，查询效率会很低，容易造成表锁，甚至堆积很多连接，直接挂掉，水平分表能够很大程度减少这些压力  
    按行数据进行分表  
    如果一张表中某个字段非常多（长文本，二进制等）而且只有在很少的情况下会查询这时候就可以把字段多个单独放到一个表，通过外键关联起来

水平分表策略  
按时间分表  
这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录。微信消息记录等，这种数据很少用户会查询几个月前的数据，可以按月分表  
按区间范围分表  
一般在有严格的自增id需求上，如按照user\_id水平分表  
table\_1 user\_id 从1~100w

table\_2 user\_id 从101~200w  
table\_3 user\_id 从201~300w  
**hash分表  
通过一个原始目标的id或者名称通过一定的hash算法计算数据库存储表的表名，然后访问相应的表。**

**数据库优化的读写分离**  
一台数据库支持的最大并发连接数是有限的，如果用户并发访问太多，一台服务器满足不了要求是可以集群处理的。MySQL的集群处理技术最常用的就是读写分离。  
主从同步  
数据库最终会把数据持久化到磁盘，如果集群必须确保每个数据库服务器的数据是一致的  
能改变数据库数据的操作，都写在主数据库。而其他的数据库从主数据库上同步数据。  
读写分离  
使用负载均衡来实现写的操作都往主数据库去，而读的操作往从服务器去。

**数据库优化之缓存**  
在持久层dao和数据库db之间添加一层缓存层，如果用户访问的数据已经缓存起来时，在用户访问时直接从缓存中获取，不访问数据库，而缓存是在操作内存级，访问速度快。  
作用：减少数据库服务器压力，减少访问时间  
Java中常用的缓存有，hibernate的二级缓存，该缓存不能完成分布式缓存。  
可以使用Redis、memcahe等来作为中央缓存  
数据库语句优化  
DDL优化  
通过禁用索引来提供导入数据性能，这个操作主要是针对有数据库的表，追加数据  
//去除键  
alter table test3 DISABLE keys;  
//批量插入数据  
insert into test3 select \* from test;  
//恢复键  
alter table test3 ENABLE keys;  
关闭唯一校验  
set unique\_checks=0关闭  
set unique\_checks=1开启  
修改事物提交方式（导入） 变多次提交为一次  
set autocommit=0关闭  
//批量插入  
set autocommit=1开启  
DML优化 变多次提交为一次  
insert into test values(1,2);  
insert into test values(1,3);  
insert into test values(1,4);  
//合并多条为一条  
insert into test values((1,2),(1,3),(1,4))  
在mybatis中需要使用循环来完成。  
DQL优化  
order by 优化  
1多用索引排序 2 普通结果排序 非索引排序 filesort  
group by 优化  
是使用order by null,取消默认排序  
子查询优化  
在客户列表找到不在支付列表的客户  
在客户列表找到不在 支付列表的客户 查询没买过东西的客户  
explain select \* from customer where customer\_id not in(select DISTINCT customer\_id from payment);子查询 这是基于外键查询  
explain select \* from cusyomer c left join payment p on(c.customer\_id = p.customer\_id) where p.customer\_id is null; 这是基于索引外链  
or优化  
在两个独立索引上使用or的性能优化  
or两边都是索引字段做判断，性能好  
or两边有一边不用，性能差  
如果employee表的name和email这两列是一个复合索引，但是如果是：name=‘A’ or email='B’这种方式不会用到索引  
limit优化  
select file\_id,desc from file order by title limit 50,5;  
select a.file\_id,a.desc from file a inner join(select file\_id from file order by title limit 50,5) b on a.file\_id = b.file\_id  
**JDBC批量插入百万条数据怎么实现**  
便多次提交为一次  
使用批量操作  
[https://www.cnblogs.com/fnz0/p/5713102.html][https_www.cnblogs.com_fnz0_p_5713102.html][百万数据插入参考链接][https_www.cnblogs.com_fnz0_p_5713102.html]

[https_www.cnblogs.com_fnz0_p_5713102.html]: https://www.cnblogs.com/fnz0/p/5713102.html