Mysql——高级——SQL优化（四）

sql性能下降的原因

（1）查询语句写得差

（2）索引失效：没有创建，或者创建了没有用上

（3）关联查询太多join（设计缺陷或者不得已的需求）

（4）服务器调优以及各个参数的设置（缓冲、线程数等）

1、优化SQL步骤

（1）查看SQL执行频率

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

show status like 'Com_______';

show status like 'Innodb_rows_%';

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

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

（2）定位低效率执行SQL

1）慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

2）show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。命令：kill id 中断该连接

（3）explain分析执行计划

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

查询SQL语句的执行计划 ：

explain select * from tb_item where id = 1;

示例表：

1）explain 之 id

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

• id 相同表示加载表的顺序是从上到下；

• id 不同id值越大，优先级越高，越先被执行；

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

2）explain 之 select_type

表示 SELECT 的类型，常见的取值

3）explain 之 table：表示数据是关于哪一张表的

4）explain 之 type：访问类型，是较为重要的一个指标

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

5）explain 之 key

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

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

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

6）explain 之 rows：扫描行的数量

7）explain 之 extra：其他的额外的执行计划信息

（4）show profile分析SQL

show profiles 能了解sql时间都耗费在哪里。通过 select @@have_profiling 命令，能够看到当前MySQL是否支持profile

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：set profiling=1

通过profile，能够更清楚地了解SQL执行的过程

show databases;
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;

执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：

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

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

2、索引的使用

准备表

create table `tb_seller` (
`sellerid` varchar (100),
`name` varchar (100),
`nickname` varchar (50),
`password` varchar (60),
`status` varchar (1),
`address` varchar (100),
`createtime` datetime,
primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;

create index idx_seller_name_sta_addr on tb_seller(name,status,address);

（1）索引建立原则

1）为经常需要排序、分组和联合操作的字段建立索

（2）避免索引失效

1）全值匹配 ，对索引中所有列都指定具体值：索引生效，执行效率高

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

2）最左前缀法则

如果索引有多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引：

 违法最左前缀法则 ， 索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：下面这种只有name索引生效 

3）范围查询右边的列，不能使用索引：范围查询后面列的索引不生效

4）不要在索引列上使用运算和函数， 索引将失效

5）字符串不加单引号，会造成索引失效：查询时没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效

6）尽量使用覆盖索引（建立索引的列），避免select *，如果查询列，超出索引列，也会降低性能。

7）用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01
12:00:00';

8）以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。出现开头需要%时可以通过覆盖索引来解决：select 索引列  from table where name like '%黑马程序员%'

9）用不等于（！=或者<>）的时候无法使用索引

10）is NULL可以用到索引， is NOT NULL 不能用到索引。

11）in和exists走索引， not in 和not exists索引失效。

12）单列索引和复合索引。 尽量使用复合索引，少使用单列索引；数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。or 有时会使用多个索引

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

优化索引口诀

全值匹配我最爱，最左前缀要遵守

带头大哥不能丢，中间兄弟不能断

索引列上少计算，范围之后全失效

like百分写最右， 覆盖索引不写星

不等空值还有or，索引失效要少用

var引号不能丢，SQL高级也不难