mysql索引

索引是创建在表上的，是对数据库中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。

索引的分类

普通索引
　在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型上，其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后，查询时可以通过该索引进行查询。

　　唯一性索引
　　　　使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。通过唯一性索引，可以更快速的确定某条记录。主键就是一种特殊唯一性索引。

　　全文索引
　　　　使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在char、Varchar或text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

　　单列索引
　　　　在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

　　多列索引
　　　　多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的id、name和sex字段上建立一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。

索引的设计原则

　　1.选择唯一性索引

　　唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。

　　2.为经常需要排序、分组和联合操作的字段建立索引

　　经常需要排序、分组和联合操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效的避免排序操作。

 select * from bqk order by qc_bh（建立qc_bh索引）

　　3.为常作为查询条件的字段建立索引

　　如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

　　4.限制索引的数目

　　索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

　　5.尽量使用数据量少的索引

　　如果索引的值很长，那么查询的速度会受到影响。例如，对一个char（100）类型的字段进行全文检索需要的时间肯定要比char（10）类型的字段需要的时间要多

对于CHAR或VARCHAR类型的字段，还可以只使用字段内容前面的一部分来创建索引，只需要在对应的字段名称后面加上形如(length)的指令即可，表示只需要使用字段内容前面的length个字符来创建索引。在这里，以User表的username字段(类型为VARCHAR(50))为例，使用username字段的6个字符前缀来创建索引。

 CREATE INDEX idx_user_username ON user (username(6));

　　由于多数字段的前6个字符通常不同，所以此索引不会比使用字段的全部内容创建的索引速度慢很多。另外，使用字段的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

在MySQL中，前缀长度最大值为255字节。对于存储引擎为MyISAM或InnoDB的数据表，前缀最长为1000字节。

必须注意的是，在MySQL中，对于TEXT和BLOB这种大数据类型的字段，必须给出前缀长度(length)才能成功创建索引。

6.尽量使用前缀来索引

　 如果索引字段的值很长最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

　 7.删除不在使用或者很少使用的索引。

　　表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将他们删除，从而减少索引对更新操作的影响。

创建索引

创建表的时候创建索引

　　　创建表的时候可以直接创建索引，这种方式最简单、方便。其基本形式如下：

　　　　CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
                    [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
                    [索引名](字段名1 [(长度)] [ASC | DESC])
　　　　);

UNIQUE：可选。表示索引为唯一性索引。

FULLTEXT；可选。表示索引为全文索引。

SPATIAL：可选。表示索引为空间索引。

INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以，作用是一样的。

索引名：可选。给创建的索引取一个新名称。

字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。

长度：可选。指索引的长度，必须是字符串类型才可以使用。

ASC：可选。表示升序排列。

DESC：可选。表示降序排列。

创建普通索引

create table t_score（
     score_id int not null primary key,
     stu_id int,
     score float,
     index(score_id)
）；

唯一索引/全文索引

create table t_score(
    score_id int unique,
    stu_id int,
    score float,
    info  varchar(20),
    unique index index_id(score_id),     //唯一索引
    fulltext index indexInfo(info),      //全文索引
    index multi_index(score_id,stu_id)   //多列索引
);

在已经创建表上创建索引

在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：

create [unique|fulltext|spatial] index 索引名 on 表名（属性名 [长度][asc|desc]）；

  create index index_id on t_score(score_id);
  create unique index index_id on t_score(score_id);
  create fulltext index index_info on t_score(info);
  create index multi_index on t_score(score_id,stu_id);

用alter table语句来创建索引

　　在已经存在的表上，可以通过alter table 语句直接为表上的一个或几个字段创建索引。其基本形式如下：

　　alter table 表名 add [unique |fullText |spatial] index 索引名（属性名[（长度）][asc|desc]）;

 alter table t_score add [unique } fulltext ]index index_id(score_id);

删除索引

　　对于已经存在的索引，可以通过drop 语句来删除索引。基本形式如下：

　　drop index 索引名 on 表名

 drop index index_id on t_score;

查看索引
在MySQL中，要查看某个数据库表中的索引也非常简单，只需要使用以下两个命令中的任意一种即可。

--如果查看索引前，没有使用use db_name等命令指定具体的数据库，则必须加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
--如果查看索引前，没有使用use db_name等命令指定具体的数据库，则必须加上db_name.前缀
SHOW INDEX FROM [db_name.]table_name

在MySQL中，只有当数据表的存储引擎为MyISAM, InnoDB或BDB类型时，才能向具有NULL值的列或者数据类型为TEXT或BLOB的列添加索引。

不使用索引

表记录太少

数据重复且分布平均的表字段：假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

经常增删改的表不需要创建索引

频发更新的字段不适合创建索引：索引字段频繁更新，或者表数据物理删除容易造成索引失效

索引不会包含有NULL值的列

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

排序的索引问题

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

Hash索引与B-Tree索引的区别

　　Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

　　由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以，只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

　　（2）Hash 索引无法被用来避免数据的排序操作。由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

　　（3）Hash 索引不能利用部分索引键查询。对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

　　（4）Hash 索引在任何时候都不能避免表扫描。前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

　　（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

posted on 2018-10-24 22:23 溪水静幽阅读(266) 评论(0) 收藏举报