一.什么是索引
索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。
二.索引原理
通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。
三.索引的数据结构
每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,b+树应运而生。
b+树性质
1.索引字段要尽量的小
2.索引的最左匹配特性
四.MySQL索引管理
1.功能
索引的功能就是加速查找;
mysql中的primary key,unique,联合唯一也都是索引,这些索引除了加速查找以外,还有约束的功能;
2.MySQL索引分类
普通索引INDEX:加速查找 唯一索引: -主键索引PRIMARY KEY:加速查找+约束(不为空、不能重复) -唯一索引UNIQUE:加速查找+约束(不能重复) 联合索引: -PRIMARY KEY(id,name):联合主键索引 -UNIQUE(id,name):联合唯一索引 -INDEX(id,name):联合普通索引
3.索引的两大类型(hash,btree)
#我们可以在创建上述索引的时候,为其指定索引类型,分两类 hash类型的索引:查询单条快,范围查询慢 btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它) #不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引; MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引; Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引; NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引; Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
4.正确使用索引
覆盖索引:在索引文件中直接获取数据
索引合并:把多个单列索引合并使用
若想利用索引达到预想的提高查询速度的效果,我们在添加索引时,必须遵循以下原则
1.最左前缀匹配原则,非常重要的原则,必须按照从左到右的顺序匹配 2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式 3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录 4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
- 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 #排序条件为索引,则select字段必须也是索引字段,否则无法命中 - order by select name from s1 order by email desc; 当根据索引排序时候,select查询的字段如果不是索引,则不走索引 select email from s1 order by email desc; 特别的:如果对主键排序,则还是走索引: select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为:(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引 - count(1)或count(列)代替count(*)在mysql中没有差别了 - create index xxxx on tb(title(19)) #text类型,必须制定长度
- 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引(经常使用多个条件查询时) - 尽量使用短索引 - 使用连接(JOIN)来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值(重复少)不适合建索引,例:性别不适合