xml地图|网站地图|网站标签 [设为首页] [加入收藏]
高级查询,聚集索引扫描
分类:数据库

 

最近发现一个分页查询存储过程中的的一个SQL语句,当聚集索引列的排序方式不同的时候,效率差别达到数十倍,让我感到非常吃惊
由此引发出来分页查询的情况下对大表做Clustered Scan的时候,
不同情况下会选择FORWARD 或者 BACKWARD差别,以及建立聚集索引时,选择索引列的排序方式的一些思考
废话不多,上代码
先建立一张测试表,在Col1上建立聚集索引,写入100W条数据

1、        修改表:

Preface

create table ClusteredIndexScanDirection
(
    Col1 int identity(1,1),
    Col2 varchar(50),
    Col3 varchar(50),
    Col4 Datetime
)


create unique clustered index idx_Col1 on ClusteredIndexScanDirection(Col1 ASC) 

DECLARE @date datetime,@i int=0
    set @date=GETDATE()
    while @i<1000000
    begin
    insert into ClusteredIndexScanDirection values (NEWID(),NEWID(),DATEADD(MI,@i,GETDATE()-200))
    set @i=@i+1
end

(1)修改表名语法:

ALTER TABLE <旧表名> RENAME [ TO ] <新表名>;

注意:其中[TO]为可选参数,使用与否不影响结果,仅修改表名,表结构不变;

(2)添加字段语法:

ALTER TABLE 表名 ADD 字段名 数据类型 [属性];

(3)修改字段语法:

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

其中,”原字段名“指修改前的字段名,“ 新字段名”指修改后的字段名,“数据类型”指修改后的数据类型,

如果不需要修改数据类型,则和原数据类型保持一致,但“数据类型”不能为空。

注意:由于不同的类型的数据储存方式和长度不同,修改数据类型可能会影响数据表中已有的数据,因此,此种情况下不应轻易修改数据类型。

(4)删除字段语法:

ALTER TABLE 表名 DROP字段名;

Eg:

DROP TABLE IF EXISTS `demo01`;

#创建表

CREATE TABLE  `demo01`(

       `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,

       `name` VARCHAR(8) NOT NULL

);

#修改表名

ALTER TABLE `demo01` RENAME `demo02`;

#添加字段

ALTER TABLE ` demo02 ` ADD `password` VARCHAR(32) NOT NULL;

#修改字段

ALTERTABLE ` demo02 ` CHANGE `name` `username`CHAR(10) NOT NULL;

#删除字段

ALTERTABLE ` demo02 ` DROP `password`;

 

 

2、        添加主外键:

    I got an replication error 1236 when I modified the password of a user without starting slave threads of replication.Further more,the user was absent in slave.below is the whole operation how I solve the issue.

 

(1)添加主键约束语法:

ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名(主键字段);

Eg:设置grade表中gradeId字段为主键

ALTER TABLE `grade` ADD CONSTRAINT `pk_grade` PRIMARY KEY `grade`(`gradeId`);

(2)添加外键约束语法:

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFE RENCES 关联表名(关联字段);

Eg:将student表的gradeId字段和grade表的gradeId字段建立外键关联:

ALTER TABLE `student`  ADD  CONSTRAINT fk_student_grade FOREIGN KEY(`gradeId`) REFERENCES `grade` (`gradeId`);

提示:在MySQL中,InnoDB储存类型的表支持外键,

MyISAM储存类型的表不支持外键,因此对于MySISAM储存类型的表,也可以通过建立逻辑关联的方式保证数据的完整性和一致性,

这种关联实际上是依照设计之初就定义好的固有逻辑,按照这种固有逻辑存取数据即可,不需要在数据库层面进行“硬绑定”。

 

先直观地看一下聚集索引扫描时候的FORWARD 和 BACKWARD

3、      DML(数据操作语言):

Procedure

 

(1)插入数据记录:

(A)插入单行数据记录语法:

        INSERT INTO 表名 [(字段名列表)] VALUES(值列表);

        Eg:

           INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`)

           VALUES('123','黄小平',1,'13956799999','1996-5-8');

其中:

1.表的字段名是可选的,如果省略,则依次插入所有字段。

2.多个列表和多个值之间使用逗号分隔。

3.值列表必须和字段名列表数量相同,且数据类型相符。

4.如果插入的是表中部分数据,字段名列表必须填写。

 (B)插入多行数据语法:在MySQL中INSERT语句支持一次插入多条记录,插入时可指定多个值列表,每个值列表之间逗号隔开。

          INSERT INTO 新表(字段名列表)VALUES(值列表1),(值列表2),……(值列表n);

          注意:插入数据时需要指定具体字段名。

         Eg:

           INSERT INTO `subject`(`subjectNo`,`subjectName`,`classHour`,`gradeID`)

           VALUES(1,'Logic Java',220,1),(2,'HTML',160,1),(3,'Java OOP',230,2);

 

          注意:为避免表结构发生变化引发的错误,建议插入数据时写明具体字段名!

          插入时间类型也需要 ‘ ’ 。

(2)将查询结果插入到新表语法:

        CREATE TABLE 新表(SELECT 字段1,字段2,..........FROM 原表);

         注意:新表无需事先创建,如新表已存在,将会报错!

(3)更新数据记录语法:

         UPDATE 表名 SET 字段1=值,字段2=值,。。。。字段n=值[WHERE 条件];

(4)删除数据记录语法:

         DELETE FROM 表名 [WHERE 条件];

          除此之外,也可以使用TRUNCATE语句删除表中所有行。语法:

          TRUNCATE TABLE 表名;

          注意:TRUNCATE 语句实现删除表中所有数据,删除后将重置自增列,表结构及其字段、约束、索引保持不变执行速度比DELETE语句快。

 

SQL Sever数据库与MySQL数据库把数据插入新表语法对比:

 

 

 BACKWARD

4、数据查询:

I modified the user password on master at first.

  执行如下分页查询,当按照Col4符合2017-7-18和2017-7-23,并且Col1 倒序排序的时候
  从执行计划看,Clustered Index Scan的Scan Direction的方式是BACKWARD

         ●查询回顾:SQL Server中查询语句在MySQL中同样支持;

1 root@localhost:mysql3306.sock [(none)]>alter user zlm@'192.168.56.%' identified by 'zlmzlm';
2 Query OK, 0 rows affected (0.00 sec)

  图片 1

SQL Service中的数据查询语法:

 

图片 2

SELECT<字段名列表>

**I then started the slave replication on zlm3.**

 

FRMO<表名或视图>

 1 #mysql -uroot -p -S /var/lib/mysql/mysql.sock
 2 Enter password: 
 3 Welcome to the MySQL monitor.  Commands end with ; or g.
 4 Your MySQL connection id is 3
 5 Server version: 5.7.21-log MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
14 
15 root@localhost:mysql.sock [(none)]>start slave;
16 Query OK, 0 rows affected (0.00 sec)
17 
18 root@localhost:mysql.sock [(none)]>show slave statusG
19 *************************** 1. row ***************************
20                Slave_IO_State: 
21                   Master_Host: 192.168.56.100
22                   Master_User: repl
23                   Master_Port: 3306
24                 Connect_Retry: 60
25               Master_Log_File: mysql-bin.000047
26           Read_Master_Log_Pos: 194
27                Relay_Log_File: relay-bin.000027
28                 Relay_Log_Pos: 4
29         Relay_Master_Log_File: mysql-bin.000047
30              Slave_IO_Running: No //The IO_Thread is not running on account of Error 1236
31             Slave_SQL_Running: Yes
32               Replicate_Do_DB: 
33           Replicate_Ignore_DB: 
34            Replicate_Do_Table: 
35        Replicate_Ignore_Table: 
36       Replicate_Wild_Do_Table: 
37   Replicate_Wild_Ignore_Table: 
38                    Last_Errno: 0
39                    Last_Error: 
40                  Skip_Counter: 0
41           Exec_Master_Log_Pos: 194
42               Relay_Log_Space: 532
43               Until_Condition: None
44                Until_Log_File: 
45                 Until_Log_Pos: 0
46            Master_SSL_Allowed: No
47            Master_SSL_CA_File: 
48            Master_SSL_CA_Path: 
49               Master_SSL_Cert: 
50             Master_SSL_Cipher: 
51                Master_SSL_Key: 
52         Seconds_Behind_Master: NULL
53 Master_SSL_Verify_Server_Cert: No
54                 Last_IO_Errno: 1236
55                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
56                Last_SQL_Errno: 0
57                Last_SQL_Error: 
58   Replicate_Ignore_Server_Ids: 
59              Master_Server_Id: 1003306
60                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
61              Master_Info_File: /data/mysql/mysql3306/data/master.info
62                     SQL_Delay: 0
63           SQL_Remaining_Delay: NULL
64       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
65            Master_Retry_Count: 86400
66                   Master_Bind: 
67       Last_IO_Error_Timestamp: 180721 09:05:50
68      Last_SQL_Error_Timestamp: 
69                Master_SSL_Crl: 
70            Master_SSL_Crlpath: 
71            Retrieved_Gtid_Set: 
72             Executed_Gtid_Set: 27af30ca-6800-11e8-ad7e-080027de0e0e:1-7,
73 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-2700058 //This is the id of maximum transaction executed on slave.
74                 Auto_Position: 1
75          Replicate_Rewrite_DB: 
76                  Channel_Name: 
77            Master_TLS_Version: 
78 1 row in set (0.00 sec)

 FORWARD

[WHERE <查询条件>]

 

  执行如下分页查询,当按照Col4符合2017-7-18和2017-7-23,并且Col1 正序排序的时候
  从执行计划看,Clustered Index Scan的Scan Direction的方式是FORWARD

[GROUP BY<分组字段名>]

**I decided to set gtid_purged variable to a certain value.So I executed "reset master;" to remove the value of "Executed_Gtid_Set".**

图片 3

[HAVING<条件>]

 1 root@localhost:mysql.sock [(none)]>reset master;
 2 Query OK, 0 rows affected (0.05 sec)
 3 
 4 root@localhost:mysql.sock [(none)]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: 192.168.56.100
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000047
12           Read_Master_Log_Pos: 194
13                Relay_Log_File: relay-bin.000027
14                 Relay_Log_Pos: 4
15         Relay_Master_Log_File: mysql-bin.000047
16              Slave_IO_Running: No
17             Slave_SQL_Running: Yes
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 194
28               Relay_Log_Space: 532
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 1236
41                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1003306
46                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
47              Master_Info_File: /data/mysql/mysql3306/data/master.info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 180721 09:11:00
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: //The value has been cleared after operation of "reset master;".
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

图片 4

[OGDER BY<排序的字段名>[ASC 或 DESC]]

 

  查询条件一样,分页情况下,排序方式不一样,性能上有么有差别?肯定有,太明显了,如果没有,本文也就没有什么意义了
  如图是上述两种查询方式在我本机的测试结果,同样是前100条数据,因为排序方式不同,其代价也是不同的
  逻辑读,一个是2327,一个是9978次,差别不小吧,在实际场景中,这个差别是非常非常大的,大到足以超乎你想想

 

**In order to know which value of "gtid_purged" I should set,I analyzed the binlog on master.**

 

Eg:

 1 zlm@192.168.56.100:3306 [(none)]>show master status;
 2 +------------------+----------+--------------+------------------+-------------------------------------------------+
 3 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 4 +------------------+----------+--------------+------------------+-------------------------------------------------+
 5 | mysql-bin.000090 |      440 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715627 |
 6 +------------------+----------+--------------+------------------+-------------------------------------------------+
 7 1 row in set (0.00 sec)
 8 
 9 zlm@192.168.56.100:3306 [(none)]>flush logs;
10 Query OK, 0 rows affected (0.04 sec)
11 
12 zlm@192.168.56.100:3306 [(none)]>show master status;
13 +------------------+----------+--------------+------------------+-------------------------------------------------+
14 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
15 +------------------+----------+--------------+------------------+-------------------------------------------------+
16 | mysql-bin.000091 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715627 |
17 +------------------+----------+--------------+------------------+-------------------------------------------------+
18 1 row in set (0.00 sec)
19 
20 [root@zlm1 09:16:13 /data/mysql/mysql3306/logs]
21 #mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000090 > /root/1.log
22 [root@zlm1 09:16:36 /data/mysql/mysql3306/logs]
23 #cd
24 
25 [root@zlm1 09:16:37 ~]
26 #ls -l|grep 1.log
27 -rw-r--r--   1 root root    1743 Jul 21 09:16 1.log
28 [root@zlm1 09:17:23 ~]
29 #less 1.log
30 ...
31 
32 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
33 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
34 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
35 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
36 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
37 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
38 DELIMITER /*!*/;
39 # at 4
40 #180721  9:01:29 server id 1003306  end_log_pos 123 CRC32 0x6e71f8f2    Start: binlog v 4, server v 5.7.21-log created 180721  9:01:29 at startup
41 ROLLBACK/*!*/;
42 # at 123
43 #180721  9:01:33 server id 1003306  end_log_pos 194 CRC32 0x074cfb08    Previous-GTIDs
44 # 2a4b3562-2ab6-11e8-be7a-080027de0e0e:5-12715626
45 # at 194
46 #180721  9:02:53 server id 1003306  end_log_pos 259 CRC32 0x9125f364    GTID    last_committed=0        sequence_number=1       rbr_only=no
47 SET @@SESSION.GTID_NEXT= '2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715627'/*!*/;
48 # at 259
49 #180721  9:02:53 server id 1003306  end_log_pos 440 CRC32 0x7a5353bf    Query   thread_id=3     exec_time=0     error_code=0
50 SET TIMESTAMP=1532156573/*!*/;
51 SET @@session.pseudo_thread_id=3/*!*/;
52 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
53 SET @@session.sql_mode=1436549152/*!*/;
54 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
55 /*!C utf8 *//*!*/;
56 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
57 SET @@session.lc_time_names=0/*!*/;
58 SET @@session.collation_database=DEFAULT/*!*/;
59 ALTER USER 'zlm'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*512FB3FAA6F522E351929CAC70AD8EBB0F5901B6'
60 /*!*/;
61 # at 440
62 #180721  9:11:46 server id 1003306  end_log_pos 487 CRC32 0x0882ea24    Rotate to mysql-bin.000091  pos: 4
63 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
64 DELIMITER ;
65 # End of log file
66 
67 //I've known that the operation of altering password is in the uuid:12715627.Therefore,I could set gtid_purged to the previous gtid(uuid:5-12715626).

 对FORWARD和BACKWARD有一个直观的感受之后,来说说这两者的区别

SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate`

 

  如果了解B树索引结构的话,应该知道聚集索引是以类似于B树结构的方式来组织的,既然是B树结构,
  那么下面这个图就不难理解了,
  在索引列按照某事方式排序的情况下,比如

FROM `student`

*I tried to set "gtid_purged" to the right value according to the content of binlog on master.*

  create unique clustered index idx_Col1 on ClusteredIndexScanDirection(Col1 ASC) 
  或者是
  create unique clustered index idx_Col1 on ClusteredIndexScanDirection(Col1 DESC) 

WHERE `gradeId` = 1

  1 root@localhost:mysql.sock [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:5-12715626';
  2 Query OK, 0 rows affected (0.00 sec)
  3 
  4 root@localhost:mysql.sock [(none)]>start slave io_thread;
  5 Query OK, 0 rows affected (0.01 sec)
  6 
  7 root@localhost:mysql.sock [(none)]>show slave statusG
  8 *************************** 1. row ***************************
  9                Slave_IO_State: 
 10                   Master_Host: 192.168.56.100
 11                   Master_User: repl
 12                   Master_Port: 3306
 13                 Connect_Retry: 60
 14               Master_Log_File: mysql-bin.000047
 15           Read_Master_Log_Pos: 194
 16                Relay_Log_File: relay-bin.000027
 17                 Relay_Log_Pos: 4
 18         Relay_Master_Log_File: mysql-bin.000047
 19              Slave_IO_Running: No //It was not running again.
 20             Slave_SQL_Running: Yes
 21               Replicate_Do_DB: 
 22           Replicate_Ignore_DB: 
 23            Replicate_Do_Table: 
 24        Replicate_Ignore_Table: 
 25       Replicate_Wild_Do_Table: 
 26   Replicate_Wild_Ignore_Table: 
 27                    Last_Errno: 0
 28                    Last_Error: 
 29                  Skip_Counter: 0
 30           Exec_Master_Log_Pos: 194
 31               Relay_Log_Space: 532
 32               Until_Condition: None
 33                Until_Log_File: 
 34                 Until_Log_Pos: 0
 35            Master_SSL_Allowed: No
 36            Master_SSL_CA_File: 
 37            Master_SSL_CA_Path: 
 38               Master_SSL_Cert: 
 39             Master_SSL_Cipher: 
 40                Master_SSL_Key: 
 41         Seconds_Behind_Master: NULL
 42 Master_SSL_Verify_Server_Cert: No
 43                 Last_IO_Errno: 1236
 44                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
 45                Last_SQL_Errno: 0
 46                Last_SQL_Error: 
 47   Replicate_Ignore_Server_Ids: 
 48              Master_Server_Id: 1003306
 49                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
 50              Master_Info_File: /data/mysql/mysql3306/data/master.info
 51                     SQL_Delay: 0
 52           SQL_Remaining_Delay: NULL
 53       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
 54            Master_Retry_Count: 86400
 55                   Master_Bind: 
 56       Last_IO_Error_Timestamp: 180721 09:20:25
 57      Last_SQL_Error_Timestamp: 
 58                Master_SSL_Crl: 
 59            Master_SSL_Crlpath: 
 60            Retrieved_Gtid_Set: 
 61             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:5-12715626
 62                 Auto_Position: 1
 63          Replicate_Rewrite_DB: 
 64                  Channel_Name: 
 65            Master_TLS_Version: 
 66 1 row in set (0.00 sec)
 67 
 68 root@localhost:mysql.sock [(none)]>reset master;
 69 Query OK, 0 rows affected (0.03 sec)
 70 
 71 root@localhost:mysql.sock [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715626'; //I began with the first gtid this time.
 72 Query OK, 0 rows affected (0.00 sec)
 73 
 74 root@localhost:mysql.sock [(none)]>stop slave;
 75 Query OK, 0 rows affected (0.00 sec)
 76 
 77 root@localhost:mysql.sock [(none)]>start slave;
 78 Query OK, 0 rows affected (0.00 sec)
 79 
 80 root@localhost:mysql.sock [(none)]>show slave statusG
 81 *************************** 1. row ***************************
 82                Slave_IO_State: Waiting for master to send event
 83                   Master_Host: 192.168.56.100
 84                   Master_User: repl
 85                   Master_Port: 3306
 86                 Connect_Retry: 60
 87               Master_Log_File: mysql-bin.000091
 88           Read_Master_Log_Pos: 194
 89                Relay_Log_File: relay-bin.000028
 90                 Relay_Log_Pos: 367
 91         Relay_Master_Log_File: mysql-bin.000090
 92              Slave_IO_Running: Yes //IO_Thread could work normally.
 93             Slave_SQL_Running: No //SQL_Thread was down instead.
 94               Replicate_Do_DB: 
 95           Replicate_Ignore_DB: 
 96            Replicate_Do_Table: 
 97        Replicate_Ignore_Table: 
 98       Replicate_Wild_Do_Table: 
 99   Replicate_Wild_Ignore_Table: 
100                    Last_Errno: 1396 //A new arrival error 1396 occurred.
101                    Last_Error: Error 'Operation ALTER USER failed for 'zlm'@'192.168.56.%'' on query. Default database: ''. Query: 'ALTER USER 'zlm'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*512FB3FAA6F522E351929CAC70AD8EBB0F5901B6''
102                  Skip_Counter: 0
103           Exec_Master_Log_Pos: 194
104               Relay_Log_Space: 1556
105               Until_Condition: None
106                Until_Log_File: 
107                 Until_Log_Pos: 0
108            Master_SSL_Allowed: No
109            Master_SSL_CA_File: 
110            Master_SSL_CA_Path: 
111               Master_SSL_Cert: 
112             Master_SSL_Cipher: 
113                Master_SSL_Key: 
114         Seconds_Behind_Master: NULL
115 Master_SSL_Verify_Server_Cert: No
116                 Last_IO_Errno: 0
117                 Last_IO_Error: 
118                Last_SQL_Errno: 1396
119                Last_SQL_Error: Error 'Operation ALTER USER failed for 'zlm'@'192.168.56.%'' on query. Default database: ''. Query: 'ALTER USER 'zlm'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*512FB3FAA6F522E351929CAC70AD8EBB0F5901B6''
120   Replicate_Ignore_Server_Ids: 
121              Master_Server_Id: 1003306
122                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
123              Master_Info_File: /data/mysql/mysql3306/data/master.info
124                     SQL_Delay: 0
125           SQL_Remaining_Delay: NULL
126       Slave_SQL_Running_State: 
127            Master_Retry_Count: 86400
128                   Master_Bind: 
129       Last_IO_Error_Timestamp: 
130      Last_SQL_Error_Timestamp: 180721 09:22:48
131                Master_SSL_Crl: 
132            Master_SSL_Crlpath: 
133            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715627
134             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715626
135                 Auto_Position: 1
136          Replicate_Rewrite_DB: 
137                  Channel_Name: 
138            Master_TLS_Version: 
139 1 row in set (0.00 sec)
140 
141 root@localhost:mysql.sock [(none)]>ALTER USER 'zlm'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*512FB3FAA6F522E351929CAC70AD8EBB0F5901B6'; //I couldn't executed it in mysql cli either.
142 ERROR 1396 (HY000): Operation ALTER USER failed for 'zlm'@'192.168.56.%'
143 root@localhost:mysql.sock [(none)]>

  下面这张图分别是FORWARD和BACKWARD两种Scan direction的实现方式

ORDER BY studentNo;

 

   图片 5

其中:

*Then,I checked the user on both master and slave.I found that there was no "zlm" user on slave at all.*

                FORWARD 

1.字段名列表为要查询的字段名、各字段之间使用逗号分隔、若查询表中所有字段、则使用“* ”号表示。

 1 //Master
 2 root@localhost:mysql3306.sock [(none)]>select user,host from mysql.user;
 3 +---------------+--------------+
 4 | user          | host         |
 5 +---------------+--------------+
 6 | repl          | 192.168.56.% |
 7 | zlm           | 192.168.56.% |
 8 | bkuser        | localhost    |
 9 | mysql.session | localhost    |
10 | mysql.sys     | localhost    |
11 | root          | localhost    |
12 +---------------+--------------+
13 6 rows in set (0.00 sec)
14 
15 //Slave
16 root@localhost:mysql.sock [(none)]>select user,host from mysql.user;
17 +---------------+--------------+
18 | user          | host         |
19 +---------------+--------------+
20 | repl          | 192.168.56.% |
21 | bkuser        | localhost    |
22 | mysql.session | localhost    |
23 | mysql.sys     | localhost    |
24 | root          | localhost    |
25 +---------------+--------------+
26 5 rows in set (0.00 sec)
27 
28 root@localhost:mysql.sock [(none)]>create user zlm@'192.168.56.%'; //I created the absent user "zlm" on slave.
29 Query OK, 0 rows affected (0.00 sec)
30 
31 root@localhost:mysql.sock [(none)]>start slave sql_thread;
32 Query OK, 0 rows affected (0.01 sec)
33 
34 root@localhost:mysql.sock [(none)]>show slave statusG
35 *************************** 1. row ***************************
36                Slave_IO_State: Waiting for master to send event
37                   Master_Host: 192.168.56.100
38                   Master_User: repl
39                   Master_Port: 3306
40                 Connect_Retry: 60
41               Master_Log_File: mysql-bin.000091
42           Read_Master_Log_Pos: 194
43                Relay_Log_File: relay-bin.000030
44                 Relay_Log_Pos: 407
45         Relay_Master_Log_File: mysql-bin.000091
46              Slave_IO_Running: Yes
47             Slave_SQL_Running: Yes //It was ok.
48               Replicate_Do_DB: 
49           Replicate_Ignore_DB: 
50            Replicate_Do_Table: 
51        Replicate_Ignore_Table: 
52       Replicate_Wild_Do_Table: 
53   Replicate_Wild_Ignore_Table: 
54                    Last_Errno: 0
55                    Last_Error: 
56                  Skip_Counter: 0
57           Exec_Master_Log_Pos: 194
58               Relay_Log_Space: 695
59               Until_Condition: None
60                Until_Log_File: 
61                 Until_Log_Pos: 0
62            Master_SSL_Allowed: No
63            Master_SSL_CA_File: 
64            Master_SSL_CA_Path: 
65               Master_SSL_Cert: 
66             Master_SSL_Cipher: 
67                Master_SSL_Key: 
68         Seconds_Behind_Master: 0
69 Master_SSL_Verify_Server_Cert: No
70                 Last_IO_Errno: 0
71                 Last_IO_Error: 
72                Last_SQL_Errno: 0
73                Last_SQL_Error: 
74   Replicate_Ignore_Server_Ids: 
75              Master_Server_Id: 1003306
76                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
77              Master_Info_File: /data/mysql/mysql3306/data/master.info
78                     SQL_Delay: 0
79           SQL_Remaining_Delay: NULL
80       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
81            Master_Retry_Count: 86400
82                   Master_Bind: 
83       Last_IO_Error_Timestamp: 
84      Last_SQL_Error_Timestamp: 
85                Master_SSL_Crl: 
86            Master_SSL_Crlpath: 
87            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715627
88             Executed_Gtid_Set: 27af30ca-6800-11e8-ad7e-080027de0e0e:1, //This gtid was generated by the statment of "create user ... ".
89 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715627 //The executed gtid was equal with the master.
90                 Auto_Position: 1
91          Replicate_Rewrite_DB: 
92                  Channel_Name: 
93            Master_TLS_Version: 
94 1 row in set (0.00 sec)

 

2.FROM后的表名为要查询的数据的来源,可以单个可以多个。

 

图片 6

3.WHERE子句为可选项,指定查询的条件。

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:高级查询,聚集索引扫描

上一篇:SERVER诊断系列,column快很多奇怪案例分析 下一篇:SQL获取数据库名,mysql字符集和字符排序
猜你喜欢
热门排行
精彩图文