MySQL索引

1. 作用
1.1 作用
帮助 MySQL 高效获取的数据的数据结果。它对于高性能非常关键，但是人们通常会忘记或误解它，因此建立索引（indexing）是现实中性能问题的首要原因。
1.2 原理
索引页面相对于数据页面来说小得多。当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，再直接通过指针从数据页面中读取数据。

从某种程度上，可以把数据库看作一本书，把索引看作书的目录，通过目录查找书中的信息，显然较没有目录的书方便、快捷。

2. 特点
2.1 索引可以加快数据库的检索速度
2.2 索引降低了数据库插入、修改、删除等维护任务的速度
2.3 索引创建在表上，不能创建在视图上
2.4 索引既可以直接创建，也可以间接创建
2.5 使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引

3. 使用注意

3.1 数字型比字符串型索引更高效，减少甚至避免给字符型建索引
3.2 字符串型的索引尽量使用前缀索引
create index IDX_FORUMDOC_HREF on FORUM_DOC (S_HREF(30));
3.3 避免使用NULL字段
MySQL难以优化引用了可空列的查询、它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要在MySQL 内部进行特殊处理。当可空列被索引的时候，每条记录都需要一个额外的字节，还能导致MyISAM中固定大小的索引（例如一个整数列上的索引）变成可变大小的索引。
3.4 不在索引列做运算或使用函数
无法使用索引，导致全表扫描。
3.5 尽量不用外键，不用连表查询
在JOIN操作中(需要从多个数据表提取数据时)，MySQL只有在主键和外键的数据类型相同时才能使用索引。
3.6 主键会自动建立聚集索引
在 InnoDB 中按照主键顺序插入行。最简单的办法是使用AUTO_INCREMENT列。这会保证是顺序插入的并且提高使用主键联接的性能。最好避免随机（乱序）聚集键（主键）。使用UUID值是不好的选择；它使聚集索引插入是随机的，这是最坏的情形，并且会使数据集完全没有帮助。
3.7 避免负向查询和%前缀模糊查询。
避免负向查询，NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。避免%前缀模糊查询，使用不了索引，导致全表扫描。
3.8 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
3.9 谨防where子句中的OR。
3.10 建议使用in或者union代替
3.11 经常同时存取多列，且每列都含有重复值可考虑建立组合索引
复合索引的前导列一定好控制好，否则无法起到索引的效果。如果查询时前导列不在查询条件中则该复合索引不会被使用。前导列一定是使用最频繁的列。

4. 缺点

4.1 索引还会在硬盘上占用相当大的空间
4.2 MySQL把同一个数据表里的索引总数限制为16个
4.3 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
4.4 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
4.5 每修改数据记录，索引就必须刷新一次。为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项
4.6 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

5. 工具

5.1 explain
      explain是用来分析sql语句，帮助优化的一个命令。执行结果会有id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra几列
5.1.1 列名说明
        type表示链接的类型。比较重要。链接类型由好到坏的，依次是system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。 一般情况，至少要达到range级别，最好是ref级别。否则可能会有性能问题。
        extra是指额外的信息。比较重要。distinct，说明mysql找到了域行联合匹配的行，就不再查找了。not_exits:mysql优化了left_join，一旦找到了left_join匹配的行，便不再进行搜索了。           rang checked for each没有找到理想的索引。using filesort ，则需要改进sql了。这说明 mysql执行 需要 文件排序。这是比较影响效率的。using  temporary , 这是使用了 临时表。 这种情况也比较影响效率，sql需要改进。或者从应用层进行改进。where used 说明使用了where语句。如果 type为 all 或者 index ，一般会出现这样的结果。这样的问题，一般是查询需要改进。
        possible_keys 是指可以应用到该表的索引，如果为NULL则没有。
        key 是指用到的索引。
        key_len 是索引的长度，在不影响查询精度的情况下，值越小越好。
        ref 是指索引的那一列被使用了。一般会是个常数。
        rows 是指有多少行。
        table 表示是哪个表的数据。
5.2 慢日志
      慢查询日志是记录了所有执行超过long_query_time（单位：秒）所设置值的sql语句的文本文件，辅助查询出运行慢的sql，定位问题
      参数
        log_slow_queries表示慢查询功能是否开启
        slow_query_log_file表示慢查询日志的位置
        long_query_time表示慢sql的设定值，超过该值就写入慢查询日志
      查看
        文本文件，可以直接查看
        mysqldumpslow工具，可以进行分类查看

6. 配置参数

    key_buffer_size，设置索引缓存的大小，只适用于MyISAM引擎
    innodb_buffer_pool_size，为InnoDB数据表及其索引而保留的RAM内存量
    innodb_additional_men_pool_size， InnoDB用来存储数据字典(data dictionary)信息和其它内部数据结构(internaldata structures)的存储器组合(memorypool)大小。
    join_buffer_size，在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度，不宜>2M