mysql索引浅析

    谈起索引，首先简单的介绍一下Mysql的存储引擎。

Mysql有多种存储引擎，像

    MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

当然，您可以先看一下您当前版本的MYSQL都提供哪些存储引擎：

    mysql> show engines;

    +――――+―――-+―――――――――――――――――――――-+

    | Engine | Support | Comment |

    +――――+―――-+―――――――――――――――――――――-+

    | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |

    | MEMORY | YES | Hash based, stored in memory, useful for temporary tables |

    | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys |

    | BerkeleyDB | YES | Supports transactions and page-level locking |

    | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |

    | EXAMPLE | NO | Example storage engine |

    | ARCHIVE | NO | Archive storage engine |

    | CSV | NO | CSV storage engine |

    | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |

    | FEDERATED | NO | Federated MySQL storage engine |

    | MRG_MYISAM | YES | Collection of identical MyISAM tables |

    | ISAM | NO | Obsolete storage engine |

    +――――+―――-+―――――――――――――――――――――-+

看你的mysql当前默认的存储引擎:

    mysql> show variables like ‘%storage_engine%’;

你要看某个表用了什么引擎(在显示结果里参数ENGINE后面的就表示该表当前用的存储引擎)

    mysql> show create table 表名;

简单对比MyISAM、InnoDB存储引擎区别。

    MyISAM： Mysql的默认数据库，最为常用。

    拥有较高的插入，查询速度，它提供高速存储和检索，以及全文搜索能力，但不支持事务，表级锁。

    MyISAM把表存储在两个文件中。一个数据文件，一个索引文件。扩展名为.MYD和.MYI。也就是你可以把这两个文件拷贝在任意的平台上。

    InnoDB ：事务型数据库的首选引擎，支持ACID事务，支持行级锁定。

    其他的如果您有兴趣可以去研究一下，相关这方面的资料很多的呵呵。

下面我们就以MyISAM 来谈索引。

    索引是在存储引擎层实现的，而不是服务器层。因此，它们并不是标准化的，每个引擎的索引工作方式略有不同。

    即使多个引擎支持同样的索引，它们的实现方式也可能有所不同。

    下面我们针对MyISAM存储引擎谈谈最频繁使用的B-Tree索引类型。

    (这种索引在 Innodb 存储引擎中被称为 Secondary Index，后面稍加介绍)

B-Tree定义

    1970年，R.Bayer和E.mccreight提出了一种适用于外查找的树，它是一种平衡的多叉树，称为B树。

    如下图，主键索引和非主键索引除了叶子结点上存储的分别是PK value 和普通key value之外，结构完全一致。

    

我们可以创建的一些索引类型。(MyISAM)

     1) 主键索引(PRIMARY KEY)

     每张表只有一个，保证数据记录集的唯一性。

     2)唯一性索引

     主键索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY KEY而不是UNIQUE。

     3)普通索引(由关键字KEY或INDEX定义的索引)

     4)全文索引(innodb不支持)(FULLTEXT)

     可以在 CHAR、VARCHAR 或 TEXT 列上创建

     5)多列索引(或复合索引)

     根据查询的需求设置，可以完成多条件查询，尤其大数据量查询时可以大大提升查询速度。

浅析索引优化及SQL优化

    我们来看一张示意图，只是表示关系图，请注意并上的数据并不是代表数据实际存储位置。

    看图说话及提问：

图上建立了(uid,catid,day)的复合索引

在表建立后，有了数据，MYSQL会按着你建立的索引对应数据去生成上面的结构，每一个根到叶子到根的距离都是一样的，是有序的。

从上图可以看出如果你的uid是定值(=)，catid是定值(=)，day(=)是定值，是最理想状态，完全用到索引

如果你的uid是定值，catid是定值，day是范围，也可以完全用到

uid定值，catid范围，day是范围，则可以用到uid和catid，因为catid您使用了范围，后面的day就用不到啦，因为排好的树不能满足您的查询需求啦。

？1―-如果uid定值，catid范围，day范围，那可以用到哪个索引，为什么呢？

？2―-如果表中的数据更新或是新增，那么想问，上述排好序的索引会不会改变呢？

    能使用B-Tree索引的查询

匹配全名

匹配最左前缀

匹配范围值

精确匹配一部分并且匹配某个范围中的另一部分。

只访问索引的查询

    局限性

如果查找没有从索引列的最左边开始，它就没什么用处。

不能跳过索引中的列。

存储引擎不能优化访问任何在第一个范围条件右边的列。

    我们来详细说明一些我们在写SQL时或使用索引经常会模糊的地方及要遵守的约定。

    最完美的您设定的索引完全使用，而非全表扫描。

索引的顺序规范。

     注：建立索引时的顺序非常关键，如果是复合索引，就是多列的，请一定要仔细分析业务查询，将限定查询的字段放在最前面，最常用必用的放在最前面，范围查询的字段尽量后靠。

     因为索引是不能跳过索引中的列，所以一个不常用的或范围列放在前面会导致后面的索引字段无法使用。

索引的顺序和where条件中字段的顺序请尽量保持一致

     通常mysql会调整自己where语句中查询条件字段的顺序，而不是以查询字段的顺序为准，它是进行查询优化，去选择合适的索引。

     目前我的mysql 5.0.45或77中会根据索引情况去选择合适的索引使用，所以where条件用的顺序与索引并不完全相关，

     但之前有些版本要求两者一致，所以我们尽量保持一致，这样符合我们业务及可读性，同样避免一些版本处理不同的问题。

     当然您可以通过测试来看看执行计划进行索引分析。

不是索引越多越好。

     索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。(上面的问题2应该知道答案了吧)

尽量避免NULL(根据实际情况慎重选择default NULL)

     应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

     如select a from t where a-5>100;如果a上有索引则用不到，会进行全表扫描；

     Select a from t where a>105;则可以用上索引啦。

如若对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

请避免使用select * 去查询，请列出你所实际要询的字段列，不要返回任何你用不到的字段，慷慨不是好事。

说一个强制使用某索引的方法：EXPLAIN SELECT uid

     FROM tablename

     FORCE INDEX ( uid_cid )

     WHERE uid = 1

     AND cid > 1(自己试试吧，呵呵！)

    好啦，目前先说这些，都是一些归纳总结，主要就是先铺个概念，大家了解一下存储引擎，支持的索引类型，以及实际建立及使用时的注意事项。

    后续如果有时间的话，会介绍查看执行计划的explain工具，以及mysql 中show 命令，你可以想show 什么就show 什么。或者是建立表时“set utf8 collate utf8_bin NOT NULL default ””这么一行有什么作用，呵呵，后续探讨。

    同样大家感兴趣的话，可以自己去研究，还是依然推荐那本老少咸宜的宝书《高性能MYSQL》。

    如理解上有偏差，欢迎各位同学批评指正，交流产生进步哈哈！