mysql <>优化_Mysql优化

最新推荐文章于 2024-07-29 09:52:01 发布

绝对伤心往事

最新推荐文章于 2024-07-29 09:52:01 发布

阅读量489

点赞数

文章标签： mysql <>优化

本文链接：https://blog.csdn.net/weixin_35316354/article/details/113336929

版权

Mysql优化

版本

select @@version;select version();

5.7.23

一、准备Sql

1、创建表结构

t_test：

DROP TABLE IF EXISTS`t_test`;CREATE TABLE`t_test` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`user_id` int(11) NULL DEFAULT NULL,

`user_level`int(1) NULL DEFAULT NULL,

`type`int(255) NULL DEFAULT NULL,

`y_score`varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`t_score`varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,PRIMARY KEY(`id`) USING BTREE

) ENGINE= InnoDB AUTO_INCREMENT = 1CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT =Dynamic;SET FOREIGN_KEY_CHECKS = 1;

user_id字段添加唯一索引；type字段添加普通索引

ALTER TABLE`db_cold`.`t_test`ADD UNIQUE INDEX `idx_user_id`(`user_id`) USING BTREE;ALTER TABLE`db_cold`.`t_test`ADD INDEX `idx_type`(`type`) USING BTREE;

t_type：

--------------------------------Table structure for t_type------------------------------

DROP TABLE IF EXISTS`t_type`;CREATE TABLE`t_type` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`type_name`varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,PRIMARY KEY(`id`) USING BTREE

) ENGINE= InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT =Dynamic;--------------------------------Records of t_type------------------------------

INSERT INTO `t_type` VALUES (1, '类型1');

2、使用存储过程插入1w条数据

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_auto_insertdata`( IN `rownum` INT)BEGIN

DECLAREindex_numINTEGER DEFAULT 1;WHILEindex_num<=rownum DO--insert

INSERT INTO `db_cold`.`t_test` (`user_id`, `user_level`, `type`, `y_score`, `t_score` )VALUES(

index_num+ 1,IF(index_num <= rownum/2, 10, 20),CASE index_num%3

WHEN 0 THEN 0

WHEN 1 THEN 1

WHEN 2 THEN 2

ELSE

END,

CONCAT( "Y", index_num+ 10),

CONCAT( "T", index_num+ 100)

);SET index_num = index_num + 1;END WHILE;END

CALL proc_auto_insertdata ( 10000)>OK> 时间: 348.543s

二、使用Explain分析SQL

1、Explain的一些内容

参考 https://segmentfault.com/a/1190000008131735

示例：

mysql> EXPLAIN select * from t_test t where 1=1 and t.type = 1\G;*************************** 1. row ***************************id:1select_type: SIMPLEtable: t

partitions:NULLtype: ref

possible_keys: idx_typekey: idx_type

key_len:5ref: const

rows:3334filtered:100.00Extra:NULL

1 row in set, 1 warning (0.00 sec)

1.1 select_type

1.1.1

EXPLAIN select * from t_test t;

id select_type table

1 　　SIMPLE 　　t

SIMPLE，单表查询。

1.1.2

EXPLAIN SELECT * FROM t_test t1 WHERE t1.type = ( SELECT t2.id FROM t_test t2 WHERE t2.id = 1 );

id select_type table

1 PRIMARYt12 SUBQUERY t2

t1外层查询对应的是PRIMARY，t2子查询对应的是SUBQUERY

1.1.3

EXPLAIN select * from t_test t1 where t1.id=1 UNION select * from t_test t2 where t2.id=1;

id select_type table

1 PRIMARYt12 UNIONt2UNION RESULT

UNION左边(第一个SELECT)对应PRIMARY，UNION右边(第二个以及之后的查询)对应UNION，最后合并的操作对应UNION RESULT

1.1.4

EXPLAIN select * from t_test t1 where t1.id in (select t2.id from t_test t2 where t2.id=1 UNION select t3.id from t_test t3 where t3.id=2 );

id select_type 　　　　table

1 PRIMARYt12DEPENDENT SUBQUERY t23 DEPENDENT UNIONt3UNION RESULT 　　

in 查询的子查询(下划线部分)与1.1.3一致，对于整个查询而言，子查询的PRIMARY是整个查询的DEPENDENT SUBQUERY，子查询的UNION是整个查询的DEPENDENT UNION，DEPENDENT SUBQUERY和DEPENDENT UNION都依赖于外层的结果，原因在与Mysql优化器会自动优化上面的语句为：

select * from t_test t1 where t1.id in (select t2.id from t_test t2 where t2.id=1 and t1.id=t2.id UNION select t3.id from t_test t3 where t3.id=2 and t1.id=t3.id);

也就说明了子查询的subquery和union确实依赖于外层的sql

1.2 type(参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types)

执行效率从低到高： all < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < ref < eq_ref < const

1.2.1 null

MySQL不访问任何表或索引，直接返回结果

EXPLAIN select 1;

EXPLAIN select CURRENT_DATE();

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　No tables used

1.2.1 const/system

单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询。

system是const的特例，表中只有一行数据。

主键索引：

EXPLAIN select * from t_test t where 1=1 and t.id = 2;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t 　　　　　　　　　　　　const PRIMARY PRIMARY 4 const 1 100.00

唯一索引：

EXPLAIN select * from t_test t where 1=1 and t.user_id = 2;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t 　　　　　　　　　　　　const idx_user_id idx_user_id 5 const 1 100.00

1.2.2 eq_ref

SQL_1_1：

EXPLAIN SELECTt1.*

FROMt_test t1,

t_type t2WHERE

1 = 1

AND t1.id = t2.id;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t2 index PRIMARY PRIMARY 4 2 100.00 Using index

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 db_cold.t2.id 1 100.00

SQL_1_2：相比SQL_1_1，多了t1 字段 type的过滤条件

EXPLAIN SELECTt1.*

FROMt_test t1,

t_type t2WHERE

1 = 1

AND t1.id =t2.idAND t1.type = 1;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t2 index PRIMARY PRIMARY 4 2 100.00 Using index

1 SIMPLE t1 eq_ref PRIMARY,idx_type PRIMARY 4 db_cold.t2.id 1 32.91 Using where

SQL_2：

EXPLAIN SELECTt2.*

FROMt_type t1,

t_test t2WHERE

1 = 1

AND t1.id =t2.idAND t2.type = 1;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t1 index PRIMARY PRIMARY 4 2 100.00 Using index

1 SIMPLE t2 eq_ref PRIMARY,idx_type PRIMARY 4 db_cold.t1.id 1 32.91 Using where

left join

SQL_3：

EXPLAIN SELECTt1.*

FROMt_test t1LEFT JOIN t_type t2 ON t1.id =t2.idWHERE

1 = 1;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t1 ALL 10130 100.00

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 db_cold.t1.id 1 100.00 Using index

SQL_4：

EXPLAIN SELECTt1.*

FROMt_test t1LEFT JOIN t_type t2 ON t1.id =t2.idWHERE

1 = 1

AND t1.type = 3;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t1 ref idx_type idx_type 5 const 1 100.00

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 db_cold.t1.id 1 100.00 Using index

总结：

以下面的执行计划为例

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra

1 SIMPLE t1 ref idx_type idx_type 5 const 1 100.00

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 db_cold.t1.id 1 100.00 Using index

前面t1的每一行数据，t2(type==eq_ref)表中都有唯一的一行记录与之匹配，t1和t2链接规则为主键索引或者不为null的唯一索引。

1.2.3 ref

单表：

EXPLAIN SELECT * FROM t_test t WHERE t.type = 1;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE 　　　　t 　　　　　　　　ref idx_type idx_type 5 const 3334 100.00

多表：

EXPLAIN SELECT

FROMt_test t1LEFT JOIN t_type t2 ON t1.type =t2.idWHERE

1 = 1

AND t1.type = 1;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t1 ref idx_type idx_type 5 const 3334 100.00

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00

总结：type为 ref 表示匹配当前表中的多行数据

1.2.2 index

最典型的就是查询的列是索引列

EXPLAIN SELECTt.typeFROMt_test tWHERE

1 = 1;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t index idx_type 5 10130 100.00 Using index

1.2.1 all

全表扫描

EXPLAIN SELECT

FROMt_test tWHERE

1 = 1

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t ALL 10130 100.00

1.2.1 range(封闭区间检索，只使用不会使用Range)

唯一索引：

BETWEEN(range)

EXPLAIN SELECT

FROMt_test tWHERE

1 = 1

AND t.user_id BETWEEN 1 AND 2;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t range idx_user_id idx_user_id 5 1 100.00 Using index condition

IN(range)

EXPLAIN SELECT

FROMt_test tWHERE

1 = 1

AND t.user_id IN ( 1, 2 );

1 SIMPLE t range idx_user_id idx_user_id 5 2 100.00 Using index condition

< >(range)

EXPLAIN SELECT

FROMt_test tWHERE

1 = 1

AND t.user_id > 10

AND t.user_id < 100;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t range idx_user_id idx_user_id 5 89 100.00 Using index condition

普通索引：

BETWEEN(all)

EXPLAIN SELECT

FROMt_test tWHERE

1 = 1

AND t.type BETWEEN 1 AND 2;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t ALL idx_type 10130 65.81 Using where

IN(all)

EXPLAIN SELECT

FROMt_test tWHERE

1 = 1

AND t.type IN ( 1, 2 );

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t ALL idx_type 10130 65.81 Using where

< >(range)

EXPLAIN SELECT

FROMt_test tWHERE

1 = 1

AND t.type > 10

AND t.type < 100;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t range idx_type idx_type 5 1 100.00 Using index condition

非主键开区间检索：> 或者<

EXPLAIN SELECT

FROMt_test tWHERE

1 = 1

AND t.user_id > 10;

id select_type table partitions type possible_keys keykey_len ref rows filtered Extra1 SIMPLE t ALL idx_user_id 10130 98.63 Using where

主键索引：

type　　　SQL

const: EXPLAIN select * from t_test t where 1=1 and t.id = 1;

range: EXPLAINselect * from t_test t where 1=1 and t.id > 1;

range: EXPLAINselect * from t_test t where 1=1 and t.id >= 1;

range: EXPLAINselect * from t_test t where 1=1 and t.id < 1;

range: EXPLAINselect * from t_test t where 1=1 and t.id <= 1;

range: EXPLAINselect * from t_test t where 1=1 and t.id <> 1;

range: EXPLAINselect * from t_test t where 1=1 and t.id != 1;

range: EXPLAINselect * from t_test t where 1=1 and (t.id = 1 or t.id = 2);

range: EXPLAINselect * from t_test t where 1=1 and t.id BETWEEN 1 and 2;ALL: EXPLAIN select * from t_test t where 1=1 and t.id like '%1';ALL: EXPLAIN select * from t_test t where 1=1 and t.id like '1%';ALL: EXPLAIN select * from t_test t where 1=1 and t.id like '%1%';

const: EXPLAINselect * from t_test t where 1=1 and t.id in (2);

range: EXPLAINselect * from t_test t where 1=1 and t.id not in (1);NULL: EXPLAIN select * from t_test t where 1=1 and t.id is null;ALL: EXPLAIN select * from t_test t where 1=1 and t.id is not null;

普通索引

type　　　SQL

ref: EXPLAIN select * from t_test t where 1=1 and t.type = 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.type > 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.type >= 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.type < 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.type <= 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.type <> 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.type != 1;ALL: EXPLAIN select * from t_test t where 1=1 and (t.type = 1 or t.type = 2);ALL: EXPLAIN select * from t_test t where 1=1 and t.type BETWEEN 1 and 2;ALL: EXPLAIN select * from t_test t where 1=1 and t.type like '%1';ALL: EXPLAIN select * from t_test t where 1=1 and t.type like '1%';ALL: EXPLAIN select * from t_test t where 1=1 and t.type like '%1%';

ref: EXPLAINselect * from t_test t where 1=1 and t.type in (2);ALL: EXPLAIN select * from t_test t where 1=1 and t.type not in (1);

ref: EXPLAINselect * from t_test t where 1=1 and t.type is null;ALL: EXPLAIN select * from t_test t where 1=1 and t.type is not null;

唯一索引

type　　　SQL

NULL: EXPLAIN select * from t_test t where 1=1 and t.user_id = 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.user_id > 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.user_id >= 1;

range: EXPLAINselect * from t_test t where 1=1 and t.user_id < 1;

range: EXPLAINselect * from t_test t where 1=1 and t.user_id <= 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.user_id <> 1;ALL: EXPLAIN select * from t_test t where 1=1 and t.user_id != 1;

range: EXPLAINselect * from t_test t where 1=1 and (t.user_id = 1 or t.user_id = 2);

range: EXPLAINselect * from t_test t where 1=1 and t.user_id BETWEEN 1 and 2;ALL: EXPLAIN select * from t_test t where 1=1 and t.user_id like '%1';ALL: EXPLAIN select * from t_test t where 1=1 and t.user_id like '1%';ALL: EXPLAIN select * from t_test t where 1=1 and t.user_id like '%1%';

const: EXPLAINselect * from t_test t where 1=1 and t.user_id in (2);ALL: EXPLAIN select * from t_test t where 1=1 and t.user_id not in (1);

ref: EXPLAINselect * from t_test t where 1=1 and t.user_id is null;ALL: EXPLAIN select * from t_test t where 1=1 and t.id is not null;