MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。一般工作中用到最多的是BTree索引。
mysql索引目的:
索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的,如果我想找到m开头的单词呢?或者ze开头的单词呢?是不是觉得如果没有索引,这个事情根本无法完成?
咱们去图书馆借书也是一样,如果你要借某一本书,一定是先找到对应的分类科目,再找到对应的编号,这是生活中活生生的例子,通用索引,可以加快查询速度,快速定位。
建立索引的原理
一个最重要的原则是最左前缀原理,在提这个之前要先说下联合索引,MySQL中的索引可以以一定顺序引用多个列,这种索引叫做联合索引,一般的,一个联合索引是一个有序元组,其中各个元素均为数据表的一列。另外,单列索引可以看成联合索引元素数为1的特例。
索引匹配的最左原则具体是说,假如索引列分别为A,B,C,顺序也是A,B,C:
-
那么查询的时候,如果查询【A】【A,B】 【A,B,C】,那么可以通过索引查询
-
如果查询的时候,采用【A,C】,那么C这个虽然是索引,但是由于中间缺失了B,因此C这个索引是用不到的,只能用到A索引
-
如果查询的时候,采用【B】 【B,C】 【C】,由于没有用到第一列索引,不是最左前缀,那么后面的索引也是用不到了
-
如果查询的时候,采用范围查询,并且是最左前缀,也就是第一列索引,那么可以用到索引,但是范围后面的列无法用到索引
索引的缺陷
索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好
建立索引的常用技巧
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)的索引,那么只需要修改原来的索引即可,当然要考虑原有数据和线上使用情况
主键和索引的区别:
1. 主键一定是唯一性索引,唯一索引并不一定就是主键。
2. 一个表中可以有多个唯一索引,但只能有一个主键。
3. 主键列不允许空值,而唯一索引列允许空值。