Python之MySQL优化(上)_Python

1.索引优化

1.1 索引的分类

分类角度	索引名称
数据结构	B+树，Hash索引，B-Tree 等
存储层面	聚簇索引，非聚簇索引
逻辑层面	主键索引，普通索引，组合索引，唯一索引，空间索引

1.2 回表

假设我们执行一条查询语句
select * from person where ID = 6,因为直接使用的是主键ID查询，所以就会用主键索引，由于主键索引直接关联了整行所有数据，所以，引擎只要执行一次就能查询出结果。
如果执行的sql语句是非主键索引

select * from person where age = 18

上述语句会走age的普通索引，索引先根据age搜索等于18的索引记录，找到ID=10的记录，然后再到主键索引搜索一次，然后拿出需要查询的数据。
从普通索引查出主键索引，然后查询出数据的过程叫做回表。由于回表需要多执行一次查询，这也是为什么主键索引要比普通索引要快的原因，所以，我们要尽量使用主键查询。

1.3 覆盖索引

我们通常创建索引的依据都是根据查询的where条件，但是这只是我们通常的做法，我们根据上面的分析可以知道，如果要想查询效率高，第一，使用主键索引，第二，避免回表，也就是尽可能的在索引中就能获取想要的数据。如果一个索引包含了需要查询的字段，那么我们就叫做"覆盖索引"

建立复合索引：

create index idx_staffs_nameAgePos on staffs(name,age,pos);
-- idx_为建立的复合索引
-- （name,age,pos）是为这三个字段所创建的索引

1.4 索引的口诀

全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
like百分写最右,覆盖索引不写星
不等空值还有or,索引失效要少用
varchar引号不可丢,SQL高级也不难

2.0 索引优化案例

2.1 单表优化

#建表
create table article(
    id int unsigned not null primary key auto_increment,
    author_id int unsigned not null,
    category_id int unsigned not null,
    views int unsigned not null,
    comments int unsigned not null,
    title varchar(255) not null,
    content text not null
);
#插入数据
insert into article(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) values 
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
--需求：查询category_id为1且comments大于1的情况下,views最多的article_id

select * from article where category_id = 1 and comments>1 order by views desc;

进行索引优化：
在这里插入图片描述
查询结果出现文件内排序
优化方案，为字段category_id，views添加索引:

添加组合索引后，文件内排序消失。

2.2 双表优化

-- 建表
-- 商品类别表
create table class(
    id int unsigned not null primary key auto_increment,
    card int unsigned not null
);
-- 图书表
create table book(
    bookid int unsigned not null auto_increment primary key,
    card int unsigned not null
);

-- 驱动表的概念，mysql中指定了连接条件时，满足查询条件的记录行数少的表为-驱动表；如未指定查询条件，则扫描行数少的为驱动表。mysql优化器就是这么粗暴以小表驱动大表的方式来决定执行顺序的。

两个表关联查询：
（1）当使用左连接时，往右表添加索引
（2）当使用右连接时，往左表添加索引
（3）当使用内连接时，可添加组合索引如下：
在这里插入图片描述

3.0 Join语句优化

3.1 关联查询的算法

我们在使用数据库查询数据时，有时一张表并不能满足我们的需求，很多时候都涉及到多张表的连接查询。今天，我们就一起研究关联查询的一些优化技巧。在说关联查询优化之前，我们先看下跟关联查询有关的几个算法：

关联查询的算法
• Nested-Loop Join 算法
• Block Nested-Loop Join 算法

Nested-Loop Join 算法
一个简单的 Nested-Loop Join(NLJ) 算法一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。
我们试想一下，如果在被驱动表中这个关联字段没有索引，那么每次取出驱动表的关联字段在被驱动表查找对应的数据时，都会对被驱动表做一次全表扫描，成本是非常高的（比如驱动表数据量是 m，被驱动表数据量是 n，则扫描行数为 m * n ）。
好在 MySQL 在关联字段有索引时，才会使用 NLJ，如果没索引，就会使用 Block Nested-Loop Join。我们先来看下在有索引情况的情况下，使用 Nested-Loop Join 的场景（称为：Index Nested-Loop Join）。
因为 MySQL 在关联字段有索引时，才会使用 NLJ，因此本节后面的内容所用到的 NLJ 都表示 Index Nested-Loop Join。如下：

在这里插入图片描述
怎么确定这条 SQL 使用的是 NLJ 算法？
从执行计划中可以看到这些信息：
• 驱动表是 t2，被驱动表是 t1。原因是：explain 分析 join 语句时，在第一行的就是驱动表；选择 t2 做驱动表的原因：如果没固定连接方式优化器会优先选择小表做驱动表。所以使用 inner join 时，前面的表并不一定就是驱动表。
• 使用了 NLJ。原因是：一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer （***）；则表示使用的 join 算法是 NLJ。

Block Nested-Loop Join 算法
Block Nested-Loop Join(BNL) 算法的思想是：把驱动表的数据读⼊到 join_buffer 中，然后扫描被驱动表，把被驱动表每⼀⾏取出来跟 join_buffer 中的数据做对⽐，如果满⾜ join 条件，则返回结果给客户端。

我们⼀起看看下⾯这条 SQL 语句：

explain select * from s1 inner join s2 on s1.id=s2.id;

在这里插入图片描述
在 Extra 发现 Using join buffer (Block Nested Loop)，这个就说明该关联查询使⽤的是 BNL 算法。

在没有建立索引的情况下，(BNL)会比(NLJ)磁盘扫描更少，因此是更优的选择。因此对于 MySQL 的关联查询，如果被驱动表的关联字段没索引，会使⽤ BNL 算法。

3.2 关联查询优化：

使用临时表优化
由于表 s1 和表 s2 的字段 id 都没索引，因此使⽤的是效率⽐较低的 BNL 算法。现在⽤临时表的⽅法对这条 SQL 进⾏优化：

CREATE TEMPORARY TABLE `s1_tmp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL,`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMME NT '记录创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UP DATE CURRENT_TIMESTAMP COMMENT '记录更新时间', PRIMARY KEY (`id`), KEY `idx_a` (`a`), KEY `idx_b` (b)) ENGINE=InnoDB ;
#把 s1 表中的数据写⼊临时表 t1_tmp 中：
insert into s1_tmp select * from s1;
#执⾏ join 语句
select * from t1_tmp join t2 on t1_tmp.b= t2.b;
#结果显示：
Extra 没出现 “Block Nested Loop”，说明使⽤的是 Index Nested-Loop Join，并且扫描⾏数也⼤⼤降低了
所以当遇到 BNL 的 join 语句，如果不⽅便在关联字段上添加索引，不妨尝试 创建临时表，然后在临时表中的关联字段上添加索引，然后通过临时表来做关 联查询