MySQL数据库索引问题

  索引是快速搜索的关键。MySQL 索引的建立对于 MySQL 的高效运行是很重要的。下面介绍建立索引的基本知识和几种常见的 MySQL 索引类型。

SQL 建立索引的原则

  1. 定义主键的数据列一定要建立索引。
  2. 定义有外键的数据列一定要建立索引。
  3. 对于经常查询的数据列最好建立索引。
  4. 对于需要在指定范围内的快速或频繁查询的数据列;
  5. 经常用在WHERE子句中的数据列。
  6. 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。
  7. 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
  8. 对于定义为text、image和bit的数据类型的列不要建立索引。
  9. 对于经常存取的列避免建立索引
  10. 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。
  11. 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

常用的索引方法

B树(B-Tree 索引)

  B树(B tree)是一种以树的构造管理存储位置信息的方式。在B树中每个节点(node)可以有多个子数据,从而控制树的高度。因此,即使有很多数据,也可以很快地从根部找到存放地址。
  B树中除根节点外,其他节点拥有的子数据最多为 $2n$ 个,最少为 $n$ 个。

散列(Hash 索引)

  散列(hash)是对数据键值运用散列函数,求得存储地址的方法。散列用于”=”,”IN”和”<=>”这样的完全一致检索时功能强劲。但是,散列不适用比较条件检索和模糊检索。

MySQL 选择索引的原则

选择索引数据类型的原则

  1. 越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。
  2. 简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。
  3. 尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

选择标识符的原则

  1. 整型:通常是作为标识符的最好选择,因为可以更快的处理,而且可以设置为AUTO_INCREMENT。
  2. 字符串:尽量避免使用字符串作为标识符,它们消耗更好的空间,处理起来也较慢。而且,通常来说,字符串都是随机的,所以它们在索引中的位置也是随机的,这会导致页面分裂、随机访问磁盘,聚簇索引分裂(对于使用聚簇索引的存储引擎)。

一旦选定数据类型,应该保证所有相关的表都使用相同的数据类型。

MySQL 索引类型

普通索引(INDEX)

  这是最基本的索引,它没有任何限制。

唯一索引(UNIQUE)

  它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

主键索引(PRIMARY)

  它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。

全文索引(FULLTEXT)

  用来对大表的文本域(char,varchar,text)进行索引。

其他详细资料及部分引用资料
SQL索引一步到位
理解MySQL——索引与优化
MySQL索引类型一览 让MySQL高效运行起来
MySQL的btree索引和hash索引的区别

文章目录
  1. 1. SQL 建立索引的原则
  2. 2. 常用的索引方法
    1. 2.1. B树(B-Tree 索引)
    2. 2.2. 散列(Hash 索引)
  3. 3. MySQL 选择索引的原则
    1. 3.1. 选择索引数据类型的原则
    2. 3.2. 选择标识符的原则
    3. 3.3. MySQL 索引类型
      1. 3.3.1. 普通索引(INDEX)
      2. 3.3.2. 唯一索引(UNIQUE)
      3. 3.3.3. 主键索引(PRIMARY)
      4. 3.3.4. 全文索引(FULLTEXT)

20160320-mysql-index/

本页二维码