15、INDEX索引（下）

管理索引

创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (column_list);
help CREATE INDEX;

INDEX：默认类型，用于普通的索引。
UNIQUE：唯一索引，保证列值唯一。
FULLTEXT：全文索引，用于全文搜索。
SPATIAL：空间索引，用于空间数据类型的搜索
其中，UNIQUE、FULLTEXT 和 SPATIAL 是索引类型，index_name 是索引的名称，table_name 是要创建索引的表名，column1、column2 等是要创建索引的列名，ASC 和 DESC 表示升序和降序。

查看索引

SHOW INDEXES FROM [db_name.]tbl_name;

删除索引

DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

优化表空间：

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

"SET GLOBAL userstat=1;" 是 MySQL 的语法命令，用于启用或禁用用户统计信息的收集。此命令用于修改 userstat 系统变量的全局值，以更改服务器跟踪和记录连接用户使用统计信息的行为。
当启用用户统计信息时，MySQL 收集有关连接次数、查询次数和其他性能相关统计信息的数据。收集的数据可用于识别瓶颈，识别性能较慢的查询，并优化数据库性能。通过将 userstat 变量设置为 1，您可以启用所有连接用户的用户统计信息收集。userstat 的默认值为 0，这意味着仅在查询中明确指定时，服务器才会收集用户统计数据。
需要注意的是，userstat 功能仅适用于某些版本和设置的 MySQL。此外，启用用户统计信息可能会增加处理和存储开销，因此应谨慎使用，特别是在生产环境中。

EXPLAIN 工具

explain使用介绍：​​https://dev.mysql.com/doc/refman/5.7/en/explain-output.html​​

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

输出的第一列是“id”号，它表示查询中使用的表计划的标识符。每个表都是唯一的，并且查询计划以id的值列表排序。

接下来是“select_type”，它表示用于生成结果的查询类型。查询类型分为几类：简单的SELECT、联合查询、子查询等等。每一种查询类型都对应着不同的查询计划。

“table”指查询计划中包含的表，这里也包括用于查询的该表的别名（如果存在）。

“partitions”表示查询计划中涉及的分区，如果使用了分区表的话。

“type”表示用于将行匹配到结果的查询方式，也称为访问类型；它是查询计划优化的关键因素之一。MySQL的查询访问类型可以是：ALL、index、range、ref等等。ALL是最差的情况，它表示将扫描整个表以找到匹配项。更好的访问类型将使用主键或索引以避免扫描整个表。

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const >eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range >
index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

“possible_keys”包含可能用到的索引的名称，这些索引与查询中的WHERE条件或ORDER BY子句相关。MYSQL优化器会尝试从这些索引中选择一个最好的索引。

“key”字段代表查询所使用的索引。最佳索引应该尽可能的窄和唯一，具有高选择性和较低的重复性。

“key_len”表示MySQL使用的索引的长度，与使用的索引相关。

“ref”是指连接该表和索引（或常量、表达式等）的列或常量，这里也包括任何使用的类型转换。如果此列输出“const”，则表示此查询使用了常量连接。

“rows”代表MySQL估计查询将扫描的行数。

“filtered”表示查询结果有多少行满足WHERE过滤器的条件。

“Extra”列包含有关执行计划的其他信息，例如文件排序、临时表、查询优化器使用等等。它可以帮助您确定性能问题的根本原因以及是否使用了合适的查询优化策略。

通过分析EXPLAIN工具的输出，您可以了解您的查询计划的执行情况，识别潜在问题并进行必要的查询优化和调整，以获得更好的查询性能和效率。

索引优化

1、合理选择索引类型：不同的数据类型和查询方式需要使用不同的索引类型。比如，B-Tree 索引适用于全值匹配或者是前缀匹配，而哈希索引则适用于等值查询。

2、优化复合索引：复合索引适用于多列查询，但是如果列的顺序不正确，可能会影响索引效率。因此，需要优化复合索引的列顺序，将最常用的列放在前面。

3、避免使用过多的索引：过多的索引会增加写操作的成本，并占用更多的磁盘空间。因此，需要评估每个索引的作用，避免使用不必要的索引。

4、避免在索引列上使用函数：函数会导致 MySQL 无法使用索引，从而降低查询效率。如果必须使用函数，可以考虑将函数的结果存储到另外一个列中，并在该列上建立索引。

5、避免使用 LIKE 查询：LIKE 查询会导致全表扫描，从而降低查询效率。如果必须使用 LIKE 查询，可以使用全文检索或者正则表达式进行优化。

6、对于 InnoDB 存储引擎，可以使用聚簇索引：聚簇索引可以避免在查询时需要回表，从而提高查询效率。

7、定期进行索引维护：索引维护可以优化索引的性能，包括重新组织表、优化表碎片、优化索引等。

语句使用索引优化建议

1、独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）

2、左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度

3、多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引

4、只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

5、对于有多个列where或者order by子句，应该建立复合索引

6、尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高

7、不要使用RLIKE正则表达式会导致索引失效

8、查询时，能不要*就不用*，尽量写全字段名，比如:select id,name,age from students;

9、大部分情况连接效率远大于子查询

10、在有大量记录的表分页时使用limit

11、对于经常使用的查询，可以开启查询缓存