理论+实验:MySQL索引、事物与存储引擎 PostgreSQL JOIN 多表查询 TP框架实现Excel批量导入数据库数据 MySQL中的列转行 这次是真拯救了我,MySQL索引优化,explain讲得非常清楚了 Oracle快速入门(PLSQL编程) MySQL字符串拼接、截取 MySQL事务管理及存储引擎 《浪姐》万茜点赞宁静、郁可唯黑贴坐实?盗号者和程序员是背锅侠 程序员被公司辞退12天后,前领导要求回公司讲清代码,结果懵了 易语言大漠多线程foobar在游戏多窗口中时时显示输出信息 非科班,自学两年,复盘两个月,侥幸拿到头条、阿里offer 2020年全国数学建模大赛B题源代码以及模型的建立 (2) 2020年数学建模-校园供水系统智能管理 windows破解锁屏密码(亲测有效:再也不怕别人锁屏防你啦!) 清华大佬力荐的JVM学习路线+实战笔记+阿里真题,嚼碎吃透吊打面试官 打造多模块+高可用+高扩展Spring Cloud版分布式电商项目源码分享 Python爬虫入门教程 89-100 定个小目标,先用Python爬个一亿B站用户 五大分布式事务,你了解多少? 2020-09-12 九大核心专题,630页内容,熬夜23天吃透,我收割了3个大厂offer 防抖节流 防抖和节流 函数节流 debounce throttle 每天补充一点 一些零碎的小知识点 JavaScript作用域和作用域链知多少 01-jquery概述 简单概述JSONP原理 02-$符号-jquery与js相互转换 遇见 vue.js --------阿文的vue.js学习笔记(2)----- 基本使用 全国鞋服行业首个5G专项应用落地柒牌 自动搬运作业提升效率150% 大数据系统提高生产效率超10% [网络安全自学篇] 九十四.《Windows黑客编程技术详解》之提权技术(令牌权限提升和Bypass UAC) 鸿蒙OS 2.0 开源蹭热浅读 蚂蚁三面滑铁卢!遭分布式截胡,靠这些笔记潜修30天,挺进京东 【高并发】Redis如何助力高并发秒杀系统,看完这篇我彻底懂了!! K8s概述:几种集群方案的对比 Linux到底该怎么学?RHCA架构师整理了300页学习笔记 到了2020年,技术水平到底需要达到怎样的程度才能成为顶级的阿里P8架构师 Linux怎么学?一张思维导图带你深入Linux核心原理 金九银十首战告捷!凭借这份Alibaba爆款“面试宝典”成功斩获美团Offer 大数据杀熟:我投之以元宝,它报之以砍刀! “物联网加持”下的社区长啥样儿? 潘云鹤院士:大数据智能是人工智能2.0的核心组成部分 防小孩和老人走失,定位精度达1厘米?上海社区为先进物联网产品提供落地场景 技术老兵十年专攻MySQL编写了763页核心总结,90MySQL问题全解 【Jenkins自动化部署】Windows节点Apache+Django服务自动化构建 Mybatis 使用通用 mapper 正道的光!阿里爆款Jenkins+K8s笔记终于全网开源了 不要死磕Java并发了,阿里P7架构师带你深入剖析synchronized的实现原理 EtherNet/IP协议基础知识(Part 1) CGB2005-京淘13 思科 OSPF协议简单配置与分析
您的位置:首页 >大数据 >

理论+实验:MySQL索引、事物与存储引擎

个人整理,感谢观看

一、索引1.1 概念1.2 索引的作用1.3 索引的分类二、创建索引的原则依据三、查看、删除、索引的方法四、事务4.1 概念4.2 事务的ACID特点4.3 事务控制语句五、存储引擎5.1 介绍5.2 MyISAM5.3 MyISAM适用的生产场景5.4 InnoDB介绍5.5 适用生产场景分析5.6 企业选择存储引擎依据六、修改存储引擎方法

一、索引

1.1 概念

排序的列表,索引值和这个值所对应的位置,物理地址;方便查询,通过物理地址就可以找到所需数据;表中一列或若干列值排序的方法;占用额外的磁盘空间;

1.2 索引的作用

快速定位,加快查询速率;当数据表很大后查询多个表示,效率提高;降低数据库的IO成本,降低数据库的排序成本;创建唯一索引保证数据表数据的唯一性;加快数据表与表之间的连接;使用分组和排序时,可大大的减少组合排序时间;

1.3 索引的分类

普通索引
最基本的索引类型,没有唯一性之类的限制创建普通的索引的方式 1、用create方法创建普通索引
mysql> create index index_age on info (age);

2、用alter方法创建普通索引

mysql> alter table info add index index_age (age);

3、创建表的时候创建普通索引

mysql> create table user (-> id int(4) not null primary key auto_increment,-> name varchar(10) not null,-> score decimal not null,-> hobby int(2) not null default '1',-> index index_scrore (score));
唯一索引
与 ‘普通索引’ 基本相同区别普通索引:索引列的所有值只能出现一次,必须唯一 1、用create unique方法,创建唯一索引
mysql> create unique index unique_name on info (name);

2、用alter table方法创建唯一索引

mysql> alter table info add unique index_name (name);

3、用新建表的方法创建唯一索引

mysql> create table user1 (-> id int(4) not null primary key auto_increment,-> name varchar(10) not null,-> score decimal not null,-> hobby int(2) not null default '1',-> unique index index_scrore (score));
主键索引
是一种特殊的唯一索引,指定为‘primary key’ (小写了)一个表只能有一个主键,不允许有空值
mysql> create table user2 (-> id int(4) not nullauto_increment,-> name varchar(10) not null,-> age int(3) not null,-> primary key (`id`));
组合索引(单列与多列)
可以是单列上创建的, 也是可以是多列上创建的最左原则,从左往右依次执行
mysql> create table user3 (-> name varchar(10) not null,-> age int(3) not null,-> sex tinyint(1) not null,-> index user3(name,age,sex));
全文索引
mysql从3.23.23版本开始支持全文索引和全文检索索引类型为fulltext可以在 CHAR,VARCHAR或者TEXT类型的列上创建
mysql> create table user4 (-> id int(11) not null auto_increment,-> tile char(255) character set utf8 collate utf8_general_ci not null,-> content text character set utf8 collate utf8_general_ci not null,-> primary key (`id`),-> fulltext (content));

二、创建索引的原则依据

表的主键,外键必须有索引记录数超过300行的表应该有索引经常与其他表进行连接的表,在连接字段上建立索引经常出现在 where 子句中的字段,特别是大表的字段选择性比较高的字段上小字段也可以建立唯一性太差的字段不适合建立索引更新太频繁的字段不适合创建索引

三、查看、删除、索引的方法

查看索引
>showindex from 表名;>showkeys from 表名;例如:show index from IT_salary;show keys from IT_salary;
删除索引
>dropindex 索引名on 表名;>altertable表名drop 索引名;例如:drop index index_puid on info;alter table info drop index index_puid;

四、事务

4.1 概念

一种保护机制吧,一个操作序列,包含了一组数据库操作命令,并且把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这组数据库命令要么都执行,要么都不执行不可分割的工作逻辑单元,在数据库系统上执行并发操作时,事务是最小的控制单元适用于多用户同时操作的数据库系统的场景,数据比较敏感的关键性操作等。如:银行,证券交易系统…通过事务的整体性以保证数据的一致性!!

4.2 事务的ACID特点

原子性

事务是一个完整的操作,事务的各元素是不可分的 事务中的所有元素必须作为一个整体提交或回滚 如果事务中的任何元素失败,则整个事务将失败

一致性
当事务完成时,数据必须处于一致状态在事务开始前,数据库中存储的数据处于-致状态在正在进行的事务中,数据可能处于不一致的状态当事务成功完成时,数据必须再次回到已知的一致状态
隔离性
对数据进行修改的所有并发事务是彼此隔离的,表明事务必须是 独立的,它不应以任何方式依赖于或影响其他事务修改数据的事务可在另一个使用相同数据的事务开始之前访问这 些数据,或者在另一个使用相同数据的事务结束之后访问这些数 据
持久性
指不管系统是否发生故障,事务处理的结果都是永久的一旦事务被提交,事务的效果会被永久地保留在数据库中

4.3 事务控制语句

Mysql事务默认是自动提交的,当sql语句提交时事务便自动提交控制语句
BEGIN或START TRANSACTIONCOMMITROLLBACKSAVEPOINT 事务名RELEASE SAVEPOINT 事务名ROLLBACK TO 事务名SET TRANSACTION
控制语句解释:(按自上到下顺序解释:)

显示开启一个事务; 提交事务,并使已对数据库进行的所有修改变为永久性; 回滚, 会结束用户的事务,并撤销正在进行的所有未提交的修改; 允许在事务中创建一个保存点,一个事务中可以有多个 删除一个事务的保存点,当没有指定保存点是,执行该语句会显示异常 把事务会滚到保存的事务点; 用来设置事务的隔离级别。lnnonDB 存储引擎提供事务的隔离级别,有read uncommitted , read committed …

先搭建库,表,添加数据;
创建数据库:mysql> create database uou;Query OK, 1 row affected (0.00 sec)创建数据表:mysql> create table lol(-> id int(4) not null primary key auto_increment,-> name varchar(10) not null,-> address varchar(50) default 'taihe',-> age int(3) not null);在表中添加数据:mysql> insert into lol-> (name,address,age) values-> ('小明','北京',23),-> ('小刚','南京',20);查看数据记录mysql> select * from lol;+----+--------+---------+-----+| id | name | address | age |+----+--------+---------+-----+|1 | 小明 | 北京|23 ||2 | 小刚 | 南京|20 |+----+--------+---------+-----+
事务操作
mysql> begin; Query OK, 0 rows affected (0.00 sec)mysql> insert into lol-> (name,address,age)-> values-> ('小红','深圳',22);mysql> select * from lol;+----+--------+---------+-----+| id | name | address | age |+----+--------+---------+-----+|1 | 小明 | 北京|23 ||2 | 小刚 | 南京|20 ||3 | 小红 | 深圳|22 |+----+--------+---------+-----+3 rows in set (0.00 sec)mysql> savepoint a;#保存到一个事务点名a;Query OK, 0 rows affected (0.00 sec)mysql> insert into lol #写入数据验证-> (name,address,age)-> values-> ('小兰','上海',25);Query OK, 1 row affected (0.00 sec)mysql> select * from lol;+----+--------+---------+-----+| id | name | address | age |+----+--------+---------+-----+|1 | 小明 | 北京|23 ||2 | 小刚 | 南京|20 ||3 | 小红 | 深圳|22 ||4 | 小兰 | 上海|25 |+----+--------+---------+-----+4 rows in set (0.00 sec)mysql> rollback to a;#回滚到保存点a;Query OK, 0 rows affected (0.00 sec)mysql> select * from lol;+----+--------+---------+-----+| id | name | address | age |+----+--------+---------+-----+|1 | 小明 | 北京|23 ||2 | 小刚 | 南京|20 ||3 | 小红 | 深圳|22 |+----+--------+---------+-----+3 rows in set (0.00 sec)mysql> insert into lol#重新写入数据-> (name,address,age)-> values-> ('小兰','上海',25);Query OK, 1 row affected (0.00 sec)mysql> commit;#提交数据之后,保证一致性,才会显示!!退出当前管理用户账号,从新登录进来,会看到数据已经写进去!

五、存储引擎

5.1 介绍

MySQL中的数据用各种不同的技术存储在文件中,每 一种技术都使用不同的存储机制、索引技巧、锁定水平 并最终提供不同的功能和能力,这些不同的技术以及配 套的功能在MySQL中称为存储引擎

存储引擎是MySQL将数据存储在文件系统中的存储方 式或者存储格式

MySQL常用的存储引擎 ●MyISAM ●InnoDB

MySQL数据库中的组件,负责执行实际的数据I/0操作

MySQL系统中,存储引擎处于文件系统之上,在数据 保存到数据文件之前会传输到存储引擎,之后按照各个 存储引擎的存储格式进行存储

5.2 MyISAM

MyISAM不支持事务,也不支持外键访问速度快对事务完整性没有要求MyISAM在磁盘.上存储成三个文件 ●.frm文件存储表定义 ●数据文件的扩展名为.MYD (MYData) ●索引文件的扩展名是.MYI (MYIndex)

■表级锁定形式,数据在更新时锁定整个表 ■数据库在读写过程中相互阻塞 ●会在数据写入的过程阻塞用户数据的读取 ●也会在数据读取的过程中阻塞用户的数据写入 ■数据单独写入或读取,速度过程较快且占用资源相对少 ■MyIAM支持的存储格式 ●静态表 ●动态表 ●压缩表

5.3 MyISAM适用的生产场景

公司业务不需要事务的支持单方面读取或写入数据比较多的业务MyISAM存储引擎数据读写都比较频繁场景不适合使用读写并发访问相对较低的业务数据修改相对较少的业务对数据业务-致性要求不是非常高的业务服务器硬件资源相对比较差

5.4 InnoDB介绍

支持4个事务隔离级别行级锁定,但是全表扫描仍然会是表级锁定MyISAM存储引擎数据读写都比较频繁场景不适合使用读写并发访问相对较低的业务数据修改相对较少的业务对数据业务-致性要求不是非常高的业务服务器硬件资源相对比较差

5.5 适用生产场景分析

业务需要事务的支持行级锁定对高并发有很好的适应能力,但需确保查询是 通过索引来完成业务数据更新较为频繁的场景 如:论坛,微博等业务数据一致性要求较高 如:银行业务硬件设备内存较大,利用Innodb较好的缓存能力来提 高内存利用率,减少磁盘IO的压力

5.6 企业选择存储引擎依据

需要考虑每个存储引擎提供的核心功能及应用场景支持的字段和数据类型 所有引擎都支持通用的数据类型 但不是所有的引|擎都支持其它的字段类型,如二进制对象锁定类型:不同的存储引擎支持下同级别的锁定 表锁定 行锁定索引的支持 建立索弓|在搜索和恢复数据库中的数据时能显著提高性能 不同的存储弓|擎提供不同的制作索引|的技术 有些存储引擎根本不支持索引事务处理的支持 提高在向表中更新和插入信息期间的可靠性 可根据企业业务是否要支持事务选择存储弓|擎

六、修改存储引擎方法

方法1:alter table 表名engine=引擎;方法2:vi/etc/my.cnf在[mysqld]配置段内添加,指定默认引擎并重启服务;default-storage-engine=innoDB重启mysqld方法3:create table表名(字段....)engine = 引擎;方法4:Mysql_convert_table_format--user=root--password=密码 --sock=/tmp/mysql.sock --engine = 引擎库名表名yum -y install perl-DBI perl-DBD-MySQL/usr/local/mysql/bin/mysql_convert_table_format --user=root --password='123456' --sock=/tmp/mysql.sock auth

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。