mysql索引
目录
1. 索引类型
普通索引:最基本的索引,它没有任何限制,用于加速查询。
唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
主键索引:是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。
全文索引:主要用来查找文本中的关键字,而不是直接与索引中的值相比较。
联合(组合)索引:指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合。特点就是第一个字段一定是有序的。
索引优化策略
说明:MySQL的优化主要分为结构优化(Scheme optimization)和查询优化(Query optimization)。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# 1、最左前缀匹配原则,上面讲到了 # 2、主键外键一定要建索引 # 3、对 where,on,group by,order by 中出现的列使用索引 # 4、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例, # 5、为较长的字符串使用前缀索引 ''' # 6、不要过多创建索引, 权衡索引个数与DML之间关系,DML也就是插入、删除数据操作。 这里需要权衡一个问题,建立索引的目的是为了提高查询效率的,但建立的索引过多, 会影响插入、删除数据的速度,因为我们修改的表数据,索引也需要进行调整重建 ''' ''' # 7、对于like查询,”%”不要放在前面。 SELECT * FROMhoudunwangWHEREunameLIKE'后盾%' -- 走索引 SELECT * FROMhoudunwangWHEREunameLIKE "%后盾%" -- 不走索引 ''' ''' # 8、查询where条件数据类型不匹配也无法使用索引 字符串与数字比较不使用索引; CREATE TABLEa(achar(10)); EXPLAIN SELECT * FROMaWHEREa="1" – 走索引 EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因 '''
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
建索引原则: 1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配, 比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的, 如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。 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)的索引,那么只需要修改原来的索引即可 建索引原则
2. MyISAM实现索引
介绍
1. MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。
2. MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引。
3. 如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
4. MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。
原理图
1. 这里设表一共有三列,假设我们以Col1为主键,则下图是一个MyISAM表的主索引(Primary key)示意。
2. 可以看出MyISAM的索引文件仅仅保存数据记录的地址。
3. InnoDB索引实现
注:虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。
区别1:InnoDB的数据文件本身就是索引文件
1. 从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。
2. 而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。
3. 这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
说明:
可以看到叶节点包含了完整的数据记录,这种索引叫做聚集索引。
因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有)
如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键
如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
区别2:InnoDB的辅助索引data域存储相应记录主键的值而不是地址
1. 换句话说,InnoDB的所有辅助索引都引用主键作为data域。
2. 这里以英文字符的ASCII码作为比较准则。
3. 聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引
4. 首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
为什么不建议使用过长的字段作为主键
1. 例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键
2. 因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。
3. 再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,
4. 非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。
4. 索引越多越好吗?
索引并不是建立越多越好,把索引比作目录,相当于,一本100页的书,如果有50页目录,书本这么厚,实际内容就那么少,谁还会去翻看,若在面试中被问到,可以根据以下三点来回答:
1.数据量小的表不需要建立索引,建立会增加额外的索引开销;
2.数据变更需要维护索引,因此更多的索引意味着更多的维护成本;
3.更多的索引意味着也需要更多的空间(索引也是需要空间来存放的);