Mariadb 简明教程

MariaDB - Indexes & Statistics Tables

索引是加速记录检索的工具。索引为索引列中的每个值生成一个条目。

Indexes are tools for accelerating record retrieval. An index spawns an entry for each value within an indexed column.

有四种类型的索引 −

There are four types of indexes −

  1. Primary (one record represents all records)

  2. Unique (one record represents multiple records)

  3. Plain

  4. Full-Text (permits many options in text searches).

在这种用法中，术语 “键”和 “索引”是相同的。

The terms “key” and “index” are identical in this usage.

索引与一列或多列关联，并支持快速搜索和高效的记录组织。在创建索引时，考虑在查询中经常使用的列。然后在这些列上创建一个或多个索引。此外，将索引视为主键表。

Indexes associate with one or more columns, and support rapid searches and efficient record organization. When creating an index, consider which columns are frequently used in your queries. Then create one or multiple indexes on them. In addition, view indexes as essentially tables of primary keys.

尽管索引加快搜索或 SELECT 语句，但它们会因为对表和索引执行操作而拖慢插入和更新速度。

Though indexes accelerate searches or SELECT statements, they make insertions and updates drag due to performing the operations on both the tables and the indexes.

Create an Index

可以通过 CREATE TABLE…​INDEX 语句或 CREATE INDEX 语句创建索引。支持可读性、维护性和最佳实践的最佳选项是 CREATE INDEX。

You can create an index through a CREATE TABLE…​INDEX statement or a CREATE INDEX statement. The best option supporting readability, maintenance, and best practices is CREATE INDEX.

查看索引的一般语法，如下所示：

Review the general syntax of Index given below −

CREATE [UNIQUE or FULLTEXT or...] INDEX index_name ON table_name column;

查看其使用示例：

Review an example of its use −

CREATE UNIQUE INDEX top_sellers ON products_tbl product;

Drop an Index

可以使用 DROP INDEX 或 ALTER TABLE…​DROP 删除索引。支持可读性、维护性和最佳实践的最佳选项是 DROP INDEX。

You can drop an index with DROP INDEX or ALTER TABLE…​DROP. The best option supporting readability, maintenance, and best practices is DROP INDEX.

查看删除索引的一般语法，如下所示：

Review the general syntax of Drop Index given below −

DROP INDEX index_name ON table_name;

查看其使用示例：

Review an example of its use −

DROP INDEX top_sellers ON product_tbl;

Rename an Index

使用 ALTER TABLE 语句重命名索引。查看其给出的常用语法：

Rename an index with the ALTER TABLE statement. Review its general syntax given below −

ALTER TABLE table_name DROP INDEX index_name, ADD INDEX new_index_name;

查看其使用示例：

Review an example of its use −

ALTER TABLE products_tbl DROP INDEX top_sellers, ADD INDEX top_2016sellers;

Managing Indexes

您需要检查并跟踪所有索引。使用 SHOW INDEX 列出与给定表关联的所有现有索引。您可以通过使用诸如“\G”之类的选项来设置显示内容的格式，它指定垂直格式。

You will need to examine and track all indexes. Use SHOW INDEX to list all existing indexes associated with a given table. You can set the format of the displayed content by using an option such as “\G”, which specifies a vertical format.

查看以下示例：

Review the following example −

mysql > SHOW INDEX FROM products_tbl\G

Table Statistics

由于提供更快地访问记录和统计信息，索引被大量用于优化查询。然而，许多用户发现索引维护很繁琐。MariaDB 10.0 提供了与存储引擎无关的统计表，该表会计算每个存储引擎中每张表的统计信息，甚至会计算未被索引的列的统计信息。

Indexes are used heavily to optimize queries given the faster access to records, and the statistics provided. However, many users find index maintenance cumbersome. MariaDB 10.0 made storage engine independent statistics tables available, which calculate data statistics for every table in every storage engine, and even statistics for columns that are not indexed.