mysql数据库索引

最新推荐文章于 2024-06-25 09:28:31 发布

Liu-1998

最新推荐文章于 2024-06-25 09:28:31 发布

阅读量4.6k

点赞数 4

文章标签： mysql

本文链接：https://blog.csdn.net/m0_46192647/article/details/124015658

版权

一、索引的定义：

二、MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引的创建

一、索引的定义：

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

通俗来说:索引是好比书的目录，可以更加快速准确的找到想要搜寻的内容。

二、MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引的创建

1、普通索引

（1）、创建索引

直接创建索引：
CREATE INDEX 索引名 ON 表名 (列名[(length)]);

        #(列名(name))：name是可选项，下同
        #如果忽略 name 的值，则使用整个列的值作为索引
        #如果指定使用列前的 name 个字符来创建索引，这样有利于减小索引文件的大小
        #索引名建议以“_index”结尾


例：
create index 列名_index on 表名 (列名);
#直接创建索引

show create table 表名;
#展示表的结构以及创建表的具体语句

（2）、修改表的格式

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

例：
alter table 表名 add index 列名_index (列名);   #这是第二种创建普通索引的方式，能修改表格式

（3)、创建表时指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));


例：
create table liu_test(id int(4) not null,name varchar(10) not null,sex varchar(18) not null,index id_index (id));

2、创建唯一索引

（1）、直接创建索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

例：
create unique index 列名_index on 表名 (列名);
#注意，创建唯一索引必须得满足每个值都是唯一的，否则无法创建

（2）、修改表的格式

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);


例：
alter table 表名 add unique 列名_index (列名);

（3)、创建表时指定索引

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));


例:
create table test (id int,name varchar(20),unique 列名_index (列名));

3、创建主键索引

（1）、直接创建索引

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));


例：
create table 表名 (id int primary key,name varchar(20));
#这里用了两种方式创建，一种是在字段中直接指定，第二种是在字段结束后指定
create table 表名 (id int,name varchar(20),primary key (id));

（2）、修改表的格式

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

4、创建全文索引

（1）、直接创建索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);
#全文索引 fulltext，全文只可在 char、varchar、text 类型上创建，且每个表只允许有一个

（2）、修改表的格式

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

（3）、创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT

（4）、使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

5、创建组合索引（单列索引与多列索引）

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

SELECT * FROM 表名 WHERE 列名1='...' AND 列名2='...' AND 列名3='...';

三、修改和删除索引的操作

修改索引：
    对于已经建立的索引，如果需要对其重新命名，可以使用ALTER INDEX 语句。其一般格式为
    ALTER INDEX <旧引索名字> RENAME TO<新引索名>
删除索引：
    当某个时期基本表中数据更新频繁或者某个索引不在需要时，需要删除部分索引。SQL语言使用DROP INDEX 语句删除索引，    其一般格式是：
    DROP INDEX<索引名>
    删除索引时，DBMS不仅在物理删除相关的索引数据，也会从数据字典删除有关该索引的描述。

四、Mysql各种索引区别

普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它 是一种特殊的唯一索引，不允许有空值。 
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件        的列放在最左边，依次递减。

五、索引查看方法

show index from 表名;
show index from 表名\G;

或
show keys from 表名;
show keys from 表名\G;
在命令后面加上“\G”，可以竖向显示索引的信息（这两种方法的显示结果完全相同）

索引的详细信息：

显示参数	描述
Table	表名称
Non_unique	索引值的唯一性，0 表示唯一性，1 表示非唯一性
Key_name	索引的名称
Seq_in_index	索引中的列序号，从 1 开始
Column_name	列名称
Collation	列以什么方式存储在索引中。在 MySQL 中，有值 A（升序）或 NULL（无分类）
Cardinality	索引中唯一值数目的估计值
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目；如果整列被编入索引，则为 NULL
Packed	指示关键字如何被压缩；如果没有被压缩，则为 NULL
Null	如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）
Comment	备注

六、索引的优点与缺点：

优点：
    1.大大加快数据的检索速度;
    2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
    3.加速表和表之间的连接;
    4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：
    1.索引需要占物理空间。
    2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

七、索引在查询中如何使用

索引主要是在数据量较大的情况下，可以增加数据库内数据的查询速度：
检查查询的的筛选条件设置索引：若是索引根据索引进行查询，若不是索引则遍历全表进行查询

八、判断索引是否适合

适合使用索引的情况：
    1、主键自动建立唯一索引
    2、频繁作为查询条件的字段适合创建索引
    3、查询时候与其他表关联的字段，外键关系建立索引
    4、在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。
    5、单键/组合索引的选择问题，组合索引性价比更高
    
不适合创建索引的情况：
    1、表记录太少
    2、经常增删改的字段
    3、Where条件里用不到的字段不创建索引（索引建多了影响 增删改 的效率）
    4、许多重复的内容，为它建立索引就没有太大的实际效果 (应该只为最经常查询和最经常排序的数据列建立索引)
            假如一个表table有10万行记录，有一个字段A只有True和False两种值，且每一个值的分布概率大约为50%，那么       对这种表A字段建索引一般不会提高数据库的查询速度。
            索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同       的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性接近于1，这个索引的效率就越高。