January 19, 2016

MySQL中的索引-读书笔记¶

索引（MySQL中的Key）即书的目录，图书馆的图书编目等。索引是存储引擎实现的，所以索引与存储引擎相关，不同的引擎各有不同。

MySQL中的索引有：B-Tree，哈希和R-Tree(空间)三种索引类型。

B-Tree索引¶

B-Tree索引与列顺序有很大的关系，只有在使用了索引的最左前缀（Leftmost Prefix）时才有效。它可以很好的应用于：

全键值匹配
匹配最左前缀：索引中的第一列
匹配列前缀：匹配列值的开头部分
匹配范围值
精确匹配一部分且匹配某个范围中的另一部分
只访问索引的查询

哈希索引¶

空间索引¶

空间索引(R-Tree, Spatial Index)

全文索引¶

高性能索引策略¶

如果建立索引：

了解自己的应用，检查最常运行的查询，考虑所有的索引是否形成一个优化配置；
分析查询的响应时间，消耗的资源，不断优化测试索引
首先尝试扩展索引，而不是新增索引

隔离列¶

隔离列即索引不能是表达式的一部分，也不能位于函数中。

前缀索引和索引选择性¶

对于长度较大的列，如果需要建立索引，可以选择一个合适长度的前缀进行索引。长度的选择非常重试，太长-索引占用空间大，太短-索引选择性差。

计算合适前缀长度的一种办法是计算全列的选择性，并使前缀的选择性接近于它。方法如下：

-- 计算全列选择性
SELECT COUNT(DISTINCT col_name)/COUNT(*) FROM tbl_name;
-- 计算不同长度前缀的选择率，选择与全列选择率最接近的长度
SELECT COUNT(DISTINCT LEFT(col_name, 3))/COUNT(*) AS sel3,
    COUNT(DISTINCT LEFT(col_name, 4))/COUNT(*) AS sel4,
    COUNT(DISTINCT LEFT(col_name, 5))/COUNT(*) AS sel5,
    COUNT(DISTINCT LEFT(col_name, 6))/COUNT(*) AS sel4,
    COUNT(DISTINCT LEFT(col_name, 7))/COUNT(*) AS sel4
    FROM tbl_name;

聚集索引¶

没搞明白什么东东！

覆盖索引¶

所有查询需要的数据都包含在索引中索引称之为覆盖索引

压纹索引¶

MyISAM存储引擎使用前缀索引以减小索引大小，可以将更多索引装入内存，在某些情况下可以极大提高性能。默认不会压缩字符串，但可以压缩整数。

可以在CREATE TABLE命令中使用PACK_KEYS控制索引压缩方式。

多余索引和重复索引¶

MySQL允许在同一个列上建立多个索引。MySQL单独维护每一个索引，并且在查询优化器时逐一考虑每个索引（所以？）。

应该避免重复索引和多余索引。

参考资料¶

高性能MySQL（第二版）

Posted by LiuHui

Filed under: database, mysql

Tags: database, mysql, dba

Life

Learning, Promotion