11数据库优化

11.1存储引擎

11.2索引

11.2.1索引概述

索引是一种特殊的数据结构，可以看作是利用MySQL提供的语法将数据表中的某个或某些字段与记录的位置建立一个对应关系，并按照一定顺序排序好，以便于快速定位数据的位置。

索引的大致分类

普通索引
唯一性索引
主键索引
全文索引
空间索引

若创建的索引是从左开始截取数据表中字段值的一部分内容，这种字段可以统称为前缀索引。根据索引开始的部分字符，可以大大节约索引空间，提高索引的效率。

索引原则

避免对经常更新的表创建过多的索引。
可以对数据量大，更新较少，查询较多的表创建多个索引。
在视图上创建索引可以显著提升查询性能。
尽量不要在含有大量重复的值的字段建立索引。
在主键上创建索引可以大幅提升查询速度。

11.2.2索引的基本操作

11.2.2.1创建索引

索引可以在创建数据表或对已创建的数据表进行添加。

# 创建数据表时创建索引
CREATE TABLE 表名(
    字段名 数据类型 [约束条件]
    ...
    PRIMARY KEY [索引类型] (字段列表) [索引选项],
    {INDEX | KEY} [索引名称] [索引类型] (字段列表) [索引选项],
    UNIQUE [INDEX | KEY] [索引名称] [索引类型] (字段列表) [索引选项],
    {FULLTEXT | SPATIAL} [INDEX | KEY] [索引名称] (字段列表) [索引选项],
)[表选项];

# ALTER TABLE 向已创建的数据表添加索引
ALTER TABLE 表名 
    ADD PRIMARY KEY [索引类型] (字段列表) [索引选项]
    |ADD {INDEX | KEY} [索引名称] [索引类型] (字段列表) [索引选项]
    |ADD UNIQUE [INDEX | KEY] [索引名称] [索引类型] (字段列表) [索引选项]
    |ADD FULLTEXT [INDEX | KEY] [索引名称] (字段列表) [索引选项]
    |ADD SPATIAL [INDEX | KEY] [索引名称] (字段列表) [索引选项],...;

# CREATE INDEX 向已创建的数据表添加索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名称
    [索引类型] ON 数据表名 (字段列表) [索引选项] [算法选项 | 锁选项];

在使用时只需要确定三点，一是确定采用哪种索引创建索引；二是创建哪些索引；三是为各索引创建设置选项，具体如如下表所示，其中主键索引不能设置索引名称，其它索引的名称也可以省略，默认使用建立索引的字段表示，复合索引则使用第一个字段的名称作为索引名。

索引选项	语法
索引类型	USING {BTREE\|HASH}
字段列表	字段[(长度)[ASC\|DESC]]
索引选项	KEY_BLOCK_SIZE [=] 值 \| 索引类型\|WITH PAESER 解析器插件名 \| COMMENT '描述信息'
算法选项	ALGORITHM [=] {DEFAULT\|INPLACE\|COPY}
锁选项	LOCK [=] {DEFAULT\|NONE\|SHARED\|EXCLUSIVE}

在上表中只有字段列表是必选项，其余均为可选。索引选项中的KEY_BLOCK_SIZE只能在M有ISAM存储引擎的表中使用。表示索引的大小(以字节为单位)，WITH PAESER只能用于全文索引。此外，对于全文索引和空间索引不能设置索引类型，而索引类型在不同的存储引擎中也不相同。如InnoDB和MyISAM支持BTREE，而MEMORY则同时支持BTREE和HASH。

普通索引与主键索引

ALTER TABLE tablename ADD INDEX name_index (name);

唯一性索引

ALTER TABLE tablename
ADD UNIQUE INDEX unique_index (id);

全文索引

ALTER TABLE tablename
ADD FULLTEXT INDEX ft_index (content);
-- 创建完成后，查询数据使用全文索引需要采用MySQL提供的特定语法
MATCH (字段列表) AGAINST (字符串);

空间索引

# 创建数据表
CREATE TABLE tablename (space GEOMETRY NOT NULL);
# 添加空间索引
ALTER TABLE tablename ADD SPATIAL INDEX(space);
-- 为space字段创建空间索引时，要保证该字段的数据类型为空间数据类型，其中，GEOMETRY是具有层次结构的几何空间类型

单列索引和复合索引

在这以上的索引都为单列索引，即仅对一个字段设置的索引，与之对应的是复合索引，即多个字段上创建一个索引。
多个字段的设置顺序要遵循“最左前缀原则”，也就是把最频繁使用的字段放在最左侧，然后依次类推。

ALTER TABLE tablename ADD INDEX multi(name,price,keyword);
-- multi为复合索引名称，只有name被使用该复合索引才会被使用

前缀索引

在实际情况中，有事需要所有的字段是一段很长的字符串(如TEXT、很长的VARCHAR等)，这时会导致索引变大且检索速度也会变慢。解决办法可以采用前缀索引的方式，在保证高查询效率的同时避免空间浪费。

设置前缀索引时，字符串的设定需要通过一定的计算和测试从才能够选取最合适的一个范围。这里使用”不重复得到索引数量/总记录数“的方法，比对查询时不设置长度与设置长度最相近的值，然后利用获取的这个值设置前缀索引。

ALTER TABLE tablename ADD INDEX (keyword(3));-- 假设3为最合适的值

值得一提的的是，在MySQL的索引分类中，全文索引和空间索引不支持前缀索引的设置。

Hey, IO0288!

MySQL语法#4