MySQL 筆數查詢、分頁查詢

N 筆資料查詢

    SELECT * FROM 成績單 LIMIT 3    // 取成績單前三筆資料
    SELECT * FROM 成績單 ORDER BY 分數 DESC LIMIT 3 // 取分數前三名的資料
    SELECT * FROM 成績單 ORDER BY 分數 DESC LIMIT 3, 7 // 取第四名到第十名的資料

分頁查詢

     隨著查詢筆數越來越多，網站可能就會需要用分頁來表示查詢結果。

     這裡會出現一個問題 Offset ( LIMIT 的第1個參數 ) 越大，查詢速度越慢。

    SELECT * FROM 成績單 LIMIT  100000, 1         // 大 Offset

     Google 的結果是 InnoDB 會有的情況，InnoDB 會掃 100001 筆資料，然會再把 100000 筆資料給捨棄，導致查詢速度越來越慢。所以解決方法為先只掃索引。

    SELECT * FROM 成績單 WHERE id >= (SELECT id FROM 成績單 LIMIT 100000, 1) LIMIT 1

參考資料 :
https://codertw.com/%E8%B3%87%E6%96%99%E5%BA%AB/21569/
https://blog.csdn.net/fdipzone/article/details/72793837

Read More

MySQL 備份

備份 MySQL

備份單一資料庫

mysqldump -u root -p database_name > backup.sql

備份多個資料庫

mysqldump -u root -p --databases db1 db2 > backup.sql

備份全部資料庫

mysqldump -u root -p --all-databases > backup.sql

備份單一資料表

mysqldump -u root -p database_name table_name > backup.sql

備份多個資料表

mysqldump -u root -p database_name table1 table2 > backup.sql

備份資料庫除了特定資料表

mysqldump -u root -p database --ignore-table=database.table1 > database.sql

導入 MySQL

導入單一資料庫

mysql -u root -p database_name < backup.sql

導入多個資料庫

mysql -u root -p < backup.sql

參考資料 :
https://code.yidas.com/mysqldump/

Read More

MySQL with "localhost"

前言

     移植(複製)系統後，新系統很慢

     事情是這樣的，公司成立分部，鑑於前輩們 (都走了) 建的系統很好用

     公司讓我去建一個到分部，我心裡 OS : 複製貼上我最會 !!

     一切都很順利，安裝  PHP 然後 Hello World ~~

     安裝 Mysql 匯出匯入，會 Google 的都會，直到我開啟系統...好慢...

檢查步驟 1 : 打開"開發者人員選項"

     不急不慌先怪硬體...，開玩笑的...請按 F12

     開了 F12 就發現 Hello World 很快，但系統很慢...

     再仔細觀察發現連結資料庫特很慢 ( 圖檔很快 )

檢查步驟 2 : 檢查 Mysql 

     我第一個想法，我要去優化前輩們的 SQL，結果大概是 2.2 秒 變 2 秒...

檢查步驟 3 : Google it !!

     Google 結果發現問題出在 localhost 身上

     當你連結資料庫時，hostname = "localhost"，此時 Server 會找 localhost 所對應的 IP 位置

     只要你沒特別將 localhost 導向 127.0.0.1，那就會找 ::1，IPv6 裡的主機本身

     鑒於 Mysql 並沒設定開啟 IPv6 所以你就只能等，等 Server 用 ::1發現不行才會換 127.0.0.1

     造成連結資料庫特很慢的結果。

解決辦法

     解決辦法有 3 種

     1. 把系統裡的 hostname 通通從 localhost 改成 127.0.0.1

          如果你前輩有先見之明，它會是全域參數，改一下就好

          若不是...見一個改一個...

     2. 設定主機的 localhost 讓其導向 127.0.0.1 

          這我不會，附上參考資料

          http://iamshowp.blogspot.com/2010/05/127001localhostipv6.html

     3. Enable Mysql IPv6 

          這最簡單，最推薦

          修改 config

[mysqld]
bind_address = 127.0.0.1,::1

重啟 MySQL (windows指令)

net stop mysql
net start mysql

參考資料 : https://dev.mysql.com/doc/refman/5.5/en/ipv6-local-connections.html

Read More

MySQL 利用執行順序提高效率

MySQL 利用執行順序提高效率

因為公司的內部網站平常有點慢，後來又架了個在美國的 Server
原本的小問題就被放大，雖然優化網站的方式主要不是靠 SQL的改進

最普通的優化就是不要用 SELECT * ，理由顯而易見，資料越多搜尋越慢
依照這點，所以要讓搜尋的資料越少，就要利用明確的條件去限制搜尋
也就是利用 SQL 的執行順序

SQL 的執行順序如下 :

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE 或 WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. Top / limit

所以可以改進的就是 ON 跟 WHERE
而且 ON 在 JOIN 前，所以我們要利用 ON 把 Table 的資料降到最小

所以好的 SQL 我認為應該是，利用 ON 來把資料降到最小，甚至到不用 WHERE 的程度

參考資料 :
https://docs.microsoft.com/
http://pclevinblog.pixnet.net/blog/

Read More

網頁開發(3) 重複查詢同一資料表

對同一個資料表做重複查詢

有時候可能會對同一個資料表做重複查詢很多次
也就是需要 JOIN 兩次以上

例如( 以下範例不小心用到中文，沒試過到底可不可行 )

Table1

Table2

這時可能就需要 JOIN Table2 重複 2 次來達到完整資料

利用 AS 來達到區分 Table2 達到 JOIN 2 次的效果

    SELECT send.名稱 as sendman, recv.名稱 as recvman
    FROM Table1 as log
    LEFT JOIN Table2 AS send ON log.寄件者 = send.id
    LEFT JOIN Table2 AS recv ON log.收件者 = recv.id

Read More