MySQL学习笔记——〇五索引、执行计划

首先,我们先建立一个有3000000条数据的数据库文件,格式如下

         

 

为了操作的便利,我们用Python代码来创建数据文件,py代码如下:

import pymysql
import random

conn = pymysql.connect(
    host = 'localhost',
    database = 'userinfo',
    user = 'root',
    password='',
    charset='utf8'
)

cursor = conn.cursor()
for i in range(1000001,3000000):
    name = str(i)
    emaile = str(i)+'@qq.com'
    gender = random.choice(['男','女'])
    sql="insert into user(name,emaile,gender) values('%s','%s','%s');"%(name,emaile,gender)
    print(i)
    cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()

索引的作用和种类

索引

 索引是一个表的目录,在查找目标前可以现在目录文件中查找索引的位置,以此快速定位查询数据。对于索引来说，会保存在另外的文件当中。

索引的种类

• 普通索引：仅仅加速查询
• 唯一索引：加速查询，但值是唯一的（可以为null）
• 主键索引：减速查询+唯一值+不能为null
• 组合索引：多个字段组成一个索引，专门用于组合搜索，效率要大于索引合并(后面会说到)
• 全文索引:对文本内容进行分词，进行搜索。

索引的使用

索引的增、删、改、查

下面的语句是MySQL里如何创建、删除和查询已有的索引

 还可以创建唯一索引，就是把index前增加关键字unique就可以了

create unique index 索引名 on table(字段);
drop unique index 索引名 on table;

我们先看一下没有建立所以的时候的效果，执行一下下面的代码

select * from user where email='2111111@qq.com';

看看时间

 

 如果我们对emaii字段添加一个索引，重新进行查询

create index ix_email on user(email);
select * from user where email='2111111@qq.com';

再看看时间

 

 是不是快的多（这个0.01已经是大概的时间了），并且数据的子样也不是非常多。如果数据量非常大的话，效果会好得多。

索引引擎支持

索引形式

我们常用的索引的类型有两种：Hash和Btree：InnoDB和MyISAM默认的索引是Btree索引，而Memory默认的索引是Hash索引。

Hash索引是把字段里的数据经过hash转换后放新的文件中，hash值对应的还有数据的存储地址，我们在搜索数据的时候先从hash中定位到数据的地址然后从数据库中取出相应数据。但是Hash表中数据的存储顺序和数据库中的存储顺序是不一定相同的，所以在相较于取但一值来说，进行一个区间数据的索引就会耗时比较长。

Btree索引是以一种类似于二叉树的方式来进行数据存储，所以如果是2**（i-1）个数据最多只需要i次索引就可以获得所需要的数据。

二者的区别

1. hash索引查找数据基本上能一次定位数据，当然有大量碰撞的话性能也会下降。而btree索引就得在节点上挨着查找了，很明显在数据精确查找方面hash索引的效率是要高于btree的；
2. 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；
3. 对于btree支持的联合索引的最优前缀，hash也是无法支持的，联合索引中的字段要么全用要么全不用。提起最优前缀居然都泛起迷糊了，看来有时候放空得太厉害；
4. hash不支持索引排序，索引值和计算出来的hash值大小并不一定一致。

覆盖索引和索引合并

覆盖索引

我们在上面的user表里已经存在了两个索引:id和email，我们进行下面的索引

select id from user where id =111;
select email from user where email='111';

在这里我们只是使用索引文件而没有进行数据库的访问，这个过程就叫做覆盖索引。

覆盖索引的定义：

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

只扫描索引而无需回表的优点：
    1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
    2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
    3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
    4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息。

索引合并

我们的user表里存在id和email两个索引，那么可以把这两个索引联合起来使用

select * from user where id=111 and email = '111@qq.com';

索引合并就是把单个字段的索引合并起来来使用。如果是联合索引，会有一个叫最左前缀匹配的现象。这个最左前缀匹配我们在后面会讲到。但是索引合并的效率没有联合索引高。至于要用联合索引还是索引合并就要以具体的使用环境来定。

索引使用的注意事项

索引可以提高命中的效率，但是并不是创建的索引越多越好，因为虽然能提高查询的消耗时间，但是对于增、删、改的时候，都会增加消耗的时间。所以，具体怎么创建索引好以业务为主。如果某一个自动需要频繁的查询操作，就可以创建索引。

索引的命中

 

------------恢复内容开始------------

今天我们来学习一下MySQL的索引

首先,我们先建立一个有3000000条数据的数据库文件,格式如下

         

 

为了操作的便利,我们用Python代码来创建数据文件,py代码如下:

import pymysql
import random

conn = pymysql.connect(
    host = 'localhost',
    database = 'userinfo',
    user = 'root',
    password='',
    charset='utf8'
)

cursor = conn.cursor()
for i in range(1000001,3000000):
    name = str(i)
    emaile = str(i)+'@qq.com'
    gender = random.choice(['男','女'])
    sql="insert into user(name,emaile,gender) values('%s','%s','%s');"%(name,emaile,gender)
    print(i)
    cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()

索引的作用和种类

索引

 索引是一个表的目录,在查找目标前可以现在目录文件中查找索引的位置,以此快速定位查询数据。对于索引来说，会保存在另外的文件当中。

索引的种类

• 普通索引：仅仅加速查询
• 唯一索引：加速查询，但值是唯一的（可以为null）
• 主键索引：减速查询+唯一值+不能为null
• 组合索引：多个字段组成一个索引，专门用于组合搜索，效率要大于索引合并(后面会说到)
• 全文索引:对文本内容进行分词，进行搜索。

索引的使用

索引的增、删、改、查

下面的语句是MySQL里如何创建、删除和查询已有的索引

 还可以创建唯一索引，就是把index前增加关键字unique就可以了

create unique index 索引名 on table(字段);
drop unique index 索引名 on table;

我们先看一下没有建立所以的时候的效果，执行一下下面的代码

select * from user where email='2111111@qq.com';

看看时间

 

 如果我们对emaii字段添加一个索引，重新进行查询

create index ix_email on user(email);
select * from user where email='2111111@qq.com';

再看看时间

 

 是不是快的多（这个0.01已经是大概的时间了），并且数据的子样也不是非常多。如果数据量非常大的话，效果会好得多。

索引引擎支持

索引形式

我们常用的索引的类型有两种：Hash和Btree：InnoDB和MyISAM默认的索引是Btree索引，而Memory默认的索引是Hash索引。

Hash索引是把字段里的数据经过hash转换后放新的文件中，hash值对应的还有数据的存储地址，我们在搜索数据的时候先从hash中定位到数据的地址然后从数据库中取出相应数据。但是Hash表中数据的存储顺序和数据库中的存储顺序是不一定相同的，所以在相较于取但一值来说，进行一个区间数据的索引就会耗时比较长。

Btree索引是以一种类似于二叉树的方式来进行数据存储，所以如果是2**（i-1）个数据最多只需要i次索引就可以获得所需要的数据。

二者的区别

1. hash索引查找数据基本上能一次定位数据，当然有大量碰撞的话性能也会下降。而btree索引就得在节点上挨着查找了，很明显在数据精确查找方面hash索引的效率是要高于btree的；
2. 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；
3. 对于btree支持的联合索引的最优前缀，hash也是无法支持的，联合索引中的字段要么全用要么全不用。提起最优前缀居然都泛起迷糊了，看来有时候放空得太厉害；
4. hash不支持索引排序，索引值和计算出来的hash值大小并不一定一致。

覆盖索引和索引合并

覆盖索引

我们在上面的user表里已经存在了两个索引:id和email，我们进行下面的索引

select id from user where id =111;
select email from user where email='111';

在这里我们只是使用索引文件而没有进行数据库的访问，这个过程就叫做覆盖索引。

覆盖索引的定义：

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

只扫描索引而无需回表的优点：
    1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
    2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
    3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
    4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息。

索引合并

我们的user表里存在id和email两个索引，那么可以把这两个索引联合起来使用

select * from user where id=111 and email = '111@qq.com';

索引合并就是把单个字段的索引合并起来来使用。如果是联合索引，会有一个叫最左前缀匹配的现象。这个最左前缀匹配我们在后面会讲到。但是索引合并的效率没有联合索引高。至于要用联合索引还是索引合并就要以具体的使用环境来定。

索引使用的注意事项

索引可以提高命中的效率，但是并不是创建的索引越多越好，因为虽然能提高查询的消耗时间，但是对于增、删、改的时候，都会增加消耗的时间。所以，具体怎么创建索引好以业务为主。如果某一个自动需要频繁的查询操作，就可以创建索引。

索引的命中

 索引是否命中决定了能否快速的获取搜索对象。如果没有命中索引，是达不到索引的效果的，我们要先看看前面提到的最左前缀匹配

最左前缀匹配

如果我们把多个字段联合成为一个索引，比方执行一下下面的代码建立一个name和email的联合索引

create index ix_name_emaile on user(name,email)

注意上面的代码里，name是在email的前面，如果用下面的索引方法是可以走索引的

select * from user where name='111';
select * from user where name='111' and email='111@qq.com';

但是直接用email是不行的。

select * from user where email='111@qq.com';

如果是多个字段(超过两个)的联合索引呢?比方是(列1,列2,列3)，那么有下面几种情形：

where 1,2,3
where 1,3
where 1

上面三种情形是可以命中索引的，但是下面的就不行

where 2,3
where 3

所以，如果有些时候就需要用索引合并来替代联合索引。

影响到索引命中的几种情形

like通配符的使用

　　在条件中添加了通配符会影响到索引的使用

索引中添加函数

　　在条件中添加了函数，也会影响到索引的效率，如果必须通过一些函数来实现功能，可以将函数放在类似于Python的代码中操作sql语句来实现。

使用or

　　当or条件中有未建立索引的字段才索引失效，下面的几种情形还是会使用索引的

select * from user where id = 1 or email ='1'；
select * from user where id = 1 or name= '1' and emaile= '1'；

上面的第二条语句,因为id是索引，就会忽略掉第二条name不是索引，所以也是可以使用索引的。

类型不一致

　　索引字段对应的数据类型要和搜索的数据类型一致，（例子中的name不是索引，来演示下）

　　因为我们在定义表的时候规定了name是char类型，但是如果我们使用下面的语句也是可以的

select * from user where name=2222222;

我们在查询的时候把name给的条件是int，但是也是能查到相对应的数据的。

 

 所以在使用检索的时候，一定要注意数据类型的一致性，即便不是索引，也会对遍历的时间有影响。

!= 、>的使用

　　如果在条件中用了上面的符号，也是不走索引的（主键除外）

order by

　　对索引列使用了排序，不走索引(主键除外)

其他注意事项

1. 避免使用select *
2. 尽量使用count(1)来替代count(*)
3. 创建表的时候用char，不要用varchar(字符长度已知)
4. 创建表的时候固定长度的字段放在前面
5. 用组合索引来替代多个单列的索引(经常使用多个条件查询时)
6. 对于散列值的情况（比方性别，只有两个值），不适合使用索引
7. 在join连表的时候，也要类型一致（不一致的时候也是可以连上的，比方一个int，一个char）
8. 尽量使用短索引

　　上面说的短索引点一下,比方是一个text类型,有非常多的字符，这个字段每行数据都做成索引是不现实的，常用的方法是把关键字提出来做成索引，整个过程是通过第三方工具来完成的。

　　如果一个列是text的话，直接创对这个字段建索引是会直接报错的，可以指定前多少个字符

create index xxx on tb(text(15));

就是把前15个字符提出来做成索引。

limit分页

 分页是个基本都要遇到的问题，在数据量比较大的时候不可能一次把所有的数据读上来，现在有几种常见的分页效果，为了简化流程，我们下面把id作为分页的依据。

方法一

最简单，只显示上一页和下一页，效果大概就是下面这样的，实际场所应该比较少见

 

 

 这种方式最简单，只要知道当前页面的最大id和最小id，比方每页都显示20条数据，那么前一页后后一页的思路就是这样的(伪代码,，只说一下思路)

select * from table where id in(select id from table limit idmax,20);
select * from table where id in(select * from table limit idmin-20,20);

方式二

只显示前几页，

 

 

 在点击了某一页以后会出现下面的效果（百度上的效果）

 这种方式就是固定显示10页，进可能保证当前页前面有5个button，后面有4个

方法三

显示前几页和最后一页（博客园首页）

 

 

后两种方法的实现思路差不多，都是在当前的maxId和minId基础上加上每页的id总数和页码差的乘积。然后再进行相关跳转

配合py代码，假设现在显示的是从200000开始的10条数据，
select * from user where id>200000 limit 10;
就是记录好当前页的最大id或最小id max_id,min_id
下一页
select * from user where id>max_id limit 10
上一页
select * from user where id <min_id order by id desc limit 10
要是是那种
1.2.3.4........200的页面按钮,在第一页点击了第4个要怎么办
就要从上位的代码实现
if 跳转页面>现在页面
(跳转页面-现在页面)*页面显示条数 +id_max= 跳转数
select.....where id >跳转数  limit 10
还是基于当前页面的id来显示的分页方式。
如果是往前面跳转就是把上面的方式反过来

执行计划

我们还可以通过执行计划来预判sql代码的执行效率在select的前面加上explain

explain select * from user;

返回了一个表

 

我们主要关注的是那个type的字段 

性能从低到高一次为性能:all<index<range<index_merge<ref_or_null_ref_eq_ref<system/const

all 全表扫描
    特别的如果有limit限制,则在赵奥后不再向下扫描
    
index 
全索引扫描,对索引从头到尾进行扫描
其实index和all的速度差不多,但是如果索引列有重复的值,新建的索引文件行数就会略少一点,就能快一些
但是all如果扫第三列的话不光要在行上移动,还要在列上扫描

range
对索引列进行范围查找 注意这里是索引列
select * from t where name<111
range 包括 between and   in   > >=  < <= 特别要注意!=和>

index_merge
索引合并,使用多个单列索引进行搜索
select * from t where name ='111' or id in (11,12,13)

ref
根据所索引查找一个或多个值
select * from user where email = '111'

EQ_REF
连接使用主键或unique类型EQ这的就是唯一


const
常量
表最多有一个匹配行,因为只有一行,在这行的列值可以被优化器剩余部分认为是常数,const表很快,因为他们只读取一次。

system
系统，表只有一行数据（系统表），这是const链接类型的一个特例

慢日志记录

我们在上一章大概提到过，MySQL有个功能，叫做慢日志记录，可以记录执行时间超过指定时长的sql代码

配置方法

直接改参数

先查一下变量

show variables like '%query%';

 

 关注一下标出来的三个变量

long_query_time　 　表示指定时长,超过该时长的指令会被记录在日志中

slow_query_log　   　表示是否开启慢日志记录

slow_query_log_file　表示日志存储路径

我们把时长设置为1.0,并且启用日志记录

set global long_query_time=1.0;
set global long_query_log=ON;

然后做一个超过1s的搜索

select * from user where name='1234567';

执行时间大概是1.7s

 

 然后我们看一看日志文件

MySQL, Version: 5.7.24 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
MySQL, Version: 5.7.24 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2020-02-14T10:09:48.175254Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 1.725777  Lock_time: 0.000797 Rows_sent: 1  Rows_examined: 3000000
use userinfo;
SET timestamp=1581674988;
select * from user where name='1234567';

可以发现有相关记录。

或者我们直接把配置文件里写上相关配置

long_query_time=1.0

slow_query_log=ON

slow_query_log_file=path

然后在启动MySQL服务时指定好相关配置文件的路径

mysqld --default-file='path'

也是可以的。

对了，在修改配置文件之前一定要记得先备份文件，修改以后要重启服务。