我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MySQL(7)-mysql索引和存储引擎

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MySQL(7)-mysql索引和存储引擎

     本篇博客讲的是MySQL的索引的功能和使用 , 以及存储引擎的基本简介


 一. mysql索引


索引的简介和作用


索引在MySQL中叫做"键" , 是存储引擎用于快速找到记录的一种数据结构 . 索引对良好的性能非常关键 , 尤其是当表中的数据量越来越大时 , 索引对于性能的影响愈来愈发重要 .

作用 : 通过一定的算法将数据库中的记录按一定的规律进行分组 , 这样查信息时可以缩小数据的搜索范围 , 从而提高溜了查询效率

生活实例来说 , 索引就好像书的目录 , 清单上的列表 ; 好比人去吃火锅 , 当菜单拿到客户手上 , 客户可以根据菜单上的分类(海鲜 , 蔬菜类 , 肉类 , 饮料类等) , 根据自己的口味能第一时间找到自己想吃的菜 .


索引的分类


索引可分为 : 普通索引 , 唯一索引 , 全文索引 , 单列索引 , 多列索引 , 空间索引


语法格式:


CREATE TABLE 表名 (
       字段名1  数据类型 [完整性约束条件…],
       字段名2  数据类型 [完整性约束条件…],
       [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
[索引名]  (字段名[(长度)]  [ASC |DESC])
);


示例 : 

创建一个INDEX普通索引

CREATE TABLE dept(
     dept_id INT,
     dept_name VARCHAR(30) ,
     comment VARCHAR(50),
     index  (dept_name)              # 将表中的dept_name字段指定为普通索引字段
);

MySQL(7)-mysql索引和存储引擎


创建一个UNIQUE唯一索引:

CREATE TABLE dept2 (
     dept_id INT,
     dept_name VARCHAR(30) ,
     comment VARCHAR(50),
     UNIQUE INDEX  (dept_name)
);

相比普通索引来说 , 比上面的索引选项多了一个unique选项


创建一个全文索引;

CREATE TABLE dept3 (
     dept_id INT,
     dept_name VARCHAR(30) ,
     comment VARCHAR(50),
     log text,
FULLTEXT INDEX  (log)
)engine=myisam;

: 只有MYISAM存储引擎支持全文索引,innodb存储引擎不支持全文索引

MySQL(7)-mysql索引和存储引擎


创建多列索引

CREATE TABLE dept13 (
     dept_id INT,
     dept_name VARCHAR(30) ,
     comment VARCHAR(50),
INDEX  (dept_name, comment)
);

相比普通索引来说 , 就是将多个字段设置为索引


对已存在的表创建索引


语法一:

CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名  ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

创建普通索引示例:此方法要指定索引名称
CREATE INDEX index_dept_name ON dept6  (dept_name);

创建唯一索引示例:
CREATE UNIQUE INDEX index_dept_name ON dept6 (dept_name);

创建全文索引示例:
CREATE FULLTEXT INDEX index_dept_name ON dept6 (dept_name);

创建多列索引示例:
CREATE INDEX index_dept_name_ comment ON dept6 (dept_name, comment);


语法二:

ALTER TABLE在已存在的表上创建索引:
ALTER  TABLE  表名  ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
     索引名 (字段名[(长度)]  [ASC |DESC]) ;


管理索引:


查看索引: show  create  table  表名\G

测试索引: explain  select  *  from  表名  where  字段名='xx';

删除索引: drop  index  索引名  on  表名


索引检测实例:


要求 : 创建一个school的数据库 , 创建一张t2表 , 用存储过程脚本t2表插入1000W条数据 , 然后查询t2数据看看花费了多长时间 ; 再为t2创建一个索引 , 再次查看数据看看所花费的时间 


准备:

           create  database  school                                          #创建school数据库

           create table school.t2(id int,name varchar(30));    #创建一张t2表 , 里面记录id号和名字


定义一个插入1000W条数据的存储过程 , 并调用此存储过程

mysql> delimiter $$                            //设置命令的界定符(也称为结束符)
mysql> create procedure autoinsert1()          //创建autoinsert1这个存储过程(类似于shell脚本)
    -> BEGIN
    -> declare i int default 1;
    -> while(i<100000)do
    -> insert into school.t2 values(i,'ccc');
    -> set i=i+1;
    -> end while;
    -> END$$
mysql> delimiter ;
       call  atuoinsert1();

MySQL(7)-mysql索引和存储引擎


分两次查询数据 , 对比所花的时间(实验中途插入1000W条数据花了33分钟 , 插100W估计就能看出效果了):

MySQL(7)-mysql索引和存储引擎


 从上述实验可以看到 , 在一个存放1000W的表中 , 查询一条数据跟创建索引后查询一条数据相差了4S的时间 , 随着数据更大 , 查询时间也会不断增大 , 所以足以证明 , 创建索引会大大提高MySQL的查询工作效率!!!




二. MySQL存储引擎介绍


了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)

在Oracle 和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的存储引擎。


1. 查看存储引擎
SHOW ENGINES;
SHOW ENGINES\G               查看MYSQL支持的存储引擎
SHOW VARIABLES LIKE 'storage_engine%';    查看当前的存储引擎
SHOW VARIABLES LIKE 'auto_inc%';       查看自增长的设置状态
show global variables   like  '%connet%'   查看connet环境变量设置
mysql> show variables\G          查看所有的环境变量
show variables当前的会话
show global variables\G全局


2. 选择存储引擎
方法1.
mysql> create table innodb1(
-> id int
-> )engine=innodb;
mysql> show create table innodb1;

create tables test100(id init)engine=inodb;

方法2.
/etc/my.cnf
[mysqld]     在此行下添加下面的一行内容
default-storage-engine=INNODB



MySQL常用的存储引擎


MyISAM存储引擎
由于该存储引擎不支持事务、也不支持外键,所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。

InnoDB存储引擎(MYSQL默认用此存储引擎)
由于该存储引擎在事务上具有优势,即支持具有提交、回滚及崩溃恢复能力等事务特性,所以比MyISAM存储引擎占用更多的磁盘空间。
因此当需要频繁的更新、删除操作,同时还对事务的完整性要求较高,需要实现并发控制,建议选择。

MEMORY
MEMORY存储引擎存储数据的位置是内存,因此访问速度最快,但是安全上没有保障。适合于需要快速的访问或临时表。

BLACKHOLE
黑洞存储引擎,可以应用于主备复制中的分发主库。
使用BLACKHOLE存储引擎的表不存储任何数据,但如果mysql启用了二进制日志,SQL语句被写入日志(并被复制到从服务器)。这样使用BLACKHOLE存储引擎的mysqld可以作为主从复制中的中继重复器或在其上面添加过滤器机制。




免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL(7)-mysql索引和存储引擎

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

MySql中的存储引擎和索引

这篇文章主要介绍了MySql中的存储引擎和索引,文章围绕主题展开详细的内容介绍,具有一定的参考价值,需要的小伙伴可以参考一下
2022-11-13

MySQL学习笔记(7):存储引擎

本文更新于2019-06-23,使用MySQL 5.7,操作系统为Deepin 15.4。目录InnoDBMyISAMMERGEMEMORYNDB常用存储引擎对比和大多数数据库不同,插件式存储引擎是MySQL最重要的特性之一。InnoDBInnoDB表提供事务
MySQL学习笔记(7):存储引擎
2020-04-30

mysql-存储引擎

1.存储引擎完整的建表语句:CREATE TABLE dept (DEPTNO int(2) NOT NULL,DNAME varchar(14) DEFAULT NULL,LOC varchar(13) DEFAULT NULL,PRIMARY KEY (D
mysql-存储引擎
2020-03-17

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

存储引擎什么是数据库存储引擎? 数据库引擎是数据库底层软件组件,不同的存储引擎提供不同的存储机制,索引技巧,锁定水平等功能,使用不同的数据库引擎,可以获得特定的功能 如何查看引擎?--如何查看数据库支持的引擎 show engines;
2022-05-11

MySQL 存储引擎

简介:存储引擎是数据库底层软件组件,数据库管理系统使用数据引擎进行创建、查询、更新和删除操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。   常见分类有:InnoDB、MyISAM、Memory等;一、InnoDB  1. 是事务型存储引擎,
MySQL 存储引擎
2017-04-10

MYSQL存储引擎

存储引擎InnoDB     (1) InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询
2021-04-17

Mysql--存储引擎

1.文件系统: 1.1 操作系统组织和存取数据的一种机制。 1.2 文件系统是一种软件。 2.文件系统类型:ext2 3 4 ,xfs 数据 2.1 不管使用什么文件系统,数据内容不会变化 2.2 不同的是,存储空间、大小、速度。 3.MySQ
Mysql--存储引擎
2021-09-03

MySQL 存储引擎

存储引擎DBMS使用存储引擎进行数据的创建、查询、更新、删除操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎还可以获得特定的功能。MySQL提供了多种存储引擎,最常见的有3种。     1、MyISAM MySQL5.5之前
MySQL  存储引擎
2015-11-16

【MySQL】存储引擎

文章目录 1. 前言2. MySQL的体系结构3. 存储引擎简介4. InnoDB介绍5. MyISAM介绍6. Memory7. 存储引擎的选择8. 总结 1. 前言 存储引擎是 MySQL 的核心组件之一,负责数据的存储和管
2023-08-16

MySQL 存储引擎(2)

首先我们带着下边三个问题来认识存储引擎存储引擎在MySQL中的作用是什么?MySQL都有哪些存储引擎SQL又与存储引擎有什么关系?存储引擎在MySQL中的作用是什么?顾名思义,存储引擎就是用于存储我们的数据的。在关系型数据库中我们一般将数据库存放在表中(Tab
MySQL 存储引擎(2)
2016-06-21

mysql federated存储引擎

通过federated存储引擎建立远程连接表# 开启federated存储引擎SHOW ENGINES ;MyISAM YESInnoDB DEFAULT...FEDERATED YES# 创建远程存储引擎表CREATE TABL
mysql federated存储引擎
2020-02-03

编程热搜

目录