學習筆記: 6月 2019

2019年6月26日星期三

order by

1.order by 子句，盡量使用index方式排序，避免使用 filesort方式排序（即explain select...,查看Extra的欄位，using index 優於 using filesort）
2.盡可能在索引列上完成排序操作，遵照索引的最佳左前綴
3.如果不在索引列上，filesort會使用到sort_buffer的容量去進行排序，如果查詢的字段的總數據超出了sort_buffer的容量，就會導致每次只能取sort_buffer容量大小的數據，進行排序（創建tmp文件，再合併），排完後再次取sort_buffer容量大小，再排.....大量的I/O操作。解決：增大max_length_for_sort_data參數的設置＋增大sort_buffer_size參數的設置

提高order by 的速度

order by 時大忌是使用 select *
當query的字段大小總和小於 max_length_for_sort_data 而且排序字段不是TEXT | BLOB類型時，會用上列第三個算法排序
當嘗試提高sort_buffer_size，此參數會提高效率，但實際還是要根據系統的能力去提高，因為此值針對每個進程的
當嘗試提高max_length_for_sort_data，如果設太高，數據總容量會超出sort_buffer_size的概率就會增大，反而是提高硬碟I/O活動和降低處理器使用率

範例：order by為排序使用索引

MYSQL兩種排序方式：結果直接或結果字段使用到索引即產生有效排序

MYSQL能為排序與查詢使用相同的索引

key a_b_c(a,b,c)

order by 可以使用到索引最左前綴

-order by a

-order by a,b

-order by a,b,c

-order by a desc, b desc , c desc

如果where使用索引的最左前綴定義為常量(const)，則order by 能使用索引

-where a = const order by b,c

-where a = const and b = const order by c

-where a = const order by b,c

-where a = const and b > const order by b,c

不能使用索引進行排序

-order by a asc, b desc , c desc (因排序不一致)

-where f = const order by b,c (a索引丟失)

-where a = const order by c (b索引丟失)

-where a = const order by a,d (d非索引)

-where a in (...) order by b,c (a = const order by a,d (d非索引)

-where a in (...) order by b,c (對於排序來說，多個相等條件也是範圍查詢)

group by 同order by

group by 是先排序後再進行分組，遵照索引建的最佳左前綴原則
當無法使用索引列時，增大max_length_for_sort_data參數的設置＋增大sort_buffer_size參數的設置
where 高於having，能寫在where限定的條件就不要去having限定了

2019年6月25日星期二

欄位及欄位值說明：
【id】 : 此數字表示執行select 操作表的順序，id越大最優先123的順序就是321

情況1：id相同，執行順序由上至下
情況2：id不同，如果是子查詢，id的序號會遞增，id值越大優先執行
情況3：id相同與不同都有，可認為是同一組，從上往下順序執行，id值越大則優先執行

【select_type】:

SIMPLE：簡單查詢，查詢中不含子查詢或UNION

PRIMARY：查詢中若包含任何複雜的子部份，最外層的查詢則被標記為primary

SUBQUERY：在select或where列表中包含子查詢

DERIVED：衍生表，在FROM列表中包含的子查詢被標記為DERIVED，MYSQL會將歸類這些子查詢，於臨時表中

2019年6月5日星期三

【MYSQL】mysql無法連線-排錯方法

mysql無法連線問題有以下可能，

1.查詢mysql.user表的host和user列,是否輸帳的帳號及IP有對應。

如下顯示，使用者rosalie使用非 10.23.25.18 就會造成無法連線登入到DB

mysql> select user,host from mysql.user;

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

| user             | host           |

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

| root             | 192.168.1.1    |

| rosalie          | 10.23.25.18    |

| root             | localhost      |

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

2.密碼錯誤,一般報1045錯誤，代碼可用（perror 代碼）查詢

[root@rosalie-mysql01 mysql]# mysql -uroot -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

查詢其他代碼

[root@rosalie-mysql01 mysql]# perror 1045

MySQL error code 1045 (ER_ACCESS_DENIED_ERROR): Access denied for user '%-.48s'@'%-.64s' (using password: %s)

3.是否設定了禁用dns解析,即設定了skip_name_resolve卻還是用主機名稱連線

※用反解析，簡單的說，就是HOST只能用IP登入，不能用名稱登入

4.是否設定了限制ip訪問(白名單)，即設定了bind-address卻還是用其他ip連線

5.確認是否是網路問題，一般直接ping ip的方式確定，這時先解決網路問題

6.檢查磁碟空間是否已經滿了,即磁碟不足

[root@rosalie-mysql01 mysql]# df -h

7. 檢查mysqld程序是否存活

[root@rosalie-mysql01 mysql]# service mysqld status

或

[root@rosalie-mysql01 mysql]# systemctl status mysqld

8.檢查防火牆,是否開通了對應的db port

[root@rosalie-mysql01 mysql]# netstat -antp | grep 3306

tcp        0      0 ::3306           :::*             LISTEN      26370/mysqld    

9.確認是否連線數打滿了,一般報 MySQL: ERROR 1040: Too many connections

Max_used_connections 伺服器響應的最大連線數大於 max_connections最大連接數

以下例是沒有超過

mysql> show variables like '%max_connections%';

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

| Variable_name   | Value |

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

| max_connections | 151   |

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

1 row in set (0.00 sec)

mysql> show global status like 'Max_used_connections';

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

| Variable_name        | Value |

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

| Max_used_connections | 6     |

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

1 row in set (0.00 sec)

2019年6月3日星期一

【MYSQL】一台機器，開啟兩個MYSQL服務(不同數據)

機器主~master-192.168.73.76_5.7.22測試

先查看目前使用的版本

[root@rosalie-mysql02 data]# mysql -V

mysql Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

因為要一台機器啟動兩個mysql，且連結的db也庫不同，看需求，可以同版本，亦可不同版本

這邊我們用同一個版本所以（紅色指令）

[root@rosalie-mysql02]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.16-linux-glibc2.12-x86_64.tar

[root@rosalie-mysql02 ~]# tar xvf mysql-8.0.16-linux-glibc2.12-x86_64.tar

mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

mysql-router-8.0.16-linux-glibc2.12-x86_64.tar.xz

mysql-test-8.0.16-linux-glibc2.12-x86_64.tar.xz

[root@rosalie-mysql02 ~]# tar -xJf mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

[root@rosalie-mysql02 ~]# mv mysql-8.0.16-linux-glibc2.12-x86_64 /usr/local/mysql_3308

1.先下載 MYSQL 8.0.16

2.解打包，會產生三個檔案，主要是 mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

3.解tar.xz的壓縮檔

4.將解好的壓縮檔移動到/usr/local/mysql_3308 因原本沒有，所以移動會自動更名---這是啟動的位置

這是原本的my.cnf的設定

[mysqld]

# 原本就啟動中的DB~my.cnf的基本設定

datadir =/data/mysql

basedir =/usr/local/mysql

socket =/tmp/mysql.sock

log_bin =mysql-bin

port 　 = 3306

server-id = 01

log_error =/data/mysql/rosalie-mysql02.err

pid-file =/data/mysql/rosalie-mysql02.pid

可以由上看到原DB其 datadir資料夾路徑為/data/mysql

所以先創另一個資料夾以不同PORT-3308來區別

[root@rosalie-mysql01 data] # mkdir /data/mysql_3308

[root@rosalie-mysql01 data] # chown mysql:mysql /data/mysql_3308

然後考備原my.cnf的設定到/etc/下,自行命名不同的名字

[root@rosalie-mysql01 data] # cp /etc/my.cnf /etc/mysql_3308_my.cnf

接著編輯3308 PORT的 my.cnf

[root@rosalie-mysql01 data] # vi /etc/mysql_3308_my.cnf

更改參數如下

[mysqld]------第二台mysql的my.cnf

datadir =/data/mysql_3308

basedir =/usr/local/mysql_3308

socket =/tmp/mysql_3308.sock

log_bin =mysql-bin

port 　 = 3308

server-id = 11

log_error =/data/mysql_3308/rosalie-mysql02.err

pid-file =/data/mysql_3308/rosalie-mysql02.pid

port = 3308

這一步是在剛剛安裝建立的目錄下面，初始化數據庫。（紅色是指令，紫色是初始化後產生的帳密）

[root@rosalie-mysql02 local]# /usr/local/mysql_3308/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql_3308 --datadir=/data/mysql_3308

2019-06-03T07:09:54.333434Z 0 [System] [MY-013169] [Server] /usr/local/mysql_3308/bin/mysqld (mysqld 8.0.16) initializing of server in progress as process 2523

2019-06-03T07:09:55.964734Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: uWJbs%lJw9ZW

2019-06-03T07:09:56.739579Z 0 [System] [MY-013170] [Server] /usr/local/mysql_3308/bin/mysqld (mysqld 8.0.16) initializing of server has completed

接著啟動DB--- 指令下之後會停住，但已啟動，可關閉或是再開一個視窗

[root@rosalie-mysql01 etc]# mysqld_safe --defaults-file=/etc/mysql_3308_my.cnf &

登入方式，指令輸入完成後，再輸入密碼即可登入

[root@rosalie-mysql01 etc]# mysql -uroot -p -P3308 --socket=/tmp/mysql_3308.sock

登入後，必需先改密碼才能使用！！

mysql> show databases;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user user() identified by "12345";

Query OK, 0 rows affected (0.06 sec)

#會看到是完全新的數據庫，不影響原本機器內的另一個數據庫，也不需要重啟MYSQL

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

查看啟動的端口有哪些，出現3306及3308

[root@rosalie-mysql02 ~]# netstat -tulpn

Active Internet connections (only servers)

略

tcp 0 0 :::3306 :::* LISTEN 1308/mysqld

tcp 0 0 :::3308 :::* LISTEN 2807/mysqld

略

若要關閉3308

[root@rosalie-mysql02 tmp]# /usr/local/mysql_3308/bin/mysqladmin -uroot -p -S /tmp/mysql_3308.sock shutdown

再次查看端口，只剩下3306

[root@rosalie-mysql02 tmp]# netstat -tulpn

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1420/sshd

tcp 0 0 :::3306 :::* LISTEN 1308/mysqld

學習筆記

2019年6月26日星期三

【MYSQL】order by 和group by索引優化

order by

範例：order by為排序使用索引

2019年6月25日星期二

【MYSQL】EXPLAIN - 欄位說明解釋

2019年6月5日星期三

【MYSQL】mysql無法連線-排錯方法

2019年6月3日星期一

【MYSQL】一台機器，開啟兩個MYSQL服務(不同數據)

【MYSQL】MYSQL的SYS表說明（版本8.0）

2019年6月26日 星期三

【MYSQL】order by 和group by索引優化

order by

範例：order by為排序使用索引

2019年6月25日 星期二

【MYSQL】EXPLAIN - 欄位說明解釋

2019年6月5日 星期三

【MYSQL】mysql無法連線-排錯方法

2019年6月3日 星期一

【MYSQL】一台機器，開啟兩個MYSQL服務(不同數據)

【MYSQL】MYSQL的SYS表說明（版本8.0）

2019年6月26日星期三

2019年6月25日星期二

2019年6月5日星期三

2019年6月3日星期一