《Mysql - 索引》

一：索引概述？

　　- 数据库里的数据是以文件的形式存储的，里面放了我们的各种数据。就和一本书一样。

　　- 通俗的来说，索引就像一个书签一样，可以直接帮我们找到内容，避免了我们一页一页找。

　　- 就像你不会给你的一本书上的每一页都加上索引一样，索引的建立和维护也需要考虑。

 

二：索引的优点/缺点？

　　- 优点

　　　　- 使用索引的列，会在查询时大大的加速查询速度。

　　　　- 大大减少服务器需要扫描的数据量。

　　　　- 帮助服务器避免排序和临时表。

　　　　- 将随机 I/O，变为 顺序I/O。

 

　　- 缺点

　　　　- 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

　　　　- 索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值

　　　　- 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

 

三：索引的使用原则？

　  - 索引适合在中到大型的表中建立会高效。

　　　　- 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引。

 

　　- 数据量小的表，使用全表扫描查询会更高效。

　　　　- 数据量小的表最好不要使用索引

　　　　- 因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

 

　　- 超过500W的表，应该考虑使用 分区/分表 进行处理

 

四：如何建立索引 (B+Tree)？

　　- 查看当前表的索引

　　　　- SHOW INDEX FROM 表名 \G；

 

　　- 建立索引

　　　　- 单列索引

　　　　　　- CREATE INDEX 索引名 ON 表(字段);

　　　　- 前缀索引

　　　　　　- CREATE INDEX 索引名 ON 表(字段(长度));

　　　　- 唯一索引

　　　　　　- CREATE UNIQUE INDEX 索引名 ON 表(字段);

　　　　- 主键索引

　　　　　　- ALTER TABLE `表名` ADD PRIMARY KEY (`字段名`);

　　　　- 多列索引

　　　　　　- CREATE INDEX 索引名 ON 表(字段1, 字段2, ...);

 

五：高性能的索引策略

　　- 限制每张表的索引数量

　　　　- 一张表不要超过五个,索引不是越多越好，会提高/也会降低索引

　　　　- 禁止给每一列建立索引，并不會获得很好的效果

 

　　- 对于 Varchar/Char/Text 等字段，最好使用 前缀索引

　　　　- 优点

　　　　　　- 前缀索引能有效减小索引文件的大小，提高索引的速度。

　　　　- 缺点

　　　　　　-  不能在 ORDER BY 或 GROUP BY 中使用前缀索引。

　　　　　　-  也不能把它们用作覆盖索引(Covering Index)。

　　　　- 建立

　　　　　　- 为了创建前缀索引的合适长度，确定区分度，建立前缀索引

　　　　　　- SELECT COUNT(DISTINCT LEFT(column_name, $length)) / COUNT(*) FROM table_name;

 

　　- 在哪些列上建立索引？

　　　　- 在 select/update/delete SQL中的 where 条件中建立索引

　　　　- 在 order by / group by 字段上建立索引

 

　　- 多列索引的建立

　　　　- 将区分度最高的列放在索引最前列（不是绝对，需要根据具体的情况来定）

　　　　- 尽量将查询包含在多列索引中

　　　　- SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

  

　　- 对于联合索引来说，要遵守最左前缀法则

　　　　- 举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。

　　　　- 所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。