mysql存儲資源文件（mysql 文件資料庫）

• 1、mysql 資料庫可以有多大
• 2、設置mysql 資料庫文件最大多少
• 3、mysql裡面text能存多大的數據
• 4、MySQL里存儲圖片的是什麼數據類型

MySQL 3.22 限制的表大小為4GB。由於在MySQL 3.23 中使用了MyISAM 存儲引擎，最大表尺寸增加到了65536TB（2567 – 1位元組）。由於允許的表尺寸更大，MySQL資料庫的最大有效表尺寸通常是由操作系統對文件大小的限制決定的，而不是由MySQL內部限制決定的。

InnoDB 存儲引擎將InnoDB 表保存在一個表空間內，該表空間可由數個文件創建。這樣，表的大小就能超過單獨文件的最大容量。表空間可包括原始磁碟分區，從而使得很大的表成為可能。表空間的最大容量為64TB。

擴展資料

據D.V.B 團隊以及Cmshelp 團隊做CMS 系統評測時的結果來看，MySQL單表大約在2千萬條記錄（4G）下能夠良好運行，經過資料庫的優化後5千萬條記錄（10G）下運行良好。

這對於MySQL是不公平的，那些CMS廠商非但沒有把內核做好反而還在添加很多花哨的功能，最終導致其產品自身負載過低。

他們並沒有針對自身負載效果作出相應的資料庫優化方案及標準，而是繼續保留著複雜的結構造成對MySQL的資源無休止的浪費，最終導致了其負載上的缺陷。

於是他們便充分發揮中國人的傳統優勢——變通：避重就輕的採用了所謂的分表式存儲，雖然在一定程度上緩解了自身負載的缺陷，但是導致了網站後期維護以及資源上的浪費。

用一個不恰當的比喻來形容，MySQL中的的表就像一塊地，單表就相當於利用這塊地蓋高層建築充分利用達到高人員負載，但分表就相當於用這塊地蓋了一間平房。

如果為了達到高人員負載的話那就需要另開地皮達到目的，但是我們要思考，是地不夠，還是他的能力不夠，如此做法讓人感到資源的浪費以及規劃的嚴重缺陷。

非root用戶運行MySQL，當MySQL配置比較高時，MySQL運行中生效的參數值與配置的值不一樣,所以具體分析一下MySQL是怎麼調整這些參數值的。 

這篇文章的目的是為了說明在系統資源不夠的情況下，MySQL 是怎麼調整者三個參數的。說明此文涉及到三個參數open_files_limit、 max_connections、 table_open_cache。與這三個參數相關的系統資源是打開文件數限制，即文件描述符（fd）限制。系統參數與文件描述符的關係 – max_connection  fd : 每一個MySQL connection      都需要一個文件描述符；

– table_open_cache  fd 打開一張表至少需要一個      文件描述符，如打開MyISAM需要兩個fd ；

– 系統最大打開文件數可以通過 ulimit -n查看。MySQL調整參數的方式

根據配置（三個參數的配置值或默認值）計算 request_open_files（需要的文件描述符）；

  2.獲取有效的系統的限制值effective_open_files；  3.根據effective_open_files調整request_open_files；  4.根據調整後的request_open_files,計算實際生效的參數值（show variables 可查看參數值）。計算request_open_filesrequest_open_files有三個計算公式：1.      // 最大連接數+同時打開的表的最大數量+其他（各種日誌等等）2.     limit_1= max_connections+table_cache_size * 2 + 10;3.   4.      //假設平均每個連接打開的表的數量（2-4）5.      //源碼中是這麼寫的：6.      //We are trying to allocate no less than 7.      // max_connections*5 file handles8.      limit_2= max_connections * 5;9.   10.    //mysql 默認的默認是500011.    limit_3= open_files_limit ? open_files_limit : 5000;12.  13.     所以open_files_limit期待的最低14.     request_open_files= max(limit_1,limit_2,limit_3);計算effective_open_files:MySQL 的思路： 

在有限值的的範圍內MySQL 盡量將effective_open_files的值設大。

修正request_open_files

requested_open_files= min(effective_open_files, request_open_files)

重新計算參數值

修正open_files_limit

open_files_limit = effective_open_files

修正max_connections

max_connections 根據 request_open_files 來做修正。1.  limit = requested_open_files – 10 – TABLE_OPEN_CACHE_MIN * 2;

如果配置的max_connections值大於limit，則將max_connections 的值修正為limit

其他情況下 max_connections 保留配置值

修正table_cache_size

table_cache_size 會根據 request_open_files 來做修正1.   // mysql table_cache_size 最小值，4002.   limit1 = TABLE_OPEN_CACHE_MIN3.   // 根據 requested_open_files 計算4.   limit2 = (requested_open_files – 10 – max_connections) / 25.   limit = max(limit1,limt2);

如果配置的table_cache_size 值大於limit，則將 table_cache_size 的值修正為limit

其他情況下table_cache_size 保留配置值

舉例

以下用例在非 root 用戶下運行

參數設置：

   //mysql

max_connections = 500

         table_open_cache = 999

//ulimit -n

1500

生效的值：

   open_files_limit = 1500   max_connections = min[(1500 – 10 – 800),500] = 500

table_open_cache = ( 1500 – 10 – 500) / 2 =495

最多可以存儲65535位元組數據。

在mysql中，文本文件存儲從0到65，535位元組（64KB）的位元組。因此，mysql中的文本最多可以存儲65，535位元組。

文本文件有四種類型：tinytext、Text、mediumtext和longtext，它們都具有相同的最大長度和存儲需求。

擴展資料：

mysql的特點：

1．用C和c＋＋編寫，並使用各種編譯器進行測試，以確保源代碼的可移植性。

2、支持AIX、FreeBSD、hp－ux、Linux、MacOS、NovellNetware、OpenBSD、OS／2Wrap、Solaris、Windows等操作系統。

3．api是為各種編程語言提供的。這些編程語言包括C、c＋＋、Python、Java、Perl、PHP、Eiffel、Ruby、．net和Tcl。

4．支持多線程，充分利用CPU資源。

5、優化SQL查詢演算法，有效提高查詢速度。

6、可以作為單獨的應用程序應用在客戶端伺服器網路環境中，也可以作為嵌入式軟體庫嵌入到其他軟體中。

7．提供多語言支持。通用代碼如中文的GB2312、日文的BIG5和日文的Shift＿JIS可以用作數據表名和數據列名。

背景

MySQL 一直以來都有 TEXT、BLOB 等類型用來存儲圖片、視頻等大對象信息。比如一張圖片，隨便一張都 5M 以上。視頻也是，隨便一部視頻就是 2G 以上。

假設用 MySQL 來存放電影視頻等信息，一部是 2G，那麼存儲 1000 部就是 2TB，2TB 也就是 1000 條記錄而已，但是對資料庫性能來說，不僅僅是看記錄數量，更主要的還得看佔用磁碟空間大小。空間大了，所有以前的經驗啥的都失效了。

所以一般來說存放這類信息，也就是存儲他們的存放路徑，至於文件本身存放在哪裡，那這就不是資料庫考慮的範疇了。資料庫只關心怎麼來的快，怎麼來的小。

舉例

雖然不推薦 MySQL 這樣做，但是也得知道 MySQL 該怎麼做才行，做到心裡有數。比如下面一張微信圖片，大概 5M 的樣子。

root@ytt:/var/lib/mysql-files# ls -sihl 微信圖片_20190711095019.jpg274501 5.4M -rw-r–r– 1 root root 5.4M Jul 11 07:17 微信圖片_20190711095019.jpg

拷貝 100 份這樣的圖片來測試

root@ytt:/var/lib/mysql-files# for i in `seq 1 100`; do cp 微信圖片_20190711095019.jpg “$i”.jpg;done;

root@ytt:/var/lib/mysql-files# ls

100.jpg   17.jpg  25.jpg  33.jpg  41.jpg  4.jpg   58.jpg  66.jpg  74.jpg  82.jpg  90.jpg  99.jpg  f8.tsv

10.jpg    18.jpg  26.jpg  34.jpg  42.jpg  50.jpg  59.jpg  67.jpg  75.jpg  83.jpg  91.jpg  9.jpg   微信圖片_20190711095019.jpg

1111.jpg  19.jpg  27.jpg  35.jpg  43.jpg  51.jpg  5.jpg   68.jpg  76.jpg  84.jpg  92.jpg  f1.tsv

11.jpg    1.jpg   28.jpg  36.jpg  44.jpg  52.jpg  60.jpg  69.jpg  77.jpg  85.jpg  93.jpg  f2.tsv

12.jpg    20.jpg  29.jpg  37.jpg  45.jpg  53.jpg  61.jpg  6.jpg   78.jpg  86.jpg  94.jpg  f3.tsv

13.jpg    21.jpg  2.jpg   38.jpg  46.jpg  54.jpg  62.jpg  70.jpg  79.jpg  87.jpg  95.jpg  f4.tsv

14.jpg    22.jpg  30.jpg  39.jpg  47.jpg  55.jpg  63.jpg  71.jpg  7.jpg   88.jpg  96.jpg  f5.tsv

15.jpg    23.jpg  31.jpg  3.jpg   48.jpg  56.jpg  64.jpg  72.jpg  80.jpg  89.jpg  97.jpg  f6.tsv

16.jpg    24.jpg  32.jpg  40.jpg  49.jpg  57.jpg  65.jpg  73.jpg  81.jpg  8.jpg   98.jpg  f7.tsv

我們建三張表，分別用 LONGBLOB、LONGTEXT 和 VARCHAR 來存儲這些圖片信息

mysql show create table tt_image1G

*************************** 1. row ***************************

Table: tt_image1

Create Table: CREATE TABLE `tt_image1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`image_file` longblob,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

mysql show create table tt_image2G

*************************** 1. row ***************************

Table: tt_image2

Create Table: CREATE TABLE `tt_image2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`image_file` longtext,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

mysql show create table tt_image3G

*************************** 1. row ***************************

Table: tt_image3

Create Table: CREATE TABLE `tt_image3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`image_file` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

我們來給三張表插入 100 張圖片（插入前，建議把 max_allowed_packet 設置到最大）

tt_image1

root@ytt:/var/lib/mysql-files# for i in `seq 1 100`;

do mysql -S /var/run/mysqld/mysqld.sock -e “insert into ytt.tt_image1(image_file)

values (load_file(‘/var/lib/mysql-files/$i.jpg’))”;done;

tt_image2

root@ytt:/var/lib/mysql-files# for i in `seq 1 100`;

do mysql -S /var/run/mysqld/mysqld.sock -e “insert into ytt.tt_image2(image_file)

values (hex(load_file(‘/var/lib/mysql-files/$i.jpg’)))”;done;

tt_image3

root@ytt:/var/lib/mysql-files# aa=’begin;’;for i in `seq 1 100`;

do aa=$aa”insert into ytt.tt_image3(image_file) values

(‘/var/lib/mysql-files/$i.jpg’);”;

done;aa=$aa’commit;’;mysql -S /var/run/mysqld/mysqld.sock -e “`echo $aa`”;

檢查下三張表記錄數

mysql select ‘tt_image1’ as name ,count(*) from tt_image1 union allselect ‘tt_image2’,count(*) from tt_image2 union all select ‘tt_image3’, count(*) from tt_image3;+———–+———-+| name      | count(*) |+———–+———-+| tt_image1 |      100 || tt_image2 |      100 || tt_image3 |      100 |+———–+———-+3 rows in set (0.00 sec)

看下文件大小，可以看到實際大小排名，LONGTEXT 欄位存儲的最大，LONGBLOB 欄位縮小到一半，最小的是存儲圖片路徑的表 tt_image3。所以這裡從存儲空間來看，存放路徑最佔優勢。

root@ytt:/var/lib/mysql/ytt# ls -silhS tt_image*274603 1.1G -rw-r—– 1 mysql mysql 1.1G Jul 11 07:27 tt_image2.ibd274602 545M -rw-r—– 1 mysql mysql 544M Jul 11 07:26 tt_image1.ibd274605  80K -rw-r—– 1 mysql mysql 112K Jul 11 07:27 tt_image3.ibd

那麼怎麼把圖片取出來呢？

tt_image3 肯定是最容易的

mysql select * from tt_image3;+—-+—————————-+| id | image_file                 |+—-+—————————-+|  1 | /var/lib/mysql-files/1.jpg |+—-+—————————-+…100 rows in set (0.00 sec)

tt_image1 直接導出來二進位文件即可，下面我寫了個存儲過程，導出所有圖片。

mysql DELIMITER $$mysql USE `ytt`$$mysql DROP PROCEDURE IF EXISTS `sp_get_image`$$mysql CREATE DEFINER=`ytt`@`localhost` PROCEDURE `sp_get_image`()mysql BEGIN      DECLARE i,cnt INT DEFAULT 0;      SELECT COUNT(*) FROM tt_image1 WHERE 1 INTO cnt;      WHILE i cnt DO        SET @stmt = CONCAT(‘select image_file from tt_image1  limit ‘,i,’,1 into dumpfile ”/var/lib/mysql-files/image’,i,’.jpg”’);        PREPARE s1 FROM @stmt;        EXECUTE s1;        DROP PREPARE s1;      SET i = i + 1;      END WHILE;      END$$mysql DELIMITER ;mysql call sp_get_image;

tt_image2 類似，把 select 語句里 image_file 變為 unhex(image_file) 即可。

總結

這裡我舉了個用 MySQL 來存放圖片的例子，總的來說有以下三點：

佔用磁碟空間大（這樣會帶來各種各樣的功能與性能問題，比如備份，寫入，讀取操作等）

使用不易

還是推薦用文件路徑來代替實際的文件內容存放