本文共 5428 字，大约阅读时间需要 18 分钟。

 目录

一、索引 - 优化查询

1-0 查询的三种情况

• 缓存查询(不在mysql中进行数据查询)
• 全表扫描
• 索引扫描

1-1 索引种类

• Btree(btree  b+tree b*tree) - 二叉树
  • BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
• Rtree
  • RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
    相对于BTREE，RTREE的优势在于范围查找。
• HASH
  • 由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
    HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
• FullText - 全文索引
  • 即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
    全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

二、普通索引 - 单例索引、组合索引

MySQL索引的建立可以大大提高MySQL的检索速度。

索引分单列索引和组合索引：

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的缺点：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
• 建立索引会占用磁盘空间的索引文件。
• 建立索引的时候，速度很慢

注：一旦表创建索引，所有查询首先查询索引，再根据索引定位的结果去找数据。

2-1 普通索引的创建方式（三种）

2-1-1 创建表是建立索引

CREATE TABLE 表名 (                字段名1  数据类型 [完整性约束条件…],                字段名2  数据类型 [完整性约束条件…],                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY                [索引名]  (字段名[(长度)]  [ASC |DESC])                 );CREATE TABLE mytable(       ID INT NOT NULL,       username VARCHAR(16) NOT NULL,      INDEX [indexName] (username(length))   );

2-1-2 在已存在的标上创建索引 - CREATE

CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名                      ON 表名 (字段名[(长度)]  [ASC |DESC]) ;CREATE INDEX indexName ON mytable(username(length));

2-1-3 在已存在表上创建索引 -  ALTER TABLE

ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX                             索引名 (字段名[(长度)]  [ASC |DESC]) ;ALTER table tableName ADD INDEX indexName(columnName)

2-2 普通索引的删除方式

DROP INDEX [indexName] ON mytable;

2-3 查看索引信息

SHOW INDEX FROM table_name; \G

三、基于Btree的索引

 

• 聚集索引:基于主键,自动生成的,一般是建表时创建主键.如果没有主键,自动选择唯一键做为聚集索引.
• 辅助索引:人为创建的(普通,覆盖)；MUL
• 唯一索引:人为创建(普通索引,聚集索引)；UNI,如果有重复值是创建不了的

聚集索引和辅助索引的对比总结

• 聚集索引:叶子结点,按照主键列的顺序,存储的整行数据,就是真正的数据页
• 辅助索引: 叶子结点,列值排序之后,存储到叶子结点+对应的主键的值,便于回表查询

 

3-1 聚集索引

聚集索引（clustered index）就是按照每张表的主键（primary key）构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。

特点：叶子节点存放的一整条数据

如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。

如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

 

3-2 辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引）

与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

特点：如果是按照这个字段创建的索引，那么叶子节点存放的是：{名字：名字所在那条记录的主键的值}

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

3-2-1 覆盖索引 and 非覆盖索引

覆盖索引：只在辅助索引的叶子节点中就已经找到了所有想要的数据

select name from user where name='name1';

非覆盖索引：查找非叶子节点存在的数据

select age from user where name='name1';

3-2-2 普通辅助索引创建 （MUL）

alter table blog_userinfo add key idx_email(email);create index idx_phone on blog_userinfo(phone);

3-2-3 查看索引结构

desc blog_userinfo;show index from blog_userinfo;

3-2-4 删除索引

alter table blog_userinfo drop index idx_email;drop index idx_phone on   blog_userinfo;

3-3 前缀索引

对于列的值较长，比如BLOB、TEXT、VARCHAR，就必须建立前缀索引，即将值的前一部分作为索引。这样既可以节约空间，又可以提高查询效率。但无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

语法ALTER TABLE table_name ADD KEY(column_name(prefix_length));示例ALTER TABLE city ADD KEY(cityname(7))select count(*),substring(password,1,20) as sbp  from blog_userinfo group by sbp;alter table blog_userinfo add index idx(password(10));

3-4 唯一键索引

唯一索引，一种索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。

alter table blog_userinfo add unique key uni_email(email);

3-5 覆盖索引 with 联合索引

作用:不需要回表查询,不需要聚集索引,所有查询的数据都从辅助索引中获取

联合索引：指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列

mysql> create table t(    -> a int,    -> b int,    -> primary key(a),    -> key idx_a_b(a,b)    -> );Query OK, 0 rows affected (0.11 sec)alter table t1 add index idx_gam(gender,age,money);

覆盖索引：InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。 - 可以减少会表查询几率

select *  from  people   where   gender ,  age ,  money注意：查询顺序abc固定为建立联合索引的顺序，若顺序混乱则无法进行查询例如：a，b，c建立联合索引，则正常可查询的如下：    where a b c    where a b非正常可查询如下：    where b c a - 无法查询    where a c b - 只能查询到a    where c - 无法查询    where b - 无法查询

四、explain（desc）命令应用 - 获取优化器选择后的执行计划

explain select * from city where countrycode='CHN'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: city         type: refpossible_keys: CountryCode,idx_co_po          key: CountryCode      key_len: 3          ref: const         rows: 1        Extra: Using index condition1 row in set (0.00 sec)

五、索引结构的重要字段

5-0 查看表结构的重要字段

• Extra - 使用的索引类型
• key - 真正走的索引

5-1 explain 下的 type字段 - 查询SQL语句的查询类型

作用:

1. 可以判断出,全表扫描还是索引扫描(ALL就是全表扫描,其他的就是索引扫描)
2. 对于索引扫描来讲,又可以细划分,可以判断是哪一种类的索引扫描

 

SQL语句内的重要字段：

• possible_keys - 可能会走的索引
• type - 索引类型

5-1-1 ALL - 全表扫描

desc select  *  from  t1;

5-1-2 Index - 全索引扫描 - 即在覆盖索引实现查询

desc select countrycode from city ;

5-1-3 range - 索引范围扫描

• where
  • > <  >= <=   
  • in   or   between and 
  •  like 'CH%'
• 注意：字符的普通索引都是走的ALL类型

in 或者 or 改写成 union	select * from city where countrycode='CHN'union all select * from city where countrycode='USA';

5-1-4 ref - 辅助索引的等值查询

select * from city where countrycode='CHN'

5-1-5 eq_ref - 多表链接查询（join、on）

5-1-6 const、system - 主键或唯一键等值查询

5-2 Extra - 使用的索引决定

5-2-1 Extra下出现using  filesort - 文件排序的解决方式

！！将order by、group by 、distinct 后的列和where条件列建立联合索引！！！

转载地址：http://brlvn.baihongyu.com/