Python MySQL(索引的使用补充)

索引

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