数据库索引的优化

物理存储索引分类

1.聚集索引：决定数据在磁盘上的排序，一个表只能有一个聚集索引

2.非聚集索引：索引上只包含被建立索引的数据，以及一个定位符，这个定位符可以理解为一个聚集索引物理排序的指针，通过这个指针可以找到行数据

逻辑角度可以分为

• 普通索引
• 唯一索引
• 主键索引
• 联合索引（复合索引）
• 全文索引

优化规则

1.前导模糊查询不能使用索引，非前导模糊查询可以使用索引

#前导模糊查询
select * from a where name like '%xx';
#非前导模糊查询
select * from a where name like 'xx%';复制代码

so，搜索严禁使用前导模糊查询或全模糊查询，可用搜索引擎代替

2.union、in、or建议使用in

select * from a where status in (1,2);复制代码

3.负向条件查询不能使用索引，可以优化为in

首先负向条件有：!=、<>、not in、not exists、not like

4.最左原则（联合索引）

在(a,b,c)三个字段上建立联合索引，能够加快 a | (a,b) | (a,b,c) 上的查询速度 

• 建联合索引时，区分度最高的在最左边
• 如果建了(a,b,c)这样的联合索引，就不需要再建立a、(a,b)这样的索引了
• sql 中where条件中如果非等号和等号并存时，在建立索引时将等号条件放在前面

5.范围列可以用到索引（<，<=，>，>=等）

范围列可以用索引，但范围列后的列就无法用到索引，索引最多只能用到一个范围列

6.更新十分频繁、数据区分度不高的字段不宜建立索引

区分度可以用：count(distinct(列名))/count(*)计算，一般区分度80%以上可以建立索引

7.order by 、group by，要注意索引的有序性

select * from doc where a=? and b=? order by c;复制代码

上述建议建立(a,b,c)的联合索引

如果索引中有范围查询，索引就无法排序了

8.如果明确只有一条数据返回，limit 1可以提高效率

select * from where id=? limit 1；复制代码

9.单表索引控制在5个以内，单索引字段数不允许超过5个

10.索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，所以不要随意建立索引

如果表a有6亿数据，表b有3亿数据，通过外键tid关联，如何最快的查询出满足条件的第50000到50200这200条数据：

1.如果tid是自增长且连续：

select * from a,b where a.tid=b.tid and a.tid>50000 limit 200;复制代码

2.如果tid不连续，就需要覆盖索引。tid 要么是主键，要么是辅助索引，b 表 id 也需要有索引

select * from b, (select tid from a limit 50000,200) a where b.id=a.tid;复制代码