澳门十大赌场最新排名 > 澳门十大赌场 > 创建主键等常用操作总结,在PXC中重新添加掉线

原标题:创建主键等常用操作总结,在PXC中重新添加掉线

浏览次数:62 时间:2019-07-13

一. 列常用操作

① 添加新的一列test_column,并将其作为主键,FIRST将其放在表中第一行,auto_increement是自动增长

alter table test_table add column test_column int not null auto_increment FIRST add primary key(test_column);
  • 1

 

可以使用SQL语句“alter table ai3 add id0 int  auto_increment primary key first;”来添加主键列。可以使用SQL语句“alter table ai4 modify id int auto_increment primary key;”来修改主键列。

② 删除列

 

alter table test_table drop column test_column;
  • 1

③ 修改某一列的字段长度(例如本来是30字节改为50字节长)

alter table test_table modify column test_column varchar(50);
  • 1

④ 完全修改某一列(假设原本列名是test1_column,类型是int)

alter table test_table change column test1_column test_column varchar(30);
  • 1

⑤ 仅仅想重命名某一列(首先需要了解这一列的类型,假如原本是int且不为空,列名是error_name_column)

alter table test_table change column error_name_column test_column int not null;
  • 1

 

MySQL EXPLAIN命令是查询性能优化不可缺少的一部分,该文主要讲解explain命令的使用及相关参数说明。

二. 针对表的多数操作

① 修改指定表的存储引擎,假设原本是MYISAM

alter table test_table engine=innodb;
  • 1

② 删除指定表的主键

alter table test_table drop primary key;
  • 1

这里有个情况需要指出,如果该主键列是自动增长(auto_increment)的,因为mysql要求自动增长列必须是索引,所以删除主键也就删除了主键索引,这是不符合mysql要求的,是无法实现的,会报错,必须先删除自动增长(通过修改列属性),后删除主键

③ 为指定表添加主键

alter table test_table add primary key(test_column);
  • 1

④ 为指定表添加索引(普通索引),test_index是索引名

alter table test_table add index test_index(test_column);
  • 1

⑤ 删除指定表索引

alter table test_table drop index test_index;
  • 1

⑥ 重命名表

alter table test_table rename new_name_table;

 

 



 

 

如果想在一个已经建好的表中添加一列,可以用诸如:

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null;

这条语句会向已有的表中加入新的一列,这一列在表的最后一列位置。如果我们希望添加在指定的一列,可以用:

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null after COLUMN_NAME;

注意,上面这个命令的意思是说添加新列到某一列后面。如果想添加到第一列的话,可以用:

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null first;

Preface

EXPLAIN Output Columns

列名 说明
id 执行编号,标识select所属的行。如果在语句中没子查询或关联查询,只有唯一的select,每行都将显示1。否则,内层的select语句一般会顺序编号,对应于其在原始语句中的位置
select_type 显示本行是简单或复杂select。如果查询有任何复杂的子查询,则最外层标记为PRIMARY(DERIVED、UNION、UNION RESUlT)
table 访问引用哪个表(引用某个查询,如“derived3”)
type 数据访问/读取操作类型(ALL、index、range、ref、eq_ref、const/system、NULL)
possible_keys 揭示哪一些索引可能有利于高效的查找
key 显示mysql决定采用哪个索引来优化查询
key_len 显示mysql在索引里使用的字节数
ref 显示了之前的表在key列记录的索引中查找值所用的列或常量
rows 为了找到所需的行而需要读取的行数,估算值,不精确。通过把所有rows列值相乘,可粗略估算整个查询会检查的行数
Extra 额外信息,如using index、filesort等

 

id

id是用来顺序标识整个查询中SELELCT 语句的,在嵌套查询中id越大的语句越先执行。该值可能为NULL,如果这一行用来说明的是其他行的联合结果。

    When we add a new node into PXC structure,it will estimate the mothed(IST/SST) to transfer data to the new node.It's no wonder that sst will be automatically choosed.What if a dropped node in PXC by accident want to rejoin the cluster?Especially it has been a long period of time after it was dropped out.We should avoid starting up the node directly.As the SST(either xtrabackup-v2 or rsync mode) will be choosed to resynchronize the dropped node instead of IST.Generally speaking,SST may cause to decline the perormance of the whole cluster.It's recommend to use another way to deal with that kind of issue.That is,slave replication.

select_type

表示查询的类型

类型 说明
simple 简单子查询,不包含子查询和union
primary 包含union或者子查询,最外层的部分标记为primary
subquery 一般子查询中的子查询被标记为subquery,也就是位于select列表中的查询
derived 派生表——该临时表是从子查询派生出来的,位于form中的子查询
union 位于union中第二个及其以后的子查询被标记为union,第一个就被标记为primary如果是union位于from中则标记为derived
union result 用来从匿名临时表里检索结果的select被标记为union result
dependent union 顾名思义,首先需要满足UNION的条件,及UNION中第二个以及后面的SELECT语句,同时该语句依赖外部的查询
subquery 子查询中第一个SELECT语句
dependent subquery 和DEPENDENT UNION相对UNION一样

 

table

对应行正在访问哪一个表,表名或者别名

  • 关联优化器会为查询选择关联顺序,左侧深度优先
  • 当from中有子查询的时候,表名是derivedN的形式,N指向子查询,也就是explain结果中的下一列
  • 当有union result的时候,表名是union 1,2等的形式,1,2表示参与union的query id

注意:MySQL对待这些表和普通表一样,但是这些“临时表”是没有任何索引的。

Framework

type

type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ,一般来说,得保证查询至少达到range级别,最好能达到ref。

类型 说明
All 最坏的情况,全表扫描
index 和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index,说明正在使用覆盖索引,只扫描索引的数据,它比按索引次序全表扫描的开销要小很多
range 范围扫描,一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref 一种索引访问,它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是,它用在关联操作只使用了索引的最左前缀,或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref 最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 (高效)
const 当确定最多只会有一行匹配的时候,MySQL优化器会在查询前读取它而且只读取一次,因此非常快。当主键放入where子句时,mysql把这个查询转为一个常量(高效)
system 这是const连接类型的一种特例,表仅有一行满足条件。
Null 意味说mysql能在优化阶段分解查询语句,在执行阶段甚至用不到访问表或索引(高效)

 

possible_keys

显示查询使用了哪些索引,表示该索引可以进行高效地查找,但是列出来的索引对于后续优化过程可能是没有用的

Hostname IP Port OS Version MySQL Version Xtrabackup version
zlm2 192.168.1.101 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm3 192.168.1.102 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm4 192.168.1.103 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)

key

key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

 

key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好 。

Procedure

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

 

rows

rows列显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。

what's show in error log of initiating SST/IST transfer operation on a new added node.

Extra

Extra是EXPLAIN输出中另外一个很重要的列,该列显示MySQL在查询过程中的一些详细信息,MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

类型 说明
Using filesort MySQL有两种方式可以生成有序的结果,通过排序操作或者使用索引,当Extra中出现了Using filesort 说明MySQL使用了后者,但注意虽然叫filesort但并不是说明就是用了文件来进行排序,只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快,所以一般这时也要考虑优化查询了。使用文件完成排序操作,这是可能是ordery by,group by语句的结果,这可能是一个CPU密集型的过程,可以通过选择合适的索引来改进性能,用索引来为查询结果排序。
Using temporary 用临时表保存中间结果,常用于GROUP BY 和 ORDER BY操作中,一般看到它说明查询需要优化了,就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not exists MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了。
Using index 说明查询是覆盖了索引的,不需要读取数据文件,从索引树(索引文件)中即可获得信息。如果同时出现using where,表明索引被用来执行索引键值的查找,没有using where,表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的,但无需再回表查询记录。
Using index condition 这是MySQL 5.6出来的新特性,叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的,但是现在可以了,这样减少了不必要的IO操作,但是只能用在二级索引上。
Using where 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意:Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。
Using join buffer 使用了连接缓存:Block Nested Loop,连接算法是块嵌套循环连接;Batched Key Access,连接算法是批量索引连接
impossible where where子句的值总是false,不能用来获取任何元组
select tables optimized away 在没有GROUP BY子句的情况下,基于索引优化MIN/MAX操作,或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
distinct 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

作者:高广超
链接:
來源:简书
版权申明:内容来源网络,版权归原创者所有。除非无法确认,我们都会标明作者及出处,如有侵权烦请告知,我们会立即删除并表示歉意。谢谢。

 

小编积累多年的干货文档免费赠送,包含前端后端和测试,系统架构,高并发处理,优化等

图片 1

 

 1 2018-08-09T07:23:32.568794 01:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4433' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
 2 2018-08-09T07:23:33.225673 01:00 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2|192.168.1.103:4444/xtrabackup_sst//1
 3 2018-08-09T07:23:33.225697 01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 3) (Increment: 1 -> 3)
 4 2018-08-09T07:23:33.225704 01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 5 2018-08-09T07:23:33.225721 01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
 6 2018-08-09T07:23:33.225760 01:00 0 [Note] WSREP: Service thread queue flushed.
 7 2018-08-09T07:23:33.226619 01:00 2 [Note] WSREP: Check if state gap can be serviced using IST
 8 2018-08-09T07:23:33.226638 01:00 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 9 2018-08-09T07:23:33.226677 01:00 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST
10 2018-08-09T07:23:33.226683 01:00 2 [Note] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (bd5525ab-9a15-11e8-aa0f-4b830c783fc7): 1 (Operation not permitted)
11      at galera/src/replicator_str.cpp:prepare_for_IST():538. IST will be unavailable.
12 2018-08-09T07:23:33.228003 01:00 0 [Note] WSREP: Member 2.0 (zlm4) requested state transfer from '*any*'. Selected 0.0 (zlm2)(SYNCED) as donor.
13 2018-08-09T07:23:33.228029 01:00 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 22)
14 2018-08-09T07:23:33.228088 01:00 2 [Note] WSREP: Requesting state transfer: success, donor: 0
15 2018-08-09T07:23:33.228108 01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
16     2018-08-09T05:23:33.781389Z WSREP_SST: [INFO] Proceeding with SST.........
17     2018-08-09T05:23:33.808866Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!

 

**Check the PXC status at first.**

 1 (zlm@192.168.1.101 3308)[(none)]>show global status like '%wsrep%';
 2  ---------------------------------- ---------------------------------------------------------- 
 3 | Variable_name                    | Value                                                    |
 4  ---------------------------------- ---------------------------------------------------------- 
 5 | wsrep_local_state_uuid           | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
 6 | wsrep_protocol_version           | 8                                                        |
 7 | wsrep_last_applied               | 22                                                       |
 8 | wsrep_last_committed             | 22                                                       |
 9 | wsrep_replicated                 | 0                                                        |
10 | wsrep_replicated_bytes           | 0                                                        |
11 | wsrep_repl_keys                  | 0                                                        |
12 | wsrep_repl_keys_bytes            | 0                                                        |
13 | wsrep_repl_data_bytes            | 0                                                        |
14 | wsrep_repl_other_bytes           | 0                                                        |
15 | wsrep_received                   | 21                                                       |
16 | wsrep_received_bytes             | 2733                                                     |
17 | wsrep_local_commits              | 0                                                        |
18 | wsrep_local_cert_failures        | 0                                                        |
19 | wsrep_local_replays              | 0                                                        |
20 | wsrep_local_send_queue           | 0                                                        |
21 | wsrep_local_send_queue_max       | 1                                                        |
22 | wsrep_local_send_queue_min       | 0                                                        |
23 | wsrep_local_send_queue_avg       | 0.000000                                                 |
24 | wsrep_local_recv_queue           | 0                                                        |
25 | wsrep_local_recv_queue_max       | 2                                                        |
26 | wsrep_local_recv_queue_min       | 0                                                        |
27 | wsrep_local_recv_queue_avg       | 0.047619                                                 |
28 | wsrep_local_cached_downto        | 0                                                        |
29 | wsrep_flow_control_paused_ns     | 0                                                        |
30 | wsrep_flow_control_paused        | 0.000000                                                 |
31 | wsrep_flow_control_sent          | 0                                                        |
32 | wsrep_flow_control_recv          | 0                                                        |
33 | wsrep_flow_control_interval      | [ 173, 173 ]                                             |
34 | wsrep_flow_control_interval_low  | 173                                                      |
35 | wsrep_flow_control_interval_high | 173                                                      |
36 | wsrep_flow_control_status        | OFF                                                      |
37 | wsrep_cert_deps_distance         | 0.000000                                                 |
38 | wsrep_apply_oooe                 | 0.000000                                                 |
39 | wsrep_apply_oool                 | 0.000000                                                 |
40 | wsrep_apply_window               | 0.000000                                                 |
41 | wsrep_commit_oooe                | 0.000000                                                 |
42 | wsrep_commit_oool                | 0.000000                                                 |
43 | wsrep_commit_window              | 0.000000                                                 |
44 | wsrep_local_state                | 4                                                        |
45 | wsrep_local_state_comment        | Synced                                                   |
46 | wsrep_cert_index_size            | 0                                                        |
47 | wsrep_cert_bucket_count          | 22                                                       |
48 | wsrep_gcache_pool_size           | 1712                                                     |
49 | wsrep_causal_reads               | 0                                                        |
50 | wsrep_cert_interval              | 0.000000                                                 |
51 | wsrep_ist_receive_status         |                                                          |
52 | wsrep_ist_receive_seqno_start    | 0                                                        |
53 | wsrep_ist_receive_seqno_current  | 0                                                        |
54 | wsrep_ist_receive_seqno_end      | 0                                                        |
55 | wsrep_incoming_addresses         | 192.168.1.101:3308,192.168.1.102:3308,192.168.1.103:3308 |
56 | wsrep_desync_count               | 0                                                        |
57 | wsrep_evs_delayed                |                                                          |
58 | wsrep_evs_evict_list             |                                                          |
59 | wsrep_evs_repl_latency           | 0/0/0/0/0                                                |
60 | wsrep_evs_state                  | OPERATIONAL                                              |
61 | wsrep_gcomm_uuid                 | 13eae368-9b79-11e8-9053-338307f4c6cc                     |
62 | wsrep_cluster_conf_id            | 11                                                       |
63 | wsrep_cluster_size               | 3                                                        | //It means I've got three nodes in PXC.
64 | wsrep_cluster_state_uuid         | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
65 | wsrep_cluster_status             | Primary                                                  |
66 | wsrep_connected                  | ON                                                       |
67 | wsrep_local_bf_aborts            | 0                                                        |
68 | wsrep_local_index                | 0                                                        |
69 | wsrep_provider_name              | Galera                                                   |
70 | wsrep_provider_vendor            | Codership Oy <info@codership.com>                        |
71 | wsrep_provider_version           | 3.26(r)                                                  |
72 | wsrep_ready                      | ON                                                       |
73  ---------------------------------- ---------------------------------------------------------- 
74 68 rows in set (0.00 sec)

 

Shutdown MySQL instance on zlm4.

 1 [root@zlm4 09:02:18 /data/mysql/mysql3308]
 2 #!ps
 3 ps aux|grep mysqld
 4 mysql     5367  0.2 21.9 1574708 223476 pts/0  Sl   08:00   0:07 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
 5 root      5809  0.0  0.0 112640   960 pts/0    R    09:02   0:00 grep --color=auto mysqld
 6 
 7 [root@zlm4 09:02:22 /data/mysql/mysql3308]
 8 #pkill mysqld
 9 
10 [root@zlm4 09:02:43 /data/mysql/mysql3308]
11 #!ps
12 ps aux|grep mysqld
13 root      5827  0.0  0.0 112640   960 pts/0    R    09:02   0:00 grep --color=auto mysqld
14 [1]   Done                    mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf

 

Check the error log on node zlm3.

 1 [root@zlm3 09:01:42 /data/mysql/mysql3308/data]
 2 #tail -f error.log 
 3 2018-08-09T09:02:44.880772 01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.103:4567 
 4 2018-08-09T09:02:44.880866 01:00 0 [Note] WSREP: declaring 13eae368 at tcp://192.168.1.101:4567 stable
 5 2018-08-09T09:02:44.880877 01:00 0 [Note] WSREP: forgetting 74f3db69 (tcp://192.168.1.103:4567)
 6 2018-08-09T09:02:44.880899 01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting off
 7 2018-08-09T09:02:44.884451 01:00 0 [Note] WSREP: Node 13eae368 state primary
 8 2018-08-09T09:02:44.887086 01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 9 view (view_id(PRIM,13eae368,12)
10 memb {
11     13eae368,0
12     23fb8f7a,0
13     }
14 joined {
15     }
16 left {
17     }
18 partitioned {
19     74f3db69,0
20     }
21 )
22 2018-08-09T09:02:44.887205 01:00 0 [Note] WSREP: Save the discovered primary-component to disk
23 2018-08-09T09:02:44.887402 01:00 0 [Note] WSREP: forgetting 74f3db69 (tcp://192.168.1.103:4567)
24 2018-08-09T09:02:44.887688 01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
25 2018-08-09T09:02:44.887705 01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
26 2018-08-09T09:02:44.888444 01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe
27 2018-08-09T09:02:44.889150 01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe from 0 (zlm2)
28 2018-08-09T09:02:44.889187 01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe from 1 (zlm3)
29 2018-08-09T09:02:44.889198 01:00 0 [Note] WSREP: Quorum results:
30     version    = 4,
31     component  = PRIMARY,
32     conf_id    = 11,
33     members    = 2/2 (primary/total),
34     act_id     = 22,
35     last_appl. = 0,
36     protocols  = 0/8/3 (gcs/repl/appl),
37     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
38 2018-08-09T09:02:44.889206 01:00 0 [Note] WSREP: Flow-control interval: [141, 141]
39 2018-08-09T09:02:44.889210 01:00 0 [Note] WSREP: Trying to continue unpaused monitor
40 2018-08-09T09:02:44.889304 01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
41 2018-08-09T09:02:44.889315 01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 12: Primary, number of nodes: 2, my index: 1, protocol version 3
42 2018-08-09T09:02:44.889319 01:00 2 [Note] WSREP: Setting wsrep_ready to true
43 2018-08-09T09:02:44.889324 01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 2 -> 2) (Increment: 3 -> 2)
44 2018-08-09T09:02:44.889328 01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
45 2018-08-09T09:02:44.889336 01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
46 2018-08-09T09:02:44.889355 01:00 0 [Note] WSREP: Service thread queue flushed.
47 2018-08-09T09:02:50.000210 01:00 0 [Note] WSREP:  cleaning up 74f3db69 (tcp://192.168.1.103:4567)

 

**Do some DML operations on node zlm3.**

本文由澳门十大赌场最新排名发布于澳门十大赌场,转载请注明出处:创建主键等常用操作总结,在PXC中重新添加掉线

关键词: PG电子游艺

上一篇:没有了

下一篇:没有了