专栏首页java初学数据库索引

数据库索引

  在数据量非常大的情况下,在数据库中加入索引能够提升数据库查找的性能,常见的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 条评论
登录 后参与评论

相关文章

  • 数据库索引(结合B-树和B+树)

    Mister24
  • java — 重载和覆盖

    Mister24
  • java线程安全— synchronized和volatile

    Mister24
  • mysql索引优化要点

    槽痞
  • Oracle索引种类介绍

    逻辑上: Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Funct...

    葆宁
  • MySQL的干货你了解吗?

    想进大厂,mysql不会那可不行,来接受mysql面试挑战吧,看看你能坚持到哪里?

    故里
  • 高性能MySQL第五章 读书笔记

    用户7962184
  • 看了这篇MySQL,开发功力又升级

    大家好,我是小菜,一个渴望在互联网行业做到蔡不菜的小菜。可柔可刚,点赞则柔,白嫖则刚! 死鬼~看完记得给我来个三连哦!

    蔡不菜丶
  • MySQL系列 | 索引数据结构大全

    对于二叉树而言,每个节点只能有两个子节点,如果是一颗单边二叉树,查询某个节点的次数与节点所处的高度相同,时间复杂度为 O(n);如果是一颗平衡二叉树,查找效率高...

    Tinywan
  • MySql学习笔记(二)- 索引的设计和使用

    作为开发人员,数据库的索引是我们再熟悉不过的了。那么实话真的会了吗,在项目开发中随便定义一个int、varchar后边跟个primary key或者加个inde...

    程序员_备忘录

扫码关注云+社区

领取腾讯云代金券