MySQL索引

Ms_Xiong

已于 2023-07-31 14:13:50 修改

阅读量60

点赞数

文章标签： mysql 数据库

于 2023-07-31 13:29:59 首次发布

本文链接：https://blog.csdn.net/huhandfgh/article/details/132020260

版权

一.索引概述

MySql官方对索引的定义为：索引是帮助MySql高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

个人对于MySql索引的理解：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，包括B+树或者Hash表。由于存储引擎表示的是数据在磁盘上面的不同的组织形式，所以索引底层采用哪种数据结构是跟数据库的存储引擎相关的。如果是MyIsam或者是InnoDB存储引擎，那么对应的底层的数据结构为B+树，如果是Memory存储引擎，那么对应的底层的数据结构为Hash表。采用B+树的最根本的原因是由于二叉树的树太高，树太高则直接影响到磁盘IO的次数，影响数据查询的效率，采用B+树的数据结构，可以在某个数据节点里面尽可能多的存储数据，使树的高度尽量的变低，提高效率。日常开发过程中，遇到的比较多的可能就是聚簇索引和联合索引，里面又涉及到了覆盖索引，最左匹配，回表，索引下推等各方面的知识点，在编写SQL语句的时候，我们就可以利用这些点来进行优化，提高数据的查询效率。

二、InnoDB和MyISAM的区别

1、InnoDB支持事务，MyISAM不支持；
2、InnoDB支持外键，MyISAM不支持；
3、InnoDB是簇索引，MyISAM是非簇索引；
4、Innodb不支持fulltext全文索引，MyISAM支持；
5、InnoDB支持到行级锁，MyISAM仅支持表级锁；
6、InnoDB表唯一索引必选，Myisam可以选；
7、存储文件不同，Myisam这棵树的叶子结点存储数据是物理地址，InnoDB的叶子结点直接存储数据记录，这也是簇索引与非簇索引的区别。
8、MyISAM存储结构相对简单，易于恢复，备份覆盖即可。若程序写少读多，对原子性要求不高，MyISAM是最优选择。
9、若大量写入作为核心业务，InnoDB是最优选择。InnoDB最恼人的是ibdata1只能增大，无法缩小，只能全部备份、删除ibdata，然后全部恢复，相当于重新迁移数据库了。这点不如微软的SQL Server，可以随时压缩清空日志到1MB。
多年的实际工程体验中，MySQL数据量达到百万以后，如果没有设计好分库分表分区索引，PHP+MySQL分页就是一个梗。而SQL Serve在千万级以上，C#+存储过程，分页秒开，当然这其中也有C#的功劳，毕竟编译型语言执行效率要高于解释性语言。从备份＆还原来看，后者易于前者，备份还原，或复制附加数据，非常简单。
总是选择MySQL是因为这货人缘好，Windows、Unix、Linux 和 Mac OS通吃，易于跨平台移植。而SQL Server在非Windows之外的系统上运行，需要繁琐的安装工作。

三、索引的优缺点

1.索引的优点

类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本。
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。

2.索引的缺点

实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的。
虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行 INSERT、 UPDATE、 DELETE。因为更新表时,MSQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

四、索引的分类

1.索引按功能分为

普通索引，没有特别的约束
主键索引，数据唯一不能为空，一个表只能有一个
唯一索引，数据唯一
全文索引，对长文本进行匹配，text
组合索引，将多列组合到一起进行查询

2.按索引结构分为

聚簇索引

索引键的顺序和实际数据的顺序一致

类似：新华字典的拼音目录

一个表只能有一个聚簇索引

主键属于聚簇索引
非聚簇索引

索引键的顺序和实际数据的顺序不一致

类似：新华字典的偏旁目录

一个表可以有多个

五、创建索引

1. 创建索引的方法

创建普通索引

#命令格式如下
CREATE INDEX <索引名> ON tablename (列的列表)
#(列名(length))：length是可选项。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾，容易识别

#先创建数据库price，数据表list
mysql> create database price;
mysql> use price;
#表中插入信息
mysql> insert into list values(5,'gouwa','123456','19132323','杭州','this is 213');
mysql> select * from list;
+------+----------+--------+-------------+---------+-------------+
| id   | name     | cardid | phone       | address | remark      |
+------+----------+--------+-------------+---------+-------------+
|    1 | zhangsan | 123456 | 19181716565 | 南京    | hehe        |
|    2 | wangw    | 123456 | 19181565    | 北京    | hhhhhhh     |
|    3 | liu      | 12345  | 19186543    | 南京    | hehehe      |
|    4 | wangwang | 123456 | 19132323    | 杭州    | this is sb  |
|    5 | gouwa    | 123456 | 19132323    | 杭州    | this is 213 |
+------+----------+--------+-------------+---------+-------------+
5 rows in set (0.00 sec)
#创建索引
mysql> create index phone_index on list(phone);
mysql> show index from list;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| list  |          1 | phone_index |            1 | phone       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

修改表的方式创建

#语法格式
ALTER TABLE 表名 ADD INDEX 索引名 (列名);

mysql> alter table list add index id_index(id);
mysql> select id from list;                        	#查询索引
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

mysql> select id,name from list;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | wangw    |
|    3 | liu      |
|    4 | wangwang |
|    5 | gouwa    |
+------+----------+
5 rows in set (0.00 sec)

创建表时指定索引

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

mysql> create table test(id int(4) not null,name varchar(20) not null,cardid varchar(18) not null,index id_index(id));

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE "test" (
  "id" int(4) NOT NULL,
  "name" varchar(20) NOT NULL,
  "cardid" varchar(18) NOT NULL,
  KEY "id_index" ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 创建唯一索引

直接创建

#语法格式
CREATE UNIQUE INDEX 索引名 ON 表名(列名);

mysql> create unique index name_index on list (name);
show create table member;

修改表方式创建

#语法格式
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

mysql> alter table test add unique name_index (name);

mysql> select name from test;
+--------+
| name   |
+--------+
| laod   |
| sanba  |
| wangba |
+--------+
3 rows in set (0.00 sec)

创建表的时候指定索引

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

create table test3 (id int,name varchar(20),unique id_index (id));
show create table test3;

3. 创建主键索引

创建表的时候指定

#语法格式
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

#用了两种方式创建，一种是在字段中直接指定，第二种是在字段结束后指定
mysql> create table test4 (id int primary key,name varchar(30));
mysql> create table test5 (id int,name varchar(20),primary key (id));

show create table test4;
show create table test5;

修改表方式创建

#语法格式
ALTER TABLE 表名 ADD PRIMARY KEY (列名); 


mysql> alter table list add primary key (id);
mysql> show create table list;

4. 创建全文索引

直接创建索引

#语法格式
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

#全文索引 fulltext，全文只可在 char、varchar、text 类型上创建，且每个表只允许有一个
mysql> create fulltext index remark_index on list (remark);

mysql> show create table list;

修改表方式创建

#语法格式
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

六、查看索引

1. 查看索引的方法

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

2. 索引详细信息解析

七、总结

数据库索引可以协助快速查询表中的数据，但并不是任何字段都需要创建索引

索引分为：
① 普通索引：针对所有字段，没有特殊的需求/规则
② 唯一索引 : 针对唯一性的字段，仅允许出现一次空值
③ 组合索引 (多列/多字段组合形式的索引)
④ 全文索引（varchar char text）
⑤ 主键索引：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引

创建索引的方法：
① 在创建表的时候，直接指定index
② alter修改表结构的时候，进行add 添加index
③ 直接创建索引index

Ms_Xiong

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引

数据库索引可以协助快速查询表中的数据，但并不是任何字段都需要创建索引索引分为：① 普通索引：针对所有字段，没有特殊的需求/规则② 唯一索引 : 针对唯一性的字段，仅允许出现一次空值③ 组合索引 (多列/多字段组合形式的索引)④ 全文索引（varchar char text）⑤ 主键索引：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引创建索引的方法：① 在创建表的时候，直接指定index② alter修改表结构的时候，进行add 添加index。
复制链接

扫一扫