mysql · php 相關整理

## 1、設置pager **設置pager**：將mysql的執行結果快速原樣保存到服務器文件 ~~~ pager cat > /data/webroot/XXX/testMysql.csv ; #設置pager nopager ; #取消pager ~~~ ## 2、mysql當前進程數 ~~~ 可用作緊急處理： (1)當mysql query數滿了， (2)或者慢查詢導致大片的query 處于等待緊急處理可以殺死query，先讓系統不至于崩潰。 **注意**：只是緊急處理，需要排查是具體哪里導致的問題，從根源解決 ~~~ ~~~ show processlist \G;#查詢當前的query kill '第一列的數字' ; #殺死該查詢 ~~~ ## 3、case ~~~ select id, case tableName.fieldName when '0' then '情況1' when '2' then '情況2' when '3' then '情況3' end as fieldName from `tableName` ~~~ ## 4、引擎 MyISAM： ~~~ Select更優不支持事務只支持表鎖不支持外鍵支持FULLTEXT全文索引 ~~~ InnoDB： ~~~ 支持事務 Inset、update、delete更優支持表鎖、行鎖支持外鍵不支持全文索引 ~~~ ~~~ 覺得使用InnoDB可以應對更為復雜的情況，特別是對并發的處理要比MyISAM高效。同時結合memcache也可以緩存SELECT來減少SELECT查詢，從而提高整體性能。 ~~~ ## 5、千萬級select 五千萬及以上，取全量、或者取某頁，直接用limit 會很慢 ~~~ 1、主鍵是連續的，直接計算出來，直接limit 2、主鍵是非連續的，可以考慮先找出ID ID是主鍵索引，查詢很快先找ID，然后用in `select id from table order by id limit 50000,10;` `SELECT * FROM table WHERE id IN(XXX, XXX, XXX...);` 3、加新表維護頁數 4、體驗降級過大的頁數提示沒數據 5、復合索引 select 索引 ~~~ ~~~ 主鍵，是一種特殊的唯一索引，在一張表中只能定義一個主鍵索引，主鍵用于唯一標識一條記錄，使用關鍵字 PRIMARY KEY 來創建。索引可以覆蓋多個數據列，就是聯合索引。聯合主鍵，就是多個主鍵聯合形成一個主鍵組合，體現在聯合。索引可以極大的提高數據的查詢速度，但是會降低插入、刪除、更新表的速度，因為在執行寫操作時，還要操作索引文件 ~~~ ## 6、鎖 ~~~ ## **共享鎖與排他鎖** * 共享鎖（讀鎖）：其他事務可以讀，但不能寫。 * 排他鎖（寫鎖）：其他事務不能讀取，也不能寫。 ## **粒度鎖** * MyISAM 和 MEMORY 存儲引擎采用的是表級鎖（table-level locking） * InnoDB 存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認情況下是采用行級鎖。默認情況下，表鎖和行鎖都是自動獲得的，不需要額外的命令。但是在有的情況下，用戶需要明確地進行鎖表或者進行事務的控制，以便確保整個事務的完整性，這樣就需要使用事務控制和鎖定語句來完成。 ~~~ ~~~ * 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低。更適合于以查詢為主，并發用戶少， * 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。最大程度的支持并發，同時也帶來了最大的鎖開銷。 ~~~ ~~~ ## **MyISAM加表鎖方法：** 執行查詢語句（SELECT）前，會自動給涉及的表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT 等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預， ## **InnoDB加鎖方法：** * 對于 UPDATE、 DELETE 和 INSERT 語句， InnoDB 會自動給涉及數據集加排他鎖（X)； * 對于普通 SELECT 語句，InnoDB 不會加任何鎖；事務可以通過語句顯式加共享鎖或排他鎖： * 共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 * 排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。 ~~~ ## 7 、sql注入 ~~~ 防護 1.永遠不要信任用戶的輸入。對用戶的輸入進行校驗，可以通過正則表達式，或限制長度；對單引號和雙"-"進行轉換等。 2.永遠不要使用動態拼裝 sql，可以使用參數化的 sql 3.永遠不要使用管理員權限的數據庫連接，為每個應用使用單獨的權限有限的數據庫連接。 4.不要把機密信息直接存放，加密或者 hash 掉密碼和敏感的信息。 5.應用的異常信息應該給出盡可能少的提示示，最好使用自定義的錯誤信息對原始錯誤信息進行包裝 6.sql 注入檢測 ~~~ ~~~ **PHP怎樣防止SQL注入？** 使用預處理語句和參數化查詢。預處理語句和參數分別發送到數據庫服務器進行解析，參數將會被當作普通字符處理。 ~~~ ### 長度 ~~~ TINYINT——這個類型最多可容納三位數。 ·SMALLINT——最多可容納五位數。 ·MEDIUMINT——最多可容納八位數。 ·INT——可以容納十位數。 ·BIGINT——最多可容納二十位數。 ~~~ ### 復合索引 ~~~ 最左原則：只要包含最左邊的一列即可， userId, mobile, billMonth三個字段添加上聯合索引！ 1.查詢條件為 userid 聯合索引有效 2.查詢條件為 mobile 聯合索引無效 3.查詢條件為 billMonth 聯合索引無效 4.查詢條件為 userid and mobile 聯合索引有效 5.查詢條件為 mobile and userid 聯合索引依舊有效 6.查詢條件為 userid or mobile 把 and 換成 or，發現聯合所索引無效！ 7.查詢條件為 userid and billMonth 第一和第三，測試聯合索引依舊有效！ 8.查詢條件為 mobile and billMonth 第二和第三，發現聯合索引無效！ 9.查詢條件為 userid and mobile and billMonth 所有條件一起查詢，聯合索引有效！原文：https://blog.csdn.net/Abysscarry/article/details/80792876 ~~~