MySQL索引,有很多很多的东西需要去学习,我会写一些自己的总结,这些总结主要是平时运用在实际项目中的,有很多的经验往往设计表的人很清楚,但是总是有:‘这个东西就在哪里哪里用一下,加不加都行,设计成这样也没关系。’诸如此类的想法,这样是很不负责任的。关于索引方面理论的东西也很多很深奥,我也有很多不懂的地方,所以我不会去写很多理论的东西,以免误导大家。
一、首先谈一下什么是索引
官方描述:数据库系统维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
MySQL普遍使用B+Tree实现其索引结构,你也不用纠结B+Tree是什么,其实它就像字典的目录一样。
二、索引解决了哪些问题
下面根据一个表来做些实验:
索引是一个单独的文件,是与数据文件分离的,具有更好的数据结构,而且文件更小。
索引的作用就是可以快速定位、快速知道从上读还是从下读的、排好顺序的列表。比如上面的表,要获取性别为男二班的的数据,那么建立sex+class的联合索引scindex,就能迅速的从索引文件中获取数据地址,再从数据文件中获取内容,如下。
explain
SELECT
*
FROM
`stu_test`
where
sex=
'男'
and
class=
'二班'
;
其实呢,索引也是可以存储内容的,不需要访问数据文件,速度非常非常快,这就是‘覆盖索引(Covering Index)’,在输出的EXtra信息中输出的是Using index,就会用到覆盖索引(前提是你select字段要在索引中)。比如你所查询的数据:
在explain中有一个字段rows(相对应慢查询日志中是Rows_examined),就是你sql语句所扫描的行数,添加索引的目标就是为了降低这个行数。
注意:一次检索只能用一个索引,如果所用的不是你所期望的索引,那么你可以用 force index来使sql语句强制使用你指定的索引。
总结:索引可以快速定位到内容、快速完成排序、甚至不需要读取数据文件就可以返回数据。
三、需要注意的一些问题
1、核心的sql语句务必添加索引,状态类型字段最好不要添加索引,我在上一篇中有详细说明为什么。
2、字符字段的索引如果要加索引则考虑前缀索引。
MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。语法:
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
prefix_length这个值标志着区分多少条记录,比如这个值为5 那么就可以区分26x26x26x26x26条数据
3、不要在sql语句中做运算,运算要放在程序中进行。
4、不要用字符串做主键
我的数据库是MariaDB,它的默认引擎是InnoDB,如果你的表中有主键索引,InnoDB会为主键建立聚簇索引(注意聚簇索引不是在所有引擎都支持的,现在的是InnoDB支持)。如果表中没有主键索引,InnoDB也会定义一个隐藏的主键对其建立聚簇索引,一个表只能有一个聚簇索引。至于为什么不用字符串做主键,我找了一些理由:
通常情况下,DBMS(数据库管理系统)会在主键上建立聚集索引,由于一般使用B-Tree的数据结构来存储索引数据,所以一般对主键有以下两个要求:
越短越好——越短在一个Page中存储的节点越多,检索速度就越快。
顺序增长——如果每一条插入的数据的主键都比前面的主键大,那么B-Tree上的节点也是顺序增长的,不会造成频繁的B-Tree分割。
越短越好是为了查询的速度快,顺序增长是为了插入速度快。
有了这两个要求,我们再来分析下各个数据类型:
字符类型:基本不满足前面提到的2点要求,字符类型一般不会很短,而且也很可能不是顺序增长的,所以不是特别推荐的主键类型。当然如果确实业务需求使用字符类型,那么也尽量使用char(XX)而不要使用varchar(XX)。
数字类型:不需要多介绍