MySQL学习（六）---->查询优化相关

一、讨论的范围

优化维度大致四个：

硬件升级、系统配置、表结构设计、SQL语句及索引

 

本节讨论的重点在SQL语句及索引。

二、表结构设计和优化

2.1  设计中间表

设计中间表，一般针对于统计分析或者实时性不高的需求（报表）

2.2  设计冗余字段

设计冗余字段，减少关联查询，同时为了减少数据一致性问题应该合理选择不太可能变化的字段为冗余字段

2.3  拆表

对于字段太多的表，考虑拆表

对于表中不经常使用的字段或者存储数据比较多的字段，考虑拆表

2.4  主键优化

对每张表建立主键，且最好是int整型（存储空间小、可排序），建议自增主键

三、SQL及索引优化

准备数据——创建表：

CREATE TABLE `t_big_user` (
  `id` int(32) unsigned NOT NULL AUTO_INCREMENT,
  `nickname` varchar(20) NOT NULL COMMENT '昵称',
  `loginname` varchar(20) NOT NULL COMMENT '登录名',
  `age` tinyint(4) unsigned NOT NULL COMMENT '年龄',
  `sex` tinyint(4) unsigned NOT NULL COMMENT '性别',
  `status` tinyint(4) NOT NULL COMMENT '状态',
  `address` varchar(255) NOT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=375299 DEFAULT CHARSET=utf8mb4 COMMENT='测试用表';

准备数据——添加数据：

create PROCEDURE test_insert()
BEGIN DECLARE i INT DEFAULT 1;
WHILE i < 10000000 DO
    insert into t_big_user VALUES (null , CONCAT('x' , i ) , CONCAT('xlogin' , i ) , 6 , 1 , 1 ,'chengdu');
    SET i=i+1;
END WHILE;
COMMIT;
END;

3.1  EXPLAIN

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信

息，供开发人员有针对性的优化。例如：

explain select * from  t_big_user where id > 3 ;

  • select_type
    表示查询的类型。常用的值如下：
    SIMPLE ： 表示查询语句不包含子查询或union
    PRIMARY：表示此查询是最外层的查询
    UNION：表示此查询是UNION的第二个或后续的查询
    EXPLAIN SELECT * from user WHERE id < 3;
    DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
    UNION RESULT：UNION的结果
    SUBQUERY：SELECT子查询语句
    DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。
    最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。
  • type
    表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还
    是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。
    ALL：表示全表扫描，性能最差。
    index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
    range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
    ref：表示使用非唯一索引进行单值查询。
    eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一
    行结果。
    const：表示使用主键或唯一索引做等值查询，常量查询。
    NULL：表示不用访问表，速度最快。
  • possible_keys
    表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
  • key
    表示查询时真正使用到的索引，显示的是索引名称。
  • rows
    MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是
    越少效率越高，可以直观的了解到SQL效率高低。
  • key_len
    表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
    key_len的计算规则如下：
    • 字符串类型
      字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4
      char(n)：n*字符集长度
      varchar(n)：n * 字符集长度 + 2字节
    • 数值类型
      TINYINT：1个字节
      SMALLINT：2个字节
      MEDIUMINT：3个字节
      INT、FLOAT：4个字节
      BIGINT、DOUBLE：8个字节
    • 时间类型
      DATE：3个字节
      TIMESTAMP：4个字节
      DATETIME：8个字节
    • 字段属性
      NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。
  • Extra
    Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
    Using where
    表示查询需要通过索引回表查询数据。
    Using index
    表示查询需要通过索引，索引就可以满足所需数据。
    Using filesort
    表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort
    建议优化。
    Using temprorary
    查询使用到了临时表，一般出现于去重、分组等操作。

3.2 回表查询

　　在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要

有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记

录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记

录，这就叫做回表查询，它的性能比扫一遍索引树低。

总结：通过索引查询主键值，然后再去聚簇索引查询记录信息 ，它的性能比扫一遍索引树低

3.3 覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。

如何判断：explain的输出结果Extra字段为Using index时，能够触发索引覆盖

如何实现：实现索引覆盖最常见的方法就是：将被查询的字段，建立组合索引。

3.4 最左前缀原则

drop index idx_n_l_a on t_big_user;
create index idx_n_l_a on t_big_user(nickname , loginname , address);
explain select * from t_big_user where nickname = 'x1000000';
explain select * from t_big_user where nickname = 'x1000000' and loginname = 'xlogin1000000';
explain select * from t_big_user where nickname = 'x1000000' and loginname = 'xlogin1000000' and address = 'chengdu';
explain select * from t_big_user where loginname = 'xlogin1000000';
explain select * from t_big_user where address = 'chengdu' and loginname = 'xlogin1000000';

3.5 LIKE查询

问题1：MySQL在使用like模糊查询时，索引能不能起作用？

create index idx_nickname on t_big_user(nickname);
explain select * from t_big_user where nickname like '%o%'; //不起作用
explain select * from t_big_user where nickname like 'o%'; //起作用
explain select * from t_big_user where nickname like '%o'; //不起作用

3.6 NULL查询

问题2：如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？

不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

insert into t_big_user VALUES (null , null , CONCAT('xlogin' , 1 ) , 6 , 1 , 1 ,'chengdu')
# 单个索引
explain select * from t_big_user where nickname is null;
# 组合索引
create index idx_n_age_ on t_big_user(nickname , loginname , address);
explain select * from t_big_user where nickname is null;
explain select * from t_big_user where nickname is null and loginname = '11';
explain select * from t_big_user where nickname is null and loginname = '11' and address = '1';

结论：支持但不建议

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如'1970-01-01 00:00:00'。

3.7 索引和排序

MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高 。

如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用index排序方式，建议大家尽可能采用覆盖索引。

  • 以下几种情况，会使用index方式的排序。
    • ORDER BY 子句索引列组合满足索引最左前列
    • WHERE子句+ORDER BY子句索引列组合满足索引最左前列
create index idx_nickname_login on t_big_user(loginname, nickname);
explain select nickname, loginname  , address from t_big_user where loginname = 'xlogin100000' order by nickname;
  • 以下几种情况，会使用filesort方式的排序。
    • 对索引列同时使用了ASC和DESC
create index idx_nickname_login on t_big_user( loginname, nickname);
explain select address from t_big_user order by loginname desc , nickname asc;
    • WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in等）
create index idx_nickname_login on t_big_user( loginname, nickname);
explain select address from t_big_user where loginname < 10 order by nickname asc;
    • ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列
    • 使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引
create index idx_nickname on t_big_user(nickname);
create index idx_loginname on t_big_user(loginname);
explain select address from t_big_user order by loginname,nickname; 

create index idx_nickname on t_big_user(nickname, loginname);
explain select nickname,loginname from t_big_user order by nickname,loginname;
    • WHERE子句与ORDER BY子句，使用了不同的索引
    • WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式

3.8    慢查询优化

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time%';

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;
  • 如何判断是否为慢查询？
    MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执 行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。
  • 如何判断是否应用了索引？
    SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain命令分析查看，检查结果中的 key 值，是否为NULL。
  • 应用了索引是否一定快？
explain select id from t_big_user where id > 1;
  • 我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快 。

举例：比如对性别建立索引，没有好的过滤性

慢查询原因总结

  1. 全表扫描：explain分析type属性all
  2. 全索引扫描：explain分析type属性index
  3. 索引过滤性不好：靠索引字段选型、数据量、表设计
  4. 频繁的回表查询开销：尽量少用select *，使用覆盖索引

3.9 分页查询优化

show variables like 'profiling';

set profiling = 1;
  • 如果偏移量固定，返回记录量对执行时间有什么影响？
select * from t_big_user limit 10000 , 1;
select * from t_big_user limit 10000 , 10;
select * from t_big_user limit 10000 , 100;
select * from t_big_user limit 10000 , 1000;
select * from t_big_user limit 10000 , 10000;

结论：当查询较大量数据时，随着查询记录量越大，所花费的时间也会越来越多。

  • 如果查询偏移量变化，返回记录数固定对执行时间有什么影响？
select * from t_big_user limit 1 , 10000;
select * from t_big_user limit 10 , 10000;
select * from t_big_user limit 100 , 10000;
select * from t_big_user limit 1000 , 10000;
select * from t_big_user limit 10000 , 10000;

结论：当查询较大量数据时，随着查询记录量越大，所花费的时间也会越来越多。

3.9.1 分页优化方案

  • 使用覆盖索引优化
select * from t_big_user limit 10000 , 100;
select id  from t_big_user limit 10000 , 100;
  • 利用子查询优化
select * from t_big_user limit 10000 , 100;
select * from t_big_user where id >= (select id from t_big_user limit 10000 , 1) limit 100;
原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

 

优化总结：

  • 开启慢查询日志，定位运行忙的SQL语句
  • 利用explain执行计划，查看SQL执行情况
  • 关注索引使用情况：type
  • 关注Rows：行扫描
  • 关注Extra
  • 加索引后，观察索引使用情况，index只是覆盖索引，并不算很好地使用索引
  • 如果有关联尽量将索引用到eq_ref或ref级别
  • 对于复杂的SQL要逐一分析，找到比较费时地SQL语句片段进行优化

 

posted @ 2023-03-15 16:59  donleo123  阅读(25)  评论(0)  编辑  收藏  举报