程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 程序員必備MySQL數據庫開發常識

程序員必備MySQL數據庫開發常識

編輯:關於MYSQL數據庫
MySQL數據庫的應用非常普遍,本文為大家分享MySQL數據庫開發當中的一些常識,存儲引擎的選擇,索引設計及使用和大批量插入時SQL語句的優化。希望能對大家有幫助。

  存儲引擎的選擇

  聲明:本文所針對的數據庫版本都是MySQL 5這裡我主要針對兩種存儲引擎進行簡單比較分別是MyISAM和InnoDB,首先比較下區別:

  1. MyISAM不支持事務,不支持外鍵,優點是訪問速度高,批量插入速度快。假設大量的操作是select、insert,建議采用該存儲引擎。但是在我的實際應用中,出現過批量插入過於頻繁的時候,當數據量到達一定級別,出現表損壞的情況。

  2. InnoDB支持事務處理,但是相對於前者,處理效率低一些,並且其索引及數據也更占用磁盤空間。在存儲一些關鍵數據,並需要對其進行事務操作的時候,我們可以選擇innodb,當然,我認為他不應該是訪問量太大的。

  索引的設計及使用

  沒有索引的表是恐怖的,除非裡頭沒多少數據,但是怎麼設計索引是合理的?恐怕不是所有人都明白,這裡簡要分析下索引的設計及使用。

  1. 索引通常是設置where字句中的列,如果你設置select後的列,這是沒有任何意義的。當然你需要對某列進行排序,order by後的列也是可以建成索引的。

  2. 使用唯一索引,主鍵就是最好的例子,假設你建的索引列,大量都是重復的,例如:性別,那麼這樣的索引並不會加快搜索速度。至於為什麼,請大家自行了解索引的工作原理。

  3. 只要有可能,就要盡量限定索引的長度,例如索引列為 char(100),在其前10個字符大部分都是唯一的,請設置索引的長度為10,使用短索引可以加快查詢速度,並節省硬盤空間。

  4. 索引的左前綴特性,聯合索引實質上也是建立了多個的索引,那麼是建立聯合索引好還是分別建多個索引好呢?顯然前者更好,利用左前綴特性,只要聯合索引的最左的列被用到,那麼索引都會被使用。

  5. 當然,最後要說的是,不要過度使用索引,索引越多,插入的速度越慢,尤其到數據量龐大時,同時,大量的索引將耗費很多硬盤空間,造成不必要的浪費。

  下面舉幾個列子來說明索引的使用:

  1.聯合索引的左前綴

  先看索引結構:

以下是代碼片段:
 MySQL> show index from user; 
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
  | user  |    0 | PRIMARY  |1 | user_id     | A   |     2 |     NULL | NULL   || BTREE|   | 
  | user  |    1 | user     |1 | username    | A   |  NULL |     NULL | NULL   || BTREE|   | 
  | user  |    1 | user     |2 | order | A   |  NULL |     NULL | NULL   || BTREE|   | 
  | user  |    1 | user     |3 | email | A   |  NULL |     NULL | NULL   | YES  | BTREE|   | 
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
  4 rows in set (0.00 sec)

  

user是聯合索引的名稱,包含3個列,分別是username,order,email。接下來執行以下sql,使用explain命令來分析下運行結果。

 

以下是代碼片段:
 MySQL> explain select * from user where username='leehui'; 
  +----+-------------+-------+------+---------------+------+---------+-------+------+--------+ 
  | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra | 
  +----+-------------+-------+------+---------------+------+---------+-------+------+--------+ 
  |  1 | SIMPLE| user  | ref  | user    | user | 152     | const |    1 | Using where | 
  +----+-------------+-------+------+---------------+------+---------+-------+------+--------+ 
  1 row in set (0.00 sec) 
  MySQL> explain select * from user where pws='123'; 
  +----+-------------+-------+------+---------------+------+---------+------+------+---------+ 
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra | 
  +----+-------------+-------+------+---------------+------+---------+------+------+---------+ 
  |  1 | SIMPLE| user  | ALL  | NULL    | NULL | NULL    | NULL |    2 | Using where | 
  +----+-------------+-------+------+---------------+------+---------+------+------+---------+ 
  1 row in set (0.00 sec)

  在兩句sql中,我們可以發現,第一個sql雖然沒用上,全部的索引列,但由於使用到了最左端的列,所以,聯合索引還是啟用了,第二句沒有使用到最左的列,所以索引沒有使用。

2.關於like關鍵字

  對於使用like的查詢,需要注意的是只有列的%不在第一個字符索引才可能被使用。以下分別展示了使用like的查詢,第一個是索引被使用的,第二個是索引未被使用的。

以下是代碼片段:
 MySQL> explain select * from user where username like'lee%'; 
  +----+-------------+-------+-------+---------------+------+---------+------+------+---------+ 
  | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra | 
  +----+-------------+-------+-------+---------------+------+---------+------+------+---------+ 
  |  1 | SIMPLE| user  | range | user    | user | 152     | NULL |    1 | Using where | 
  +----+-------------+-------+-------+---------------+------+---------+------+------+---------+ 
  1 row in set (0.00 sec) 
  MySQL> explain select * from user where username like'%lee'; 
  +----+-------------+-------+------+---------------+------+---------+------+------+----------+ 
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra | 
  +----+-------------+-------+------+---------------+------+---------+------+------+----------+ 
  |  1 | SIMPLE| user  | ALL  | NULL    | NULL | NULL    | NULL |    2 | Using where | 
  +----+-------------+-------+------+---------------+------+---------+------+------+----------+ 
  1 row in set (0.00 sec)

   3. 查看索引使用情況

  使用以下命令:

 MySQL> show status like 'Handler_read_key'; 
  +------------------+-------+ 
  | Variable_name    | Value | 
  +------------------+-------+ 
  | Handler_read_key | 0     | 
  +------------------+-------+ 
  1 row in set (0.00 sec)

  如果索引正在工作,那麼Handler_read_key 會很高,如果查詢中出現Handler_read_rnd_next的值很高,則表明查詢低效,索引的應用並不合理。

大批量插入時的SQL語句優化

  在大量插入時,尤其是並發插入時,mysql往往要承受更高的負載,使用MySQL administortar的健康檢查就可以發現,其avg的值相當高,在這種情況下,首先要做的是sql語句的優化,比較下面兩個句子,後者的速度比前者要快得多。因為減少大量的連接。

  復制內容到剪貼板代碼:

以下是代碼片段:
 insert into test values(aa,bb) 
  insert into test values(cc,dd) 
  insert into test values (aa),(bb),(cc),(dd)

  在我的一個實際應用中,由於需要經常有數百個並發的插入,我還采用了insert delayed into來取代insert into,前者與後者的區別是在執行插入語句時,數據保存在內存隊列中,待數據庫空閒時執行,但是會立即返回一個插入成功的信息。使用insert delayed into時需要注意:此時不能使用MySQL_insert_id(),因為此時並沒有真正插入。對特別重要的數據不宜采用該語句,避免數據以外丟失。

  其他雜談

  1.MySQL myisam 表超過4G無法訪問的解決

  myisam引擎默認是支持4GB,innodb理論上可以到6TB,假設單張表容量超過4GB,可能導致表都無法訪問了。可以通過以下命令增加表最大數據量:

以下是代碼片段:
 MySQL> alter table user MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000; 
  Query OK, 2 rows affected (0.09 sec) 
  Records: 2  Duplicates: 0  Warnings: 0
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved