1 定义
DBA 数据库管理员
DBMS 数据库管理系统
索引 和 表分区 都可以提高查询性能。
索引是为了增加查询速度附加的一种字段,除此以外还需要一个索引表存放索引的位置信息。如果对某一字段增加索引,查询是就会先去索引表中直接定位到特定值的位置,大大减少遍历的行数。
优点:
①唯一性索引还能用于保证数据库的唯一性。
②加快检索的速度。
③加速表和表之间的链接。
④在使用分组和排序子句(order by ASC 或 DESC GROUP BY )进行数据检索的时候,也可以显著减少分组和排序的时间。
缺点:
①创建索引和维护索引也需要时间和空间,如果是聚簇索引,那么需要的空间会更大。
②对表中数据维护的时候,也要维护索引。
2 分类
聚簇索引:InnoDB(存储引擎)将通过主键(自增更好,若为UUID随机生成的主键很糟糕)来聚簇索引,如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键作为聚簇索引。
非聚簇索引:①B+tree(平衡树) ②Hash索引 动态扩容
3 添加索引的原则
不应该:
①在查询中很少使用或者参考到的列不应该创建索引。
②只有很少数据值的列不应该创建索引。
③当修改性能远大于检索性能的时候不应该创建索引。
④把使用频繁的若干列依次放在最左部,因为mysql索引查询遵循最左匹配原则。
应该:
①表的主键、外键必须有索引
②数据量超过300的表应该有索引
③经常出现在where子句中的字段,特别是大表的字段,应该建立索引
④经常与其他表进行连接的表,在连接字段上应该建立索引
⑤索引应该建立在选择性高的字段上
⑥索引应该建在小字段上,对于大的文本字段甚至超长字段,不应建索引
⑦复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
⑧频繁进行数据操作的表,不要建立太多的索引;
⑨删除无用的索引,避免对执行计划造成负面影响;
4 结构
常见的MySQL主要有两种结构:Hash索引和B+ Tree索引,使用的是InnoDB引擎,默认的是B+树
什么时候不适合hash索引?
①哈希索引适合等值查询,但是无法进行范围查询
②哈希索引没办法利用索引完成排序
③哈希索引不支持多列联合索引的最左匹配规则
④如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞问题