8.3.?Optimization and Indexes(优化索引)

    提高SELECT操作的性能，最好的办法是在一个或多个列上创建索引。索引条目像指针表行，允许查询快速确定哪些行符合条件的WHERE子句中，和检索这些行的其他列的值。MySQL所有数据列都可以创建索引。

    但是在所有列上创建索引会影响MySQL性能。因为需要维护这些索引。

1.MySQL如何使用索引

    索引用来快速地找到特定的列值的行。如果没有索引，MySQL必须从第一个行，然后读出整个表中，以找出相关行。大表时，该操作将花费更多时间。

    大多数MySQL索引（主键，唯一，索引和FULLTEXT）存储在B-trees结构中。例外是对空间数据类型的索引使用R-trees，并且MEMORY表还支持哈希索引。

    在一般情况下，索引被作为在下面的讨论中描述。具体到哈希索引（如MEMORY表使用）特性在本节末尾描述。

    MySQL使用索引这些操作：

• 快速找到匹配WHERE子句的行。
  
  
• 如果有多个指标之间进行选择，MySQL通常会使用找到最少行的索引（最有选择性的索引）。
  
  
• 当进行表之间的Join时，MySQL能更有效地使用索引列上，如果他们被声明为相同的类型和大小。在此上下文中，VARCHAR和CHAR被认为是相同的，如果他们被声明为相同的大小。例如，VARCHAR（10）和CHAR（10）是相同的大小，但是VARCHAR（10）和CHAR（15）是不一样的。
  
  
  不同列之间不能进行比较，但可以通过转换方式进行比较。假设一个数字列进行比较字符串列。在数字列一个给定的值如1，它可能在字符串列比等于任意数量的值如“1”，“1”，“00001”，或'01.e1'。
  
  
• 要找到MIN（）或MAX（）值的特定索引的字段key_col 。这是由预处理程序，检查是否使用优化的WHERE key_part_N= constanton key_colin索引之前发生的所有关键部件。在这个例子，MySQL的做了一个键查找每个MIN（）或MAX（）表达式并取代它的常量。如果所有的表达式替换为常量，查询将返回一次。例如：
      SELECT MIN(key_part2),MAX(key_part2) 
      FROM tbl_nameWHERE key_part1=10;
  
  
• 要排序或分组一个表，如果排序或分组在一个可用键的最左前缀完成（例如，ORDER BY key_part1, key_part2 ）。如果所有的关键部分都按照倒序（DESC），关键词读取顺序相反。
  
  
• 在某些情况下，查询可优化来检索值，而无需咨询数据行（它提供了所有必要的结果的查询的索引称为覆盖索引）。如果查询使用的列列时数值型并在前缀的最左面，所选择的值可以从索引树中检索得到更快的速度。
      SELECT key_part3FROM tbl_name 
      WHERE key_part1=1
  
  
  在小表中索引显得不是那么重要，或者在报表中需要查询大表所有行。当查询需要访问大部分行，按顺序读取比从索引读取速度快。连续读取最小化磁盘寻道，即使需要对查询不是所有的行。
  
  

2.使用主键

   在表最重要或经长时间查询的列上创建主键。它有一个相关的快速查询性能指标。从NOT NULL优化查询性能优势，因为它不能包含任何空值。在InnoDB存储引擎，表中的数据被物理地组织基于主键列或多列做超快速查找和排序。

    如果是重要的大表，但没有明显的列或可以设置为主键使用的列，可以创建一个单独的自动递增的列作为主键使用。当你使用外键连接表这些独特的ID可以作为指针相应行到其他表。

3.外键

4.索引列

 

    索引的最常见的类型包括单列，在数据结构中存储从该列复制出的值，允许的行快速查找与对应的列值。B树的数据结构可以让索引快速找到一个特定的值，一组值，或一个范围的值。对应的操作如=, >, ≤, BETWEEN, IN,等等。

    前缀索引

        与col_name(N)在索引规范语法 ，可以创建一个只是用列值前N个字节的索引。只使用列值的前缀索引会很小。当索引列是BLOB或 TEXT        类型时，必须指定一个前缀长度来创建索引。例如：

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

        

        前缀索引长度可以有1000 bytes（InnoDB表示767 bytes）。前缀索引单位是字节（bytes），而前缀长度在CREATE TABLE语句被解释为字符数。使用多字节字符集时指定前缀长度为列一定要考虑到这一点。

    全文索引

        可以通过创建全文索引，来搜索全文。只有InnoDB和MyISAM存储引擎支持全文索引并且创建索引列的数据类型是char,vachar和TEXT。索引同时创建在整个列和创建前缀索引是不支持的。

        优化适用于某些类型的FULLTEXT查询对单InnoDB表。查询这些特性是特别有效：

• 全文索引 查询只返回的文档ID，或将文档ID和搜索排名。
  
  
• 全文索引 查询该降序得分的匹配行进行排序，并应用LIMIT子句取前N个匹配的行。对于这种优化应用，必须没有WHERE子句，只有一个有降序排列的ORDER BY子句。
  
  
• 全文索引 在检索匹配搜索词的行只有COUNT（*）值的查询，没有额外的WHERE子句。

         

        空间索引

            可以在空间类型字段上创建空间索引。只有MyISAM支持在空间类型上创建R-tree索引。在其他存储引擎支持在空间类型上创建B-tree索引（ARCHIVE存储引擎不支持空间索引）。

        MEMORY存储引擎上的索引

                在MEMORY存储引擎默认是Hash索引，但是也支持BTREE索引。

5. 多列索引（Multiple-Column Indexes）

    MySQL的复合索引，最多可以包含16列。MySQL能使用多列索引的查询来测试所有列的索引，或者查询 该测试仅在第一列中，前两列，前三列，依此类推。如果以正确的顺序列指定了索引定义，一个复合索引可以在同一个表加快多个查询。

    一个多列索引可以被认为是一个排序的数组，该行包含的是通过串联的索引列的值创造的值。
    

        | 注意：作为一个复合索引，可以提出一个“hashed” 基于其他列中的信息栏。如果这个列短，合理的约束，并且是索引，它可能比
        |            许多列“wide”的索引快。在MySQL中，它是非常简单去使用这个额外的列：
        |            SELECT * FROM tbl_name  WHERE hash_col=MD5(CONCAT(val1,val2))  AND col1=val1AND 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索引包含了last_name,first_name两个列。该索引可用于查询指定的值在一个已知的范围内的last_name和first_name值的组合查询。它也可以用于查询last_name值因为列是一个索引的最左前缀。因此，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';

    但是，name索引不能用在下面的查询：

    SELECT * FROM test WHERE first_name='Michael';

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

    假设你发出以下SELECT命令：

    mysql> SELECT * FROM tbl_nameWHERE col1=val1AND col2=val2;

    如果在col1和col2上有复合索引，该恰当的行可以直接得到。如果col1和col2上单独有索引，优化器尝试使用索引合并优化，或者试图找到更匹配的索引。

    如果一个表上有一个复合索引，该指数的任何最左前缀可以由优化器用来找到行。例如：在(col1, col2, col3)三列上有一个复合索引，该索引可以搜索在(col1), (col1, col2), 和(col1, col2, col3)。

    如果该列不是最左侧的前缀列，MySQL无法使用复合索引。
   

6. 验证索引的使用

    使用EXPLAIN命令经常检查你所有的查询使用索引。

7. MyISAM和InnoDB索引收集统计数据

    存储引擎收集关于表的统计信息将使用在优化上。优化的目的，一个重要的统计平均值组的大小。

    MySQL使用平均值组的大小在以下方面:

• 评估每个ref访问读取多少行。
  
  
• 评估一个部分的join将产生多少行；这是，这种操作将产生的行数：
  (...) JOIN tbl_nameON tbl_name.key= expr

    一个索引的平均值组大小的增加。

    join是基于<=>为比较操作符，NULL不同于任何值

    在MyISAM和InnoDB表，可以用innodb_stats_method和myisam_stats_method系统变量收集表统计数据。这些变量有三个可能：

• 当变量设置为nulls_equal，所有NULL值视为相同的。（那就是，他们都形成一个单值群）。
  
  如果NULL值组的大小要超过一般的no-Null值的组的大小，该方法的平均值组的大小向上倾斜。因此，nulls_equal 方法可能导致优化器不使用索引来访问时。
  
  
• 当变量设置为nulls_unequal，NULL值是不相同的。
  
  
• 当变量设置为nulls_ignored，NULL值将被忽略。
  

    可以使用下面方法更新表信息：

• 执行myisamchk --stats_method=method_name--analyze
  
  
• 更改表使其统计淘汰（例如，insert一些行或delete一些行），然后设置myisam_stats_method并执行ANALYZE TABLE命令

8. 比较B-Tree和Hash索引

    B-Tree索引特点：    

        B-Tree索引可以比较下面的表达式 =,>,>=,<,<=或between 。也能匹配%表达式，但是不能以匹配符为开始。

    Hash索引的特点：

• Hash索引只能匹配 = 或 <=>表达式。而不能匹配范围表达。
  
  
• Hash索引无法被用来避免数据的排序操作。
  
  
• Hash 索引不能利用部分索引键查询。
  
  
• Hash 索引在任何时候都不能避免表扫描（使用B-tree索引的最左前缀的关键，任何能找到的行）。