Hacking MySQL: Breaking, Optimizing, and Securing MySQL for Your Use Case

Table of Contents....5 About the Author....12 Acknowledgments....13 Preface....14 Foreword....18 Part I: The Basics of MySQL....19 Chapter 1: The World of MySQL....20 The History of MySQL....20 The Architecture of MySQL....21 Basic Use Cases and Initial Considerations....23 Storage Engines....24 Other Storage Engines....25 Summary....26 Chapter 2: Individual Storage Engines....27 The Modern King of Storage Engines....27 InnoDB in MySQL and MariaDB....27 Storage Engines in Percona Server....36 Percona MyRocks and TokuDB....37 MyRocks....37 TokuDB....39 The Primary Contestant of InnoDB....40 The Early Days of MyISAM....40 MyISAM in the Present Day....41 Storage Engine Use Cases....42 The MEMORY and TempTable Storage Engines....42 The CSV Storage Engine....43 The ARCHIVE and BLACKHOLE Storage Engines....44 The MERGE Storage Engine....45 The Storage Engine for High Availability....46 Installing and Using NDB....47 The FEDERATED and EXAMPLE Storage Engines....50 Storage Engines Exclusive to MariaDB....51 Summary....53 Part II: Breaking MySQL....54 Chapter 3: What Breaks MySQL?....55 MySQL Use Cases....55 Problematic Use Cases....56 Performance Hiccups....57 Availability Issues....59 Security Problems....60 Understanding Your Data....61 Choosing the Proper Schema and Data Types....62 Character Sets and Collations....65 Your Architecture Is a Mess....67 Communicating with MySQL Through Software....69 Top Causes of Slow Query Performance....70 Summary....73 Chapter 4: How You Broke Your Queries....74 The Good, Bad, and the Ugly: Understanding Queries in MySQL....74 How You Broke Queries in MySQL....75 Types of Queries in MySQL....75 Factors Breaking DML Queries in MySQL....76 Factors Breaking DDL Queries in MySQL....78 Factors Breaking DCL and TCL Queries in MySQL....81 Why Are Queries Slow?....81 Devising the Perfect Schema Design....87 Understanding Data Types....90 Understanding Character Sets and Collations....93 Understanding Indexes....97 Understanding Partitions....99 Partitions and Big Data....102 NULL Values and Pruning in Partitions....103 Things to Avoid When Optimizing Queries in MySQL....105 Dont Blindly Trust Documentation....106 Summary....108 Chapter 5: Understanding Query Components....109 SQL Queries and Stored Procedures....109 Parsers and Optimizers....111 Queries and Error Messages....115 Common MySQL Error Codes....116 Factors Disliked by Your Queries....118 Isolate Your Columns!....119 Get Rid of Duplicate Indexes....119 Use EXISTS Instead of IN....120 Make Use of Stored Procedures and Triggers....120 SHOW STATUS and EXPLAIN....122 Summary....126 Chapter 6: Understanding Your Server....127 Efficiently Using Server Resources....127 Understanding and Simulating Errors....129 Server Components and Their Interaction with MySQL....132 Coding for MySQL Performance and Security....136 What Not to Do....140 Summary....141 Part III: Optimizing MySQL....143 Chapter 7: Optimizing Your Server for MySQL....144 Why Optimize Your Server for MySQL?....144 Common Webserver Issues Affecting MySQL....147 What Limits the Performance of MySQL?....149 Choosing Servers and Hard Drives....150 Configuring MySQL Parameters....151 Configuring MySQL IO for Your Operating System....156 Testing Your Hardware....158 Taking Advantage of ACID Properties....161 Summary....164 Chapter 8: Optimizing Storage Engines, Schemas, and Data Types....165 Why Optimize Storage Engines and Data Types?....165 Optimizing Storage Engines....166 Data Types in MySQL....169 String-Based Data Types....170 Numeric Data Types....170 Date, Time, Spatial, and JSON Data Types....171 Storage Requirements for Data Types....172 Choosing the Right Data Type....174 Optimizing Schemas and Data Types for Big Data....175 Summary....177 Chapter 9: Optimizing Queries....178 Why Optimize SQL Queries?....178 Optimizing Specific Types of Queries....180 The Query Cache....180 Optimizing INSERT Queries....182 Optimizing SELECT Queries....186 Optimizing Indexes and Constraints....191 Optimizing UPDATE Queries....196 Optimizing DELETE Queries....200 Optimizing Queries for Big Data, Avoiding Deadlocks, and Other Query Optimization Tips....201 Summary....203 Chapter 10: Optimizing MySQL for Big Data....204 Can MySQL Deal with Big Data?....205 MariaDB and Big Data: Operations with Big Data Sets....206 Inserting Big Data Into MariaDB....207 Reading Big Data with MariaDB....209 Updating Big Data in MariaDB....211 Deleting Big Data From MariaDB....212 Storage Engines and Big Data....213 ACID and Big Data....214 Big Data Pitfalls and Known Issues....215 Summary....217 Chapter 11: Indexing MySQL....218 Why Index? Indexes Available in MariaDB....218 What and When to Index?....222 Indexing Myths, Misconceptions, and Fragmentation Issues....225 Your Hardware, Database, and Indexes....227 Types of Indexes....229 B-Tree Indexes....230 Covering Indexes....237 Multicolumn (Composite) Indexes....241 Prefix Indexes....244 Spatial (R-Tree), Hash, and Clustered Indexes....245 Devising the Perfect Index Design....247 Indexing for Performance and Big Data....251 Summary....256 Chapter 12: Optimizing Partitions....257 Why Partition Data?....257 When to Partition Data?....258 Internals of Database Partitioning....260 Types of Partitioning in MySQL....262 Partitioning Tips: Subpartitioning, Limitations, NULL Values, and More....269 Summary....276 Chapter 13: Optimizing Backups and Recovery....277 Why, When, and How to Backup MariaDB?....277 Backup Types and Tools....280 Backup Compression and Security....286 Backing Up Big Data Sets....288 Recovering MariaDB....291 Recovering Big Data....292 Backup and Recovery Pitfalls....293 Pitfalls for Big Data....294 Summary....297 Chapter 14: Optimizing Replication....298 Understanding Replication....298 Configuring and Implementing Replication....299 Types of Replication....302 Replication Notes and Tips....305 Securing Replication....306 Summary....308 Chapter 15: Optimizing for Security....309 Understanding Security in MariaDB....309 Securing MariaDB upon Installation....313 General Security Measures....315 Summary....316 Part IV: Securing MySQL....317 Chapter 16: The World of Security in MySQL....318 General Security Guidelines and Measures Revisited....318 Access Control....321 User Security....325 MariaDB Components and Plugins That Keep Data Safe....327 Firewalling MariaDB....329 Summary....330 Chapter 17: Securing Your Database Instance....331 Security Guidelines for Specific Use Cases and Defense in Depth....331 Account Categories and Reserved Accounts....334 Password Management and Account Locking....335 SQL Injection, Input Sanitization, and MariaDB....338 Corner Cases of SQL Injection....341 Other Attacks Targeting Your Database....341 Summary....343 Chapter 18: Security and Big Data....344 Security, Big Data, and Code in the Initial Phases of Your SDLC....347 Data, Script Kiddies & Co.....349 What Happens If?....351 Summary....355 Appendix: Things You Wish You Knew, but Dont....357 Schrdingers Tables....357 Having Fun with ibdata1....358 Having Fun with Indexes....359 Query That Breaks MySQL 5.7....361 Reliably Using MyISAM....362 Building APIs and Interacting with Big Data....363 Preparing for the Future....365 Summary....365 Index....367