MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法总结-mysql建表时添加索引

mysql创建索引的时候支持字段的desc排序方式吗？

MySql创建索引时支持ASC或DESC排序。

下面举例 创建表时同时创建索引降序排序(sname 字段上普通索引降序） create table tbl1 ( id int unique, sname varchar(50), index tbl1_index_sname(sname desc) ); 在已有的表创建索引语法 create [unique|fulltext|spatial] index 索引名 on 表名(字段名 [长度] [asc|desc]);

mysql新增字段为何会锁表？

MySQL在5.6.0版本之前对表结构进行修改会锁表的，5.6以后引入了online ddl，online ddl解决的就是修改表结构时候锁表的问题，能够让mysql在进行表变更时候，不影响正常的读写操作。

要知道为什么表结构变更时候（新增／修改字段、索引的删除和添加）会锁表，就得知道当我们修改表结构时候，MySQL都做了哪些事情。

SQL语言

1. DQL：数据查询语言：SELECT <字段名表> FROM <表或视图名> WHERE <查询条件>;
2. DML：数据操作语言：INSERT/UPDATE/DELETE;
3. DDL：数据定义语言：CREATE TABLE/VIEW/INDEX;
4. DCL：数据控制语言：授权、事物ROLLBACK/COMMIT;

DDL

在MySQL5.6之前的版本中，执行ddl有copy和inplace两种方式，可以根据命名就知道两种方式的意思。其中replace方式仅支持添加、删除索引操作。这两种方式都是锁表操作。copy方式执行的操作：

1. 创建一个临时表，和要修改的表结构一致；
2. 将原来的表锁住，禁止DML操作，可以DQL操作；
3. 将原来的表数据拷贝到临时表中
4. 将临时表重命名为原来的表，删除原来的表
5. 创建新的索引数据

inplace方式执行的操作：

1. 新建索引的数据字典
2. 锁表，禁止DML操作，可以DQL操作；
3. 构造新的索引数据
4. 等待所有只读操作完毕
5. 创建索引结束

所以表新增字段属于ddl数据定义语言，采用的是copy方式，锁表。那么是否说online ddl就不存在锁表的问题了呢？不是的，对于不支持online ddl操作的DDL语句，还得采取copy方式，比如修改列的数据类型、主键的删除、表字符集的修改等这些需要彻底修改记录数据格式的操作。

线上大数据表如何执行DDL

当我们需要对生产数据库中的表执行DDL的话，一定要小心，一定要慎之又慎。一不小心就会导致锁表，锁表一旦产生，数据库就会堆积大量对该表的请求，瞬间将数据库的连接吃没，CPU飙升，最后。。。数据库宕机！这里提供以下思路供大家参考：1.停服务执行，这种方式要求业务可以停止运行的情况下执行，比如半夜凌晨执行表结构变更，简单粗暴。

2.参考copy的方式自己执行这些步骤：

• 创建一个临时表table_copy，代表最新的表结构和索引；

• 把旧表的数据copy到新表：这步不要用sql操作，自己写一个脚本，按照数据的创建时间一次10000条的拷贝到新表，这个过程中可能会有新的数据进入，所以根据每一条记录的创建时间不断同步，直至两张表的记录完全一致，再执行第三步。
• 删除旧表，把新表重命名为旧表的名字

3.pt-online-schema-change：在线修改大数据表结构工具，可以google了解一下。

最近再找一些Mysql锁表原因，整理出来一部分sql语句会锁表的，方便查阅，整理的不是很全，都是工作中碰到的，会持续更新笔者能力有限，如果有不正确的，或者不到位的地方，还请大家指出来，方便你我，方便大家。此测试环境Mysql5.5基于innodb引擎 insert into table1 valu

锁是计算机协调多个进程或线程并发访问某一资源的机制，数据库中，数据也是一种供许多用户共享的资源。需要用到锁来保证数据的一致性。

添加表字段本身就是对自身表结构的修改，而在一开始创建表之后就会存储到对应的数据文件当中，如果不加锁，当前你在修改的时候，客户端请求还可以继续来访问数据，这样违背了数据的一致性。因为前后的表结构已经发生了变化

例如：接通电路的时候，我们同样需要关闭电源的开关

如何发现表锁

对于锁定表，首先我们需要去发现它，这样才能做故障的排查

1、服务端命令检查

一般情况下我们可以通过show processlist 来检查出是那条语句导致大量查询语句处于等待状态，然后kill 掉修改表格语句的进程。

2、服务监控提示

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like 'table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Table_locks_immediate | 2979 |

| Table_locks_waited | 0 |

+-----------------------+-------+

2 rows in set (0.00 sec))

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

Table_locks_immediate表示立即释放表锁数

Table_locks_waited表示需要等待的表锁数

表结构修改原理

在MySQL5.6之前的版本中，直接修改表结构的过程中会锁表。之后引入了Online DDL用于支持DDL执行期间DML语句的并行操作，提高数据库的吞吐量

（1）首先创建新的临时表，表结构通过命令ALTAR TABLE新定义表结构

（2）然后把原表中数据导入到临时表

（3）删除原表

（4）最后把临时表重命名为原来的表名

如果有感悟，欢迎关注额