python怎么建立mysql数据库索引_python之路_mysql数据库索引相关

索引本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。索引的主要的功能就是加速查找。

一、mysql的常见索引

普通索引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 default1;while(i<3000000)do

insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));

set i=i+1;

endwhile;

END$$#$$结束

delimiter ; #重新声明分号为结束符号

#3. 查看存储过程

show create procedure auto_insert1\G#4. 调用存储过程

call auto_insert1();

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

五、正确使用索引

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

1、范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、

大于 小于

不等于

between...and

like

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

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

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

而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用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会一直向右匹配直到遇到范围查询(>、 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 namefroms1 order by email desc;

当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢

select emailfroms1 order by email desc;

特别的：如果对主键排序，则还是速度很快：

select* fromtb1 order by nid desc;-组合索引最左前缀

如果组合索引为：(name,email)

nameand 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%';

setglobal 变量名 =值-配置文件

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-log1. 启用#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=1slow-query-log-file=slow.log

long_query_time=3查看慢查询日志

测试:BENCHMARK(count,expr)

SELECT BENCHMARK(50000000,2*3);

日志管理