mysql索引优化

#mysql索引优化

##前言提高select查询性能，最好的方法，是在一个或多个列中创建索引。索引条目就像是指向行的指针，在查询过程中可以快速确定哪些行，符合WHERE条件，并检索这些行的其他列值。所有的MySQL数据类型可以被索引。

虽然很容易为每一个可能的列在查询中创建一个索引。但是不必要的索引，会浪费空间和时间，指标对MySQL的使用时间，同时会增加insert、update和delete操作的成本，因为每个索引都必须更新。必需找到一个最佳的平衡来实现快速的查询。

##mysql如何使用索引索引用于快速查找特定的列值的行。如果没有索引，MySQL必须从第一行开始，然后通过读取整个表，以找到相关的行。表越大，开销越大。如果给某列添加了索引，mysql可以快速确定查找的位置，而不需要进行全表扫描。这比按顺序读取每一行要快得多。

大多数MYSQL索引(PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT)是存储为B-trees,有一些例外：Indexes on spatial data types use R-trees;MEMORY表还支持hash索引。INNODB对于全文索引，使用倒序列表。

mysql索引使用

1.更快速的匹配到WHERE子句。 2.基于消除行的考虑，如果有多个索引可供选择，mysql通常会选使用匹配到最小行数的索引。 3.如果是一个复合索引，任何最左前缀，可以用于优化查询。例如有一个复合索引(col1,col2,col3)，那么(col1), (col1, col2), and (col1, col2, col3)都可以使用到索引。 4.可以用来提高JOIN查询效率。如果字段的类型和长度申明为一致，则可以使用索引。VARCHAR和CHAR通常认为是一致的类型，如果它们的长度指定为相同的话。 VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.

5.To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example:

SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;

6.对于group by 和order by的优化

7.覆盖索引。在某些情况下，可以优化查询以检索值，而不检索数据行。（一个索引，它提供了一个查询的所有必要的结果，被称为一个覆盖索引），如果select查询中的列，包含在索引中，那么值将从索引中直接返回，这样速度将非常快。

索引对于较小的表，或者是一个较大的表，需要读取表中大部分行的查询时，显得更没那么重要。当一个查询需要访问大多数的行时，读取顺序比通过索引工作的速度更快。顺序读取最大限度地减少磁盘的查找，即使不是所有的行都需要查询。

##列索引Column Indexes 单列索引是较为常见的，它通过拷备列的值为副本，存储在相应的数据结构中。B-tree数据结构，可以让索引快速的找到相应的值，或一个范围的值，对应的如在WHERE子句中的(=,>,<=,BETWEEN,IN等等)

每种存储引擎对索引个数和索引长度的限制不同，一张表至少是支持16个索引，至少的索引长度是256字节，甚至更高。

##前缀索引Prefix Indexes

对于一个字符串类型的列，可以为索引指定前N个字符，使用列的前N个字符作为索引，这样可以让索引文件变得更小，BLOB or TEXT 字段必需指定前缀长度，如

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

前缀长度可以达到1000字节(innodb表767字节，除非你使用innodb_large_prefix选项设置)

Note

Prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements is interpreted as number of characters for nonbinary string types (CHAR, VARCHAR, TEXT) and number of bytes for binary string types (BINARY, VARBINARY, BLOB). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.

##全文索引FULLTEXT Indexes 全文索引是用于全文搜索。只有InnoDB和MyISAM存储引擎支持全文索引，并且只支持(char、varchar、text)列。索引总是发生在整个列和列前缀索引不支持的时候。 Optimizations are applied to certain kinds of FULLTEXT queries against single InnoDB tables. Queries with these characteristics are particularly efficient:

FULLTEXT queries that only return the document ID, or the document ID and the search rank.

FULLTEXT queries that sort the matching rows in descending order of score and apply a LIMIT clause to take the top N matching rows. For this optimization to apply, there must be no WHERE clauses and only a single ORDER BY clause in descending order.

FULLTEXT queries that retrieve only the COUNT(*) value of rows matching a search term, with no additional WHERE clauses. Code the WHERE clause as WHERE MATCH(text) AGAINST ('other_text'), without any > 0 comparison operator. 

##空间索引Spatial Indexes

You can create indexes on spatial data types. MyISAM and (as of MySQL 5.7.5) InnoDB support R-tree indexes on spatial types. Other storage engines use B-trees for indexing spatial types (except for ARCHIVE, which does not support spatial type indexing). Indexes in the MEMORY Storage Engine

The MEMORY storage engine uses HASH indexes by default, but also supports BTREE indexes.

##复合索引

MySQL可以创建复合索引（即针对多个列的索引），一个复合索引最多包含16列个列。

最左前缀原则

对于一个复合索引，任何最左前缀，可以用于优化查询。例如有一个复合索引(col1,col2,col3)，那么(col1), (col1, col2), and (col1, col2, col3)都可以使用到索引。如果存在复合索引(col1, col2, col3)，那么以下第1,2条语句使用索引，第3,4语句不能使用到索引。

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;

复合索引可以被认为是一个已排序的数组，其中索引包含的行的数据，是由相关的索引的列联系在一起的。

Note

As an alternative to a composite index, you can introduce a column that is “hashed” based on information from other columns. If this column is short, reasonably unique, and indexed, it might be faster than a “wide” index on many columns. In MySQL, it is very easy to use this extra column:

SELECT * FROM tbl_name
  WHERE hash_col=MD5(CONCAT(val1,val2))
  AND col1=val1 AND col2=val2;

假设现在有一个表的定义如下：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

其中name是一个包含2个列的复合索引，可用于查找last_name和first_name在已知范围内的组合。

以下的查询都可以用到索引

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

但是，以下查询将不会使用到索引

不符合最左前缀原则
SELECT * FROM test WHERE first_name='Michael';

使用了OR，范围未知
SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

##索引合并

对于以下SQL查询

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

如果存在复合索引(col1,col2)那么直接使用复合索引。不存在复合索引，但存在col1、col2单独的列索引，优化器会尝试使用索引合并操作。或者选择最少行的索引

http://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html http://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html http://dev.mysql.com/doc/refman/5.7/en/column-indexes.html http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

http://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html http://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_covering_index http://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html http://dev.mysql.com/doc/refman/5.7/en/how-to-avoid-table-scan.html

IT码农

mysql索引优化

Search

Table of Contents