翼度科技»论坛 编程开发 mysql 查看内容

【MySQL】MySQL语句优化

3

主题

3

帖子

9

积分

新手上路

Rank: 1

积分
9
一、嵌套查询优化

当SLQ语句存在嵌套查询时,MySLQ会生成临时表来存储子查询的结果数据,外层查询会从临时表中读取数据,待整个查询完毕后,会删除临时表,在这个过程中是非常耗时的。
方案:使用JOIN语句进行联表查询来代替,提升查询性能。
例如这里查询t_goods数据表中t_category字段不在t_goods_category数据表中的数据。
使用嵌套查询
  1. mysql> explain select * from t_goods where t_category not in (select t_category from t_goods_category)\G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: PRIMARY
  5.         table: t_goods
  6.    partitions: NULL
  7.          type: ALL
  8. possible_keys: NULL
  9.           key: NULL
  10.       key_len: NULL
  11.           ref: NULL
  12.          rows: 35
  13.      filtered: 100.00
  14.         Extra: Using where
  15. *************************** 2. row ***************************
  16.            id: 2
  17.   select_type: SUBQUERY
  18.         table: t_goods_category
  19.    partitions: NULL
  20.          type: ALL
  21. possible_keys: NULL
  22.           key: NULL
  23.       key_len: NULL
  24.           ref: NULL
  25.          rows: 15
  26.      filtered: 100.00
  27.         Extra: NULL
  28. 2 rows in set, 1 warning (0.00 sec)
复制代码
使用join联表查询
  1. mysql> explain select * from t_goods as goods left join t_goods_category as category on goods.t_category_id = category.id where category.t_category is null \G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: goods
  6.    partitions: NULL
  7.          type: ALL
  8. possible_keys: NULL
  9.           key: NULL
  10.       key_len: NULL
  11.           ref: NULL
  12.          rows: 35
  13.      filtered: 100.00
  14.         Extra: NULL
  15. *************************** 2. row ***************************
  16.            id: 1
  17.   select_type: SIMPLE
  18.         table: category
  19.    partitions: NULL
  20.          type: ALL
  21. possible_keys: PRIMARY
  22.           key: NULL
  23.       key_len: NULL
  24.           ref: NULL
  25.          rows: 15
  26.      filtered: 10.00
  27.         Extra: Using where; Using join buffer (hash join)
  28. 2 rows in set, 1 warning (0.00 sec)
复制代码
这里由于数据量的原因,效果可能不是这么明显,但是当查询量非常大时,这种查询效率的效果会更加明显。
二、OR条件语句优化

在多个条件使用OR关键字进行连接时,只要OR连接的条件中有一个查询条件没有使用索引,那么MySQL就不会使用索引,而是对数据表进行全表扫描。也就是说,在使用OR连接多个查询条件时,必须每个查询条件都是索引字段,MySQL才会使用索引查询数据。
  1. mysql> show create table t_goods \G;
  2. *************************** 1. row ***************************
  3.        Table: t_goods
  4. Create Table: CREATE TABLE `t_goods` (
  5.   `id` int NOT NULL AUTO_INCREMENT,
  6.   `t_category_id` int DEFAULT NULL,
  7.   `t_category` varchar(30) DEFAULT NULL,
  8.   `t_name` varchar(50) DEFAULT NULL,
  9.   `t_price` decimal(10,2) DEFAULT NULL,
  10.   `t_stock` int DEFAULT NULL,
  11.   `t_upper_time` datetime DEFAULT NULL,
  12.   PRIMARY KEY (`id`),
  13.   KEY `index_category_name` (`t_category_id`,`t_name`),
  14.   KEY `category_part` (`t_category`(10)),
  15.   CONSTRAINT `foreign_category` FOREIGN KEY (`t_category_id`) REFERENCES `t_goods_category` (`id`)
  16. ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  17. 1 row in set (0.00 sec)
复制代码
例如我有一个由t_category_id字段和t_name构成联合索引index_category_name
如果这里我使用And语句条件来进行查询,那么这里会使用联合索引index_category_name。
但是我如果使用OR语句,那么将不会使用索引查询,因为这两个字段无法单独构成索引。
  1. mysql> explain select * from t_goods where t_name = '耳机' or t_category_id = 1 \G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t_goods
  6.    partitions: NULL
  7.          type: ALL
  8. possible_keys: index_category_name
  9.           key: NULL
  10.       key_len: NULL
  11.           ref: NULL
  12.          rows: 35
  13.      filtered: 12.57
  14.         Extra: Using where
  15. 1 row in set, 1 warning (0.01 sec)
复制代码
  1. mysql> explain select * from t_goods where t_name = '耳机' and t_category_id = 1 \G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t_goods
  6.    partitions: NULL
  7.          type: ref
  8. possible_keys: index_category_name
  9.           key: index_category_name
  10.       key_len: 208
  11.           ref: const,const
  12.          rows: 1
  13.      filtered: 100.00
  14.         Extra: NULL
  15. 1 row in set, 1 warning (0.00 sec)
复制代码
三、ORDER BY语句优化

在查询语句中使用ORDER BY排序时,如果排序的字段不是索引字段,那么MySQL会对结果进行filesort文件排序,filesort是一种在磁盘上执行的排序算法。如果排序的字段是索引字段,那么会采用索引排序Index,会避免额外的操作,提高查询的性能。
  1. mysql> explain select id,t_stock from t_goods order by t_stock \G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t_goods
  6.    partitions: NULL
  7.          type: index
  8. possible_keys: NULL
  9.           key: stock_index
  10.       key_len: 5
  11.           ref: NULL
  12.          rows: 35
  13.      filtered: 100.00
  14.         Extra: Using filesort
  15. 1 row in set, 1 warning (0.00 sec)
复制代码
这里将商品以价格进行排序,可以看到这里的排序算法为文件排序Using filesort
现在我们在这个字段上添加索引
  1. mysql> alter table t_goods add index stock_index(t_stock);
  2. Query OK, 0 rows affected (0.06 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
复制代码
现在添加索引后,再次执行查询语句,将商品名以价格进行排序,也就是以字段t_stock进行排序
  1. mysql> explain select id,t_stock from t_goods order by t_stock \G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t_goods
  6.    partitions: NULL
  7.          type: index
  8. possible_keys: NULL
  9.           key: stock_index
  10.       key_len: 5
  11.           ref: NULL
  12.          rows: 35
  13.      filtered: 100.00
  14.         Extra: Using index
  15. 1 row in set, 1 warning (0.00 sec)
复制代码
可以看到这里使用了索引排序。如果查询的字段不在索引字段之内,那么在排序之后依然要进行筛选,所以依然会使用文件排序。(除主键外,因为本身就会使用索引排序)
四、GROUP BY语句优化

GROUP BY分组,用于数据查询时,对子段进行分组。在MySQL8.0之前的版本中,使用GROUP BY进行分组查询时,默认会根据分组的字段进行排序,如果该字段上不存在索引,会进行文件排序,比较耗费性能。解决方案:可以指定查询语句通过ORDER BY NULL语句来禁止文件排序,减少ORDER BY子句带来的性能消耗。
但是在8.0之后的版本中,没有这个默认条件。这里用的是MySQL8.0
  1. mysql> explain select t_category,count(*) from t_goods group by t_category \G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t_goods
  6.    partitions: NULL
  7.          type: ALL
  8. possible_keys: NULL
  9.           key: NULL
  10.       key_len: NULL
  11.           ref: NULL
  12.          rows: 35
  13.      filtered: 100.00
  14.         Extra: Using temporary
  15. 1 row in set, 1 warning (0.00 sec)
复制代码
可以看到这里没有使用文件排序,这样避免了filesort排序,会显著提升性能。
五、分页查询的优化

LIMIT分页查询,指定查询范围。在使用分页查询时,软如果单纯的使用LIMIT m,n语句,MySQL默认需要排序出数据表中的前m+n条数据,然后将前m条数据舍弃,返回m+1到m+n条数据记录,这样会非常消耗性能。
1、回表查询优化

例如,按照t_upper_time排序分页查询t_goods数据表中的数据
  1. mysql> explain select id,t_price from t_goods order by t_upper_time limit 1,10 \G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t_goods
  6.    partitions: NULL
  7.          type: ALL
  8. possible_keys: NULL
  9.           key: NULL
  10.       key_len: NULL
  11.           ref: NULL
  12.          rows: 35
  13.      filtered: 100.00
  14.         Extra: Using filesort
  15. 1 row in set, 1 warning (0.00 sec)
复制代码
直接使用LIMIT语句进行分页时,MySQL会进行全表扫描并对查询的结果数据使用filesort方式进行排序。
现在使用索引分页并回表查询数据
  1. mysql> explain select g1.id,g1.t_price from t_goods as g1 inner join (select id from t_goods order by t_upper_time limit 1,10) as g2 on g1.id=g2.id \G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: PRIMARY
  5.         table: <derived2>
  6.    partitions: NULL
  7.          type: ALL
  8. possible_keys: NULL
  9.           key: NULL
  10.       key_len: NULL
  11.           ref: NULL
  12.          rows: 11
  13.      filtered: 100.00
  14.         Extra: NULL
  15. *************************** 2. row ***************************
  16.            id: 1
  17.   select_type: PRIMARY
  18.         table: g1
  19.    partitions: NULL
  20.          type: eq_ref
  21. possible_keys: PRIMARY
  22.           key: PRIMARY
  23.       key_len: 4
  24.           ref: g2.id
  25.          rows: 1
  26.      filtered: 100.00
  27.         Extra: NULL
  28. *************************** 3. row ***************************
  29.            id: 2
  30.   select_type: DERIVED
  31.         table: t_goods
  32.    partitions: NULL
  33.          type: index
  34. possible_keys: NULL
  35.           key: t_upper_time_index
  36.       key_len: 6
  37.           ref: NULL
  38.          rows: 11
  39.      filtered: 100.00
  40.         Extra: Using index
  41. 3 rows in set, 1 warning (0.00 sec)
复制代码
这里使用查询嵌套,将子查询的结果集(派生表)作为中间表与g1也就是goods表进行join连接,形成最终表。这里的三条记录分别代表步骤。
第一条记录(最终的子查询),展示了从执行子查询到最终进行连接和返回结果的整个过程
第二条记录(主查询的一部分),也就是g1表与派生表之间的嵌套查询,通过id进行条件筛选。
第三条记录(子查询),也就是g2派生表,在这个查询中使用的索引排序并且使用逐渐筛选。
这样会大大提高查询的效率。不需要扫瞄全表。
2、记录数据标识优化分页

简单来说就是通过id值来查询数据的索引位置,当数据量特别大时,可以记录分页数据的最后一条数据的Iid值,当需要查询下一页时,只需要查询id的值大于记录的id值的n条数据即可。
例如,这里的每页查询10条记录,现在我需要查询第二页,那么我只需要将条件设置为id>20即可,向后取10个数据,再次记录id值,以此类推。
  1. mysql> explain select id,t_price from t_goods force index (t_upper_time_index) where id>20 order by t_upper_time limit 10 \G;
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t_goods
  6.    partitions: NULL
  7.          type: index
  8. possible_keys: NULL
  9.           key: t_upper_time_index
  10.       key_len: 6
  11.           ref: NULL
  12.          rows: 10
  13.      filtered: 33.33
  14.         Extra: Using where
  15. 1 row in set, 1 warning (0.00 sec)
复制代码
可以看到这样页可以大大提高查询的性能,但是这种优化方式有限定条件,必须保证排序字段的索引不会重复,如果出现了重复,则可能会丢失部分数据。
六、插入数据优化

向数据表中插入数据时,如果数据表中存在索引、唯一性校验时,会影响插入数据的效率。
1、MyISAM数据表插入数据优化

1.1、禁用索引

插入大量数据时,数据表中存在索引会降低数据插入的性能,可以在插入数据前禁用索引,待数据插入完毕再开启索引。
  1. alter table t_table_name disable keys;    #禁用索引
  2. alter table t_table_name enable keys;    #开启索引
复制代码
1.2、禁用唯一性检查

向数据表中插入数据时,对数据进行唯一性检查也会降低数据的插入性能,和索引一样,可以在插入数据前禁用唯一性检查,插入完毕后再开启。
  1. set unique_checks = 0 ;    #禁用唯一性检查
  2. set unique_checks = 1 ;    #开启唯一性检查
复制代码
1.3、禁用外键检查
  1. set foreign_key_checks = 0 ;     #禁用外键检查
  2. set foreign_key_checks = 1 ;     #开启外键检查
复制代码
1.4、批量插入数据

向数据表中韩插入多条数据时,使用insert语句一次插入多条数据比每次插入一条数据性能要高很多。
  1. INSERT INTO t_goods_category (id, t_category, t_remark) VALUES
  2. (6, '食品', '各种食品和饮料'),
  3. (7, '玩具', '儿童和成人的玩具'),
  4. (8, '家具', '家庭和办公室家具'),
  5. (9, '办公用品', '办公设备和文具'),
  6. (10, '宠物用品', '宠物的食品和用品');
复制代码
1.5、批量导入数据

使用LOAD DATA INFILE 语句向数据表中导入数据比使用INSERT语句的性能高,适用于需要批量导入的数据量很大时。
  1. LOAD DATA INFILE 'data_file_path' into table table_name;
复制代码
2、InnoDB数据表插入数据优化

这里,MyISAM中适用的优化,在这里也适用。但是需要注意的是,在批量导入数据时,被导入的文件中的数据记录最好是按主键顺序排列,这样可以提高导入数据的效率。
除此之外,InnoDB是支持事务的,可以在插入数据之前禁用MySQL自动提交事务,待插入数据完成后,在开启事务的自动提交,这样可以提高数据的插入性能。
  1. set autocommit = 0 ;    #禁用事务自动提交
  2. set autocommit = 1 ;    #开启事务自动提交
复制代码
七、删除数据的优化

如果数据表是分区表,删除数据表中的数据时,如果待删除的数据正好是数据表中某个分区的所有数据,这样可以直接删除分区,比DELETE语句删除数据性能要高很多。
  1. alter table table_name drop partition partition_name;    #删除分区
复制代码
注意,通过删除分区的方式删除数据只适用于range、list、range columns、list columns分区。

来源:https://www.cnblogs.com/changwan/p/18218721
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

举报 回复 使用道具