MySQL大表处理方式、快速复制表结构和数据的方式

mysql 如何给大表添加字段

（1）复制大表的表结构，创建一个表结构相同的新表

CREATE TABLE  account_bak LIKE account;

（2）给新表添加要求添加的字段

alter table account_bak add column `password` VARCHAR(10);

（3）复制大表数据到新表中

INSERT INTO account_bak(id,account_name,balance) SELECT id,account_name,balance FROM account;

(4) 将大表改名

RENAME TABLE account TO account_bak_1;

(5) 将新表改为原表名

RENAME TABLE account_bak TO account;

怎么快速删除大数据量表

（1）复制大表的表结构，创建一个表结构相同的新表

CREATE TABLE  account_bak LIKE account;

（2）修改表名

RENAME TABLE account TO account_bak;

（3）执行删除

drop table account;

mysql千万级数据大表该如何优化?

Mysql单表可以存储10亿级的数据，只是这个时候性能非常差，项目中大量的实验证明，Mysql单表容量在500万左右，性能处于最佳状态。
针对大表的优化，主要可以采取以下方案：
（1） 垂直分表，减少单表的数据量，常见的分表策略有： 按年月、时间等方式、hash和取余的方式、按范围的方式、按指定前缀的方式
（2）通过主从复制实现读写分离
（3）如果读多写少，使用缓存减小大表的压力
（4）大字段分表存储、建立索引、优化大表相关的SQL

MySQL数据库如何优化性能

MySQL优化主要分为软优化和硬优化

硬优化

（1）CPU、内存、磁盘

• 配置多核心和频率高的cpu,多核心可以执行多个线程
• 配置大内存，提高缓存区容量，能减少磁盘I/O时间
• 配置转速更高的硬盘

（2）优化数据库的配置参数

• max_connections： 最大连接数
• key_buffer_size：只对MyISAM表起作用，索引缓存区的大小
• innodb_buffer_pool_size：缓冲数据和索引的内存空间
• query_cache_size：查询缓存区的大小，MySQL将SELECT语句查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果
• table_cache：能同时打开表的个数

（3）数据库集群，分库分表，实现读写分离

（4）建立redis缓存集群

软优化

（1）优化查询语句，尽量避免全表查询

（2）优化子查询，尽量使用join来代替子查询

（3）使用索引

• LIKE关键字匹配%开头的字符串,不会使用索引
• or关键字的两个字段必须都使用了索引，否则会导致索引失效
• 组合索引满足最左匹配原则
• 不要列上使用函数，并且避免类型转换
• 复合索引中不能含有NULL值

（4）适当增加冗余字段

数据库设计和表设计要考虑性能

• 避免字段查询null值
• 尽量使用合适字段类型
• 单表字段不宜过多，一般不超过20个
• 选取合适的存储引擎

MyISAM和InnoDB引擎的区别

（1）《最重要1》InnoDB支持事务、外键、行锁和表锁，MyISAM支持表锁，但不支持事务、外键、行锁
（2）InnoDB必须由主键，即便没有定义主键，InnoDB会自动创建隐藏列row_id，MyISAM可以没有主键
（3）《最重要2》InnoDB和MyISAM都使用的B+树索引结构，但:

• InnoDB聚集索引的叶子节点存储了主键索引和数据，非聚集索引（辅助索引）需要先通过索引查询到主键，在通过主键到聚集索引索引中查询到数据
• MyISAM聚集索引的叶子节点存储的是索引和数据的磁盘存储地址。索引和数据是分离的

（4）Innodb存储文件由两个文件组成：frm、ibd，而MyISAM由三个文件组成：frm、MYD、MYI

• Innodb：frm是表定义文件，ibd是数据文件
• Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

B树索引和B+树索引的区别

• B树的每个节点存储了索引和数据，B+树的叶子节点才会存数据和索引才一起存储的，叶子节点组成了一条链表，再范围查询更有优势，只需要找到最小叶子节点然后遍历链表。
• B+树查询效率更稳定。B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定

create table select from和insert into table select from区别

（1）create table select from 表结构在复制的时候存在
CREATE TABLE tt1  SELECT value1,value2 FROM account;

（2）insert into table select from 要求表结构存在
CREATE TABLE tt2 LIKE  account;
INSERT INTO tt2(value1,value2) SELECT value1,value2 FROM account; 

create table as与create table like区别

想复制表结构（不包括索引）和查询的数据
CREATE TABLE table1 as SELECT * FROM account;
如果仅想复制表结构（不包括索引），而不想复制数据
CREATE TABLE table1 as SELECT * FROM account limit 0;
复制表数据和索引，但不复制数据
CREATE TABLE table2 LIKE account;