叶金荣 程序猿都该知道的MySQL秘籍

2020-02-27 56浏览

  • 1.程序猿都该知道的 MySQL秘籍 ‫ݨ‬ᰂឍhttp://imysql.com‫ل‬ռ‫ݩ‬ғimysql_wx 2016.5.14
  • 2.关于我 • ‫ݨ‬ᰂឍ • Oracle MySQL ACE • ࢵ๋ٖ෱ጱMySQLവଠᘏ • From 2006҅http://imysql.com• ՗ԪMySQLፘ‫ى‬ૡ֢10֟ଙ • ඘ᳩMySQL௔ᚆս۸ • ሿӫဳMySQL DBAՈ಍ङِ • ‫ل‬ռ‫ݩ‬ғimysql_wxҁMySQLӾ෈ᗑ҂
  • 3.Agenda • MySQLս۸ᑃᔁ • InnoDB or MyISAMҘ • InnoDBᤒଫᧆெԍሻ • ӞԶս۸݇ᘍ • ᶋَࣳDBAெԍሻঅMySQL • ‫؀‬஑๗இጱ5.7ෛᇙ௔
  • 4.先从一个 成见 开始
  • 5.还死守MyISAM？out了 • ᧛ग़ٟ੝ጱ࣋วӥ҅፥ጱMyISAM੪‫ݳ‬ᭇ‫ހ‬ • რ᩸ғInnoDBᬮฎMyISAM ٚ᧨MySQLਂ‫ؙ‬୚කጱᭌೠ • TAᥡᅩ • MyISAMጱ᧛௔ᚆฎྲInnoDB୩ӧ੝ • MyISAMᔱ୚޾හഝ‫҅୏ړ‬Ӭᔱ୚ํܴᖽٖ҅ਂֵአሲፘ੒ๅṛ • MyISAM‫ݢ‬զፗളᥟፍMYD/MYI෈կ௩॔හഝ҅ፘ੒ๅள • count(*)޾order byපሲ֗҅Ӭcount(*)տᲁᤒ • InnoDBጱinsert޾updateॡளԧ҅੕ᛘ՗ପๅӧӤ • MyISAMํmergeᔄࣳ҅‫ݢ‬զள᭛count(*)
  • 6.InnoDB的好处 • ౯ጱᥡᅩ • य़ग़හӱ‫ۓ‬Ӿ҅95%զӤጱ࣋ว҅᮷‫ݢ‬զ᯻አInnoDB୚ක • InnoDB‫ݢ‬զ಩හഝ̵ᔱ୚̵ํ‫ץ‬දጱහഝනࣁٖਂbufferӾ҅ᘒӬํᛔᭇଫߢ૶ᔱ ୚̵change buffer mergeᒵᒵ҅ԪਫӤๅṛප • InnoDBԞ‫ݢ‬ፗള੕‫ڊ‬ᤒᑮᳵ෈կ‫ࣁ҅ݸ‬ፓຽ๐‫࢏ۓ‬Ӥ੕‫̶ف‬୮ᆐԧ҅ӧᚆፗളࣁᕚ ೩ᨬ҅ᵱᥝᑖ‫॒ے‬ቘ҅֕ଚӧἋᅸ • ဌํᔱ୚෸҅count(*)޾order byᙗਧපሲ֗҅ӧ‫ړ܄‬୚ක̶InnoDBฎԪ‫ۓ‬ᤒ҅ಅզ ‫ق‬ᤒҁ෫WHERE๵կ෸҂count(*)ᏟਫտౌӞԶ҅֕ଚӧտᲁᤒ • InnoDBጱinsert޾updateॡளԧ҅੕ᛘ՗ପๅӧӤ —- টጱ҅ᬯӻᒌ౮ԧᗌᅩҘ • MyISAMํmergeᔄࣳ҅‫ݢ‬զள᭛count(*) —- ‫ݢ‬զ᯻አᤒ‫܄ړ‬ᇙ௔҅ইຎՐՐ ‫ݝ‬ฎԅԧCOUNT(*)҅‫ݢڞ‬զ᯻አredisᒵᒫӣො๶ᦇහ
  • 7.赶紧转投InnoDB怀抱 • ՗MyISAM᫨౮InnoDBጱঅ॒ • ਠෆԪ‫ۓ‬ᇙ௔ඪ೮҅឴஑ๅṛଚ‫ݎ‬TPSҔ • ඪ೮crash recovery̶MyISAMӧᚆᛔۖ‫҅॔ץ‬Ӭᘙ෸ๅ ԋҔ • ԪਫӤ҅‫᧛ݝ‬පሲๅṛ̶ࢩԅInnoDB಩හഝ݊ᔱ୚‫ݶ‬෸න ࣁbufferӾ҅ᘒMyISAM‫ݝڞ‬ᖨਂԧᔱ୚Ҕ • ݇ᘍғ[MySQL FAQ]ᔮ‫ — ڜ‬՗MyISAM᫨‫ک‬InnoDBᵱᥝဳ ఺Ջԍ
  • 8.so，InnoDB怎么玩好？
  • 9.InnoDB的正确玩法 • Ḓ‫҅ض‬ᥝྋᏟᦊᦩInnoDB • चԭB+໅ᕮ຅ጱᘸᵞᔱ୚ᕟᕢᤒҁᔄ֒ORACLEጱIOT༷ஷ҂ • ᤒහഝጱ᭦ᬋਂ‫ؙ‬ᶲଧ‫ݐ‬٬ԭᘸᵞᔱ୚ጱᶲଧ • ἕᦊᭌೠԆᲫ֢ԅᘸᵞᔱ୚҅෫‫ݳ‬ᭇԆᲫ෸҅੪አٖᗝኞ౮ጱ ROW_ID֢ԅᘸᵞᔱ୚ • InnoDBጱᤈᲁฎ‫ࣁے‬ᔱ୚Ӥጱ • ඪ೮4ӻԪ‫ۓ‬ᵍᐶᕆ‫҅ڦ‬ἕᦊጱRRᥴ٬ԧଝ᧛ᳯ᷌
  • 10.InnoDB的正确玩法
  • 11.InnoDB的正确玩法 • InnoDBᤒ᮷ᥝํӞӻԆᲫ҅ӬԆᲫ๋অဌํӱ‫ۓ‬አ᭔҅ӧᥝ‫ץ‬දԆᲫ‫؀‬ • ԆᲫ๋অฎ‫כ‬೮ᶲଧ᭓ी҅ᵋ๢ԆᲫ‫؀‬տ੕ᛘᘸᵞᔱ୚໅᷇ᔺ‫ړ‬᤯҅ᵋ๢I/O ीग़҅හഝᐶව҅௔ᚆӥᴳ • ᝑ෫ᇙྛᵱᥝ҅ᥝ୏‫ސ‬Ԫ‫ۓ‬ᛔۖ൉Ի autocommit=1̶಩य़Ԫ‫ۓ‬ೆ‫ړ‬౮ग़ӻੜ Ԫ‫҅ۓ‬ੜྍள᪒ොୗ‫୏ړ‬൉Ի҅᭿‫ํع‬य़Ԫ‫ۓ‬๚൉Ի੕ᛘᳩ෸ᳵᤈᲁᒵஇ • ဌํᔱ୚ጱๅෛ҅‫ݢ‬ᚆտ੕ᛘ‫ق‬ᤒහഝ᮷ᤩᲁ֘҅޾ᤒᕆᲁᒵ‫ݸݶ‬ຎ • ਧԎ‫ંڜ‬௔෸҅ᳩଶᶼ֌ड़አ੪অ҅ဌ஠ᥝአᇙ‫ڦ‬य़ጱහഝᔄ̶ࣳVARCHAR/ TEXTᒵහഝᔄࣳӾਫᴬਂ‫ؙ‬හഝᳩଶ᩼ੜ᩼অ҅‫ݎڞވ‬ኞᤈფ‫ڊ‬ҁoff-page storage҂෸੒௔ᚆ୽ߥ‫ݢ‬ᚆஉय़ • ӧᥝፗളSELECT *᧛‫قݐ‬᮱‫ݢ҅ڜ‬ᚆտ੕ᛘๅग़ጱI/O᧛
  • 12.一些优化参考
  • 13.联合索引怎么用 • ᘶ‫ݳ‬ᔱ୚k1ҁc1, c2, c3҂ • ӥᶎߺӻSQLӧᚆਠෆአ‫ک‬ෆӻᘶ‫ݳ‬ᔱ୚ጱҘ • WHERE c1 = ? AND c2 IN (?, ?) AND c3 = ? √ • WHERE c1 = ? AND c2 =? ORDER BY c3 √ • WHERE c3 = ? AND c1 = ? AND c2 IN (?, ?) √ • WHERE c1 = ? AND c2 IN (?, ?) ORDER BY c3 x • 5.6զ‫ڹ‬ୌᦓෛी (c1, c3) ᔱ୚҅5.6զ‫ݢݸ‬զአICPᇙ௔
  • 14.JOIN怎么优化 • inner join
  • 15.JOIN怎么优化 • straight join
  • 16.JOIN怎么优化 • INNER JOINฎᛔۖᭌೠJOINᶲଧ҅‫ݢ‬ᚆӧฎ๋ս • LEFT JOIN & STRAIGHT_JOIN ᮷ฎ୩‫ૢګ‬ᬟጱᤒ ֢ԅḝۖᤒ • RIGHT JOIN‫ڞ‬ፘ‫ݍ‬ • ग़ᤒJOINӾ҅ഭଧ‫ڜ‬ইຎӧંԭḝۖᤒ҅‫ڞ‬෫ဩ‫ڥ‬ አᔱ୚ਠ౮ഭଧ
  • 17.硬件、系统、配置选项 优化
  • 18.硬件优化
  • 19.系统优化 • io scheduler • deadline • noop • ࣙ٬ӧᚆcfq
  • 20.系统优化
  • 21.系统优化 • fs • xfs • ext4̵zfsᒵ • ࣙ٬ӧᚆext3
  • 22.系统优化
  • 23.MySQL参数优化 • innodb_buffer_pool_size҅ᕅᇔቘٖਂጱ50% ~ 70% • innodb_data_file_path҅‫ڡ‬ত۸य़ੜᛗ੝1G • 5.6զӤᇇ๜҅ᦡᗝᇿᒈundoᤒᑮᳵ • innodb_log_file_size҅5.5݊զӤ1GզӤ҅5.5զӥୌᦓӧ᩻512M • innodb_flush_log_at_trx_commit҅0=>๋ளහഝ๋ӧਞ‫҅ق‬1=>๋ౌ๋ਞ ‫҅ق‬2=>ರӾ • innodb_max_dirty_pages_pct҅25%~50%ԅ਩ • innodb_io_capacity҅ฦ᭗๢༁ፏ=>1000ૢ‫҅ݦ‬SSD=>10000ૢ‫҅ݦ‬PCIe SSD=>20000զӤ
  • 24.MySQL参数优化 • key_buffer_size҅ᦡᗝ32Mզӥ • sync_binlog҅0=>๋ளහഝ๋ӧਞ‫҅ق‬ᔮᕹᛔ૩٬ਧ‫ڬ‬ෛbinlogጱ᷇ ሲҔ1=>๋ౌ๋ਞ‫ྯ҅ق‬ӻevent‫ڬ‬ෛӞེҔN=>ྯNӻԪ‫ڬۓ‬Ӟེbinlog • long_query_time҅ୌᦓᦡᗝੜԭ0.5ᑁ • open_files_limit & innodb_open_files҅ୌᦓ65535 • max_connections҅ᑱ‫๋ݎ‬य़ᬳളහጱ80%ԅ਩҅ᬦय़਻ฃ੕ᛘ‫ق‬᮱‫ྒܜ‬ • thread_handling = “pool-of-thread”҅‫ސ‬አᕚᑕ࿰ • query_cache_size & query_cache_type҅‫ى‬ᳮ
  • 25.没踩过 坑 的都非正常人
  • 26.有哪些坑 • QUERY CACHE҅ᓌᑍQC • ᕷय़ग़හఘ‫٭‬ӥểᙃ๋҅অ‫ى‬ᳮ • QCᲁฎ‫ق‬ੴᲁ҅ྯེๅෛQCጱٖਂࣘᲁդհṛ҅உ਻ ฃ‫ڊ‬ሿWaiting for query cache lockᇫா • query_cache_size =0 & query_cache_type = 0҅‫ى‬ᳮ • ݇ᘍғhttp://t.cn/RAF4d7zhttp://t.cn/RAF4d7Z
  • 27.ibdata1文件暴增 • ibdata1෈կ᮷ਂ‫ؙ‬ԧՋԍٖ਻Ҙ • Data dictionary • Double write buffer • Insert buffer • Rollback segments • UNDO space • Foreign key constraint system tables
  • 28.ibdata1文件暴增 • ibdata1෈կูीܻࢩ • य़ᰁԪ‫҅ۓ‬Ծኞय़ᰁጱundo log • ํ෯Ԫ‫ۓ‬ᳩ෸ᳵ๚൉Ի҅Ծኞय़ᰁ෯undo log • file i/o௔ᚆ૧҅purgeᬰଶౌ • 32bitᔮᕹӥํbug
  • 29.ibdata1文件暴增 • ibdata1෈կูीᥴ٬ • ‫܋‬ᕆ‫ک‬5.6݊զӤҁ64-bit҂҅᯻አᇿᒈundoᤒᑮᳵ • ी‫ے‬purgeᕚᑕහ innodb_purge_threads • ൉ṛfile i/oᚆ‫ێ‬ • Ԫ‫݊ۓ‬෸൉Ի҅ӧᥝᑌܴ • ἕᦊ಑୏autocommit = 1 • ༄ັ୏‫ݎ‬໛ຝ҅Ꮯᦊautocommit=0ጱࣈො҅Ԫ‫ۓ‬ᕮ๳‫ݸ‬᮷ํ൉Ի ౲ࢧ჻
  • 30.隐式类型转换 • ํӻಋ๢‫ݩ‬Ꮁ‫ڜ‬ • iphone CHAR(11) NOT NULL DEFAULT ’’ • ํӥᶎᬯӻັᧃ • WHERE iphone = 13900000000
  • 31.隐式类型转换 • ெԍᏈ • ද౮WHERE iphone = ’13900000000’ • ౲ iphone ‫ڜ‬ᔄࣳද౮ BIGINT UNSIGNED
  • 32.连接数过高 • too many connectionsጱ॒ቘ • ଉᥢጱ؉ဩғమ‫ې‬ဩ๤ധग़֟ጱᬳള҅‫ے‬य़ᬳളහ • ٌਫଫᧆฎᬯ໏ғᴴ‫ګ‬ᬳളහ҅ᦡਧ max_user_connections • ইຎฎᬯ໏ޫғextra-port • ୌᦓғਧ෸༄ັ҅ଗധౌັᧃ҅᭿‫ع‬ᴥल҅ᛔ౯‫כ‬ಷ
  • 33.非典型DBA怎么玩好 MySQL
  • 34.小规模集群怎么运维 • ॓ղ • ՗mysqldumpҁਁᒧᵞ҂ => xtrabackupҁႰ‫ݳ‬୚ක҂ • ᛔۖ(୑ࣈ)॓ղ̵༄ັ̵௩॔ḵᦤ • ṛ‫ݢ‬አ̵ඳᵑ᫨ᑏ • ‫݌‬๢+keepalivedṛ‫ݢ‬አҁᚏ᤯҂ • ग़๢MHAṛ‫ݢ‬አҁᜓᅩग़҂ • ॓አ୑ࣈ๢಄ҁ౮๜ṛ҂ • ‫ڥ‬አ୊᬴॔‫ګ‬ᇙ௔ᶼᴠӸ᯿᧏඙֢
  • 35.表数据从十万到千万表怎么玩 • 10ӡᤒ҅Ӟᛱᶌᓌ‫ܔ‬ᔱ୚੪ᚆ൥ਧ • 100ӡᤒ҅ᵱᥝ୏তᘍᡤࣁᕚDDLጱᷚᴾԧ҅զ݊ӞԶ᩻ᬦ1ᑁጱSQL • 1000ӡ҅ெԍ؉॓ղ҅Ӥᕚ‫ڹ‬ᶼ‫ض‬؉SQLᥴຉ҅ᶼᴠӸ᯿ෛๅᵙᳯ᷌ • ᵱᥝํᬀۗಋྦྷ҅ፊᥤ᩻ᬦNᑁጱSQL҅ᚆள᭛ᛔ॒ۖቘ҅ଚӬಸᦄ‫ڊ‬๶҅ ‫ݸ‬ᖅ᪙ᬰս۸ • य़හഝᰁӥ҅‫ړ‬ପ‫ړ‬ᤒ๚஠ฎᱷ୨҅‫ݍ‬ᘒ‫ݢ‬ᚆฎᔴᩣ҅ս‫ض‬؉‫ٯ‬ᅾහഝ‫ړ‬ ᐶ౲୭໩ • ‫ړ‬ପ‫ړ‬ᤒ෸ս‫ݶࣁض‬Ӟӻਫֺӥ҅᪜ਫֺӞᛱᵱᥝᶌᨏጱproxy಍ᤈ • pt-query-digest + anemometer
  • 36.大量日志如何存储 • ਂ‫فؙ‬ପս‫ض‬አtokudb୚ක • ܲ‫ݥ‬୭໩੕‫ف‬य़හഝ‫ړ‬ຉଘ‫ݣ‬ • ෭பᤒ‫ݢ‬զೲ෸ᳵྦྷ‫ړ‬ᤒҁ‫܄ړ‬҂ • ਧ๗‫ڠ‬ୌ/‫ڢ‬ᴻ/୭໩‫ړ‬ᤒҁ‫܄ړ‬҂
  • 37.拥抱5.7
  • 38.从5.6升级方案 • ᭦ᬋ‫܋‬ᕆොໜ • mysqldump੕‫ڊ‬ • ෛୌਫֺ҅੕‫҅ف‬௩॔හഝ • ᇔቘ‫܋‬ᕆොໜ • fast_shutdown=0 • ೩ᨬᇔቘ෈կ౲xtrabackup॓ղ • ෛୌਫֺ҅௩॔හഝҁredo̵undo log໒ୗӧӞ໏҅ᵱᥝ᯿ෛ‫ڡ‬ত۸҂ • አmysql_upgrade‫܋‬ᕆP_S̵I_S̵mysqlପ • ӧവគ՗5.5‫ک‬5.7ፗള‫܋‬ᕆ
  • 39.5.7重大变化 • ᐬአጱ‫ۑ‬ᚆ • innodb monitor table‫ۑ‬ᚆ • አinnodb_status_output̵ innodb_status_output_locks୏‫ى‬ • old-password໒ୗ • ӧਞ‫҅ق‬᩷ᔲ‫܋‬ᕆঅԧ • skip-innodbᭌᶱҁӧᚆᐬአInnoDBԧ҂
  • 40.值得关注的MySQL 5.7 新特性 • ਞ‫ق‬௔൉‫҅܋‬୩۸ᨴಁᓕቘ݊ੂᎱᒽኼ • ‫ڡ‬তਞᤰἕᦊտᦡᗝᵋ๢ੂᎱҁḒེጭ‫ݸف‬ᵱ‫ץ‬ද҂ • ‫ڡ‬ত۸ᵋ๢ੂᎱ‫ਂכ‬ԭerror logӾ • ‫ํݝ‬root@localhostᨴ‫҅ݩ‬ٚ෫‫ݷ܇‬ᨴ‫ݩ‬ • ෛᨴ‫ڠݩ‬ୌ‫ੂݸ‬Ꮁἕᦊํᬦ๗෸ᳵ • 5.7.10‫ڹ‬ἕᦊ360ॠ҅5.7.10‫ݸ‬ἕᦊ0ॠҁӧᬦ๗҂ • ඪ೮SSL/TLS᱾ളොୗ
  • 41.值得关注的MySQL 5.7 新特性 • ຽ‫ٵ‬۸൉‫҅܋‬ӧّٚ਻ӞԶ“ӧᥢ᝜”አဩ • ἕᦊ‫ސ‬አSTRICT_TRANS_TABLES̵ NO_ZERO_IN_DATE̵ ERROR_FOR_DIVISION_BY_ZERO ᒵ‫پ‬ᐿᥢ‫ڞ‬ • ֺইғ‫ݻ‬CHAR(20)ٟ‫ف‬30ӻਁᒧտಸᲙ҅ᘒӧՐ ฎ‫ᦄޞ‬ • ӧٚᤩᧀየ“ॡฬᚆ”ԧ
  • 42.值得关注的MySQL 5.7 新特性 • ‫ۑ‬ᚆ௔൉‫ֵ҅܋‬አ᩸๶ๅ‫ڥ׎‬ • Online DDLी୩ • InnoDB୚ක‫ࣁץ‬ᕚ᧣ෆVARCHARᳩଶ • ी‫ے‬/‫ץ‬දᶋԆᲫ‫ڜ‬᎖ᳵਠ౮ • ‫ץ‬දᔱ୚‫ݷ‬ҁᶋԆᲫ҂ • InnoDB buffer poolᓕቘी୩ • ࣁᕚۖா᧣ෆ • ੕‫ڊ‬੕‫ف‬ी୩
  • 43.值得关注的MySQL 5.7 新特性 • ௔ᚆय़ଏ൉‫܋‬ • InnoDB੒Fusion-io Non-Volatile Memory (NVM)ܻኞඪ೮ • InnoDBᔱ୚‫ڠ‬ୌ̵᯿ୌಢᰁ‫ے‬᫹҅൉ṛපሲ • Optimizer Hints • ᯿ٟԧय़᮱‫ړ‬ᥴຉ࢏҅ս۸࢏޾౮๜ཛྷࣳ • InnoDB‫᧛ݝ‬Ԫ‫ۓ‬௔ᚆ൉‫܋‬ • දᬰ InnoDB ጱ‫ݢ‬ಘ઀௔޾ԁ෸ᤒጱ௔ᚆ҅՗ᘒਫሿๅளጱᗑᕶ޾ य़හഝ‫ے‬᫹ᒵ඙֢
  • 44.值得关注的MySQL 5.7 新特性 • ॔‫ۑګ‬ᚆ൉‫܋‬ • ଚᤈ॔‫ګ‬൉‫܋‬ • ग़რ॔‫ګ‬ • ࣁᕚ‫ץ‬දreplication filter • ࣁᕚ‫ސ‬አ GTIDs҅޾ी୩ጱ܎‫ګ॔ྍݶ‬ • ඪ೮Group ReplicationҁਫḵӾ҂
  • 45.值得关注的MySQL 5.7 新特性 • ෛᇙ௔ • MySQL Router • ඪ೮Generated Columns • ඪ೮JSON • ी‫ے‬sys schema • ඪ೮ग़ӻtrigger • ඪ೮GIS