一、什么是索引及其分类

索引是对数据库表中一个或多个列（例如，employee 表的姓名 (name) 列）的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。“索引”这个词其实和我们以前接触的书本目录其实是一个概念，其最主要作用就是为了加快数据的查询速度。

下面介绍几种常见的MySQL索引类型。在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL 
 
);  

我们随机向里面插入了10000条记录，其中有一条：5555, admin。

在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描所有记录，即要查询10000条记录。

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

MySQL索引类型包括：

（1）普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

1）创建索引 

CREATE INDEX indexName ON mytable(username(length));

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

2）修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length));

3)创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

删除索引的语法：

DROP INDEX [indexName] ON mytable;

（2）唯一索引

它与前面的普通索引类似，不同点是：索引列的值必须唯一(不允许具有索引值相同的行)，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

1）创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length));

2）修改表结构 

ALTER mytable ADD UNIQUE [indexName] ON (username(length));

3）创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);

 

（3）主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
PRIMARY KEY(ID)  
 
);

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

（4）组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
city VARCHAR(50) NOT NULL,  
 
age INT NOT NULL 
 
);  

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


ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

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

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

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

usernname,city,age  
usernname,city  
usernname  


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

SELECT * FROM mytable WHREE username="admin" AND city="郑州";
SELECT * FROM mytable WHREE username="admin";

而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州";
SELECT * FROM mytable WHREE city="郑州";


（5）建立索引的时机

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name 
 
FROM mytable t LEFT JOIN mytable m   
 
ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%';

而下句就不会使用：



SELECT * FROM mytable WHEREt Name like'%admin';


因此，在使用LIKE时应注意以上的区别。

（6）索引的缺点：

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

1）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2）建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

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

（7）使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

1）索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2）使用短索引

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

3）索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4）like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5）不要在列上进行运算

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01’;

6）不使用NOT IN和<>操作

二、索引原理

        想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉)，也就是b tree或者 b+ tree，重要的事情说三遍：“平衡树，平衡树，平衡树”。当然， 有的数据库也使用哈希桶作用索引的数据结构 ， 然而主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

1、首先来聊聊聚集索引

        我们平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。 事实上， 一个加了主键的表，并不能被称之为「表」。一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。没错， 再说一遍， 整个表变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。



上图就是带有主键的表（聚集索引）的结构图。其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句：

select * from table where id = 1256;

首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行细节， 但是从上图能看出，树一共有三层， 从根节点至叶节点只需要经过三次查找就能得到结果。如下图



假如一张表有一亿条数据 ，需要查找其中某一条数据，按照常规逻辑， 一条一条的去匹配的话， 最坏的情况下需要匹配一亿次才能得到结果，用大O标记法就是O(n)最坏时间复杂度，这是无法接受的，而且这一亿条数据显然不能一次性读入内存供程序使用， 因此， 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销，以现在磁盘的IO能力和CPU的运算能力， 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据， 速度以指数级别提升，用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是树的层次数。换言之，查找次数是以树的分叉数为底，记录总数的对数，用公式来表示就是



用程序来表示就是Math.Log(100000000,10)，100000000是记录数，10是树的分叉数（真实环境下分叉数远不止10）， 结果就是查找次数，这里的结果从亿降到了个位数。因此，利用索引会使数据库查询有惊人的性能提升。

然而， 事物都是有两面的， 索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的， 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

2、常规索引

讲完聚集索引 ， 接下来聊一下非聚集索引， 也就是我们平时经常提起和使用的常规索引。

非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段，假如给user表的name字段加上索引 ，那么索引就是由name字段中的值构成。在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。如下图



 

每次给字段建一个新索引，字段中的数据就会被复制一份出来用于生成索引。 因此，给表添加索引会增加表的体积，占用磁盘存储空间。

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值 ，再使用主键的值通过聚集索引查找到需要的数据。如下图



不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。

然而， 有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种非主流的方法 称之为「覆盖索引」查询， 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出， 当为字段建立索引以后， 字段中的内容会被同步到索引之中， 如果为一个索引指定两个字段， 那么这个两个字段的内容都会被同步至索引之中。

先看下面这个SQL语句

//建立索引

create index index_birthday on user_info(birthday);

//查询生日在1991年11月1日出生用户的用户名

select user_name from user_info where birthday = '1991-11-1'

这句SQL语句的执行过程如下

首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就的真实数据（数据行）存储的位置

最后， 从得到的真实数据中取得user_name字段的值返回， 也就是取得最终的结果

我们把birthday字段上的索引改成双字段的覆盖索引

create index index_birthday_and_user_name on user_info(birthday, user_name);

这句SQL语句的执行过程就会变为

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而， 叶节点中除了有user_name表主键ID的值以外， user_name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能，如下图



数据库索引的大致工作原理就是像文中所述， 然而细节方面可能会略有偏差，这但并不会对概念阐述的结果产生影响 。

 

https://zhuanlan.zhihu.com/p/23624390

https://blog.csdn.net/weixin_42181824/article/details/82261988

索引特征及概念
索引是最为常用的改善数据库性能的技术。SQL Server引入索引主要是为了提高查询的效率，它能够加速ORDER BY和GROUP
 BY子句的操作，保证数据的唯一性，并加快表连接的速度。

索引的概念和创建原则


索引就是一个指向表中数据的指针，如果我们把书比作数据，那么各个章节就相对于数据库中的表，而书的目录就是索引了。
索引是在基本表的列上建立的一种数据库对象，它和基本表分开存储，它的建立或撤销对数据的内容毫无影响。索引一经创建，就完全由系统自动选择和维护，不需要用户指定使用索引，也不需要用户执行打开索引或进行重新索引等操作。

在创建索引时，务必注意下列问题

（1）基本表的记录数量越多，记录越长，越有必要创建索引，这对加快查询速度十分有效。
（2）索引是为了加快查询速度而创建的，但同时会影响基本表的插入、删除或修改等更新活动的速度。因此只有那些对数据量大、查询频度高、实时性要求强的基本表才创建索引，且一张基本表上不要创建过多的索引。
（3）索引是建立在基本表列上的对象，对哪个列进行索引，通常根据列在WHERE、ORDER BY、GROUP
 BY子句中出现的频率决定。
不适合建索引的列的情况
（1）包含太多重复值的列
（2）查询中很少被引用的列
（3）值特别长的列
（4）具有很多null值的列
（5）需要经常插入、删除、修改的列
（6）记录较少的基本表
（7）需要进行频繁的大批量数据更新的基本表

索引的类型
从两个方面分类

1、从列的使用角度可将索引分为单列索引、唯一索引、复合索引三类。

2、从是否改变基本表记录的物理位置角度可分为聚集索引和非聚集索引两类。


分类


1、单列索引
      是对基本表的某一单独的列进行索引，通常应对每个基本表的主关键字建立单列索引。
2、唯一索引
      一旦在一个或多个列上建立了唯一索引，则不允许在表中相应的列上插入任何相同的取值。
3、复合索引
      是针对基本表中两个或两个以上列建立的索引。
4、聚集索引
      采用聚集索引会改变基本表中记录的物理存储顺序。
5、非聚集索引
      采用非聚集索引，表中记录的物理顺序与索引顺序不同，即表中的记录仍按实际插入的先后顺序排列，不按索引列排序。

聚集和非聚集索引的对比


1、存取速度
      从建立了聚集索引的表中取出数据要比建立了非聚集索引的表快，但相对而言，聚集索引会降低向表中插入、删除和修改数据的速度。
2、索引的数据          

      对聚索引的主要限制是每个表只能建立一个聚集索引，但是一个表可以有不止一个非聚集索引。
3、所需空间
      非聚集索引需要较多的硬盘空间和内存

创建和删除索引


1、使用企业管理器方法
         （1）通过点击“钥匙”图标创建，这种方式将在所选定的一个或多个列上创建一个聚集索引，是表的主关键字索引。
         （2）即使用索引管理菜单创建、修改、撤销索引。
                     鼠标右击——〉所有任务——〉管理索引
2、使用Transact SQL创建索引
     Transact SQL使用create index命令创建索引，常用的语法结构如下：
     create [unique]  [clustered|nonclustered] index <索引名> 
                 on   <表名> (<列名1> [次序]
 [，<列名2>[次序]]···)
     说明：
          unique:为表或视图创建唯一索引（不允许存在索引值相同的两行）。
          clustered:聚集索引。
          nonclustered:非聚集索引，默认值
          索引名：在实际使用时，，用户并不需要知道索引名，但在创建阶段，索引名应符合SQL Server的命名规则，并且在整个数据库中，索引名不能重复。
     例如：create unique clusteredindex myindex1
               on  
成绩表（学号，课程号）
3、使用Transact SQL删除索引
      语法结构：drop indextable_name.index_name [,···n]
约束

设计数据库完整性的目的是为了防止数据库存在不符合语义的数据，防止错误信息的输入和输出。SQL Server提供的用来实施数据完整性的途径主要是约束(Constraint)、标识列(Identity Column)、默认(Default)、规则(Rule)、触发器(Trigger)、数据类型(Data Type)、索引(Index)和存储过程(Stored Procedure)等

使用约束实施数据的完整性


约束的用途是限制用户输入到表中的数据的值的范围，一般分为列级约束和表级约束两种。要浏览某张表格上所有约束的信息，可以使用存储过程：
           sp_helpconstraint 表名
(1)  primary key  
约束
     特征：
       a、创建primary key约束时，SQL Server会自动创建一个唯一的聚集索引；
       b、定义了primary key约束的字段的取值不能重复，并且不能取null值；
       c、每个表只能定义一个primary key约束；
       d、如果表中已经有了聚集索引，那么在创建primary key约束之前，要么指定所创建的是非聚集索引，要么删除已有的聚集索引


例 1  创建表级primary key约束。
      create table 学生表(
         学号  char(8)  not null,
         姓名 char(8) null,
        constraint  
pk_学生表  primary key  (学号));
例 2  创建列级primary key 约束
      create table 学生表(
    学号 char(8)  not null,
    constraint pk_学号 primary keynonclustered(学号),
     姓名  char(8) null);
(2) Foreign  Key约束
    特征：
     a、一旦Foreign Key约束定义了某个字段，则该字段的取值必须参照(Reference)同一表或另一表中的Primary Key约束或Unique约束。
     b、Foreign Key约束不能自动建立索引。
 例 3 
 create table  成绩表1(
       学号 char(8) not null constraintpk_no references 学生表(学号),
       成绩 int);
(3) default 约束
  特征：
      a.每一个字段只能有一个Default约束；
      b.default约束不能放在IDENTITY字段上或者timestamp字段上。
例 4 alter  table 成绩表
       add constraint df_成绩 default 0 for 成绩
例 5  create  table 成绩表2(
         学号 char(8) not null,
          课程代码 char(7) not null,
          成绩 
float constraint  df_score default 0.00);
(4)Unique约束(用于不是主键但又要求不能有重复值出现的字段)   特征：
    a.一个表可以有多个Unique 约束；
    b. 按照Unique约束的要求，在一个表中不允许受约束列的字段上有相同的null值，因此最好将被定义了Unique约束的列定义为非空（允许被约束列的值为空）
    c.创建Unique约束时，系统自动创建了非聚集索引。
例 6  alter table 学生表
         add constraint un_name unique(姓名)
例 7  create table abc(
          学号 char(8) not null  constraint un_no unique,
          姓名 char(8));
例 8  create table cbd(
                  学号 char(8) not null,
                  姓名 char(8),
                  constraint  
un_xh unique(学号));
(5)Check约束
     特征：
     a.限制了向特定的字段列输入数据的类型；
     b.表级定义的Check约束可以对多个字段列进行核查。
例 9  create table efg(
            学号 char(8)not null,
            姓名 char(8) not null,
            年龄 int,
           constraint  
chk_age check (年龄>17 and 年龄<25));
例10  create table  efg1(
           学号 char(8) not null,
           姓名 char(8) not null,
          年龄 int constraint chk_age1 check(年龄>17 and 年龄<25));

(6)删除约束
如果需要将某个约束删除，可以使用alter table约束，其语法格式是：
     alter table 表名
      drop constraint 约束名

1.索引的概念:

索引是一种特殊的文件，包含着对数据表中所有的记录的引用指针，数据库索引好比是一本书前面的目录，能加快数据库的查询速度，数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。
2.索引的作用：

建立索引的目的是加快对表中记录的查找或排序，为表设置索引要付出代价；一是增加了数据库的存储空间，二是在插入和修改数据时要花费更多的时间（因为索引也会随之改变）。

（1），设置合适的索引之后，数据库利用各种快速的定位技术，可以大大加快数据的查询速度，这也是创建索引的最主要的原因。

（2）当表很大的时候，或者查询涉及到多个表时，使用索引可以使查询速度快上成千倍。

（3）可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。

（4）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

（5）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。

3.索引的分类

MySQL的索引分为以下几类：

（1）普通索引，这是最基本的索引类型，而且它没有唯一性之类的限制。

（2）唯一索引，这种索引和前面的‘普通索引’基本相同，但是有一个区别；索引列表的所有值只能出现一次，即必须唯一，当现有数据中存在重复的键值时，大多数数据库都不允许新创建的唯一索引与表一起保存，数据库还可以防止添加将再表中创建重复键值的新数据。

（3）主键索引，主键索引时一种唯一性索引，但它必须指定为‘PRIMARY KEY（字段）’在数据库中为表定义主键将自动创建主键索引，主键索引是唯一的特定类型，该索引要求主键中的每一个值都唯一。

（4）全文索引，索引类型为（FULLTEXT），全文索引可以在CHAR VARCHAR 或者TEXT类型的列上创建。

（5）单列索引与多列索引，索引可以是单列上创建索引，也可以是在多列上创建的索引，多列索引可以区分其中一列可能有相同的行。如果经常同时搜索两列或者多列排序时，索引也时很有帮助的。

4.创建索引的原则依据。

索引可以提升数据库查询的速度，因为索引会占用数据库的系统资源不是任何情况下都需要建立索引的，数据库查询会先查询索引查询。

（1）表的主键、外建必须有索引，主键具有唯一性，索引值也是唯一性，查询时可以快速定位到数据行，外键一般关联的是另一个表的主键，所有在多表查询时也可以快速定位。

（2）数据量过300行的表应该有索引，数据量较大的时候，如果没有索引，需要把表遍历一遍，严重影响数据库的性能。

（3）经常与其他表进行表连接的表，在连接字段上应该建立索引。

（4）唯一性太差的字段不适合建立索引，如果索引字段的数据唯一性太差，是不适合创建索引。

（5）更新太频繁的字段不合适创建索引，在表中进行增加、删除、修改操作时，索引也有相应操作产生、字段更新得过于频繁，对于系统资源占用也会更多。

（6）经常出现在where（条件判断）字句中的字段，特别是大表的字段，应该建立索引。

（7）索引应该建立在选择性高的字段上，如果很少的字段拥有相同值，即有很多独特值，则选择性很高。

（8）索引应该建在小字段上，对于大的文本段甚至超长字段，不要建索引。
5.创建索引的方法；

（1）创建普通索引，命令格式是：

CREATE INDEX (索引名字)ON tablename(列的列表)
mysql> create index salary_index （索引名）on sys.IT_salary(name)（那个库的表里的字段）;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  show index from IT_salary;查看索引方法

explain select * from IT_salary;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | IT_salary | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

各属性含义：

id: 查询的序列号

select_type: 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询

SIMPLE：查询中不包含子查询或者UNION

查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY

在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

table: 输出的行所引用的表

type: 访问类型
从左至右，性能由差到好

1.ALL: 扫描全表

2.index: 扫描全部索引树

3.range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

4.ref: 使用非唯一索引或非唯一索引前缀进行的查找

（eq_ref和const的区别：）

5.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

6.const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询的表只有一行的情况下， 使用system。

7.NULL: 不用访问表或者索引，直接就能得到结果，如select 1 from test where 1

possible_keys: 表示查询时可能使用的索引。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引

key: 显示MySQL实际决定使用的索引。如果没有索引被选择，是NULL

key_len: 使用到索引字段的长度

注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

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

rows: 这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的

Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。

1.

.Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。

.Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。

Key_name对应的是索引名字，显示名字是salary_index的索引已经存在，NOnunique对应值是1，表示不是唯一性索引。
（2）创建唯一索引，命令格式：
CREATE UNIQUE INDEX(索引名字) ON tablename（表名）（列的列表，字段）；

该索引比普通索引多了一个关键字UNIQUE关键字。
mysql> create unique index salary （索引名字）on IT_salary（表）(id);//将这个表中的ID号设置为唯一索引名字为salary
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

它的NOn_unique值为0表示唯一索引’

（3）创建主机索引两种方式

1.1：一种是在创建表的同时创建主键，主键索引会自动创建，命令格式：
CREATE TABLE tablename（表名）([...],PRIMARY KEY(列的列表))；

1.2：已经创建了表，没有指定主键，然后修改表加入主键，主键索引会自动创建命令如下：
ALTER TABLE （表名） ADD PRIMARY KEY(列的类表，字段)；


（4）在mysql中使用全文索引（FULLTEXT index）,目前只有使用myiSAM类型表的时候有效（msISAM是默认的表类型）全文索引建立在TEXT、CHAR或者VARVHAR类型的字段或者字段组合上，创建表时指定或修改表时指定全文搜索，命令如下：
CREATE TABLE 表名(列名 TEXT,FULLLTEXT(列名)) enginde=MyISAM;

ALTER TABLE 表名 ADD FULLTEXT（列名）；

CREATE TABLE 表名(列名字1 TEXT,FULLTEXT(列名1)) engine=MyISAM;

(5)多列索引只需要在创建索引时指定多少列即可；
mysql> create index salary_age_index （索引名字）on IT_salary(age列1,type列2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

（6）不使用索引时，应该删除索引
1.DROP INDEX 索引名 ON 表名;删除索引
2.ALTER TABLE  表名 DROP INDEX l列;
3.ALTER TABLE 变名 DROP PRIMARY KEY;

第一条命令是直接删除索引，第二条命令是修改表时删除索引，第三条是删除主键索引。

1.第一种直接删除索引。



2.修改表时删除索引。



3.删除主键索引。



（7）查看索引方法：
SHOW INDEX FROM 表名；或者SHOW KEYS FROM 表名。

MongoDB优化索引
基本特点
基础语法
简单练习
准备数据
创建普通索引
创建复合索引
创建唯一索引
分析索引
选择规则

基本特点

建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

因为，增加索引也有许多不利的方面。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

基础语法

创建索引语法

db.集合名.createIndex(待创建索引的列 [,额外选项])

参数：

待创建的列：{键：1…键：-1}

说明：1升序，-1降序，例如{age:1}表示创建age索引并按照升序的方式存储

额外选项：设置索引的名称或者唯一索引等等


删除索引语法


全部删除

db.集合名.dropIndexes()


删除指定

db.集合名.dropIndex(索引名)


查看索引语法

db.集合名.getIndexes()

简单练习
准备数据
向数据库中增加十万条数据
 use test5
 for(var i=0;i<100000;i++){
 	db.c1.insert({name:"aaa"+i,age:i});
 }


创建普通索引
1 . 给name列创建普通索引并查看所有索引
//创建普通索引
db.c1.createIndex({name:1})
//查看所有索引
db.c1.getIndexes()


2.删除name索引并查看
重点：先查看所有索引获取要删除的索引名


//删除name列的索引
 db.c1.dropIndex('name_1')
//查看所有索引
 db.c1.getIndexes()



3.给name列创建索引并命名为name_czy(自定义)
//给name列创建索引并命名为name_czy
 db.c1.createIndex({name:1},{name:"name_czy"})
//查看所有索引
 db.c1.getIndexes()


创建复合索引
一次性给多个字段添加索引
db.集合名.createIndex({键1:存储方式，键2:存储方式...})

给name和age两个字段添加索引
db.c1.createIndex({name:1,age:1})
db.c1.getIndexes()


创建唯一索引
语法
db.集合名.createIndex(待添加的索引的列, [unique:列名])

给name添加普通索引
 //删除所有索引
 db.c1.dropIndexes()
 //创建唯一索引
 db.c1.createIndex({name:1},{unique:"name"})


分析索引
语法
db.集合名.find().explain('executionStats')


扫描方式

COLLSCAN 全表扫描

IXSCAN 索引扫描

FETCH 根据索引去检索指定document


age列未添加索引扫描

db.c1.find({age:18}).explain('executionStats')



age列添加索引扫描

//添加普通索引
db.c1.createIndex({age:1})
//扫描
db.c1.find({age:18}).explain('executionStats')


选择规则

为常做条件、排序、分组的字段建立索引
选择唯一性索引
选择较小的数据列，为较长的字符串使用前缀索引