MySQL 索引

1. MySQL 索引及优化实战（一）

2. MySQL 索引及优化实战（二）（本文）

MySQL索引优化规则（接上篇文章）

11. 使用短索引（又叫前缀索引）来优化索引

前缀索引，就是用列的前缀取代整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的区分度接近全列索引，同时由于索引key变短而减少了索引文件的大小和维护开销，可以使用 count(distinctleft(列名, 索引长度))/count(*)的来计算前缀索引的区分度。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索引（Covering index，即当索引本身包含查询所需全部数据时，不再访问数据文件本身），许多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

例如对于下面的sql语句：

SELEC * FROM employees.employees WHEREfirst_name='Eric' AND last_name='Anido';

我们可以建立索引：(first_name, last_name(4))

12. 建立索引的列，不允许为null

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集，所以，请使用notnull约束以及默认值。

13. 利用延迟关联或者子查询优化超多分页场景

MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset 行，返回N行，那当offset特别大的时候，效率就超级的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

示例如下，先快速定位需要获取的 id 段，然后再关联:

select a.* from 表1 a, (select id from 表1 where 条件 limit 100000,20 ) b wherea.id=b.id

14. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的，另外，即使在应用层做了超级完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

15. 超过三个表最好不要join

需要join的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。

16. 如果明确知道只有一条结果返回，limit 1能够提高效率

列如如下sql：

select *from user where login_name=?

可以优化为：

select *from user where login_name=? limit 1

自己明确知道只有一条结果，但数据库并不知道，明确告知它，让它主动停止游标移动

17. SQL性能优化explain中的type：至少要达到range 级别，要求是 ref 级别，如果可以是consts 最好

A、consts：单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据

B、ref：使用普通的索引(normal index)

C、range：对索引进行范围检索

D、当type=index时，索引物理文件全扫，速度超级慢

18. 单表索引提议控制在5个以内

19. 单索引字段数不允许超过5个

字段超过5个时，实际已经起不到有效过滤数据的作用了

20. 创建索引时避免以下错误观念

A、索引越多越好，认为一个查询就需要建一个索引

B、宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度

C、抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决

D、过早优化，在不了解系统的情况下就开始优化

问题详解

1. 请问如下三条sql该如何建立索引？

where a=1 and b=1

where b=1

where b=1 order by time desc

MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引吗？

答：

A、提议建立两个索引：idx_a_b(a,b) 和 idx_b_time(b,time)

B、MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，对于上面的第一条sql，如果建立索引为idx_b_a(b,a)也是可以用到索引的，不过提议where后的字段顺序和联合索引保持一致，养成好习惯。

2、如果有联合索引(emp_no、title、from_date)，下面的sql是否可以用到索引，如果可以的话，会使用几个列？

select * from employees.titles whereemp_no between '10001' and '10010' and title='Senior Engineer' and from_datebetween '1986-01-01' and '1986-12-31'

答：可以使用索引，可以用到索引全部三个列，这个sql看起来是用了两个范围查询，但作用于emp_no上的“between”实际上相当于“in”，也就是说emp_no实际是多值准确匹配，在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

3. 既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？

答：不是，由于索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不提议建索引。第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，另一种是数据的区分度比较低，可以使用count(distinct(列名))/count(*)来计算区分度。

4. 主键和聚集索引的关系？

答：在MySQL中，InnoDB引擎表是(聚集)索引组织表(clustered index organize table)，它会先按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来取代，如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。由此可见，在InnoDB表中，主键必然是聚集索引，而聚集索引则未必是主键。MyISAM引擎表是堆组织表(heap organize table)，它没有聚集索引的概念。

5. 一个6亿的表a，一个3亿的表b，通过外键tid关联，如何最快的查询出满足条件的第50000到第50200中的这200条数据记录？

A、如果a表tid是自增长,并且是连续的,b表的id为索引

select * from a,b where a.tid =b.id and a.tid>500000 limit 200;

B、如果a表的tid不是连续的,那么就需要使用覆盖索引，tid要么是主键,要么是辅助索引,b表id也需要有索引。

select * from b, (select tidfrom a limit 50000,200) a where b.id = a.tid;

6. 如果建立联合索引(a,b,c)，下列语句是否可以使用索引，如果可以，使用了那几列？（考察联合索引最左前缀原则）

联合索引

7. 文章表的表结构如下：

CREATE TABLE IF NOT EXISTS`article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `views` int(10) unsigned NOT NULL,
  `comments` int(10) unsigned NOT NULL,
  `title` varbinary(255) NOT NULL,
  `content` text NOT NULL,PRIMARY KEY (`id`)
);

下面语句应该如何建立索引？

select author_id, title, content from`article`where category_id = 1 and comments > 1order by views desc limit 1;   

答：

没有联合索引时，explain显示：

没有联合索引

创建
idx_category_id_comments_views(`category_id`,`comments`,`views`)联合索引时，explain显示：

联合索引1

创建idx_category_id_views(`category_id`,`views`)联合索引，explain显示：

联合索引2

由此可见，可以创建idx_category_id_views(`category_id`,`views` )联合索引