MySQL 索引及优化实战

索引概念和作用

索引是一种使记录有序化的技术，它可以指定按某列/某几列预先排序，从而大大提高查询速度（类似于汉语词典中按照拼音或者笔画查找）。

索引的主要作用是加快数据查找速度，提高数据库的性能。

MySQL 索引类型

从物理存储角度上，索引可以分为聚集索引和非聚集索引。

1. 聚集索引（Clustered Index）

聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引。

2. 非聚集索引（Non-clustered Index）

非聚集索引并不决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据，以及一个行定位符 row-locator，这个行定位符，可以理解为一个聚集索引物理排序的指针，通过这个指针，可以找到行数据。

从逻辑角度，索引可以分为以下几种。

1. 普通索引：最基本的索引，它没有任何限制。

2. 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

3. 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用     primary key 来约束。主键和聚集索引的关系详见“问题详解”中的第4题。

4. 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。

5. 全文索引：老版本     MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

MySQL索引优化规则

可以通过以下规则对 MySQL 索引进行优化。

1.前导模糊查询不能使用索引。

例如下面 SQL 语句不能使用索引。

select * fromdoc where title like '%XX'

而非前导模糊查询则可以使用索引，如下面的 SQL 语句。

select * fromdoc where title like 'XX%'

页面搜索严禁左模糊或者全模糊，如果需要可以用搜索引擎来解决。

2.union、in、or 都能够命中索引，建议使用 in。

• union：能够命中索引。

示例代码如下：

select * fromdoc where status=1

unionall

select * fromdoc where status=2

直接告诉 MySQL 怎么做，MySQL 耗费的 CPU 最少，但是一般不这么写 SQL。

• in：能够命中索引。

示例代码如下：

select * fromdoc where status in (1, 2)

查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in

• or：新版的 MySQL 能够命中索引。

示例代码如下：

select * fromdoc where status = 1 or status = 2

查询优化耗费的 CPU 比 in 多，不建议频繁用 or。

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

负向条件有：!=、<>、not in、not exists、not like 等。

例如下面代码：

select * fromdoc where status != 1 and status != 2

可以优化为 in 查询：

select * fromdoc where status in (0,3,4)

4.联合索引最左前缀原则（又叫最左侧查询）

• 如果在(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

例如登录业务需求，代码如下。

selectuid, login_time from user where login_name=? andpasswd=?

可以建立(login_name, passwd)的联合索引。

因为业务上几乎没有 passwd 的单条件查询需求，而有很多 login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。

• 建联合索引的时候，区分度最高的字段在最左边。

• 如果建立了(a,b)联合索引，就不必再单独建立 a 索引。同理，如果建立了(a,b,c)联合索引，就不必再单独建立 a、(a,b) 索引。

• 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如     where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

• 最左侧查询需求，并不是指 SQL 语句的 where 顺序要和联合索引一致。

下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。

selectuid, login_time from user where passwd=? andlogin_name=?

但还是建议 where 后的顺序和联合索引一致，养成好习惯。

5.范围列可以用到索引（联合索引必须是最左前缀）。

• 范围条件有：<、<=、>、>=、between等。

• 范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而 title 和 from_date 则使用不到索引。

select * fromemployees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

6.把计算放到业务层而不是数据库层。

• 在字段上进行计算不能命中索引。

例如下面的 SQL 语句。

select * fromdoc where YEAR(create_time) <= '2016'

即使 date 上建立了索引，也会全表扫描，可优化为值计算，如下：

select * fromdoc where create_time <= '2016-01-01'

• 把计算放到业务层。

这样做不仅可以节省数据库的 CPU，还可以起到查询缓存优化效果。

比如下面的 SQL 语句：

select * fromorder where date < = CURDATE()

可以优化为：

select * fromorder where date < = '2018-01-2412:00:00'

优化后的 SQL 释放了数据库的 CPU 多次调用，传入的 SQL 相同，才可以利用查询缓存。

7.强制类型转换会全表扫描

如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。

 select * fromuser where phone=13800001234

可以优化为：

select * fromuser where phone='13800001234'

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

• 更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。

• “性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

9.利用覆盖索引来进行查询操作，避免回表。

被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

例如登录业务需求，代码如下。

selectuid, login_time from user where login_name=? andpasswd=?

可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。

10.如果有 order by、group by 的场景，请注意利用索引的有序性。

• order     by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现     file_sort 的情况，影响查询性能。

• 例如对于语句 where a=? and b=? order by     c，可以建立联合索引(a,b,c)。

• 如果索引中有范围查找，那么索引有序性无法利用，如 WHERE     a>10 ORDER BY b;，索引(a,b)无法排序。

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

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

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

例如对于下面的 SQL 语句：

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

我们可以建立索引：(firstname, lastname(4))。

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

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

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

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

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

selecta.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id

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

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

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

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

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

比如如下 SQL 语句：

select * fromuser where login_name=?

可以优化为：

select * fromuser where login_name=? limit 1

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

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

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

• ref：使用普通的索引（Normal Index）。

• range：对索引进行范围检索。

• 当 type=index 时，索引物理文件全扫，速度非常慢。

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

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

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

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

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

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

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

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

问题详解

这部分，我将列出平时会遇到的一些问题，并给予解答。

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

where a=1and b=1

where b=1

where b=1order by time desc

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

回答：             

 

第一问：建议建立两个索引，即 idxab(a,b) 和 idxbtime(b,time)。

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

2.假如有联合索引(empno、title、fromdate)，下面的 SQL 是否可以用到索引，如果可以的话，会使用几个列？

select * fromemployees.titles where emp_no between '10001' and'10010' and title='Senior Engineer' and from_date between '1986-01-01'and '1986-12-31'

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

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

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

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

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

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

回答：方法一：如果 a 表 tid 是自增长，并且是连续的，b表的id为索引。SQL语句如下。

select * froma,b where a.tid = b.id and a.tid>500000 limit200;

方法二：如果 a 表的 tid 不是连续的，那么就需要使用覆盖索引，tid 要么是主键，要么是辅助索引，b 表 id 也需要有索引。SQL语句如下。

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

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

where a= 3

答：是，使用了 a 列。

where a= 3 and b = 5

答：是，使用了 a，b 列。

where a = 3 and c = 4 and b = 5

答：是，使用了 a，b，c 列。

where b= 3

答：否。

where a= 3 and c = 4

答：是，使用了 a 列。

where a = 3 and b > 10 andc = 7

答：是，使用了 a，b 列。

where a = 3 and b like 'xx%' andc = 7

答：是，使用了 a，b 列。

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

CREATE TABLEIF NOT EXISTS `article` (`id`int(10) unsigned NOT NULLAUTO_INCREMENT,

`author_id`int(10) unsignedNOT NULL,

`category_id`int(10) unsigned NOT NULL,

`views`int(10) unsignedNOT NULL,

`comments`int(10) unsignedNOT NULL,

`title`varbinary(255) NOT NULL,

`content`text NOTNULL,

PRIMARY KEY (`id`)

);

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

selectauthor_id, title, content from `article`

wherecategory_id = 1 and comments > 1

order byviews desc limit 1;

回答：

没有联合索引时，explain显示，如下图所示：

创建 idxcategoryidcommentsviews(category_id,comments, views) 联合索引时，explain显示，如下图所示：

创建 idxcategoryidviews(categoryid,views) 联合索引，explain 显示，如下图所示：

由此可见，可以创建 idxcategoryidviews(categoryid,views) 联合索引。

作为免费又高效的数据库，mysql基本是首选。良好的安全连接，自带查询解析、sql语句优化，使用读写锁（细化到行）、事物隔离和多版本并发控制提高并发，完备的事务日志记录，强大的存储引擎提供高效查询（表记录可达百万级），如果是InnoDB，还可在崩溃后进行完整的恢复，优点非常多。即使有这么多优点，仍依赖人去做点优化，看书后写个总结巩固下，有错请指正。

　　完整的mysql优化需要很深的功底，大公司甚至有专门写mysql内核的，sql优化攻城狮，mysql服务器的优化，各种参数常量设定，查询语句优化，主从复制，软硬件升级，容灾备份，sql编程，需要的不是一星半点的知识与时间来掌握，作为一名像俺这样的菜鸟开发，强吃这么多消化不了也没意义：没地儿用啊，况且还有运维和dba，还不如把手头的业务写好，也就是写好点的sql，而且很多sql语句优化跟索引还是有很大关系的。

　　首先，mysql的查询流程大致是：mysql客户端通过协议与mysql服务器建立连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，有一系列预处理，比如检查语句是否写正确了，然后是查询优化（比如是否使用索引扫描，如果是一个不可能的条件，则提前终止），生成查询计划，然后查询引擎启动，开始执行查询，从底层存储引擎调用API获取数据，最后返回给客户端。怎么存数据、怎么取数据，都与存储引擎有关。然后，mysql默认使用的BTREE索引，并且一个大方向是，无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

　　mysql通过存储引擎取数据，自然跟存储引擎有很大关系，不同的存储引擎索引也不一样，如MyISAM的全文索引，即便索引叫一个名字内部组织方式也不尽相同，最常用的当然就是InnoDB了（还有完全兼容mysql的MariaDB，它的默引擎是XtraDB，跟InnoDB很像），这里写的是InnoDB引擎。而索引的实现也跟存储引擎，按照实现方式分，InnoDB的索引目前只有两种：BTREE索引和HASH索引。通常我们说的索引不出意外指的就是B树索引，InnoDB的BTREE索引，实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引。至于B树与B+树的区别，原谅的俺数据结构没好好学，也是需要补的地方。

　　使用了BTREE索引，意味着所有的索引是按顺序排列存储的（升序），mysql就是这么干的，mysl中的BTREE索引抽象结构如下图（参考高性能mysql）。

　　结构中，每一层节点均从左往右从小到大排列，key1 < key2 < ... < keyN，对于小于key1或在[key1，key2)或其他的值的节点，在进入叶子节点查找，是一个范围分布，同时，同一层节点之间可直接访问，因为他们之间有指针指向联系（MyISAM的BTREE索引没有）。每次搜索是一个区间搜索，有的话就找到了，没有的话就是空。索引能加快访问速度，因为有了它无需全表扫描数据（不总是这样），根据查找的值，跟节点中的值比较，通常使用二分查找，对于排好序的数值来说，平均速度几乎是最快的。

　　val指向了哪里，对于InnoDB，它指向的就是表数据，因为InnoDB的表数据本身就是索引文件，这是与MyISAM索引的显著区别，MyISAM的索引指向的是表数据的地址（val指向的是类似于0x7DFF..之类）。比如对于InnoDB一个主键索引来说，可能是这样

　　　　　　　　

　　InnoDB的索引节点val值直接指向表数据，即它的叶子节点就是表数据，它们连在一起，表记录行没有再单独放在其他地方，叶子节点（数据）之间可访问。

　　前面在BTREE的抽象结构中，索引值的节点是放在页中的，这里有两个需注意的问题：

　　1. 叶子页、页中的值（上上图），即所谓的页是啥，俺加了个节点注释，即这里的页最小可近似当做是单个节点。我们知道计算机的存储空间是一块一块的，通常一块用完了再用另一块，如果上一块只剩余5kb，但这里刚好要申请8kb的空间，就得在一个新的块上申请这个空间，然后以后的申请又接在这个8kb后面，只要这个块的空间足够，那么上一块的5kb通常就成了所谓的“碎片”，电脑用多了会有很多这样零散的碎片空间，因此有碎片整理。在mysql中，这里的页可理解为块存储空间，即索引的树节点是存放在页中的，每一页（称为逻辑页）有固定大小，InnoDB目前是16kb，一页用完了，当继续插入表生成新的索引节点时，就去新的页中存储这个节点，再有新的节点就继续放在这个新的页的节点后面。

　　2. 页分裂问题，一页总要被存满，然后新开一页继续，这种行为被称作页分裂。何时开辟新的页，mysql规定了一个分裂因子，达到页存储空间的15/16则存到下一页。页分裂的存在可能极大影响性能维护索引的性能。通常提倡的是，设定一个无意义的整数自增索引，有利于索引存储

　　　　

　　如果非自增或不是整数索引，如非自增整数、类似MD5的字符串，以他们作为索引值时，因为待插入的下一条数据的值不一定比上一条大，甚至比当前页所有值都小，需要跑到前几页去比较而找到合适位置，InnoDB无法简单的把新行插入到上一行后面，而找到并插入索引后，可能导致该页达到分裂因子阀值，需要页分裂，进一步导致后面所有的索引页的分裂和排序，数据量小也许没什么问题，数据量大的话可能会浪费大量时间，产生许多碎片。

　　　　

　　主键总是唯一且非空，InnoDB自动对它建立了索引（primary key），对于非主键字段上建立的索引，又称辅助索引，索引排列也是顺序排列，只是它还附带一个本条记录的主键值的数据域，不是指向本数据行的指针，在使用辅助索引查找时，先找到对应这一列的索引值，再根据索引节点上的另一个数据域---主键值，来查找该行记录，即每次查找实际经过查找了两次。额外的数据域存储主键值的好处是，当页分裂发生时，无需修改数据域的值，因为即使页分裂，该行的主键值是不变的，而地址就变了。比如name字段的索引简示如下　

　　　　　　

　　 包含一列的索引称为单列索引，多列的称为复合索引，因为BTREE索引是顺序排列的，所以比较适合范围查询，但是在复合索引中，还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。

　　比如有这样一张表

1 2 3 4 5 6 7

create  table  staffs(      id int  primary  key  auto_increment,      name  varchar (24) not  null  default  ''  comment '姓名' ,      age int  not  null  default  0 comment '年龄' ,      pos varchar (20) not  null  default  ''  comment '职位' ,      add_time timestamp  not  null  default  current_timestamp  comment '入职时间'    ) charset utf8 comment '员工记录表' ;

　　添加三列的复合索引

1	alter  table  staffs add  index  idx_nap( name , age, pos);

　　在BTREE索引的使用上，以下几种情况可以用到该索引或索引的一部分（使用explain简单查看使用情况）：

　　1. 全值匹配

　　如select * from staffs where name = 'July' and age = '23' and pos = 'dev' ，key字段显示使用了idx_nap索引

　　2. 匹配最左列，对于复合索引来说，不总是匹配所有字段列，但是可以匹配索引中靠左的列

　　如select * from staffs where name = 'July' and age = '23'，key字段显示用到了索引，注意，key_len字段（表示本次语句使用的索引长度）数值比上一条小了，意思是它并未使用全部索引列（通常这个长度可估摸着用了哪些索引列，埋个坑），事实上只用到了name和age列

　　再试试select * from staffs where name = 'July'，它也用了索引，key_len值更小，实际只用到了索引中的name列

　　3. 匹配列前缀，即一个索引中列的前一部分，主要用在模糊匹配，如select * fromstaffs where name like 'J%'，explain信息的key字段表示使用了索引，但是mysql的B树索引不能非列前缀的模糊匹配，如select * from staffs where name like '%y' 或者 like '%u%'，据说是由于底层存储引擎的API限制

　　4. 匹配范围，如select * from staffs where name > 'Mary'，但俺在测试时发现>可以，>=却不行，至少在字符串列上不行（测试mysql版本5.5.12），然而在时间类型（timestamp）上却可以，不测试下还真不能确定说就用到了索引==

　　出于好奇测了下整型字段的索引（idx_cn(count, name)，count为整型），发现整型受限制少很多，下面的都能用到索引，连前模糊匹配的都行

1 2 3 4 5 6

select  * from  indexTest1 where  count  > '10'    select  * from  indexTest1 where  count  >= '10'    select  * from  indexTest1 where  count  > '10%'    select  * from  indexTest1 where  count  >= '10%'    select  * from  indexTest1 where  count  > '%10%'    select  * from  indexTest1 where  count  >= '%10%'

　　5. 精确匹配一列并范围匹配右侧相邻列，即前一列是固定值，后一列是范围值，它用了name与age两个列的索引（key_len推测）

　　如select * from staffs where name = 'July' and age > 25

　　6. 只访问索引的查询，比如staffs表的情况，索引建立在(name,age,pos)上面，前面一直是读取的全部列，如果我们用到了哪些列的索引，查询时也只查这些列的数据，就是只访问索引的查询，如

1 2	select  name ,age,pos from  staffs where  name  = 'July'  and  age = 25 and  pos = 'dev'    select  name ,age from  staffs where  name  = July and  age > 25

　　第一句用到了全部索引列，第二句只用了索引前两列，select的字段就最多只能是这两列，这种查询情况的索引，mysql称为覆盖索引，就是索引包含（覆盖）了查询的全部字段。是不是用到了索引查询，在explain中需要看最后一个Extra列的信息，Using index表明使用了覆盖索引，同时Using where表明也使用了where过滤

　　7. 前缀索引

　　区别于列前缀（类似like 'J%'形式的模糊匹配）和最左列索引（顺序取索引中靠左的列的查询），它只取某列的一部分作为索引。通常在说InnoDB跟MyISAM的区别时，一个明显的区别是：MyISAM支持全文索引，而InnoDB不行，甚至对于text、blob这种超长的字符串或二进制数据时，MyISAM会取前多少个字符作为索引，InnoDb的前缀索引跟这个类似，某些列，一般是字符串类型，很长，全部作为索引大大增加存储空间，索引也需要维护，对于长字符串，又想作为索引列，一个可取的办法就是取前一部分（前缀），代表一整列作为索引串，问题是：如何确保这个前缀能代表或大致代表这一列？所以mysql中有个概念是索引的选择性，是指索引中不重复的值的数目（也称基数）与整个表该列记录总数（#T）的比值，比如一个列表（1,2,2,3），总数是4，不重复值数目为3，选择性为3/4，因此选择性范围是[1/#T, 1]，这个值越大，表示列中不重复值越多，越适合作为前缀索引，唯一索引（UNIQUE KEY）的选择性是1。

　　比如有一列a varchar(255)，以它作前缀索引，比如以7个测试，逐个增加看看选择性值增长到那个数基本不变，就表示可以代表整列了，再结合这个长度的索引列是否存储数据太多，做个权衡，基本就行了。但如果这个选择性本来就小的可怜还是算了

1	select  count ( distinct  left (a, 7))/ count (*) as  non_repeat from  tab;

　　定好一个前缀数目，如9，添加索引时可以这样

1 2	alter  table  tab add  index  idx_pn( name (9)) --单独前缀索引    alter  table  tab add  index  idx_cpn( count , name (9)) --复合前缀索引

　　以上为常见的使用索引的方式，有这么些情况不能用或不能全用，有的就是上面情况的反例，以key(a, b, c)为例

　　1. 跳过列，where a = 1 and c = 3，最多用到索引列a；where b = 2 and c = 3，一个也用不到，必须从最左列开始

　　2. 前面是范围查询，where a = 1 and b > 2 and c = 3，最多用到 a, b两个索引列；

　　3. 顺序颠倒，where c = 3 and b = 2 and a = 1，一个也用不到；

　　4. 索引列上使用了表达式，如where substr(a, 1, 3) = 'hhh'，where a = a + 1，表达式是一大忌讳，再简单mysql也不认。有时数据量不是大到严重影响速度时，一般可以先查出来，比如先查所有有订单记录的数据，再在程序中去筛选以'cp1001'开头的订单，而不是写sql过滤它；

　　5. 模糊匹配时，尽量写 where a like 'J%'，字符串放在左边，这样才可能用得到a列索引，甚至可能还用不到，当然这得看数据类型，最好测试一下。

　　排序对索引的影响

　　order by是经常用的语句，排序也遵循最左前缀列的原则，比如key(a, b)，下面语句可以用到（测试为妙）

1 2 3

select  * from  tab where  a > 1 order  by  b    select  * from  tab where  a > 1 and  b > '2015-12-01 00:00：00'  order  by  b    select  * from  tab order  by  a, b

　　以下情况用不到

　　1. 非最左列，select * from tab order by b;

　　2. 不按索引列顺序来的，select * from tab where b > '2015-12-01 00:00:00' order by a;

　　3. 多列排序，但列的顺序方向不一致，select * from tab a asc, b desc。

　　聚簇索引与覆盖索引

　　前面说到，mysql索引从结构上只有两类，BTREE与HASH，覆盖索引只是在查询时，要查询的列刚好与使用的索引列完全一致，mysql直接扫描索引，然后就可返回数据，大大提高效率，因为不需再去原表查询、过滤，这种形式下的索引称作覆盖索引，比如key(a,b)，查询时select a,b from tab where a = 1 and b > 2，本质原因：BTREE索引存储了原表数据。

　　聚簇索引也不是单独的索引，前面简要写到，BTREE索引会把数据放在索引中，即索引的叶子页中，包括主键，主键是跟表数据紧挨着放在一起的，因为表数据只有一份，一列键值要跟每一行数据都紧挨在一起，所以一张表只有一个聚簇索引，对于mysql来说，就是主键列，它是默认的。

　　聚簇索引将表数据组织到了一起（参考前面主键索引简略图），插入时严重依赖主键顺序，最好是连续自增，否则面临频繁页分裂问题，移动许多数据。

　　哈希索引

　　简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，也是对索引列计算一个散列值（类似md5、sha1、crc32），然后对这个散列值以顺序（默认升序）排列，同时记录该散列值对应数据表中某行的指针，当然这只是简略模拟图

　　　　　　

　　比如对姓名列建立hash索引，生成hash值按顺序排列，但是顺序排列的hash值并不对应表中记录，从地址指针可反应出来，而且，hash索引可能建立在两列或者更多列上，取得是多列数据后的hash值，它不存储表中数据。它先计算列数据的hash值，与索引中的hash值比较，找到了然后比对列数据是否相等，可能涉及其他列条件，然后返回数据。hash当然会有冲突，即碰撞，除非有很多冲突，一般hash索引效率很高，否则hash维护成本较高，因此哈希索引通常用在选择性较高的列上面。哈希索引的结构决定了它的特点：

　　1. hash索引只是hash值顺序排列，跟表数据没有关系，无法应用于order by；

　　2. hash索引是对它的所有列计算哈希值，因此在查询时，必须带上所有列，比如有(a, b)哈希索引，查询时必须 where a = 1 and b = 2，少任何一个不行；

　　3. hash索引只能用于比较查询 = 或 IN，其他范围查询无效，本质还是因不存储表数据；

　　4. 一旦出现碰撞，hash索引必须遍历所有的hash值，将地址所指向数据一一比较，直到找到所有符合条件的行。

　　填坑

　　前面提到通过explain的key_len字段，可大致估计出用了哪些列，索引列的长度跟索引列的数据类型直接相关，一般，我们说int是4字节，bigint8字节，char是1字节，考虑到建表时要指定字符集，比如utf8，还跟选的字符集有关（==!），在utf8下边，一个char是3字节，但是知道这些仍不能说key_len就是将用到的索引列的数据类型代表字节数一加不就完啦？事实总有点区别，测试方法比较机械（以下基于mysql 5.5.2）

　　建表，加索引，int型

1 2 3 4 5 6 7 8

--测试表    create  table  keyLenTest1(      id int  primary  key  auto_increment,      typeKey int  default  0 ,      add_time timestamp  not  null  default  current_timestamp    ) charset utf8    --添加索引    alter  table  keyLenTest1 add  index  idx_k(typeKey);

　　可知int型索引默认长度为5，在4字节基础上+1

　　char型

1 2	--改为 char 型，1个字符   alter table keyLenTest1 modify typeKey char (1);

1 2	--改为char型，2个字符    alter  table  keyLenTest1 modify  typeKey char (2);

　　可知，char型初始是4字节（3+1 bytes），后续按照3字节递增

　　varchar型

1 2	--改为varchar型，1个字符    alter  table  keyLenTest1 modify  typeKey varchar (1);

1 2	--改为varchar型，2个字符    alter  table  keyLenTest1 modify  typeKey varchar (2);

　　可知，varchar型，1个字符时，key_len为6，以后以3字节递增

　　所以，如果一个语句用到了int、char、varchar，key_len如何计算以及用了哪些索引列应该很清楚了。

　　如果想了解的更详细点，explain各字段意义，索引的更多细节，除了explain，还有show profiles、慢查询日志等（没细看），推荐看高性能mysql

MySQL性能优化的21个最佳实践 和 mysql使用索引

1. 为查询缓存优化你的查询

大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。

这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。请看下面的示例：

 

上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而 开启缓存。

　　2. EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。

挑一个你的SELECT语句(推荐挑选那个最复杂的，有多表联接的)，把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后，你会看到一张表格。下面的这个示例中，我们忘记加上了group_id索引，并且有表联接：

 

当我们为 group_id 字段加上索引后：

 

我们可以看到，前一个结果显示搜索了 7883 行，而后一个只是搜索了两个表的 9 和 16 行。查看rows列可以让我们找到潜在的性能问题。

　　3. 当只要一行数据时使用 LIMIT 1

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。

在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

下面的示例，只是为了找一下是否有“中国”的用户，很明显，后面的会比前面的更有效率。(请注意，第一条中是Select *，第二条是Select 1)

 

　　4. 为搜索字段建索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。

 

从上图你可以看到那个搜索字串 “last_name LIKE ‘a%’”，一个是建了索引，一个是没有索引，性能差了4倍左右。

另外，你应该也需要知道什么样的搜索是不能使用正常的索引的。例如，当你需要在一篇大的文章中搜索一个词时，如： “WHERE post_content LIKE ‘%apple%’”，索引可能是没有意义的。你可能需要使用MySQL全文索引 或是自己做一个索引(比如说：搜索关键词或是Tag什么的)

　　5. 在Join表的时候使用相当类型的例，并将其索引

如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。(两个表的字符集有可能不一样)

 

　　6. 千万不要 ORDER BY RAND()

想打乱返回的数据行?随机挑一个数据?真不知道谁发明了这种用法，但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。

如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得 不去执行RAND()函数(很耗CPU时间)，而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事(因为要排序)

下面的示例是随机挑一条记录

 

　　7. 避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

所以，你应该养成一个需要什么就取什么的好的习惯。

 

　　8. 永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT标志。

就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。

而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……

在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比 如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课 程ID叫“外键”其共同组成主键。

　　9. 使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

MySQL也有一个“建议”(见第十条)告诉你怎么去重新组织你的表结构。当你有一个 VARCHAR 字段时，这个建议会告诉你把其改成 ENUM 类型。使用 PROCEDURE ANALYSE() 你可以得到相关的建议。

　　10. 从 PROCEDURE ANALYSE() 取得建议

PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

例如，如果你创建了一个 INT 字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段，因为数据不多，你可能会得到一个让你把它改成 ENUM 的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。

在phpmyadmin里，你可以在查看表时，点击 “Propose table structure” 来查看这些建议

 

一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确。一定要记住，你才是最终做决定的人。

　　11. 尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。

首先，问问你自己“Empty”和“NULL”有多大的区别(如果是INT，那就是0和NULL)?如果你觉得它们之间没有什么区别，那么你就不要使用NULL。(你知道吗?在 Oracle 里，NULL 和 Empty 的字符串是一样的!)

不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

 

　　12. Prepared Statements

Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。

Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击。当然，你也可以手动地检查你的这些变量，然而，手动的检查容易出问题， 而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候，这样的问题会好一些。

在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。

虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势，所以这会使得网络传输非常有效率。

当然，也有一些情况下，我们需要避免使用Prepared Statements，因为其不支持查询缓存。但据说版本5.1后支持了。

在PHP中要使用prepared statements，你可以查看其使用手册：mysqli 扩展 或是使用数据库抽象层，如： PDO.

 

　　13. 无缓冲的查询

正常的情况下，当你在当你在你的脚本中执行一个SQL语句的时候，你的程序会停在那里直到没这个SQL语句返回，然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。

 

mysql_unbuffered_query() 发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大 量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。

然而，这会有一些限制。因为你要么把所有行都读走，或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且， mysql_num_rows() 或 mysql_data_seek() 将无法使用。所以，是否使用无缓冲的查询你需要仔细考虑。

　　14. 把IP地址存成 UNSIGNED INT

很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当 你需要使用这样的WHERE条件：IP between ip1 and ip2。

我们必需要使用UNSIGNED INT，因为 IP地址会使用整个32位的无符号整形。

而你的查询，你可以使用 INET_ATON() 来把一个字符串IP转成一个整形，并使用 INET_NTOA() 把一个整形转成一个字符串IP。在PHP中，也有这样的函数 ip2long() 和 long2ip()。

 

　　15. 固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

使用“垂直分割”技术(见下一条)，你可以分割你的表成为两个一个是定长的，一个则是不定长的。

　　16. 垂直分割

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。(以前，在银行做过项目，见过一张表有100多个字段，很恐怖)

示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改 写这个字段。那么，为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有 好的性能。

示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户 ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。

另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。

　　17. 拆分大的 DELETE 或 INSERT 语句

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。

Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。

如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你泊WEB服务Crash，还可能会让你的整台服务器马上掛了。

所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。下面是一个示例：

 

　　18. 越小的列会越快

对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。

参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。

如果一个表只会有几列罢了(比如说字典表，配置表)，那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。

当然，你也需要留够足够的扩展空间，不然，你日后来干这个事，你会死的很难看，参看Slashdot的例子(2009年11月06日)，一个简单的ALTER TABLE语句花了3个多小时，因为里面有一千六百万条数据。

　　19. 选择正确的存储引擎

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都 无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

下面是MySQL的手册

target=”_blank”MyISAM Storage Engine

InnoDB Storage Engine

　　20. 使用一个对象关系映射器(Object Relational Mapper)

使用 ORM (Object Relational Mapper)，你能够获得可靠的性能增涨。一个ORM可以做的所有事情，也能被手动的编写出来。但是，这需要一个高级专家。

ORM 的最重要的是“Lazy Loading”，也就是说，只有在需要的去取值的时候才会去真正的去做。但你也需要小心这种机制的副作用，因为这很有可能会因为要去创建很多很多小的查询反而会降低性能。

ORM 还可以把你的SQL语句打包成一个事务，这会比单独执行他们快得多得多。

目前，个人最喜欢的PHP的ORM是：Doctrine。

　　21. 小心“永久链接”

“永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了，它会永远处在连接的状态，就算是数据库操作已经结束了。而且，自 从我们的Apache开始重用它的子进程后——也就是说，下一次的HTTP请求会重用Apache的子进程，并重用相同的 MySQL 链接。

PHP手册：mysql_pconnect()

在理论上来说，这听起来非常的不错。但是从个人经验(也是大多数人的)上来说，这个功能制造出来的麻烦事更多。因为，你只有有限的链接数，内存问题，文件句柄数，等等。

而且，Apache 运行在极端并行的环境中，会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在你决定要使用“永久链接”之前，你需要好好地考虑一下你的整个系统的架构。

 

补充：

mysql强制索引和禁止某个索引

1、mysql强制使用索引:force index(索引名或者主键PRI)

例如:

select * from table force index(PRI) limit 2;(强制使用主键)

select * from table force index(ziduan1_index) limit 2;(强制使用索引”ziduan1_index”)

select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引”PRI和ziduan1_index”)

 

2、mysql禁止某个索引：ignore index(索引名或者主键PRI)

例如:

select * from table ignore index(PRI) limit 2;(禁止使用主键)

select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引”ziduan1_index”)

select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引”PRI,ziduan1_index”)

MySQL高级-索引优化

　索引失效

　　　　　

　　　　　　1、

　　　　　　

　　　　　　2、最佳左前缀法则

　　　　　　　　

　　　　　　4、

　　　　　　

　　　　8、

　　　　　　

　　　　　　

　　　　　　　　使用覆盖索引解决这个问题。

　　　　　　

 　　　　　　

　　　　　　

　　二、索引优化

　　　　1、ORDER BY 子句，尽量使用Index方式排序，避免使用FileSort方式排序

　　　　　　MySQL支持两种方式的排序，FileSort和Index,Index效率高。它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

　　　　　　ORDER BY 满足两种情况，会使用Index方式排序

　　　　　　　　①ORDER BY语句使用索引最左前列

　　　　　　　　②使用WHERE 子句与ORDER BY子句条件列组合满足索引最左前列

 　　　　

 

 

　　　　

　　2、GROUP BY 关键字优化

　　　　1、group by 实质是先排序后分组，遵照索引的最佳左前缀。

　　　　2、当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

　　　　3、where 高于having，能写在where限定的条件就不要去having去限定了。

MySQL 索引优化全攻略

所谓索引就是为特定的mysql字段进行一些特定的算法排序，比如二叉树的算法和哈希算法，哈希算法是通过建立特征值，然后根据特征值来快速查找。而用的最多，并且是mysql默认的就是二叉树算法 BTREE，通过BTREE算法建立索引的字段，比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果。

Explain优化查询检测

EXPLAIN可以帮助开发人员分析SQL问题，explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上Explain就可以了：

Explain select * from blog where false;

mysql在执行一条查询之前，会对发出的每条SQL进行分析，决定是否使用索引或全表扫描如果发送一条select * from blog where false，Mysql是不会执行查询操作的，因为经过SQL分析器的分析后MySQL已经清楚不会有任何语句符合操作。

实例

mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; 
-- 结果： 
id: 1 

select_type: SIMPLE -- 查询类型（简单查询、联合查询、子查询） 

table: user -- 显示这一行的数据是关于哪张表的 。

type: range -- 区间索引（在小于1990/2/2区间的数据)，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中，ALL代表扫描了全表才确定结果。一般来说，得保证查询至少达到range级别,最好能达到ref。 

possible_keys: birthday  -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。  

key: birthday -- 实际使用到的索引。如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。 

key_len: 4 -- 最长的索引宽度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好。

ref: const -- 显示哪个字段或常数与key一起被使用。  

rows: 1 -- 这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。 

Extra: Using where; Using index -- 执行状态说明，这里可以看到的坏的例子是Using temporary和Using

select_type

1. simple:简单select（不使用union或子查询）。
2. primary:最外面的select。
3. union:union中的第二个或后面的select语句。
4. dependent union:union中的第二个或后面的select语句，取决于外面的查询。
5. union result:union的结果。
6. subquery:子查询中的第一个select。
7. dependent subquery:子查询中的第一个select，取决于外面的查询。
8. derived:导出表的select（from子句的子查询）。

其它说明

1. Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了。
2. Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
3. Range checked for each Record(index map:#):没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
4. Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
5. Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
6. Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
7. Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）。
8. system 表只有一行：system表。这是const连接类型的特殊情况。
9. const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待。
10. eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
11. ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于索引匹配的记录多少，越少越好。
12. range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
13. index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
14. ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

其中type:

1. 如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。
2. 如果是where used，就是使用上了where限制。
3. 如果是impossible where 表示用不着where，一般就是没查出来啥。
4. 如果此信息显示Using filesort或者Using temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

索引的类型

UNIQUE唯一索引

不可以出现相同的值，可以有NULL值。

INDEX普通索引

允许出现相同的索引内容。

PRIMARY KEY主键索引

不允许出现相同的值，且不能为NULL值，一个表只能有一个primary_key索引。

fulltext index 全文索引

上述三种索引都是针对列的值发挥作用，但全文索引，可以针对值中的某个单词，比如一篇文章中的某个词，然而并没有什么卵用，因为只有myisam以及英文支持，并且效率让人不敢恭维，但是可以用coreseek和xunsearch等第三方应用来完成这个需求。

索引的CURD

索引的创建

ALTER TABLE

适用于表创建完毕之后再添加。

ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名，可要可不要；如果不要，当前的索引名就是该字段名。 
ALTER TABLE `table_name` ADD UNIQUE (`column_list`) 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) 
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)

CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。

--例：只能添加这两种索引 
CREATE INDEX index_name ON table_name (column_list) 
CREATE UNIQUE INDEX index_name ON table_name (column_list)

另外，还可以在建表时添加：

CREATE TABLE `test1` ( 
  `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意，下面创建了主键索引，这里就不用创建了 
  `username` varchar(64) NOT NULL COMMENT '用户名', 
  `nickname` varchar(50) NOT NULL COMMENT '昵称/姓名', 
  `intro` text, 
  PRIMARY KEY (`id`),  
  UNIQUE KEY `unique1` (`username`), -- 索引名称，可要可不要，不要就是和列名一样 
  KEY `index1` (`nickname`), 
  FULLTEXT KEY `intro` (`intro`) 
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后台用户表';

索引的删除

DROP INDEX `index_name` ON `talbe_name`  
ALTER TABLE `table_name` DROP INDEX `index_name` 
-- 这两句都是等价的,都是删除掉table_name中的索引index_name; 

ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引，注意主键索引只能用这种方式删除

索引的查看

show index from tablename;

索引的更改

更改个毛线,删掉重建一个既可

创建索引的技巧

1. 维度高的列创建索引。

• 数据列中不重复值出现的个数，这个数量越高，维度就越高。
• 如数据表中存在8行数据a,b ,c,d,a,b,c,d这个表的维度为4。
• 要为维度高的列创建索引，如性别和年龄，那年龄的维度就高于性别。
• 性别这样的列不适合创建索引，因为维度过低。

对 where,on,group by,order by 中出现的列使用索引。对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键。为较长的字符串使用前缀索引。不要过多创建索引，除了增加额外的磁盘空间外，对于DML操作的速度影响很大，因为其每增删改一次就得从新建立索引。使用组合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引。

组合索引与前缀索引

注意，这两种称呼是对建立索引技巧的一种称呼，并非索引的类型。

组合索引

MySQL单列索引和组合索引究竟有何区别呢？

为了形象地对比两者，先建一个表：

CREATE TABLE `myIndex` ( 
  `i_testID` INT NOT NULL AUTO_INCREMENT,  
  `vc_Name` VARCHAR(50) NOT NULL,  
  `vc_City` VARCHAR(50) NOT NULL,  
  `i_Age` INT NOT NULL,  
  `i_SchoolID` INT NOT NULL,  
  PRIMARY KEY (`i_testID`)  
);

假设表内已有1000条数据，在这 10000 条记录里面 7 上 8 下地分布了 5 条 vc_Name=”erquan” 的记录，只不过 city,age,school 的组合各不相同。来看这条 T-SQL：

SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='郑州' AND `i_Age`=25; -- 关联搜索;

首先考虑建MySQL单列索引：

在 vc_Name 列上建立了索引。执行 T-SQL 时，MYSQL 很快将目标锁定在了 vc_Name=erquan 的 5 条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉 vc_City 不等于”郑州”的记录，再排除 i_Age 不等于 25 的记录，最后筛选出唯一的符合条件的记录。虽然在 vc_Name 上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的,在 vc_City 和 i_Age 分别建立的MySQL单列索引的效率相似。

为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 vc_Name,vc_City,i_Age 建到一个索引里：

ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

建表时，vc_Name 长度为 50，这里为什么用 10 呢？这就是下文要说到的前缀索引，因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。

执行 T-SQL 时，MySQL 无须扫描任何记录就到找到唯一的记录！

如果分别在 vc_Name,vc_City,i_Age 上建立单列索引，让该表有 3 个单列索引，查询时和上述的组合索引效率一样吗？答案是大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引，另外两个是用不到的，也就是说还是一个全表扫描的过程。

建立这样的组合索引，其实是相当于分别建立了：

• vc_Name,vc_City,i_Age
• vc_Name,vc_City
• vc_Name

这样的三个组合索引！为什么没有 vc_City,i_Age 等这样的组合索引呢？这是因为 mysql 组合索引 “最左前缀” 的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个 T-SQL 会用到：

SELECT * FROM myIndex WHREE vc_Name=”erquan” AND vc_City=”郑州” SELECT * FROM myIndex WHREE vc_Name=”erquan”

而下面几个则不会用到：

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”郑州” SELECT * FROM myIndex WHREE vc_City=”郑州”

也就是，name_city_age(vc_Name(10),vc_City,i_Age) 从左到右进行索引，如果没有左前索引Mysql不执行索引查询。

前缀索引

如果索引列长度过长，这种列索引时将会产生很大的索引文件，不便于操作，可以使用前缀索引方式进行索引前缀索引应该控制在一个合适的点，控制在0.31黄金值即可（大于这个值就可以创建）。

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; — 这个值大于0.31就可以创建前缀索引，Distinct去重复 ALTER TABLE `user` ADD INDEX `uname`(title(10)); — 增加前缀索引SQL，将人名的索引建立在10，这样可以减少索引文件大小，加快索引查询速度。

什么样的sql不走索引

要尽量避免这些不走索引的sql

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引，因为所有索引列参与了计算 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引，因为使用了函数运算，原理与上面相同 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引 

-- 正则表达式不使用索引，这应该很好理解，所以为什么在SQL中很难看到regexp关键字的原因 

-- 字符串与数字比较不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引 

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引，我们建议大家尽量避免使用or 关键字 

-- 如果mysql估计使用全表扫描要比使用索引快，则不使用索引

多表关联时的索引效率

• SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; — 不会使用索引，因为使用了函数运算，原理与上面相同
• SELECT * FROM `houdunwang` WHERE `uname` LIKE’后盾%’ — 走索引
• SELECT * FROM `houdunwang` WHERE `uname` LIKE “%后盾%” — 不走索引

从上图可以看出，所有表的type为all，表示全表索引。也就是6 6 6，共遍历查询了216次。

除第一张表示全表索引（必须的，要以此关联其他表），其余的为range（索引区间获得），也就是6+1+1+1，共遍历查询9次即可。

所以我们建议在多表join的时候尽量少join几张表，因为一不小心就是一个笛卡尔乘积的恐怖扫描，另外，我们还建议尽量使用left join，以少关联多。因为使用join 的话，第一张表是必须的全扫描的，以少关联多就可以减少这个扫描次数。

索引的弊端

不要盲目的创建索引，只为查询操作频繁的列创建索引，创建索引会使查询操作变得更加快速，但是会降低增加、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行重新排序或更新。

但是，在互联网应用中，查询的语句远远大于DML的语句，甚至可以占到80%~90%，所以也不要太在意，只是在大数据导入时，可以先删除索引，再批量插入数据，最后再添加索引。