开心学习
当前位置：数据库 > > 正文

mysql索引优化技巧（MySQL如何优化索引）

时间：2022-01-16 00:18:15类别：数据库

mysql索引优化技巧

MySQL如何优化索引

1.  MySQL如何使用索引

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

大多数MySQL索引（PRIMARY KEY，UNIQUE，INDEX和FULLTEXT）存储在B树（B-tree）中。例外情况：空间数据类型的索引使用R树； MEMORY表还支持哈希索引。 InnoDB对FULLTEXT索引使用倒排列表。

MySQL使用索引进行以下操作：

最后，索引对小表的查询不太重要。当查询需要访问大多数行时，顺序读取比处理索引快。

2.  避免全表扫描

当MySQL使用全表扫描来解析查询时，EXPLAIN的输出在type列中显示ALL。 这通常在以下情况下发生：

对于小表，表扫描通常是合适的，并且对性能的影响可以忽略不计。 

对于大表，可以尝试以下技术，以避免优化器错误地选择表扫描：

3.  列索引

B树（B-tree）数据结构使索引可以在WHERE子句中快速找到与运算符（例如=，>，≤，BETWEEN，IN等）相对应的特定值，一组值或一系列值。 

每个存储引擎都会定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，并且索引总长度至少为256个字节。

索引前缀

用col_name(N)可以创建仅使用列的前N个字符的索引。在InnoDB表中，前缀最长767字节。

全文索引

FULLTEXT索引用于全文搜索。仅InnoDB和MyISAM存储引擎支持FULLTEXT索引，并且仅支持CHAR，VARCHAR和TEXT列。索引始终在整个列上进行，并且不支持列前缀索引。

空间索引

指依据空间对象的位置和形状或空间对象之间的某种空间关系按一定的顺序排列的一种数据结构

MEMORY存储引擎上的索引

默认情况下，MEMORY存储引擎使用HASH索引，但也支持BTREE索引。 

4.  多列索引

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

假设有一张表示这样定义的： 

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
    		•
  • CREATE TABLE test (
  •   id     INT NOT NULL,
  •   last_name CHAR(30) NOT NULL,
  •   first_name CHAR(30) NOT NULL,
  •   PRIMARY KEY (id),
  •   INDEX idx_name (last_name,first_name)
  • );
    		•

    • idx_name索引是建立在last_name和first_name列之上的索引，该索引可以用于指定了last_name和first_name值组合的查询，也可以用于仅指定last_name值的查询，因为该索引是最左前缀匹配的。

    因此，idx_name索引可以用于下列查询：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
    		•
  • SELECT * FROM test WHERE last_name='Jones';
  •  
  • SELECT * FROM test WHERE last_name='Jones' AND first_name='John';
  •  
  • SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');
  •  
  • SELECT * FROM test WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N';
    		•

    • 然而，idx_name索引不能用于下列查询：

  • ?
  • 1
  • 2
  • 3
    		•
  • SELECT * FROM test WHERE first_name='John';
  •  
  • SELECT * FROM test WHERE last_name='Jones' OR first_name='John';
    		•

    • 考虑下面的SQL：

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

    • 如果在col1和col2上存在一个多列索引，那么可以直接抓取适当的行。如果col1和col2上分别存在单独的单列索引，则优化器将尝试使用索引合并优化，或者通过确定哪个索引需要排除更多行来查找限制性最强的索引，并使用该索引来获取行。

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

    如果列不构成索引的最左前缀，则MySQL无法使用索引执行查找。

    再看下面的SQL语句：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
    		•
  • 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;
    		•

    • 如果在（col1, col2, col3）上存在复合索引，那么只有前两个查询会使用。而后最后两个查询不会使用索引来执行查找，因为（col2）和（col2，col3）并不是（col1，col2，col3）的最左前缀。

    5.  B-Tree 和 Hash 索引的比较

    B树索引特征 

    B树（B-tree）索引可用于使用=，>，>=，<，<=，BETWEEN运算符的表达式中的列比较。如果LIKE的参数是一个不以通配符开头的常量字符串，则该索引也可以用于LIKE比较。

    下列这些子句不会使用索引：

  • ?
  • 1
  • 2
  • 3
  • 4
    		•
  • /* the LIKE value begins with a wildcard character */
  • SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
  • /* the LIKE value is not a constant */
  • SELECT * FROM tbl_name WHERE key_col LIKE other_col;
    		•

    • 没有覆盖WHERE子句中所有AND级别的任何索引都不会用于优化查询。换句话说，为了能够使用索引，必须在每个AND组中使用索引的前缀。

    下列WHERE子句会使用索引：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
    		•
  • ... WHERE index_part1=1 AND index_part2=2 AND other_column=3
  •  
  •   /* index = 1 OR index = 2 */
  • ... WHERE index=1 OR A=10 AND index=2
  •  
  •   /* optimized like "index_part1='hello'" */
  • ... WHERE index_part1='hello' AND index_part3=5
  •  
  •   /* Can use index on index1 but not on index2 or index3 */
  • ... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
    		•

    • 下面这些WHERE子句不会使用索引：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
    		•
  • /* index_part1 is not used */
  • ... WHERE index_part2=1 AND index_part3=2
  •  
  •   /* Index is not used in both parts of the WHERE clause */
  • ... WHERE index=1 OR A=10
  •  
  •   /* No index spans all rows */
  • ... WHERE index_part1=1 OR index_part2=10
    		•

    • 有时，即使有可用的索引，MySQL也不使用索引。发生这种情况的一种可能原因是，优化器估计使用索引将需要访问表中很大比例的行。（在这种情况下，表扫描可能会更快，因为它需要更少的查找。）但是，如果这样的查询使用LIMIT只检索某些行，则MySQL仍然使用索引，因为它可以更快地找到返回结果的几行。

    哈希索引特征

    哈希索引与刚刚讨论的索引具有一些不同的特征：

    B-tree

    树型数据结构，广泛用于数据库索引中。该结构始终保持有序，从而可以快速查找精确匹配（等于运算符）和范围（例如，大于，小于和BETWEEN运算符）。 此类索引可用于大多数存储引擎，例如InnoDB和MyISAM。

    因为B树节点可以有很多子节点，所以B树与二叉树不同，后者的每个节点最多只能有2个子节点。

    术语B树的使用旨在参考索引设计的一般类别。由于经典B树设计中不存在复杂性，MySQL存储引擎使用的B树结构可能被视为变体。

    Hash index

    一种索引类型，专用于使用相等运算符而不是范围运算符的查询。 它可用于MEMORY表。 尽管出于历史原因，哈希索引是MEMORY表的默认索引，但是该存储引擎还支持B树索引，对于一般用途的查询而言，B树索引通常是更好的选择。

    6.  优化数据大小

    设计表以使得它们在磁盘上占用最少的空间。 通过减少写入磁盘和从磁盘读取的数据量，这可以带来巨大的改进。 较小的表通常在查询执行期间处理其内容时需要较少的主内存。表数据的任何空间减少都会导致索引变小，从而可以更快地处理索引。

    MySQL支持许多不同的存储引擎（表类型）和行格式。对于每个表，可以决定使用哪种存储和索引方法。为应用程序选择适当的表格式可以大大提高性能。 

    Table Columns

    Row Format 

    为了通过压缩形式存储表数据来进一步减少空间，请在创建InnoDB表时指定ROW_FORMAT=COMPRESSED

    Indexes 

    Joins

    在具有相同数据类型的不同表中声明具有相同信息的列，以加快基于相应列的联接。
    保持列名简单，以便可以在不同的表中使用相同的名称，并简化联接查询。例如，在名为customer的表中，使用name列名代替customer_name。为了使你的名称可移植到其他SQL服务器中，请考虑将名称长度控制在18个字符以内。 

    Normalization

    通常，尽量保持所有数据不冗余(数据库理论中称为第三范式)。为它们分配唯一的id来代替一个重复冗长的值，根据需要在多个较小的表中重复这些id，并通过在join子句中引用id来连接查询中的表。 

    7.  优化数据类型

    数值类型

    行的唯一标识最好使用数值而不是字符串，因为大数值比相应的字符串占用更少的存储字节，因此传输和比较它们更快，占用的内存也更少。

    字符和字符串类型

    其它

    以上就是MySQL如何优化索引的详细内容，更多关于MySQL优化索引的资料请关注开心学习网其它相关文章！

    原文链接：https://www.cnblogs.com/cjsblog/p/14180515.html

    标签：
    mysql 优化 索引
    上一篇下一篇

    猜您喜欢

    热门推荐

    © 2021 开心学习