数据库索引

  在数据量非常大的情况下,在数据库中加入索引能够提升数据库查找的性能,常见的mysql索引分为以下几类:

①普通索引

可以直接创建索引:CREATE INDEX indexName ON table(column(length))  

  如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length

可以通过修改表结构来创建索引:ALTER tableADD INDEX indexName ON (column(length))

可以在创建表的同时创建索引:

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

删除索引:DROP INDEX indexName ON table

②唯一索引

与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。

直接创建唯一索引:CREATE UNIQUE INDEX indexName ON table(column(length))

修改表结构的时候创建唯一索引:ALTER table ADD UNIQUE indexName ON (column(length))

创建表的时候指定索引:

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

③主键索引

  主键索引是一种特殊的唯一索引,不允许有空值,一般都是在建表的时候就创建主键。

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );  

④全文索引

  mysql从3.23.23版开始支持全文索引和全文检索,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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,       `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,       `time` int(10) NULL DEFAULT NULL ,       PRIMARY KEY (`id`),       FULLTEXT (content)       ); 修改表结构添加全文索引:ALTER TABLE article ADD FULLTEXT index_content(content) 直接创建索引: CREATE FULLTEXT INDEX index_content ON article(content)

⑤组合索引(单列索引、多列索引)

http://greatwqs.iteye.com/blog/1897118

这一篇文章对这一部分内容作了详细的介绍,注意多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。

 实例:现在我们想查出满足以下条件的用户id:       mysql>SELECT `uid` FROM people WHERE lname`='Liu'  AND `fname`='Zhiqun' AND `age`=26 ; 因为我们不想扫描整表,故考虑用索引。        1、单列索引:        ALTER TABLE people ADD INDEX lname (lname);      将lname列建索引,这样就把范围限制在lname='Liu'的结果集1上,之后扫描结果集1,产生满足fname='Zhiqun'的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。       由于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引,再创建fname或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。      2、多列索引:      ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

     为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B树格式保存,所以我们不用扫描任何记录,即可得到最终结果。      注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。      3.最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。      注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏极客生活

mysql 数据去重

从excel中导入了一部分数据到mysql中,有很多数据是重复的,而且没有主键,需要按照其中已经存在某一列对数据进行去重。

14520
来自专栏吴柯的运维笔记

MySQL索引类型及用法

2017年9月13日 12:55 索引类型: ? index索引: ? 在已有表中创建index索引: create index 索引名 on 表名(字段名)...

31780
来自专栏www.96php.cn

mysql 快速生成百万条测试数据

1、生成思路 利用mysql内存表插入速度快的特点,先利用函数和存储过程在内存表中生成数据,然后再从内存表插入普通表中 2、创建内存表及普通表 CREATE T...

700120
来自专栏开源优测

SQL常用的基础语法

SQL常用的语法 本文主要分享SQL日常使用过程中常用的语法样式。 在sql语句中,主要的关键词有SELECT、INSERT、UPDATE、DELETE、ALE...

32380
来自专栏ml

mysql的小知识点(关于数据库的导入导出 对于windows)

      对于,一个存在的数据,我们该如何去打包成.sql属性的文件呢?       直接进行这两条语句: 1 D:\Program Files\MySQL\...

51480
来自专栏Python研发

MySql[二]

视图就是一个虚拟表(非真实存在),其本质是[根据SQL语句获取动态的数据集,并为其命名],用户使用时只需要使用[名称]即可获取结果集并可以将其当做代表来使用·

17840
来自专栏抠抠空间

MySQL 如何删除有外键约束的表数据

今天删除数据库中数据,提示因为设置了foreign key,无法修改删除 可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况。

21230
来自专栏测试开发架构之路

MySQL/Oracle索引的创建与使用

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

9220
来自专栏calmound

操作数据表

打开数据库    use 数据库 ---- use mydata 查看数据库    show databases 显示当前用户打开的数据库   SELECT ...

32460
来自专栏jeremy的技术点滴

保存mysql InnoDB的auto_increment值另类方案

28850

扫码关注云+社区

领取腾讯云代金券