mysql--索引

https://www.cnblogs.com/luyucheng/p/6289714.html

https://www.cnblogs.com/zhangqie/p/8416164.html

创建索引:

  • CREATE INDEX index_name ON table_name (column_list)
  • CREATE UNIQUE INDEX index_name ON table_name (column_list)

 

  • ALTER TABLE table_name ADD INDEX index_name (column_list)
  • ALTER TABLE table_name ADD UNIQUE (column_list)
  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)

 

  • DROP INDEX index_name ON talbe_name
  • ALTER TABLE table_name DROP INDEX index_name
  • ALTER TABLE table_name DROP PRIMARY KEY

 

索引底层数据结构:

  • MyISAM

MyISAM引擎使用B+ Tree作为索引结构,叶节点存放的是数据记录的地址。

MyISAM引擎的辅助索引(二级索引)和主索引在结构上没有区别,只是辅助索引的key可以重复,叶节点 上存放的也是数据记录的地址。

MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。

 

  • InnoDB

InnoDB中表数据本身就是按B+ Tree组织的一个索引结构,叶节点存放的就不是数据记录的地址,而是完整的数据记录。所以InnoDB这种存储方式,又称为聚集索引,使得按主键的搜索十分高效,但二级索引搜索需要检索两遍索引:首先二级索引获得主键,然后用主键到主索引中检索到数据记录。

因为主键是InnoDB表记录的”逻辑地址“,所以InnoDB要求表必须有主键,MyISAM可以没有。

 

类型:

1.普通索引

是最基本的索引,它没有任何限制。它有以下几种创建方式:

(1)直接创建索引

CREATE INDEX index_name ON table(column(length))

(2)修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

(3)创建表的时候同时创建索引

CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER NOT NULL , `content` text CHARACTER NULL , `time` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), INDEX index_name (title(length)) )

(4)删除索引

DROP INDEX index_name ON table

 

2.唯一索引

与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

(1)创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

(2)修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

(3)创建表的时候直接指定

CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER NOT NULL , `content` text CHARACTER NULL , `time` int(10) NULL DEFAULT NULL , UNIQUE indexName (title(length)) );

 

3.主键索引

是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引:

CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) NOT NULL , PRIMARY KEY (`id`) );

 

主键索引与唯一索引的区别

  • 1.本质:主键为一种约束,唯一索引为一种索引,是表的冗余数据结构,本质上就不同。
  • 2.个数:主键在表中只能有一个,唯一索引可以有多个。
  • 3.主键:主键创建后一定包含唯一性索引,而唯一索引不一定就是主键。
  • 4.null:主键不能为null,唯一索引可以为null.
  • 5.引用为外键:主键可以被其它表引用为外键,唯一索引不能。
  • 6.键:主键和索引都是键,主键是逻辑键,索引为物理键,即主键不实际存在。

 

 

4.组合索引

指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

 

5.全文索引

主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。

(1)创建表的适合添加全文索引

CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER NOT NULL , `content` text CHARACTER NULL , `time` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), FULLTEXT (content) );

(2)修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

(3)直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)

 

四、缺点

  • 1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete。因为更新表时,不仅要保存数据,还要保存一下索引文件。
  • 2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会增长很快。
  • 索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

 

五、注意事项

使用索引时,有以下一些技巧和注意事项:

  • 1.索引不会包含有null值的列:只要列中包含有null值都将不会被包含在索引中,复合索引中只要有一列含有null值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
  • 2.使用短索引:对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个char(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
  • 3.索引列排序:查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
  • 4.like语句操作:一般情况下不推荐使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
  • 5.不要在列上进行运算:这将导致索引失效而进行全表扫描,例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

  • 6.不使用not in和<>操作

 

六、适合加索引的

  1. 表的主键、外键必须有索引;
  2. 数据量超过300的表应该有索引;
  3. 经常与其他表进行连接的表,在连接字段上应该建立索引;
  4. 经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
  5. 索引应该建在选择性高的字段上;
  6. 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
  7. 频繁进行数据操作的表,不要建立太多的索引;

 

复合索引 or 单字段索引

  • 复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
  1. A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
  2. B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
  3. C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
  4. D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
  5. E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;

 

 

七、不适合加索引的

  1. 查询过程中很少用到
  2. 只有很少数据值的列
  3. 定义为image,text,bit数据类型的列
  4. 当修改性能远大于检查性能(改的多)

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值