MySQL优化（废话少）

---

查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。

--下面的命令显示了当前 session 中所有统计参数的值
show session status like 'Com_______';  -- 查看当前会话统计结果
show global  status  like 'Com_______';  -- 查看自数据库上次启动至今统计结果
 
show status like 'Innodb_rows_%';       -- 查看针对Innodb引擎的统计结果

---

定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

定位低效率执行SQL-慢查询日志

通过慢查询日志定位那些执行效率较低的 SQL 语句。

-- 查看慢日志配置信息 
show variables like '%slow_query_log%'; 

-- 开启慢日志查询 
set global slow_query_log=1; 

-- 查看慢日志记录SQL的最低阈值时间 
show variables like 'long_query_time%'; 

-- 修改慢日志记录SQL的最低阈值时间 
set global long_query_time=4;

定位低效率执行SQL-show processlist

show processlist;

---

explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

explain 查询语句;

Explain 之 id

• id 相同表示加载表的顺序是从上到下。
• id 不同id值越大，优先级越高，越先被执行。
• id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

Explain 之 select_type

Explain 之 type

结果值从最好到最坏以此是：system > const > eq_ref > ref > range > index > ALL

Explain 之 table

显示这一步所访问数据库中表名称有时不是真实的表名字，可能是简称。

Explain 之 rows

扫描行的数量。

Explain 之 key

• possible_keys : 显示可能应用在这张表的索引， 一个或多个。

• key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

• key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
  

Explain之 extra

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

---

索引优化

避免索引失效-全值匹配

查询字段和建立索引字段相同（建立索引的字段和查询时查的字段相同），可以不分顺序。
例如以下sql中，查询时查的就是建索引的那几个字段这样效率最高。

-- 创建组合索引 
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

避免索引失效-最左前缀法则

如果索引了多列，要遵守最左前缀法则。
指的是查询从索引的最左前列开始，并且不跳过索引中的列。
如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
简单来说就是从左到右匹配；

-- 创建组合索引 
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

-- 最左前缀法则
explain select * from tb_seller where name='小米科技'; -- 403
explain select * from tb_seller where name='小米科技' and status='1'; -- 410
explain select * from tb_seller where  status='1' and name='小米科技'; -- 410

-- 违反最左前缀法则 ， 索引失效：
explain select * from tb_seller where status='1'; -- nulll
 
-- 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
explain select * from tb_seller where name='小米科技'  and address='北京市'; -- 403

避免索引失效-范围查询右边列索引失效

-- 创建组合索引 
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

-- 范围查询右边的列，不能使用索引 。 （address列索引失效）
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市’;

避免索引失效-索引列上运算会使索引失效

-- 创建组合索引 
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

-- 不要在索引列上进行运算操作， 索引将失效。 
explain select * from tb_seller where substring(name,3,2)='科技’ 

避免索引失效-字符串不加单引号索引失效

-- 创建组合索引 
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

-- 字符串不加单引号，造成索引失效。 （status列是varchar）
explain select * from tb_seller where name='小米科技' and status = 1 ;

避免索引失效-避免使用 select *

避免索引失效-or连接的条件索引失效

避免索引失效-模糊查询以%开头索引失效，走全表扫描

可以使用索引列查询避免

explain select name from tb_seller where name like '%科技%';

如果MySQL评估使用索引比全表更慢，则不使用索引

优化器自动优化，由数据本身决定

is NULL ， is NOT NULL 有时有效，有时索引失效。

in和not in

• 普通索引中in使用索引，not in不使用索引
• 主键索引中in和not in都使用索引

单列索引和复合索引，尽量走复合索引

-- 创建组合索引 
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

等价于：
name
name+status
name+status+address

如果一张表有多个单列索引，即使where中都使用了这些索引列，则只有一个最优索引（由优化器决定）生效

SQL优化

大批量插入数据-主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

大批量插入数据-关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

insert优化

• 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

-- 原始方式为：
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 values(1,'Tom'),(2,'Cat')，(3,'Jerry');

• 在事务中进行数据插入（手动事务，减少事务开启、提交频率）。

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

• 数据有序插入

-- 数据有序插入
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');
 
-- 优化后
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 values(4,'Tim');
insert into tb_test values(5,'Rose');

order by优化

• order by后边的多个排序字段要求尽量排序方式相同
• order by后边的多个排序顺序尽量和组合索引字段顺序一致
• 可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

优化子查询

多表查询效率比子查询高；
连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

limit查询优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 900000,10 ，此时需要MySQL排序前900010 记录，仅仅返回900000 - 900010 的记录，其他记录丢弃，查询排序的代价非常大 。

• 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
• 当用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

-- 在索引树种找第900000条数据是很快的
select * from tb_user where id>900000 limit 10;