风之力科技
股票代码:781340
400-718-5885或13605745691
service@nwpit.com

技术资讯

22May2016
点击

Mysql学习笔记,聊聊数据库索引。

用简单的方式理解索引:索引就是新华字典前面的按拼音、按部首查字的那部分。

生活中,查一个生僻字,如果不知道读音,也不借助与笔画查找,恐怕很难查得到吧,如果让机器去做这件事,那就是查阅整本字典的所有字,匹配后返回。这就造成了全表扫描,效率很低。

但是我们通过部首去找,然后通过笔画,可能查阅的数据量很小,让机器去做,只扫描相关数据项就好了,速度很快。

因此查询速度的快慢,和数据量的大小没有绝对关系,查询速度取决于取出所需的数据,扫描了多少条表数据。他们的比值越小,效率越高。


几个比较重要的知识点:

1、MySQL在检索表中的数据时,先按照索引“关键字”的值在索引中进行查找,如果能够查找的到,则可以直接定位到起始页,如果没有,则全表扫描。

2、索引其实就是数据库表中字段值的复制,改字段称为索引的关键字。

3、一张数据表可以建立多个索引。

4、什么是前缀索引:

    新华字典中的“部首检字表”的使用方法。首先我们先确认要查找这个字的部首是什么,然后看部首外的字笔画是多少,然后查找相应的字。同样对于数据库表而言,索引中关键字的值可以是索引“关键字的一部分,这种索引成为"前缀索引"。例如我可以给用户表中姓名表建立前缀索引。统计姓名中姓张的人数、姓李的人数。

5、索引可以是字段的组合吗?

    对数据库而言,索引可以是字段的组合。数据库表中的某个索引如果有多个关键字构成,此时该索引被称为复合索引。无论索引是一个字段、还是几个字段的组合。这些字段必须来自同一张表,比昂切关键字的值必须是表中相应字段值的copy,。

6、不能跨表建立索引。

7、索引数据需要额外的存储空间吗?

   这个问题很扯淡,比如想让牛跑的好,又想让牛不吃草。索引文件肯定占存储空间了。对于数据库表的索引而言,索引关键字经过排序后放在外存中。对于MyISAM数据库表而言,索引存放在外存MYI索引文件中。对于InnoDB存储引擎而言,索引数据存放在外存InnoDB表空间文件中(可能是共享表空间文件,也可能是独享表空间文件)。为了提升数据的检索效率,无论MyISAM表的索引还是,InnoDB的索引,索引的关键字经过升序排序后放在外存中。

8、表中那些字段适合选作表的索引?什么事主索引,什么是聚簇索引?

   对于MyISAM表而言,MySQL会自动的将表中所有记录主键值的备份以及每条记录所在的起始页编入索引中。像部首检字表一样生成一张“索引表”存放在外存中,这种索引称为主索引(primary key)。MyISAM表的MYI索引文件,与MYD数据文件位于两个文件。通过MYI索引文件中的“表记录指针”,可以找到MYD数据文件中表记录所在的物理地址。

Innodb表的“主索引”与MyISAM表的主索引不同。InnoDB表的主索引关键字的顺序与InnoDB表记录主键值的顺序一致。这种索引就称为“聚簇索引”,并且每一张表只能拥有一个聚簇索引。

9、索引与数据结构是什么关系?

   数据库中的所以关键字在索引文件中存储规则十分复杂。为了有效的提高数据库检索效率。索引通常使用平衡树(btree)或者哈希表等复杂的数据结构进行“编排”。在操作数据库的时候,底层操作在执行这种复杂的运算,但我们是感觉不到的。

10、索引越多越好吗?

   如果索引过多,在更新数据(添加、修改、删除)时,除了需要修改表中的数据外,还需要对该表的所有索引进行维护,以维持表字段值和索引关键字值的一致性。反而降低了数据的更新速度。

   实践表名,当修改表记录的操作特别频繁时,过多的索引会导致硬盘I/O次数明显增加,反而会明显的降低服务器性能。甚至可能会导致宕机。

11、索引关键字的选取原则。

    原则1、表中的某字段离散度越高,该字段越适合作为选作索引的关键字。

    数据库用户在创建主键约束的同时,MySQL会自动的创建主索引(primary index)且索引名为primary ;在创建唯一约束的同时,MySQL会自动创建唯一性约束(unique index)默认情况下,索引名为唯一约束性约束的字段名。

    原则2、占用存储空间少的字段更适合选作索引的关键字。

    原则3、存储空间固定的字段更适合选作索引的关键字。

    原则4、 where字句中经常使用的字段应该创建索引,分组字段或者排序字段应该创建索引,两个表的连接字段应该创建索引。

 引入索引的目的是为了提高检索的效率,因此索引关键字的选择与select语句息息相关。这句话有两个方面的含义,select语句的设计可以决定索引的设计;索引的设计也同样影响着select的设计。select语句中的where字句、group by 字句,以及order by字句,又可以影响索引的设计。两个表的链接字段应该创建索引,外键约束一经创建,mysql便会自动地创建与外键相对应的索引,这是由于外键字段通常是两个表的连接字段。

    原则5、更新频繁的字段不适合创建索引,不会出现在where字句中的字段不应该创建索引。

    原则6、最左前缀的原则。

    复合索引还有另外一个有点,他通过被称为“最左前缀”(leftmost prefixing)的概念体现出来,假设向一个表的多个字段(例如 firstName ,lastName、address)创建复合索引(索引名:firstname_lastname_address)。当where查询的条件是以下各种字段的组合时,mysql将使用fname_lastname_address索引。其他情况将无法使用          fname_lastname_address索引。

firstname,lastname,address

firstname,;lastname

firstname

   原则7、 尽量使用前缀索引。

    仅仅在姓名中的姓氏部分创建索引,从而可以节省索引的存储空间,提高检索效率。

    与数据库的设计一样,索引的设计同样需要数据库开发人员经验的积累,以及智慧的沉淀,同时需要依据系统各自的特点设计出更好的索引,在“加快检索效率”与“降低更新速度”之间做好平衡。从而大幅提升数据库的整体性能。

索引与约束关系:

      mysql中的索引与约束的关系是怎样的。约束分为主键约束、唯一性约束、默认值约束、检查约束、非空约束、以及外键约束。其中主键约束、唯一性约束、外键约束与索引的关系更为紧密。

      约束主要用于保证业务逻辑操作数据库时数据库的完整性。

     而索引则是将关键字数据以某种数据结构(如btree,及二叉树,哈希等)存储到外存,用于提升数据的检索性能。

     约束是逻辑层面的概念,而索引既有逻辑上的概念,更是一种物理存储方式,且事实存在,需要耗费一定的存储空间。

     unqiue index   索引名  唯一索引

     index   索引名      普通索引

     index  complex_index(price,publish_time)复合索引