vlambda博客
学习文章列表

  
    
    
  

   
     
     
   
 
    
 
     
 
      
 
       
 
        
 
         
 
          
 
           
 
            
内容目录
 
            
一、索引介绍二、三种创建索引的方法1、用 CREATE INDEX 语句2、使用 CREATE TABLE 语句3、 使用 ALTER TABLE 语句三、创建索引实例1、mysql索引类型normal，unique，full text的区别是什么？2、在实际操作过程中，应该选取表中哪些字段作为索引？3、索引之间的区别
 
            
一、索引介绍
 
            
  索引（Index）是帮助MySQL高效获取数据的数据结构；
 
            
  在 MySQL 中，通常有以下两种方式访问数据库表的行数据：
 
            
 
             
1) 顺序访问
顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。
2) 索引访问
索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。
 
            
 
            
  例如，在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。
 
            
二、三种创建索引的方法
 
            
1、用 CREATE INDEX 语句
 
            
  可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。
 
            
语法格式：
CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
# 语法说明如下：
<索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
<表名>：指定要创建索引的表名。

<列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 
WHERE 子句里经常出现的列作为索引列。

<长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索
引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大
上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索
引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。

ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。
 
            
2、使用 CREATE TABLE 语句
 
            
  索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。
  在使用 CREATE TABLE 语句定义列选项的时候，可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个 PRIMARY KRY(<列名>，…) 子句的方式来实现。
 
            
CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的主键。

KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的索引。

UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。

FOREIGN KEY <索引名> <列名>
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的外键。
 
            
3、 使用 ALTER TABLE 语句
 
            
  CREATE INDEX 语句可以在一个已有的表上创建索引，ALTER TABLE 语句也可以在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项。
 
            
ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加索引。

ADD PRIMARY KEY [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加主键。

ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。

ADD FOREIGN KEY [<索引名>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加外键。
 
            
三、创建索引实例
 
            
1、mysql索引类型normal，unique，full text的区别是什么？
 
            
 
             
 
              
normal：表示普通索引
 
              
unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
 
              
full textl: 表示 全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
  创建一个表 tb_stu_info，在该表的 height 字段创建一般索引。输入的 SQL 语句和执行过程如下所示。
 
             
 
            
 
            
2、在实际操作过程中，应该选取表中哪些字段作为索引？
 
            
  为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则：
 
            
 
             
1．选择唯一性索引
2．为经常需要排序、分组和联合操作的字段建立索引
3．为常作为查询条件的字段建立索引
4．限制索引的数目
5．尽量使用数据量少的索引
6．尽量使用前缀来索引
7．删除不再使用或者很少使用的索引
 
            
 
            
3、索引之间的区别
 
            
  索引我们分为四类：单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引。
 
            
 
             
 
              
单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。这里不要搞混淆了。
 
              
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
 
              
唯一索引：索引列中的值必须是唯一的，但是允许为空值，
 
              
主键索引：是一种特殊的唯一索引，不允许有空值。
 
              
组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。这个如果还不明白，等后面举例讲解时在细说
 
              
全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个靓仔，靓女 …" 通过靓仔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思，如果感兴趣进一步深入使用它，那么看下面测试该索引时，会给出一个博文，供大家参考。
 
              
空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。
 
             
 
             
4、操作索引
 
             
  
 
            
 
            
--创建普通索引
CREATE INDEX index_name ON table_name(col_name);

--创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

--创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

--创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);


2. 通过修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name);


3. 创建表时直接指定索引
CREATE TABLE table_name (
    ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);


4. 删除索引
--直接删除索引
DROP INDEX index_name ON table_name;
--修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;


5. 其它相关命令

--查看表结构
desc table_name;
--查看生成表的SQL
show create table table_name;
--查看索引
show index from  table_name;
--查看执行时间
set profiling = 1;
SQL...
show profiles;