MySQL 优化

从设计上优化
从查询上优化
从索引上优化
从存储上优化

-- 查看当前会话SQL执行类型的统计信息
show session status like 'Com_______';

-- 查看全局(上次启动至今)执行类型的统计信息
show global status like 'Com_______';

-- 查看针对Innodb引擎的统计结果
show status like 'Innodb_rows_%’;       

-- 查看慢日志配置信息 
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;

-- 该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
show processlist;

• id列，用户登录mysql时，系统分配的”connection_id”，可以使用函数connection_id()查看
• user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
• host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
• db列，显示这个进程目前连接的是哪个数据库
• command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等time列，显示这个状态持续的时间，单位是秒
• state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
• info列，显示这个sql语句，是判断问题语句的一个重要依据

explain分析执行计划

-- explain分析执行计划
explain sql

字段	含义
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	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在FROM列表中包含的子查询，被标记为DERIVED(衍生) MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION;若UNION包含在FROM子句的子查询中，外层SELECT将被标记为: DERIVED
UNION RESULT	从UNION表获取结果的SELECT

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	系统表，少量数据，往往不需要进行磁盘lO; 如果是5.7及以上版本的话就不是system了，而是all，即使只有
const	命中主键(primary key)或者唯一(unique)索引; 被连接的部分是一个常量(const)值;
eq_ref	对于前表的每一行，后表只有一行被扫描。(1) join查询; (2)命中主键(primary key)或者非空唯一(unique not null)索引; (3)等值连接;
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。对于前表的每一行(row)，后表可能有多于一行的数据被扫描。
range	只检索给定返回的行，使用一个索引来选择行。where之后出现 between,< , > , in等操作。
index	需要扫描索引上的全部数据。
all	全表扫描，此时id上无索引

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

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”,效率低。
using temporary	需要建立临时表(temporary table)来暂存中间结果，常见于order by和group by;效率低
using index	SQL所需要返回的所有列数据均在一棵索引树上，避免访问表的数据行，效率不错。

show profiles了解时间耗费

-- show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。。
select @@have_profiling; 
set profiling=1; -- 开启profiling 开关； 

-- 通过show  profile for  query  query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间
show profile for query 8;

-- 在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间  ：
show profile cpu for query 133;

字段	含义
Status	sql语句执行的状态
Duration	sql执行过程中每一个步骤的耗时
CPU_user	当前用户占有的cpu
cPU_system	系统占有的cpu

trace分析优化器执行计划

-- 打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace="enabled=on",end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;
-- 执行SQL语句 ：

-- 最后， 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：
select * from information_schema.optimizer_trace\G; 	-- 终端执行

使用索引优化

大批量插入数据

-- 1、首先，检查一个全局系统变量 'local_infile' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like 'local_infile';
 
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;
 
-- 3、加载数据 
/*
脚本文件介绍 :
    sql1.log  ----> 主键有序
    sql2.log  ----> 主键无序
*/
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

关闭唯一性校验

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

-- 关闭唯一性校验
SET UNIQUE_CHECKS=0;
 
truncate table tb_user;
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
 
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;

优化order by语句

第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

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

经历

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

优化limit查询

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

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

SQL注入