标签分类 技术文章:
当前位置:首页 > Mysql技术文章 > Mysql修改所有表的存储引擎格式语句

Mysql修改表的存储引擎格式语句整理

  • 发布时间:
  • 作者:码农之家原创
  • 点击:185

这篇文章主要知识点是关于MYSQL、所有表、存储引擎、基于Mysql存储引擎的深入分析 MySQL数据库三种常用存储引擎特性对比 的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下电子书

MySQL与MariaDB学习指南
  • 类型:数据库大小:68.9 MB格式:PDF出版:人民邮电出版社作者:罗素 戴尔
立即下载

更多Mysql相关的学习资源可以参阅 Mysql电子书程序设计电子书 等栏目。

Mysql修改所有表的存储引擎格式语句

 

SELECT GROUP_CONCAT(CONCAT( 'ALTER TABLE ' ,TABLE_NAME ,' ENGINE=ARCHIVE; ') SEPARATOR '' )
FROM information_schema.TABLES AS t
WHERE TABLE_SCHEMA = 'userinfo' AND TABLE_TYPE = 'BASE TABLE'

-- 将语句粘出来,执行;

基于Mysql存储引擎的深入分析

MySQL有很多种存储引擎,针对不同的应用,可以为每张表选择合适的存储引擎,这样有助于提升MySQL性能。
创建新闻表news:


CREATE  TABLE `sandbox`.`news`

    `id` INT NOT NULL AUTO_INCREMENT , 
    `name` VARCHAR(45) NULL ,     
    `content` VARCHAR(45) NULL , 
    `created` VARCHAR(45) NULL , 
    PRIMARY KEY (`id`)
) ENGINE = MyISAM;

注意,MySQL大小写敏不敏感,是由你所使用的平台决定的,在Windows下,Mysql大小写不敏感;在Linux平台下,MySQL大小写敏感。
最后一句SQL语句ENGINE=“存储引擎名”,就是为表选择存储引擎,MySQL允许我们在创建表的时候选择不同的存储引擎,那么MySQL到底都有哪些存储引擎呢?

MySQL存储引擎包括:MyISAM引擎,MyISAM Merge引擎,InnoDB引擎,Memory引擎,Archive引擎,CSV引擎,Federated引擎,Blackhle引擎,NDB Cluster引擎,Falcon引擎,SolidDB引擎,PBXT引擎,
Maria引擎和其它引擎。建表时,选择合适的存储引擎很重要,如果到后期再更换将会很麻烦。这里,我们只介绍常用的三种存储引擎:

MyISAM引擎
MyISAM引擎是MySQL默认的存储引擎,MyISAM不支持事务和行级锁,所以MyISAM引擎速度很快,性能优秀。MyISAM可以对整张表加锁,支持并发插入,支持全文索引。
如果你不需要事务支持,通常我们建表时都选用MyISAM存储引擎,像新闻表之类都没有必要支持事务。

InnoDB引擎
InnoDB是专为事务设计的存储引擎,支持事务,支持外键,拥有高并发处理能力。但是,InnoDB在创建索引和加载数据时,比MyISAM慢。
涉及到货币操作一般都需要支持事务,什么都能错,钱不能出错。

Memory引擎
内存表,Memory引擎将数据存储在内存中,表结构不是存储在内存中的,查询时不需要执行I/O操作,所以要比MyISAM和InnoDB快很多倍,但是数据库断电或是重启后,表中的数据将会丢失,表结构不会
丢失。如果你需要将SESSION数据存在数据库中,那么使用Memory引擎是个不错的选择。
通常,我们使用这三种存储引擎就够了,更多的介绍请查阅MySQL手册。

MySQL数据库三种常用存储引擎特性对比

MySQL 的存储引擎可能是所有关系型数据库产品中最具有特色的了,不仅可以同时使用多种存储引擎,而且每种存储引擎和MySQL之间使用插件方式这种非常松的耦合关系。

由于各存储引擎功能特性差异较大,这篇文章主要是介绍如何来选择合适的存储引擎来应对不同的业务场景。

MyISAM

特性
不支持事务:MyISAM存储引擎不支持事务,所以对事务有要求的业务场景不能使用
表级锁定:其锁定机制是表级索引,这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
读写互相阻塞:不仅会在写入的时候阻塞读取,MyISAM还会在读取的时候阻塞写入,但读本身并不会阻塞另外的读
只会缓存索引:MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO,但是这个缓存区只会缓存索引,而不会缓存数据

适用场景
不需要事务支持(不支持)
并发相对较低(锁定机制问题)
数据修改相对较少(阻塞问题)
以读为主
数据一致性要求不是非常高

最佳实践
尽量索引(缓存机制)
调整读写优先级,根据实际需求确保重要操作更优先
启用延迟插入改善大批量写入性能
尽量顺序操作让insert数据都写入到尾部,减少阻塞
分解大的操作,降低单个操作的阻塞时间
降低并发数,某些高并发场景通过应用来进行排队机制
对于相对静态的数据,充分利用Query Cache可以极大的提高访问效率
MyISAM的Count只有在全表扫描的时候特别高效,带有其他条件的count都需要进行实际的数据访问

InnoDB

特性
具有较好的事务支持:支持4个事务隔离级别,支持多版本读
行级锁定:通过索引实现,全表扫描仍然会是表锁,注意间隙锁的影响
读写阻塞与事务隔离级别相关
具有非常高效的缓存特性:能缓存索引,也能缓存数据
整个表和主键以Cluster方式存储,组成一颗平衡树
所有Secondary Index都会保存主键信息

适用场景
需要事务支持(具有较好的事务特性)
行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘 IO

最佳实践
主键尽可能小,避免给Secondary index带来过大的空间负担
避免全表扫描,因为会使用表锁
尽可能缓存所有的索引和数据,提高响应速度
在大批量小插入的时候,尽量自己控制事务而不要使用autocommit自动提交
合理设置innodb_flush_log_at_trx_commit参数值,不要过度追求安全性
避免主键更新,因为这会带来大量的数据移动

NDBCluster

特性
分布式:分布式存储引擎,可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分
支持事务:和Innodb一样,支持事务
可与mysqld不在一台主机:可以和mysqld分开存在于独立的主机上,然后通过网络和mysqld通信交互
内存需求量巨大:新版本索引以及被索引的数据必须存放在内存中,老版本所有数据和索引必须存在与内存中

适用场景
具有非常高的并发需求
对单个请求的响应并不是非常的critical
查询简单,过滤条件较为固定,每次请求数据量较少,又不希望自己进行水平Sharding

最佳实践
尽可能让查询简单,避免数据的跨节点传输
尽可能满足SQL节点的计算性能,大一点的集群SQL节点会明显多余Data节点
在各节点之间尽可能使用万兆网络环境互联,以减少数据在网络层传输过程中的延时

注:以上三个存储引擎是目前相对主流的存储引擎,还有其他类似如:Memory,Merge,CSV,Archive等存储引擎的使用场景都相对较少,这里就不一一分析了。

以上就是本次给大家分享的关于Mysql的全部知识点内容总结,大家还可以在下方相关文章里找到在OneProxy的基础上实行My、 MySql Date函数用法总结、 mysql查询当天、本周,本月、 等mysql文章进一步学习,感谢大家的阅读和支持。

上一篇:mysql在动态SQL内获取返回值的方法详解

下一篇:MySQL命令行中使用SQL语句的规则以及实例方法

展开 +

收起 -

学习笔记
网友NO.763629

MySQL Memory 存储引擎浅析

后来看到博客园在用NorthScale Memcached Server(官方站点:http://www.couchbase.com/products-and-services/memcached),貌似共享收费,又犹豫了。其实项目里的需求很简单,也想自己用.Net Cache来实现,但稳定性难以评估,开发维护成本又似乎太大,没办法,My SQL Memory Storage成了唯一选择,因为几乎不怎么需要编写代码。 先看官方手册,然后写了个简单的性能测试。因为官方最新的文档都是英文版的,所以译了5.5版本 MySQL Memory Storage章节。 官方文档(译自5.5版本的The Memory Storage Engine) Memory存储引擎将表的数据存放在内存中。Memory替代以前的Heap成为首选项,但同时向下兼容,Heap仍被支持。 Memory存储引擎特性: Storage limits RAM Transactions No Locking granularity Table MVCC No Geospatial data type support No Geospatial indexing support No B-tree indexes Yes Hash indexes Yes Full-text search indexes No Clustered indexes No Data caches N/A Index caches N/A Compressed data No Encrypted data Yes Cluster database support No Replication support Yes Foreign key support No Backup / point-in-time recoveryc Yes Query cache support Yes Update statistics for data dictionary Yes Memory 与 MySQL Cluster的比较 希望部署内存引擎的开发者们会考虑MySQL Cluster是否是更好的选择,参考如下Memory引擎的使用场景及特点: 能像会话(Session)或缓存(Caching)一样方便操作和管……

网友NO.927874

MySQL修改数据表存储引擎的3种方法介绍

MySQL作为最常用的数据库,经常遇到各种各样的问题。今天要说的就是表存储引擎的修改。有三种方式,列表如下。 1.真接修改。在数据多的时候比较慢,而且在修改时会影响读取性能。my_table是操作的表,innoDB是新的存储引擎。 ALTER TABLE my_table ENGINE=InnoDB 2.导出,导入。这个比较容易操作,直接把导出来的sql文件给改了,然后再导回去。用mysqldump ,枫哥常用的是navicate那样更容易上手。友情提醒风险较大。 3.创建,插入。这个比第一种速度快, 安全性比第二种高,推荐。分2步操作 a.创建表,先创建一个和要操作表一样的表,然后更改存储引擎为目标引擎。 CREATE TABLE my_tmp_table LIKE my_table; ALTER TABLE my_tmp_table ENGINE=InnoDB; b.插入。为了安全和速度,最好加上事务,并限制id(主键)范围。 INSERT INTO my_tmp_table SELECT * FROM my_table; 就到这里,希望对需要的同学有帮助。 ……

网友NO.663186

MySQL常用存储引擎功能与用法详解

本文实例讲述了MySQL常用存储引擎功能与用法。分享给大家供大家参考,具体如下: MySQL存储引擎主要有两大类: 1. 事务安全表 :InnoDB、BDB。 2. 非事务安全表 :MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。 MySQL默认的存储引擎是MyISAM(5.7版本中默认为InnoDB)。 配置文件中设置默认存储引擎的参数:default-table-type。 查询当前数据库支持的存储引擎: show engines;show variables like 'have%'; 查看当前的默认存储引擎: show variables like '%table_type%'; 创建新表时指定存储引擎: create table(...) engine=MyISAM; 下面详细介绍4个比较常用的存储引擎:MyISAM、InnoDB、MEMORY和MERGE。 一、MyISAM 1. 数据文件: MyISAM数据表在磁盘存储成3个文件,其文件名都和表名相同,扩展名分别是: (1).frm:存储数据表结构定义。 (2).MYD:存储表数据。 (3).MYI:存储表索引。 其中,数据文件和索引文件可以放置在不同的目录,平均分布IO,获得更快的速度。指定索引文件和数据文件的路径,需要在创建表的时候通过data directory和index directory语句指定。(文件路径需要是绝对路径并且具有访问的权限) MyISAM类型的表可能会损坏,原因可能是多种多样的,损坏后的表可能不能访问,会提示需要修复或者访问后返回错误的结果。可以使用check ……

网友NO.494848

Mysql存储引擎详解

存储引擎的介绍 关系型数据库表是用来存储和组织信息的数据结构,可以将表理解为由行和列组成的表格。 由于表的类型不同,我们在实际开发过程中,就有可能需要各种各样的表,不同的表就意味着存储不同类型的数据,数据的处理上也会存在差异 对于Mysql来说,它提供了很多种类型的存储引擎 存储引擎说白了就是如何存储数据,如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。 因为在关系型数据库中数据的存储是以表的形式存储 的,所以存储引擎也可以称为表类型(即存储和操作此表的类型) Mysql中的存储引擎 MyISAM 这种引擎是mysql最早提供的,这种引擎又可以分为静态MyISAM、动态MyISAM和压缩MyISAM三种 静态MyISAM: 如果数据表中的各数据列的长度都是预先固定好的,服务器将自动选择这种表类型。 因为数据表中的每一条记录所占用的空间都是一样的,所以这种表存取和更新的效率非常高。 当数据受损时,恢复工作也比较容易做 动态MyISAM: 如果数据表中出现varchar,xxxtext或xxxxBLOB字段时,服务器将自动选择这种表类型 。 相对于静态MyISAM,这种表存储空间比较小,但由于每条记录的长度不一,所以多次修改数据后,数据表中的数据就可能离散的存储在内存中,进而导致执行效率下降. 同时内存中也可能会……

<
1
>

Copyright 2018-2019 xz577.com 码农之家

版权责任说明