常用的三种删除方式：通过delete、truncate、drop关键字进行删除；这三种都可以用来删除数据，但场景不同。
执行速度：drop > truncate >> delete

1、delete命令
delete属于数据库DML操作语言。在InnoDB中，delete其实并不会真的把数据删除，mysql实际上只是给删除的数据打了个标记为已删除，因此delete删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见。虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以重用这部分空间(重用 → 覆盖)。
delete操作以后使用“optimize table table_name”会立刻释放磁盘空间。所以要想达到释放磁盘空间的目的，delete以后执行optimize table操作。

2、drop命令
drop属于数据库DDL定义语言，同truncate一样，执行后立即生效，无法找回。“drop table table_name”立刻释放磁盘空间，不管是InnoDB和MyISAM。drop语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)。依赖于该表的存储过程/函数将保留，但是变为invalid状态。

3、truncate命令
truncate属于数据库DDL定义语言，不走事务，原数据不放到rollback segment中，操作不触发trigger。执行后立即生效，无法找回！
“truncate table table_name”立刻释放磁盘空间，不管是InnoDB和MyISAM。truncate table其实有点类似于drop table然后create，只不过这个create table的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度。
truncate 能够快速清空一个表，并且重置auto increment的值。

mysql没有for update wait 5 这种等待加锁超时时间设置，但是可以在配置中设置innodb_lock_wait_timeout

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

默认时间是50秒，可以改成1，就是1秒

一、全局锁
全局锁是对整个数据库实例加锁，整个库处于只读状态。

flush tables with read lock

适用场景
全局锁适用于做全库逻辑备份，但是整个库处于只读状态，在备份期间，所有的更新操作、DDL将会被阻塞，会对业务产生影响。

single-transaction
mysqldump备份时可以使用single-transaction参数，在备份数据之前启动一个事务，借助于MVCC获取到一致性视图，保证在备份的过程中，还支持数据的更新操作。
但是single-transaction只能用于支持事务的引擎，比如MyISAM不支持事务，所以使用MyISAM引擎的时候，是无法使用single-transaction的。

二、表级锁
表级锁分为表锁和元数据锁。

1、表锁
表锁从名字上就可以看出锁的是数据库表（Table），语法为：

1、在[mysqld]下添加skip-log-bin，重启服务

2、查看binlog是否开启
show variables like '%log_bin%';

3、删除原有的binlog
不能直接删，要通过设置过期时间删除

1）显示binlog的过期时间
show variables like '%expire%';

2）设置过期时间
set global binlog_expire_logs_seconds=60*60*24;

3）手动刷新日志
flush logs;

数据切分，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。数据的切分同时还可以提高系统的总体可用性，因为单台设备Crash之后，只有总体数据的某部分不可用，而不是所有的数据。

数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。一种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分。

一、数据的垂直切分
数据的垂直切分，也可以称之为纵向切分。将数据库想象成为由很多个一大块一大块的“数据块”（表）组成，我们垂直的将这些“数据块”切开，然后将他们分散到多台数据库主机上面。这样的切分方法就是一个垂直（纵向）的数据切分。

一个架构设计较好的应用系统，其总体功能肯定是由很多个功能模块所组成的，而每一个功能模块所需要的数据对应到数据库中就是一个或者多个表。而在架构设计中，各个功能模块相互之间的交互点越统一越少，系统的耦合度就越低，系统各个模块的维护性以及扩展性也就越好。这样的系统，实现数据的垂直切分也就越容易。

我们可以通过执行“show engine innodb status”命令来获取比较详细的系统当前Innodb性能状态。

1、BACKGROUND THREAD
后台线程，可以看到活动线程，停止线程，空闲线程

2、SEMAPHORES
这部分主要显示系统中当前的信号等待信息以及各种等待信号的统计信息

3、TRANSACTIONS
这里主要展示系统的锁等待信息和当前活动事务信息。通过这部分输出，我们可以查追踪到死锁的详细信息

4、FILE I/O
十个IO线程，一个更改线程，一个日志线程，4个读线程，4个写线程

5、INSERT BUFFER AND ADAPTIVE HASH INDEX
显示插入缓存当前状态信息以及自适应Hash Index的状态

Innodb存储引擎的数据（包括索引）存放在相同的文件中，这一点和MySQL默认存储引擎MyISAM的区别较大，后者分别存放于独立的文件。除此之外，Innodb的数据存放格式也比较独特，每个Innodb表都会将主键以聚簇索引的形式创建。所有的数据都是以主键来作为升序排列在物理磁盘上面，所以主键查询并且以主键排序的查询效率也会非常高。

由于主键是聚族索引的缘故，Innodb的基于主键的查询效率非常高。如果我们在创建一个Innodb存储引擎的表的时候并没有创建主键，那么Innodb会尝试在创建于我们表上面的其他索引，如果存在由单个not null属性列的唯一索引，Innodb则会选择该索引作为聚族索引。如果也没有任何单个not null属性列的唯一索引，Innodb会自动生成一个隐藏的内部列，该列会在每行数据上占用6个字节的存储长度。所以，实质上每个Innodb表都之少会有一个索引存在。

在Innodb上面除了聚族索引之外的索引被成为secondary index，每个secondary index上都会包含有聚族索引的索引键信息，方便通过其他索引查找数据的时候能够更快的定位数据位置所在。

Innodb存储引擎和MyISAM存储引擎最大区别主要有四点，第一点是缓存机制，第二点是事务支持，第三点是锁定实现，最后一点就是数据存储方式的差异。

一、Innodb缓存相关优化
1、innodb_buffer_pool_size的合理设置
Innodb存储引擎的缓存机制和MyISAM的最大区别就在于Innodb不仅仅缓存索引，同时还会缓存实际的数据。所以，完全相同的数据库，使用Innodb存储引擎可以使用更多的内存来缓存数据库相关的信息，当然前提是要有足够的物理内存。

innodb_buffer_pool_size参数用来设置Innodb最主要的Buffer(Innodb_Buffer_Pool)的大小，也就是缓存用户表及索引数据的最主要缓存空间，对Innodb整体性能影响也最大。无论是MySQL官方手册还是网络上很多人所分享的Innodb优化建议，都简单的建议将Innodb的Buffer Pool设置为整个系统物理内存的50%～80%之间。

2、如何分析MySQL内存使用
假设是一台单独给MySQL使用的主机，物理内存总大小为8G，MySQL最大连接数为500，同时还使用了MyISAM存储引擎，这时候我们的整体内存该如何分配呢？
内存分配为如下几大部分：
a）系统使用，假设预留800M；
b）线程独享，约2GB=500*(1MB+1MB+1MB+512KB+512KB)，组成大概如下：
sort_buffer_size：1MB
join_buffer_size：1MB
read_buffer_size：1MB
read_rnd_buffer_size：512KB
thread_statck：512KB
c）MyISAM Key Cache，假设大概为1.5GB；
d）Innodb Buffer Pool最大可用量：8GB-800MB-2GB-1.5GB=3.7GB；
假设这个时候我们还按照50%～80%的建议来设置，最小也是4GB，而通过上面的估算，最大可用值在3.7GB左右，那么很可能在系统负载很高当线程独享内存差不多出现极限情况的时候，系统很可能就会出现内存不足的问题了。而且上面还仅仅只是列出了一些使用内存较大的地方，如果进一步细化，很可能可用内存会更少。

1、Query Cache实现原理
MySQL的Query Cache实现原理实际上并不是特别的复杂，简单的来说就是将客户端请求的Query语句（当然仅限于SELECT类型的Query）通过一定的hash算法进行一个计算而得到一个hash值，存放在一个hash桶中。同时将该Query的结果集（Result Set）也存放在一个内存Cache中的。存放Query hash值的链表中的每一个hash值所在的节点中同时还存放了该Query所对应的Result Set的Cache所在的内存地址，以及该Query所涉及到的所有Table的标识等其他一些相关信息。

系统接受到任何一个SELECT类型的Query的时候，首先计算出其hash值，然后通过该hash值到Query Cache中去匹配，如果找到了完全相同的Query，则直接将之前所Cache的Result Set返回给客户端而完全不需要进行后面的任何步骤即可完成这次请求。

而后端的任何一个表的任何一条数据发生变化之后，也会通知Query Cache，需要将所有与该Table有关的Query的Cache全部失效，并释放出之前占用的内存地址，以便后面其他的Query能够使用。

对于基于性能的数据库Schema设计，我们并不能完全以规范化范式理论来作为唯一的指导。在设计过程中，应该从实际需求出发，以性能提升为根本目标来展开设计工作，很多时候为了尽可能提高性能，我们必须做反范式设计。

一、高效的模型设计

1、适度冗余-让Query尽两减少Join
数据库的规范化理论其实质是在概念上的单一化，虽然规范后的数据库中的表一般都较小，使表中相关列最少。这虽然可能在某些情况下增强了数据库的可维护性，但在系统要完成一些数据的查询检索时，可能要用复杂的Join才能实现，这势必会造成查询检索的性能低下。如果我们通过拆分Join，通过多次简单的查询来在应用中实现Join逻辑，那所带来的网络开销将会是非常巨大的。

2、大字段垂直分拆-summary表优化
大字段垂直拆分策略相对于前面介绍的适度冗余策略在做法上可以说差不多是完全相反的做法。适度冗余策略是将别的表中的字段拿过来在自己身上也存一份数据，而大字段垂直拆分简单来说就是将自己身上的字段拆分出去放在另外（单独）的表里面。