mysql自适应hash mysql hashcode

整理自《高性能MySQL》一书，外加自己实测。

MySQL中，索引是存储在引擎层面而不是服务器层实现的。不用存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

MySQL分两大索引 B-Tree索引 Hash索引

1. B-Tree索引 == 使用B-Tree数据结构来存储数据。MySQL默认是InnoDB引擎，大多数MySQL都支持这种索引。
2. Hash索引 == 基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
   在MySQL中只有Memory引擎显示支持哈希索引，这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。
   工作中常用的MySQL默认InnoDB引擎，InnoDB引擎只支持B-Tree索引。InnoDB索引也不支持全文索引，MyISAM引擎支持全文索引
3. 索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录的匹配。例如可以使用分区技术。

Mysql InnoDB引擎中Normal key、primary key 、unique key 的区别

1. primary key 主键索引 ： 一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引，不可以为null

PRIMARY KEY 约束：唯一标识数据库表中的每条记录；
                 主键必须包含唯一的值；
                 主键列不能包含 NULL 值；
                 每个表都应该有一个主键，并且每个表只能有一个主键。
                （PRIMARY KEY 拥有自动定义的 UNIQUE 约束）

1. unique key 唯一索引，可以为null但必须唯一。 unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个唯一索引；
   UNIQUE 约束：唯一标识数据库表中的每条记录。
   UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
   （每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束）
2. Normal key 相当于普通索引。数据可重复，也可以有多个普通索引，仅用于辅助查询用
3. 多列索引 == 一个常见的错误就是，为每个列创建独立的索引，或是按照错误的顺序创建多列索引
   为每个列单独创建独立索引，从 show create table中很容易看到这种情况

CREATE TABLE `sys_user` (
  `userid` varchar(64) NOT NULL,
  `second_level` varchar(64) DEFAULT NULL,
  `three_level` varchar(64) DEFAULT NULL,
  `four_level` varchar(64) DEFAULT NULL,
  `companyid` varchar(64) DEFAULT NULL,
  `rank` varchar(16) DEFAULT NULL,
  `username` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`userid`),
  KEY `index` (`second_level`),
  KEY `index1` (`three_level`),
  KEY `index2` (`four_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='PC用户表'

这种索引策略一般是由于听到一些专家诸如“把where 条件里面的列都建上索引”，这样模糊的建议导致的。这个建议是非常错误的。这最好的情况只能是一星索引，有时如果无法设计一个三星索引，不如忽略掉where子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

创建多列索引

CREATE TABLE `sys_user` (
  `userid` varchar(64) NOT NULL,
  `second_level` varchar(64) DEFAULT NULL,
  `three_level` varchar(64) DEFAULT NULL,
  `four_level` varchar(64) DEFAULT NULL,
  `companyid` varchar(64) DEFAULT NULL,
  `rank` varchar(16) DEFAULT NULL,
  `username` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`userid`),
  KEY `index` (`second_level`,`three_level`,`four_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='PC用户表'

sql会先过滤出符合second_level条件的记录，在其基础之上再过滤出符合three_level条件的记录，最后过滤出four_level

注意创建多列索引时需要选择合适的索引顺序

可以先计算一下各个索引对应的数据基数多大

所以该索引应该按照基数从小到大排列，修改删除表索引时：表和索引名字必须用 ` 这个符号标记，不然sql不通过

ALTER TABLE `sys_user` ADD KEY `index`(second_level,four_level,three_level)

CREATE TABLE `sys_user` (
  `userid` varchar(64) NOT NULL,
  `second_level` varchar(64) DEFAULT NULL,
  `three_level` varchar(64) DEFAULT NULL,
  `four_level` varchar(64) DEFAULT NULL,
  `companyid` varchar(64) DEFAULT NULL,
  `rank` varchar(16) DEFAULT NULL,
  `username` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`userid`),
  KEY `index` (`second_level`,`four_level`,`three_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='PC用户表'

6. 覆盖索引== 如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”



7. 如图所示 出现using index时，为覆盖索引
   不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。另外不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引，Memory存储引擎就不支持覆盖索引。
8. 维护索引和表
   CHECK TABLE sys_user 命令查询该表是否损坏
   通常能够找出大部分的表和索引的错误
   可以使用REPAIR TABLE sys_user 修复损坏的表，但不是所有的存储引擎都支持该命令。如果存储引擎不支持，也可以通过ALTER操作来重建表，例如修改表的存储引擎为当前的引擎。下面是一个针对InnoDB表的例子 ALTER TABLE sys_user ENGINE = INNODB
10. 更新索引统计信息
    如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行 ANALYZE TABLE sys_user来重新生成统计信息解决这个问题。
    可以使用SHOW INDEX FROM sys_user



11. 这里需要提到索引列基数（Cardinality），其显示了存储引擎估算索引列有多少个不同的数值。
12. 减少索引和数据的碎片
    B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。
    表数据存储也可能碎片化。然而数据存储的碎片化比索引更加复杂。有三种类型的数据碎片。
    1、行碎片化：这种碎片指的是数据行被存储为多个地方的多个片段中，即使查询只从索引中访问一行记录，行碎片也会导致性能下降
    2、行间碎片：行间碎片是指逻辑上的顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和簇族索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益
    3、剩余空间碎片：剩余空间碎片是指数据页中有大量的剩余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。
    对于MyISAM表，这三类碎片化都可能发生。但InnoDB不会出现短小的行碎片，InnoDB会移动短小的行并重写到一个片段中。
    可以通过执行OPTIMIZE TABLE sys_user或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。
    最新版的InnoDB引擎新增了“在线”添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。
    对于不支持OPTIMIZE TABLE 的存储引擎，可以通过ALTER TABLE操作重建表，只需要将表的存储引擎修改为当前的引擎即可ALTER TABLE sys_user ENGINE = INNODB