mysql
DESCRIPTION
TRANSCRIPT
![Page 1: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/1.jpg)
2012
Chris Henry@chrishnry
MySQL Essentials
Sunday, January 20, 13
![Page 2: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/2.jpg)
hello.
Sunday, January 20, 13
![Page 3: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/3.jpg)
MySQL @ Behance
Powers Be.net17 Dell R710s120GBXFS Filesystem
Percona 5.5
Sunday, January 20, 13
![Page 4: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/4.jpg)
What Version of MySQL?
- Does it actually matter? Fuck yes.
mysql> SELECT VERSION()
Welcome to the MySQL monitor.Your MySQL connection id is 54248515Server version: 5.5.24-55-log Percona Server (GPL), Release rel26.0, Revision 256
Sunday, January 20, 13
![Page 5: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/5.jpg)
Percona
• Why?http://www.percona.com/
• Percona Toolkithttp://www.percona.com/software/percona-toolkit
Sunday, January 20, 13
![Page 6: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/6.jpg)
MySQL Engines
• Pluggable architecture.• INNODB vs MyISAM• Transactions• Locking• Fulltext
• Should be InnoDB for most things
http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html
Sunday, January 20, 13
![Page 7: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/7.jpg)
Configuration
• table_open_cache• innodb_buffer_pool • max_connections• key_buffer_size
Sunday, January 20, 13
![Page 8: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/8.jpg)
Slow Log
my.cnf
slow-query-log-file = /var/lib/mysqllogs/slow-loglong-query-time = 2
Sunday, January 20, 13
![Page 9: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/9.jpg)
Tuning - Easy Wins
• skip-name-resolve• Linux Swappiness
Sunday, January 20, 13
![Page 10: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/10.jpg)
Slow Log Output
# Query_time: 0.015029 Lock_time: 0.000044 Rows_sent: 481 Rows_examined: 5080 Rows_affected: 0 Rows_read: 481# Bytes_sent: 4888 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 126992# Filesort: Yes Filesort_on_disk: No Merge_passes: 0# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000SELECT DISTINCT proj.id FROM projects proj INNER JOIN project_summary ps ON proj.id=ps.proj_id WHERE proj.published = 1 AND proj.privacy2 = 1 ORDER BY ps.apps DESC LIMIT 0, 481;
Sunday, January 20, 13
![Page 11: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/11.jpg)
EXPLAIN Output
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: proj type: refpossible_keys: PRIMARY,published,privacy2, key: privacy2 key_len: 2 ref: const,const rows: 1 filtered: 100.00 Extra: Using index; Using temporary; Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: ps type: refpossible_keys: proj_id key: proj_id key_len: 4 ref: be_net2.proj.id rows: 1 filtered: 100.00 Extra: Distinct2 rows in set, 1 warning (0.00 sec)
Sunday, January 20, 13
![Page 12: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/12.jpg)
Table Design
• Data Types
• Relations
• Normal Form
Sunday, January 20, 13
![Page 13: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/13.jpg)
Data Types
• Right tool for the job
• Numeric
• String
• Temporal
• Spatial
Sunday, January 20, 13
![Page 14: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/14.jpg)
Integer Data Types
• How big?
• Signed or unsigned?
• What the hell does that number in parens mean?
• Integer types are great for storing things like type attributes.
Sunday, January 20, 13
![Page 15: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/15.jpg)
Integer Table
CREATE TABLE IF NOT EXISTS `integers` ( `tiny` tinyint(4) NOT NULL, `tiny_zerofill` tinyint(10) unsigned zerofill NOT NULL, `small` smallint(6) NOT NULL, `medium` mediumint(9) NOT NULL, `int` int(11) NOT NULL, `int_unsigned` int(10) unsigned NOT NULL, `big` bigint(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Sunday, January 20, 13
![Page 16: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/16.jpg)
Decimals
• Fixed Point
• DECIMAL (5,2)
Sunday, January 20, 13
![Page 17: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/17.jpg)
Text Datatypes
• CHAR (0 to 255)
• VARCHAR (0 to 65,535) + length
Sunday, January 20, 13
![Page 18: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/18.jpg)
Bigger Text Datatypes
• TINYTEXT• TEXT• MEDIUMTEXT • LONGTEXT
http://dev.mysql.com/doc/refman/5.5/en/string-type-overview.html
Sunday, January 20, 13
![Page 19: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/19.jpg)
Character Sets + Collation
• A Character Set is a set of symbols and encodings.
• Collation is the set of rules for comparing characters. • utf8 / utf8_unicode_ci is best bet for most Western languages.
Sunday, January 20, 13
![Page 20: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/20.jpg)
Temporal
• DATE• TIME• DATETIME• TIMESTAMP
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html
Sunday, January 20, 13
![Page 21: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/21.jpg)
Table Design
• Primary Key
• Compound Keys
• Indexes
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html
Sunday, January 20, 13
![Page 22: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/22.jpg)
Querying
• Sakila Database
• DVD Rental Store
http://dev.mysql.com/doc/sakila/en/index.html
Sunday, January 20, 13
![Page 23: Mysql](https://reader037.vdocuments.us/reader037/viewer/2022110115/54b782834a79591f6f8b45c0/html5/thumbnails/23.jpg)
That’s it!
Thank you all for coming!
Feedback is welcome!
Sunday, January 20, 13