Python MySQL(索引的使用补充)
索引
- 作用
- 约束
- 加速查找
- 索引
- 普通索引:加速查找
- 主键索引:加速查找 + 不能为空 + 不能重复
- 唯一索引:加速查找 + 不能重复
- 联合索引 (联合唯一索引)
- 联合主键索引(多列联合起来作为主键索引)
- 联合唯一索引(多列联合起来作为唯一索引)
- 联合普通索引
- 加速查找(索引就像一个目录一样,如果没有目录就要从头到尾看一遍)
- 无索引:从前到后依次查找
- 索引:
会创建一个额外文件,如果查找时,先到额外文件茶,查到了再到数据库里找 - 索引种类
- hash索引:把内容转换成hash值,把它的值和在数据库的地址存储成一个表,但位置和原来数据库的数据位置不同
优点:查找单值时非常快
缺点:不能按范围查找,如果想要按ID查到,但hash索引位置不固定就不能查找出来 - btree索引:
- 把数据转换成数字然后放进二叉树中,假如是1024不需要从1查到1024查1024次,只需要2**10,查找10次
- hash索引:把内容转换成hash值,把它的值和在数据库的地址存储成一个表,但位置和原来数据库的数据位置不同
- 建立索引:
- 额外的文件保存特殊的数据结构
- 查询快;插入更新删除慢,因为在数据库和索引中同时改动
- 命中索引,创建索引并使用了索引
select * from userinfo where name = '小明'; 快
select * from userinfo where name like '小明'; 慢 - 主键一般创建表的时候都会创建
- 普通索引:
create index 索引名称 on 表(列名)
能创建就能删除 drop index 索引名称 on 表; - 唯一索引:(创建表的时候也能创建)
create unique index 索引名称 on 表(列名)
drop unique index 索引名称 on 表; - 联合索引:(普通联合索引和唯一联合索引)
create (unique) index 索引名称 on 表(列名,列名)
drop (unique) index 索引名称 on 表;
最左前缀匹配:
create index 索引名称 on 表(id,name) ;
select * from userinfo where id = 9;
select * from userinfo where name = '小明';
select * from userinfo where id = 9 and name = '小明'; # 前三种都会使用索引
select * from userinfo where name='小明' and id = 9; # 最后一种不是使用索引
# 假如列名有ABC三种 A、B、C、AB、AC、BC都会使用索引BA、CB、CA之类的都不都会使用索引,以此类推
组合索引效率>索引合并,但索引合并比较灵活,看情况使用 - 覆盖索引:(不是真的索引,是一种专有名词)
select id from t1i where id = 9; (假设id已经创建了索引)
这不同于 * from 从数据表中查询, id from 是直接在硬盘中创建的那个id索引文件中找条件ID - 索引合并:(也不是真的索引,是一种专有名词)
把多个单列索引合并使用
select * from t1 where id = 9 and name = '小明' ; (假设ID和NAME都创建了索引)
这样同时使用两个单列索引的方法就叫索引合并
- 索引的注意点:
- 频繁使用的列表才建立索引,否则平时的插入更新删除会变慢
- 如果数据被频繁查询到最好别用 like 来查找,最好用第三方工具来查找 比如数据是
ID DATE 1 英国政府表示,不会提前宣布进行全国隔离,因为会导致英国民众隔离“行为疲劳 2 这样同时使用两个单列索引的方法就叫索引合并 3 英国政府表示,不会提前宣布进行全国隔离,因为会导致英国民众隔离“行为疲劳 - ID 1,3 数据date内容都是:英国政府表示,不会提前宣布进行全国隔离,因为会导致英国民众隔离“行为疲劳”。)
- like查询就是 select * from t1 where date like '%英国政府%' ; (比较慢)
- 而第三方工具 会把创建一个文件解析内容把“英国政府”,“全国隔离”,“行为疲劳” 记录到 ID 1,3然后select * from t1 where id in (1,3);
- 尽量不要使用函数来操作数据库(如翻转),不然会改变保存数据的方式,如果想要修改显示的方式可以在Python中修改显示
select * from t1 where reverse(name) = '小明': - OR(假设ID和name有索引)
- select * from t1 where id = 9 or title = '时间'; (假设ID有索引,title没有索引,查找也会很慢)
- 但是如果select * from t1 where id = 9 or title='时间' and name = '小明' (但是如果是这样就会用ID和NAME进行索引,跳过title)
- 查询时的数据类型要和列类型一样,不然搜索时也会变慢
假如 name 列是 char 类型,如果用 select * from t1 where name = 9; 就会很慢 - 避免使用select *
- count(1)或者count(列名)来代替count(*)
- 创建表时尽量用char来代替char
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引(如果经常用到的话)
- 尽量使用短索引:比如都是9位数字的邮箱就可以只取前几位来建立索引,create index xx on t1(title(9))
- 使用连接(JOIN)来代替子查询
- 连表时注意条件类型要一致
- 索引散列值(重复少)不适合建立索引,比如:性别 就不合适
- 时间:
虽然索引有这么多规矩,但最终判断命中索引的正确还是要通过时间,时间短才是王道。
不过也不能每次都执行语句来检测每一个的时间,然后在判断哪个时间短,所以就要用到一个工具MySQL自带的执行计划:让MySQL预估执行操作的时间(一般是正确的)- explain select * from t1;
查询出来有个type,它说明SQL语句是用什么方式访问表的,有以下几种方式,一般是通过访问方式来判断语句的查询速度
性能排序为 all<index<range<index_merge<ref_or_null<ref<eq_ref<system/const
- ALL : 全表扫描,把数据表从头到尾找一遍 如:select * from t1;
但是也有特例,如limit explain select * from t1 limit 1;
# 这个的访问类型也是ALL 但是它的执行速度也很快,因为他如果找到了所要的数据就不继续扫描了,所以访问方式也只是做个参考 - INDEX:全索引扫描,对索引从头到尾找一遍 如:select id from t1;
- RANDE:对索引列进行范围查找 如 select * from t1 where id = 9;
- INDEX_MERGE : 合并索引,使用多个单列索引搜索 如:select * from t1 where id = 9 or name = '小明';
- REF:根据索引查找一个或多个值 如 : select * from t1 where name = '小明'
- EQ_REF:连接时使用primary key 或 unique类型
- CONST:常量(表最多有一个匹配行,因为仅有一行,在这行的列值可以被优化器剩余部分认为是常数,const表很快,因为它们只读取一次) 如:select id from t1 where id = 2;
- SYSTEM:系统(表只有一行=系统表。这是const联接类型的一个特例) 如:select * from (select id from t1 where id = 2) as A;
- explain select * from t1;