Mysql索引

简介

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

好处

打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

缺点

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

类别

  • 普通索引index :加速查找

  • 唯一索引

  1. 主键索引:primary key :加速查找+约束(不为空且唯一)

  2. 唯一索引:unique:加速查找+约束 (唯一)

  3. 联合索引

    1. primary key(id,name):联合主键索引
    2. unique(id,name):联合唯一索引
    3. index(id,name):联合普通索引

4.全文索引fulltext :用于搜索很长一篇文章的时候,效果最好。

创建

普通索引
方法一:创建表的时候同时创建索引
CREATE TABLE test_table2
    -> (
    -> id         INT NOT NULL  PRIMARY KEY AUTO_INCREMENT,
    -> firstname   CHAR(100) NOT NULL,
    -> middlename CHAR(100) NOT NULL,
    -> lastname   CHAR(100) NOT NULL,
    -> birth      DATE NOT NULL,
    -> title       CHAR(100) NULL
    -> INDEX index_name (firstname(length))
    -> ) ENGINE=MyISAM CHARSET=utf8

方法二:在已存在的表上直接创建索引
CREATE INDEX index_name ON table(column(length))

方法三:表已存在时修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

删除索引
DROP INDEX index_name ON table
唯一索引

唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值

第一种方法: 创建表的时候同时创建索引
CREATE TABLE `table` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT ,
    -> `title` char(255) NOT NULL ,
    -> `content` text  NULL ,
    -> `time` int(10) NULL DEFAULT NULL ,
    -> PRIMARY KEY (`id`),
    -> [UNIQUE | PRIMARY KEY ] s2 (title(length)) CHARSET=utf8;

创建唯一索引
CREATE [ UNIQUE | PRIMARY KEY ] INDEX indexName ON table(column(length))
修改表结构
ALTER TABLE table_name ADD [ UNIQUE | PRIMARY KEY ] indexName ON (column(length))
全文索引(FULLTEXT)

FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

第一种方法: 创建表的时候添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255)  NOT NULL ,
`content` text NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
) CHARSET=utf8;
第二种: 修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
第三种: 直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)
组合索引

格式是:

CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);

如果你建立了 组合索引(nickname_account_createdTime_Index) 那么他实际包含的是3个索引 (nickname) (nickname,account)(nickname,account,created_time)
在使用查询的时候遵循mysql组合索引的”最左前缀”,下面我们来分析一下 什么是最左前缀:及索引where时的条件要按照建立索引的时候字段的排序方式

  1. 不按索引最左列开始查询(多列索引) 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where c2 = aaa and c3=sss 不能使用索引
    1. 查询中某个列有范围查询,则其右边的所有列都无法使用查询(多列查询)
      Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询
    2. 不能跳过某个字段来进行查询,这样利用不到索引,比如我的sql 是
      select * from award where nickname > ‘rSUQFzpkDz3R’ and account = ‘DYxJoqZq2rd7’ and created_time = 1449567822; 他也用不了
善用帮助文档
help create
help create index

查看表创建的索引

查看索引

show index from tablename;
  • Table
    表的名称。
  • Non_unique
    如果索引不能包括重复词,则为0。如果可以,则为1。
  • Key_name
    索引的名称。
  • Seq_in_index
    索引中的列序列号,从1开始。
  • Column_name
    列名称。
  • Collation
    列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。
  • Cardinality
    索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。
  • Sub_part
    如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
  • Packed
    指示关键字如何被压缩。如果没有被压缩,则为NULL。
  • Null
    如果列含有NULL,则含有YES。如果没有,则该列含有NO。
  • Index_type
    用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。
文章目录
  1. 1. 简介
    1. 1.1. 好处
    2. 1.2. 缺点
  2. 2. 类别
  3. 3. 创建
    1. 3.1. 普通索引
    2. 3.2. 唯一索引
    3. 3.3. 全文索引(FULLTEXT)
    4. 3.4. 组合索引
    5. 3.5. 善用帮助文档
  4. 4. 查看表创建的索引