SQL性能优化

show [session|global] status : 查看服务器状态

show global status like 'Com_' : 查看各种语句的执行次数

开启慢查询: 在 MySQL 配置文件（/etc/my.cnf）配置:

#开启MySQL慢日志查询开关 
slow_query_log=1 
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 
long_query_time=2

查看 SQL 的执行耗时:

#查看每一条SQL的耗时基本情况 
show profiles; 

#查看指定query_id的SQL语句各个阶段的耗时情况 
show profile for query query_id; 

#查看指定query_id的SQL语句CPU的使用情况 
show profile cpu for query query_id;

EXPLAIN 语句

EXPLAIN: 获取如何执行 SELECT 语句的信息

#直接在select语句之前加上关键字 
explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

EXPLAIN 执行计划各字段含义：

• Id：查询的序列号，表示查询中执行 select 子句或者操作表的顺序 ( id 相同，执行顺序从上到下；id 不同，值越大，越先执行)

• select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE 之后包含了子查询）等

• type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all

• possible_key：显示可能应用在这张表上的索引，一个或多个

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

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

• rows：MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的

• filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

索引使用规则

最左前缀法制:
指的是查询从索引的最左列开始,不跳过索引中的列,如果跳跃某一列,后面的字段索引会失效

和查询时放的列顺序无关,存在即可,比如:

尽量使用 >= 而不是 >, 这样索引就不会失效

插入数据优化

• 插入数据时选择批量插入:

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

数据量大时可以选择 load 指令:

#客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

• 手动提交事务:

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

主键设计原则:

• 满足业务的情况下尽量降低 主键长度
• 插入数据时，尽量选择顺序插入，不要乱序, 性能更好, 选择使用 AUTO_INCREMENT 自增主键
• 尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号
• 业务操作时，不要对主键修改

order by 优化

• Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序

• Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则

• 尽量使用覆盖索引

• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）

• 如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size (默认 256k)

group by优化

分组操作时,索引的使用要满足最左前缀法制,这样的效率比较高

limit优化

通过覆盖索引加子查询形式进行优化

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count优化

• count () 对于返回的结果集，一行行地判断，如果参数不是 NULL，值加 1，否则不加，最后返回值
• 用法: count (*)、count (主键)、count (字段)、count (1)

效率 : count (字段) < count (主键) < count (1) ≈ count (*), 尽量使用 count(*)

update优化

执行更新时,更新的条件一定要有索引,而且这个索引不能失效,不然会从行锁升级为表锁,并发性能会降低