一个系列搞懂Mysql数据库5：根据MySQL索引原理进行分析与优化

Table of Contents

• 一：Mysql原理与慢查询
• 一个慢查询引发的思考
• 二：索引建立
• 三：浅析explain用法
  • 有什么用？
  • 怎么使用？
• 参数介绍
  • id
  • select_type
  • extra
  • possible_keys
  • key
  • key_len
  • ref
  • rows
• 四：慢查询优化
  • 建索引的几大原则
  • 回到开始的慢查询
  • 查询优化神器 - explain命令
  • 慢查询优化基本步骤
• 五：最左前缀原理与相关优化
  • 情况一：全列匹配。
  • 情况二：最左前缀匹配。
  • 情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。
  • 情况四：查询条件没有指定索引第一列。
  • 情况五：匹配某列的前缀字符串。
  • 情况六：范围查询。
  • 情况七：查询条件中含有函数或表达式。
• 索引选择性与前缀索引
• 六：InnoDB的主键选择与插入优化

一：Mysql原理与慢查询

MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

本人从13年7月份起，一直在美团核心业务系统部做慢查询的优化工作，共计十余个系统，累计解决和积累了上百个慢查询案例。随着业务的复杂性提升，遇到的问题千奇百怪，五花八门，匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。

一个慢查询引发的思考

select   count(*) from   task where   status=2    and operator_id=20839    and operate_time>1371169729    and operate_time<1371174603    and type=2;

系统使用者反应有一个功能越来越慢，于是工程师找到了上面的SQL。
并且兴致冲冲的找到了我，“这个SQL需要优化，给我把每个字段都加上索引”
我很惊讶，问道“为什么需要每个字段都加上索引？”
“把查询的字段都加上索引会更快”工程师信心满满
“这种情况完全可以建一个联合索引，因为是最左前缀匹配，所以operate_time需要放到最后，而且还需要把其他相关的查询都拿来，需要做一个综合评估。”
“联合索引？最左前缀匹配？综合评估？”工程师不禁陷入了沉思。
多数情况下，我们知道索引能够提高查询效率，但应该如何建立索引？索引的顺序如何？许多人却只知道大概。其实理解这些概念并不难，而且索引的原理远没有想象的那么复杂。

二：索引建立

1. 主键索引

primary key() 要求关键字不能重复，也不能为null,同时增加主键约束 
主键索引定义时，不能命名

2. 唯一索引

unique index() 要求关键字不能重复，同时增加唯一约束

3. 普通索引

index() 对关键字没有要求

4. 全文索引

fulltext key() 关键字的来源不是所有字段的数据，而是字段中提取的特别关键字

关键字：可以是某个字段或多个字段，多个字段称为复合索引

建表：creat table student(    stu_id int unsigned not null auto_increment,    name varchar(32) not null default '',    phone char(11) not null default '',    stu_code varchar(32) not null default '',    stu_desc text,    primary key ('stu_id'),     //主键索引    unique index 'stu_code' ('stu_code'), //唯一索引    index 'name_phone' ('name','phone'),  //普通索引，复合索引    fulltext index 'stu_desc' ('stu_desc'), //全文索引) engine=myisam charset=utf8; 更新：alert table student    add primary key ('stu_id'),     //主键索引    add