精华内容
下载资源
问答
  • 创建主键索引的关键字是
    千次阅读
    2021-01-19 00:13:10

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

    基本语法

    MySQL 提供了三种创建索引的方法:

    1) 使用 CREATE INDEX 语句

    可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引,但该语句不能创建主键。

    语法格式:

    CREATE ON ( [] [ ASC | DESC])

    语法说明如下:

    :指定索引名。一个表可以创建多个索引,但每个索引在该表中的名称是唯一的。

    :指定要创建索引的表名。

    :指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。

    :可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小,节省索引列所占的空间。在某些情况下,只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节(MyISAM 和 InnoDB 表的最大上限为 1000 个字节),如果索引列的长度超过了这个上限,就只能用列的前缀进行索引。另外,BLOB 或 TEXT 类型的列也必须使用前缀索引。

    ASC|DESC:可选项。ASC指定索引按照升序来排列,DESC指定索引按照降序来排列,默认为ASC。

    2) 使用 CREATE TABLE 语句

    索引也可以在创建表(CREATE TABLE)的同时创建。在 CREATE TABLE 语句中添加以下语句。语法格式:

    CONSTRAINT PRIMARY KEY [索引类型] (,…)

    在 CREATE TABLE 语句中添加此语句,表示在创建新表的同时创建该表的主键。

    语法格式:

    KEY | INDEX [] [] (,…)

    在 CREATE TABLE 语句中添加此语句,表示在创建新表的同时创建该表的索引。

    语法格式:

    UNIQUE [ INDEX | KEY] [] [] (,…)

    在 CREATE TABLE 语句中添加此语句,表示在创建新表的同时创建该表的唯一性索引。

    语法格式:

    FOREIGN KEY

    在 CREATE TABLE 语句中添加此语句,表示在创建新表的同时创建该表的外键。

    在使用 CREATE TABLE 语句定义列选项的时候,可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键。而当主键是由多个列组成的多列索引时,则不能使用这种方法,只能用在语句的最后加上一个 PRIMARY KRY(,…) 子句的方式来实现。

    3) 使用 ALTER TABLE 语句

    CREATE INDEX 语句可以在一个已有的表上创建索引,ALTER TABLE 语句也可以在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时,可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项。

    语法格式:

    ADD INDEX [] [] (,…)

    在 ALTER TABLE 语句中添加此语法成分,表示在修改表的同时为该表添加索引。

    语法格式:

    ADD PRIMARY KEY [] (,…)

    在 ALTER TABLE 语句中添加此语法成分,表示在修改表的同时为该表添加主键。

    语法格式:

    ADD UNIQUE [ INDEX | KEY] [] [] (,…)

    在 ALTER TABLE 语句中添加此语法成分,表示在修改表的同时为该表添加唯一性索引。

    语法格式:

    ADD FOREIGN KEY [] (,…)

    在 ALTER TABLE 语句中添加此语法成分,表示在修改表的同时为该表添加外键。

    创建一般索引

    【实例 1】创建一个表 tb_stu_info,在该表的 height 字段创建一般索引。输入的 SQL 语句和执行过程如下所示。

    mysql> CREATE TABLE tb_stu_info

    -> (

    -> id INT NOT NULL,

    -> name CHAR(45) DEFAULT NULL,

    -> dept_id INT DEFAULT NULL,

    -> age INT DEFAULT NULL,

    -> height INT DEFAULT NULL,

    -> INDEX(height)

    -> );

    Query OK,0 rows affected (0.40 sec)

    mysql> SHOW CREATE TABLE tb_stu_info\G

    *************************** 1. row ***************************

    Table: tb_stu_info

    Create Table: CREATE TABLE `tb_stu_info` (

    `id` int(11) NOT NULL,

    `name` char(45) DEFAULT NULL,

    `dept_id` int(11) DEFAULT NULL,

    `age` int(11) DEFAULT NULL,

    `height` int(11) DEFAULT NULL,

    KEY `height` (`height`)

    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312

    1 row in set (0.01 sec)

    创建唯一索引

    【实例 2】创建一个表 tb_stu_info2,在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引。输入的 SQL 语句和执行过程如下所示。

    mysql> CREATE TABLE tb_stu_info2

    -> (

    -> id INT NOT NULL,

    -> name CHAR(45) DEFAULT NULL,

    -> dept_id INT DEFAULT NULL,

    -> age INT DEFAULT NULL,

    -> height INT DEFAULT NULL,

    -> UNIQUE INDEX(height)

    -> );

    Query OK,0 rows affected (0.40 sec)

    mysql> SHOW CREATE TABLE tb_stu_info2\G

    *************************** 1. row ***************************

    Table: tb_stu_info2

    Create Table: CREATE TABLE `tb_stu_info2` (

    `id` int(11) NOT NULL,

    `name` char(45) DEFAULT NULL,

    `dept_id` int(11) DEFAULT NULL,

    `age` int(11) DEFAULT NULL,

    `height` int(11) DEFAULT NULL,

    UNIQUE KEY `height` (`height`)

    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312

    1 row in set (0.00 sec)

    查看索引

    在 MySQL 中,如果要查看已创建的索引的情况,可以使用 SHOW INDEX 语句查看表中创建的索引。

    语法格式:

    SHOW INDEX FROM [ FROM ]

    语法说明如下:

    :要显示索引的表。

    :要显示的表所在的数据库。

    显示数据库 mytest 的表 course 的索引情况。

    mysql> SHOW INDEX FROM course FROM mytest;

    该语句会返回一张结果表,该表有如下几个字段,每个字段所显示的内容说明如下。

    Table:表的名称。

    Non_unique:用于显示该索引是否是唯一索引。若不是唯一索引,则该列的值显示为 1;若是唯一索引,则该列的值显示为 0。

    Key_name:索引的名称。

    Seq_in_index:索引中的列序列号,从 1 开始计数。

    Column_name:列名称。

    Collation:显示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。

    Cardinality:显示索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。

    Sub_part:若列只是被部分编入索引,则为被编入索引的字符的数目。若整列被编入索引,则为 NULL。

    Packed:指示关键字如何被压缩。若没有被压缩,则为 NULL。

    Null:用于显示索引列中是否包含 NULL。若列含有 NULL,则显示为 YES。若没有,则该列显示为 NO。

    Index_type:显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。

    Comment:显示评注。

    【实例 3】使用 SHOW INDEX 语句查看表 tb_stu_info2 的索引信息,输入的 SQL 语句和执行结果如下所示。

    mysql> SHOW INDEX FROM tb_stu_info2\G

    *************************** 1. row ***************************

    Table: tb_stu_info2

    Non_unique: 0

    Key_name: height

    Seq_in_index: 1

    Column_name: height

    Collation: A

    Cardinality: 0

    Sub_part: NULL

    Packed: NULL

    Null: YES

    Index_type: BTREE

    Comment:

    Index_comment:

    1 row in set (0.03 sec)

    < 上一页MySQL索引简介

    MySQL修改和删除索引下一页 >

    更多相关内容
  • 1、分类MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列,索引项可以为null(唯一索引、组合索引等),但是只要列中有null值就不会被包含在索引中。(0)创建表:create ...

    1、分类

    MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。

    索引不会包含有null值的列,索引项可以为null(唯一索引、组合索引等),但是只要列中有null值就不会被包含在索引中。

    (0)创建表:

    create tabletest(

    id1int,

    id2int,

    id3int,

    id4int);

    (1)创建普通索引:

    create index index_name on table(column);

    或者创建表时指定,create table(..., index index_name column);

    eg:

    CREATE index index_id1 on test(id1); -- 普通索引

    (2)创建唯一索引:

    类似普通索引,索引列的值必须唯一(可以为空,这点和主键索引不同)create unique index index_name on table(column);

    或者创建表时指定unique index_name column

    eg:

    CREATE unique index index_id2 on test(id2); --唯一索引

    (3)创建主键索引:

    特殊的唯一索引,不允许为空,只能有一个,一般是在建表时指定primary key(column)

    (4)创建组合索引:

    在多个字段上创建索引,遵循最左前缀原则。alter table t add index index_name(a,b,c);

    eg:

    alter table test add index index_id34(id3,id4); --组合索引

    (5)全文索引:

    主要用来查找文本中的关键字,不是直接与索引中的值相比较,像是一个搜索引擎,配合match against使用,现在只有char,varchar,text上可以创建全文索引。在数据量较大时,先将数据放在一张没有全文索引的表里,然后再利用create index创建全文索引,比先生成全文索引再插入数据快很多。

    效果图:

    abd0637722c18dcb285e258c4a910d1f.png

    2、何时使用索引

    MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”,倒不如说,和全表扫描比起来,去分析两个索引B+树更加耗费时间。所以where A=a and B=b这种查询使用(A,B)的组合索引最佳,B+树根据(A,B)来排序。

    (1)主键,unique字段;

    (2)和其他表做连接的字段需要加索引;

    (3)在where里使用>,≥,=,<,≤,is null和between等字段;

    (4)使用不以通配符开始的like,where A like 'China%';

    (5)聚集函数MIN(),MAX()中的字段;

    (6)order by和group by字段;

    3、何时不使用索引

    (1)表记录太少;

    (2)数据重复且分布平均的字段(只有很少数据值的列);

    (3)经常插入、删除、修改的表要减少索引,因为修改也要修改索引的数据,耗费性能;

    (4)text,image等类型不应该建立索引,这些列的数据量大(假如text前10个字符唯一,也可以对text前10个字符建立索引),因为索引占用的物理空间,数据量太大浪费硬盘;

    (5)MySQL能估计出全表扫描比使用索引更快时,不使用索引;

    4、索引何时失效

    (1)组合索引未使用最左前缀,例如组合索引(A,B),where B=b不会使用索引;

    (2)like未使用最左前缀,where A like '%China';

    (3)搜索一个索引而在另一个索引上做order by,where A=a order by B,只使用A上的索引,因为查询只使用一个索引 ;

    (4)or会使索引失效。如果查询字段相同,也可以使用索引。例如where A=a1 or A=a2(生效),where A=a or B=b(失效)

    (5)如果列类型是字符串,要使用引号。例如where A='China',否则索引失效(会进行隐式类型转换);

    (6)在索引列上的操作,函数(upper()等)、or、!=(<>)、not in等;

    5、explain语句

    type列字段为All,未使用索引;为ref,使用索引

    ALL                  全表扫描

    index                索引全扫描

    range               索引范围扫描,常用语=,between等操作

    ref                    使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中

    eq_ref               类似ref,区别在于使用的是唯一索引,使用主键的关联查询

    const/system    单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询

    null                   MySQL不访问任何表或索引,直接返回结果

    还有key字段表示用到的索引,没有用到为null

    更多解释参考:

    all:

    5c1ed09141ee630f77342bb6e7edbaef.png

    ref:

    ee26f4b65a275139fe8ae662b8887b76.png

    展开全文
  • 需要按照要求给表创建主键、创建索引,创建外键(并设置删除和更新规则) 一、创建主键 ​ 自己创建一个数据库和最少两个表(student表 和 course 表),然后上图:点击创建主键 [外链图片转存失败,源站可能有防盗...

    ​ 图书馆学习没有多大干劲了,数据库又快考试了根据考试内容写总结。老师回放已过期不得不自己来尝试 心酸!

    需要按照要求给表创建主键、创建索引,创建外键(并设置删除和更新规则)

    一、创建主键

    ​ 自己创建一个数据库和最少两个表(student表 和 course 表),然后上图:点击创建主键

    在这里插入图片描述

    二、创建外键

    1、什么是外键:(这是百度说的,不是我说的)如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。由此可见,外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表,具有此外键的表被称为主表的从表。外键又称作外关键字

    最后总结一下用人的话说:就是有两个表A和表B A和B同时有属性x,但是在A中X只是一个普普通通的键没啥大的作用,却在B中担当主键的位置。所以B就是主表,A就是从表,x就是A中的外键。所以A就是参照表,B就是被参照表。

    我希望创建的外键在student表中,也就是说:course作为被参照表(主表)student表作为参照表(从表)。

    表格信息:

    表格名称主键外键
    studentsnoscname
    coursecno

    2、创建外间的步骤:

    1、在student表中创建关系

    弹出关系窗口,点击添加,点击 表和列的规范 一项,设置主表和从表的关系。(这里student表的scname参照course表中的cno)

    2、设置insert和update规范

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eh1CsODt-1622373901735)(C:\Users\赵轶楠\AppData\Roaming\Typora\typora-user-images\image-20210530185111634.png)]

    默认的不执行任何操作。如果是“不执行任何操作”,当我们删除或更新主键表的数据时,会告诉用户不能执行删除或更新该操作。

    “级联”的意思是当我们删除或更新主键表的数据时,会删除或更新外键表中所涉及的相关数据的所有行。

    “设置Null”的意思是当我们删除或更新主键表的数据时,外键表中的外键列的值会设为Null,但前提是该列允许为空。

    “设置默认值”的意思是如果我们将外键列定义了默认值,当我们删除或更新主键表的数据时,外键表中的外键列的值设为定义的默认值。

    使用代码创建外键(在创建表格时加上"foreign key() references table_name() "即可,先创建主表再创建从表)

    create table course
    cno nchar(10) primary key,
    cname nchar(10) not null,
    tname nchar(10);
    create table student
    sno nchar(10) primary key,
    sname nchar(10) not null,
    ssex nchar(10),
    scname nchar(10) foreign key(scname) references course(cno);
    

    三、创建索引

    1、什么是索引:索引就像一个标签,创建索引之后便可以加快查询的速度

    2、创建索引的步骤:

    • 点击表在索引处右击创建索引

    在这里插入图片描述

    • 点击添加,至少选择一个作为索引点击确定,及创建完毕

    出现第二个图标即是成功
    在这里插入图片描述

    3、用代码创建索引(CREATE[ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX索引名
    ON {表名|视图名} (列名[ ASC | DESC ] [ ,…n ] ))

    create index_name
    on course(cno)
    

    动作描述 使用聚集索引 使用非聚集索引
    外键列 应 应
    主键列 应 应
    列经常被分组排序(order by) 应 应
    返回某范围内的数据 应 不应
    小数目的不同值 应 不应
    大数目的不同值 不应 应
    频繁更新的列 不应 应
    频繁修改索引列 不应 应
    一个或极少不同值 不应 不应

    聚集索引:

    • 聚集索引的叶节点就是实际的数据页
    • 聚集索引中的排序顺序仅仅表示数据页链在逻辑上是有序的。而不是按照顺序物理的存储在磁盘上
    • 行的物理位置和行在索引中的位置是相同的
    • 每个表只能有一个聚集索引
    • 聚集索引的平均大小大约为表大小的5%左右

    非聚集索引:

    • 非聚集索引的页,不是数据,而是指向数据页的页。
    • 若未指定索引类型,则默认为非聚集索引。
    • 叶节点页的次序和表的物理存储次序不同
    • 每个表最多可以有249个非聚集索引
    • 在非聚集索引创建之前创建聚集索引(否则会引发索引重建)
    展开全文
  • 主键索引 组合索引 创建和删除组合索引 全文索引 全文索引的案例 空间索引 索引的优点 索引的缺点 创建索引的原则 Hash算法 二叉树 平衡二叉树 BTREE树 每文一语 索引介绍 索引是通过某种算法,构建...

    目录

    索引介绍

    Hash索引和B+tree索引的区别

    应用场景

    索引分类

    普通索引

    创建单列索引——普通索引(3种语法)

    查看数据库中的索引

    查看数据表中的索引

     删除索引

    唯一索引

    主键索引

    组合索引

    创建和删除组合索引

    全文索引

    全文索引的案例

    空间索引

    索引的优点

    索引的缺点

    创建索引的原则

    Hash算法

    二叉树

    平衡二叉树

    BTREE树

     每文一语


    索引介绍

    索引是通过某种算法,构建出一个数据模型,用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

    索引类似一本书的目录,比如要查找’student’这个单词,可以先找到s开头的页然后向后查找,这个就类似索引。

    一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。
    这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
    换句话说,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

    索引是存储引擎用来快速查找记录的一种数据结构,按照实现的方式类分,主要有Hash索引B+Tree索引 

    Hash索引和B+tree索引的区别

    1、在查询速度上,如果是等值查询,那么Hash索引明显有绝对优势,因为只需要经过一次 Hash 算法即可找到相应的键值,复杂度为O(1);当然了,这个前提是键值都是唯一的

    如果键值不是唯一(或存在Hash冲突),就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据,这时候复杂度会变成O(n),降低了Hash索引的查找效率。

    所以,Hash 索引通常不会用到重复值多的列上,比如列为性别、年龄的情况等(当然B+tree索引也不适合这种离散型低的字段上);

    2、Hash 索引是无序的,如果是范围查询检索,这时候 Hash 索引就无法起到作用,即使原先是有序的键值,经过 Hash 算法后,也会变成不连续的了。因此

    ①、Hash 索引只支持等值比较查询、无法索成范围查询检索,B+tree索引的叶子节点形成有序链表,便于范围查询。

    ②、Hash 索引无法做 like ‘xxx%’ 这样的部分模糊查询,因为需要对 完整 key 做 Hash 计算,定位bucket。而 B+tree 索引具有最左前缀匹配,可以进行部分模糊查询。

    ③、Hash索引中存放的是经过Hash计算之后的Hash值,而且Hash值的大小关系并不一定和Hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算。B+tree 索引的叶子节点形成有序链表,可用于排序。

    3、Hash 索引不支持多列联合索引,对于联合索引来说,Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值,不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时,联合索引无法被利用;

    4、因为存在哈希碰撞问题,在有大量重复键值情况下,哈希索引的效率极低。B+tree 所有查询都要找到叶子节点,性能稳定;

    应用场景

    1、大多数场景下,都会有组合查询,范围查询、排序、分组、模糊查询等查询特征,Hash 索引无法满足要求,建议数据库使用B+树索引。

    2、在离散型高,数据基数大,且等值查询时候,Hash索引有优势。

    索引分类

    按照功能划分,索引划为以下分类:

    普通索引

    单列索引一个索引只包含单个列,但一个表中可以有多个单列索引;

    普通索引MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。

    创建单列索引——普通索引(3种语法)

    create database mydb5;
    use mydb5;
    
    -- 方式1-创建表的时候直接指定
    create  table student(
        sid int primary key,
        card_id varchar(20),
        name varchar(20),
        gender varchar(20),
        age int,
        birth date, 
        phone_num varchar(20),
        score double,
        index index_name(name) -- 给name列创建索引
    );
    

     直接使用index index_name(列名)即可

    -- 方式2-直接创建
    -- create index indexname on tablename(columnname); 
    create index index_gender on student(gender); 
    
    -- 方式3-修改表结构(添加索引)
    -- alter table tablename add index indexname(columnname)
    alter table student add index index_age(age);
    

    查看数据库中的索引

    -- 1、查看数据库所有索引 
    -- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名’; 
    select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5';
    
    

    查看数据表中的索引

    -- 2、查看表中所有索引 
    -- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and a.table_name like '%表名%’; 
    select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5' and a.table_name like '%student%';
    
    

    -- 3、查看表中所有索引 
    -- show index from table_name; 
    show index from student;
    

     删除索引

    drop index 索引名 on 表名 
    -- 或 
    alter table 表名 drop index 索引名 
    
    drop index index_gender on student 
    -- 或 
    alter table student drop index index_name
    

    唯一索引

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

    -- 方式1-创建表的时候直接指定
    create  table student2(
        sid int primary key,
        card_id varchar(20),
        name varchar(20),
        gender varchar(20),
        age int,
        birth date, 
        phone_num varchar(20),
        score double,
        unique index_card_id(card_id) -- 给card_id列创建索引
    );
    -- 方式2-直接创建
    -- create unique index 索引名 on 表名(列名) 
    create unique index index_card_id on student2(card_id);
    
    -- 方式3-修改表结构(添加索引)
    -- alter table 表名 add unique [索引名] (列名)
    alter table student2 add unique index_phone_num(phone_num)
    

    可以发现,就像我们创建普通索引的时候一样,将index前面加一个unique即可

    删除索引的方法也是一样的

    drop index index_card_id on student2 
    -- 或 
    alter table student2 drop index index_phone_num
    

    主键索引

    每张表一般都会有自己的主键,当我们在创建表时,MySQL会自动在主键列上建立一个索引,这就是主键索引。主键是具有唯一性并且不允许为NULL,所以他是一种特殊的唯一索引。

    组合索引

    组合索引也叫复合索引,指的是我们在建立索引的时候使用多个字段,例如同时使用身份证和手机号建立索引,同样的可以建立为普通索引或者是唯一索引。
    复合索引的使用复合最左原则。

    -- 创建索引的基本语法 
    create index indexname on table_name(column1(length),column2(length)); 
    

    创建和删除组合索引

    -- 组合索引
    use mydb5;
    -- 创建索引的基本语法-- 普通索引
    -- create index indexname on table_name(column1(length),column2(length)); 
    create index index_phone_name on student(phone_num,name);
    -- 操作-删除索引
     drop index index_phone_name on student; 
    -- 创建索引的基本语法-- 唯一索引
    create  unique index index_phone_name on student(phone_num,name); 
    
    select * from student where name = '张三'; 
    select * from student where phone_num = '15100046637'; 
    select * from student where phone_num = '15100046637' and name = '张三'; 
    select * from student where name = '张三' and phone_num = '15100046637'; 
    /* 
      三条sql只有 2 、 3、4能使用的到索引idx_phone_name,因为条件里面必须包含索引前面的字段
      才能够进行匹配。
      而3和4相比where条件的顺序不一样,为什么4可以用到索引呢?是因为mysql本身就有一层sql优化,
      他会根据sql来识别出来该用哪个索引,我们可以理解为3和4在mysql眼中是等价的。 
    
    */
    
    

    注意组合索引的匹配是从左到右依次

    总结索引的匹配顺序:

    首先根据创建组合索引的顺序,进行匹配,如果第一个是该索引的第一个,则可以用到该索引,如果第一个不是的话,而无法使用索引的效果,后期在MySQL优化里面会讲到索引的一些特性和具体使用。

    全文索引

    全文索引的关键字是fulltext

    全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较,它更像是一个搜索引擎,基于相似度的查询,而不是简单的where语句的参数匹配。

    用 like + % 就可以实现模糊匹配了,为什么还要全文索引?like + % 在文本比较少时是合适的,但是对于大量的文本数据检索,是不可想象的。全文索引在大量的数据面前,能比 like + % 快 N 倍,速度不是一个数量级,但是全文索引可能存在精度问题。

    全文索引的版本、存储引擎、数据类型的支持情况:
    MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;

    MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;

    只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引;

    在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用create index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多;

    测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

    MySQL 中的全文索引,有两个变量,最小搜索长度和最大搜索长度,对于长度小于最小搜索长度和大于最大搜索长度的词语,都不会被索引。通俗点就是说,想对一个词语使用全文索引搜索,那么这个词语的长度必须在以上两个变量的区间内。这两个的默认值可以使用以下命令查看:

    #

    参数名称

    默认值

    最小值

    最大值

    作用

    1

    ft_min_word_len

    4

    1

    3600

    MyISAM 引擎表全文索引包含的最小词长度

    2

    ft_query_expansion_limit

    20

    0

    1000

    MyISAM引擎表使用 with query expansion 进行全文搜索的最大匹配数

    3

    innodb_ft_min_token_size

    3

    0

    16

    InnoDB 引擎表全文索引包含的最小词长度

    4

    innodb_ft_max_token_size

    84

    10

    84

    InnoDB 引擎表全文索引包含的最大词长度

    -- 创建表的时候添加全文索引
    create table t_article (
         id int primary key auto_increment ,
         title varchar(255) ,
         content varchar(1000) ,
         writing_date date -- , 
         -- fulltext (content) -- 创建全文检索
    );
    

    全文索引的案例

    insert into t_article values(null,"Yesterday Once More","When I was young I listen to the radio",'2021-10-01');
    insert into t_article values(null,"Right Here Waiting","Oceans apart, day after day,and I slowly go insane",'2021-10-02'); 
    insert into t_article values(null,"My Heart Will Go On","every night in my dreams,i see you, i feel you",'2021-10-03');
    insert into t_article values(null,"Everything I Do","eLook into my eyes,You will see what you mean to me",'2021-10-04');
    insert into t_article values(null,"Called To Say I Love You","say love you no new year's day, to celebrate",'2021-10-05');
    insert into t_article values(null,"Nothing's Gonna Change My Love For You","if i had to live my life without you near me",'2021-10-06');
    insert into t_article values(null,"Everybody","We're gonna bring the flavor show U how.",'2021-10-07');
    
    -- 修改表结构添加全文索引
    alter table t_article add fulltext index_content(content)
     
    -- 直接添加全文索引
    create fulltext index index_content on t_article(content);
    

    使用全文索引

    和常用的模糊匹配使用 like + % 不同,全文索引有自己的语法格式,使用 match 和 against 关键字,格式:

    select * from t_article where match(content) against('yo’); -- 没有结果 单词数需要大于等于3 
    select * from t_article where match(content) against('you'); -- 有结果
    

    空间索引

    MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型
    空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。
    MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。
    创建空间索引的列,必须将其声明为NOT NULL。
    空间索引一般是用的比较少,了解即可。

    类型

    含义

    说明

    Geometry

    空间数据

    任何一种空间类型

    Point

    坐标值

    LineString

    线

    有一系列点连接而成

    Polygon

    多边形

    由多条线组成

    create table shop_info (
      id  int  primary key auto_increment comment 'id',
      shop_name varchar(64) not null comment '门店名称',
      geom_point geometry not null comment '经纬度’,
      spatial key geom_index(geom_point)
    );
    

    索引的优点

    大大加快数据的查询速度

    使用分组和排序进行数据查询时,可以显著减少查询时分组和排序的时间

    创建唯一索引,能够保证数据库表中每一行数据的唯一性

    在实现数据的参考完整性方面,可以加速表和表之间的连接

    索引的缺点

    创建索引和维护索引需要消耗时间,并且随着数据量的增加,时间也会增加

    索引需要占据磁盘空间

    对数据表中的数据进行增加,修改,删除时,索引也要动态的维护,降低了维护的速度


    创建索引的原则

    更新频繁的列不应设置索引

    数据量小的表不要使用索引(毕竟总共2页的文档,还要目录吗?)

    重复数据多的字段不应设为索引(比如性别,只有男和女,一般来说:重复的数据超过百分之15就不该建索引)

    首先应该考虑对where 和 order by 涉及的列上建立索引

    Hash算法

    二叉树

    平衡二叉树

    BTREE树

    目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构,Btree结构可以有效的解决之前的相关算法遇到的问题。

     每文一语

    每一次的开始都是有意义的!

    展开全文
  • 浅谈主键索引与唯一索引

    千次阅读 2017-11-16 18:57:38
    在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时,它还允许快速访问数据。 它们的一些比较: (1)对于主健/...
  • 转自http://blog.itpub.net/29272216/viewspace-1243730/转https://www.cnblogs.com/wezheng/p/8399305.html索引关键字的选取原则1、表的某个字段值得离散度越高,该字段越适合选作索引的关键字。主键字段以及唯一性...
  • //主键alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);//增加一个新列alter table t2 add d timestamp;alter table infos add ex ...
  • 文章目录 第四十六章 索引关键字 - Abstract用法详解默认 第四十六章 索引关键字 - Abstract 指定索引是抽象的。 用法 要指定索引是抽象的,请使用以下语法: Index MyIndex [ Abstract ]; 注意:创建分片表时,会...
  • 索引关键字的选取原则 1、表的某个字段值得离散度越高,该字段越适合选作索引的关键字。主键字段以及唯一性约束字段适合选作索引的关键字,原因就是这些字段的值非常离散。尤其是在主键字段创建索引时,cardinality...
  • 1.一张表中可以有几个主键? 答:一个。 2.那联合主键是什么? 答:一张表多个主键,就是联合主键,可以由多个列形成联合主键,但是主键只能有一个 3.主键作用是什么?...创建使用关键字INDEX或KEY; (2).唯一
  • 你还在使用UUID 作为mysql 主键索引

    千次阅读 2022-04-17 17:15:02
    记得当初学习数据库的时候知道我们需要建立一列来作为... 由于工作慢慢接触sql优化,提到sql 优化可能大家最先想到的就是“索引”,有一句话这么说:”合理的设计且使用索引的mysql 是一辆超跑,没有设计和使用索...
  • sql优化篇-关键字与索引使用explain查看sql性能使用索引提高查询效率普通索引主键索引唯一索引复合索引前缀索引sql关键字对索引的影响in 不会使索引失效like 部分情况会使索引失效and 不会使索引失效 ,or 会使索引...
  • //加主关键字索引mysql> alter table tablename add primary key(id); //加唯一限制条件的索引mysql> alter table tablename add unique emp_name2(cardnumber); //删除某个索引mysql>alter table tablename drop...
  • mysql主键索引与非主键索引区别

    千次阅读 2019-03-06 18:19:54
    主键是逻辑键,索引是物理键。主键不实际存在,而索引实际存在于数据库中。 索引会真正产生文件。数据会真正产生文件。 redo log 记录的内容:物理日志,"某个数据页上做了什么修改" ,循环使用。 bin log...
  • 禁用索引的语句: ALTER TABLE table_name DISABLE KEYS; 开启索引语句: ...创建普通索引、组合索引 ADD INDEX [<索引名>] (<列名>,…) 查看索引 SHOW INDEX FROM <表名> [ FRO.
  • 干货:主键是关系表中记录的唯一标识。主键的选取非常重要:主键不要带有业务含义,而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。可以使用多个列作为联合主键,但...通过创建唯一索引,可以保证某一列...
  • 为什么要创建索引 mysql在默认的情况下,表中的数据记录是没有顺序的,就是说,在数据检索的时候,符合条件的数据存在表中的什么位置是不知道的,如果使用select 进行查询,数据库会从第一条数据进行检索,找到第一...
  • 如果作为搜索条件的列上已经创建索引,MySQL无 需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。 mysql在使用like查询中,能不能用到索引?在...
  • 此时假设一个场景:uid是主键,有主键索引树,name有索引,创建二级索引树 当前场景下的主键索引树如下,B+树非叶子节点上只有索引值,叶子节点上有索引值和数据地址 MyISAM索引树原理图如下: 当前场景下的二级...
  • (1)直接创建索引 CREATE INDEX index_name ON table(column(length)) (2)修改表结构的方式添加索引 ALTER TABLE table_name ADD INDEX index_name ON (column(length)) (3)创建表的时候同时创建索引 CREATE...
  • 普通索引 最基本的索引,没有任何限制 mysql&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) -- 在索引上添加mul mysql&amp;amp;amp;amp;amp;...
  • 本篇文章简单介绍一下聚集索引主键的区别1主键的概念主关键字(主键,primary key)是被挑选出来,作表的行的唯一标识的候选关键字。一个表只有一个主关键字。主关键字又可以称为主键主键可以由一个字段,也可以....
  • 关于主键索引的概念与作用

    千次阅读 2022-04-18 18:05:56
    当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束,而且 PRIMARY KEY 约束中的列不能接受空值。 由于 PRIMARY KEY 约束确保唯一数据,所以经常用来定义标识列。 作用: 1...
  • 主键 主键:primary key用来唯一的约束该字段里面的数据,不能重复,不能为空,一张表中最多只能有一个主键,主 键所在的列通常是整数类型。 一个可能被忽略的问题:数据库表中必须有主键吗? 答案是否定的。...
  • 1. MySQL中的主键是B+树,Mongo中的主键是B树(有些地方写作B-树,这不叫做B减树,还是B树的意思)。 为什么两个数据库选择不一样呢?①B树和B+树的区别在于,B树的内部节点上也会存储有数据data,但是B+树所有的data...
  • MySQL创建主键

    2021-02-02 10:34:41
    MySQL InnoDB 存储引擎是必须要有一...创建主键的三种方式:在建表语句字段后指定 PRIMARY KEY 关键字设置主键,这种方式只能定义主键为一个字段的情况,如下所示:CREATE TABLE `t_user` (`id` int(11) NOT NULL A...
  • 其实 MySQL 中的 like 关键字也能用索引

    千次阅读 多人点赞 2022-01-19 16:12:48
    上篇文章中,松哥和大家分享了索引的两个使用规则: 索引上不要使用函数运算。 使用覆盖索引避免回表。 当然,凡事有个度,用哪一种策略也要结合具体的项目来定,不能为了 SQL 优化而抛弃了业务。 今天,松哥在前...
  • //主键alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);//增加一个新列alter table t2 add d timestamp;alter table infos add ex ...
  • 数据库之主键索引

    千次阅读 2020-06-02 18:52:52
    主键、外键: 关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键 比如: 学生表(学号,姓名,性别,班级) 其中每个学生的学号是唯一的,学号...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 75,281
精华内容 30,112
热门标签
关键字:

创建主键索引的关键字是

友情链接: model_referencing.zip