关于sql优化问题

定位：查找、定位慢查询

优化手段：

1. 创建合适的索引，我们就可以现在索引中查询，查询到以后直接找对应的记录。
2. 分表   ：当一张表的数据比较多或者一张表的某些字段的值比较多并且很少使用时，采用水平分表和垂直分表来优化

3. 读写分离：当一台服务器不能满足需求时，采用读写分离的方式进行集群

4. 缓存:使用redis来进行缓存

5. 一些常用优化技巧

查找慢查询并定位慢查询（mysql）

1. 开启慢查询sql日志存储，配置my.ini文件(永久)或修改数据库数据(临时)
2. 修改慢查询时间：set global long_query_time=1
3. 找出日志中的慢查询，使用explain来详细分析慢查询

 关于存储引擎

MyISAM存储引擎

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

INNODB存储引擎:

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

问 MyISAM 和 INNODB的区别(主要)

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

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

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

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

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

索引

索引（Index）是帮助DBMS高效获取数据的数据结构。扩展名为:DCX

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

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

唯一索引:除了不能有重复的记录外，其它和普通索引一样(用户名、用户身份证、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' 会使用到索引

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

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

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

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

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

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

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

1. 对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引。
2. 对于使用like的查询，查询如果是’%aaa’不会使用到索引而‘aaa%’会使用到索引。
3. 如果条件中有or，有条件没有使用索引,即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段,都必须单独使用时能使用索引.、
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
5. 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

   表里面只有一条记录

数据库优化之读写分离(其他博客会详细介绍)

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、多用索引排序

1. 普通结果排序（非索引排序）Filesort

   group by优化

      是使用order by null,取消默认排序

   子查询优化

在客户列表找到不在支付列表的客户

#在客户列表找到不在“支付列表”的客户 , 查询没买过东西的客户

#子查询      -- 这种是基于func外链
explain

select * from customer where customer_id not in (select DISTINCT customer_id from payment); 

-- 这种是基于“索引”外链
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

如何批量添加几百万的数据

1、变多次提交为一次提交

2、使用批量操作

可以使用存储过程

参考：视频学习