MySql 存储引擎和索引相关知识总结

2023-07-22 18:33:03 74

存储引擎

什么是数据库存储引擎？

数据库引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同的数据库引擎，可以获得特定的功能

如何查看引擎？

--如何查看数据库支持的引擎
showengines;

--查看当前数据的引擎：
showcreatetable表名\G

--查看当前库所有表的引擎：
showtablestatus\G

建表时指定引擎

createtableyingqin(idint,namevarchar(20))engine='InnoDB';

修改表的引擎

altertable表名engine='引擎名称';

修改默认引擎

vi/etc/my.cnf （配置文件地址根据安装情况）
[mysqld]下面
default-storage-engine=MyIsAM
记得保存后重启服务

MyISAM与InnoDB的区别

MyISAM：支持全文索引（fulltext）；不支持事务;表级锁;保存表的具体行数;奔溃恢复不好。

Innodb：支持事务；以前的版本是不支持全文索引，但在5.6之后的版本就开始支持这个功能了；行级锁（并非绝对，当执行sql语句时不能确定范围时，也会进行锁全表，例如：updatetablesetid=3wherenamelike'a%';）；不保存表的具体行数；奔溃恢复好。

什么时候选择什么引擎比较好

MyISAM：

不需要用到事务的时候
做很多count计算

InnoDB：

可靠性要求高的，或者要求支持事务
想要用到外键约束的时候（MyISAM建立的外键是无效的）

推荐用InnoDB

索引

什么是索引？

索引是一个单独的，存储在磁盘中上的数据库结构，它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找出在某列或多列中有特定值的行。

索引的优点：

通过创建唯一索引，来保证数据库表中的每一行数据的唯一性。
可以加快数据的检索速度。
可以保证表数据的完整性与准确性

索引的缺点：

索引需要占用物理空间。
对表中的数据进行改动时，索引也需要跟着动态维护，降低了数据的维护速度。

索引的常见类型：

index：普通索引
unique：唯一索引
primarykey：主键索引
foreignkey：外键索引
fulltext:全文索引
组合索引

普通索引与唯一索引

什么是普通索引？

普通索引（index）顾名思义就是各类索引中最为普通的索引，主要任务就是提高查询速度。其特点是允许出现相同的索引内容，允许空（null）值

什么是唯一索引？

唯一索引：（unique）顾名思义就是不可以出现相同的索引内容，但是可以为空（null）值

如何创建普通索引或者唯一索引？

--创建表的时候创建
createtabletest(
idint(7)zerofillauto_incrementnotnull,
usernamevarchar(20),
servnumbervarchar(30),
passwordvarchar(20),
createtimedatetime,
unique(id)
)DEFAULTCHARSET=utf8;

--直接为表添加索引
--语法：altertable表名addindex索引名称(字段名称);
--注意：假如没有指定索引名称时，会以默认的字段名为索引名称
altertabletestadduniqueunique_username(username);

--直接创建索引
--语法：createindex索引on表名(字段名);
createindexindex_createtimeontest(createtime);

查看索引

--语法：showindexfrom表名\G
showindexfromtest\G

如何删除索引

--语法：dropindex索引名称on表名;
dropindexunique_usernameontest;
--语法：altertable表名dropindex索引名;
altertabletestdropindexcreatetime;

主键索引

什么是主键索引？

把主键添加索引就是主键索引，它是一种特殊的唯一索引，不允许有空值，而唯一索引（unique是允许为空值的）。指定为“PRIMARYKEY”

主键：主键是表的某一列，这一列的值是用来标志表中的每一行数据的。注意：每一张表只能拥有一个主键

创建主键：

--1）创建表的时候创建

--2）直接为表添加主键索引
--语法：altertable表名addprimarykey(字段名);
altertabletestaddprimarykey(id);

删除主键：

--语法：altertable表名dropprimarykey;
altertabletestdropprimarykey;

注意：在有自增的情况下，必须先删除自增，才可以删除主键

--删除自增：
altertabletestchangeididint(7)unsignedzerofillnotnull;

全文索引

什么是全文索引？

全文索引是将存储在数据库中的文章或者句子等任意内容信息查找出来的索引，单位是词。全文索引也是目前搜索引擎使用的一种关键技术。指定为fulltext

--创建练习表的sql：
createtablecommand(
idint(5)unsignedprimarykeyauto_increment,
namevarchar(10),
instructionvarchar(60)
)engine=MyISAM;

--插入数据sql：
insertintocommandvalues('1','ls','listdirectorycontents');
insertintocommandvalues('2','wc','printnewline,word,andbytecountsforeachfile');
insertintocommandvalues('3','cut','removesectionsfromeachlineoffiles');
insertintocommandvalues('4','sort','sortlinesoftextfiles');
insertintocommandvalues('5','find','searchforfilesinadirectoryhierarchy');
insertintocommandvalues('6','cp','复制文件或者文件夹');
insertintocommandvalues('7','top','displayLinuxprocesses');
insertintocommandvalues('8','mv','修改文件名，移动');
insertintocommandvalues('9','停止词','is,not,me,yes,no...');

添加全文索引：

--1）创建表的时候创建全文索引

--2）通过alter添加
altertablecommandaddfulltext(instruction);

使用全文索引：

--语法：select*from表名wherematch(字段名)against('检索内容');
select*fromcommandwherematch(instruction)against('sections');

查看匹配度：

select*fromcommandwherematch(instruction)against('directory');

停止词：

出现频率很高的词，将会使全文索引失效。

inbooleanmode模式：

inbooleanmode：意思是指定全文检索模式为布尔全文检索（简单可以理解为是检索方式）

--语法：select*from表名wherematch(字段名)against('检索内容'inbooleanmode);
select*fromcommandwherematch(instruction)against('direct*'inbooleanmode);

注意点：使用通配符*时，只能放在词的后边，不能放前边。

删除全文索引：

altertablecommanddropindexinstruction;

注意点总结：

一般情况下创建全文索引的字段数据类型为char、varchar、text。其它字段类型不可以
全文索引不针对非常频繁的词做索引。比如is，no，not，you，me，yes这些，我们称之为停止词
对英文检索时忽略大小写

外键约束

什么是外键？

外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性。

添加外键约束：

--语法：foreignkey(字段名)references关联的表名(关联表的字段名)
--注意：主键跟外键的字段类型一定要相同

--createtable的方法：
CREATETABLE`employee`(
`empno`int(11)NOTNULLCOMMENT'雇员编号',
`ename`varchar(50)DEFAULTNULLCOMMENT'雇员姓名',
`job`varchar(30)DEFAULTNULL,
`mgr`int(11)DEFAULTNULLCOMMENT'雇员上级编号',
`hiredate`dateDEFAULTNULLCOMMENT'雇佣日期',
`sal`decimal(7,2)DEFAULTNULLCOMMENT'薪资',
`deptnu`int(11)DEFAULTNULLCOMMENT'部门编号',
PRIMARYKEY(`empno`),
foreignkey(deptnu)referencesdept(deptnu)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

--altertable的方法：
altertableemployeeaddforeignkey(deptnu)referencesdept(deptnu);

删除外键约束：

注意：在干掉外键索引之前必须先把外键约束删除，才能删除索引

mysql>altertableemployeedropindexdeptnu;
ERROR1553(HY000):Cannotdropindex'deptnu':neededinaforeignkeyconstraint
mysql>
mysql>altertableemployeedropforeignkeyemployee_ibfk_1;
QueryOK,0rowsaffected(0.01sec)
Records:0Duplicates:0Warnings:0

mysql>
mysql>altertableemployeedropindexdeptnu;
QueryOK,0rowsaffected(0.01sec)
Records:0Duplicates:0Warnings:0

注意点总结：

俩个表，主键跟外键的字段类型一定要相同
要使用外键约束表的引擎一定得是InnoDB引擎，MyISAM是不起作用的
在干掉外键索引之前必须先把外键约束删除，才能删除索引

联合索引

什么是联合索引？

联合索引又称组合索引或者复合索引，是建立在俩列或者多列以上的索引。

创建联合索引

--语法：altertable表名addindex(字段1,字段2,字段3);
altertabletestaddindex(username,servnumber,password);

删除联合索引

--语法：altertabletestdropindex索引名;
altertabletestdropindexusername;

为什么要使用联合索引，而不使用多个单列索引？

联合索引的效率远远高于单列索引。假如创建了三个单列索引，并且查询条件中也存在这三列，但是MySQL只会选择最优的列索引，而不会三个索引都用上

联合索引的最左原则

以上面的索引为例，查询条件中必须有username，才会去使用这个索引，否则不会去使用该索引

注意点总结：

索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费。
当表的数据量很大的时候，可以考虑建立索引。
表中经常查数据的字段，可以考虑建立索引。
想要保证表中数据的唯一性，可以考虑建立唯一索引。
想要保证俩张表中的数据的完整性跟准确性，可以考虑建立外键约束。
经常对多列数据进行查询时，可以考虑建立联合索引。

以上就是MySql存储引擎和索引相关知识总结的详细内容，更多关于MySql存储引擎和索引的资料请关注毛票票其它相关文章！

MySql 存储引擎和索引相关知识总结

热门推荐

随机推荐