MySQL基本概念【一】

哎呀我的天吶發表於2015-07-25

1.看錶的DDL建立語句
show create table TABLE_NAME\G;

點選(此處)摺疊或開啟

mysql> show create table dept_emp\G;
*************************** 1. row ***************************
Table: dept_emp
Create Table: CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`),
KEY `emp_no` (`emp_no`),
KEY `dept_no` (`dept_no`),
CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified

2.在列上建立索引，和Oracle一樣

點選(此處)摺疊或開啟

mysql> create index emp_no_ind on dept_emp(emp_no);
Query OK, 0 rows affected, 1 warning (2.37 sec)
Records: 0 Duplicates: 0 Warnings: 1

很多書上說，在建立主鍵的時候要建立自增主鍵，為什麼不同於Oracle，因為Oracle的表是堆表，大家都明白，Oracle的索引是。innodb的表是天然的樹形結構，如果你不建立primary key 他也會給你建立，一個列上有唯一性的id，你看不見他，他是根據記憶體裡面的一個值，然後遞增生成的，他不是為單獨的表遞增的，他是為系統中所有的表遞增，是個全域性性total order的id，這樣就成為一個熱點，為了避免這種熱點，我們在建立MySQL中的表的時候要指定遞增唯一主鍵，所以MySQL中插入一條資料不是說找到一個空閒的記憶體區就放在上面，而是放在自己的B-Tree位置。是Oracle IOT表的概念。

InnoDB的一個page是16K，如果你的id列不是自增，如果你插入了id = 1，id = 14，第一個id在B-Tree的最左端，機械盤對隨機讀的衰減的影響非常大。如果id相鄰 1 2 3 那麼一個page就搞定了即一個IO，這就是為什麼要有自增索引， SSD減少隨機讀了，但不能消除。

3.重新命名錶

點選(此處)摺疊或開啟

mysql> rename table test to test_bak;
Query OK, 0 rows affected (0.01 sec)

4.新增列

點選(此處)摺疊或開啟

mysql> alter table test add sal int;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+---------+------+
| id | ename | sal |
+------+---------+------+
| 1 | wuyusen | NULL |
+------+---------+------+

在MySQL5.5之前，新增列，表中的資料會被影響，我現在是5.6.22表中有一行資料，在我新增列的時候，0 rows affected，5.5的話會有1 rows affected，5.5寫入會被阻塞。

5.alter table drop index

6.index

點選(此處)摺疊或開啟

mysql> select * from test where id = 1;

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

| id | ename | sal |

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

| 1 | wuyusen | NULL |

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

1 row in set (0.00 sec)

mysql> select * from test limit 10\G;

*************************** 1. row ***************************

   id: 1

ename: wuyusen

  sal: NULL

*************************** 2. row ***************************

   id: 2

ename: aaa

  sal: NULL

2 rows in set (0.00 sec)

ERROR:

No query specified

mysql> select * from test where id = 1\G;

*************************** 1. row ***************************

   id: 1

ename: wuyusen

  sal: NULL

1 row in set (0.00 sec)

ERROR:

No query specified

下面是

點選(此處)摺疊或開啟

mysql> explain select * from test where id =1\G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: test

         type: ref

possible_keys: test_id_ind

          key: test_id_ind

      key_len: 5

          ref: const

         rows: 1

        Extra: NULL

1 row in set (0.00 sec)

ERROR:

No query specified

點選(此處)摺疊或開啟

mysql> explain select * from test where id = 1;

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

| id | select_type | table | type | possible_keys |   key | key_len | ref | rows | Extra |

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

| 1 |   SIMPLE |  test | ref |   test_id_ind | test_id_ind | 5 | const |   1 | NULL |

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

1 row in set (0.01 sec)

先做詞法解析，把select * from test 每個單詞，是否是符合語法，生成執行計劃，這裡的possible_keys是可能選擇走的索引，如果有主鍵和一個索引的話，possible_keys就會有兩個值，key上就會出現真實的走的是哪個，

7.將一個表匯出來

點選(此處)摺疊或開啟

mysqldump -h127.0.0.1 -uroot employees salaries > /media/employees.sql

點選(此處)摺疊或開啟

[root@MysqlPri media]# more employees.sql
-- MySQL dump 10.13 Distrib 5.6.22, for Linux (x86_64)
--
-- Host: 127.0.0.1 Database: employees
-- ------------------------------------------------------
-- Server version 5.6.22-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `salaries`
--
DROP TABLE IF EXISTS `salaries`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`),
KEY `emp_no` (`emp_no`),
CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `salaries`
--
LOCK TABLES `salaries` WRITE;
/*!40000 ALTER TABLE `salaries` DISABLE KEYS */;
INSERT INTO `salaries` VALUES (10001,60117,'1986-06-26','1987-06-26'),(10001,62102,'1987-06-26','1988-06-25'),(10001,66074,'1988-06-25','1989-06-25'),(10001,66596,'1989-06-25','1990-06-25'),(
10001,66961,'1990-06-25','1991-06-25'),(10001,71046,'1991-06-25','1992-06-24'),(10001,74333,'1992-06-24','1993-06-24'),(10001,75286,'1993-06-24','1994-06-24'),(10001,75994,'1994-06-24','1995-
06-24'),(10001,76884,'1995-06-24','1996-06-23'),(10001,80013,'1996-06-23','1997-06-23'),(10001,81025,'1997-06-23','1998-06-23'),(10001,81097,'1998-06-23','1999-06-23'),(10001,84917,'1999-06-2
3','2000-06-22'),(10001,85112,'2000-06-22','2001-06-22'),(10001,85097,'2001-06-22','2002-06-22'),(10001,88958,'2002-06-22','9999-01-01'),(10002,65828,'1996-08-03','1997-08-03'),(10002,65909,'
1997-08-03','1998-08-03'),(10002,67534,'1998-08-03','1999-08-03'),(10002,69366,'1999-08-03','2000-08-02'),(10002,71963,'2000-08-02','2001-08-02'),(10002,72527,'2001-08-02','9999-01-01'),(1000
3,40006,'1995-12-03','1996-12-02'),(10003,43616,'1996-12-02','1997-12-02'),(10003,43466,'1997-12-02','1998-12-02'),(10003,43636,'1998-12-02','1999-12-02'),(10003,43478,'1999-12-02','2000-12-0
1'),(10003,43699,'2000-12-01','2001-12-01'),(10003,43311,'2001-12-01','9999-01-01'),(10004,40054,'1986-12-01','1987-12-01'),(10004,42283,'1987-12-01','1988-11-30'),(10004,42542,'1988-11-30','
1989-11-30'),(10004,46065,'1989-11-30','1990-11-30'),(10004,48271,'1990-11-30','1991-11-30'),(10004,50594,'1991-11-30','1992-11-29'),(10004,52119,'1992-11-29','1993-11-29'),(10004,54693,'1993
-11-29','1994-11-29'),(10004,58326,'1994-11-29','1995-11-29'),(10004,60770,'1995-11-29','1996-11-28'),(10004,62566,'1996-11-28','1997-11-28'),(10004,64340,'1997-11-28','1998-11-28'),(10004,67
096,'1998-11-28','1999-11-28'),(10004,69722,'1999-11-28','2000-11-27'),(10004,70698,'2000-11-27','2001-11-27'),(10004,74057,'2001-11-27','9999-01-01'),(10005,78228,'1989-09-12','1990-09-12'),
(10005,82621,'1990-09-12','1991-09-12'),(10005,83735,'1991-09-12','1992-09-11'),(10005,85572,'1992-09-11','1993-09-11'),(10005,85076,'1993-09-11','1994-09-11'),(10005,86050,'1994-09-11','1995
-09-11'),(10005,88448,'1995-09-11','1996-09-10'),(10005,88063,'1996-09-10','1997-09-10'),(10005,89724,'1997-09-10','1998-09-10'),(10005,90392,'1998-09-10','1999-09-10'),(10005,90531,'1999-09-
10','2000-09-09'),(10005,91453,'2000-09-09','2001-09-09'),(10005,94692,'2001-09-09','9999-01-01'),(10006,40000,'1990-08-05','1991-08-05'),(10006,42085,'1991-08-05','1992-08-04'),(10006,42629,
'1992-08-04','1993-08-04'),(10006,45844,'1993-08-04','1994-08-04'),(10006,47518,'1994-08-04','1995-08-04'),(10006,47917,'1995-08-04','1996-08-03'),(10006,52255,'1996-08-03','1997-08-03'),(100

40101是版本相容
直接source XX.sql 就能匯入，不同的庫，
也可以：
select * from test into outfile '/backup/xx.sql';

8.資料遷移

方式一：停機時間少（簡單）
如果5.5 遷移5.6那麼搭建備庫，master 5.5，slave 5.6接到主庫5.5上。
設定read_only 為on，這樣的你的庫就是隻讀的了，root使用者限制不住。主庫沒有寫入，備庫將bin-log應用完，然後將應用、DNS切換到備庫。

5.5 master --&gt5.5 slave
|
5.6 slave
|

點選(此處)摺疊或開啟

mysql> set global read_only=on

點選(此處)摺疊或開啟

mysql> show variables like '%read%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 23
Current database: test
+-----------------------------------------+---------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------+
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_write_io_threads | 4 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 1 |
| net_read_timeout | 30 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 402 |
| pseudo_thread_id | 23 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 33554432 |
| thread_cache_size | 9 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
| tx_read_only | OFF |
+-----------------------------------------+---------------------------+
23 rows in set (0.00 sec)

9.bin log

點選(此處)摺疊或開啟

mysql> show binlog events

點選(此處)摺疊或開啟

mysql> reset master;
Query OK, 0 rows affected (0.07 sec)
mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+---------------------------------------+
| mybinlog.000001 | 4 | Format_desc | 203306 | 120 | Server ver: 5.6.22-log, Binlog ver: 4 |
+-----------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)

reset master是清理bin log的，下面我們建立一個表再看bin log

點選(此處)摺疊或開啟

mysql> create table xx (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> show binlog master;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master' at line 1
mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+---------------------------------------+
| mybinlog.000001 | 4 | Format_desc | 203306 | 120 | Server ver: 5.6.22-log, Binlog ver: 4 |
| mybinlog.000001 | 120 | Query | 203306 | 218 | use `test`; create table xx (id int) |
+-----------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

第二行就是建立xx表，記錄在bin log中的內容。
120位元組的位置，多了個query 操作，相當Oracle redo log。

點選(此處)摺疊或開啟

[root@MysqlPri logs]# mysqlbinlog -vvv mybinlog.000001 > /media/log.log
[root@MysqlPri logs]# more /media/log.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150724 6:30:41 server id 203306 end_log_pos 120 CRC32 0x3d6c3f2f Start: binlog v 4, server v 5.6.22-log created 150724 6:30:41 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
EWuxVQ8qGgMAdAAAAHgAAAABAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAARa7FVEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAS8/
bD0=
'/*!*/;
# at 120
#150724 6:32:46 server id 203306 end_log_pos 218 CRC32 0x5e7a611a Query thread_id=23 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1437690766/*!*/;
SET @@session.pseudo_thread_id=23/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table xx (id int)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/

也可以用insert 看看，實驗下這裡就不做了，

將當前session sql的binlog關閉

點選(此處)摺疊或開啟

mysql> set session sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)

與nologging還不太一樣，這個binlog是完完全全的不記錄日誌。

切換日誌，現在已經變成002了... ...

點選(此處)摺疊或開啟

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000002 | 120 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)

這個server 層的redo ，他是不能迴圈利用的，類似於Oracle的Archive Log。InnoDB有自己的redo，其中ibdata1是InnoDB的後設資料，ib_logfile1、ib_logfile1 預設在datadir下，我們也可以指定其位置，這個是迴圈應用的，0用完用1，1用完用0，類似Redo但不完全相同。InnoDB log不歸檔，Oracle的redo只有一份即用來做例項的recovery，又用來備庫、備份，但是InnoDB的iblog只能用來例項恢復，只保證InnoDB層不丟資料，至於你後續更改了什麼，那是server層記錄的日誌，這就是inno層，server層的不同，也是為什麼mysql多種引擎，InnoDB他脫離的MySQL能是個資料庫，最初InnoDB就是個資料庫，後來InnoDB發展不好，同時MySQL的server層介面很好就結合在一起，這就是為什麼有兩個日誌。你可以這麼理解，server層的bin log只記錄理解為DML，DDL使用者的操作，那麼InnoDB的iblog就是記錄這些操作怎麼儲存，你是轉換成什麼位元組，什麼編碼二進位制，server層不管你plugin了什麼引擎，只要你能給我存進去就行.... ....

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1751252/，如需轉載，請註明出處，否則將追究法律責任。

Mysql基本概念
2018-12-25
MySql
MySQL MVCC中的基本概念
2024-04-06
MySqlMVC
GacUI基本概念（一）
2019-05-10
UI
資料庫（01）基本概念丨MySQL
2018-11-30
資料庫MySql
RocketMQ系列（一）基本概念
2020-06-03
MQ
RocketMQ-基本概念(一)
2024-04-22
MQ
Thrift原理分析(一)基本概念
2018-11-07
【機器學習】第一節 - 基本概念
2024-04-06
機器學習
一文了解Docker基本概念
2021-11-21
Docker
Oracle 的一些基本概念
2010-11-14
Oracle
MySQL字符集基本概念和詳細設定
2016-06-22
MySql
【cbo計算公式】CBO基本概念(一)
2018-08-19
公式
Python爬行動物（一）：基本概念
2015-06-16
Python
一眼看穿?JS基本概念
2019-02-16
JS
轉儲存的一些基本概念
2015-01-30
AIX儲存管理基本概念和操作（一)
2015-08-22
AI
WebLogic中的一些基本概念
2015-11-11
Web
Java 多執行緒基礎（一）基本概念
2020-06-05
Java執行緒
ES 入門（一）：安裝部署與基本概念
2019-02-14
一文掌握GitHub Actions基本概念與配置
2022-06-25
Github
資料結構的一些基本概念
2021-01-22
資料結構
資料結構之第一講基本概念
2020-10-09
資料結構
IPsec相關的一些基本概念
2017-05-04
js完整教程一：基本概念和陣列操作
2016-01-04
JS陣列
儲存的一些基本概念（HBA，LUN）
2008-09-17
RocketMQ基本概念
2019-04-03
MQ
Kafka基本概念
2020-12-13
Kafka
mongodb 基本概念
2022-09-07
MongoDB
javascript：基本概念
2018-04-21
JavaScript
Kafka 基本概念
2017-05-07
Kafka
MQ 基本概念
2017-05-09
MQ
機器學習基本概念
2017-11-15
機器學習
執行查詢第一篇：基本概念
2020-05-24
統計學習一：1.概論：基本概念
2018-08-09
ElasticSearch 學習筆記(一) 基本概念與基本使用
2022-07-18
Elasticsearch筆記
Shiro學習筆記(一) 基本概念與使用
2022-06-11
筆記
oracle 11g RAC 的一些基本概念
2016-12-14
Oracle
ORACLE執行計劃的一些基本概念
2008-07-26
Oracle

MySQL基本概念【一】

相關文章