当前位置:Gxlcms > 数据库问题 > python学习_day47_mysql数据库索引相关

python学习_day47_mysql数据库索引相关

时间:2021-07-01 10:21:17 帮助过:12人阅读

普通索引INDEX:加速查找

唯一索引:
    -主键索引PRIMARY KEY:加速查找+约束(不为空、不能重复)
    -唯一索引UNIQUE:加速查找+约束(不能重复)

联合索引:
    -PRIMARY KEY(id,name):联合主键索引
    -UNIQUE(id,name):联合唯一索引
    -INDEX(id,name):联合普通索引

  除此之外还有全文索引,即FULLTEXT,但其实对于全文搜索,我们并不会使用MySQL自带的该索引,而是会选择第三方软件如Sphinx,专门来做全文搜索。

二、索引类型

  索引主要包括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 等索引;

三、创建与删除索引

1、在创建表时创建索引

create table t1(
    id int,
    name char(5),
    age int;
    unique key uni_name(name),           #uni_name为索引名
    index index_age(age),                #index_age为索引名,不需要key
    primary key(id)                      #primary不需要起索引名,起了也不会显示
     );                    

技术分享

2、创建完表后为其添加索引

create table t3(
    id int,
    name char(5),
    age int
  );

create index indx_name on t3(name);  #常用
alter table t3 add index indx_id(id);
alter table t3 add primary key(age);

技术分享

3、删除索引

drop index indx_id on t3;

alter table t3 drop primary key;

  上述第一个删除语法中,因primary key 没有名字,所以删除方式为:drop index ‘primary’ on t3,其他有名字的索引删除方式为:drop index 索引名 on 表名

四、测试索引

  按照如下sql语句创建表s1,后续所有测试均基于此表:

#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,egon,male,concat(egon,i,@oldboy));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

1、加索引可以加快查询效率,但是会降低写的效率

技术分享

五、正确使用索引

  并不是说我们创建了索引就一定会加快查询速度,若想利用索引达到预想的提高查询速度的效果,我们在添加索引时,必须遵循以下问题。

1、范围问题,或者说条件不明确,条件中出现这些符号或关键字:>、>=、<、<=、!= 、between...and...、like

  大于 小于

技术分享

  不等于

技术分享

  between...and

技术分享

  like 

技术分享

2、尽量选择区分度高的字段作为索引,区分度是指的字段中数据的重复性,越重复,区分度变低

技术分享

我们编写存储过程为表s1批量添加记录,name字段的值均为egon,也就是说name这个字段的区分度很低(gender字段也是一样的,我们稍后再搭理它)

回忆b+树的结构,查询的速度与树的高度成反比,要想将树的高低控制的很低,需要保证:在某一层内数据项均是按照从左到右,从小到大的顺序依次排开,即左1<左2<左3<...

而对于区分度低的字段,无法找到大小关系,因为值都是相等的,毫无疑问,还想要用b+树存放这些等值的数据,只能增加树的高度,字段的区分度越低,则树的高度越高。极端的情况,索引字段的值都一样,那么b+树几乎成了一根棍。本例中就是这种极端的情况,name字段所有的值均为egon

#现在我们得出一个结论:为区分度低的字段建立索引,索引树的高度会很高,然而这具体会带来什么影响呢???

#1:如果条件是name=‘xxxx‘,那么肯定是可以第一时间判断出‘xxxx‘是不在索引树中的(因为树中所有的值均为‘egon’),所以查询速度很快

#2:如果条件正好是name=‘egon‘,查询时,我们永远无法从树的某个位置得到一个明确的范围,只能往下找,往下找,往下找。。。这与全表扫描的IO次数没有多大区别,所以速度很慢

3、索引字段不可以参与计算

技术分享

4、and or

#注意:
条件1 and 条件2:查询原理是:首先条件1与条件2都成立的前提下,才算匹配成功一条记录;其次mysql会按先优先判断索引字段的条件,如果按照该条件为真,但锁定的范围很小,或者干脆为假,那我们即便是没有为其他条件的字段添加索引,最终的结果仍然很快

#例如:
若条件1的字段有索引,而条件2的字段没有索引,那么如果在按照条件1查出的结果很少的情况下,即便我们没有为条件2创建索引,最终的查询速度依然很快

若条件1的字段没有索引,而条件2的字段有索引,那么如果在按照条件2查出的结果很少的情况下,即便我们没有为条件1创建索引,最终的查询速度依然很快

技术分享

 在左边条件成立但是索引字段的区分度低的情况下(name与gender均属于这种情况),会依次往右找到一个区分度高的索引字段,加速查询

技术分享

   经过分析,在条件为name=‘egon‘ and gender=‘male‘ and id>333 and email=‘xxx‘的情况下,我们完全没必要为前三个条件的字段加索引,因为只能用上email字段的索引,前三个字段的索引反而会降低我们的查询效率。

技术分享

 5、最左前缀匹配原则,非常重要的原则,对于组合索引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的顺序可以任意调整。

技术分享

 6、其他情况

- 使用函数
    select * from tb1 where reverse(email) = egon;
            
- 类型不一致
    如果列是字符串类型,传入条件是必须用引号引起来,不然...
    select * from tb1 where email = 999;
    
#排序条件为索引,则select字段必须也是索引字段,否则无法命中
- order by
    select name from s1 order by email desc;
    当根据索引排序时候,select查询的字段如果不是索引,则速度仍然很慢
    select email from s1 order by email desc;
    特别的:如果对主键排序,则还是速度很快:
        select * from tb1 order by nid desc;
 
- 组合索引最左前缀
    如果组合索引为:(name,email)
    name and email       -- 命中索引
    name                 -- 命中索引
    email                -- 未命中索引


- count(1)或count(列)代替count(*)在mysql中没有差别了

- create index xxxx  on tb(title(19)) #text类型,必须制定长度
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引(经常使用多个条件查询时)
- 尽量使用短索引
- 使用连接(JOIN)来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值(重复少)不适合建索引,例:性别不适合

六、索引合并与覆盖

1、索引合并

#索引合并:把多个单列索引合并使用

#分析:
组合索引能做到的事情,我们都可以用索引合并去解决,比如
create index ne on s1(name,email);#组合索引
我们完全可以单独为name和email创建索引,然后按照where name=xxx and email=xxx使用 #索引合并

组合索引可以命中:
select * from s1 where name=egon ;
select * from s1 where name=egon and email=adf;

索引合并可以命中:
select * from s1 where name=egon ;
select * from s1 where email=adf;
select * from s1 where name=egon and email=adf;

乍一看好像索引合并更好了:可以命中更多的情况,但其实要分情况去看,如果是name=egon and email=adf,那么组合索引的效率要高于索引合并,如果是单条件查,那么还是用索引合并比较合理

2、索引覆盖

#覆盖索引:
    - 所有字段(条件的,查询结果的等)都是索引字段
    http://blog.itpub.net/22664653/viewspace-774667/

#分析
select age from s1 where id=123 and name = egon; #id字段有索引,但是name字段没有索引
该sql命中了索引,但未覆盖全部。
利用id=123到索引的数据结构中定位到了id字段,但是仍要判断name字段,但是name字段没有索引,而且查询结果的字段age也没有索引
最牛逼的情况是,索引字段覆盖了所有,那全程通过索引来加速查询以及获取结果就ok了

七、查询优化神器explain

  关于explain命令相信大家并不陌生,具体用法和字段含义可以参考官网explain-output,这里需要强调rows是核心指标,绝大部分rows小的语句执行一定很快(有例外,下面会讲到)。所以优化语句基本上都是在优化rows。

技术分享

八、慢日志管理

慢日志
            - 执行时间 > 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);

日志管理

 

python学习_day47_mysql数据库索引相关

标签:效果   相等   ref   flush   二进制日志   而且   管理   查询   views   

人气教程排行