索引的创建原则

2.索引结构
了解索引结构和使用索引原理有利于对创建原则有更好的理解，知道在使用索引时，索引是如何提高查询效率的就会知道在创建索引时会有哪些影响。

首先mysql索引为b+tree，是一种树型结构，叫做平衡多叉查找树，有节点和叶子节点，InnoDB中主键索引是一种聚集索引，节点中只存在健值，叶子节点会存健值，数据和链指针，链指针会把叶子节点双向串联，便于范围查找和排序。
InnoDB中每张表只有一个聚集索引，一般就为主键索引，而我们创建的索引多为辅助索引，辅助索引为非聚集索引，也是b+tree，但是唯一的区别就是叶子节点中只存索引的健值和主键id

下面我们举一个查询的列子进行索引查询的原理解说，
建表和索引

--创建测试表
CREATE TABLE `index_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` int NOT NULL DEFAULT '0' ,
  `name` varchar(300) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_test` (`user_id`)
) ENGINE=InnoDB;

--插入测试数据
insert into index_test(user_id,name) values(1,'刘备');
insert into index_test(user_id,name) values(2,'张飞');
insert into index_test(user_id,name) values(3,'关羽');

--分别进行两次查询
mysql> explain select name from index_test where id =3;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name from index_test where user_id =3;
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | ref  | idx_test      | idx_test | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

上面建了一张表index_test，其中生成了两个索引 ，一个主键id的聚集索引，一个user_id的辅助索引，接下来分别展示两次查询的过程
explain select name from index_test where id =3;
这个查询看执行计划用到了主键索引，在查找数据时
第一步，找到主键索引并从根节点中确定id=3的子节点位置
第二步，利用节点中健值记录找到最终id=3的叶子节点
第三部，在id=3的叶子节点中找到name的数据

explain select name from index_test where user_id =3;
这个查询看执行计划用到了辅助索引，在查找数据时
第一步，找到辅助索引userid，在根节点中确定user_id =3的子节点位置
第二步，利用节点中健值记录找到最终user_id =3的叶子节点
第三部，在user_id =3的叶子节点中找到id的值
第四部，此时将进行经常说到的回表，在进行一次主键索引的过程找到对应id的叶子节点中的数据name

因此在使用辅助索引的过程中，其实还会用到主键索引，因为最终数据是存在主键索引中的。这大概就是主键索引和辅助索引使用时候的过程，但并不是绝对的，只代表大多数情况，毕竟查询优化使用索引，需要根据具体的表和数据相结合进行计算，有时候也可以设计不用回表的辅助索引。

3.索引的创建规则
3.1表的主键、外键必须有索引；
3.2数据量超过1000的表应该有索引；
3.3经常与其他表进行连接的表，在连接字段上应该建立索引；
3.4经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
3.5索引应该建在选择性高的字段上，最好是有连续性的字段；
3.6索引应该建在数据类型小字段上，对于大的文本字段甚至超长字段，不要建索引；
3.7复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
正确选择复合索引中的主列字段，一般是选择性较好的字段；
复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
3.8频繁进行数据操作的表和字段，不要建立太多的索引；
3.9单表创建索引不要过多，最好不要超过6个；
3.10使用字符串创建索引时候最好使用前缀创建索引，且不适合排序操作
3.11创建复合索引时将使用度最高的字段放在最左边
3.12不要对一个字段出现在多个索引中,不要定义冗余或重复的索引
3.13为经常需要排序、分组和联合操作的字段建立索引
3.14尽量减少like，但不是绝对不可用，”xxxx%” 是可以用到索引的
3.15 尽量把>,<用>=和<=替换 ，因为后者是可以走复合索引的

4.适合创建索引场景
4.1 经常出现在where查询中的字段，并且字段的唯一性比较好，字段的有序性好，且如果单字段有索引的话，就不要建立这个字段的组合索引，如果一个查询中多个字段都要创建索引的情况下，联合索引优于单列索引
4.2 在order by 或者 group by 的字段适合建立索引
4.3 多表join时候，作为关联字段的字段
4.4 最左匹配原则，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的顺序可以任意调整。
4.5 = 和 in 可以乱序比如 a = 1 and b = 2 and c =3，建立（a，b，c）索引，a，b，c可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。
5.不适合创建索引场景
5.1 like ’ %xxx ‘不适合使用索引
5.2 not in ， ！=不适合使用索引
5.3 对列进行函数运算的情况（如 where md5(password) = ’xxxx‘）
5.4 数据量大（如text类型字段）、区分度低，不经常被使用的字段
5.5 存了数值的字符串类型字段（如手机号），查询时记得不要丢掉值的引号，否则无法用到该字段相关索引，反之则没关系
5.6 不建议无序的值作为索引