【MYSQL】記錄一次DBI connect(';;mysql_read_default_group=client','root',...) failed: Can't connect to local MySQL server through socket...報錯-版本5.7

[root@rosalie-mysql01 ~]# pt-duplicate-key-checker -u root -p1234 --database=tttt

DBI connect(';;mysql_read_default_group=client','root',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /usr/local/bin/pt-duplicate-key-checker line 894

顯示預設的sock路徑有誤，重新指定正確路徑即可，加入socket=路徑

加入紫色字段即可

[root@rosalie-mysql01 ~]# pt-duplicate-key-checker -u root -p1234 --database=tttt --socket=/tmp/mysql.sock

# ########################################################################

# tttt.test_index

# ########################################################################

# ID_2 is a duplicate of PRIMARY

# Key definitions:

# KEY `ID_2` (`ID`),

# PRIMARY KEY (`ID`),

# Column types:

# `id` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `ID_2`;

# Uniqueness of ID ignored because PRIMARY is a duplicate constraint

# ID is a duplicate of PRIMARY

# Key definitions:

# UNIQUE KEY `ID` (`ID`),

# PRIMARY KEY (`ID`),

# Column types:

# `id` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `ID`;

# B is a left-prefix of BCD

# Key definitions:

# KEY `B` (`B`),

# KEY `BCD` (`B`,`C`,`D`),

# Column types:

# `b` int(11) not null

# `c` int(11) not null

# `d` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `B`;

# A is a left-prefix of ABC

# Key definitions:

# KEY `A` (`A`),

# KEY `ABC` (`A`,`B`,`C`),

# Column types:

# `a` int(11) not null

# `b` int(11) not null

# `c` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `A`;

# ########################################################################

# Summary of indexes

# ########################################################################

# Size Duplicate Indexes 16

# Total Duplicate Indexes 4

# Total Indexes 8

【MYSQL】percona-toolkit工具-利用pt-duplicate-key-checker找出重複索引與冗餘索引

因實際環境上，可能開發因為需求不斷增加，但卻沒有仔細分析檢測過是否重複，此時可以利用percona-toolkit工具內的pt-duplicate-key-checker功能，來找出重複索引與冗餘索引。

安裝方法可參考↓

https://rosalie1211.blogspot.com/2019/03/mysqlpercona-toolkit-304-rpmtar.html

pt-duplicate-key-checker的使用方法

pt-duplicate-key-checker [OPTION...] [DSN]

使用說明：檢查重複或多餘(冗餘)的索引和外鍵，並打印出信息。

參數說明：加粗的為常用的。

--all-structs：

結構（B樹，哈希等）的索引，默認情況下是禁止的。因為BTREE索引可能會覆蓋作為FULLTEXT索引的列，其實他們不是真正的重複，因為是不同的索引結構。

--ask-pass 

提示輸入密碼。

--charset

縮寫-A，字符集設置：utf8，gbk，latin1 …

--[no] clustered

主鍵和第二索引一起是重複的，默認開啟。檢測時的非主鍵索引的多列索引的後綴是一個主鍵最左邊的前綴，則作為一個重複鍵。如：

PRIMARY KEY (`a`)

KEY `b` (`b`,`a`)

SELECT ... WHERE b=1 ORDER BY a;

如果按照這個工具去掉b索引，則會出現filesort，他給的意見是留下b（b）單列索引，把order by a去掉，因為他們的結果是一樣的，a是主鍵。

--config 

 讀取該配置文件的列表。

--databases

只檢查該列表中的數據庫。

--defaults-file

縮寫-F，讀取Mysql的配置文件，需要絕對路徑。

--engines

縮寫-e，只檢查該列表中指定的表的存儲引擎。

--tables

縮寫-t，只檢查列表中指定的表。

--help 

顯示幫助

--host

縮寫-h，連接到主機地址。

--ignore-databases

跳過檢查的某些數據庫。

--ignore-engines

跳過檢查的某些存儲引擎。

--ignore-tables

 跳過檢查某些表。

--ignore-order

加了這個參數會報： KEY(a,b)和KEY(b,a)是重複索引，默認關閉。

--key-types

檢查索引的類型：f=foreignkeys, k=keys or fk=both，默認是fk。

--password

縮寫-p，連接mysql時候的密碼。

--pid 

連接的PID

--port

縮寫-P，連接時候的端口。

--socket

縮寫-S，連接時候的套接字。

--[no]sql

打印出sql，如果有重複會答應出刪除重複索引的sql。默認開啟。

--[no]summary

打印出索引的統計信息。默認開啟。

--verbose 

縮寫-v，打印出所有的索引信息，包括重複索引。

--version 

打印版本信息。

#創建一個test_index表，並加入多個索引

mysql >

CREATE TABLE test_index(

    ID INT NOT NULL PRIMARY KEY,

    A INT NOT NULL,

    B INT NOT NULL,

    C INT NOT NULL,

    D INT NOT NULL,

    UNIQUE(ID),

    INDEX(ID),

    KEY B (`B`),

    KEY A (`A`),

    KEY ABC (`A`,`B`,`C`),

    KEY BCD (`B`,`C`,`D`),

    KEY ID_C_D (`ID`,`C`,`D`)

) ENGINE=InnoDB;

#可以看一下表的結構

mysql> DESC test_index;

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

| Field | Type    | Null | Key | Default | Extra |

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

| ID    | int(11) | NO   | PRI | NULL    |       |

| A     | int(11) | NO   | MUL | NULL    |       |

| B     | int(11) | NO   | MUL | NULL    |       |

| C     | int(11) | NO   |     | NULL    |       |

| D     | int(11) | NO   |     | NULL    |       |

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

5 rows in set (0.00 sec)

#開始分析剛創建的那張test_index的索引是否重覆

[root@rosalie-mysql02 mysql]# pt-duplicate-key-checker -u root -p1234 --database=tttt

# A software update is available:

#   * The current version for Percona::Toolkit is 3.0.5

# ########################################################################

# tttt.test_index                                                         

# ########################################################################

# ID_2 is a duplicate of PRIMARY

# Key definitions:

#   KEY `ID_2` (`ID`),

#   PRIMARY KEY (`ID`),

# Column types:

#      `id` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `ID_2`;

##紫色的是工具分析出,可以直接在DB執行

#索引ID_2是多餘的,因為ID原本就是主鍵了,又多設一個INDEX變ID_2

# Uniqueness of ID ignored because PRIMARY is a duplicate constraint

# ID is a duplicate of PRIMARY

# Key definitions:

#   UNIQUE KEY `ID` (`ID`),

#   PRIMARY KEY (`ID`),

# Column types:

#      `id` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `ID`;

#索引ID是多餘的,因為ID原本就是主鍵了,又多設一個唯一索引ID這欄索引就再重複

# B is a left-prefix of BCD

# Key definitions:

#   KEY `B` (`B`),

#   KEY `BCD` (`B`,`C`,`D`),

# Column types:

#      `b` int(11) not null

#      `c` int(11) not null

#      `d` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `B`;

#索引B是多餘的,因為已經有一個組合索引，同單左前綴索引效用

# A is a left-prefix of ABC

# Key definitions:

#   KEY `A` (`A`),

#   KEY `ABC` (`A`,`B`,`C`),

# Column types:

#      `a` int(11) not null

#      `b` int(11) not null

#      `c` int(11) not null

# To remove this duplicate index, execute:

ALTER TABLE `tttt`.`test_index` DROP INDEX `A`;

#索引A是多餘的,因為已經有一個組合索引，同單左前綴索引效用

# ########################################################################

# Summary of indexes                                                      

# ########################################################################

# Size Duplicate Indexes   16  #Size重複索引16

# Total Duplicate Indexes  4   #重複索引總數

# Total Indexes            9

#這是在未刪除多餘索引前查看表創建的內容

mysql> show create table tttt.test_index;

| test_index | CREATE TABLE `test_index` (

  `ID` int(11) NOT NULL,

  `A` int(11) NOT NULL,

  `B` int(11) NOT NULL,

  `C` int(11) NOT NULL,

  `D` int(11) NOT NULL,

  PRIMARY KEY (`ID`),

  UNIQUE KEY `ID` (`ID`),

  KEY `ID_2` (`ID`),

  KEY `B` (`B`),

  KEY `A` (`A`),

  KEY `ABC` (`A`,`B`,`C`),

  KEY `BCD` (`B`,`C`,`D`),

  KEY `ID_C_D` (`ID`,`C`,`D`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 

1 row in set (0.00 sec)

#依上面工具分析的結果，將多餘的INDEX刪除後，再重新查看

mysql> show create table tttt.test_index;

| test_index | CREATE TABLE `test_index` (

  `ID` int(11) NOT NULL,

  `A` int(11) NOT NULL,

  `B` int(11) NOT NULL,

  `C` int(11) NOT NULL,

  `D` int(11) NOT NULL,

  PRIMARY KEY (`ID`),

  KEY `ABC` (`A`,`B`,`C`),

  KEY `BCD` (`B`,`C`,`D`),

  KEY `ID_C_D` (`ID`,`C`,`D`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

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

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限定了

【MYSQL】EXPLAIN - 欄位說明解釋

EXPLAIN - 欄位說明解釋

mysql> explain select * from PROCESSLIST;
+----+------------+------+------------+------+--------------+----+---------+----+-----+--------+-------+
| id | select_type| table| partitions | type | possible_keys| key| key_len | ref| rows|filtered| Extra |
+----+------------+------+------------+------+--------------+----+---------+----+-----+--------+-------+
1 row in set, 1 warning (0.00 sec)

欄位及欄位值說明：
【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會將歸類這些子查詢，於臨時表中

【MYSQL】不走索引的SQL

不走索引的SQL

• like語句，'%w'不會使用索引，'w%'會使用索引

select * from table_name where name LIKE "%wang%";  -----------------不會使用索引

select * from table_name where name LIKE "wang%";  ------------------會使用索引

• 列類型為字符串類型字符串類型指(CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET)，查詢時沒有用單引號引起來

select * from table_name where ship_id=20;  -----------------不會使用索引

select * from table_name where ship_id="20";  ------------------會使用索

• 在where查詢語句中使用計算試+-*/或是!= , <> 比較

select * from table_name where id +1 = 2103;-----------------不會使用索引

• 在where查詢語句中對字段進行NULL值判斷（is NULL 或is not NULL）

select * from table_name where group_name=NULL;  -----------------不會使用索引

• 在where查詢中使用了or關鍵字, myisam表能用到索引， innodb不行;(用UNION替換or,可以使用索引) 

例：where中的indexed欄有建索引、no_index欄沒建索引。

select * from table_name where Indexed="20" or No_index="11";--不會使用索引(一有一沒有建)

select * from table_name where Indexed="20" or Indexed2="11";----會使用索引(兩個都有建)

• where中組合索引未按順序查詢的index(A欄,B欄,C欄)左前綴法則
• 但查A,C欄雖有使用到索引，效果沒有ABC來得好，因為只使用到部份索引

select * from table_name where B欄="小熊" and C欄="小Q";--不會使用索引(一定要A欄先)

select * from table_name where A欄="小胖" and C欄="小Q";----會使用索引(A欄有在第一個就可)

• 如果mysql估計使用全表掃描要比使用索引快,則不使用索引（數據量小時or 索引數據量大於20%的

• delete中帶in不會走索引
  (查詢table_name2中的id=20符合的，刪除在table_name的資料)

delete from table_name where id in(select id from table_name2 where id=20);--不會使用索引

delete table_name from table_name inner join table_name2 on 
table_name.id=table_name2.id where table_name2=20;--------會使用索引

【MYSQL】索引種類與說明

索引可以提高查詢的速度，對於常常查詢的欄位使用索引，有無設定的速度差可能會好幾倍。所以
範例1~例如：若資料上萬筆

SELECT * FROM table_name where datetime>= '2019-03-22 00:00:00'



AND datetime <= '2019-03-22 23:59:59';

如果在datetime欄位加上索引，和一個沒加索引若資料量龐大，兩個查詢的速度會差很多!

加上索引的，MYSQL會優先針對datetime去查詢，沒加索引的表，則是會全文搜尋。

索引(index)的類型

1.主鍵索引(primary key)。

語法：ALTER TABLE table_name ADD primary key (column) ;

• 不能為NULL
• 不能重覆
• 一個表只能有一個主鍵索引。

例如：用於，自動編號、身份證字號....等

2.唯一索引(unique index)。

語法：ALTER TABLE table_name ADD unique (column) ;
或CREATE UNIQUE INDEX index_name ON table_name(column);

• 可以為NULL 
• 不能重覆

例如：像是手機有號碼，E-MAIL....等，有些人不一定有手機或E-MAIL

3.一般索引、普通索引。

語法：（填加或直接創建）ALTER TABLE table_name ADD INDEX index_name (column) ;
或CREATE INDEX index_name ON table_name (column);

• 最基本的索引，無任何限制

4.組合索引。

語法：ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);

• 組合索引中最左邊的欄位column1一定要出現在條件中，才會使用索引

例如：以下兩個查詢會用到索引

SELECT * FROM table_name WHERE column1=val1;


SELECT * FROM table_name WHERE column1=val1 AND column2=val2;

以下兩個查詢不會用到索引，請注意條件中 column2並非索引的第一個欄位

SELECT * FROM tbl_name WHERE column2=val2;


SELECT * FROM tbl_name WHERE column2=val2 AND column3=val3;

5.全文索引。

語法：ALTER TABLE table_name ADD FULLTEXT (column);


或CREATE FULLTEXT INDEX index_name ON table_name(column);

查看索引，可以用三種方法：

SHOW {INDEX | INDEXES | KEYS} from table_name;

或是查看資料庫內的索引：

SHOW {INDEX | INDEXES | KEYS} from database_name;

或是針對表查看進行管理：

show create table 表名\G

刪除索引：

DROP INDEX index_name ON table_name;

特殊案例
測試EXPLAIN - 效能分析語句
在SELECT 前加入EXPLAIN 可以查出語句的執行效能
以下為例當我在ID上加上索引，實際查詢where id=20067，查詢時間很快

mysql> select id from users where id = 20067;
+-------+
| id    |
+-------+
| 20067 |
+-------+
1 row in set (0.00 sec)

mysql> explain select id,loginname from users where id = 20067;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | id            | id   | 4       | const |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

注意ID+1，其實都是查同一筆資料，但索引未產生效用MYSQL無法解析程式

mysql> select id from users where id +1 = 20068;
+-------+
| id    |
+-------+
| 20067 |
+-------+
1 row in set (0.88 sec)

mysql> explain select id,loginname from users where id +1 = 20068;
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | users | index | NULL          | id   | 4       | NULL | 576128 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)