-
2018-05-15 18:53:58
在创建数据表时创建索引的基本语法结构:
CREATE TABLE table_name(
属性名 数据类型[约束条件],
……
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[ 别名 ] ( 属性名1 [( 长度 )] [ ASC | DESC )
);属性值的含义如下:
a. UNIQUE: 可选参数,表示索引为唯一索引。
b. FULLTEXT: 可选参数,表示索引为全文索引。
c. SPATIAL: 可选参数,表示索引为空间索引。
d. INDEX 和 KEY 参数用于指定字段为索引的,用户在选择时,只需要选择其中的一种即可。
e. "别名" : 为可选参数,其作用是给创建的索引取新名称。
d. 属性名1: 指索引对应的字段名称,该字段必须被预先定义。
f. 长度: 可选参数,其指索引的长度,必须是字符串类型才可以使用。
g. ASC/DESC: 可选参数,ASC 表示升序排列,DESC 表示降序排列。
1. 普通索引创建
创建普通索引,即不添加 UNIQUE、FULLTEXT 等任何参数。
【例】创建表名为 score 的数据表,并在该表的 id 字段上建立索引,SQL 语句如下:
mysql> CREATE table score( -> id int(11) AUTO_INCREMENT primary key not null, -> name varchar(50) not null, -> math int(5) not null, -> English int (5) not null, -> Chinese int (5) not null, -> index(id) -> );
2. 创建唯一索引
创建唯一索引时,使用 UNIQUE 参数进行约束。
【例】创建表名为 address 的数据表,并在该表的 id 字段上建立唯一索引,SQL 语句如下:
mysql> CREATE table address( -> id int(11) auto_increment primary key not null, -> name varchar(50), -> address varchar(200), -> UNIQUE INDEX address(id ASC) -> );
3. 创建全文索引
全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。
【例】创建表名为 cards 的数据表,并在该表的 name 字段上建立全文索引,SQL 语句如下:
mysql> create table cards( -> id int(11) auto_increment primary key not null, -> name varchar(50), -> number bigint(11), -> info varchar(50), -> FULLTEXT KEY cards_number(name) -> );
4. 创建单列索引
创建单列索引,即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数,用户在建立时只需要指定单列字段名,即可创建单列索引。
【例】创建名称为 telephone 的数据表,并指定在 tel 字段上建立名称为 tel_num 的单列索引,SQL 语句如下:
mysql> create table telephone( -> id int(11) primary key auto_increment not null, -> name varchar(50) not null, -> tel varchar(50) not null, -> index tel_num(tel(20)) -> );
5. 创建多列索引
创建多列索引即指定表的多个字段即可实现。
【例】创建名称为 information 的数据表,并指定 name 和 sex 为 多列索引,SQL 语句如下:
需要注意的是,在多列索引中,只有查询条件中使用了这些字段中的第一个字段(即上面示例中的 name 字段),索引才会被使用。mysql> create table information( -> inf_id int(11) auto_increment primary key not null, -> name varchar(50) not null, -> sex varchar(5) not null, -> birthday varchar(50) not null, -> index info(name,sex) -> );
触发多列索引的条件是用户必须使用索引的第一字段,如果没有用到第一字段,则索引不起任何作用,用户想要优化查询速度,可以应用该类索引形式。
6. 创建空间索引
创建空间索引时,需要设置 SPATIAL 参数。同样,必须说明的是,只有 MyISAM 类型表支持该类型索引。而且,索引字段必须有非空约束。
【例】创建一个名称为 list 的数据表,并创建一个名为 listinfo 的空间索引,SQL语句如下:
goods 字段上已经建立名称为 listinfo 的空间索引,其中 goods 字段必须不能为空,且数据类型是 GEOMETRY,该类型是空间数据类型。空间类型不能用其他类型代替,否则在生成空间素引时会产生错误且不能正常创建该类型索引。mysql> create table list( -> id int(11) primary key auto_increment not null, -> goods geometry not null, -> SPATIAL INDEX listinfo(goods) -> )engine=MyISAM;
空间类型除了上述示例中提到的 GEOMETRY 类型外,还包括如 POINT、LINESTRING、POLYGON 等类型,这些空间教据类型在平常的操作中很少被用到。
【注】参考于清华大学出版社《MySQL数据库应用案例课堂》2016年1月第1版
更多相关内容 -
为数据表创建索引的目的是什么
2021-01-29 18:21:30一、SQL创建索引的目的如下:1、通过唯一性索引(unique)可确保数据的唯一性;2、加快数据的检索速度;3、加快表之间的连接;4、减少分组和排序时间;5、使用优化隐藏器提高系统性能。二、创建SQL索引的语法:CREATE ...一、SQL创建索引的目的如下:
1、通过唯一性索引(unique)可确保数据的唯一性;
2、加快数据的检索速度;
3、加快表之间的连接;
4、减少分组和排序时间;
5、使用优化隐藏器提高系统性能。
二、创建SQL索引的语法:CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED](索引类型) INDEX ON ( [ASC|DESC]
[, [ASC|DESC]...])。
扩展资料:
索引的类别介绍:
1、唯一索引:
唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。
2、主键索引:
数据库表经常有一列或多列组合,其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。
推荐教程: 《mysql教程》
-
千万级数据表如何索引快速查找
2021-11-14 12:50:58Mysql索引数据结构 Mysql索引数据结构索引1.二叉树的优缺点优点缺点2.红黑树的优缺点优点缺点如何去优化?3.B-Tree4.B+TreeB+Tree特点底层为什么查找这么快的原因 1.慢Sql查询:执行时间几秒, 几十秒,怎么去优化...Mysql索引数据结构
1.慢Sql查询:执行时间几秒, 几十秒,怎么去优化????
2.索引,本来需要执行几秒几十秒的查询,加上合适的索引可能几十毫秒就结束了
3.为什么?
4.底层怎么实现的?
索引
索引是帮助MySQL高效获取数据的排好序的数据结构
索引数据结构:
二叉树 红黑树 Hash表 B-Tree
MySQL底层为什么会选择像B-Tree,B+Tree这样的数据结构来存储我们的索引?
MySQL早期版本选择二叉树,红黑树来存储我们的索引,只不过这些数据结构还存在一些小问题。
1.二叉树的优缺点
优点
COL2作为我们的索引,原来需要做6次I/O,现在只需要做3次I/O,性能提升了一倍
缺点
二叉树,插入大的元素总是放在我们的右下角,插小的元素放左下角,
把Col1当做索引时(当列数据是自增的),和全表扫描在性能上边没有太大的差别,而且还额外增加了索引的存储空间2.红黑树的优缺点
HashMap的底层实现就用到了红黑树。
优点
红黑树本质上也是二叉树,但是和二叉树不一样,他是二叉平衡树
它有自我平衡功能,如果一个树,一边比另外一边大的太多,它能够自动平衡,让一边与另外一边相差不要太多比单纯的二叉树查找次数缩短了一半,磁盘的I/O次数减少了一半
缺点
为什么MySQL最终没有选择红黑树呢?
红黑树当数据存储比较大的时候,由于它的树的高度不可控,导致在树的结构遍历元素的时候,如果到了叶子节点,那么需要查找很多次磁盘,性能就会非常低。这是MySQL没有选择红黑树的最主要的原因。
如何去优化?
如果是树进行存储的话,树的高度越小,查找的次数越少,性能效率就会有很大的提升
多路查找
分配索引节点存储的空间的时候,一次给它分配的大一点点,分配多一点点,一个节点可以放更多的索引元素,索引和索引之间还留一点空间做一些分叉,没一个分叉又可以放一点节点。同样存储500万条数据数的高度会更小(横向增多了)
这个优化的结构就是B-Tree
3.B-Tree
MySQL最终并没有用B-Tree,是在B-Tree上对整个数据结构做了一点点优化得到一个B+Tree(B-Tree变种)4.B+Tree
B+Tree是一个什么的结构呢?
它会把整张表的所有的索引元素都放到叶子节点,叶子节点有整张表的所有的索引元素,非叶子节点是从每一个叶子索引节点拿的第一个元素,做冗余的索引,来组织这一颗B+Tree我们期望存储相同的元素,树的高度越小越好,MySQL底层的这个B+Tree存储索引的结构,它的容量大概是多少?
每一个节点默认设置16KB,整个树可以放两千多万条索引元素B+Tree特点
有序性
元素从磁盘读到内存里去,相当于做磁盘I/O,磁盘I/O性能很低
内存中的折半查找是相当快的,与一个磁盘I/O的时间相比可以忽略不计,B+Tree非叶子结点,直接在MySQL初始化的时候,都已经加载到内存中去了,真正查找一个元素的时候,直接在内存中快速定位,也就是说整个过程中我们只需要1次的磁盘I/O,效率相当高。哪怕上千万行的表记录
不是合理的走索引的话,这条SQL语句要执行几十秒(几千万行全部扫描需要几十秒)
合理的走索引性能提升几个数量级(可能扫描一条记录就搞定了,怎么扫的,前边都内存里快速匹配,然后通过树的结构快速定位到某个节点,磁盘上加载1次I/O就结束了,性能相当高)几毫秒,几十毫秒就查找到我们的元素了。底层为什么查找这么快的原因
借助B+Tree结构的巧妙的设计
-
MySQL-创建表时建立索引和在已存在表中添加索引
2019-10-07 16:18:54另一种是使用ALTER TABLE 语句在已存在的表上创建索引,或者使用CREATE_INDEX 在已存在的表上创建索引。 1. 在 创建表时创建【普通】索引 语法格式为: CREATE TABLE Y1 ( COLUMN_NAME DATA_TYPE SCHEME, ...MySQL提供了多种在单列或多列上创建索引的方法:一种是在创建表时指定索引列;另一种是使用ALTER TABLE 语句在已存在的表上创建索引,或者使用CREATE INDEX 在已存在的表上创建索引。
1. 在 创建表时创建【普通】索引
语法格式为:
CREATE TABLE Y1 ( COLUMN_NAME DATA_TYPE SCHEME, [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY ] INDEX_NAME([LENGTH],.....) [ASC | DESC] )
UNIQUE | FULLTEXT | SPATIAL 这三个是可选项,UNIQUE 为唯一索引,FULLTEXT 为全文索引,SPATIAL 为空间索引,其中FULLTEXT 和SPATIAL InnoDB 存储引擎不支持,MyISAM存储引擎支持;INDEX 和 KEY 为同义词,创建索引时使用它们中任何一个都可以,INDEX_NAME 为索引名称,可选参数,如果不指定,缺省(默认值)为COLUMN_NAME ,LENGTH 为可选参数,可以指定索引的长度,需注意的是,只有字符串类型的列才能指定索引长度,ASC 或 DESC 指定升序或者降序的索引值存储。
使用INDEX 创建 SQL:
```sql CREATE TABLE Y1 ( ID INT (10) AUTO_INCREMENT NOT NULL, NAME VARCHAR (20) NOT NULL, SEX INT (1) NOT NULL DEFAULT 0, INDEX IDX (ID) ) --创建普通索引
CREATE TABLE T4 ( ID INT NOT NULL, NAME CHAR(30) NOT NULL, AGE INT NOT NULL, INFO VARCHAR(255), FULLTEXT INDEX FULLTEXTIDX(INFO) ) ENGINE=MyISAM -- FullText 全文索引,需指定存储引擎为MyISAM,MySQL默认存储引擎为InnoDB
CREATE TABLE T5 ( G GEOMETRY NOT NULL, SPATIAL INDEX SPATINDEX (G) ) ENGINE = MYISAM -- SPATIAL 创建空间索引,需指定存储引擎为MyISAM,MySQL默认存储引擎为InnoDB
-- 创建唯一索引 CREATE TABLE Y3( ID INT NOT NULL, NAME CHAR(20), UNIQUE INDEX UNIQUEIDX(ID) )ENGINE = INNODB
-- 创建组合索引 CREATE TABLE Y4( ID INT NOT NULL, NAME CHAR(20), AGE INT NOT NULL, INDEX MULTIIDX(ID,NAME(19),AGE) ) ENGINE= INNODB
使用KEY创建索引 SQL
CREATE TABLE Y2 ( ID INT NOT NULL, NAME VARCHAR (233) NOT NULL, KEY IDX (ID) ) ENGINE = INNODB DEFAULT CHARSET = UTF8 --用KEY创建普通索引
**
2. 在已存在表中添加索引
**
基本语法分两种,第一种语法为2.1:
ALTER TABLE TABLE_NAME ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY ] INDEX_NAME(COLUMN_NAME(LENGTH),.....) [ASC | DESC]
SQL:
ALTER TABLE t1 ADD UNIQUE INDEX ALTERIDX(NAME) -- 使用ALTER TABLE 语句创建索引
2.2:
第二种语法为:CREATE [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY ] INDEX_NAME ON TABLE_NAME(COLUMN_NAME(LENGTH),.....) [ASC | DESC]
SQL :
CREATE UNIQUE INDEX CREIDX ON t4(NAME(20)) -- 使用CREATE INDEX 在已存在表中创建索引
-
关于InnoDB表数据和索引数据的存储
2018-12-21 22:00:51关于InnoDB表数据和索引数据的存储的一些误解,这里是澄清的过程 -
使用Navicat给MySQL数据表创建索引、索引的说明
2020-08-24 23:14:20目录 为表格创建索引 ...创建唯一索引的字段值不能有相同的数据,否则保存不成功;主键id默认就是唯一索引 创建组合索引 选择自己需要的字段 创建全文索引 MySQL>6默认使用innodb,要把存. -
千万级别数据表创建索引
2017-09-25 12:52:10业务背景最近一个开发维护的公众号管理系统用户表(user_info)数据已经达到15,000k了,而此时有一个业务场景需要将公众号的用户信息重新同步一次,且后台原有过针对单个公众号的用户同步,但是已经非常难以使用,... -
Pandas中DataFrame索引、选取数据
2020-03-26 23:32:30上一篇文章总结了Series索引问题。今天这篇来总结一下DataFrame索引问题。 1. 索引是什么 1.1 认识索引 先创建一个简单的DataFrame。 myList = [['a', 10, 1.1], ['b', 20, 2.2], ['c', 30, 3.3], ['d', 40, ... -
千万级数据库使用索引查询速度更慢的疑惑-数据回表问题
2018-08-01 13:24:17千万级数据库使用索引查询速度更慢的疑惑-数据回表问题 -
ES删除索引中的所有数据(不删除索引结构)含curl删除方式
2021-06-29 16:28:16第一种:只删除索引中的所有数据,不删除索引结构 POST 192.168.100.88:9200/my_index/_delete_by_query 请求体: { "query": { "match_all": {} } } 注释: 其中 my_index是索引名称 第二种:删除索引中... -
使用Java Api 查询ElasticSearch 索引中的全部数据
2021-08-13 10:49:08//1、查询索引中全部数据 SearchRequest request = new SearchRequest(); request.indices("user"); request.source(new SearchSourceBuilder().query(QueryBuilders.matchAllQuery())); SearchResponse response =... -
MySQL中如何使用索引
2021-01-15 12:03:56原标题:MySQL中如何使用索引 者:Airy 在数据分析之路狂奔,立志成为大咖级人物。前言学完基础的MySQL知识,以及MySQL的增删改查,我们要学习一些性能方面的东西。今天来讲一下索引(Index)。索引在关系数据库中,... -
4、当数据表中A、B字段做了组合索引,那么单独使用A或单独使用B会有索引效果吗?(使用like查询如何有索引...
2019-08-12 16:55:36答:看A、B两字段做组合索引的时候,谁在前面,谁在后面,如果A在前,那么单独使用A会有索引效果,单独使用B则没有,反之亦然。同理,使用like模糊查询时,如果只是使用前面%,那么有索引效果,如果使用双%号匹配,... -
MySQL索引(MyISAM和InnoDB)所使用的数据结构-B+树
2019-01-19 11:40:11本文以MySQL数据库为研究对象,讨论与数据库索引...为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论。 文章主要内容分为三个部分。 第一... -
MySQL高级篇——索引的数据结构
2022-04-11 16:35:123.InnoDB中的索引 3.1 设计索引 3.2 常见索引概念 3.2.1 聚簇索引 3.2.2 非聚簇索引 3.2.3 联合索引 4.InnoDB与MyISAM的索引对比 5.B-Tree和B+Tree的差异 1.为什么使用索引? 假如给数据使用 二叉树 ... -
mysql给数据量大的表添加索引的办法
2020-04-07 22:48:26在创建此表前没有未相应字段添加索引,所以此时需要为表添加索引。但是因为数据量大的原因,索引添加不成功,想了很多办法,终于在短时间内解决了。 办法如下: 1、进入mysql界面。mysql -uroot -hlocalhost -... -
删除ES索引里面的数据
2020-12-29 17:21:26总之,就是无法像处理openresty日志那样,按天生成1个ES索引,这样就可以简单匹配月份,直接删除整个月的索引数据了。但是,现在所有接口日志写入进1个索引里。我想删除几百天,比如200天前的数据,研究了好久。下面... -
数据库索引及其数据结构
2018-04-08 20:26:04数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种... -
oracle分区表中本地索引和全局索引的适用场景
2018-06-11 10:19:10oracle分区表仲本地索引和全局索引的适用场景 ...既然存在就会有存在的原因,也就是在特定的场景中就更能发挥出索引的性能的; 本文档通过测试,总结出两种索引的适合的场景; 测试环境 数据库版本:1... -
oracle表数据量大时建立索引,需要加上online参数
2018-06-01 17:19:03转自:...所以在大数据量的情况下建立索引,而此时会对该表进行DML操作时需要在建索引语句后加上online参数。同时建立索引时可以用并发,记得建完之后要关掉并发否则会影响oracle性能。... -
MySQL索引篇,创建表时创建索引
2021-01-27 02:34:24索引对于MySQL数据库查询速度具有无可取代的作用,一个合适的索引能给数据查询的效率带来巨大的提升,本文来给大家讲讲如何创建索引,索引的最佳创建方式是在建表的时候就确定好要索引的字段并建立好索引。索引的... -
MySQL百万级数据添加索引
2019-01-07 19:52:14背景 公司某产品基础数据表,各模块依赖这张表,该表数据量四百万,导致某功能打开页面半... 创建新表与原表结构保持一致,在该表上执行alter语句添加索引; 将表数据导入创建的新表; 修改新建的表名为原表名。 ... -
数据库表结构及索引设计
2021-12-13 20:31:52在数据库表设计上有个很重要的设计准则,称为范式设计。 范式设计 什么是范式? 范式来自英文Normal Form,简称NF。MySQL是关系型数据库,但是要想设计—个好的关系,必须使关系满足一定的约束条件,此约束已经... -
避免全表扫描的几种情况-索引的使用
2019-01-02 09:54:17查询语句的时候尽量避免全表扫描,使用全扫描,索引扫描!会引起全表扫描的几种SQL如下 1、模糊查询效率很低: 原因:like本身效率就比较低,应该尽量避免查询条件使用like;对于like ‘%...%’(全模糊)这样的... -
索引到底能提升多少查询效率?何时该使用索引?一文快速搞懂数据库索引及合理使用它
2021-08-09 12:29:00无论是面试、还是日常工作中,或多或少都会使用或者听到别人谈论索引这个技术。 然而很大一部份程序员对索引的了解仅限于到“加索引能使查询变快”这个概念为止。 使用索引也很简单,然而, 会使用索引是一回事, 而... -
MySQL中如何使用SQL语句创建、管理索引
2022-04-28 16:14:48#(1)利用create table语句在test数据库中创建数据表writers,其表结构内容如下表所示。 字段名 字段描述 数据类型 主键 外键 非空 唯一 ... -
MySQL中的B+树索引结构
2021-08-08 19:11:01B树 B树(B-tree、B-树):是一种平衡的多路搜索树,多用于文件系统、数据库的实现。 B树的特点: 1个节点可以存储超过2个元素、可以拥有超过2个子节点;...的地址,叶子结点以上各层作为索引使用。 -
MySQL为什么使用B+树作为索引? 索引原理?
2022-03-22 10:22:50在MySQL中,主要有四种类型的索引,分别为:B-Tree索引,Hash索引,Fulltext索引(MyISAM 表)和R-Tree索引,本文讲的是B-Tree索引。 后面的索引原理一定要看,太重要了,阿里两个人都问这个mysql的索引原理 mysql... -
Elasticsearch 使用kibana 查询索引数据
2020-03-05 10:42:53首先保证索引里面有数据,我查资料说是kibana 是用时间筛选数据的,所以必须保证类型属性里面必须有一个date时间格式的属性。但是新版的可以不用时间来...创建完成就可以在discover界面选择索引模式来查看数据了 ... -
索引的三种数据结构
2020-06-25 23:21:52索引可能有三种数据结构哈希表、有序数组和N叉树。MySQL使用了B+树。 1.哈希表(散列表) 哈希表是一种以键 - 值(key-value)存储数据的结构,我们只要输入待查找的值即 key,就可以找到其对应的值即 Value。 ...