mysql-02-SQL数据库层面优化

1. SQL优化-数据库层面优化

1.1.1 查看SQL执行频率

通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

-- 执行此sql查看当前数据库连接对应状态信息
SHOW STATUS LIKE 'Com___';    

-- 加个global就是查询全局信息
SHOW  global STATUS LIKE 'Com___';   

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。

查询Innodb操作的数量

show status like 'Innodb_rows_%'; 

Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

1.1 explain分析sql执行计划

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref。

查询SQL语句的执行计划

 explain  select * from tb_item where id = 1; 

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------>all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

1.1.1 explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种

1） id 相同表示加载表的顺序是从上到下。

2） id 不同id值越大，优先级越高，越先被执行。

3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越

大，优先级越高，越先执行。

1.1.2 explain 之 select_type

表示 SELECT 的类型，常见的取值，如下表所示:

1.1.3 explain 之 table

展示这一行的数据是关于哪一张表的

1.1.4 explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

结果值从最好到最坏以此是：一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL  system > const > eq_ref > ref > range > index > ALL 

1.1.5 explain 之 key

possible_keys : 显示可能应用在这张表的索引， 一个或多个。 key ： 实际使用的索引， 如果为NULL， 则没有使用索引。 key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度， 在不损失精确性的前提下， 长度越短越好 。

1.1.6 explain 之 rows

扫描行的数量

1.1.7 explain 之 extra

其他的额外的执行计划信息，在该列展示 。

1.2 常见的sql语句优化

insert插入语句优化

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句.只有一次io的操作

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry'); 

优化后:  一次插入多行数据 
insert into tb_test (id,name) values(1,'Tom'),(2,'Cat')，(3,'Jerry');  

优化group by 语句

GROUP BY 实际上同样会进行排序操作所以group by 分组后加上不排序 order by null;

优化OR条件

对于包含 OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索

引(因为复合索引会失效)； 如果没有索引，则应该考虑增加索引.

如sql语句 建议使用union去代替or , union是并集,or是或者
select * from emp where id = 1 or age = 30;  

优化分页Limit查询

mysql的方言分页limit ,比如说我们查询limit 10000,10,返回仅仅是第10000条-10010条的记录,查询的代价非常大.

优化后的sql语句: 这样直接从10000条数据截断,从10001条数据开始查询的10条数据.只能用于主键自增

//如果只给一个参数,表示返回最大的记录行数, 从10000条开始,检索前10条记录

select * from 表名 where id>10000 limit 10;   
如下,使用了主键id作为索引,效率更高
SELECT * FROM mkt_business_info order by id limit 10000,10 ; 

优化嵌套查询

有些情况下，子查询是可以被更高效的连接（JOIN）替代。

explain  select * from t_user where id in (select user_id from user_role );   
优化后使用join,如下
explain  select * from t_user u , user_role ur where u.id = ur.user_id;   
select * from t_user u inner join user_role ur on u.id = ur.user_id;  

1.3 避免索引失效

1. 全值匹配 ，对索引中所有列都指定具体值。该情况索引生效,执行效率高, 即where条件 如id=1,制定具体值.

2. 最左前缀法则: 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。该情况走索引,查询效率高.

如创建的三个索引,name,status,address

where 后面一定要第一个是name ,后面按照顺序来,有或者没有都可以,索引都是生效的
where name='q'   ...   address='w' //符合最左匹配,跳过中间的status 这样索引也是有效的 

3. 范围查询右边的列,不能使用索引. //如范围查询status>1

   执行的sql语句有范围查询 如 where status>1 右边的列不能使用索引

4. 不能在索引列进行运算操作

5. 字符串不加单引号,会造成索引失效. 字符串必须加单引号,mysql的优化器会对其自动的进行类型转换,造成索引失效.

6. 尽量使用覆盖索引,避免使用select *

7. 用or分隔开的条件,如果or前面的列有索引,而后面的列没有索引,那么涉及的索引都不会被用到.

8. 以%开头的like模糊查询,索引会失效. // 如果是like后面带%是不会索引失效的,尾部模糊匹配不会失效.

SELECT * FROM mkt_business_info like '模糊查询%' 

9. 如果mysql评估使用索引比全表更慢,则不适用索引.

10. 使用is null 或者is not null ,索引有时候会失效,where子句中避免使用is null 或者is not null

11. in走索引,not in不走索引:索引失效.

12. 单列索引和复合索引:尽量多使用多列索引,少使用单列索引. 因为复合索引包括单列索引

1.4 使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说:就是在SQL语句中加入一些人为的提示来告诉数据库我们是用哪些索引或者不使用哪些索引,来达到优化操作的目的。

4.1 USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

create index idx_seller_name on tb_seller(name);  -- 创建单列索引
create index idx_seller_name_age_sex on tb_seller(name,age,sex);   -- 创建复合索引

我们人为的干预,让其走复合索引
USE INDEX(指定的索引名称)
select * from tb_seller use index(idx_seller_name_age_sex) where name = '小米科技'; 

4.2 IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

忽略单列索引,就会使用复合索引
explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技'; 

4.3 FORCE INDEX

强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。

如果数据库认为全表扫描比走索引还快,就不会走索引了
create index idx_seller_address on tb_seller(address);  

-- 强制其使用索引address, 就会走我们创建的索引
select * from tb_seller force  index(address) where address = '北京市';