如何進(jìn)行MySQL管理基礎(chǔ)中的性能優(yōu)化

如何進(jìn)行MySQL管理基礎(chǔ)中的性能優(yōu)化，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

1.索引

<1>應(yīng)該添加索引的列包括：用于連接的列，WHERE、GROUP BY或ORDER BY子句涉及的列。
<2>有很多重復(fù)值的列不能通過(guò)建立索引來(lái)改善性能，取值唯一的列則能充分通過(guò)索引獲益。
<3>索引并非越多越方便，它可能在增加搜索速度的同時(shí)，減慢INSERT、DELETE和UPDATE等寫(xiě)操作。很小的表不需要?jiǎng)?chuàng)建索引。

   一些服務(wù)器狀態(tài)變量的值可以反映索引的情況或?yàn)樘砑铀饕峁﹨⒖迹纾?br/>
   Handler_read_key：通過(guò)索引讀取行的請(qǐng)求數(shù)。該值較低，表示索引不經(jīng)常使用。
   Handler_read_rnd_next：在數(shù)據(jù)文件中讀下一行的請(qǐng)求數(shù)。該值較高，表示存在大量的全表掃描，說(shuō)明表上沒(méi)有適當(dāng)?shù)亟⑺饕虿樵儧](méi)有利用到索引。

   可以mysqladmin extended-status或SHOW STATUS命令查看狀態(tài)變量，例如：

mysql> show status like 'Handler_read_%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
6 rows in set (0.00 sec)

[@more@]對(duì)于建立了索引的表，應(yīng)該定期執(zhí)行ANALYZE TABLE命令。該命令分析并存儲(chǔ)一個(gè)表的鍵分布，MySQL使用存儲(chǔ)的鍵分布決定連接時(shí)表的連接順序，以及查詢時(shí)使用哪些索引。


2.query cache

   查看相關(guān)的服務(wù)器系統(tǒng)變量，確認(rèn)query cache已經(jīng)打開(kāi)：

mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 15728640 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

   have_query_cache：MySQL服務(wù)器是否支持query cache。
   query_cache_size：分配給query cache的內(nèi)存總量。
   query_cache_type：query cache的類型。0或OFF表示不使用query cache，但系統(tǒng)還是會(huì)為query cache分配內(nèi)存空間；1或ON表示緩存所有查詢結(jié)果，但以SELECT SQL_NO_CACHE開(kāi)頭的除外；2或DEMAND表示只緩存以SQL_CACHE開(kāi)頭的語(yǔ)句。

   一旦表發(fā)生變化，query cache中涉及該表的緩存內(nèi)容就會(huì)失效，所以經(jīng)常變化的表并不能從query cache的使用中受益，可以考慮為這些查詢添加SQL_NO_CACHE選項(xiàng)。


3.查詢分析

   在查詢語(yǔ)句最前面添加EXPLAIN關(guān)鍵字，返回的結(jié)果將列出該語(yǔ)句中所有的SELECT。例如：

mysql> explain select a.username, b.nickname, a.email from members a, memberfields b where a.uid = b.uid and a.email lik
e "%163.com";
+----+-------------+-------+--------+---------------+---------+---------+-------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------+-------+-------------+
|  1 | SIMPLE      | b     | ALL    | PRIMARY       | NULL    | NULL    | NULL        | 46237 |             |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 3       | test2.b.uid |     1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------+-------+-------------+
2 rows in set (0.00 sec)

   id列為SELECT在整個(gè)查詢中的位置；select_type列為查詢的類型，如簡(jiǎn)單查詢、UNION中最后面的查詢、子查詢等等；type列為執(zhí)行連接的情況，const最佳，ALL最差；possible_keys列為可能使用的索引；key列為實(shí)際使用的索引；rows列為執(zhí)行該查詢需要在相應(yīng)表中檢索的行的數(shù)量，將每條記錄中的數(shù)字相乘即為該查詢的總開(kāi)銷(xiāo)；Extra列為一些其它相關(guān)信息。


4.優(yōu)化多表查詢

(1) 子查詢轉(zhuǎn)連接

   例如：

select email from cdb_members where uid = (select uid from cdb_memberfields where nickname = 'aquxyz');

   可轉(zhuǎn)化為：

select email from cdb_members c_m, cdb_memberfields c_mf where c_m.uid = c_mf.uid and nickname = 'aquxyz';

(2) 巧妙利用對(duì)結(jié)果集的限制

   例如：

select nickname from cdb_memberfields where uid = (select uid from cdb_members where email like '%com.cn' and regdate = (select min(regdate) from cdb_members));

   可轉(zhuǎn)化為：

select nickname from cdb_memberfields c_mf, cdb_members c_m where c_mf.uid = c_m.uid and c_m.email like '%com.cn' order by c_m.regdate asc limit 1;

(3) 使用變量

   還是上面的例子，可轉(zhuǎn)化為如下兩條語(yǔ)句：

select @min:=min(regdate) from cdb_members;
select nickname from cdb_memberfields c_mf, cdb_members c_m where c_mf.uid = c_m.uid and c_m.email like '%com.cn' and c_m.regdate = @min;


5.優(yōu)化表

<1>對(duì)于經(jīng)常修改的表，使用定長(zhǎng)字段，而不用變長(zhǎng)字段（對(duì)InnoDB表無(wú)效）；刪除沒(méi)有用的字段。
<2>OPTIMIZE TABLE命令。
<3>對(duì)表進(jìn)行合并；在查詢中使用具有相同類型和長(zhǎng)度的連接字段。


6.調(diào)整服務(wù)器設(shè)置

   可以通過(guò)調(diào)整一些服務(wù)器系統(tǒng)變量，提高M(jìn)ySQL服務(wù)器的性能。

<1>key_buffer_size：MyISAM表的索引塊使用的緩沖區(qū)大小。一般可以設(shè)置為服務(wù)器可用內(nèi)存的25%-30%。
<2>table_cache：MySQL的所有線程可以打開(kāi)的表的總數(shù)。應(yīng)根據(jù)Opened_tables狀態(tài)變量的值來(lái)決定是否要增大該值。
<3>sort_buffer_size：為每個(gè)線程的排序操作分配的緩沖區(qū)大小。增大該值可以加快ORDER BY和GROUP BY操作。
<4>read_buffer_size：為每個(gè)線程的sequential scan分配的緩沖區(qū)。增大該值可以提高查詢速度。
<5>read_rnd_buffer_size：為每個(gè)線程的random scan分配的緩沖區(qū)。索引排序操作后，MySQL從這個(gè)緩沖區(qū)中讀取排好順序的行，增大該值可以加快ORDER BY操作。每個(gè)客戶端都會(huì)分配此緩沖區(qū)，所以不要全局變量設(shè)置得太大。
<6>binlog_cache_size：事務(wù)過(guò)程中為為binary log保存SQL語(yǔ)句的高速緩存。如果經(jīng)常使用大型、多語(yǔ)句的事務(wù)，可以增大該值以獲得更好的性能。
<7>bulk_insert_buffer_size：為每個(gè)線程的批量insert操作分配的樹(shù)狀cache的大小。
<8>tread_cache_size：服務(wù)器緩存線程以便重用的總數(shù)。如果有許多新連接，可以考慮增大該值。

undefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefined

關(guān)于如何進(jìn)行MySQL管理基礎(chǔ)中的性能優(yōu)化問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。