大家好，今天來為大家分享mysql單表1000萬條 性能的一些知識點，和mysql千萬數據查詢優化的問題解析，大家要是都明白，那么可以忽略，如果不太清楚的話可以看看本篇文章，相信很大概率可以解決您的問題，接下來我們就一起來看看吧！
如何分析一條SQL的性能
在做性能測試中經常會遇到一些sql的問題，其實做性能測試這幾年遇到問題最多還是數據庫這塊，要么就是IO高要么就是cpu高，所以對數據的優化在性能測試過程中占據著很重要的地方，下面我就介紹一些msyql性能調優過程中經常用到的三件利器：
1、慢查詢（分析出現出問題的sql）
2、Explain（顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句）
3、Profile（查詢到SQL會執行多少時間,并看出CPU/Memory使用量,執行過程中Systemlock,Tablelock花多少時間等等.）
首先我們先講一講mysql的慢查詢
1，配置開啟
Linux:
在mysql配置文件my.cnf中增加
log-slow-queries=/var/lib/mysql/slowquery.log(指定日志文件存放位置，可以為空，系統會給一個缺省的文件host_name-
slow.log)
long_query_time=2(記錄超過的時間，默認為10s)
log-queries-not-using-indexes(log下來沒有使用索引的query，可以根據情況決定是否開啟)
log-long-format(如果設置了，所有沒有使用索引的查詢也將被記錄)Windows:
在my.ini的[mysqld]添加如下語句：
log-slow-queries=E:\web\mysql\log\mysqlslowquery.loglong_query_time=2(其他參數如上)
2，查看方式
Linux:
使用mysql自帶命令mysqldumpslow查看
常用命令
-sORDERwhattosortby(t,at,l,al,r,aretc),'at’isdefault
-tNUMjustshowthetopnqueries
-gPATTERNgrep:onlyconsiderstmtsthatincludethisstringeg:
s，是order的順序，說明寫的不夠詳細，俺用下來，包括看了代碼，主要有c,t,l,r和ac,at,al,ar，分別是按照query次數，時間，lock的時間和返回的記錄數來排序，前面加了a的時倒序-t，是topn的意思，即為返回前面多少條的數據-g，后邊可以寫一個正則匹配模式，大小寫不敏感的
mysqldumpslow-sc-t20host-slow.log
mysqldumpslow-sr-t20host-slow.log
上述命令可以看出訪問次數最多的20個sql語句和返回記錄集最多的20個sql。
mysqldumpslow-t10-st-g“leftjoin”host-slow.log這個是按照時間返回前10條里面含有左連接的sql語句。
接下來就是explain
使用方法：
執行EXPLAINSELECT*FROMres_userORDERBYmodifiedtimeLIMIT0,1000得到如下結果：
顯示結果分析：
table|type|possible_keys|key|key_len|ref|rows|Extra
EXPLAIN列的解釋：
table
顯示這一行的數據是關于哪張表的
type
這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL
possible_keys
顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句
key
實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USEINDEX（indexname）來強制使用一個索引或者用IGNOREINDEX（indexname）來強制MYSQL忽略索引
key_len
使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref
顯示索引的哪一列被使用了，如果可能的話，是一個常數
rows
MYSQL認為必須檢查的用來返回請求數據的行數
Extra
關于MYSQL如何解析查詢的額外信息。將在表4.3中討論，但這里可以看到的壞的例子是Usingtemporary和Usingfilesort，意思MYSQL根本不能使用索引，結果是檢索會很慢
extra列返回的描述的意義
Distinct
一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了
Notexists
MYSQL優化了LEFTJOIN，一旦它找到了匹配LEFTJOIN標準的行，就不再搜索了
Rangecheckedforeach
Record（indexmap:#）
沒有找到理想的索引，因此對于從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，并用它來從表中返回行。這是使用索引的最慢的連接之一
Usingfilesort
看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行
Usingindex
列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候
Usingtemporary
看到這個的時候，查詢需要優化了。這里，MYSQL需要創建一個臨時
表來存儲結果，這通常發生在對不同的列集進行ORDERBY上，而不是GROUPBY上
Whereused
使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會發生，或者是查詢有問題
不同連接類型的解釋（按照效率高低的順序排序）
system
表只有一行：system表。這是const連接類型的特殊情況
const
表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MYSQL先讀這個值然后把它當做常數來對待
eq_ref
在連接中，MYSQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用
ref
這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時發生。對于之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴于根據索引匹配的記錄多少—越少越好
range
這個連接類型使用索引返回一個范圍中的行，比如使用>或<；查找東西時發生的情況
index
這個連接類型對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小于表數據）
ALL
這個連接類型對于前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該盡量避免
再者就是profile
我們可以先使用
mysql>SELECT@@profiling;
+-------------+
|@@profiling|
+-------------+
|0|
+-------------+
1rowinset(0.00sec)來查看是否已經啟用profile，如果profilng
值為0，可以通過
mysql>SETprofiling=1;
QueryOK,0rowsaffected(0.00sec)
mysql>SELECT@@profiling;
+-------------+
|@@profiling|
+-------------+
|1|
+-------------+
1rowinset(0.00sec)
來啟用。啟用profiling之后，我們執行一條查詢語句，比如：
SELECT*FROMres_userORDERBYmodifiedtimeLIMIT
0,1000
mysql>showprofiles;
+----------+------------+-----------------------------------------
--------------------+
|Query_ID|Duration|Query|
+----------+------------+-----------------------------------------
--------------------+
|1|0.00012200|SELECT@@profiling|
|2|1.54582000|SELECTres_idFROMres_user
ORDERBYmodifiedtimeLIMIT0,3|
+----------+------------+-----------------------------------------
--------------------+
2rowsinset(0.00sec)注意：Query_ID表示剛執行的查詢語句
mysql>showprofileforquery2;
+--------------------------------+----------+
|Status|Duration|
+--------------------------------+----------+
|starting|0.000013|
|checkingquerycacheforquery|0.000035|
|Openingtables|0.000009|
|Systemlock|0.000002|
|Tablelock|0.000015|
|init|0.000011|
|optimizing|0.000003|
|statistics|0.000006|
|preparing|0.000006|
|executing|0.000001|
|Sortingresult|1.545565|
|Sendingdata|0.000038|
|end|0.000003|
|queryend|0.000003|
|freeingitems|0.000069|
|storingresultinquerycache|0.000004|
|loggingslowquery|0.000001|
|loggingslowquery|0.000033|
|cleaningup|0.000003|
+--------------------------------+----------+
19rowsinset(0.00sec)
結論：可以看出此條查詢語句的執行過程及執行時間，總的時間約為1.545s。這時候我們再執行一次。
mysql>SELECTres_idFROMres_userORDERBYmodifiedtimeLIMIT0,3;
+---------+
|res_id|
+---------+
|1000305|
|1000322|
|1000323|
+---------+
3rowsinset(0.00sec)
mysql>showprofiles;
+----------+------------+-------------------------------------------------------------+
|Query_ID|Duration|Query|
+----------+------------+-------------------------------------------------------------+
|1|0.00012200|SELECT@@profiling|
|2|1.54582000|SELECTres_idFROMres_userORDERBYmodifiedtimeLIMIT0,3|
|3|0.00006500|SELECTres_idFROMres_userORDERBYmodifiedtimeLIMIT0,3|
+----------+------------+-------------------------------------------------------------+
3rowsinset(0.00sec)
mysql>showprofileforquery3;
+--------------------------------+----------+
|Status|Duration|
+--------------------------------+----------+
|starting|0.000013|
|checkingquerycacheforquery|0.000005|
|checkingprivilegesoncached|0.000003|
|sendingcachedresulttoclien|0.000040|
|loggingslowquery|0.000002|
|cleaningup|0.000002|
+--------------------------------+----------+
6rowsinset(0.00sec)(注意紅色標記的地方)
結論：可以看出此次第二次查詢因為前一次的查詢生成了cache，所以這次無需從數據庫文件中再次讀取數據而是直接從緩存中讀取，結果查詢時間比第一次快多了（第一次查詢用了1.5秒而本次用了不到5毫秒）。
mysql數據類型的詳解
MySQL數據庫的表是一個二維表，由一個或多個數據列構成。每個數據列都有它的特定類型，該類型決定了MySQL如何看待該列數據，我們可以把整型數值存放到字符類型的列中，MySQL則會把它看成字符串來處理。MySQL中的列類型有三種：數值類、字符串類和日期/時間類。從大類來看列類型和數值類型一樣，都是只有三種。但每種列類型都還可細分。下面對各種列類型進行詳細介紹。
數值類的數據列類型數值型的列類型包括整型和浮點型兩大類。
TINYINT：1字節非常小的正整數，帶符號：-128~127，不帶符號：0~255SMALLINT：2字節小整數，帶符號：-32768~32767，不帶符號：0~65535MEDIUMINT：3字節中等大小的整數，帶符號：-8388608~8388607，不帶符號：0~16777215INT：4字節標準整數，帶符號：-2147483648~2147483647，不帶符號：0~4294967295BIGINT：8字節大整數，帶符號：-9223372036854775808~9233372036854775807，不帶符號：0~18446744073709551615FLOAT：4字節單精度浮點數，最小非零值：+-1.175494351E-38，最大非零值：+-3.402823466E+38DOUBLE：8字節雙精度浮點數，最小非零值：+-2.2250738585072014E-308，最大非零值：+-1.7976931348623157E+308DECIMAL：M+2字節以字符串形式表示的浮點數，它的取值范圍可變，由M和D的值決定。MYSQL支持大量的列類型，它們可以被分為3類：數字類型、日期和時間類型以及字符串(字符)類型。這個章節首先給出可用類型的概述，并且總結各類型所需的存儲需求，然后提供各類型中的類型范疇更詳細的描述。概述有意地簡化了。更詳細的說明應該參考特寫列類型的附加信息，例如你能為其指定值的允許格式。MySQL支持的列類型在下面列出。下列代碼字母用于描述中：M指出最大的顯示尺寸。最大的顯示尺寸長度為255。D適用于浮點類型。指出跟隨在十進制小數點后的數字數量。最大可能值為30，但不應大于M-2。方括號(“[”and“]”)指定可選的類型修飾部份。注意，如果為一個列指定了ZEROFILL，MySQL將自動為這個列添加UNSIGNED屬性。警告：你應該知道當在兩個整數類型值中使用減法時，如有一個為UNSIGNED類型，那么結果也是無符號的。查看章節6.3.5Cast函數。
TINYINT[(M)][UNSIGNED][ZEROFILL]-128到127。無符號的范圍是0到255。BITBOOL它們是TINYINT(1)的同義詞。
SMALLINT[(M)][UNSIGNED][ZEROFILL]一個小整數。有符號的范圍是-32768到32767。無符號的范圍是0到65535。
MEDIUMINT[(M)][UNSIGNED][ZEROFILL]一個中等大小的整數。有符號的范圍是-8388608到8388607。無符號的范圍是0到16777215。
INT[(M)][UNSIGNED][ZEROFILL]一個正常大小的整數。有符號的范圍是-2147483648到2147483647。無符號的范圍是0到4294967295。
INTEGER[(M)][UNSIGNED][ZEROFILL]INT的同義詞。
BIGINT[(M)][UNSIGNED][ZEROFILL]一個大的整數。有符號的范圍是-9223372036854775808到9223372036854775807。無符號的范圍是0到18446744073709551615。你應該知道的有關BIGINT列的一些事情：BIGINT或DOUBLE值來完成的，因此你不應該使用大于9223372036854775807(63bits)的無符號大整數，除了位函數之外！如果你這樣做了，結果中的某些大數字可能會出錯，因為將BIGINT轉換成DOUBLE時產生了舍入錯誤。MySQL4.0在下列情況下可以處理BIGINT：在一個BIGINT列中使用整數存儲一個大的無符號值。在MIN(big_int_column)和MAX(big_int_column)中。當兩個操作數都是整數時使用操作符(+、-、*、等)。通常你可以在一個BIGINT列中以字符串方式存儲的一個精確的整數。在這種情況下，MySQL將執行一個字符串到數字的轉換，包括無intermediate的雙精度表示法。當兩個參數均是整數值時，“-”、“+”和“*”將使用BIGINT運算！這就意味著，如果兩個大整數的乘積(或函數的結果返回整數)的結果大于9223372036854775807時，你可能會得到意想不到的結果。FLOAT(precision)[UNSIGNED][ZEROFILL]一個浮點型數字。precision可以是<=24作為一個單精度的浮點數字和介于25和53之間作為一個雙精度的浮點數字。這些類型與下面描述的FLOAT和DOUBLE類型相似。FLOAT(X)有與相應的FLOAT和DOUBLE類型同樣的范圍，但是顯示尺寸和十進制小數位數是未定義的。在MySQL3.23中，它是一個真實的浮點值。而在MySQL早期的版本中，FLOAT(precision)通常有2小數位。注意，由于在MySQL中所有的計算都是以雙精度執行的，所以使用FLOAT可能帶來一些意想不到的問題。查看章節A.5.6解決沒有匹配行的問題。FLOAT[(M,D)][UNSIGNED][ZEROFILL]一個小的(單精度)浮點數字。允許的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38。如果UNSIGNED被指定，負值是不允許的。M是顯示寬度，D是小數位數。FLOAT沒有參數或有X<=24的FLOAT(X)代表一個單精度的浮點數字。DOUBLE[(M,D)][UNSIGNED][ZEROFILL]一個正常大小的(雙精度)浮上數字。允許的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到1.7976931348623157E+308。如果UNSIGNED被指定，負值是不允許的。M是顯示寬度，D是小數位數。DOUBLE沒胡參數或有25<=X<=53的FLOAT(X)代表一個雙精度的浮點數字。DOUBLEPRECISION[(M,D)][UNSIGNED][ZEROFILL]REAL[(M,D)][UNSIGNED][ZEROFILL]它們是DOUBLE同義詞。DECIMAL[(M[,D])][UNSIGNED][ZEROFILL]一個未壓縮(unpacked)的浮點數。運作如同一個CHAR列：“unpacked”意味著數字是以一個字符串存儲的，值的每一位將使用一個字符。小數點并且對于負數，“-”符號不在M中計算(但是它們的空間是被保留的)。如果D是0，值將沒有小數點或小數部份。DECIMAL值的最大范圍與DOUBLE一致，但是對于一個給定的DECIMAL列，實際的范圍可以被所選擇的M和D限制。如果UNSIGNED被指定，負值是不允許的。如果D被忽略，缺省為0。如果M被忽略，缺省為10。在MySQL3.23以前，M參數必須包含符號與小數點所需的空間。DEC[(M[,D])][UNSIGNED][ZEROFILL]NUMERIC[(M[,D])][UNSIGNED][ZEROFILL]DECIMAL的同義詞。DATE一個日期。支持的范圍是'1000-01-01'到'9999-12-31'。MySQL以'YYYY-MM-DD'格式顯示DATE值，但是允許你以字符串或數字給一個DATE列賦值。查看章節6.2.2.2DATETIME、DATE和TIMESTAMP類型。DATETIME一個日期和時間的組合。支持的范圍是'1000-01-0100:00:00'到'9999-12-3123:59:59'。MySQL以'YYYY-MM-DDHH:MM:SS'格式顯示DATETIME值，但是允許你以字符串或數字給一個DATETIME列賦值。查看章節6.2.2.2DATETIME、DATE和TIMESTAMP類型。TIMESTAMP[(M)]一個時間戳。范圍是'1970-01-0100:00:00'到2037年間的任意時刻。MySQL4.0和更早版本中，TIMESTAMP值是以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD格式顯示的，它取決于M是否是14(或省略)、12、8或6，但是允許你以字符串或數字給一個TIMESTAMP列賦值。從MySQL4.1開始，TIMESTAMP以'YYYY-MM-DDHH:MM:DD'格式作為字符返回。如果你你希望以數字形式返回則必須在該時間戳字段后加上+0。不同的時間戳長度是不支持的。從MySQL4.0.12開始，選項--new可以被用來使服務器與4.1一樣運作。TIMESTAMP列有益于記錄一個INSERT或UPDATE操作的日期和時間，因為如果你自己沒有給它賦值，它將被自動地設置為最近一次操作的日期和時間。也可以通過給它賦一個NULL而使它設置為當前的日期和時間。查看章節6.2.2Date和Time類型。參數M只影響一個TIMESTAMP列的顯示格式；它的值總是占用4個字節存儲。注意，當TIMESTAMP(M)列的M是8或14時，它返回的是數字而其它的TIMESTAMP(M)列返回的是字符串。這僅僅是為了可以可靠地轉儲并恢復到其它格式的表中。查看章節6.2.2.2DATETIME、DATE和TIMESTAMP類型。TIME一個時間。范圍是'-838:59:59'到'838:59:59'。MySQL以'HH:MM:SS'格式顯示TIME值，但是允許你使用字符串或數字來給TIME列賦值。查看章節6.2.2.3TIME類型。YEAR[(2|4)]一個2或4位數字格式的年(缺省為4位)。允許的值是1901到2155、0000(4位年格式)以及使用2位格式的1970-2069(70-69)。MySQL以YYYY格式顯示YEAR值，但是允許你使用字符串或數字來給YEAR列賦值。(YEAR類型在MySQL3.22之前不支持。)查看章節6.2.2.4YEAR類型。[NATIONAL]CHAR(M)[BINARY]一個定長的字符串，當存儲時，總是以空格填滿右邊到指定的長度。M的范圍是0到255(在MySQL3.23版本之前為1到255)。當該值被檢索時，尾部空格將被刪除。CHAR值根據缺省的字符集進行忽略大小寫的排索與比較，除非指定了關鍵詞BINARY。NATIONALCHAR(或短形式NCHAR)是以ANSISQL方式定義一個CHAR列，它將使用缺省的字符集。這在MySQL中是默認的。CHAR是CHARACTER的縮寫。MySQL允許以CHAR(0)類型建立一個列。一些老程序運行時必需一個列，卻又并不使用這個列的值，你就不得不為了適應它而建立該列，在這情況下，CHAR(0)將是很有益的。當需要一個列僅保存兩個值時：一個為CHAR(0)(該列沒有定義為NOTNULL)，這將僅占用一個比特位來存儲2個值：NULL或""。查看章節6.2.3.1CHAR和VARCHAR類型。CHAR這是CHAR(1)的同義詞。[NATIONAL]VARCHAR(M)[BINARY]一個變長的字符串。注意：尾部的空格在存儲時將會被刪除(這與ANSISQL約規不同)。M的范圍是0到255(在MySQL4.0.2之前的版本中是1到255)。VARCHAR值以大小寫忽略方式進行排索與比較，除非關鍵詞BINARY被指定。查看章節6.5.3.1隱式的列定義變化。VARCHAR是CHARACTERVARYING的縮寫。查看章節6.2.3.1CHAR和VARCHAR類型。TINYBLOBTINYTEXT一個BLOB或TEXT列，最大長度為255(2^8-1)個字符。查看章節6.5.3.1隱式的列定義變化。查看章節6.2.3.2BLOB和TEXT類型。BLOBTEXT一個BLOB或TEXT列，最大長度為65535(2^16-1)個字符。查看章節6.5.3.1隱式的列定義變化。查看章節6.2.3.2BLOB和TEXT類型。MEDIUMBLOBMEDIUMTEXT一個BLOB或TEXT列，最大長度為16777215(2^24-1)個字符。查看章節6.5.3.1隱式的列定義變化。查看章節6.2.3.2BLOB和TEXT類型。LONGBLOBLONGTEXT一個BLOB或TEXT列，最大長度為4294967295(2^32-1)個字符。查看章節6.5.3.1隱式的列定義變化。注意，由于服務器/客戶端的協議以及MyISAM表通常有一個16M每通信包/表行的限制，你仍然不能使用這個類型的整個范圍。查看章節6.2.3.2BLOB和TEXT類型。ENUM('value1','value2',...)一個枚舉類型。一個僅能有一個值的字符串對象，這個值選自值列'value1'、'value2'、...、NULL或特殊的""出錯值。一個ENUM列可以有最大65535不同的值。查看章節6.2.3.3ENUM類型。SET('value1','value2',...)一個集合。一個能有零個或更多個值的字符串對象，其中每個值必須選自值列'value1'、'value2'、...。一個SET列可以有最大64個成員。查看章節6.2.3.4SET類型。MySQL支持所有的ANSI/ISOSQL92數字類型。這些類型包括準確數字的數據類型(NUMERIC、DECIMAL、INTEGER和SMALLINT)，也包括近似數字的數據類型(FLOAT、REAL和DOUBLEPRECISION)。關鍵詞INT是INTEGER的同義詞，關鍵詞DEC是DECIMAL的同義詞。NUMERIC和DECIMAL類型被MySQL以同樣的類型實現，這在SQL92標準中是允許的。他們用于保存對準確精度有重要要求的值，例如與金錢有關的數據。當以它們中的之一聲明一個列時，精度和數值范圍可以(通常是)被指定；例如：salaryDECIMAL(5,2)在這個例子中，5(精度(precision))代表重要的十進制數字的數目，2(數據范圍(scale))代表在小數點后的數字位數。在這種情況下，因此，salary列可以存儲的值范圍是從-99.99到99.99。(實際上MySQL在這個列中可以存儲的數值可以一直到999.99，因為它沒有存儲正數的符號)。譯者注：M與D對DECIMAL(M,D)取值范圍的影響類型說明取值范圍（MySQL<3.23）取值范圍（MySQL>=3.23）DECIMAL(4,1)-9.9到99.9-999.9到9999.9DECIMAL(5,1)-99.9到999.9-9999.9到99999.9DECIMAL(6,1)-999.9到9999.9-99999.9到999999.9DECIMAL(6,2)-99.99到999.99-9999.99到99999.99DECIMAL(6,3)-9.999到99.999-999.999到9999.999#在MySQL3.23及以后的版本中，DECIMAL(M,D)的取值范圍等于早期版本中的DECIMAL(M+2,D)的取值范圍。注釋結束：在ANSI/ISOSQL92中，句法DECIMAL(p)等價于DECIMAL(p,0)。同樣的，在執行被允許決定值p的地方,句法DECIMAL等價于DECIMAL(p,0)。MySQL目前還不支持DECIMAL/NUMERIC數據類型的這些變體形式中的任一種。一般來說這并不是一個嚴重的問題，通過明確地控制精度和數值范圍可以得到這些類型的主要功能益處。DECIMAL和NUMERIC值是作為字符串存儲的，而不是作為二進制浮點數，以便保護這些值的十進制精確度。一個字符用于數值的每一位、小數點(如果scale>0)和“-”符號(對于負值)。如果scale是0，DECIMAL和NUMERIC值不包含小數點或小數部分。DECIMAL和NUMERIC值的最大范圍與DOUBLE一致，但是對于一個給定的DECIMAL或NUMERIC列，它的實際范圍可制定該列時的precision或scale限制。當這樣的列被賦給了小數點的位數超過scale所指定的值時，該將根據scale進行四舍五入。當一個DECIMAL或NUMERIC列被賦與一個大小超過指定(或缺省)的precisionandscale的限止范圍時，MySQL以該列范圍的端點值存儲該值。
向sql數據庫中增加多條數據，用sql命令怎么寫急！急！急
插入語句常用寫法：
INSERTINTOitems(name,city,price,number,picture)VALUES('耐克運動鞋','廣州',500,1000,'003.jpg');
這種方式只能夠一次插入一條數據，要想插入多條數據，就得多次調用此sql語句，意味著多次與數據庫建立連接。但是這樣一來，就會增加服務器的負荷，因為，執行每一次SQL服務器都要同樣對SQL進行分析、優化等操作。幸好MySQL提供了另一種解決方案，就是使用一條INSERT語句來插入多條記錄。這并不是標準的SQL語法，因此只能在MySQL中使用。
一條INSERT語句插入批量數據的寫法：
INSERTINTO
[表名]([列名],[列名])
VALUES
([列值],[列值])),
([列值],[列值])),
([列值],[列值]));
可以看到，和原來的常規INSERT語句的區別，僅僅是在VALUES后面增加值的排列，每條記錄之間用英文輸入法狀態下的逗號隔開，是不是soeasy。
示例：
INSERTINTO
items(name,city,price,number,picture)
VALUES
('耐克運動鞋','廣州',500,1000,'003.jpg'),
('耐克運動鞋2','廣州2',500,1000,'002.jpg');
這樣，就實現了一次性插入了2條數據。
建議：
在程序中，插入批量數據時，最好使用這種通過一條INSERT語句來一次性插入的方式。這樣可以避免程序和數據庫建立多次連接，從而增加服務器負荷。
mysql同一個用戶可以連接幾個
一個用戶在MySQL中可以同時連接多個數據庫，但同一時間只能連接一個數據庫。這是因為MySQL是多進程的，每個用戶連接都會產生一個新的線程，如果連接多個數據庫，則相當于在不同的線程中進行操作，可以同時進行，但需要注意的是每個線程的資源是有限的，過多的線程會導致服務器資源緊張，并影響性能。因此，一般不建議同一用戶同時連接多個數據庫，以免影響服務器性能和操作效率。另外，也需要根據具體情況和需求來決定是否需要設置多個用戶以實現更好的管理和權限控制。
mysql中怎樣對大批量級的數據查詢進行優化
MySQL一直以來都支持正則匹配，不過對于正則替換則一直到MySQL8.0才支持。對于這類場景，以前要么在MySQL端處理，要么把數據拿出來在應用端處理。
比如我想把表y1的列str1的出現第3個action的子串替換成dble，怎么實現？
1.自己寫SQL層的存儲函數。代碼如下寫死了3個，沒有優化，僅僅作為演示，MySQL里非常不建議寫這樣的函數。
mysql
DELIMITER$$
USE`ytt`$$
DROPFUNCTIONIFEXISTS`func_instr_simple_ytt`$$
CREATEDEFINER=`root`@`localhost`FUNCTION`func_instr_simple_ytt`(
f_strVARCHAR(1000),--Parameter1
f_substrVARCHAR(100),--Parameter2
f_replace_strvarchar(100),
f_timesint--timescounter.onlysupport3.
)RETURNSvarchar(1000)
BEGIN
declarev_resultvarchar(1000)default'ytt';--result.
declarev_substr_lenintdefault0;--searchstringlength.
setf_times=3;--onlysupport3.
setv_substr_len=length(f_substr);
selectinstr(f_str,f_substr)into@p1;--Firstrealposition.
selectinstr(substr(f_str,@p1+v_substr_len),f_substr)into@p2;Secondaryvirtualposition.
selectinstr(substr(f_str,@p2+@p1+2*v_substr_len-1),f_substr)into@p3;--Thirdvirtualposition.
if@p1>0&&@p2>0&&@p3>0then--Fine.
select
concat(substr(f_str,1,@p1+@p2+@p3+(f_times-1)*v_substr_len-f_times)
,f_replace_str,
substr(f_str,@p1+@p2+@p3+f_times*v_substr_len-2))intov_result;
else
setv_result=f_str;--Neverchanged.
endif;
--Purgeallsessionvariables.
set@p1=null;
set@p2=null;
set@p3=null;
returnv_result;
end;
$$
DELIMITER;
--調用函數來更新：
mysql>updatey1setstr1=func_instr_simple_ytt(str1,'action','dble',3);
QueryOK,20rowsaffected(0.12sec)
Rowsmatched:20Changed:20Warnings:0
2.導出來用sed之類的工具替換掉在導入，步驟如下：（推薦使用）1）導出表y1的記錄。
mysqlmysql>select*fromy1intooutfile'/var/lib/mysql-files/y1.csv';QueryOK,20rowsaffected(0.00sec)
2）用sed替換導出來的數據。
shellroot@ytt-Aspire-V5-471G:/var/lib/mysql-files#sed-i's/action/dble/3'y1.csv
3）再次導入處理好的數據，完成。
mysql
mysql>truncatey1;
QueryOK,0rowsaffected(0.99sec)
mysql>loaddatainfile'/var/lib/mysql-files/y1.csv'intotabley1;
QueryOK,20rowsaffected(0.14sec)
Records:20Deleted:0Skipped:0Warnings:0
以上兩種還是推薦導出來處理好了再重新導入，性能來的高些，而且還不用自己費勁寫函數代碼。那MySQL8.0對于以上的場景實現就非常簡單了，一個函數就搞定了。
mysqlmysql>updatey1setstr1=regexp_replace(str1,'action','dble',1,3);QueryOK,20rowsaffected(0.13sec)Rowsmatched:20Changed:20Warnings:0
還有一個regexp_instr也非常有用，特別是這種特指出現第幾次的場景。比如定義SESSION變量@a。
mysqlmysql>set@a='aabbcceefilucy111bs234523556119101020301040';QueryOK,0rowsaffected(0.04sec)
拿到至少兩次的數字出現的第二次子串的位置。
mysqlmysql>selectregexp_instr(@a,'[:digit:]{2,}',1,2);+--------------------------------------+|regexp_instr(@a,'[:digit:]{2,}',1,2)|+--------------------------------------+|50|+--------------------------------------+1rowinset(0.00sec)
那我們在看看對多字節字符支持如何。
mysql
mysql>set@a='中國美國俄羅斯日本中國北京上海深圳廣州北京上海武漢東莞北京青島北京';
QueryOK,0rowsaffected(0.00sec)
mysql>selectregexp_instr(@a,'北京',1,1);
+-------------------------------+
|regexp_instr(@a,'北京',1,1)|
+-------------------------------+
|17|
+-------------------------------+
1rowinset(0.00sec)
mysql>selectregexp_instr(@a,'北京',1,2);
+-------------------------------+
|regexp_instr(@a,'北京',1,2)|
+-------------------------------+
|29|
+-------------------------------+
1rowinset(0.00sec)
mysql>selectregexp_instr(@a,'北京',1,3);
+-------------------------------+
|regexp_instr(@a,'北京',1,3)|
+-------------------------------+
|41|
+-------------------------------+
1rowinset(0.00sec)
那總結下，這里我提到了MySQL8.0的兩個最有用的正則匹配函數regexp_replace和regexp_instr。針對以前類似的場景算是有一個完美的解決方案。
文章分享結束，mysql單表1000萬條 性能和mysql千萬數據查詢優化的答案你都知道了嗎？歡迎再次光臨本站哦！
