python中的索引规则_Python- 索引 B+数 比如书的目录

1.索引

为何要有索引?

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，

在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，

因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

聚集索引

叶子节点整个行中的额数据

辅助索引

叶子节点存储的是索引字段 和主键值

覆盖索引

回表

联合索引

正确使用索引

1.区分度高字段

2.索引的字段数据量应该尽可能小

3.优先使用聚集索引

4.联合索引 创建时 把区分度高放左边

查询时 尽可能使用最左边的索引

索引越多越好?

索引能够提升效率

同时降低了写入速度

增加额外的磁盘占用

2.元类

用于创建类的类 叫元类 默认元类都是type

主要控制类的创建__init__ 类的实例化__call__

3.单例

一个类只有一个实例

什么时候用,大家公用一个打印机,没有必要每个人创建新的打印机对象 共用一个对象即可

应该被索引的字段

需要排序操作的字段(order_by)

需要比较操作的字段(> < >= <=)

需要过滤操作的字段( filter exclude)

不应该索引的字段

经常更新和删除 的字段不应该被索引

默认索引的规则

主键必定是索引

外键 默认是索引 (可以不配置)

添加索引的方法:

属性中定义

meta中定义

from django.db import models

from apis.models import App

# Create your models here.

class User(models.Model):

# open_id

open_id = models.CharField(max_length=64, unique=True)

# 昵称

nickname = models.CharField(max_length=256, db_index=True)

# 关注的城市

focus_cities = models.TextField(default='[]')

# 关注的星座

focus_constellations = models.TextField(default='[]')

# 关注的股票

focus_stocks = models.TextField(default='[]')

# 菜单app

menu = models.ManyToManyField(App)

class Meta:

indexes = [

# models.Index(fields=['nickname'])

models.Index(fields=['open_id', 'nickname'])

]

def __str__(self):

return self.nickname

def __repr__(self):

return self.nickname

===============================

索引

01 为什么要用索引

因为项目运行中,查询操作非常频繁,为了提高用户体验,要提高查询的速度,

如何提高就靠索引(大多数性能问题都是慢查询 提到加速查，就必须用到索引)

作用：

- 约束

- 加速查找

02 什么是索引

搜索引导, 所以是一种单独的,物理的 有序的 存储结构,用于加速查询

例如: 字典 书的目录 车票上的车厢号

索引的本质原理就是通过不断地缩小查询范围，来降低io次数从而提升查询性能

强调：一旦为表创建了索引，以后的查询都会先查索引，再根据索引定位的结果去找数据

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，

也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

03 索引的影响

1.加速查询

2.降低写入(增加 删除 修改)速度

3.会额外占用磁盘空间

4.索引的分类

1.聚集索引 就是主键索引 (primary key )

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

行中的完整记录存在聚集索引的叶子节点上

2.辅助索引 (unique,index)

- 普通索引：加速查找

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

叶子节点 存储索引字段的值 还有 主键的值

使用辅助索引时 会产生两种现象

1.回表 要查的数据就不在辅助索引中 需要到聚集索引中查找

2.覆盖索引 要查的数据就在辅助索引中

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

- drop index 索引名称 on 表名

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

- drop unique index 索引名称 on 表名

3 联合索引（多列）：

- 联合主键索引

- 联合唯一索引

- 联合普通索引

为是什么使用它

降低资源的占用 , 降低增删改的时间 会比单个字段的索引快

建立联合索引时 应该把区分度高放最左边 区分度低的依次往右放

按照区分度的高低 从左往右 依次排列

查询中 应该优先使用左边的索引

使用and时 无所谓书写顺序 会自动找区分度最高的

注意联合索引在查询时 如果压根没用到最左侧索引 不能加速查询

组合索引(最左前缀匹配)：

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

- drop unique 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='alex@qq.com';

组合索引效率 > 索引合并

组合索引

- (name,email,)

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

select * from userinfo3 where name='alex';

索引合并：

- name

- email

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

select * from userinfo3 where name='alex';

select * from userinfo3 where email='alex';

名词：

覆盖索引：

- 在索引文件中直接获取数据

索引合并：

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

索引的正确使用姿势

命中索引 ,条件中有索引字段匹配上

1.无论索引如何设计 无法降低范围查询的查询速度

select count(*) from usr where id > 1;

即使命中索引也无法提高效率

2.索引不应该加在值重复度很高的字段上 应该加在重复度低的字段

3. 使用and时 当 条件中出现多个索引命中时 会自定找一个区分度最高的索引来使用

4.使用or的时候 如果两边都有索引 会使用索引,但是注意 or两边都要执行 顺序依然从左往右

只有一边有索引会不会使用索引? 不会使用 无法加速查询

5.优化查询 不仅仅要加索引,sql语句也需要优化 使其能命中索引

你的条件中应该使用区别度高的索引

正常开发时

优先使用聚集索引

再次 使用联合索引 如果你的条件不包含最左侧索引 不能加速查询 这时候就应该使用单个字段索引

创建索引:

create index 索引名称 on usr(索引字段);

索引未命中 详见49-2

并不是说我们创建了索引就一定会加快查询速度，

若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题

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

>、>=、<、<=、!= 、between...and...、like、

大于号、小于号

不等于！=

between ...and...

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

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的顺序可以任意调整。

7 其他情况

总结

#1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快

比如create index idx on s1(id);会扫描表中所有的数据，

然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了。

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，

这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。

因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），

如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，

如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

-----wusir

2. 频繁查找的列创建索引

- 创建索引

- 命中索引 *****

- 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 = 'seven@live.com';

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

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

select * from tb1 where nid = 1 or name = 'seven@live.com' 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 -- 不使用索引

3. 查询优化神器-explain

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，

这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快

（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

执行计划：让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: const(走索引)

4. DBA工作

慢查询优化的基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE

1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

3.order by limit 形式的sql语句让排序的表优先查

4.了解业务方使用场景

5.加索引时参照建索引的几大原则

6.观察结果，不符合预期继续从0分析

慢日志

- 执行时间 > 10

- 未命中索引

- 日志文件路径

配置：

- 内存

show variables like '%query%'

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

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

5. ******分页*******

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

)

c. *****闫龙*****：

id不连续，所以无法直接使用id范围进行查找

1. ORM框架：SQLAlchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，

简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

- 作用：

1. 提供简单的规则

2. 自动转换成SQL语句

- DB first: 手动创建数据库以及表 -> ORM框架 -> 自动生成类

- code first： 手动创建类、和数据库 -> ORM框架 -> 以及表

a. 功能

- 创建数据库表

- 连接数据库（非SQLAlchemy,pymyql,mysqldb,....）

- 类转换SQL语句

- 操作数据行

增

删

改

查

- 便利的功能