1.5.sql的优化
1.5.1.查看sql的执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。
--下面的命令显示了当前 session 中所有统计参数的值
show session status like 'Com_______'; -- 查看当前会话统计结果
show global status like 'Com_______'; -- 查看自数据库上次启动至今统计结果
show status like 'Innodb_rows_%’; -- 查看针对Innodb引擎的统计结果
1.5.2.执行效率比较低的sql的定位
可以通过以下两种方式：
-
慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句。
-
show processlist：该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 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;
show processlist
show processlist;
各个参数的意义：
1.5.3.explain分析执行计划
通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
其中各个 参数的含义为：
对id的解释
对select_type的解释
对type的解释
各个type的展示
- NULL
- system
-
const(查询唯一索引时)
-
eq_ref（左表有主键索引 且左表每一行和右表刚好匹配）
举个例子：我们创建两个表 user2 和 user2_ex
结果所示：当user2 和 user_ex的行恰好一一对应的时候查找模式为all（因为有 * ）和eq_ref
而添加一个重复元素后，就不能使用eq_ref了，效率降低。
- ref （左表为普通索引时）
ref允许左表匹配右表多行。
- range（范围查询）
- index （打印索引列）
- all （普通的全表查询）
重点是这个比较的大于顺序，一定要记牢，优化时候尽量把它维持在index等级之上。
其他指标（key 等）
]
1.5.4.使用show profile分析sql
查找步骤 ： 使用查找语句> show profiles展示>找到你要分析语句的序号> show profile for 你要找的语句
可以如上图最后一条 查找语句的cpu占用。
1.5.5.mysql的具体优化策略
1.依靠索引优化
创建组合索引，提高查找速度。要时刻遵循最左匹配法则，不能跳过。 要注意，实际查询值越高，就使用了越多的索引，速度越高。
- 如果跳过一个索引，违反最左原则，将会失效
- 范围查询，运算操作和单引号失去会使索引失去效果
- 避免使用* 因为查询除索引以外的数据需要从磁盘读取，效率较低
对比
同样的道理password没加索引，效率下降。
extra各个指数的指代意义
- or会引起索引失效
- 模糊索引
- 即使有索引，不用索引的情况
当我们一个列重复的数据较多时，我们查找重复的数据时，比如在下图找地址在北京的数据。数据库会自动为我们全表查询，此时全表查询效率较高。
而查找西安市的时候，会使用索引查询。
not in和in跟上面 道理一样 多就不用索引。
-
尽量使用复合索引
-
当一个查询条件包括三个单列索引时，取最优的生效
- 查询时，联表查询优于嵌套子查询
2.优化order by
下面的显示来自extra
尽量顺序一致，排序方式相同
3.优化Filesort
当我们迫不得已使用Filesort 时
通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法：
1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。
4.优化limit
这么写
或者
5.大批插入数据的优化
当我们要用.log文件导入大量数据，怎么做才更有效率呢？
插入方法
-- 1、首先，检查一个全局系统变量 'local_infile' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like 'local_infile';
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;
-- 3、加载数据
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
- 按照主键列排好顺序
-
关闭唯一检索
mysql在添加数据时会检测唯一索引的数据是否重复。如果我们事先已经检查好，就可以关闭唯一检索，提高效率。
-- 关闭唯一性校验 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;
6.优化insert
三个方向
-
有序插入
-- 数据有序插入 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'); -
尽量集中插入
-- 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的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;