python怎么建立mysql数据库索引,python-day44

1，索引

作用：约束，加速查找

分类：

主键索引：加速查找+不能为空+不能重复

普通索引：加速查找

唯一索引：加速查找+不能重复

联合索引(多列)：联合主键索引，联合唯一索引，联合普通索引

加速查找：

SELECT * FROM tb WHERE name=‘dksl‘　　#从头到尾查找，慢

SELECT * FROM tb WHERE id=999　　#主键查找快

无索引：从前到后依次查找

索引：

id 　　　　创建额外文件(某种格式存储)

name 　　创建额外文件(某种格式存储)

email 　　创建额外文件(某种格式存储) create index ix_name on userinfo3(email);

name email 创建额外文件(某种格式存储)

2，索引种类(某种格式存储)：

hash索引：

单值快

指定查找范围无法用于加速

btree索引：

二叉树

建立索引：

- a. 会生成额外的文件保存特殊的数据结构

- b. 查询快；但插入更新删除慢

- c. 查找时需命中索引才能加速

主键索引：

本身就能加速

普通索引：

- create index 索引名称 on 表名(列名,)

- drop index 索引名称 on 表名

唯一索引：

- create unique index 索引名称 on 表名(列名)

- drop unique index 索引名称 on 表名

组合索引(最左前缀匹配，即WHERE查找时，最左列名必须存在才会命中索引)：

- create index 索引名称 on 表名(列名1,列名2)　　#列名1是最左前缀

- drop index 索引名称 on 表名

例：

- create index ix_name_email on userinfo3(name,email)

- 最左前缀匹配

select * from userinfo3 where name=‘alex‘;

select * from userinfo3 where name=‘alex‘ and email=‘asdf‘;

select * from userinfo3 where email=‘[email protected]‘;

组合索引效率 > 索引合并

组合索引

- (name,email) 　　#按这个建一个索引，以下2个才能命中索引

select * from userinfo3 where name=‘alex‘ and email=‘asdf‘;

select * from userinfo3 where name=‘alex‘;

索引合并：

- name 建一个索引

- email 建一个索引　　#以下4个都能命中索引

select * from userinfo3 where name=‘alex‘ and email=‘asdf‘;

select * from userinfo3 where name=‘alex‘;

select * from userinfo3 where email=‘alex‘;

select * from userinfo3 where email=‘asdf‘ and name=‘alex‘;

覆盖索引：　- 在索引文件中直接获取数据，不需要再查找数据库本身

索引合并：　　- 把多个单列索引合并使用

3，为频繁查找的列创建索引

以下操作会变慢

- like ‘%xx‘

select * from tb1 where email like ‘%cn‘;

- 使用函数

select * from tb1 where reverse(email) = ‘wupeiqi‘;

- or

select * from tb1 where nid = 1 or name = ‘[email protected]‘;

特别的：当or条件中有未建立索引的列才失效，以下会走索引

select * from tb1 where nid = 1 or name = ‘seven‘;

select * from tb1 where nid = 1 or name = ‘[email protected]‘ and email = ‘alex‘

- 类型不一致

如果列是字符串类型，传入条件是必须用引号引起来，不然...

select * from tb1 where email = 999;

- !=

select * from tb1 where email != ‘alex‘

特别的：如果是主键，则还是会走索引

select * from tb1 where nid != 123

- >

select * from tb1 where email > ‘alex‘

特别的：如果是主键或索引是整数类型，则还是会走索引

select * from tb1 where nid > 123

select * from tb1 where num > 123

- order by

select name from tb1 order by email desc;

当根据索引排序时候，选择的映射如果不是索引，则不走索引

特别的：如果对主键排序，则还是走索引：

select * from tb1 order by nid desc;

- 组合索引最左前缀

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

name and email -- 使用索引

name -- 使用索引

email -- 不使用索引

4，时间

执行计划：让mysql预估执行的操作类型(一般正确)

效率对比：

all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

id,email假设做了索引

慢：

select * from userinfo3 where name=‘alex‘

explain select * from userinfo3 where name=‘alex‘ 　　#运行执行计划

type: ALL(代表全表扫描)

select * from userinfo3 limit 1;

快：

select * from userinfo3 where email=‘alex‘

type: ref(代表走索引)

5，DBA工作

慢日志

- 执行时间 > 10秒，记录到慢日志中

- 未命中索引

- 日志文件路径

配置慢日志：

- 基于内存

show variables like ‘%query%‘

show variables like ‘%queries%‘

set global 变量名 = 值 #修改当前配置

slow_query_log = OFF 是否开启慢日志记录

long_query_time = 2 时间限制，超过此时间，则记录

slow_query_log_file = /usr/slow.log 日志文件

log_queries_not_using_indexes = OFF 未使用索引的搜索是否记录

- 基于配置文件

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:/....

注意：修改配置文件之后，需要重启服务来加载它

6，分页

查找会从数据库开头开始找到开始行，再从指定行开始输出指定行数　　a. select * from userinfo3 limit 20,10; 　　b.解决方案　　　　- 超过的页数不让看　　　　- 索引表中扫：　　　　　　select * from userinfo3 where id in(select id from userinfo3 limit 200000,10) 　　　　- 方案：　　　　记录当前页最大或最小ID 　　　　1. 页面只有上一页，下一页　　　　　　# max_id 　　　　　　# min_id 　　　　　　下一页：　　　　　　select * from userinfo3 where id > max_id limit 10; 　　　　　　上一页：　　　　　　select * from userinfo3 where id < min_id order by id desc limit 10; 　　　　2. 上一页 192 193 [196] 197 198 199 下一页　　　　　　select * from userinfo3 where id in ( 　　　　　　select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10 　　　　　　) order by id limit 10; 　　　　c. *****为什么不用between*****：　　　　　　id不连续，所以无法直接使用id范围进行查找