MySQL-12-索引和性能

原理

索引要解决的问题就是在使用 WHERE 语句定位数据的时候，可以通过数据结构进行优化。因此我们在定位数据的时候，尽量使用主键（或者其他有索引的列）进行查询。

B+ 树

索引要解决的问题就是在使用 WHERE 语句定位数据的时候，可以通过二叉搜索树来优化。但是由于计算机硬件的特点，（Cache 内存硬盘）需要尽量多命中 Cache，少硬盘 IO，和需要查询稳定性的需求。所以并没有使用传统的平衡二叉树，红黑树等，而是使用了 B+ 树。

B+ 树的特点就是层级比较少（在树分支处较次数比较少，一般不超过 4 层，二叉搜索树是 logN 层），在查询的时候大大减少了对硬盘 IO 的次数。定位到目标数据所在的硬盘块的时候，就可以一起读入内存中处理，邻近性原理等也大大方便了 Cache 的命中。

而目标数据的那一块在读入内存之后，由于维护了单调的顺序，所以可以采用二分法进行具体定位，所以复杂度是和二叉搜索树一样的，但是大大减少了硬盘 IO 的次数。

物理实现分类

按照物理实现，索引可以分为：聚簇索引（一级索引）和非聚簇索引（辅助索引、二级索引）。

聚簇索引：这个索引在 InnoDB 里面默认就是主键，在建立 B+ 树索引的时候，时会将所有的数据放在聚簇索引中的（也只会在聚簇索引中），这样一旦索引找到，就可以直接获取数据。一个数据库只有一个聚簇索引。建议使用自增的 ID 列为主键，并不可改变，性能更好。

非聚簇索引：索引里面没有数据，只有一个连接。数据库中可以有多个非聚簇索引，也就是可以根据非主键搜索比较多的列，给他们建一个索引，提高性能。

使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

索引设置

隐式/显式创建

隐式创建：定义主键约束、外键约束或者唯一性约束的时候，会自动添加索引在列上，这个是隐式索引。三者都是 唯一性索引。

显式索引：在定义表的时候在表级约束地方设置。

CREATE TABLE table_name(
    XXXX,
    XXXX,
    [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [(length)]) [ASC | DESC]
)

UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
index_name 指定索引的名称，为可选参数，如果不指定，那么 MySQL 默认 col_name 为索引名；
col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC 或 DESC 指定升序或者降序的索引值存储，默认升序。

比如：

CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    pwd VARCHAR(20),
    UNIQUE INDEX,
    INDEX(username) DESC, -- 创建普通索引降序存储
    INDEX username_index(username), -- 创建普通索引，并给索引名
    UNIQUE INDEX(username(10)) -- 创建唯一性索引，并限制只有前10个字符参与索引
    UNIQUE INDEX username_pwd_index(username,pwd) -- 创建复合唯一索引，并给索引名   
)

新增索引

对于唯一主键外键绑定的索引，是按照约束的方式新增。

而普通的索引：

1 2	ALTER TABLE table_name ADD [UNIQUE \| FULLTEXT \| SPATIAL] [INDEX \| KEY] [index_name] (col_name[length],...) [ASC \| DESC]

就是一个 ALTER TABLE，语法和上面是一样的。

删除索引

1 2	SHOW INDEX FROM 表名称; -- 先看看所有的索引名称 DROP INDEX index_name ON table_name; -- 然后删除索引

索引设计原则

建立索引技巧

字段的数值有唯一性的限制适合建立索引：业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。INSERT 的成本增加忽略不计，但是搜索速度大大增加！
频繁作为 WHERE 查询条件的字段适合建立索引：或者说，尽量以有索引的列作为 WHERE 的查询条件。无论是查询还是修改删除，都会快很多。
经常 GROUP BY 和 ORDER BY 的列适合建立索引：建立索引之后，速度非常快。
DISTINCT 字段需要创建索引：有索引带来的数据有序性之后，去重快很多。
JOIN ON 的连接字段创建索引：建立连接的过程，本质也是一个 WHERE 的过程。
列类型占用空间小的可以建立索引：因为建立索引成本小。
字符串比较长的适合截取前缀建立索引：减少建立索引的空间成本和查找的时间成本，可以使用 count(distinct left(列名, 索引长度))/count(*) 越接近与 1 ，那个索引长度就越合适。
数据散列程度高的适合建立索引：散列程度越高，建立索引之后的优化效果就越好 count(distinct 索引列名) / count(*) 的结果大于 30% 就算比较高效的了，可以考虑添加索引了。
在多个字段都要创建索引的情况下，联合索引优于单值索引：联合索引成本低，散列性好。

不建立索引技巧

不要建立太多的索引：因为有空间成本和修改数据删除数据的时间成本。最好一张表少于 6 个。
数据量小的表最好不要使用索引：顺序查询花费的时间可能比通过索引的时间还要短，比如小于 1000 行。
有大量重复数据的列上不要建立索引：重复数据简历 B+ 树查找效率很低。
避免对经常更新的表创建过多的索引：索引对更新是有性能影响的。
不建议用无序的值作为索引：例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
不要定义夯余或重复的索引：浪费性能。

性能分析

希望以后可以把它补充完！