python数据库索引与慢查询优化
索引
索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查,在MySQL中也叫做'键'或者"key",是存储引擎用于快速找到记录的一种数据结构.索引对于良好的性能非常关键.尤其是当表中的数据量越来越大时.索引对于性能的影响愈发重要.减少io次数.加速查询.(其中primary key和unique key,除了有加速查询的效果之外,还有约束的效果,primary key 不为空且唯一,unique key 唯一,而index key只有加速查询的效果,没有约束效果)
索引优化应该是对查询性能优化最有效的手段了.索引能够轻易将查询性能提高好几个数量级
强调: 一旦为表创建了索引,以后的查询最好先查索引,再根据索引定位的结果去找数据
索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少,对查询性能又会产生影响,要找到一个平衡点,这对应用程序的性能至关重要
问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。
索引原理
没有数据的时候建立索引快:
因为如果已经有了很多数据了,你再去根据这些数据建索引,是不是要将数据全部遍历一遍,然后根据数据建立索引
索引建立好之后再添加数据快:
索引是用来加速查询的,那对你写入数据肯定是慢一些了,因为你但凡加入一些新的数据,都需要把索引或者说书的目录重新做一个,所以索引虽然会加快查询,但是会降低写入的效率
索引的影响
1.在表中有大量数据的前提下,创建索引速度会很慢
2.在索引创建完毕后,对表的查询性能会发幅度提升,但是写性能会降低
本质都是: 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说有了这种索引机制,我们可以总是用同一种查找方式来锁定数据
索引的数据结构
怎么做到减少IO，加速查询,每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级,b+树应运而生(B+树是通过二叉查找树,再由平衡二叉树,B树演化而来)
如上图，是一颗b+树，最上层是树根，中间的是树枝，最下面是叶子节点，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，可以看到每个磁盘块包含几个数据项（深蓝色所示，一个磁盘块里面包含多少数据，一个深蓝色的块表示一个数据，其实不是数据，后面有解释）和指针（黄色所示，看最上面一个，p1表示比上面深蓝色的那个17小的数据的位置在哪，看它指针指向的左边那个块，里面的数据都比17小，p2指向的是比17大比35小的磁盘块），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
b+树的查找过程
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。除了叶子节点，其他的树根啊树枝啊保存的就是数据的索引，他们是为你建立这种数据之间的关系而存在的。
b+树性质
1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h或者说层级，这个高度或者层级就是你每次查询数据的IO次数，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
如：你每个叶子节点只存两个数据的情况下，你如果想多加两个数据，你怎么办,所以我们需要将树建的越低越好，因为每个磁盘块的大小是一定的，那么意味着我们单个数据库里面的单个数据的大小越大越好还是越小越好，你想啊，你现在叶子节点的磁盘块，两个数据就沾满了，你数据要是更大的话，你这一个磁盘块就只能放一个数据了亲，这样随着你数据量的增大，你的树就越高啊，我们应该想办法让树的层数低下来，效率才高啊，所以我们应该让每个数据的大小尽可能的小，那就意味着，你每个磁盘块存的数据就越多，你树的层级就越少啊，树就越低啊，对不对。并且数据的数量越大，你需要的磁盘块越多，磁盘块越多，你需要的树的层级就越高，所以我们应该尽可能的用更少的磁盘块来装更多的数据项，这样树的高度才能降下来，怎么才能装更多的数据项啊，当然是你的数据项越小，你的磁盘块盛放的数据量就越多了，所以如果一张表中有很多的字段，我们应该用什么字段来建立索引啊，如果你有id字段、name字段、描述信息字段等等的，你应该用哪个来建立索引啊，当然是id字段了，你想想对不对，因为id是个数字，占用空间最少啊。
索引的最左匹配特性
简单来说就是你的数据来了以后，从数据块的左边开始匹配，在匹配右边的，当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
聚集索引与辅助索引
其实就是主键,在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO，2~4次的IO意味着查询时间只需要0.02~0.04秒。
数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），
聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息
聚集索引
InnoDB存储引擎表是索引组织表,即表中数据按照主键顺序存放,而聚集索引(clustered index)就是按照每张表的主键构造一棵B+树,同时叶子结点存放的即为整张表的行记录数据,也将聚集索引的叶子结点称为数据页.聚集索引的这个特性决定了索引组织表中数据也是索引的一部分.同B+树数据结构一样,每个数据页都通过一个双向链表来进行链接
#如果未定义主键,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚簇索引
#如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引
#由于实际的数据页只能按照一棵B+树进行排序,因此每张表只能拥有一个聚集索引.在多数情况下,查询优化器倾向于采用聚集索引.因为聚集索引能够在B+树索引的叶子节点上直接找到数据.此外由于定义了数据的逻辑顺序,聚集索引能够特别快地访问针对范围值得查询
聚集索引的好处
一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
create table s1(id int not null,name varchar(20),gender char(6),email varchar(50));
explain select * from s1 order by id desc limit 10; #Using filesort，需要二次排序
alter table s1 add primary key(id); #添加主键
二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
alter table s1 drop primary key;
explain select * from s1 where id > 1 and id < 1000000;
alter table s1 add primary key(id);
辅助索引
表中除了聚集索引外其他索引都是辅助索引(Secondary Index,也称为非聚集索引),与聚集索引的区别是:辅助索引的叶子节点不包含行记录的全部数据
叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含一个书签(bookmark).该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据
由于InnoDB存储引擎是索引组织表,因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键如下图
辅助索引的存在并不影响数据在聚集索引中的组织,因此每张表上可以有多个辅助索引,但只能有一个聚集索引.当通过辅助索引来寻找数据时,InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键,然后再通过主键索引来找到一个完整的行记录
举例来说,如果在一棵高度为3的辅助索引树种查找数据,那需要对这个辅助索引树遍历3次找到指定主键,如果聚集索引树的高度同样为3,那么还需要对聚集索引树进行3次查找,最终找到一个完整的行数据所在的页,因此一共需要6次逻辑IO访问才能得到最终的一个数据页
总结
聚集索引
1.纪录的索引顺序与物理顺序相同,因此更适合between and和order by操作
2.叶子结点直接对应数据,从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索引
非聚集索引
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引,需要更多磁盘和内容,多个索引会影响insert和update的速度
mysql索引管理
功能
1. 索引的功能就是加速查找
2. mysql中的primary key,unique联合唯一也都是索引,这些索引除了加速查找以外,还有约束的功能
MySQL常用的索引
普通索引INDEX: 加速查找
唯一索引
主键索引PRIMARY KEY: 加速查找+约束(不为空、不能重复)
唯一索引UNIQUE: 加速查找+约束(不能重复)
联合索引
PRIMARY KEY(id,name): 联合主键索引
UNIQUE(id,name): 联合唯一索引
INDEX(id,name): 联合普通索引
举例: 为某商场做一个会员卡的系统
这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT
那么这个会员编号,作为主键,使用 PRIMARY
会员姓名 如果要建索引的话,那么就是普通的 INDEX
会员身份证号码 如果要建索引的话,那么可以选择 UNIQUE(唯一的,不允许重复)
#除此之外还有全文索引,即FULLTEXT
会员备注信息,如果需要建索引的话,可以选择全文搜索
用于搜索很长一篇文章的时候,效果最好
用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以
但其实对于全文搜索,我们并不会使用MySQL自带的该索引,而是会选择第三方软件如Sphinx,专门来做全文搜索
#其他的如空间索引SPATIAL,了解即可,几乎不用
索引的两大类型hash与btree
索引两种类型
hash类型的索引: 查询单条快,范围查询慢
btree类型的索引: b+树,层数越多,数据量指数级增长(innodb默认支持它)
不同的存储引擎支持的索引类型也不一样
InnoDB: 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引
MyISAM: 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引
Memory: 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引
NDB: 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引
Archive: 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引
创建/删除索引的语法
方法一: 创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方式一
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index ix_name(name) #index没有key
);
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index(name) #index没有key
);
方法二: CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]);
#方式二
create index ix_age on t1(age);
方法三: ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]);
#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);
删除索引: DROP INDEX 索引名 ON 表名字;
#查看: show create table t1;
索引测试
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'eva','female',concat('eva',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#3. 查看存储过程
show create procedure auto_insert1\G
#4. 调用存储过程
call auto_insert1();
在表中已经存在大量数据的前提下,为某个字段段建立索引,建立速度会很慢
create index 索引名 on 表名(要索引的字段)
注意:
innodb表的索引会存放于*.ibd文件中,而myisam表的索引则会有单独的索引文件*.MYI
MySAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址而在innodb中,表数据文件本身就是按照B+Tree(BTree即Balance True)组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录.这个索引的key是数据表的主键,因此innodb表数据文件本身就是主索引,因为inndob的数据文件要按照主键聚集,所以innodb要求表必须要有主键(Myisam可以没有),如果没有显式定义,则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则mysql会自动为innodb表生成一个隐含字段作为主键,这字段的长度为6个字节,类型为长整型
索引命中
1 范围问题,或者说条件不明确,条件中出现这些符号或关键字: >、>=、<、<=、!= 、between...and...、like、
大于号、小于号
如果你写where id >1 and id <1000000;你会发现，随着你范围的增大，速度会越来越慢，会成倍的体现出来。
不等于！=
between ...and...
like=后面如果没有那些特殊字符,通配符之类的,就跟等于是一个效果,精确匹配,使用like的时候,通配符写在最前面,也是需要全匹配一遍,然后在比较字符串的第二个字符,最左匹配的规则
2 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,使用场景不同,这个比例值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
3 =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
4 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大.所以语句应该写成create_time = unix_timestamp(’2014-05-29’)
5 and/or
1、and与or的逻辑
条件1 and 条件2: 所有条件都成立才算成立,但凡要有一个条件不成立则最终结果不成立
条件1 or 条件2: 只要有一个条件成立则最终结果就成立
2、and的工作原理
条件: a = 10 and b = 'xxx' and c > 3 and d =4
索引: 制作联合索引(d,a,b,c)
工作原理: 索引的本质原理就是先不断的把查找范围缩小下来,然后再进行处理,对于连续多个and: mysql会按照联合索引,从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围),加速查询,即按照d—>a->b->c的顺序
3、or的工作原理
条件: a = 10 or b = 'xxx' or c > 3 or d =4
索引: 制作联合索引(d,a,b,c)
工作原理: 只要一个匹配成功就行,所以对于连续多个or: mysql会按照条件的顺序,从左到右依次判断,即a->b->c->d
6 最左前缀匹配原则: 非常重要的原则,对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了,有索引速度也慢),比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整
其他注意事项
避免使用select *
count(1)或count(列) 代替 count(*)
创建表时尽量时 char 代替 varchar
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引(经常使用多个条件查询时)
尽量使用短索引
使用连接(JOIN)来代替子查询(Sub-Queries)
连表时注意条件类型需一致
索引散列值(重复少)不适合建索引,例: 性别不适合
联合索引与覆盖索引
联合索引
create index 索引名 on 表名(字段1,字段2...);
注意建立联合索引的一个原则: 索引是有个最左匹配的原则的,所以建联合索引的时候,将区分度高的放在最左边,依次排下来,范围查询的条件尽可能的往后边放
联合索引的第二个好处是在第一个键相同的情况下,已经对第二个键进行了排序处理
覆盖索引
InnoDB存储引擎支持覆盖索引(covering index,或称索引覆盖),即从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录
使用覆盖索引的一个好处是: 辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作
慢查询优化的基本步骤
先运行看看是否真的很慢,注意设置SQL_NO_CACHE
where条件单表查,锁定最小返回记录表.这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
order by limit 形式的sql语句让排序的表优先查
了解业务方使用场景
加索引时参照建索引的几大原则
观察结果,不符合预期继续从0分析
慢日志管理
慢日志
执行时间 > 10
未命中索引
日志文件路径
配置：
内存
show variables like '%query%';
show variables like '%queries%';
set global 变量名 = 值
配置文件
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf内容：
slow_query_log = ON
slow_query_log_file = D:/....
注意: 修改配置文件之后,需要重启服务
mysql日志管理
======================================================== 错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息 二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作 查询日志: 记录查询的信息 慢查询日志: 记录执行时间超过指定时间的操作 中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放 通用日志： 审计哪个账号、在哪个时段、做了哪些事件 事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等 ======================================================== 一、bin-log 1. 启用 # vim /etc/my.cnf [mysqld] log-bin[=dir\[filename]] # service mysqld restart 2. 暂停 //仅当前会话 SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=1; 3. 查看 查看全部： # mysqlbinlog mysql.000002 按时间： # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" # mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54" # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 按字节数： # mysqlbinlog mysql.000002 --start-position=260 # mysqlbinlog mysql.000002 --stop-position=260 # mysqlbinlog mysql.000002 --start-position=260 --stop-position=930 4. 截断bin-log（产生新的bin-log文件） a. 重启mysql服务器 b. # mysql -uroot -p123 -e 'flush logs' 5. 删除bin-log文件 # mysql -uroot -p123 -e 'reset master' 二、查询日志 启用通用查询日志 # vim /etc/my.cnf [mysqld] log[=dir\[filename]] # service mysqld restart 三、慢查询日志 启用慢查询日志 # vim /etc/my.cnf [mysqld] log-slow-queries[=dir\[filename]] long_query_time=n # service mysqld restart MySQL 5.6: slow-query-log=1 slow-query-log-file=slow.log long_query_time=3 单位为秒 查看慢查询日志 测试:BENCHMARK(count,expr) SELECT BENCHMARK(50000000,2*3);
浙公网安备 33010602011771号