每天学习一点点,成功增加一大步

MySQL理解索引、添加索引的原则

MYSQL zhanghui 579℃

要理解索引,首先需要了解数据如何存储在硬盘上。不同的存储引擎采取的措施可能不一样,比如MySQL客户端默认采用MyISAM,该引擎为每个表创建单独的文件。

不管是否为每个表创建了单独的文件,操作系统从硬盘读取数据到内存中总是以页为单位的。因此,如果要获取特定记录,就需要把包含该记录的页读入。

MySQL在获取一条记录的时候,可以采取逐条扫描或者索引访问两种不同的方法。假设采取第一种方法,要获取id为1234的记录,就需要顺序地、依次地访问过前1233条记录。不仅如此,还需要考虑每次读入数据页的IO开销。而如果采取索引,则可以根据索引指向的页以及记录在页中的位置,迅速地读取目标页进而获取目标记录。

除了在获取特定行的情况下使用hash十分快捷,在其它情况下都(默认)采用B树来构建索引。B树是平衡多叉树,每个节点存放多少个值取决于值所占的空间,这与每一张数据页存放多少条记录与记录信息量有关同理。节点中的值是以非降序进行排列的,节点中的值总是小于等于指向它的结点中的值。

MySQL使用B树构造索引的情况下,是由叶子指向具体的页和记录的。并且一个叶子有一个指针指向下一个叶子。

使用索引需要注意:

  1. 只对WHERE和ORDER BY需要查询的字段设置索引,避免无意义的硬盘开销;
  2. 组合索引支持前缀索引;
  3. 更新表的时候,如增删记录,MySQL会自动更新索引,保持树的平衡;

一,索引的重要性

索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。注意如果你需要访问大部分行,顺序读取要快得多,因为此时我们避免磁盘搜索。

索引分三类:

  • index —-普通的索引,数据可以重复
  • unique —-唯一索引,唯一索引,要求所有记录都唯一
  • primary key —-主键索引,也就是在唯一索引的基础上相应的列必须为主键

MySQL 中会对 <,<=,=,>,>=,BETWEEN,IN 以及不以% _ 开头的like 语句使用索引。

二,原则

  1. 单表数据太少,索引反而会影响速度
  2. where后的条件,order by ,group by 等这样过滤时,后面的字段最好加上索引。根据实际情况,选择PRIMARY KEY、UNIQUE、INDEX等索引,但是不是越多越好,要适度
  3. 联合查询,子查询等多表操作时关连字段要加索引

转载请注明:隨習筆記 » MySQL理解索引、添加索引的原则