一、在创建数据表的时候创建索引
 
 
 
在MySQL中创建表的时候，可以直接创建索引。基本的语法格式如下：
 CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
 [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
 [索引名](字段名1 [(长度)] [ASC | DESC])
 );
 
 
 
  
UNIQUE：可选。表示索引为唯一性索引。
 FULLTEXT；可选。表示索引为全文索引。
 SPATIAL：可选。表示索引为空间索引。
 INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
 索引名：可选。给创建的索引取一个新名称。
 字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
 长度：可选。指索引的长度，必须是字符串类型才可以使用。
 ASC：可选。表示升序排列。
 DESC：可选。表示降序排列。
 
 
 
 
MySQL创建普通索引：
 
CREATE TABLE index1(id INT,
    name VARCHAR(20),
    sex BOOLEAN,
    INDEX(id)
);
 
MySQL创建唯一性索引：
 
CREATE TABLE index2(id INT UNIQUE,
                    name VARCHAR(20)
);
 
MySQL创建全文索引:
 全文索引使用FULLTEXT参数，并且只能在CHAR、VARCHAR或TEXT类型的字段上创建。
 
CREATE TABLE index3(id INT,
                    info VARCHAR(20),
                    FULLTEXT INDEX index3_info(info)
)ENGINE=MyISAM;
 
MySQL创建单列索引:
 单列索引是在数据表的单个字段上创建的索引。一个表中可以创建多个单列索引。唯一性索引和普通索引等都为单列索引。
 
CREATE TABLE index4(id INT,
                    subject VARCHAR(30),
                    INDEX index4_st(subject(10))
);
 
注意：subject字段长度为30，而index4_st设置的索引长度只有10，这样做是为了提高查询速度。对于字符型的数据，可以不用查询全部信息，而只查询它前面的若干字符信息。
 
MySQL创建多列索引:
 创建多列索引是在表的多个字段上创建一个索引。
 
CREATE TABLE index5(id INT,
                    name VARCHAR(20),
                    sex CHAR(4),
                    INDEX index5_ns(name,sex)
);
 
提示：使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。因此，在优化查询速度时，可以考虑优化多列索引。
 
MySQL创建空间索引：
 使用SPATIAL参数能够创建空间索引。创建空间索引时，表的存储引擎必须是MyISAM类型。而且，索引字段必须有非空约束。
 
CREATE TABLE index6(id INT,
                    space GEOMETRY NOT NULL,
                    SPATIAL INDEX index6_sp(space)
)ENGINE=MyISAM;
 
注意：space字段是非空的，而且数据类型是GEOMETRY类型。这个类型是空间数据类型。空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。这些空间数据类型平时很少用到
 
二、表创建完后创建索引
 
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
 [USING index_type]
 ON table_name (index_col_name,...)
 
三、删除索引
 
ALTER TABLE table_name DROP INDEX index_name;

索引：是对数据库表中一列或多列的值进行排序的一种结构，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。当然索引也有好处就是查询速度快，它利还是大于弊的所以请慎重使用索引 
 常用的索引类型： 
 普通索引：没有任何限制 create index index_name on table（column（length）） 
 唯一索引：索引列的值必须唯一，但允许为空值：不允许任何两行具有相同索引值的索引。 
 主键索引：主键，一个表，只能有一个逐渐，不允许为空 
 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，组合索引遵循最左前缀集合。 
 一、创建索引要注意哪些？ 
 表的主键、外键必须有索引；数据量超过300的表应该有索引；经常查询的表的字段应该建立索引；经常出现在where子句中的字段，应该建立索引；在选择性高的字段上建索引；在小字段上建索引；频繁进行数据更新（增，删，改）的表，尽量少建索引； 
 二、一般选择在这样的列上创建索引： 
 在经常需要查询的表的字段上创建索引，可以加快搜索的速度；在经常使用连接的列上创建索引，这些列主要是外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上添加索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序速度；在经常使用where子句的列上创建索引，加快条件的判断速度；经常出现在order by 、group by、distinct后面的字段创建索引
 
三、一般不选择具有这些特点的列上创建索引
 
对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查 询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求；
不要在有大量相同取值的字段上，建立索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加 快检索速度；
对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少；
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
 
四、创建索引需要注意的地方 
 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
 
避免在取值朝一个方向增长的字段（例如：日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面。由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。

对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
 
删除不再使用，或者很少被使用的索引。表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。 
 哪些情况下索引会失效？
 
如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 
 对于多列索引（组合索引）不是使用的第一部分，则会失效 
 Like查询以%开头会失效（aa%不会失效） 
 如果列类型是字符串，则一定要在条件语句中讲数据用引号引起来，否则索引会失效 
 如果mysql估计是用全表扫描要比使用索引块，则不使用索引

数据库索引
 


 
文章目录
 
数据库索引
定义
优缺点
索引类型
建立普通索引或组合索引
适合建立索引的情况
索引失效的sql
   
  
 

 
定义
 
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
 
本人对于索引理解较浅，如有错误或不足的地方，欢迎指正，共同学习。
 
优缺点
 
优点
 
 
在设计数据库时，通过创建一个惟一的索引，能够在索引和信息之间形成一对一的映射式的对应关系，增加数据的惟一性特点。
 
 
能提高数据的搜索及检索速度，符合数据库建立的初衷。
 
 
能够加快表与表之间的连接速度，这对于提高数据的参考完整性方面具有重要作用。
 
 
在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。
 
 
建立索引之后，在信息查询过程中可以使用优化隐藏器，这对于提高整个信息检索系统的性能具有重要意义。
 
缺点
 
 
在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
 
 
在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间，如果有必要建立起聚簇索引，所占用的空间还将进一步的增加
 
 
在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。
 
 
索引类型
 
根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。
 
提示：尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。
 
唯一索引
 
UNIQUE
create unique index 索引名 on 表名（表中的列[(length)]）
alter table 表名 add UNIQUE 索引名 (表中的列[(length)])
 
唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。
 
主键索引
 
PRIMARY KEY  -- 建表时自接指定
alert table 表名 add primary key (表中的列[(length)])
 
数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
 
聚集索引
 
在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别，如字典默认按字母顺序排序（物理顺序），读者如知道某个字的读音可根据字母顺序快速定位（索引顺序与物理顺序相同）。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。
 
建立普通索引或组合索引
 
create index 索引名 on 表名(列名);
create index 索引名 on 表名(列名1,列名2，..); -- 组合索引
 
组合索引 最左前缀 原则
 
个人理解 例如 ： create index index_abc on TEST_TAB(a,b,c); 相当于建立了 a, ab, abc 三组索引。
 
只要某查询条件中包含复合索引中的第一个列，该查询就会走索引，如果不包含，就不会走索引
 
有博主的实验 https://blog.csdn.net/tw7752/article/details/44595281
 
适合建立索引的情况
 
经常作为查询条件的列 （where）
经常用于表连接的列，如外键 （join）
经常用于排序的列 （order）
 
索引失效的sql
 
 
组合索引使用 or 索引失效 如 a=1 or b=2 or c=3
 
 
索引条件为 is null / is not null 索引失效(看清况，测试过 null 比较多时， not null 会走索引)
 
 
索引条件 like ‘%xxx’, 索引失效； like ‘xxxx%’ 索引生效
 
 
索引列参加计算 如 t.score/10 > 10 失效， 应改成 t.score > 10*10
 
 
索引列不要使用NOT （ != 、 <> ）如 t.score! = 10 失效，改成：t.score > 10 or t.score < 10
 
 
索引列上发生类型转换, 例如 VARCHAR2 类型的索引列 ，写成 where id = 1 ,应该 改成 where id = ‘1’ ( oracle实验)
 
 
最好是 看一下sql的执行计划，看看是否走了索引。
 
Oracle查看sql执行计划
 
explain plan for  
select xxx from tablename where xxx ;

select * from table(dbms_xplan.display); 
 
索引的原理 请看大神博客 https://blog.csdn.net/sinat_30186009/article/details/52169057
 
组合索引设计 https://blog.csdn.net/bless2015/article/details/84035845

索引的其中一个缺点就是需要占用磁盘空间。
 
这个缺点是其他 优点的基础，因为任何一种机制，不可能只有优点，而没有缺点，正因为创建索引，就是把排序后组织成B树的数据，放到磁盘上，才有了索引，才有可能运用索引的各种优势。
 
既然是索引，就一定会占用空间，那么这里的主要问题就成了如何减少占用的空间？
 

 
 
（1）减少字段个数
 
如果索引中包含了很多字段，比如：A表，一共有20个字段，而创建的索引中包含了10个字段，那么会导致索引占用的空间，接近于表的空间，整个索引的层数会增加，同样找一条数据，需要更多IO，速度更慢。
 
是否索引中真的需要包含那么多的字段？
 
可以从需求角度分析，本来想让索引覆盖整个查询，但实际上真正起到过滤作用的字段，也就是放到where条件中的只有2个，那么，这个索引最好就是这2个字段，其他字段，可以通过回表来获取。
 

 
 
（2）占用字节数少的字段
 
对于where 条件中的字段，不受这个限制，就算占用字节数较多，也得放到索引中。
 
但是对于 select count(*) from tb 这种查询，可以在占用字节数最少的字段上创建索引。
 
比如：A表里有很多字段，其中 ID是numeric(10)，flag是 tinyint，biz_date是datetime，name是varchar（30），memo是varchar（500）。
 
那么可以在flag字段上创建索引，可以显著加快select count（*） from tb查询的速度，因为flag字段的类型是tinyint，只占用1个字节，所以用这个字段创建的索引，体积会很小。