Uploaded byMYXPLAIN
20,239 views
MySQL Indexing - Best practices for MySQL 5.6
This document provides an overview of MySQL indexing best practices. It discusses the types of indexes in MySQL, how indexes work, and how to optimize queries through proper index selection and configuration. The presentation emphasizes understanding how MySQL utilizes indexes to speed up queries through techniques like lookups, sorting, avoiding full table scans, and join optimizations. It also covers new capabilities in MySQL 5.6 like index condition pushdown that provide more flexible index usage.
More Related Content
What's hot
Viewers also liked
Similar to MySQL Indexing - Best practices for MySQL 5.6
![Introduction into MySQL Query Tuning for Dev[Op]s](/image.pl?url=https%3a%2f%2fcdn.slidesharecdn.com%2fss_thumbnails%2fqtdevops-210717011329-thumbnail.jpg%3fwidth%3d640%26amp%3bheight%3d640%26amp%3bfit%3dbounds&f=jpg&w=240)
More from MYXPLAIN
![Vibe Coding vs. Spec-Driven Development [Free Meetup]](/image.pl?url=https%3a%2f%2fcdn.slidesharecdn.com%2fss_thumbnails%2fvibecodingvsspecdrivendevelopment-251209105622-43f455e7-thumbnail.jpg%3fwidth%3d640%26amp%3bheight%3d640%26amp%3bfit%3dbounds&f=jpg&w=240)
MySQL Indexing - Best practices for MySQL 5.6
- 1.MySQL IndexingBest Practicesfor MySQL 5.6Peter ZaitsevCEO, PerconaMySQL ConnectSep 22, 2013San Francisco,CA
- 2.For those whoDoes not Know Us…• Percona – Helping Businesses to be Successfulwith MySQL– Support, Consulting, RemoteDBA, Training• Creators of Open Source Software for MySQL– Percona Server, Percona XtraBackup, PerconaToolkit, Percona XtraDB Cluster• MySQL Ecosystem Educators– MySQLPerformanceBlog, Books, Webinars, Meetups, Conferenceswww.percona.com
- 3.You’ve Made aGreat Choice !• Understanding indexing is crucial both forDevelopers and DBAs• Poor index choices are responsible for largeportion of production problems• Indexing is not a rocket sciencewww.percona.com
- 4.MySQL Indexing: Agenda•Understanding Indexing• Setting up best indexes for your applications• Working around common MySQL limitationswww.percona.com
- 5.Indexing in theNutshell• What are indexes for ?– Speed up access in the database– Help to enforce constraints (UNIQUE, FOREIGNKEY)– Queries can be ran without any indexes• But it can take a really long timewww.percona.com
- 6.Types of Indexesyou might heard about• BTREE Indexes– Majority of indexes you deal in MySQL is this type• RTREE Indexes– MyISAM only, for GIS• HASH Indexes– MEMORY, NDB• FULLTEXT Indexes– MyISAM, Innodb starting 5.6www.percona.com
- 7.Family of BTREElike Indexes• A lot of different implementations– Share same properties in what operations they canspeed up– Memory vs Disk is life changer• B+ Trees are typically used for Disk storage– Data stored in leaf nodeswww.percona.com
- 8.B+Tree ExampleBranch/Root NodeLessthan 3Data PointersLeaf Nodewww.percona.com
- 9.Indexes in MyISAMvs Innodb• In MyISAM data pointers point to physicaloffset in the data file– All indexes are essentially equivalent• In Innodb– PRIMARY KEY (Explicit or Implicit) - stores data inthe leaf pages of the index, not pointer– Secondary Indexes – store primary key as datapointerwww.percona.com
- 10.What Operations canBTREE Index do ?••••Find all rows with KEY=5 (point lookup)Find all rows with KEY>5 (open range)Find all rows with 5<KEY<10 (closed range)NOT find all rows with last digit of the KEY isZero– This can’t be defined as a “range” operationwww.percona.com
- 11.String Indexes• Thereis no difference… really– Sort order is defined for strings (collation)• “AAAA” < “AAAB”• Prefix LIKE is a special type of Range– LIKE “ABC%” means• “ABC*LOWEST+”<KEY<“ABC*HIGHEST+”– LIKE “%ABC” can’t be optimized by use of theindexwww.percona.com
- 12.Multiple Column Indexes•Sort Order is defined, comparing leadingcolumn, then second etc– KEY(col1,col2,col3)– (1,2,3) < (1,3,1)• It is still one BTREE Index; not a separate BTREEindex for each levelwww.percona.com
- 13.Overhead of TheIndexing• Indexes are costly; Do not add more than youneed– In most cases extending index is better thanadding new one• Writes - Updating indexes is often major costof database writes• Reads - Wasted space on disk and in memory;additional overhead during query optimizationwww.percona.com
- 14.Impact on Costof Indexing• Long PRIMARY KEY for Innodb– Make all Secondary keys longer and slower• “Random” PRIMARY KEY for Innodb– Insertion causes a lot of page splits• Longer indexes are generally slower• Index with insertion in random order– SHA1(‘password’)• Low selectivity index cheap for insert– Index on gender• Correlated indexes are less expensive– insert_time is correlated with auto_increment idwww.percona.com
- 15.Indexing Innodb Tables•Data is clustered by Primary Key– Pick PRIMARY KEY what suites you best– For comments – (POST_ID,COMMENT_ID) can begood PRIMARY KEY storing all comments for singlepost close together• Alternatively “pack” to single BIGINT• PRIMARY KEY is implicitly appended to all indexes– KEY (A) is really KEY (A,ID) internally– Useful for sorting, Covering Index.www.percona.com
- 16.How MySQL UsesIndexes••••Data LookupsSortingAvoiding reading “data”Special Optimizationswww.percona.com
- 17.Using Indexes forData Lookups• SELECT * FROM EMPLOYEES WHERELAST_NAME=“Smith”– The classical use of index on (LAST_NAME)• Can use Multiple column indexes– SELECT * FROM EMPLOYEES WHERELAST_NAME=“Smith” AND DEPT=“Accounting”– Will use index on (DEPT,LAST_NAME)www.percona.com
- 18.It Gets TrickyWith Multiple Columns• Index (A,B,C) - order of columns matters• Will use Index for lookup (all listed keyparts)––––A>5A=5 AND B>6A=5 AND B=6 AND C=7A=5 AND B IN (2,3) AND C>5• Will NOT use Index– B>5 – Leading column is not referenced– B=6 AND C=7 - Leading column is not referenced• Will use Part of the index– A>5 AND B=2 - range on first column; only use this key part– A=5 AND B>6 AND C=2 - range on second column, use 2 partswww.percona.com
- 19.The First Ruleof MySQL Optimizer• MySQL will stop using key parts in multi partindex as soon as it met the real range (<,>,BETWEEN), it however is able to continueusing key parts further to the right if IN(…)range is usedwww.percona.com
- 20.Using Index forSorting• SELECT * FROM PLAYERS ORDER BY SCOREDESC LIMIT 10– Will use index on SCORE column– Without index MySQL will do “filesort” (externalsort) which is very expensive• Often Combined with using Index for lookup– SELECT * FROM PLAYERS WHERE COUNTRY=“US”ORDER BY SCORE DESC LIMIT 10• Best served by Index on (COUNTRY,SCORE)www.percona.com
- 21.Multi Column indexesfor efficient sorting• It becomes even more restricted!• KEY(A,B)• Will use Index for Sorting––––ORDER BY A- sorting by leading columnA=5 ORDER BY B - EQ filtering by 1st and sorting by 2ndORDER BY A DESC, B DESC - Sorting by 2 columns in same orderA>5 ORDER BY A - Range on the column, sorting on the same• Will NOT use Index for Sorting––––ORDER BY B - Sorting by second column in the indexA>5 ORDER BY B – Range on first column, sorting by secondA IN(1,2) ORDER BY B - In-Range on first columnORDER BY A ASC, B DESC - Sorting in the different orderwww.percona.com
- 22.MySQL Using Indexfor Sorting Rules• You can’t sort in different order by 2 columns• You can only have Equality comparison (=) forcolumns which are not part of ORDER BY– Not even IN() works in this casewww.percona.com
- 23.Avoiding Reading Thedata• “Covering Index”– Applies to index use for specific query, not type ofindex.• Reading Index ONLY and not accessing the “data”• SELECT STATUS FROM ORDERS WHERECUSTOMER_ID=123– KEY(CUSTOMER_ID,STATUS)• Index is typically smaller than data• Access is a lot more sequential– Access through data pointers is often quite “random”www.percona.com
- 24.Min/Max Optimizations• Indexhelp MIN()/MAX() aggregate functions– But only these• SELECT MAX(ID) FROM TBL;• SELECT MAX(SALARY) FROM EMPLOYEEGROUP BY DEPT_ID– Will benefit from (DEPT_ID,SALARY) index– “Using index for group-by”www.percona.com
- 25.Indexes and Joins•MySQL Performs Joins as “Nested Loops”– SELECT * FROM POSTS,COMMENTS WHEREAUTHOR=“Peter” AND COMMENTS.POST_ID=POSTS.ID• Scan table POSTS finding all posts which have Peter as an Author• For every such post go to COMMENTS table to fetch all comments• Very important to have all JOINs Indexed• Index is only needed on table which is being looked up– The index on POSTS.ID is not needed for this queryperformance• Re-Design JOIN queries which can’t be well indexedwww.percona.com
- 26.Using Multiple Indexesfor the table• MySQL Can use More than one index– “Index Merge”• SELECT * FROM TBL WHERE A=5 AND B=6– Can often use Indexes on (A) and (B) separately– Index on (A,B) is much better• SELECT * FROM TBL WHERE A=5 OR B=6– 2 separate indexes is as good as it gets– Index (A,B) can’t be used for this querywww.percona.com
- 27.Prefix Indexes• Youcan build Index on the leftmost prefix ofthe column– ALTER TABLE TITLE ADD KEY(TITLE(20));– Needed to index BLOB/TEXT columns– Can be significantly smaller– Can’t be used as covering index– Choosing prefix length becomes the questionwww.percona.com
- 28.Choosing Prefix Length•Prefix should be “Selective enough”– Check number of distinct prefixes vs number oftotal distinct valuesmysql> select count(distinct(title))total, count(distinct(left(title,10)))p10, count(distinct(left(title,20))) p20 from title;+--------+--------+--------+| total | p10| p20|+--------+--------+--------+| 998335 | 624949 | 960894 |+--------+--------+--------+1 row in set (44.19 sec)www.percona.com
- 29.Choosing Prefix Length•Check for Outliers– Ensure there are not too many rows sharing thesame prefixMost common Titlesmysql> select count(*) cnt, title tlfrom title group by tl order by cnt desclimit 3;+-----+-----------------+| cnt | tl|+-----+-----------------+| 136 | The Wedding|| 129 | Lost and Found || 112 | Horror Marathon |+-----+-----------------+3 rows in set (27.49 sec)Most Common Title Prefixesmysql> select count(*) cnt, left(title,20) tlfrom title group by tl order by cnt desclimit 3;+-----+----------------------+| cnt | tl|+-----+----------------------+| 184 | Wetten, dass..? aus || 136 | The Wedding|| 129 | Lost and Found|+-----+----------------------+3 rows in set (33.23 sec)www.percona.com
- 30.What is newwith MySQL 5.6 ?• Many Optimizer improvements– Most of them will make your queries betterautomatically– join_buffer_size variable has whole new meaning• Values if 32MB+ can make sense• Focus on Index Design Practices for thispresentation– Most important one: ICP (Index ConditionPushdown)www.percona.com
- 31.Understanding ICP• Pushwhere clause “Conditions” for Storageengine to filter– Think name like “%ill%” (will not convert to range)• “Much more flexible covering Index”– Plus filtering done on the engine level – efficient• Before MySQL 5.5– All or none. All is resolved through the index or“row” is read if within rangewww.percona.com
- 32.ICP Examples• SELECTA … WHERE B=2 AND C LIKE “%ill%’– MySQL 5.5 and below• Index (B) – traditional. Using index for range only• Index (B,C,A) - covering. All involved columns included– MySQL 5.6• Index (B,C)– Range access by B; Filter clause on C only read full row if match• More cases– SELECT * …– WHERE A=5 and C=6 ; Index (A,B,C)• Will scan all index entries with A=5 not all rowswww.percona.com
- 33.How MySQL Pickswhich Index to Use ?• Performs dynamic picking for every queryexecution– The constants in query texts matter a lot• Estimates number of rows it needs to accessfor given index by doing “dive” in the table• Uses “Cardinality” statistics if impossible– This is what ANALYZE TABLE updateswww.percona.com
- 34.More on Pickingthe Index• Not Just minimizing number of scanned rows• Lots of other heuristics and hacks––––PRIMARY Key is special for InnodbCovering Index benefitsFull table scan is faster, all being equalCan we also use index for Sorting• Things to know– Verify plan MySQL is actually using– Note it can change dynamically based on constantsand datawww.percona.com
- 35.Use EXPLAIN• EXPLAINis a great tool to see how MySQLplans to execute the query– http://dev.mysql.com/doc/refman/5.6/en/usingexplain.html– Remember real execution might be differentmysql> explain select max(season_nr) from title group by production_year;+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra|+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+| 1 | SIMPLE| title | range | NULL| production_year | 5| NULL | 201 | Using index for group-by |+----+-------------+-------+-------+---------------+-----------------+---------+------+------+--------------------------+1 row in set (0.01 sec)www.percona.com
- 36.Indexing Strategy• Buildindexes for set of your performance criticalqueries– Look at them together not just one by one• Best if all WHERE clause and JOIN clauses areusing indexes for lookups– At least most selective parts are• Generally extend index if you can, instead ofcreating new indexes• Validate performance impact as you’re doingchangeswww.percona.com
- 37.Indexing Strategy Example•Build Index order which benefits more queries– SELECT * FROM TBL WHERE A=5 AND B=6– SELECT * FROM TBL WHERE A>5 AND B=6– KEY (B,A) Is better for such query mix• All being equal put more selective key part first• Do not add indexes for non performancecritical queries– Many indexes slow system downwww.percona.com
- 38.Trick #1: EnumeratingRanges• KEY (A,B)• SELECT * FROM TBL WHERE A BETWEEN 2AND 4 AND B=5– Will only use first key part of the index• SELECT * FROM TBL WHERE A IN (2,3,4) ANDB=5– Will use both key partswww.percona.com
- 39.Trick #2: AddingFake Filter• KEY (GENDER,CITY)• SELECT * FROM PEOPLE WHERE CITY=“NEWYORK”– Will not be able to use the index at all• SELECT * FROM PEOPLE WHERE GENDER IN(“M”,”F”) AND CITY=“NEW YORK”– Will be able to use the index• The trick works best with low selectivity columns.– Gender, Status, Boolean Types etcwww.percona.com
- 40.Trick #3: UnionizingFilesort• KEY(A,B)• SELECT * FROM TBL WHERE A IN (1,2) ORDER BYB LIMIT 5;– Will not be able to use index for SORTING• (SELECT * FROM TBL WHERE A=1 ORDER BY BLIMIT 5) UNION ALL (SELECT * FROM TBL WHEREA=2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5;– Will use the index for Sorting. “filesort” will be neededonly to sort over 10 rows.www.percona.com
- 41.Thank You !•pz@percona.com• http://www.percona.com• @percona at Twitter• http://www.facebook.com/Perconawww.percona.com