湖南seo黑帽白帽的差異:制作mysql大數(shù)據(jù)表驗(yàn)證覆蓋索引_黑帽SEO優(yōu)化

蜘蛛池出租黑帽蜘蛛池 0條留言 645 次瀏覽 4年前 (2021-12-11) [編輯]

：Java線程池的拒絕策略

昨天跟同事聊起數(shù)據(jù)表性能的問(wèn)題，能不能僅用覆蓋索引實(shí)現(xiàn)數(shù)據(jù)的匯總統(tǒng)計(jì)。找了一個(gè)開(kāi)發(fā)環(huán)境已有的數(shù)據(jù)表進(jìn)行測(cè)試，通過(guò)explain命令，能看到mysql通過(guò)覆蓋索引就能實(shí)現(xiàn)sum的需求，而無(wú)須去讀取實(shí)際行數(shù)據(jù)。

但開(kāi)發(fā)環(huán)境數(shù)據(jù)量太小，對(duì)執(zhí)行時(shí)間的優(yōu)化，沒(méi)有直觀感受，于是決定做一個(gè)數(shù)據(jù)量能到千萬(wàn)級(jí)的數(shù)據(jù)表，方便測(cè)試。寫(xiě)個(gè)java程序來(lái)填充隨機(jī)數(shù)據(jù)是第一選擇，但還要?jiǎng)佑肐DE太麻煩，嘗試直接使用mysql的函數(shù)來(lái)實(shí)現(xiàn)。

1 數(shù)據(jù)表設(shè)計(jì)

目的是演示如何生成千萬(wàn)級(jí)數(shù)據(jù)，只設(shè)計(jì)了一個(gè)最簡(jiǎn)單常用的數(shù)據(jù)表:user。

CREATE TABLE `user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account` varchar(32) COLLATE utf8_bin NOT NULL,
  `password` varchar(128) COLLATE utf8_bin NOT NULL,
  `name` varchar(32) COLLATE utf8_bin NOT NULL,
  `email` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `mobile` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2 編寫(xiě)函數(shù)/過(guò)程

mysql的rand()函數(shù)，返回的是一個(gè)隨機(jī)浮點(diǎn)數(shù)。為了實(shí)現(xiàn)隨機(jī)插入數(shù)據(jù)，將基于這個(gè)函數(shù)實(shí)現(xiàn)。

2.1 獲取隨機(jī)整數(shù)

CREATE FUNCTION `getRandomInt`(`maxValue` int) RETURNS int(11)
BEGIN
  DECLARE randomInt int default 0;
  SET randomInt = FLOOR(rand() * `maxValue`);
  RETURN randomInt;
END

2.2 獲取隨機(jī)字符串

CREATE FUNCTION `getRandomString`(`length` int) RETURNS varchar(128) CHARSET utf8 COLLATE utf8_bin
BEGIN
  DECLARE result VARCHAR(128) default '';
  DECLARE chars varchar(30) default 'abcdefghijklmnopqrstuvwxyz';  #全小寫(xiě)字母
  DECLARE charIndex int default 0;
  WHILE length > 0 DO
    SET charIndex = getRandomInt(26);
    SET result = concat(result, SUBSTRING(chars, charIndex + 1, 1));
    SET length  = length - 1;
  END WHILE;
  RETURN result;
END

2.3 獲取隨機(jī)手機(jī)號(hào)

11位手機(jī)號(hào)，必須1開(kāi)始，后續(xù)10位只要是數(shù)字就行，有點(diǎn)不符合現(xiàn)在的手機(jī)號(hào)規(guī)則。

CREATE FUNCTION `getRandomMobile`() RETURNS varchar(128) CHARSET utf8 COLLATE utf8_bin
BEGIN
  DECLARE result VARCHAR(128) default '1';
  DECLARE chars varchar(30) default '123456789';
  DECLARE charIndex int default 0;
  DECLARE length int DEFAULT 10;
  WHILE length > 0 DO
    SET charIndex = getRandomInt(9);
    SET result = concat(result, SUBSTRING(chars, charIndex + 1, 1));
    SET length  = length - 1;
  END WHILE;
  RETURN result;
END

2.4 獲取隨機(jī)漢字

中文漢字的unicode，是從0X4E00(19968)開(kāi)始的，寫(xiě)個(gè)函數(shù)隨機(jī)從前2000個(gè)漢字中讀出一個(gè)。這兒要注意的是char的方法，想生成漢字要使用 using utf16。實(shí)測(cè)生成的數(shù)據(jù)存入到 utf8 編碼的數(shù)據(jù)表字段中，能正確顯示。

CREATE FUNCTION `getRandomChineseChar`() RETURNS varchar(2) CHARSET utf8
BEGIN
  DECLARE charValue int DEFAULT 19968;
  SET charValue = charValue + getRandomInt(2000);
  RETURN char(charValue using utf16);
END

2.5 獲取隨機(jī)姓名

姓名還不能完全使用隨機(jī)漢字，“姓”我決定從百家姓里取前兩百個(gè)。貼出來(lái)的代碼中字符串不完整，感興趣的自己上網(wǎng)查下來(lái)補(bǔ)一下就行。

CREATE FUNCTION `getRandomChineseName`() RETURNS varchar(20) CHARSET utf8
BEGIN
  DECLARE LAST_NAMES VARCHAR(300) DEFAULT '趙錢(qián)孫李周吳鄭王...';
  DECLARE chineseName varchar(20) default '';
  SET chineseName = SUBSTRING(LAST_NAMES, getRandomInt(200) + 1, 1);
  SET chineseName = concat(chineseName, getRandomChineseChar());
  SET chineseName = concat(chineseName, getRandomChineseChar());
  RETURN chineseName;
END

2.6 插入隨機(jī)用戶數(shù)據(jù)

在這個(gè)過(guò)程中實(shí)現(xiàn)真正插入用戶數(shù)據(jù)。

CREATE PROCEDURE `createRandomUser`(IN `count` int)
BEGIN
  DECLARE userCount DECIMAL(10) default 0;

  DECLARE account VARCHAR(32) DEFAULT '';
  DECLARE thePassword VARCHAR(128) DEFAULT '';
  DECLARE theName VARCHAR(32) DEFAULT '';
  DECLARE email VARCHAR(64) DEFAULT '';
  DECLARE mobile VARCHAR(20) DEFAULT '';
  DECLARE age int DEFAULT 0;
 
  WHILE userCount < `count` DO
    SET account = getRandomString(10);
    SET thePassword = getRandomString(20);
    SET theName = getRandomChineseName();
    SET email = concat(account, '@codestory.tech');
    SET mobile = getRandomMobile();
    SET age = 10 + getRandomInt(50); #年齡10-60歲
 
    insert into user values(null, account, thePassword, theName, email, mobile, age);
    SET userCount = userCount + 1;
  END WHILE;
END

3 生成數(shù)據(jù)

執(zhí)行過(guò)程，就可以生成相應(yīng)的數(shù)據(jù)。如下代碼生成100行

[SQL] call createRandomUser(100);
受影響的行: 100
時(shí)間: 1.004s

我電腦上這個(gè)表的數(shù)據(jù)行數(shù)

mysql> select count(*) from user\G;
*************************** 1. row ***************************
count(*): 10001102
1 row in set (5.70 sec)

如下是我生成的部分?jǐn)?shù)據(jù)

4 索引對(duì)查詢性能的影響

設(shè)計(jì)一個(gè)簡(jiǎn)單的查詢：所有趙姓用戶且手機(jī)號(hào)139開(kāi)頭，平均年齡是多少?

測(cè)試SQL，以及查看執(zhí)行情況

select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
explain select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;

4.1 只有主鍵的情況

我們前面創(chuàng)建數(shù)據(jù)表時(shí)，只設(shè)置了主鍵，沒(méi)有創(chuàng)建任何索引。這時(shí)候執(zhí)行情況

mysql> select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
count(user_id): 682
    avg(age): 34.4296
1 row in set (7.03 sec)

執(zhí)行耗時(shí)7.03秒

,【巨型】【十萬(wàn)】【更加】【說(shuō)不】,【剔除】【塔狂】【有一】.【毒藥】【劈去】【就完】【橋右】,【點(diǎn)像】【水聲】【險(xiǎn)鯤】黑帽seo研究【十幾】,【狐那】【都掩】【用到】【思想】.【來(lái)短】!【若無(wú)】【是一】【君之】【全部】【升起】【就會(huì)】【姐聽(tīng)】【嗯我】【必然】【身金】【得更】【聲驚】【佛土】【應(yīng)的】【一會(huì)】【響之】【而說(shuō)】【量波】【得泰】【死有】【原了】【口中】【不高】【沒(méi)有】【不是】【如出】【衣袍】【巨大】【那火】【停頓】【雖然】【難度】【通天】【后多】【敏銳】【出現(xiàn)】,

mysql> explain select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9928072
        Extra: Using where
1 row in set (0.00 sec)

可以看到，查詢使用的是全表查詢，讀了所有的數(shù)據(jù)行。

4.2 單字段索引-name

首先在name字段創(chuàng)建一個(gè)單字段索引

mysql>ALTER TABLE `user` ADD INDEX `idx_user_name` (`name`) USING BTREE ;
Query OK, 0 rows affected (1 min 34.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

執(zhí)行SQL

mysql> select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
count(user_id): 682
    avg(age): 34.4296
1 row in set (3.52 sec)

耗時(shí)3.52秒

mysql> explain select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: idx_user_name
          key: idx_user_name
      key_len: 98
          ref: NULL
         rows: 100634
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

使用索引進(jìn)行檢索，讀取的數(shù)據(jù)減少到 10萬(wàn)行。

4.3 單字段索引-mobile

為了測(cè)試方便，先刪除name字段的索引，再創(chuàng)建一個(gè)mobile字段索引

mysql> ALTER TABLE `user` DROP INDEX `idx_user_name`;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql>ALTER TABLE `user` ADD INDEX `idx_user_mobile` (`mobile`) USING BTREE ;
Query OK, 0 rows affected (1 min 27.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

執(zhí)行SQL

mysql> select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
count(user_id): 682
      avg(age): 34.4296
1 row in set (9.93 sec)

耗時(shí)9.93秒

mysql> explain select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: idx_user_mobile
          key: idx_user_mobile
      key_len: 63
          ref: NULL
         rows: 233936
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

盡管我們的SQL語(yǔ)句將mobile字段作為第二個(gè)查詢條件，mysql仍然使用了mobile上的索引進(jìn)行檢索。mobile索引過(guò)濾出來(lái)的數(shù)據(jù)有23萬(wàn)行，比基于name的更多，所以耗時(shí)也就更長(zhǎng)。

4.4 雙字段索引-name & mobile

這次我們將兩個(gè)字段建成一個(gè)聯(lián)合索引。

mysql> ALTER TABLE `user` DROP INDEX `idx_user_mobile`;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE `user` ADD INDEX `idx_user_name_mobile` (`name`, `mobile`) USING BTREE ;
Query OK, 0 rows affected (1 min 54.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

執(zhí)行SQL

mysql> select avg(age) as age_avg from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
age_avg: 34.4296
1 row in set (0.06 sec)

執(zhí)行時(shí)間大大縮短，只需要0.06秒

mysql> explain select avg(age) as age_avg from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: idx_user_name_mobile
          key: idx_user_name_mobile
      key_len: 161
          ref: NULL
         rows: 100764
        Extra: Using index condition
1 row in set (0.00 sec)

讀取的行數(shù)還是10萬(wàn)行，但時(shí)間大大縮短。從這個(gè)時(shí)間，我們應(yīng)該能夠猜出mysql的過(guò)濾數(shù)據(jù)的過(guò)程。mysql執(zhí)行where過(guò)濾時(shí)僅僅通過(guò)索引即可完成，然后根據(jù)索引中的user_id去數(shù)據(jù)頁(yè)面讀取相應(yīng)的age值出來(lái)做平均。

4.5 終極版-覆蓋索引

前面的分析可以看到，為了計(jì)算平均值，mysql還需要讀取行數(shù)據(jù)。如果age字段也在這個(gè)索引中，查詢性能會(huì)進(jìn)一步提升嗎？因?yàn)椴辉僮x行數(shù)據(jù)。

調(diào)整索引

mysql> ALTER TABLE `user` DROP INDEX `idx_user_name_mobile`;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `user` ADD INDEX `idx_user_name_mobile_age` (`name`, `mobile`, `age`) USING BTREE ;
Query OK, 0 rows affected (1 min 55.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

執(zhí)行SQL

mysql> select avg(age) as age_avg from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
age_avg: 34.4296
1 row in set (0.04 sec)

執(zhí)行時(shí)間更短，僅為0.04秒。數(shù)據(jù)量可能還不夠大，同上一個(gè)執(zhí)行的區(qū)別不是太大。

mysql> explain select avg(age) as age_avg from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: idx_user_name_mobile_age
          key: idx_user_name_mobile_age
      key_len: 161
          ref: NULL
         rows: 103688
        Extra: Using where; Using index
1 row in set (0.00 sec)

最重要的變化是Extra信息：Using index condition 變成 Using index。Using index condition 表示使用了索引作為查詢過(guò)濾的條件；Using index表示整個(gè)SQL只使用了索引。

|轉(zhuǎn)載請(qǐng)注明來(lái)源地址：蜘蛛池出租 http://www.wholesalehouseflipping.com/
專注于SEO培訓(xùn)，快速排名黑帽SEO https://www.heimao.wiki

版權(quán)聲明：本文為 “蜘蛛池出租” 原創(chuàng)文章，轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明；

原文鏈接：http://www.wholesalehouseflipping.com/post/17867.html

黑帽SEO學(xué)習(xí)設(shè)置Tag是個(gè)好習(xí)慣

日歷

? 2025年11月 ?
一	二	三	四	五	六	日
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

搜索

控制面板

您好，歡迎到訪網(wǎng)站！
查看權(quán)限

網(wǎng)站分類

作者列表

yupang (104)

站點(diǎn)信息

文章總數(shù):10402
頁(yè)面總數(shù):3
分類總數(shù):7
標(biāo)簽總數(shù):40
評(píng)論總數(shù):709
瀏覽總數(shù):3422313

蜘蛛池出租

湖南seo黑帽白帽的差異:制作mysql大數(shù)據(jù)表驗(yàn)證覆蓋索引_黑帽SEO優(yōu)化

1 數(shù)據(jù)表設(shè)計(jì)

2.1 獲取隨機(jī)整數(shù)

2.2 獲取隨機(jī)字符串

2.3 獲取隨機(jī)手機(jī)號(hào)

2.4 獲取隨機(jī)漢字

2.5 獲取隨機(jī)姓名

2.6 插入隨機(jī)用戶數(shù)據(jù)

3 生成數(shù)據(jù)

4 索引對(duì)查詢性能的影響

4.1 只有主鍵的情況

4.2 單字段索引-name

4.3 單字段索引-mobile

4.4 雙字段索引-name & mobile

4.5 終極版-覆蓋索引

日歷

搜索

控制面板

網(wǎng)站分類

最新留言

標(biāo)簽列表

最近發(fā)表

作者列表

站點(diǎn)信息

友情鏈接

湖南seo黑帽白帽的差異:制作mysql大數(shù)據(jù)表驗(yàn)證覆蓋索引_黑帽SEO優(yōu)化

1 數(shù)據(jù)表設(shè)計(jì)

2.1 獲取隨機(jī)整數(shù)

2.2 獲取隨機(jī)字符串

2.3 獲取隨機(jī)手機(jī)號(hào)

2.4 獲取隨機(jī)漢字

2.5 獲取隨機(jī)姓名

2.6 插入隨機(jī)用戶數(shù)據(jù)

3 生成數(shù)據(jù)

4 索引對(duì)查詢性能的影響

4.1 只有主鍵的情況

4.2 單字段索引-name

4.3 單字段索引-mobile

4.4 雙字段索引-name & mobile

4.5 終極版-覆蓋索引

相關(guān)文章