索引的使用
数据准备
# 执行前3步即可，因数据量庞大，创建时间可能超级长！！！可以另打开一个数据表用来查询数据个数 #1. 准备表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 创建存储过程，实现批量插入记录 delimiter $$ #声明存储过程的结束符号为$$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<300000)do insert into s1 values(i,'wuye','female',concat('wuye',i,'@blog')); set i=i+1; end while; END$$ #$$结束 delimiter ; #重新声明分号为结束符号 #3. 调用存储过程 call auto_insert1(); #4. 查看存储过程 show create procedure auto_insert1\G
使用索引前，不妨先自己查询一下看看执行时间，当索引创建成功后，再利用索引查询看一下时间对比啦！
索引介绍
# 索引的优势 # 根据带有索引的字段查询速度非常快 # 索引的劣势 # 创建索引同样占用磁盘空间，索引越多，占用内存越多 # 索引越多，写入数据越慢 # 删除索引并不会删除创建索引的数据结构，内存不会变小
# 索引的原理 # block 磁盘预读原理 # for line in f # 4096个字节 # 读硬盘的io操作的时间非常的长，比CPU执行指令的时间长很多 # 尽量的减少IO次数才是读写数据的主要要解决的问题 # 数据库的存储方式 # 新的数据结构 —— 树 # 平衡树 balance tree - b树 # 在b树的基础上进行了改良 - b+树 # 1.分支节点和根节点都不再存储实际的数据了 # 让分支和根节点能存储更多的索引的信息 # 就降低了树的高度 # 所有的实际数据都存储在叶子节点中 # 2.在叶子节点之间加入了双向的链式结构 # 方便在查询中的范围条件 # mysql当中所有的b+树索引的高度都基本控制在3层 # 1.io操作的次数非常稳定 # 2.有利于通过范围查询 # 什么会影响索引的效率 —— 树的高度 # 1.对哪一列创建索引，选择尽量短的列做索引 # 2.对区分度高的列建索引，重复率超过了10%那么不适合创建索引
# 聚集索引和辅助索引 # 在innodb中，聚集索引和辅助索引并存的 # 聚集索引 -- 主键 更快 # 数据直接存储在树结构的叶子节点 # 辅助索引 -- 除了主键之外所有的索引都是辅助索引，稍慢 # 数据不直接存储在树中 # 在myisam中，只有辅助索引，没有聚集索引
# 索引的种类 # primary key,主键,默认创建聚集索引 约束的作用：非空 + 唯一 # 联合主键 # unique 自带索引 辅助索引 约束的作用：唯一 # 联合唯一 # index 辅助索引 没有约束作用 # 联合索引
索引的创建与删除
# 创建单个索引 # 注意：如果表中数据越多，创建索引越慢，索引越多，占用磁盘空间越多，且写入数据越慢 create index 索引名 on 表名(需要创建索引的字段); create index ind_id on t1(id); # 在 t1 表为 id 字段 创建索引，索引名为 ind_id; # 删除索引 注意：删除索引并不会删除创建索引的数据结构 drop index 索引名 on 表名; drop index ind_id on t1; # 在 t1 表中删除索引名为 ind_id 的索引； # 创建联合索引
# 注意：使用联合索引时要命中索引必须要有字段1，否则索引不生效 create index 索引名 on 表名(字段1,字段2...);
索引的命中
# 以email为条件查询 # 不添加索引的时候肯定慢 # 查询的字段不是索引字段，也慢 # id作为条件的时候 # 如果不加索引，速度慢 # 加了索引，速度快 # 索引不生效的原因 # 要查询的数据的范围大 # > < >= <= != # between and # select * from 表 order by age limit 0,5; # 使用limit 速度慢 # select * from 表 where id between 1000000 and 1000005; # 使用between速度快 # like # 结果的范围大 索引不生效 # 如果 abc% 索引生效，%abc索引就不生效 # 如果一列内容的区分度不高，索引也不生效 # name列 # 索引列不能在条件中参与计算 # select * from s1 where id*10 = 1000000; 索引不生效 # select * from s1 where id = 1000000*10; 索引生效 # 对两列内容进行条件查询 # and and条件两端的内容，优先选择一个有索引的，并且树形结构更好的，来进行查询 # 两个条件都成立才能完成where条件，先完成范围小的缩小后面条件的压力 # select * from s1 where id =1000000 and email = 'wuye1000000@blog'; # or or条件的，不会进行优化，只是根据条件从左到右依次筛选 # 条件中带有or的要想命中索引，这些条件中所有的列都是索引列 # select * from s1 where id =1000000 or email = 'wuye1000000@blog'; # 联合索引 # create index ind_mix on s1(id,name,email); # select * from s1 where id =1000000 and email = 'wuye1000000@blog'; # 在联合索引中如果使用了or条件索引就不能生效 # select * from s1 where id =1000000 or email = 'wuye1000000@blog'; # 最左前缀原则 ：在联合索引中，条件必须含有在创建索引的时候的第一个索引列 # select * from s1 where id =1000000; 能命中索引 # select * from s1 where email = 'wuye1000000@blog'; 不能命中索引 # (a,b,c,d) # a,b # a,c # a # a,d # a,b,d # a,c,d # a,b,c,d # 在整个条件中，从开始出现模糊匹配的那一刻，索引就失效了 # select * from s1 where id >1000000 and email = 'wuye1000001@blog'; # select * from s1 where id =1000000 and email like 'wuye%';
# 什么时候用联合索引 # 只对 a 对abc 条件进行索引 # 而不会对b，对c进行单列的索引 # 单列索引 # 选择一个区分度高的列建立索引，条件中的列不要参与计算，条件的范围尽量小，使用and作为条件的连接符 # 使用or来连接多个条件 # 在满上上述条件的基础上 # 对or相关的所有列分别创建索引 # 覆盖索引 # 如果我们使用索引作为条件查询，查询完毕之后，不需要回表查，覆盖索引 # explain select id from s1 where id = 1000000; # explain select count(id) from s1 where id > 1000000; # 合并索引 # 对两个字段分别创建索引，由于sql的条件让两个索引同时生效了，那么这个时候这两个索引就成为了合并索引
# 执行计划 : 如果你想在执行sql之前就知道sql语句的执行情况，那么可以使用执行计划 # 情况1： # 30000000条数据 # sql 20s # explain sql --> 并不会真正的执行sql，而是会给你列出一个执行计划 # 情况2： # 20条数据 --> 30000000 # explain sql
# 索引的命中： # 范围，条件的字段是否参与计算(不能用函数) # 列的区分度(长度)，条件and/or # 联合索引的最左前缀问题 # 建表、使用sql语句的时候注意的 # char 代替 varchar # 连表 代替 子查询 # 创建表的时候 固定长度的字段放在前面
原文链接：https://www.cnblogs.com/Eva-J/articles/10126413.html#_label3
