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 来查找，最好用第三方工具来查找
      比如数据是

      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;