(仅仅是自己的个人笔记)
优化数据库, 暂时记录插入数据的优化以及查询的优化
查询:
一. 定位: 首先定位需要优化的查询语句.
开启慢查询: 1.输入:show variables like '%slow%';
必须是log_slow_queries(开启慢查询)跟slow_query_log(记录慢查询)都是为开启状态(on)
如果是off则 用语句 set global slow_query_log='ON'; set global log_slow_queries='ON';
slow_launch_time 则为查询语句超过该时间的话, 就会把语句记录到日志中. 最后一项是日志保存路径.
二. 优化手段以下五项:
-
- 创建索引:创建合适的索引，我们就可以现在索引中查询，查询到以后直接找对应的记录。
- 分表 ：当一张表的数据比较多或者一张表的某些字段的值比较多并且很少使用时，采用水平分表和垂直分表来优化
- 读写分离：当一台服务器不能满足需求时，采用读写分离的方式进行集群。
- 缓存:使用redis来进行缓存
- 一些常用优化技巧
一般来说, 优化整个数据库, 前面还需要进行遵循三范式的设计规则, 然后选择引擎等. 默认是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: 字段内容不是频繁变化.
具体技巧：
-
对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引。
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表数据一般达到百万级别，查询效率会很低，容易造成表锁，甚至堆积很多连接，直接挂掉；水平分表能够很大程度较少这些压力。
按行数据进行分表。
如果一张表中某个字段值非常多(长文本、二进制等)，而且只有在很少的情况下会查询。这时候就可以把字段多个单独放到一个表，通过外键关联起来。
考试详情，一般我们只关注分数，不关注详情。
水平分表策略：
1.按时间分表
这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录、微信消息记录等，这种数据很少有用户会查询几个月前的数据，如就可以按月分表。
2.按区间范围分表
一般在有严格的自增id需求上，如按照user_id水平分表：
table_1 user_id从1~100w
table_2 user_id从101~200w
table_3 user_id从201~300w
3.hash分表*****
通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表。
数据库优化之读写分离
一台数据库支持的最大并发连接数是有限的，如果用户并发访问太多。一台服务器满足不要要求是就可以集群处理。Mysql的集群处理技术最常用的就是读写分离。
主从同步
数据库最终会把数据持久化到磁盘，如果集群必须确保每个数据库服务器的数据是一直的。能改变数据库数据的操作都往主数据库去写，而其他的数据库从主数据库上同步数据。
读写分离
使用负载均衡来实现写的操作都往主数据去，而读的操作往从服务器去。
数据库优化之缓存
在持久层(dao)和数据库(db)之间添加一个缓存层，如果用户访问的数据已经缓存起来时，在用户访问时直接从缓存中获取，不用访问数据库。而缓存是在操作内存级，访问速度快。
作用：减少数据库服务器压力，减少访问时间。
Java中常用的缓存有，
1、hibernate的二级缓存。该缓存不能完成分布式缓存。
- 可以使用redis(memcahe等)来作为中央缓存。
对缓存的数据进行集中处理
语句优化小技巧
DDL优化:
1 、通过禁用索引来提供导入数据性能 。 这个操作主要针对有数据库的表，追加数据
//去除键
alter table test3 DISABLE keys;
//批量插入数据
insert into test3 select * from test;
//恢复键
alter table test3 ENABLE keys;
2、 关闭唯一校验
set unique_checks=0 关闭
set unique_checks=1 开启
3、修改事务提交方式(导入)（变多次提交为一次）
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)
DQL优化
Order by优化
1、多用索引排序
- 普通结果排序（非索引排序）Filesort
group by优化
是使用order by null,取消默认排序
子查询优化
在客户列表找到不在支付列表的客户
#在客户列表找到不在“支付列表”的客户 , 查询没买过东西的客户
explain
select * from customer where customer_id not in (select DISTINCT customer_id from payment); #子查询 -- 这种是基于func外链
explain
select * from customer c left join payment p on(c.customer_id=p.customer_id) where p.customer_id is null -- 这种是基于“索引”外链
Or优化
在两个独立索引上使用or的性能优于
1、 or两边都是用索引字段做判断，性能好！！
2、 or两边，有一边不用，性能差
3、 如果employee表的name和email这两列是一个复合索引，但是如果是 :name='A' OR email='B' 这种方式，不会用到索引！
limit优化
select film_id,description from film order by title limit 50,5;
select a.film_id,a.description from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id=b.film_id
扫一扫
1447
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
