sql优化

sql优化

一:优化方向

优化成本：硬件>系统配置>数据库表结构>SQL及索引。

优化效果：硬件<系统配置<数据库表结构<SQL及索引。

二:SQL性能分析(执行计划)

MySQL提供了一个非常实用的命令（explain）来便于我们对SQL进行分析，只要在SQL语句前加上一个explain关键字，那么我们就会得到一个SQL的执行计划，这个执行计划会告诉我们SQL中表的读取顺序、查询类型、用到了什么索引、使用索引的长度 、扫描了多少条记录等信息，通过执行计划提供的信息, 我们可以快速定位到哪些地方可以进行优化。

查询120万条数据:

 EXPLAIN 结果中的type(访问类型)

常见的扫描方式

system：系统表，少量数据，往往不需要进行磁盘IO

const：常量连接

eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描

ref：非主键非唯一索引等值扫描

range：范围扫描

index：索引树扫描

ALL：全表扫描(full table scan)

type扫描方式由快到慢

system > const > eq_ref > ref > range > index > ALL

★注意：一般来说，得保证查询至少达到range级别，最好能达到ref。

1.system

这种类型要求数据库表中只有一条数据，是 const 类型的一个特例，一般情况下是不会出现的。

官方文档中的解释：该表只有一行（系统表）。这是const联接类型的特例

2.const

通过索引一次就找到了数据，一般出现在使用了primary key(主键)或者unique索引(唯一索引)匹配到了数据，匹配的条件是常量(数字)。

模拟数据:

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;
​
insert into user values(1,'zhangsan');
insert into user values(2,'lisi');
insert into user values(3,'wangwu');
insert into user values(4,'zhaoliu');

 3.eq_ref

命中主键(primary key)或者非空唯一索引(unique not null) ,匹配条件是某个表的列,联表查询和等值连接

模拟数据

create table user_balance (
  uid int primary key,
  balance int
)engine=innodb;
​
insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);
insert into user_balance values(5,500);

对于user表中的每一行，对应后user_balance表只有一行被扫描，这类扫描的速度也非常的快

Const 和eq_ref的区别

两个都是在用到了主键索引或唯一索引的情况下出现，不同的是Const 的where 条件是常量，eq_ref

的where 条件是其他表的某个列，需要对这个列进行转义才能拿到匹配条件的值，也可以简单的理解

为，eq_ref 一般为关联查询。

4.ref

非唯一性索引扫描，和eq_ref 不同的是eq_ref 匹配的是唯一索引，ref它返回所有匹配某个单独值

的行,它可能会找到多个符合条件的行。

模拟数据

多表查询

同eq_ref模拟数据区别：user_balance表中的主键索引改为普通索引

由于balance表使用了普通非唯一索引，对于前表user表的每一行，后表user_balance表可能有多于一行的数据被扫描

单表查询

当id改为普通非唯一索引后，常量的连接查询，也由const降级为了ref，因为非唯一索引所以有多于一行的数据被可能被扫描

ref每一次匹配可能有多行数据返回，虽然它比eq_ref要慢，但它仍然是一个很快的类型

5.range

范围数据扫描。

between

 in

 >,>=,<,<=

 6.index

index类型，需要扫描索引上的全部数据，它仅比全表扫描快一点

 7.ALL

全表扫描。

 EXPLAIN 结果中的id--(执行顺序)

id是表示语句执行顺序的标识；

不同的id值，id越大的优先执行；

相同的id值，则由上往下执行，排在上面的语句先执行。

★注意：id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

EXPLAIN 结果中的select_type--(查询类型)

此列主要用于区分查询语句的类型，通过这个列可以区分SQL属于简单的select、联合查询、子查询等。

SIMPLE：简单的select查询，不包含任何子查询或联合查询。

PRIMARY:主查询，如果有子查询的话，最外层的查询会被标记为PRIMARY。

SUBQUERY: 该语句属于子查询语句。

DERIVED ： 生成的临时表的查询语句会被标记为DERIVED 。

UNION ： 标记为UNION类型的查询语句。

UNION RESULT: 从UNION语句中获取结果。

EXPLAIN 结果中的table--(正在访问哪个表)

显示这一行的数据是来源于哪张表的。

EXPLAIN 结果中的possible_keys--(可能使用的索引)

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

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

 EXPLAIN 结果中的key--(实际使用的索引)

实际使用的索引。如果为空，则说明没有使用索引。

 EXPLAIN 结果中的key_len--(索引中使用的字节数)

使用到的索引key的长度，如果为联合索引则显示已命中的联合索引长度之和（如：联合索引

为a+b+c ，如果索引命中了a+b ，那么长度就为a+b的索引长度，通常可以通过key_len 来分析联合索引所命中的情况）。

 如何计算？

1.先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20

2.如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘3，GBK要乘2

3.varchar这种动态字符串要加2个字节

4.允许为空的字段要加1个字节

 关于possible_keys 和key的三种关系场景：

possible_keys !=null&& key!=null ，这是正常使用到了索引的情况。

possible_keys !=null&& key==null ,这种情况一般说明通过索引并不能提升多少效率，一般而言是表的数据量很少，或者是索引的字段离散性不高，执行计划发现用索引和扫表差不多。

possible_keys ==null&& key!=null , 这种情况一般为where条件没有命中索引，但是查询的列是索引字段，也就是查询的列命中覆盖索引情况。

EXPLAIN 结果中的ref--(显示索引中被使用的列)

显示索引的哪一列被使用了，一般是一个常量const。哪些列或常量被用于查找索引列上的值。

EXPLAIN 结果中的rows--(扫描的行数)

扫描的数据行数，一般来说扫描的数据行数越少，性能越好。

EXPLAIN 结果中的filtered--(过滤比例)

该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

EXPLAIN 结果中的Extra--(额外的查询信息)

using where ： 使用了where条件,有on没有where也算.

 using index： 使用了覆盖索引 （通常情况下这是一种好现象，意味着查询的数据直接在二级索引返回了，从而减少了回表的过程）。

表示相应的select操作中使用了覆盖案引(Covering Index)，避免访问了表的数据行

1如果同时出现using where，表明索引被用来执行索引键值的查找

数据准备:personnel中的user_inf表,

创建索引

CREATE INDEX index_name_loginname_email on user_inf(username,email,loginname);

 2如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找:

它不需要访问表数据，而是从索引中就可以获取所有需要的信息，这样可以减少磁盘 I/O 操作和 CPU 开销

 3利用索引进行了排序或分组。

 using filesort ：就是使用了非索引的字段进行排序（通常这种情况需要优化）。

数据准备:去掉user_inf表的索引

 using temporary ： 使用了临时表（常见于group by、order by）,mysql在对查询结果排序时使用临时表,这个临时表通常会被创建在磁盘上，并在查询结束后被自动删除。

这个只有在数据量特别大的时候才会使用,如果筛选后行数比较少,或着系统内存充足且未被使用的内存空间可以满足需求mysql就会避免使用临时表.

using join buffer ： 使用了join buffer缓存,就是连接缓存（这种情况关注一下关联查询的字段是不是没有建索引）。也是 MySQL 用来优化连接查询性能的一种技术，它可以避免使用磁盘临时表，提升查询效率。

当 MySQL 执行连接查询时，比如 JOIN 操作，需要将两个或多个表中符合条件的行进行关联。如果连接查询中涉及到大量数据，就需要频繁地读取磁盘上的数据，这会影响查询性能。

为了加速连接查询的执行，MySQL 引入了连接缓存技术。join buffer 是一种内存中的缓冲区，用于保存连接查询过程中中间结果的临时信息。当 MySQL 执行连接查询时，会尽量利用 join buffer 来存储和处理中间结果，避免频繁读写磁盘上的数据，从而提高查询效率。

三:优化原则

SQL优化中，主要原则有三点:

最大化利用索引；

尽可能避免全表扫描；

减少无效数据的查询.

数据准备:用120万条数据

1、尽量全值匹配

给name添加索引

SQL 优化中的“尽量全值匹配”指的是在查询条件中使用完整值进行匹配，避免使用模糊查询或者部分匹配查询。这样可以让数据库系统更快地找到匹配的数据行，从而提高查询的性能。

EXPLAIN SELECT * FROM user WHERE name = '张力';

这里使用了完整的值“张三”进行匹配，这样数据库就可以直接使用索引查找符合条件的数据行，而不用像模糊匹配那样逐个比较每一条数据，因此查询速度更快。

EXPLAIN SELECT * FROM user WHERE name like '张%';

 如果使用模糊查询或者部分匹配查询，例如使用 LIKE '%张%' 或者 LIKE '张%' 等语句，则需要扫描整个表中的数据，逐个比较是否符合条件，这种方式会导致查询速度变慢。

2、最佳左前缀原则

给120万条数据添加复合索引

CREATE INDEX idx_name_age_phone ON user(name,age,phone);

SQL 优化中的“最佳左前缀原则”指的是在使用复合索引时，尽可能地使用索引的左边列进行查询，以此来提高查询效率。

 EXPLAIN SELECT * FROM user where name = '张力' and age = '27' and phone = '13429034335';

 EXPLAIN SELECT * FROM user where age = '27';

 3、不在索引列上做任何操作

给name 列创建了索引。例如使用 SQL 语句查询name是张力所有用户信息

CREATE INDEX index_name ON user(name);

尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

EXPLAIN SELECT * FROM user WHERE name = '张力';

 这个查询语句没有对索引列 name 进行任何操作，因此数据库系统可以直接利用索引找到匹配的数据行，从而提高查询效率。

EXPLAIN SELECT * FROM user WHERE UPPER(name) = '张力';

 对 name 列进行了大小写转换操作，这会导致索引失效，从而在查询时需要扫描更多的数据行，效率相对较低。

4、覆盖索引尽量用

给name,age,phone添加索引

CREATE INDEX index_name_age_phone ON user(name,age,phone);

覆盖索引是指索引能够覆盖查询的所有需要返回的列，也就是说，在索引上就可以查出所需的数据，不需要再去数据表中取一次数据。这种技术也叫做 “索引覆盖” 或者 “Covering Index”。

使用覆盖索引可以避免访问数据表，从而提升查询效率。当 SQL 查询需要返回大量数据时，如果能使用覆盖索引，则可以减少访问数据表的次数，节省系统资源，提高查询性能。

为了使用覆盖索引，需要将查询语句中需要返回的列都建立索引，这样在查询时，MySQL 就可以直接通过索引获取所需的数据值，而无需访问数据表。

当然也并非所有的查询都适合使用覆盖索引。例如，如果查询需要返回的列过多，或者需要在查询结果上进行排序、分组、函数计算等操作时，就无法使用覆盖索引。此外，也需要根据实际情况来判断是否使用覆盖索引，因为过多的索引会占用数据库系统的内存和磁盘资源，增加维护和操作的成本。

普通查询

EXPLAIN SELECT *
FROM user
WHERE name = '张力'
  AND age >= 23
  AND phone BETWEEN '13567811322' AND '19625038069';

 当查询语句使用了索引但没有覆盖查询时，Extra 列通常会显示 "Using index condition"。

覆盖索引查询:

EXPLAIN SELECT phone
FROM user
WHERE name = '张力'
  AND age >= 23
  AND phone BETWEEN '13567811322' AND '19625038069';

 5、慎用不等于：！= 和 <>

给age添加索引

CREATE INDEX INDEX_age ON user(age);

MySQL 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

EXPLAIN SELECT *
FROM user
WHERE age = 18;

 EXPLAIN SELECT * FROM user WHERE age != 18;

 EXPLAIN SELECT * FROM user WHERE age <> 18;

6、Null/Not Null

 给name设置索引

CREATE INDEX INDEX_age ON user(name);

如果创建表的时候name字段自定定义为NOT NULL

EXPLAIN SELECT * from user where name is null;
EXPLAIN SELECT * from user where name is not null;
EXPLAIN SELECT * from user where name = '张力';

 7、Like查询

给name设置索引

like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

EXPLAIN SELECT * from user where name like '张%';
​
EXPLAIN SELECT * from user where name like '%力%';
​
EXPLAIN SELECT * from user where name like '%力';

 8、字符类型加引号

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

EXPLAIN select * from user where name = 2000;

9、尽量不要用select*而是具体字段

可以节省资源,减少网络开销,可能会用到覆盖索引,提高查询效率.