mysql学习笔记-索引的创建与设计原则

1、索引的分类

1、普通索引
2、 唯一性索引
在一张数据表里 可以有多个 唯一索引。注意和主键索引区分。
3、主键索引
一张表里 最多只有一个 主键索引。why? 这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。
4、单列索引
5、多列(组合、联合)索引
6、全文索引
查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。
①.使用全文索引前，搞清楚版本支持情况;
②.全文索引比 like+% 快 N倍，但是可能存在精度问题;
③.如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
7、补充:空间索引
目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

2、哪些情况适合创建索引

2.1、字段的数值有唯一性的限制（如果 某个字段是唯一性的，就可以直接 创建唯一性索引，或者 主键索引。）
2.2、频繁作为 WHERE 查询条件的字段
2.3、经常 GROUP BY和 ORDER BY的列
2.4、UPDATE、DELETE的WHERE 条件列
2.5、DISTINCT 字段需要创建索引
2.6、多表 JOIN 连接操作时，创建索引注意事项
连接表的数量尽量不要超过 3张、对 WHERE 条件创建索引、对用于连接的字段创建索引并且该字段在多张表中的类型必须一致。
2.7、使用列的类型小的创建索引
①数据类型越小，在查询时进行的比较操作越快
②数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/0 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
2.8、使用字符串前缀创建索引
通过截取字段的前面一部分内容建立索引，这个就叫 前缀索引。
2.9、区分度高(散列性高)的列适合作为索引
在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。可以使用公式 select count(distinct a)/count(*)from t1 计算区分度，越接近1越好，一般超过33% 就算是比较高效的索引了。
2.10、使用最频繁的列放到联合索引的左侧
这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率,
2.11、在多个字段都要创建索引的情况下，联合索引优于单值索引

3、索引不是越多越好

建议单张表索引数量 不超过6个，索引占用磁盘空间、影响增删改查效率、索引太多优化器有选择困难症。

4、哪些情况下不适合建索引

① 在where中使用不到的字段，不要设置索引
② 数据量小的表最好不要使用索引
③ 有大量重复数据的列上不要建立索引 Oracle是不是可以建hash索引？不太确认了
④避免对经常更新的表创建过多的索引
⑤ 不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为ASCI，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
⑥ 删除不再使用或者很少使用的索引
⑦ 不要定义冗余或重复的索引