Mysql——索引、引擎

一.关系数据库

关系型数据库是一种基于关系模型的数据库，它将数据组织成一个或多个表格，每个表格中有多个列和行，每行代表一个记录，每列代表一个属性。关系型数据库采用结构化查询语言（SQL）作为标准的查询语言，通过 SQL 可以对表格中的数据进行增删改查等操作。关系型数据库常用于数据存储和管理、事务处理、报表生成、数据分析等领域。常见的关系型数据库包括Oracle、MySQL、SQL Server、PostgreSQL等。

关系数据库分行级数据库和列级储数据库，行级数据库的优点包括：快速访问单个记录，易于扩展，以及可并发访问多个记录，列级数据库是一种数据库类型，它以列为基本存储单元，而不是行。这意味着每个列都有一个独立的数据类型和数据模式。列级数据库相比行级数据库具有更好的可扩展性和可压缩性，因为查询只需要读取感兴趣的列，而不是整个行，这降低了I/O操作和存储开销。一些列级数据库的例子包括Google的Bigtable和Apache HBase。

二.Mysql介绍

MySQL 是一款常用的关系型行级数据库，它支持多种不同的存储引擎，每个存储引擎有其自己的特点和优缺点，可以根据应用程序的需求选择不同的存储引擎。

三.Mysql结构组成

数据库 -> 数据表 -> 行级数据 -> 字段 -> 索引 -> 主键

采用传统的表格的存储方式，数据以行的方式进行存储，在读和查询都十分方便

1.数据库

#查询所有数据库
show databases;
#创建数据库
create database if not exists test default character set utf8;
#进入数据库
use test;
#查看数据库结构
show create database test;
#修改数据库
alter database test default charset = gbk;
#删除数据库
drop database test;

2.数据表 

#创建数据库
create database test default charset = utf8;
#选择数据库
use test;
#创建数据表
create table test_table(
id int,
username varchar(20),
password varchar(20)
) engine = innodb default charset = utf8;
#查看数据表
show tables;
#查看数据库结构
show create table test_table;
desc test_table;
#修改数据库
alter table test_table add age int first username;
#删除数据库
drop database test;
#删除某个字段
drop table test_table drop username;
#修改数据表的编码格式
alter table test_table default charset=gbk;
#修改表的引擎
alter table test_table engine=myisam;
#移动数据表名称
alter table test.test_table to test1.test.test_table;
#重命名数据表
rename table test_table to test_1
alter table test_table rename test_1

3,行级数据

#创建数据库
create database test default charset = utf8;
#选择数据库
use test;
#创建数据表
create table test_table(
id int,
username varchar(20),
password varchar(20)
) engine = innodb default charset = utf8;
#查询数据表
select username from test_table where 1 = 1;
#插入数据
insert into test_table (username, password) values ('1', '2');
#更新数据
update test_table set username='3', password='3' where username = '1';
#删除数据
delete from test_table where username = '3';
#删除数据并且初始化自增事务
truncate test_table;

4.字段

#创建数据库
create database test default charset = utf8;
#选择数据库
use test;
#创建数据表
create table test_table(
id int,
username varchar(20),
password varchar(20)
) engine = innodb default charset = utf8;
#1.整数类型
#tinyint,smaillint,mediumint,int,bigint
#2.小数类型
#float、double
#3.定点类型
#decimal、numeric
#4.字符串类型
#char、varchar
#5.时间类型
#date,time,datetime,timestamp,year
#6.大文本类型
#text类型
#7.枚举集合类型
#enum、set

5.索引

MySQL索引是表中一种特殊的数据结构，可帮助MySQL在查询时更快地检索数据。它是一个用于加速数据库查询速度的关键技术。索引可以理解为是一个指针，将表中某些列的数据进行排序，并将这些数据以一种特定的数据结构存储。当查询语句需要查询某些列的数据时，MySQL可以根据这些索引快速定位到要查询的数据，而不需要扫描整个表。索引可以大大提高查询的效率，但是索引的建立、维护和使用都需要付出一定的代价，因此在设计索引时需要考虑到数据的查询频率、数据的更新频率以及索引的大小等因素。

主要索引B+Tree索引、Hash索引

B+Tree索引

非叶子结点存储索引，叶子节点存储数据

叶子结点在同级存储，

Hash索引

Hash索引是一种快速查找数据的索引方式。它基于哈希函数，将每个关键字（或关键字的某个部分）映射到一个桶中，每个桶中存储了一组具有相同哈希值的记录。在查询时，哈希函数可以快速地将要查找的关键字映射到对应的桶中，然后在桶中进行线性查找或其他方式的查找，以确定是否存在与关键字匹配的记录。由于哈希函数的高效性，Hash索引通常能够以较快的速度响应查询请求。但是，它也存在一些限制，如无法支持范围查询和排序等操作。

回表

MySQL中的回表指的是在使用索引查询时，MySQL需要在数据表中查找到对应的数据行时，需要访问磁盘或内存中的数据行，这个过程就被称为回表。

回表是因为B+树索引中，叶子节点保存的是数据行的地址，而不是数据本身。当MySQL查找到索引后，需要再次根据叶子节点中保存的地址找到对应的数据行，这个过程就需要进行一次回表操作。

回表操作的代价比较高，假如查询语句中要查询的字段不在索引中，那么MySQL需要进行回表操作才能获取到完整的数据行，这个过程会消耗大量的时间和资源。因此，在进行MySQL的优化时，需要尽量减少回表的次数，可以采用使用覆盖索引、减少查询字段等方法来避免回表操作。

索引类型

B-Tree索引：最常用的索引类型，适用于精确匹配和范围查找。

哈希索引：适用于等值查找，但不支持范围查找，也不支持排序。

全文索引：适用于全文搜索，支持自然语言查询和布尔查询。

空间索引：适用于地理位置数据的范围查询。

全文空间索引：结合了全文索引和空间索引的功能，适用于地理位置文本搜索。

前缀索引：只对索引中的前几个字符进行匹配，适用于字符串类型的索引。

组合索引：将多个字段建立在同一个索引上，适用于多个字段的组合查询。

唯一索引：保证索引列的唯一性。

主键索引：唯一标识一条记录的索引，每个表只能有一个主键索引。

语句

#创建数据库
create database test default charset = utf8;
#选择数据库
use test;
#创建数据表
create table test_table(
id int,
username varchar(20),
password varchar(20)
) engine = innodb default charset = utf8;
#创建索引
alter table test_table add index index_test(username);
#创建联合索引
alter table test_table add index index_f_test (username, password);
#索引重命名
alter table test_table rename index index_test to index_new_test;
#删除索引
alter table test_table drop index index_name;
#添加外键索引
alter table test_table add foreign key (username) references test_b(b_id);
#添加主键索引
alter table test_table add primary key (id);

6.主键 

MySQL中的主键是一种约束，它用于标识表中的唯一记录。主键必须是唯一的、非空的，并且不能被重复

#创建数据库
create database test default charset = utf8;
#选择数据库
use test;
#创建数据表
create table test_table(
id int primary key,
username varchar(20),
password varchar(20)
) engine = innodb default charset = utf8;
#添加外键索引
alter table test_table add foreign key (username) references test_b(b_id);
#索引重命名
alter table test_table rename index index_test to index_new_test;
#删除索引
alter table test_table drop index index_name;

7.备注

参考 

https://zhuanlan.zhihu.com/p/530634493

https://www.cnblogs.com/zwj-199306231519/articles/14403099.html

感谢大佬们的贡献