MySQL-索引与数据类型

索引

在数据库的查询操作中，为了提高数据查询的效率引入了索引的概念。其中，实现索引有几种不同的方式：哈希表、有序数组、搜索树等。

哈希表

把数据库中的值放在数组中，采用哈希算法把key映射到一个确定的位置，然后把value放在数组的这个位置。当发生冲突时，采用链表法解决。

缺点：只能由于等值查询无法实现范围查询。

有序数组

把数据库中的值放在一个有序数组中，查询用二分法，时间复杂度：O(log(N))

缺点：更新数据库时需要的操作太多，效率低。

搜索树

每个结点的左子树的值小于父节点，右子树的值大于父节点，查询时间复杂度：O(log(N))，更新时间复杂度：O(log(N))。

缺点：树的高度过高时，时间成本还是很高。

MySQL索引原理

值索引：在MySQL语法中，使用where比较查找值相同的索引。

MySQL的索引内部实现采用的是B+树这种数据结构，其中

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。

全文索引：在MySQL语法中，使用match against查找文中的关键字而不是直接比较是否相等。

MyISAM 存储引擎支持全文索引， InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

哈希索引： 哈希索引能以 O(1) 时间进行查找，但是失去了有序性 。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

索引优化

多列索引： 在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。

索引列的顺序 ： 让选择性最强的索引列放在前面。

前缀索引： 对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符 。

覆盖索引： 覆盖索引包含所有需要查询的字段的值

InnoDB 的 B+Tree 索引分为主索引和辅助索引。 主索引的叶子节点 data 域记录着完整的数据记录 辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

数据类型

整型：

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的 。

浮点数：

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型 。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

字符串：

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

在进行存储和检索时，会保留 VARCHAR 末尾的空格，而会删除 CHAR 末尾的空格。

时间和日期：

• DATETIME
• TIMESTAMP

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

其中，DATETIME与时区无关，能够保存从 1000 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。 默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

TIMESTAMP 和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。 保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。