Mysql索引优化-CSDN博客

本文链接：https://blog.csdn.net/qq_46550964/article/details/112441536

一. 什么是索引

索引用于快速查找具有特定列值的行。没有索引，MySQL必须从第一行开始，然后再通读整个表以找到相关的行。表越大，花费时间越多。
如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。

二. 主键优化

表的主键表示您在最重要的查询中使用的一列或一组列。它具有关联的索引，可提高查询性能。查询性能可从NOT NULL优化中受益，因为它不能包含任何NULL值。

如果您的表又大又重要，但没有明显的列或一组列用作主键，则可以创建一个单独的列，并使用自动增量值作为主键。当您使用外键联接表时，这些唯一的ID可用作指向其他表中相应行的指针。

三. 外键优化

如果一个表有很多列，并且您查询了许多不同的列组合，将不常用的数据拆分为单独的表（每个表包含几列），然后通过复制数字ID将它们关联回主表可能会比较有效。
这样，每个小表都可以具有一个主键来快速查找其数据，并且您可以使用联接操作仅查询所需的一组列。根据相关数据的分布方式，查询可能执行较少的I / O并占用较少的缓存，因为相关的列在磁盘上打包在一起。为使性能最大化，查询尝试从磁盘读取尽可能少的数据块；

四. 列索引

索引的最常见类型涉及单个列，该列将来自该列的值的副本存储在数据结构中，从而允许快速查找具有相应列值的行。B树数据结构可以让索引快速查找特定值，一组值，或值的范围，对应于运营商，如WHERE子语句 =， >，≤， BETWEEN，IN，等等。
每个存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎每个表至少支持16个索引，并且索引总长度至少为256个字节。大多数存储引擎都有更高的限制。

索引前缀

使用字符串列的索引规范中的语法，您可以创建仅使用列首字符的索引。以这种方式仅索引列值的前缀可以使索引文件小得多。为a或 column编制索引时，必须为索引指定前缀长度。例如： col_name(N)NBLOBTEXT

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

对于InnoDB使用REDUNDANT 或 COMPACT 行格式的表，前缀的最大长度为767个字节。对于InnoDB使用DYNAMIC 或 COMPRESSED 行格式的表，前缀长度限制为3072字节。对于MyISAM表，前缀长度限制为1000个字节。

注意:
前缀限制以字节为单位，而在前缀长度CREATE TABLE， ALTER TABLE和 CREATE INDEX语句被解释为非二进制串类型的字符数（CHAR， VARCHAR， TEXT对于二进制串类型），并且字节数（BINARY， VARBINARY， BLOB）。为使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑到这一点。

五. 多列索引

MySQL可以创建复合索引（即，多列上的索引）。一个索引最多可以包含16列。对于某些数据类型，可以为列的前缀建立索引。

MySQL可以将多列索引用于测试索引中所有列的查询，或者仅测试第一列，前两列，前三列等等的查询。如果在索引定义中以正确的顺序指定列，则单个复合索引可以加快对同一表的几种查询。

多列索引可以被认为是排序数组，其行包含通过串联索引列的值而创建的值。

假设一个表具有以下规范：

CREATE TABLE c(
	id INT NOT NULL AUTO_INCREMENT,
	last_name CHAR(30) NOT NULL,
	first_name CHAR(30) NOT NULL,
	PRIMARY KEY (id),
	INDEX NAME(last_name,first_name)
);

INSERT INTO c(last_name,first_name)VALUES('张','三');
INSERT INTO c(id,last_name,first_name)VALUES(NULL,'张','亮');

该name指数是在一个索引 last_name和first_name 列。该索引可用于查询中的查找，这些查询指定在已知范围内的last_name和first_name 值组合的值。它也可以用于仅指定last_name值的查询，因为该列是索引的最左前缀。
因此，该name索引用于以下查询中的查找：

SELECT * FROM c WHERE last_name='张';
SELECT * FROM c WHERE last_name='张' AND first_name='三';
SELECT * FROM c WHERE last_name='张' AND (first_name='三' OR first_name='亮');

但是，在以下查询中，name索引不用于查找：

SELECT * FROM c WHERE last_name='张';
SELECT * FROM c WHERE last_name='张' OR first_name='亮';

假设您发出以下 SELECT语句：

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

如果col1和上存在多列索引col2，则可以直接获取相应的行。如果col1和上存在单独的单列索引 col2，那么优化器将尝试使用索引合并优化（请参见第8.2.1.3节“索引合并优化”），或者尝试通过确定哪个索引排除更多行并使用来查找限制性最强的索引。该索引以获取行。

如果表具有多列索引，那么优化器可以使用索引的任何最左前缀来查找行。举例来说，如果你有一个三列的索引(col1, col2, col3)，你有索引的搜索功能 (col1)，(col1, col2)以及 (col1, col2, col3)。

如果列不构成索引的最左前缀，则MySQL无法使用索引执行查找。假设您具有以下SELECT所示的语句：

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;