Mysql基础学习（十九）--- 索引优化

最新推荐文章于 2019-10-10 14:43:59 发布
最新推荐文章于 2019-10-10 14:43:59 发布
阅读量220 收藏 2
点赞数
分类专栏： mysql 文章标签： Mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40990836/article/details/102367203
版权
创建了索引如何避免索引失效

首先创建一个表 staffs

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`NAME` VARCHAR(20) NOT NULL ,
	age INT NOT NULL DEFAULT 0 ,
	pos VARCHAR(20) NOT NULL,
	add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)CHARSET utf8 COMMENT '员工记录表';

INSERT INTO staffs (name,age,pos,add_time) VALUES ('z3',22,'manager',NOW());
INSERT INTO staffs (name,age,pos,add_time) VALUES ('l4',23,'dev',NOW());
INSERT INTO staffs (name,age,pos,add_time) VALUES ('2000',23,'dev',NOW());

ALTER TABLE ADD INDEX idx_staffs_nameAgePos(name,age,pos);

索引优化避免失效法则
在这里插入图片描述
以下挨个解释以下。
首先查看我们创建得索引

mysql> show index from staffs;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY               |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | idx_staffs_nameAgePos |            1 | NAME        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | idx_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | idx_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

现在可以看到 id 是主键， name,age,pos 是复合索引。

  1. 全值匹配我最爱
使用 EXPLAIN 分析SQL
EXPLAIN SELECT * FROM staffs WHERE name = "July";
# 返回结果
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

EXPLAIN SELECT * FROM staffs WHERE name = "July" AND age = 23 AND pos = "dev";
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 128     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


EXPLAIN SELECT * FROM staffs WHERE name = "July" AND age = 23;
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 66      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM staffs WHERE name = "July"  AND pos = "dev";
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62      | const |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM staffs WHERE  pos = "dev";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

通过上面EXPLAIN 分析得出，如果使用符合索引，那么想要避免索引失效。 就需要遵守最佳左前缀法则： 如果索引了多列，要遵守最左前缀法则，指的是查询从索引最左前列开始并且不跳过索引中得列。
打个比方就是，正常火车都是一节一节得，这里得 name 就比如一个火车头，在最前面。如果你得火车头都没有，那你得火车怎么往前面跑呢？你有了火车头， 这个时候。你得索引顺序应该是 name, age , pos, 但是你现在没有 age，那么 你得火车就好比 火车得第二节断掉了， 那么火车头自然也拉不到 第三节第四节。 不过 火车头得第二节第三节调换顺序好像是没有问题得。但是你使用得多列必须一个不能少。

EXPLAIN SELECT * FROM staffs WHERE name = "July"  AND pos = "dev" AND age = 23;
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 128     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
  1. 在索引列上使用计算，函数，以及类型转换，会导致索引失效而变为全表扫描
# 使用小写函数进行查询然后分析SQL
SELECT * FROM staffs WHERE LOWER(name) = "july";
+----+------+-----+-----+---------------------+
| id | NAME | age | pos | add_time            |
+----+------+-----+-----+---------------------+
|  3 | July |  23 | dev | 2019-10-08 10:19:42 |
+----+------+-----+-----+---------------------+
# 分析使用函数
 EXPLAIN SELECT * FROM staffs WHERE LOWER(name) = "july";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

# 分析不适用函数
 EXPLAIN SELECT * FROM staffs WHERE name = "July";
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

发现使用函数得扫描类型为 all 。没有使用索引。 没有使用函数得为 ref ， 并且使用了索引。

  1. 存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM staffs WHERE name="July" AND age > 20;
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 66      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

 EXPLAIN SELECT * FROM staffs WHERE name="July" AND age > 20 AND pos="dev";
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 66      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

这两个explain SQL语句比较后发现，在索引列 age > 20 比较之后。右侧还有索引列进行条件检索， 虽然 key 使用了创建的索引，但是 key_len并没有发生变化。

  1. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））减少SELECT *
EXPLAIN SELECT NAME , age, pos FROM staffs WHERE name="July" AND age = 23 AND pos = "dev";
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 128     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+

EXPLAIN SELECT * FROM staffs WHERE name="July" AND age = 23 AND pos = "dev";
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 128     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

对比两个EXPLAIN SQL 发现Extra 中 一个use index, 一个为Null

  1. 使用不等于（ != 和 <>） 会导致全表扫描
EXPLAIN SELECT * FROM staffs where name != 'July';
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

EXPLAIN SELECT * FROM staffs where name <> 'July';
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到possibles_keys 虽然说，可用的索引有 idx_staffs_nameAgePos, 但是实际使用到的 key 没有使用到索引/。

  1. is null , 和 is not null 也无法使用索引。
EXPLAIN SELECT * FROM staffs where name IS NULL;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

EXPLAIN SELECT * FROM staffs where name IS NOT NULL;
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到以上 使用 IS NULL 和 IS NOT NULL，实际使用索引 key 中 都为NULL

  1. Like 以通配符开头（"%abc…"）mysql 索引失效会变成全表扫描的操作
# % 在两边
 EXPLAIN SELECT *FROM staffs where name LIKE "%Ju%";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

# % 在左边
EXPLAIN SELECT *FROM staffs where name LIKE "%July";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

# %在右边
 EXPLAIN SELECT * 
 FROM staffs where name LIKE "July%";
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

从以上结果可以看出。只有 % 在右边的 才使用了索引。

那么如何解决使用 LIKE %abc% 时索引不被使用的问题呢

举例子

EXPLAIN SELECT name,age,pos FROM staffs WHERE name  LIKE "%July%";
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index | NULL          | idx_staffs_nameAgePos | 128     | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+

 EXPLAIN SELECT id,name,age,pos FROM staffs WHERE name  LIKE "%July%";
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index | NULL          | idx_staffs_nameAgePos | 128     | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

当我们指定搜索字段为 id，name，age，pos 的时候，发现 type 是 Index，并且key实际使用索引为 idx_staffs_nameAgePos

使用 指定不为覆盖索引的索引列 add_time 或者 使用 * 的时候

 EXPLAIN SELECT id,name,age,pos,add_time FROM staffs WHERE name  LIKE "%July%";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN SELECT * FROM staffs WHERE name  LIKE "%July%";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这个时候就会发现 type 为 all, 并且实际使用索引为null。 发现并没有使用到索引

  1. 字符串不加单引号索引失效
# 更新一个数据
update staffs set name='2000' where id = 2
# 查询 姓名为 2000 的， name 的类型为 VARCHAR
EXPLAIN SELECT * FROM staffs WHERE name=2000;
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
# 加上单引号进行检索
EXPLAIN SELECT * FROM staffs WHERE name='2000';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 62      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

通过以上看到进行字符串搜索的时候，如果不加上 单引号，会导致索引失效。 这是因为mysql 会在你搜索2000的时候因为你没有加单引号，所以会自动把 2000 这个转换为字符串在进行搜索。这个就相当于进行了一次类型转换。所以 实际使用索引 key 这里为NULL

  1. 少用 or , 用它连接时会索引失效。
EXPLAIN select * from staffs where name='July' or age = 23;
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    55.56 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 解决使用 or 索引失效的办法
EXPLAIN select id,name,age,pos from staffs where name='July' or age = 23;
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 128     | NULL |    3 |    55.56 | Using where; Using index |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

通过以上分析可以看出， 使用 or ，并且查询的不是覆盖索引中的索引列的时候，查询 type 为 all, 全表扫描。并且实际使用索引 key 为 null.
但是如果查询的 为 name,age,pos 是覆盖索引的索引列。查询 type 为 index, 并且使用了 覆盖索引。 idx_staffs_nameAgePos

Order By 优化

先创建一张表 tbA

CREATE TABLE tbA(
	id INT PRIMARY NOT NULL AUTO_INCREMENT,
	age INT,
	birth TIMESTAMP NOT NULL
);
INSERT INTO tbA(age,birth) VALUES (22, NOW());
INSERT INTO tbA(age,birth) VALUES (23, NOW());
INSERT INTO tbA(age,birth) VALUES (24, NOW());
# 创建索引
CREATE INDEX idx_A_ageBirth ON tbA(age,birth);

使用explain 分析sql 语句

# 使用索引列 age 并对 索引列 age 进行排序
EXPLAIN SELECT * FROM tbA WHERE age > 20 ORDER BY age
    -> ;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbA   | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
# using where  和  using index

# 使用索引列 age  并对索引列 birth 进行排序
EXPLAIN SELECT * FROM tbA WHERE age > 20 ORDER BY birth;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | tbA   | NULL       | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
# Using Where , Using Index, Using filesort

# 仅对索引列 age 进行排序
EXPLAIN SELECT * FROM tbA ORDER BY age;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbA   | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
# Using index

# 只对索引列 birth 进行排序
 EXPLAIN SELECT * FROM tbA ORDER BY birth;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tbA   | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
# Using index, Using filesort

# 对索引列 age 进行排序
EXPLAIN SELECT * FROM tbA WHERE birth < NOW() ORDER BY age;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbA   | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
# Using where , Using index

# 对age 进行 ASC 排序， 对 birth 进行 DESC 排序
EXPLAIN SELECT * FROM tbA ORDER BY age ASC,birth DESC;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tbA   | NULL       | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
# Using index, Using filesort
  1. OrderBy 子句，尽量使用 index 方式排序，避免使用 FileSort 方式排
  2. Order By 满足两种情况，会使用 index 排序， filesort的方式效率较低
    • Order By 语句使用索引最左前缀
    • 使用 WHERE 子句和 ORDER BY子句条件列组合满足索引前列 （如果where使用的索引最左前缀定义为常量，则order by 可以使用索引）
  3. 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
  4. 如果不能在索引列上，filesort 两种算法: Mysql 就要启动双路排序和单路排序
    • 双路排序A：Mysql 4.1 之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据，读取行指针和 orderby列， 对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取去数据，从磁盘中去排序字段，在 buffer 中进行排序，在从磁盘中取其他字段
    • 单路排序B：从磁盘中读取查询需要的所有列，然后按照 order by 列在buffer对他们进行排序，然后扫面描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间，因为他把每一行都保存到内存中了。
    • 单路算法问题：在sort_buffer中，方法B比方法A要多占用空间，因为方法B是把所有字段都取出来，所以有可能取出的数据总大小超出了 sort_buffer的容量，导致每次只能取sort_buffer容量的大小的数据，进行排序，创建 tmp 文件，进行多路合并，排序再取sort_buffer 容量大小，在排 从而导致多次 IO，本来是只有一次IO操作，结果反而导致了大量的IO操作，得不偿失
  5. 优化策略
    • 增大 sort_buffer_size 参数的设置
    • 增大 max_length_for_sort_data 参数的设置
    • Order By 的时候，使用 SELECT * ,是一个大忌，因为当Query的字段大小总和小于 max_length_for_sort_data 而且排序的字段不是 text|blob的时候，会用改进的算法 单路排序，否则使用 多路排序，两种算法的数据，都有可能超出 sort_buffer的容量，超出之后，会创建 tmp 文件并进行合并排序，导致多次IO，但是使用单路排序的算法的风险会更大一些，所以要提高 sort_buffer_size.
    • 尝试提高 sort_buffer_size
    • 尝试提高 max_length_for_sort_data,提高这个参数们会增加用改进算法的概率。如果设置的太高，数据总量超出 sort_buffer _size 的概率就会增大，明显症状是 高的磁盘IO，和 低的处理器使用率。
GROUP BY 关键字优化
  1. group by 实质是先排序后分组，遵照索引的最左前缀
  2. 当无法使用索引列。增大 max_length_for_sort_data 参数的设置， + 增大 sort_buffer_size 参数的设置
  3. where 高于 having , 能写在 where 限定的条件就不要去 having 限定了。
天才小熊猫12138584
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL学习笔记-基础到进阶
05-28
内容概要：MySQL学习笔记，内容包括SQL基本语法、MySQL基础知识，包括存储引擎、索引、视图、锁、优化、存储结构等MySQL主要的内容。 适用人群：适合自学MySQL的同学使用。 能学到什么：MySQL从基础到进阶的全部内容...
MYSQL学习资源及笔记-入门必备
最新发布
09-10
这份“MYSQL学习资源及笔记-入门必备”压缩包文件显然包含了帮助初学者掌握MySQL基础知识的各种资料。下面，我们将深入探讨MySQL的一些核心概念和关键知识点。 1. **MySQL基础知识** - **数据库与表**：MySQL中的...
mysql-5.5.40-winx64.zip
02-13
总之，“mysql-5.5.40-winx64.zip”是一个适合Windows 64位用户的MySQL版本，包含了丰富的功能和性能优化，无论是对于初学者还是经验丰富的开发者，都是一个值得学习和使用的数据库解决方案。通过深入理解和实践，你...
mysql-5.5.49-win32.zip
10-13
5. **并行复制**：虽然5.5版本的复制功能相比5.6和5.7并不完善，但在5.5.49中，已经开始引入了一些并行复制的基础概念，为后续版本的优化打下了基础。 6. **更好的内存管理**：MySQL 5.5.49对内存使用进行了优化，...
MySQL学习笔记3-数据库设计与优化.md
08-22
1. **索引优化** - 索引是提高数据库查询效率的关键手段之一。通过合理设计索引，可以大幅提高查询速度。例如，在图书表中为`title`字段创建索引，可以使搜索特定标题的图书变得更加迅速。 2. **查询优化** - ...
Linux 安装Mysql8.0的读写分离,以及常用报错解决方式
qq_40990836的博客
06-24 1520
Centos7.2 ,Mysql8.0主从配置和读写分离。主写从读 环境准备 系统： Centos7.2 Mysql版本： 8.0 搭建两台服务器，一台作为主服务器，一台作为从服务器。主服务器进行写操作。从服务器进行读操作 之前已经博文已经写过如何安装Mysql 8.0了。不会的可以去查看之前的博客 一、主从配置步骤 确保主数据库和从数据库的数据保持一样 二、主服务器上的操作 修改mysql配...
Linux Centos7系统下安装Mysql 8.0
qq_40990836的博客
06-24 632
环境准备 Centos 7.2 64 位 Mysql 8.0 下载Mysql 8.0 // linux 命令行 // 进入usr 目录下 cd /usr // 创建一个下载文件夹 mkdir download // 使用wget命令下载mysql 安装 wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8....
mysql 级联查询 左查询
qq_40990836的博客
06-24 565
左连接查询 select <include refid="permission_column" /> from sys_permission as p left join sys_role_permission as rp on p.id = rp.sys_permission_id left join sys_user_role a...
Mysql基础学习（十二）-- Mysql 变量
qq_40990836的博客
09-27 235
变量 系统变量： 全局变量 会话变量 自定义变量： 用户变量 局部变量 系统变量 说明： 变量有系统提供，不是用户定义。属于服务器层面 使用的语法： 查看所有的系统变量 SHOW GLOBAL [SESSION] VARIABLES; 查看满足条件的部分系统变量 SHOW GLOBAL [SESSION] variables like "char"; 查看制定的某个系统变量的值 S...
Mysql基础学习（二十一）-- 批量插入数据 1000W条
qq_40990836的博客
10-09 230
创建dept 表格 CREATE TABLE dept( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT "", loc VARCHAR(13) NOT NULL DEFAULT "" ) ...
Mysql 基础学习（一） -- 基础查询
qq_40990836的博客
09-17 224
最近感觉自己mysql的基础不够结实。准备重温一下。记录一下吧 查询语句 SELECT 查询列表 FROM TABLE 去重 distinct 关键字 SELECT DSITINCT 查询列表 FROM TABLE 拼接字符串 注意 其中如果含有NULL的话所有的值都会成为NULL SELECT CONCAT(s1,s2，...) FROM TABLE 条件运算...
Mysql 基础学习（二十）-- 慢查询日志
qq_40990836的博客
10-09 218
慢查询日志 Mysql的慢查询日志是Mysql 提供的一种日志记录，它用来记录在Mysql中响应时间超过 阈值 的语句，具体指运行时间超过 long_query_time 值得SQL，则会被记录到慢查询日志中，long_query_time得默认值为10，意思是运行 10 秒以上得语句，比如一条 SQL 执行超过 10 秒钟，我们就算慢SQL，收集超过10 秒以上的 SQL, 结合之前 expla...
Mysql基础学习（三）连接查询
qq_40990836的博客
09-19 192
连接查询 含义：又称多表查询，当查询的字段来自于多个表时。就会用到连接查询 笛卡尔乘积现象： 表1 有m行，表2 有n行， 结果 = m*n 行 发生原因：没有有效的连接条件 如何避免： 添加有效的连接条件 按功能分类 内连接： 等值连接 非等值连接 自连接 外连接： 左外连接 右外连接 全外连接 交叉连接 Sql 192 标准 案例1：查询女神名字和对...
Mysql 基础学习(十七) --索引(Index)
qq_40990836的博客
10-08 182
索引（Index） 含义： 索引 是帮助 MYSQL 高效获取数据的数据结构。 索引是一种数据结构。 索引 ：** 排好序的快速查找数据结构。** 在数据之外，数据库系统还维护者满足特点查找算法的数据结构，这些数据结构以某种方式引用（指向） 数据。这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。 BTree 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件...
Mysql基础学习（二十三） -- Mysql 锁机制
qq_40990836的博客
10-10 175
锁 锁是计算机协调多个进程或线程并发访问某一资源的机制 在数据库中，除传统的统计资源，
Mysql基础学习（十八）-- 使用 EXPLAIN 关键字
qq_40990836的博客
10-08 167
什么时EXPLAIN 关键字 使用 explain 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL 时如何处理你得
Mysql 基础（九） -- 类型和约束
qq_40990836的博客
09-26 166
类型 数值型 整型 : trinyint, smallint, mdiumint ,int , bigint 特点： ① 如何不设置无符号还是有符号，默认是有符号， 如果想设置无符号，需要添加 unsigned 关键字 ②如果插入的数值超出了整型的范围，会报 out of range 异常，并插入临界值 ③如果不设置长度，会有默认的长度，长度代表了显示的最大宽度。如果不够会使用0 在左边填充，但...
Mysql基础学习（四）-- 子查询
qq_40990836的博客
09-20 152
子查询 概念： 出现在其他语句内部的 select 语句， 称为子查询或者内查询 内部嵌套其他 select 语句的查询，称为 外查询或者 主查询 例子： SELECT first_name FROM employees WHERE department_id IN( SELECT department_id from departments WHERE location_id = 170...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值