MySQL索引及使用详解

一.索引的作用

       一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

       在数据量和访问量不大的情况下，mysql访问是非常快的，是否加索引对访问影响不大。但是当数据量和访问量剧增的时候，就会发现mysql变慢，甚至down掉，这就必须要考虑优化sql了，给数据库建立正确合理的索引，是mysql优化的一个重要手段。  

       索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从上往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

       在创建索引时，需要考虑哪些列会用于 SQL 查询，然后为这些列创建一个或多个索引。

事实上，索引也是一种表，保存着主键或索引字段，以及一个能将每个记录指向实际表的指针。数据库用户是看不到索引的，它们只是用来加速查询的。数据库搜索引擎使用索引来快速定位记录。

      INSERT 与 UPDATE 语句在拥有索引的表中执行会花费更多的时间，而SELECT 语句却会执行得更快。这是因为，在进行插入或更新时，数据库也需要插入或更新索引值。

二.索引的创建、删除

     索引的类型：

• UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
  INDEX(普通索引)：允许出现相同的索引内容
  PROMARY KEY(主键索引)：不允许出现相同的值
  fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维
  组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

  温馨提示：根据《阿里巴巴Java开发手册》里的mysql规约，唯一索引建议命名为uk_字段名，普通索引名则为idx_字段名。（uk_即unique key; idx_即index的简称）。

(1)使用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可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

(2)使用CREATE INDEX语句对表增加索引

       CREATE INDEX可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引。

CREATE INDEX index_name ON table_name(username(length)); 

       如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

//只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

     table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

(3)删除索引

     删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

       其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

      如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

(4) 组合索引与前缀索引

        在这里要指出，组合索引和前缀索引是对建立索引技巧的一种称呼，并不是索引的类型。为了更好的表述清楚，建立一个demo表如下。

create table USER_DEMO
(
   ID                   int not null auto_increment comment '主键',
   LOGIN_NAME           varchar(100) not null comment '登录名',
   PASSWORD             varchar(100) not null comment '密码',
   CITY                 varchar(30) not null comment '城市',
   AGE                  int not null comment '年龄',
   SEX                  int not null comment '性别(0:女 1：男)',
   primary key (ID)
);

       为了进一步榨取mysql的效率，就可以考虑建立组合索引，即将LOGIN_NAME,CITY,AGE建到一个索引里：

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE); 

         建表时，LOGIN_NAME长度为100，这里用16，是因为一般情况下名字的长度不会超过16，这样会加快索引查询速度，还会减少索引文件的大小，提高INSERT，UPDATE的更新速度。

       如果分别给LOGIN_NAME,CITY,AGE建立单列索引，让该表有3个单列索引，查询时和组合索引的效率是大不一样的，甚至远远低于我们的组合索引。虽然此时有三个索引，但mysql只能用到其中的那个它认为似乎是最有效率的单列索引，另外两个是用不到的，也就是说还是一个全表扫描的过程。

       建立这样的组合索引，就相当于分别建立如下三种组合索引：

LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME

      为什么没有CITY,AGE等这样的组合索引呢？这是因为mysql组合索引“最左前缀”的结果。简单的理解就是只从最左边的开始组合，并不是只要包含这三列的查询都会用到该组合索引。也就是说name_city_age(LOGIN_NAME(16),CITY,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,这样可以减少索引文件大小,加快索引查询速度

三.索引的使用及注意事项    

 

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

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

Explain select * from user where id=1;

      尽量避免这些不走索引的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估计使用全表扫描要比使用索引快,则不使用索引

      索引虽然好处很多，但过多的使用索引可能带来相反的问题，索引也是有缺点的：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT,UPDATE和DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件
• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在要给大表上建了多种组合索引，索引文件会膨胀很宽

      索引只是提高效率的一个方式，如果mysql有大数据量的表，就要花时间研究建立最优的索引，或优化查询语句。

     使用索引时，有一些技巧：

    1.索引不会包含有NULL的列

       只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。

    2.使用短索引

       对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    3.索引列排序

       mysql一张表查询只能用到一个索引。因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。这一点是很多程序猿容易忽略的，如where子句的字段建了索引，排序的字段建了索引，但是分开建的，以为会走索引，其实这样的话排序的字段不会使用索引的，除非建复合索引，切记。

    4.like语句操作

      一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。

    5.不要在列上进行运算

    6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

    7.索引要建立在经常进行select操作的字段上。

       这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

    8.索引要建立在值比较唯一的字段上。

    9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

    10.在where和join中出现的列需要建立索引。

    11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。

    12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。

    13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。

    14.在进行联表查询时，建立关联的表的字段类型最好一样且长度一致，这样能更好的发挥索引的作用。

    15.组合索引时切记此条约束：组合索引中有多个字段，其中一个字段是有范围判断，则需将此字段在最后面。如

ALTER TABLE USER_DEMO ADD INDEX name_age (NAME,AGE);  因为age会有范围判断，则建组合索引时将AGE字段放在后面。

    16.字符集字段比较，UTF8与UTF-BIN联合查询是不能走索引的。

如某张表的order_no字段类型为varchar(50),另一张表的order_no字段类型为varchar(50) COLLATE utf8_BIN。则此时联合查询时不能走索引的，切记。

即两张表的字段类型如下：

`order_no` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '订单号',
`order_no` varchar(50) NOT NULL DEFAULT '' COMMENT '订单号',

    17.以下几种情况不适合建索引：

• 表记录太少
• 经常插入、删除、修改的表
• 数据重复且分布平均的表字段。如一个表有10万行记录，其中字段column1只有A和B两种值，且每个值的分布概率大约为50%，那么对这种表column1字段建索引一般不会提高数据库的查询速度

    18.给表创建主键，对于没有主键的表，在查询和索引定义上有一定的影响。

    19.避免表字段为null，建议设置默认值（如int类型设置默认值为0），这样在索引查询上，效率会高很多。

建立索引的几大原则：

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

索引概念和作用

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

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

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 * from doc where title like '%XX'

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

select * from doc where title like 'XX%'

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

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

（1）union：能够命中索引。

示例代码如下：

select * from doc where status=1

union all

select * from doc where status=2

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

（2）in：能够命中索引。

示例代码如下：

select * from doc where status in (1, 2)

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

（3）or：新版的 MySQL 能够命中索引。

示例代码如下：

select * from doc where status = 1 or status = 2

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

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

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

例如下面代码：

select * from doc where status != 1 and status != 2

可以优化为 in 查询：

select * from doc where status in (0,3,4)

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

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

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

select uid, login_time from user where login_name=? andpasswd=?

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

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

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

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

（2）存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。

如  where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

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

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

select uid, login_time from user where passwd=? andlogin_name=?

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

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

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

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

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

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

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

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

例如下面的 SQL 语句。

select * from doc where YEAR(create_time) <= '2016'

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

select * from doc where create_time <= '2016-01-01'

把计算放到业务层，这样做不仅可以节省数据库的 CPU，还可以起到查询缓存优化效果。

比如下面的 SQL 语句：

select * from order where date < = CURDATE()

可以优化为：

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

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

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

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

 select * from user where phone=13800001234

可以优化为：

select * from user where phone='13800001234'

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

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

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

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

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

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

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

Select uid, login_time from user where login_name=? and passwd=?

可以建立(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 WHERE first_name='Eric'AND last_name='Anido';

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

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 * from user where login_name=?

可以优化为：

select * from user 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）索引越多越好，认为一个查询就需要建一个索引。

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

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

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

问题详解

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

 

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

where a=1 and b=1

where b=1

where b=1 order 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 * from a,b where a.tid = b.id and a.tid > 500000 limit 200;

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

select * from b, (select tid from a limit 50000,200) a where 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 and c = 7

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


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

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

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

CREATE TABLEIF 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 > 1

order by views desc limit 1;

回答：

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

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

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

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

结语

对于大数据量的业务，应该时刻考虑到性能，本文只是从使用层面提供了一些优化的思路，由于业务和数据的复杂性，需要具体问题具体分析，对于数据量比较大的业务，最好自己使用 explain 具体分析一下，同时，知其然知其所以然，有时间大家可以看看索引的底层实现原理。

 

 

转自：http://www.yuanrengu.com/index.php/2017-01-13.html

参考：http://tech.meituan.com/mysql-index.html

          http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

          http://thephper.com/?p=142