MySQL索引2

**************************索引建议*************************************************

使用：

• 经常检索的列

• 经常用于表连接的列

• 经常排序分组的列

不适用：

• 基数很低的列

• 更新频繁检索不频繁的列

• BLOB/TEXT等长内容的列

• 很少用于检索的列

**************************聚集索引*************************************************

• 索引中键值的顺序决定了表数据行的物理顺序

• 每张表只能建一个聚集索引，除了TokuDB引擎

• InnoDB表即聚集索引，聚集索引即表

• MyISAM没有聚集索引的概念

• 聚集索引有限选择列：

  • 含有大量非重复值的列
    

  • 被连续顺序访问的列

  • 返回大量结果集查询的列

  • 不建议的聚集索引：

  • 修改频繁的列
    

  • 新增内容太过离散随机的列

• 聚集索引选择顺序原则

  • 显示声明的主键
    

  • 第一个不包含null列的唯一索引列
    

  • 内置的rowid

**************************主键*************************************************

• 主键有表中的一个或者多个字段组成，它的值用于唯一的标识表中的一行

• 在表引用中，主键在一个表中引用来自于另一个表中的特定记录

• 保证数据的完整性

• 加快数据的操作速度

• 主键值不能重复，也不能包含NULL

• 主键设计建议：

• 
  

  • 对业务透明，无意义，免受业务变化影响

  • 主键要很少修改和删除

  • 主键最好是自增的(不是自增，容易导致B+tree索引分裂，浪费空间)

  • 不要具有动态属性，例如最后修改时间戳

• InnoDB主键特点

  • 索引定义时，若不显示包含主键，会隐式加入主键值

  • 索引定义时，若显示包含主键，会加入主键值

  • 在5.6.9后，优化器已能自动识别索引末尾的主键值，在这之前需要需要显示加上主键列才能识别，可参考刀刀博客自行实验index索引FIELDS到底含有哪些值：http://blog.csdn.net/zyz511919766/article/details/50147283

  • 案例：某InnoDB表，没有自增列主键，使用一段时间后，产生碎片，重整表空间后，表空间大小由13G变为9G，主要原因是没有使用自增列，导致B+tree频繁分裂，浪费空间。可参考叶大神的博客：http://imysql.com/2014/09/14/mysql-faq-why-innodb-table-using-autoinc-int-as-pk.shtml

**************************唯一索引*************************************************

• 不允许具有索引值相同的行，从而禁止重复的索引或者键值

• 在唯一约束上和主键一样

• 与主键不同方面：

  • insert ignore
    

  • set unique_checks=0

  • insert into t values(3,2) on duplicate key update uid=3;

  • replace into t values(2,3);
    

  • 唯一索引允许有Null值

  • 一个表只能有一个主键，可以有多个唯一索引

  • InnoDB表中主键必须是聚集索引，但是聚集索引不一定是主键

  • 唯一索引约束可以临时禁用，但是主键不行，避开唯一约束的方法

**************************联合索引*************************************************

• 多列组成

• 适合where条件中的多列组合

• 有时可避免回表

• 以往有列顺序，现在没有这个限制

• 不支持多列不同排序规则

alter table t add index idx1( a asc, b desc);
等同于
alter table t add index idx1( a asc, b asc);
或者
alter table t add index idx1( a, b);

联合索引建议

• where条件中经常出现的列放在联合索引中

• 把选择性最大的列放在联合索引的最左边

**************************覆盖索引*************************************************

通过索引数据结构，可以直接返回数据，不需要回表，执行计划中显示关键字using index

例：

 CREATE TABLE `parent` (

  `par_id` int(11) NOT NULL,

  `fname` char(20) DEFAULT NULL,

  `lname` char(20) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  PRIMARY KEY (`par_id`),

  UNIQUE KEY `lname` (`lname`,`fname`),

  KEY `idx_1` (`lname`,`age`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

root@localhost:mysql3306.sock  15:32:21 [test]>explain select * from parent where fname=‘gao‘ and lname=‘chao‘;

+----+-------------+--------+-------+---------------+-------+---------+-------------+------+-------+

| id | select_type | table  | type  | possible_keys | key   | key_len | ref         | rows | Extra |

+----+-------------+--------+-------+---------------+-------+---------+-------------+------+-------+

|  1 | SIMPLE      | parent | const | lname,idx_1   | lname | 162     | const,const |    1 | NULL  |

+----+-------------+--------+-------+---------------+-------+---------+-------------+------+-------+

1 row in set (0.00 sec)

root@localhost:mysql3306.sock  15:34:25 [test]>explain select fname,lname from parent where fname=‘gao‘ and lname=‘chao‘;

+----+-------------+--------+-------+---------------+-------+---------+-------------+------+-------------+

| id | select_type | table  | type  | possible_keys | key   | key_len | ref         | rows | Extra       |

+----+-------------+--------+-------+---------------+-------+---------+-------------+------+-------------+

|  1 | SIMPLE      | parent | const | lname,idx_1   | lname | 162     | const,const |    1 | Using index |

+----+-------------+--------+-------+---------------+-------+---------+-------------+------+-------------+

1 row in set (0.00 sec)

请注意，当指定查询字段与索引一致时，Extra是using index，直接从索引中取得结果，当指定为*时，就会回表，显示为null，查询时先根据索引找到索引行，然后根据索引的value值即主键取出锁需数据。

**************************部分索引*************************************************

• 使用部分索引的原因

  • char/varchar太长全部做索引的话，效率太差，存在浪费

  • 或者blob/text类型不能整列作为索引，因此需要使用前缀索引

• 部分索引选择建议

  • 统计平均值

  • 遵循2/8原则

  • 具体可参考老王的博客http://blog.csdn.net/freshlover/article/details/8634610，总的原则就是选取长度重复度要尽量低

• 语法

        alter table t add index idx_user(user(12));

**************************索引提高SQL效率的几种方式******************************************

• 提高数据检索效率

• 提高聚合函数效率

• 提高排序效率

• 个别时候可以避免回表

• 减少多表关联时扫描行数

• 主键、唯一索引可以作为约束

• 列定义为DEFAULT NULL时，NULL值也会有索引，存放在索引数的最前端部分

**************************什么情况下无法使用索引******************************************

• 通过索引扫描的记录数超过30%，变成全表扫描

• 联合索引中，第一个索引列使用范围查询

• 联合索引中，第一个查询条件不是最左索引列

• 模糊查询条件列最左以通配符%开始

• 内存表使用HASH索引时，使用范围检索或者ORDER BY

• 两个独立索引，其中一个用于检索，一个用于排序

• 使用了不同的order by 和group by 表达式

**************************索引设计原则及相关限制******************************************

• 索引设计原则

  • 一个索引里包含的列数，最好不要超过5个

  • 一个表的索引数，不要超过5个

  • 联合索引中 把过滤性高的列放在前面

• 索引限制

  • 不支持混合顺序

  • 不支持位图索引

  • 不支持函数索引

**************************索引举例******************************************

假设有联合索引：idx1(a,b,c)

下面的SQL可以完整用到索引：

• SELECT ... WHERE b=? and c=? and a=?;

• SELECT ... WHERE b=? and a=? and c=?;

• SELECT ... WHERE a=? and b in (?,?) and c=?;

• SELECT ... WHERE a=? and b=? order by c;

• SELECT ... WHERE a=? and b in (?,?) order by  c;

• SELECT ... WHERE a=? order by b,c;

• SELECT ... WHERE order by a,b,c;--只有指定查询列为最左索引字段时才能使用到索引

联合索引最左匹配（可用到部分索引，或者可利用到ICP(参考文章http://mdba.cn/?p=315)特性）：

• SELECT ... WHERE b=? and a=?;--只用到a,b部分

• SELECT ... WHERE a in (?,?) and b=?;--a,b,并且用到icp

• SELECT ... WHERE (a between ? and ?) and b = ?--a,b并且用到icp

• SELECT ... WHERE a = ? and b in  (?,?);--a,b，并且用到icp

• SELECT ... WHERE a = ? and (b between ? and ?) and c = ?;--a,b,c,并且有ICP

• SELECT ... WHERE a = ? and c = ?;只用到a,同时用到ICP

• SELECT ... WHERE a = ? and c 》= ?;只用到a,同时用到ICP 
  

联合索引最左匹配（未用到索引）：

• SELECT ... WHERE b = ?

• SELECT ... WHERE b = ? and c = ?

• SELECT ... WHERE order by b;

• SELECT ... WHERE order by b,a;

MySQL索引2

原文：http://gaoquan.blog.51cto.com/4503718/1734694