MySQL 性能调优的十大技巧.pdf

1、MySQL SummitConfiguration,best practices and tracking the ugly ducklingMike FrankProduct Management Director MySQLTop 10 tips for MySQL Performance TuningUrvashi OswalPrincipal Member Technical StaffMySQLAgendaAgendaImporting the data1.Use MySQL Shell Utility2.Speeding up importSchema Design3.Primar

2、y Keys4.Indexes5.Parallel Index CreationConfiguration6.The right config for the workloadMemory7.Consumption8.Linux memory allocatorAll about queries9.Workload10.Ugly ducklingUsing Machine Learning to Solve#4 Autopilot IndexingTop Ten TipsCopyright 2023,Oracle and/or its affiliates MySQL Summit Copyr

3、ight 2024,Oracle and/or its affiliates4Importing Dataat speed of light!Copyright 2023,Oracle and/or its affiliates6Importing DataFor logical dumps,MySQL Shell Dump&Load Utility should be preferred over the old and single threaded mysqldump!MySQL Shell Dump&Load can dump a full instance,one or multip

4、le schemas or tables.You can also add a where clause.This tool dumps and load the data in parallel!The data can be stored on filesystem,OCI Object Storage,S3 and Azure Blob Storage.JS util.dumpInstance(/opt/dump/,threads:32)MySQL Summit Copyright 2024,Oracle and/or its affiliates7Importing Data(2)Th

5、e dump can be imported into MySQL using util.loadDump().loadDump()is the method used to load dumps created by:util.dumpInstance()util.dumpSchemas()util.dumpTables()JS util.loadDump(/opt/dump/,threads:32)MySQL Summit Copyright 2024,Oracle and/or its affiliates8MySQL Summit Copyright 2024,Oracle and/o

6、r its affiliates9Importing Data High SpeedWe can speed up the process even more!During an initial load,&the durability is not a problemnot a problem,if there is a crash,the process can be restarted.Therefore,if the durability is not important,we can reduce it to speed up the loading even more.We can