1、索引

1.1、索引概述：

MySQL官方对索引的定义：索引（index）是帮助MySQL高效获取数据的数据结构（有效），在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。简而言之：帮助MySQL高效的查询出数据的数据结构叫做索引。

一般来说，索引本身也很大，不可能全部都存储在内存中，因此索引一般都是以索引文件的形式存储在磁盘上。索引是数据库用来提高性能最好的方式。

1.2、索引优劣势

优势：

• 索引类似于书籍的目录，提高数据检索的效率，减少数据库IO的成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势：

• 实际上索引也是一张表，存储在磁盘上，该表保存了主键与索引字段，并指向实体类的记录。
• 虽然索引大大提高了查询的速度，但是降低了增删改的速度，对表进行update、insert、delete时，需要对索引文件进行更新

1.3、索引结构

索引是在MySQL的存储引擎中实现的，而不是服务层实现的。所以每种存储引擎的索引都是不一样的，也不是所有的存储引擎都支持所有的索引类型。MySQL目前提供了以下4种索引：

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

在平常，如果没有特殊的说明，都是使用B+树结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引。

1.3.1、BTREE树

Btree树的特点：

BTree又叫多路平衡搜索树，一颗m叉的BTree特征如下：

• 树中每个节点最多包含m个孩子
• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子
• 若根节点不是叶子节点，至少含有两个孩子
• 所有的叶子节点都在同一层
• 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1]<=n<=m-1

举个例子，以3叉BTree为例，插入1~10数字

（1）、插入1,2的时候

（2）、插入3

（3）、插入4

（4）、插入5

（5）、插入6

（6）、插入7

（7）、插入8

（8）、插入9

（9）、插入10

1.3.2、B+TREE树

B+Tree为BTree的变体，B+Tree与Tree的区别为：

• n叉B+Tree最多含有n个key，而BTree最毒含有n-1个key。
• B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
• 所有的非叶子节点都可以看作是key的索引部分。

在数据库的聚集索引（Clustered Index）中，叶子节点直接包含卫星数据。在非聚集索引（NonClustered Index）中，叶子节点带有指向卫星数据的指针。

1.3.2、MYSQL中的B+Tree

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

1.4、索引分类

（1）、单值索引：及一个索引值包含单个列，一个表可以有多个单列索引
（2）、唯一索引：索引列的值必须唯一，但允许有控制
（3）、复合索引：即一个索引包含多个列

1.5、索引的用法

创建索引

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

查看索引

show index  from  table_name;

删除索引

DROP  INDEX  索引名称  ON  表名;

一些alter命令

1). alter  table  tb_name  add  primary  key(column_list); 

	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
	
2). alter  table  tb_name  add  unique index_name(column_list);
	
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
3). alter  table  tb_name  add  index index_name(column_list);

	添加普通索引， 索引值可以出现多次。
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	
	该语句指定了索引为FULLTEXT， 用于全文索引

1.6、索引的设计原则

我们在设计索引的时候，有一些原则，创建索引的以后尽量符合这些原则。便于提升索引的使用效率，更高效的使用索引：

• 对于查询频率比较高的列，可以设置为索引列
• 索引字段的选择，最佳应从where条件语句中提取，如果where子句中组合比较多，那么应当挑选最常用、效率效果最好的列的组合
• 使用唯一索引，区分度越高，使用索引的效率越高
• 索引可以有效的提升查询数据的效率，但是并不是索引越多越好，索引越多，维护起来月麻烦。对于DML操作而言，会引起相当高的维护代价，
• 可以使用段索引，索引创建之后也是使用磁盘来进行存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段的总长度比较短，那么在给定大小的存储块可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率
• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率

创建复合索引:

	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

1、直接创建索引
可以使用CREATE INDEX语句明确地创建索引，例如在emp表的ename列上创建一个名为emp_ename的索引，该索引使用表空间users。
SQL> create index emp_ename on emp(ename) STORAGE(INITIAL 50,NEXT 50,ON USERS);
该语句为该索引明确地指定了几个存储位置和一个表空间。如果没有给索引指定存储选项，则INITIAL和NEXT等存储选项会自动使用表空间的默认存储选项。
2 、创建聚集索引
当建表语句未指定聚集索引键时，达梦数据库默认的聚集索引键是ROWID。若指定索引键，表中数据都会根据指定索引键排序。
建表后，DM8也可以用创建新聚集索引的方式来重建表数据，并按新的聚集索引排序。
SQL> CREATE CLUSTER INDEX clu_emp ON emp(ename);
建议在建表时就确定聚集索引键，或在表中数据比较少时新键聚集索引，而尽量不要对数据量非常大的表建立聚集索引。
3、 创建唯一索引
索引可以是唯一的或非唯一的，唯一索引可以保证表上不会有两行数据在键列上具有相同的值，非唯一索引不会在键列上施加这个限制。
使用CREATE UNIQUE INDEX语句来创建唯一索引
SQL> CREATE UNIQUE INDEX dept_unique_index ON dept (dname) storage (on users);
用户希望在列上定义UNIQUE完整性约束，达梦数据库可以通过自动地在唯一键上定义一个唯一索引来保证UNIQUE完整性约束。
4 、自动创建与约束相关的唯一索引
达梦数据库可以通过在唯一键或主键上创建一个唯一索引来在表上实施UNIQUE KEY 或PRIMARY KEY完整性约束。
SQL> alter table emp add constraint pk_emp_name primary key(name);
上面语句会自动在表emp的name列上创建一个唯一索引。
5、 创建基于函数的索引
基于函数的索引促进了限定函数或表达式的返回值的查询，该函数或表达式的值被预先计算出来并存储在索引中。
1.创建更强有力的分类，可以用UPPER和LOWER函数执行区分大小写的分类
2.预先计算出计算密集的函数的值，并在索引中将其分类。可以在索引中存储要经常访问的计算密集的函数，当需要访问值时，该值已经计算出来了。可以极大地改善查询的执行性能。
3.增加了优化器执行范围扫描而不是全表扫描的情况的数量
SQL> create index idx on example_tab(column_a + column_b);
SQL> select * from example_tab where column_a + column_b <10;
上面创建的索引是建立在column_a + column_b 之上的，索引优化器可以在下面的查询与语句中使用范围扫描。优化器根据该索引计算查询代价，如果代价最少，优化器就会选择该函数索引，column_a+column_b就不会重复计算。
6、 创建位图索引
位图索引只要针对含有大量相同值得列而创建。位图索引被广泛引用到数据仓库中，创建方式和普通索引一致，对低基数（不同得值很少）得列创建位图索引，能够有效提高基于该列得查询效率。且执行查询语句的WEHER子句中带有AND和OR谓词时，效率更加明显。
SQL> create bitmap index s1 on purchasing.vendor(vendorid);
创建一个位图索引

1、表的主键、外键必须有索引； 
2、数据量超过300的表应该有索引； 
3、经常与其他表进行连接的表，在连接字段上应该建立索引； 
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 
5、索引应该建在选择性高的字段上； 
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

8、频繁进行数据操作的表，不要建立太多的索引； 
9、删除无用的索引，避免对执行计划造成负面影响；

以上是一些普遍的建立索引时的判断依据。 
索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。 
因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。 另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。 
总的来说，小型表肯定不建索引， 
或者数据库记录在亿条数据级以上，还是建议使用非关系型数据库。 
还有些特殊字段的数据库，比如BLOB，CLOB字段肯定也不适合建索引。 
其实这个问题更感觉偏向于做软件项目的一种经验。

对千万级MySQL数据库建立索引的事项及提高性能的手段

一、注意事项：

首先，应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据，在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。 
其次，在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。

二、性能调整方面：

首当其冲的考虑因素便是磁盘I/O。物理上，应当尽量把索引与数据分散到不同的磁盘上（不考虑阵列的情况）。逻辑上，数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

其次，我们知道，在建立索引的时候要对表进行全表的扫描工作，因此，应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。

再次，建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作，因此，应当调整排序区的大小。

9i之前，可以在session级别上加大sort_area_size的大小，比如设置为100m或者更大。

9i以后，如果初始化参数workarea_size_policy的值为TRUE，则排序区从pga_aggregate_target里自动分配获得。

最后，建立索引的时候，可以加上nologging选项。以减少在建立索引过程中产生的大量redo，从而提高执行的速度。

MySql在建立索引优化时需要注意的问题

设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：

1，创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2，复合索引

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22; 
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效 
率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, 
salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀 
特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3，索引不会包含有NULL值的列

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

4，使用短索引

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

5，排序的索引问题

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

6，like语句操作

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

7，不要在列上进行运算

select * from users where 
YEAR(adddate)

8，不使用NOT IN和操作

NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id

添加索引示例:

添加索引示例:

 

   

    

   
   

    

     CREATE 
     INDEX IDX_AUDITSTATUS 
     ON [shanghaiDB].[dbo].[Activity](AUDITSTATUS) 
     WITH(
     ONLINE=
     ON)
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     CREATE 
     INDEX IDX_ANUMMID 
     ON [nantongDB].[dbo].[Orders](ANUM,
     MID) 
     WITH(
     ONLINE=
     ON)
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     CREATE 
     INDEX IDX_SiteCode 
     ON Usercenter.[dbo].MO(SiteCode) 
     WITH(
     ONLINE=
     ON)
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     CREATE 
     INDEX IDX_AccessDt 
     ON [all].[dbo].[AccessLog](AccessDt) 
     WITH(
     ONLINE=
     ON)
    
   
 

Create index注意n如果是大表建立索引，切记加上ONLINE参数

Create index注意n如果是大表建立索引，切记加上ONLINE参数

这几天在做数据库的优化，有个2亿记录的表，发现需要添加一个联合索引，结果就采用普通的create index index_name on tablename (entp_id,sell_date),结果悲剧了，把所有的DML语句都阻塞了，导致系统不能正常使用，还好是晚上10点，用户不是非常多，1个小时候，索引结束，阻塞解决；

上网查了一下，如果加上 online参数后，就可以在线做索引，而不需要阻塞所有的DML语句，血的教训，拿出来与各位共勉，具体online与不加online区别如下：

1. DML操作对create index 的影响。 如果在create的时候，有其他的进程在对这个index 所对应的数据进行DML操作，create会受影响:

 

   

    

   
   

    

     SQL> 
     create table test (id number, name varchar2(20));
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     Table created.
    
   
 

然后重新开一个session:

 

   

    

   
   

    

     SQL> insert into test values (
     1,
     'lms');
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     1 row created.
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     <no commit>
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     SQL> create 
     index t1 
     on test(id);
    
   

   

    

   
   

    

     create 
     index t1 
     on test(id)
    
   

   

    

   
   

    

     *
    
   

   

    

   
   

    

     ERROR at line 
     1:
    
   

   

    

   
   

    

     ORA-
     00054: resource busy 
     and acquire 
     with NOWAIT specified
    
   
 

2. 加online这个参数，这个参数加上以后，除了create过程中index 保持online状态，Oracle还会在create index之前等待所有DML操作结束，然后得到DDL锁，开始create.

 

   

    

   
   

    

     SQL> create 
     index t1 
     on test(id) online;
    
   

   

    

   
   

    

     <hold before commit>
    
   

   

    

   
   

    

     <after commit>
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     SQL> commit;
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     Commit complete.
    
   

   

    

   
   

    
 
    
   

   

    

   
   

    

     Index altered.
    
   
 

如果不commit,上面的操作就会一直hold。

所以以后create索引和rebuild索引的时候最好加上online。