Mysql优化

阅读量：6171 次

发布时间：2019-06-21

本文共 14580 字，大约阅读时间需要 48 分钟。

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 NULL AUTO_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 = 1 CHARACTER 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 NULL AUTO_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    DECLARE        index_num INTEGER DEFAULT 1;    WHILE            index_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                        10                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: 1  select_type: SIMPLE        table: t   partitions: NULL         type: refpossible_keys: idx_type          key: idx_type      key_len: 5          ref: const         rows: 3334     filtered: 100.00        Extra: NULL1 row in set, 1 warning (0.00 sec)

1.1 select_type

1.1.1

EXPLAIN select * from t_test t;

id    select_type    table1    　　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    table1    PRIMARY    t12    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    table1    PRIMARY    t12    UNION    t2    UNION 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    　　　　table1    PRIMARY    　　　　　　 t12    DEPENDENT SUBQUERY    t23    DEPENDENT UNION    　  t3    UNION 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<system

1.2.1 null

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

EXPLAIN select 1;

EXPLAIN select CURRENT_DATE();

id    select_type    table    partitions    type    possible_keys    key    key_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    key    key_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    key    key_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 SELECT    t1.* FROM    t_test t1,    t_type t2 WHERE    1 = 1     AND t1.id = t2.id;

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra1    SIMPLE    t2        index    PRIMARY    PRIMARY    4        2    100.00    Using index1    SIMPLE    t1        eq_ref    PRIMARY    PRIMARY    4    db_cold.t2.id    1    100.00

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

EXPLAIN SELECT    t1.* FROM    t_test t1,    t_type t2 WHERE    1 = 1     AND t1.id = t2.id     AND t1.type = 1;

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra1    SIMPLE    t2        index    PRIMARY    PRIMARY    4        2    100.00    Using index1    SIMPLE    t1        eq_ref    PRIMARY,idx_type    PRIMARY    4    db_cold.t2.id    1    32.91    Using where

SQL_2：

EXPLAIN SELECT    t2.* FROM    t_type t1,    t_test t2 WHERE    1 = 1     AND t1.id = t2.id     AND t2.type = 1;

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra1    SIMPLE    t1        index    PRIMARY    PRIMARY    4        2    100.00    Using index1    SIMPLE    t2        eq_ref    PRIMARY,idx_type    PRIMARY    4    db_cold.t1.id    1    32.91    Using where

left join

SQL_3：

EXPLAIN SELECT    t1.* FROM    t_test t1    LEFT JOIN t_type t2 ON t1.id = t2.id WHERE    1 = 1;

id    select_type    table    partitions    type    possible_keys    key    key_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 SELECT    t1.* FROM    t_test t1    LEFT JOIN t_type t2 ON t1.id = t2.id WHERE    1 = 1    AND t1.type = 3;

id    select_type    table    partitions    type    possible_keys    key    key_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    key    key_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

前面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    key    key_len    ref    rows    filtered    Extra1    SIMPLE    　　　　t        　　　　　　　　ref    idx_type    idx_type    5    const    3334    100.00

多表：

EXPLAIN SELECT    * FROM    t_test t1    LEFT JOIN t_type t2 ON t1.type = t2.id WHERE    1 = 1     AND t1.type = 1;

id    select_type    table    partitions    type    possible_keys    key    key_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 SELECT    t.type FROM    t_test t WHERE    1 = 1;

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

1.2.1 all

全表扫描

EXPLAIN SELECT    * FROM    t_test t WHERE    1 = 1

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

1.2.1 range（封闭区间检索，只使用<或者>不会使用Range）

唯一索引：

BETWEEN（range）

EXPLAIN SELECT    * FROM    t_test t WHERE    1 = 1     AND t.user_id BETWEEN 1 AND 2;

id    select_type    table    partitions    type    possible_keys    key    key_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    * FROM    t_test t WHERE    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    * FROM    t_test t WHERE    1 = 1     AND t.user_id > 10     AND t.user_id < 100;

id    select_type    table    partitions    type    possible_keys    key    key_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    * FROM    t_test t WHERE    1 = 1     AND t.type BETWEEN 1 AND 2;

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

IN（all）

EXPLAIN SELECT    * FROM    t_test t WHERE    1 = 1     AND t.type IN ( 1, 2 );

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

< >（range）

EXPLAIN SELECT    * FROM    t_test t WHERE    1 = 1     AND t.type > 10     AND t.type < 100;

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

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

EXPLAIN SELECT    * FROM    t_test t WHERE    1 = 1     AND t.user_id > 10;

id    select_type    table    partitions    type    possible_keys    key    key_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:     EXPLAIN select * from t_test t where 1=1 and t.id > 1;range:     EXPLAIN select * from t_test t where 1=1 and t.id >= 1;range:     EXPLAIN select * from t_test t where 1=1 and t.id < 1;range:     EXPLAIN select * from t_test t where 1=1 and t.id <= 1;range:     EXPLAIN select * from t_test t where 1=1 and t.id <> 1;range:     EXPLAIN select * from t_test t where 1=1 and t.id != 1;range:     EXPLAIN select * from t_test t where 1=1 and (t.id = 1 or t.id = 2);range:     EXPLAIN select * 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:     EXPLAIN select * from t_test t where 1=1 and t.id in (2);range:     EXPLAIN select * 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:     EXPLAIN select * 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:     EXPLAIN select * 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:     EXPLAIN select * from t_test t where 1=1 and t.user_id < 1;range:     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:     EXPLAIN select * from t_test t where 1=1 and (t.user_id = 1 or t.user_id = 2);range:     EXPLAIN select * 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:     EXPLAIN select * 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:       EXPLAIN select * 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;

转载于:https://www.cnblogs.com/wrcold520/p/10728652.html

你可能感兴趣的文章

我的友情链接

查看>>

基于corosync+pacemaker 实现web的高可用

账户：Windows Phone与Windows Live ID 4-游戏（Games Hub）和商店

查看>>