mysql 8.0 索引详解

1. 索引的分类及含义

2. 索引创建的方式

3. 在已经存在的表上创建索引

4.  删除索引

索引的分类及含义: 

索引的含义和特点：索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。 # 通俗意思就是有些列查的数据量很大查询比较耗时，索引能减少耗时

特点：  mysql 有2种存储引擎，btree和hash ，  我们常用的引擎MyISAM和InnoDB存储引擎只支持btree；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

索引的优缺点  mysql索引 索引优缺点 - 丶焦人 - 博客园

分类及含义：

1.普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
2.唯一索引：要求索引列的值必须唯一，但允许有空值。组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

3.单列索引：即一个索引只包含单个列，一个表可以有多个单列索引

4.组合索引：是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

5.全文索引：类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

                5.1：支持的类型：全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建

                5.2：支持的表的引擎：MySQL中只有MyISAM存储引擎支持全文索引

6.空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON

              6.1：创建的条件：建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

索引创建的方式

        1.在创建表的定义语句CREATE TABLE中指定索引列

                       创建表的时候创建索引 例子：

                                

1.普通索引：在book表中的year_publication字段上建立普通索引
    CREATE TABLE book
    (
        bookid            	INT NOT NULL,
        bookname          	VARCHAR(255) NOT NULL,
        authors            	VARCHAR(255) NOT NULL,
        info               	VARCHAR(255) NULL,
        comment           	VARCHAR(255) NULL,
        year_publication   	YEAR NOT NULL,
        INDEX(year_publication)   # 最基本的索引，没有唯一性限制，只是加快查询速度
    );

>show create table book;  # 查看表结构
显示包含： KEY `year_publication` (`year_publication`)  # 表示索引创建成功


explain（执行计划） ： 能模拟优化器执行SQL查询语句，并不会去真正的执行这条SQL，从而知道MySQL 是如何处理你的SQL语句的。 可用来分析你的查询语句或是表结构的性能瓶颈

返回参数查看：https://blog.csdn.net/weixin_41244495/article/details/120687708


2.创建唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
优点：减少索引列的查询时间
    CREATE TABLE t1
    (
        id    INT NOT NULL,
        name CHAR(30) NOT NULL,
        UNIQUE INDEX UniqIdx(id)
    );
# UniqIdx  索引名字 id 是创建索引的列
3. 创建单列索引： 在表中一个字段创建，上面2中都是单列索引

    CREATE TABLE t2
    (
        id   INT NOT NULL,
        name CHAR(50) NULL,
        INDEX SingleIdx(name(20))
    );
# 20 是索引长度

4. 创建组合索引：
    CREATE TABLE t3
    (
        id    INT NOT NULL,
        name VARCHAR(30) 　NOT NULL,
        age  INT NOT　 NULL,
        info VARCHAR(255),
        INDEX MultiIdx(id, name, aga)
    );
# MultiIdx 组合索引的name, 可以理解成是一个集合name
explain select * from t3 where id = 1 and name="job";   # id条件值不在就不会执行组合索引
explain select * from t3 where id = 1;  
5．创建全文索引：FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引
    CREATE TABLE t4
    (
        id    INT NOT NULL,
        name CHAR(30) NOT NULL,
        age  INT NOT NULL,
        info VARCHAR(255),
        FULLTEXT INDEX FullTxtIdx(info)
    ) ENGINE=MyISAM;
    
#全文索引非常适合于大型数据集，对于小的数据集，它的用处比较小。


6. 创建空间索引:空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。
    CREATE TABLE t5
    ( 
        g GEOMETRY NOT NULL, 
        SPATIAL INDEX spatIdx(g) 
    )ENGINE=MyISAM;
    

                

        

        

        2.使用ALTER TABLE语句在存在的表上创建索引

        

语法：
　　ALTER TABLE table_name　ADD [UNIQUE|FULLTEXT|SPATIAL]　[INDEX|KEY]
　　[index_name] (col_name[length],…) [ASC | DESC]

查看指定表创建的索引：show index from  table_name;

ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );

SHOW INDEX FROM book;




其他的都差不多

        3.或者使用CREATE INDEX语句在已存在的表上添加索引

 

后面再写

        

         

       

       

       参考书籍《mysql 8 从入门到精通》