MYSQL 索引设计原则

目录

一、适合建立索引的情况

二、不适合建立索引的情况

三、索引失效的情况

---

一、适合建立索引的情况

1.字段具有唯一性（非unique)，如学生学号。

2.频繁作为where条件的字段，尤其是数据量大的情况下。

3.经常group by和order by的列。因为group by是将相同的值放在一起，order by是排序，而索引树（B+树）已经是排好序的。如何group by 与order by混合使用，可以考虑使用联合索引。

4.update、delete中where字段。如果更新的字段为非索引字段，这种情况下更新速度更快。

5.DISTINCT 字段需要创建索引。因为建立索引后，相同的值一定挨在一起。

6.表连接时，被驱动表的连接字段可以建立索引。注意表的连接时，最好不超过3张表，且连接时字段类型应一致，不然将导致索引失效。

7.使用列类型小的创建索引，索引类型越小，查询越快。因为数据类型越小，一个页中存放的记录就越多，磁盘IO次数越少。对主键来说更有效。

8.使用字符串前缀创建索引，字符串越长，索引占用空间越多；字符串越长，做字符串比较时，比较时间更长。

        计算字符串前缀的区分度： select count(distinct left(col_name,prefix_len)) / count(*) from table;

        当区分度越接近1 越好。如果字符串区分度没选好，可能导致当前列排序失效。

9.使用区分度高的列做为索引。列的基数是指列中不重复元素的个数。

select count(distinct col_name) / count(*) from table_name;计算一个列的区分度。超过33%就适合建立索引。

10.在联合索引中，区分度高的的列应该放在联合索引的最左侧。

11.在多个字段都要建立索引时，组合索引要优于单列索引。

select * from student group by student_id order by course_id;

select * from student where student_id=xx;

这是在student_id和course_id建立联合索引，上面两条查询都可以使用。

二、不适合建立索引的情况

1.where 中使用不到的索引列，包括 group_by、order_by、distinct。

2.数据量小的表。（避免回表）

3.区分度很小的列。

4.更新频繁的表少建索引。

5.不建议使用无序的值作为主键。

6.删除使用较少的索引。

7.不要定义冗余的索引。如index(a,b,c)、index(a) 、index(b) 

8.不要重复索引，unique(col) 、index(col0)。unique关键字自动在字段上建立索引。

三、索引失效的情况

1.最佳左前缀规则：对于组合索引，过滤条件要使用索引必须按索引建立时的顺序，依次满足，一旦跳过某个字段，索引后的字段都无法使用，如果查询条件中没有使用到索引的第一个字段，则组合索引直接失效。

2.主键插入顺序：每次插入记录时，主键最好递增（防止页分裂,分裂固然有性能损耗）。AUTO_INCREMENT。

3.计算、函数、类型转换都会导致索引失效。

例：index(name)、index(age)

where name like 'abc%';  走索引idx_name

where LEFT(name,3)='abc'; 索引idx_name失效

where name=123;  索引idx_name失效

where age+1 =21; 索引idx_age失效

4.范围条件右边的列索引失效（组合索引）

例：create index idx_age_classid_name ON student(age,classid,name);

select * from student where age=20 AND classid>10  AND name='123';

只能使用到联合索引idx_age_classid_name 的age和classid字段。

如果要使用到索引的全部列，只能重建索引：create index idx_age_classid_name ON student(age,name,classid);

5.!= 与<> 会导致索引列失效

where name !='abc'

where name <> 'abc';

where name not like 'abc%';

6.is null 走索引但is not null 不走索引

结论：在建表时，应该将字段设置为NOT NULL,并设定默认值。INT 默认值为 0， 字符串默认值为 ‘’。

7.LIKE 以%开头的通配符时

开发中页面搜索严禁左模糊或全模糊，如果需要请使用专门的搜索引擎。

8.OR前后 存在非所以列，索引失效。OR在前后列都有单独索引时，会使用index_merge。

练习：idx(a,b,c)

WHERE 语句	索引是否被使用
where a=3	Y,使用a
where a=3 AND b=4	Y,使用a,b
where a=3 AND b=4 AND c=5	Y,使用a,b,c
where b=3或where b=3 AND c=4 或where c=4	N
where a=3 AND c=4	Y使用a
where a=3 AND b>4 AND v=5	Y,使用a,b
where a is null AND b is nou null	Y,使用a
where a != 3	N
where abs(a) = 3	N
where a=3 AND b like'xx%' AND c =4	Y,使用a,b,c
where a=3 AND b like '%xx' AND c = 5	Y,使用a
where a =3 AND b like '%xx%' AND c = 5	Y,使用a
where a=3 AND b like 'x%KK%' AND c=5	Y,使用a,b,c