mysql创建索引的原则-原创手记-慕课网

在MySQL中建立索引的原则

（1） 最左前缀匹配原则 ，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（2）=和in可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。（不知道这句话的依据是什么？不过可以通过实际测试来检验一下）

（3）尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

（4）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可(索引越少越好；原因：主要在修改数据时，每个索引都要进行更新，降低写速度)。

参考说明：

MySQL一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。创建多列索引的意义就是为了‘减少io操作’。

InnoDB使用B+Tree作为索引结构（聚集索引）。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

在知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Always check whether all your queries really use the indexes that you have created in the tables. Use the EXPLAINstatement, as described in Section 8.8.1, “Optimizing Queries with EXPLAIN” .

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization (see Section 8.2.1.4, “Index Merge Optimization” ), or attempts to find the most restrictive index by deciding which index excludes more rows and using that index to fetch the rows.

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

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;

If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and(col2, col3) are not leftmost prefixes of (col1, col2, col3).

参考说明2：

https://mariadb.com/kb/en/index-condition-pushdown/

创建多列索引(列1，列2，列3)后的结构类似这样(index结构，附属列，附属列)
附属列可能直接跟在叶节点上，或单独存放（这个没有看到说明）…
附带可以说明单列索引就是多列索引一种形式，只不过没有附属列罢了…

搜索关键字：

mysql b tree
mysql 索引原理
mysql Composite index
mysql 联合索引

参考链接：

原文链接：http://outofmemory.cn/mysql/how-to-create-index