PHPTeK 2023 - How MySQL Indexes Work

Indexing queries is simple engineering but many get confused by what to index, when to index, and why sometimes adding an index really s-l-o-w-s processing down. This session covers why you really need to designate your own primary key as the default picked by the server will not be helpful, how to see which index the query optimizer chooses, why the old rules about multi-column index are gone, and how optimizer hints can get you back on track. There is a lot of mythology about indexing MySQL tables and you will see how it is simple engineering to speed up your queries.

Transcript

1. Databases run better with Percona
2. MySQL Indexes How They Really Work
3. © Copyright 2023 Percona® LLC. All rights reserved Who Am I? Dave Stokes Technology Evangelist at Percona Author of MySQL & JSON - A Practical Programming Guide available on Amazon Former MySQL AB, Sun Microsystems, InfiniDB, & Oracle 3
4. © Copyright 2023 Percona® LLC. All rights reserved 4 Without an index? • The entire table (or file) must be read from beginning to end. • Data may not be ordered. • Time consuming
5. © Copyright 2023 Percona® LLC. All rights reserved • Gain the ability to go to desired information • Faster With an index 5
6. © Copyright 2023 Percona® LLC. All rights reserved InnoDB Indexes Clustered Indexes
7. © Copyright 2023 Percona® LLC. All rights reserved Slides & Scripts are online https://speakerdeck.com/stoker 7
8. © Copyright 2023 Percona® LLC. All rights reserved 8 Root Node Intermediate Nodes Leaf Nodes (data) 1 to …. N 1-15 16-31 32-47 1-47 48- 1001 48- 99 100- 151 152- 1000
9. © Copyright 2023 Percona® LLC. All rights reserved 9 Root Node Intermediate Nodes Leaf Nodes (data) 1 to …. N 1-15 16-31 32-47 1-47 48- 1001 48- 99 100- 151 152- 1000 It is very easy with a B+ Tree to search for one record or a range
10. © Copyright 2023 Percona® LLC. All rights reserved 10 Root Node Intermediate Nodes Leaf Nodes (data) 1 to …. N 1-15 16-31 32-47 1-47 48- 1001 48- 99 100- 151 152- 1000 It is very easy with a B+ Tree to search for one record or a range Find record 22
11. © Copyright 2023 Percona® LLC. All rights reserved 11 Root Node Intermediate Nodes Leaf Nodes (data) 1 to …. N 1-15 16-31 32-47 1-47 48- 1001 48- 99 100- 151 152- 1000 It is very easy with a B+ Tree to search for one record or a range Records between 17 and 42
12. © Copyright 2023 Percona® LLC. All rights reserved 12 Records are stored by primary key • InnoDB stores records by the primary key and will pick one for you if you do not designate one. • And the one it picks will not be optimal. • Please pick your OWN primary key!!
13. © Copyright 2023 Percona® LLC. All rights reserved Primary key 001 Data for 001 Primary key 002 Data for 002 Primary key 003 Data for 003 Primary Key …. 13
14. © Copyright 2023 Percona® LLC. All rights reserved 14 Primary Key vs Secondary Indexes 1. Try to have a unique primary key not nullable for each row 2. Secondary key are non-primary keys that are used to access records in other tables, not nullable 3. Keys are what make up an index 4. Avoid nulls in indexes 5. No GUID PRIMARY Keys, a. maybe secondary
15. © Copyright 2023 Percona® LLC. All rights reserved Primary key 001 Column used for secondary index Primary key 002 Column used for secondary index Primary key 003 Column used for secondary index Primary Key …. 15 Secondary Index Layout
16. © Copyright 2023 Percona® LLC. All rights reserved Creating Indexes 16 Indexes are easy to create but remember they 1. Take up space 2. Add overhead for maintenance – INSERT/UPDATE/DELETE
17. © Copyright 2023 Percona® LLC. All rights reserved CREATE SCHEMA phptek; USE pphptek; CREATE TABLE ex01 (my_id SERIAL PRIMARY KEY, c1 INT, c2 INT); SHOW CREATE TABLE ex01; ex01, CREATE TABLE `ex01` ( `my_id` bigint unsigned NOT NULL AUTO_INCREMENT, `c1` int DEFAULT NULL, `c2` int DEFAULT NULL, PRIMARY KEY (`my_id`), UNIQUE KEY `my_id` (`my_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 17 A Quick demo
18. © Copyright 2023 Percona® LLC. All rights reserved 18 ex01, CREATE TABLE `ex01` ( `my_id` bigint unsigned NOT NULL AUTO_INCREMENT, `c1` int DEFAULT NULL, `c2` int DEFAULT NULL, PRIMARY KEY (`my_id`), UNIQUE KEY `my_id` (`my_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Bigint unsigned provides a range of 0 to 2^^63
19. © Copyright 2023 Percona® LLC. All rights reserved 19 ex01, CREATE TABLE `ex01` ( `my_id` bigint unsigned NOT NULL AUTO_INCREMENT, `c1` int DEFAULT NULL, `c2` int DEFAULT NULL, PRIMARY KEY (`my_id`), UNIQUE KEY `my_id` (`my_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci NOT NULL no null values in column!
20. © Copyright 2023 Percona® LLC. All rights reserved 20 ex01, CREATE TABLE `ex01` ( `my_id` bigint unsigned NOT NULL AUTO_INCREMENT, `c1` int DEFAULT NULL, `c2` int DEFAULT NULL, PRIMARY KEY (`my_id`), UNIQUE KEY `my_id` (`my_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci This is a no-no! Yes, you can do this but you have TWO indexes on the same data. If you use ATUO_INCREMENT you do not need UNIQUE KEY on the same column!
21. © Copyright 2023 Percona® LLC. All rights reserved INSERT INTO ex01 (my_id, c1, c2) values (NULL, rand(), RAND()); 21 Insert 100 records
22. © Copyright 2023 Percona® LLC. All rights reserved 22 EXPLAIN select * from ex01 where my_id = 7; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | ex01 | NULL | const | PRIMARY,my_id | PRIMARY | 8 | const | 1 | 100 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.0010 sec) Note (code 1003): /* select#1 */ select '7' AS `my_id`,'1' AS `c1`,'0' AS `c2` from `pldemo`.`ex01` where true
23. © Copyright 2023 Percona® LLC. All rights reserved create table ex02 (bad_id int unsigned not null, c1 int, c2 int); SHOW CREATE TABLE ex02; 'ex02', 'CREATE TABLE `ex02` ( `bad_id` int unsigned NOT NULL, `c1` int DEFAULT NULL, `c2` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci' (insert records) 23 For comparison
24. © Copyright 2023 Percona® LLC. All rights reserved 24 SQL > explain format=tree select * from ex02 where bad_id = 7; NO INDEX +--------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------+ | -> Filter: (ex02.bad_id = 7) (cost=10.25 rows=1) -> Table scan on ex02 (cost=10.25 rows=10) | +--------------------------------------------------------------------------------------------------+ 1 row in set (0.0007 sec) SQL > explain format=tree select * from ex01 where my_id = 7; WITH INDEX +-------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------+ | -> Rows fetched before execution (cost=0.00..0.00 rows=1) | +-------------------------------------------------------------+ 1 row in set (0.0009 sec)
25. © Copyright 2023 Percona® LLC. All rights reserved 25 { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1.00" }, "table": { "table_name": "ex01", "access_type": "const", "possible_keys": [ "PRIMARY", "my_id" ], "key": "PRIMARY", "used_key_parts": [ "my_id" ], "key_length": "8", "ref": [ "const" ], "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "0.00", "eval_cost": "0.10", "prefix_cost": "0.00", "data_read_per_join": "24" }, "used_columns": [ "my_id", "c1", "c2" ] } } } { "query_block": { "select_id": 1, "cost_info": { "query_cost": "10.25" }, "table": { "table_name": "ex02", "access_type": "ALL", "rows_examined_per_scan": 100, "rows_produced_per_join": 10, "filtered": "10.00", "cost_info": { "read_cost": "9.15", "eval_cost": "1.00", "prefix_cost": "10.25", "data_read_per_join": "16" }, "used_columns": [ "bad_id", "c1", "c2" ], "attached_condition": "(`pldemo`.`ex02`.`bad_id` = 7)" } } }
26. Can we just index everything? So, Indexes do work!
27. No! Details to come later!
28. What can I Index? A Whole Lot!
29. © Copyright 2023 Percona® LLC. All rights reserved Congratulations! You are now an employee of Yonk Box LLC and your first task is to create a table for orders of Yonk Boxes.
30. © Copyright 2023 Percona® LLC. All rights reserved 30
31. © Copyright 2023 Percona® LLC. All rights reserved CREATE TABLE customer_order ( order_id SERIAL PRIMARY KEY, customer_id INT UNSIGNED NOT NULL, order_date DATE, est_delivery DATE, item_nbr INT UNSIGNED NOT NULL, worker SMALLINT UNSIGNED); We know each Yonk Box has a serial number, a customer, a date when ordered, a estimated delivery date, an item number (delux Yonk, platinum edition, etc.), and maybe a worker assigned to the project. 31
32. © Copyright 2023 Percona® LLC. All rights reserved (this is why you need to know how you will use your data) What if we want to quickly search by customer_id? 32
33. © Copyright 2023 Percona® LLC. All rights reserved CREATE INDEX customer_order_customer_idx ON customer_order (customer_id); Yes, customer_order_customer_idx is a long name but not confusing. 33
34. © Copyright 2023 Percona® LLC. All rights reserved What if we want to quickly search by the first five numbers of the customer_id? 34
35. © Copyright 2023 Percona® LLC. All rights reserved CREATE INDEX customer_order_customer_idx ON customer_order (customer_id:5); 35
36. What about location?
37. © Copyright 2023 Percona® LLC. All rights reserved 37 CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326); ALTER TABLE geom ADD SPATIAL INDEX(g); MySQL can index spatial data
38. Key words?
39. © Copyright 2023 Percona® LLC. All rights reserved 39 ALTER TABLE customer_order ADD COLUMN description VARCHAR(500), ADD FULLTEXT desc_idx (description); Here we have made two modification to the table. First we add a description column and then create a FULLTEXT index on that new column.
40. © Copyright 2023 Percona® LLC. All rights reserved 40 ALTER TABLE customer_order ADD COLUMN description VARCHAR(500), ADD FULLTEXT desc_idx (description); SELECT order_id, customer_id FROM customer_order WHERE MATCH(description) AGAINST ('Gold'); +----------+-------------+ | order_id | customer_id | +----------+-------------+ | 1357 | 42 | +----------+-------------+ 1 row in set (0.0009 sec)
41. Functional Index A functional index is defined on the result of a function applied to one or more columns of a single table
42. © Copyright 2023 Percona® LLC. All rights reserved ALTER TABLE customer_order ADD INDEX est_month_idx((month(est_delivery))); ALTER TABLE product ADD INDEX total_production_cost_idx ((cost_of_good_sold *1.5)); explain format=tree select month(est_delivery) from customer_order where month(est_delivery) = 5; +----------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------+ | -> Index lookup on customer_order using est_month_idx (month(est_delivery)=5) (cost=0.35 rows=1) | +----------------------------------------------------------------------------------------------------+ 1 row in set (0.0010 sec) explain format=tree select id as 'item', cost_of_good_sold as 'cost to product' from product where cost_of_good_sold * 1.5 > 10.0\G *************************** 1. row *************************** EXPLAIN: -> Filter: ((cost_of_good_sold * 1.5) > 10.0) (cost=1.16 rows=2) -> Index range scan on product using total_production_cost_idx over (10.000 < (`cost_of_good_sold` * 1.5)) (cost=1.16 rows=2) 42
43. © Copyright 2023 Percona® LLC. All rights reserved A Multi-Valued Index (MVI) is a secondary index defined on a JSON column made up of an array of values. Traditionally indexes where you have one value per index entry, a 1:1 ratio. A MVI can have multiple records for each index record. Multi-Value Indexes 43
44. @ 2020 select * from customers; +----+-------+------------------------------------+ | id | name | info | +----+-------+------------------------------------+ | 12 | Fred | {"zipcode": [12345, 78901]} | | 12 | Matt | {"zipcode": [22221, 64263, 11111]} | | 15 | Kenny | {"zipcode": [12345]} | | 15 | Peter | {"zipcode": [54321, 65432]} | +----+-------+------------------------------------+ create table customers ( id int, name varchar(20), info JSON, INDEX zidx ((cast(info->'$.zipcode' AS UNSIGNED ARRAY))) ); SELECT id, name FROM customers WHERE 12345 MEMBER OF (info->"$.zipcode"); +----+-------+ | id | name | +----+-------+ | 15 | Kenny | | 12 | Fred | +----+-------+ 2 rows in set (0.0009 sec) 44
45. Multi Columns You can index more than one column in a row! Put highest cardinality/rarest in the left most column (and repeat until done)
46. © Copyright 2023 Percona® LLC. All rights reserved create table x (c1 int, c2 int, c3 int, c4 int); insert into x values (1,2,3,100),(1,2,4,101),(2,2,2,102),(4,1,1,104),(5,6,2,109); create index x_index on x (c1,c2,c3); explain format=tree select * from x where c1=1 and c2=2 and c3=3; +----------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------+ | -> Index lookup on x using x_index (c1=1, c2=2, c3=3) (cost=0.35 rows=1) | +----------------------------------------------------------------------------+ 46
47. © Copyright 2023 Percona® LLC. All rights reserved create table x (c1 int, c2 int, c3 int, c4 int); insert into x values (1,2,3,100),(1,2,4,101),(2,2,2,102),(4,1,1,104),(5,6,2,109); create index x_index on x (c1,c2,c3); explain format=tree select * from x where c1=1 and c2=2; +----------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------+ | -> Index lookup on x using x_index (c1=1, c2=2) (cost=0.70 rows=2) | +----------------------------------------------------------------------+ 47
48. © Copyright 2023 Percona® LLC. All rights reserved create table x (c1 int, c2 int, c3 int, c4 int); insert into x values (1,2,3,100),(1,2,4,101),(2,2,2,102),(4,1,1,104),(5,6,2,109); create index x_index on x (c1,c2,c3); explain format=tree select * from x where c1=1 and c3=3; +--------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------+ | -> Index lookup on x using x_index (c1=1), with index condition: (x.c3 = 3) (cost=0.54 rows=2) | +--------------------------------------------------------------------------------------------------+ 48
49. @ 2020 create table x (c1 int, c2 int, c3 int, c4 int); insert into x values (1,2,3,100),(1,2,4,101),(2,2,2,102),(4,1,1,104),(5,6,2,109); create index x_index on x (c1,c2,c3); explain format=tree select * from x where c2=2 and c3=3; +--------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------+ | -> Filter: ((x.c3 = 3) and (x.c2 = 2)) (cost=0.75 rows=1) -> Table scan on x (cost=0.75 rows=5) | +--------------------------------------------------------------------------------------------------------+ 49
50. Covering Index Get the answer from the index without looking in the table!
51. © Copyright 2023 Percona® LLC. All rights reserved create table x (c1 int, c2 int, c3 int, c4 int); insert into x values (1,2,3,100),(1,2,4,101),(2,2,2,102),(4,1,1,104),(5,6,2,109); create index x_index on x (c1,c2,c3); explain format=tree select c1,c2 from x where c1=1 and c2=2 and c3=3; +-------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------+ | -> Covering index lookup on x using x_index (c1=1, c2=2, c3=3) (cost=0.35 rows=1) | +-------------------------------------------------------------------------------------+ 51
52. © Copyright 2023 Percona® LLC. All rights reserved MySQL supports foreign keys to cross-referencing related data across tables and foreign key constraints, which help keep the related data consistent. A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table. Foreign Keys 52
53. © Copyright 2023 Percona® LLC. All rights reserved 53 CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id) ); CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE );
54. © Copyright 2023 Percona® LLC. All rights reserved 54 Hash join is a way of executing a join where a hash table is used to find matching rows between the two tables. It is typically more efficient than nested loop joins, especially if one of the inputs can fit in memory. Hash Joins
55. © Copyright 2023 Percona® LLC. All rights reserved 55 explain format=tree select a.c1, b.c2, c.c1 from a join b on (a.c2=b.c2) join c on (a.c2=c.c1)\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (c.c1 = a.c2) (cost=4.35 rows=4) -> Table scan on c (cost=0.09 rows=4) -> Hash -> Inner hash join (b.c2 = a.c2) (cost=2.50 rows=4) -> Table scan on b (cost=0.09 rows=4) -> Hash -> Table scan on a (cost=0.65 rows=4) MySQL employs a hash join for any query for which each join has an equi-join condition, and in which there are no indexes that can be applied to any join conditions
56. © Copyright 2023 Percona® LLC. All rights reserved 56 MySQL supports invisible indexes. An invisible index is not seen by the query optimizer. The feature applies to indexes other than primary keys. Much easier than removing an index for testing and then having to rebuild. Invisible Indexes
57. © Copyright 2023 Percona® LLC. All rights reserved 57 $EXPLAIN format=tree select count(CountryCode) from City where District='Texas'\G *************************** 1. row *************************** EXPLAIN: -> Aggregate: count(city.CountryCode) (cost=484.12 rows=419) -> Filter: (city.District = 'Texas') (cost=442.24 rows=419) -> Table scan on City (cost=442.24 rows=4188) $ALTER TABLE City ADD INDEX district_idx (District); Query OK, 0 rows affected (0.1120 sec) $EXPLAIN format=tree select count(CountryCode) from City where District='Texas'\G *************************** 1. row *************************** EXPLAIN: -> Aggregate: count(city.CountryCode) (cost=11.70 rows=26) -> Index lookup on City using district_idx (District='Texas') (cost=9.10 rows=26) 1 row in set (0.0006 sec) $ ALTER TABLE City ALTER INDEX district_idx INVISIBLE; $ EXPLAIN format=tree select count(CountryCode) from City where District='Texas'\G *************************** 1. row *************************** EXPLAIN: -> Aggregate: count(city.CountryCode) (cost=425.36 rows=3) -> Filter: (city.District = 'Texas') (cost=425.05 rows=3) -> Table scan on City (cost=425.05 rows=4188)
58. © Copyright 2023 Percona® LLC. All rights reserved 58 Invisible columns CREATE TABLE t1 ( i INT, j DATE INVISIBLE ) ENGINE = InnoDB; ALTER TABLE t1 ADD COLUMN k INT INVISIBLE; Some coders use numbers for columns (SELECT 1,3,4,23 FROM foo;) instead of names. If you have ‘older’ code that does this and need to add a column but do not want to re-code, Invisible columns are a handy feature.
59. Instead of indexing, you may want a histogram! Great for data with low ‘churn rate’ Optimizer ‘assumes’ even distribution of data within a column – a very rare occurrence Histograms
60. @ 2020 60 $ create table h1 (id int unsigned auto_increment, x int unsigned, primary key(id)); $ insert into h1 (x) values (1),(2),(2),(3),(3),(3),(4),(4),(4),(4),(17) ; $ explain SELECT x FROm h1 WHERE x> 0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: h1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 11 filtered: 33.32999801635742 Extra: Using where Estimate from optimizer - Will need to read 33.33 of the 11 rows To find all rows with x > 0 All rows have values of x > 0 Not a great estimate
61. @ 2020 61 $ ANALYZE TABLE h1 UPDATE HISTOGRAM ON x WITH 10 BUCKETS; +----------+-----------+----------+----------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+-----------+----------+----------------------------------------------+ | world.h1 | histogram | status | Histogram statistics created for column 'x'. | +----------+-----------+----------+----------------------------------------------+ $explain SELECT x FROm h1 WHERE x> 0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: h1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 11 filtered: 100 Extra: Using where Better Estimate
62. @ 2020 62 Index • Fast • Requires maintenance • Index Dive by Server • Take up space on disk and in memory Histogram • Fast-ish • Requires maintenance as data ages • Not for rapidly changing data Index or Histogram?
63. A Rant Against UUIDS As Primary keys
64. © Copyright 2023 Percona® LLC. All rights reserved 64 When new records are inserted into an InnoDB clustered index, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records. If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full. 36 character long! Use BIN_TO_UUID() InnoDB UUIDs do not monotonically increase
65. © Copyright 2023 Percona® LLC. All rights reserved SQL >create table uuid (x binary(16) PRIMARy KEY); Query OK, 0 rows affected (0.0171 sec) SQL > INSERT INTO uuid VALUES(UUID_TO_BIN(UUID())); Query OK, 1 row affected (0.0200 sec) SQL > select * from uuid; +------------------------------------+ | x | +------------------------------------+ | 0x4D02C4B9ED2911ED9688803F5D08564B | +------------------------------------+ 1 row in set (0.0011 sec) 65
66. Wrap Up!
67. © Copyright 2023 Percona® LLC. All rights reserved 67 TL;DR Each table needs a primary key Primary keys are best when unique, numeric, and monotonically increasing No NULLs You will be assigned a next to useless primary key if do do not pick one
68. Bonus! Some scripts you need
69. © Copyright 2023 Percona® LLC. All rights reserved Finding Tables w/o Primary Keys! SELECT i.TABLE_ID, t.NAME FROM INFORMATION_SCHEMA.INNODB_INDEXES i JOIN INFORMATION_SCHEMA.INNODB_TABLES t ON (i.TABLE_ID = t.TABLE_ID) WHERE i.NAME='GEN_CLUST_INDEX'; 69
70. © Copyright 2023 Percona® LLC. All rights reserved Unused Indexes select database_name, table_name, t1.index_name, format_bytes(stat_value * @@innodb_page_size) size from mysql.innodb_index_stats t1 join sys.schema_unused_indexes t2 on object_schema=database_name and object_name=table_name and t2.index_name=t1.index_name where stat_name='size' order by stat_value desc; 70
71. © Copyright 2023 Percona® LLC. All rights reserved Duplicate Indexes select t2.*, format_bytes(stat_value * @@innodb_page_size) size from mysql.innodb_index_stats t1 join sys.schema_redundant_indexes t2 on table_schema=database_name and t2.table_name=t1.table_name and t2.redundant_index_name=t1.index_name where stat_name='size' order by stat_value desc; 71 It is really recommended that you make a ‘questionable’ index invisible frist, well before deleting.
72. © Copyright 2023 Percona® LLC. All rights reserved Missing Indexes? select * from sys.schema_tables_with_full_table_scans; 72
73. © Copyright 2023 Percona® LLC. All rights reserved It Is Important To Have The Working Set In Memory! SELECT format_bytes(@@innodb_buffer_pool_size) BufferPoolSize, FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct, FORMAT(C.num * 100.0 / D.num,2) BufferPollDirtyPct FROM (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_data') A, (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_pages_total') B, (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_dirty') C, (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_total') D; 73 +----------------+-------------------+--------------------+ | BufferPoolSize | BufferPoolFullPct | BufferPollDirtyPct | +----------------+-------------------+--------------------+ | 8.00 MiB | 97.07 | 0.00 | +----------------+-------------------+--------------------+ 1 row in set (0.0028 sec)
74. © Copyright 2023 Percona® LLC. All rights reserved Verify the Ratio of pages requested and read from disk SELECT FORMAT(A.num * 100 / B.num,2) DiskReadRatioPct FROM (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') A, (SELECT variable_value num FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') B; 74
75. © Copyright 2023 Percona® LLC. All rights reserved REDO Log Size SELECT VARIABLE_VALUE from performance_schema.global_status WHERE VARIABLE_NAME='Innodb_redo_log_current_lsn' INTO @a;SELECT sleep(60) INTO @garb ;SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_redo_log_current_lsn' INTO @b;select format_bytes(abs(@a - @b)) per_min, format_bytes(abs(@a - @b)*60) per_hour; 75 During peak trac time, you can get an estimation of the required amount for the Redo Log Capacity by running the query below (all in one single line): +-----------+----------+ | per_min | per_hour | +-----------+----------+ | 21.18 MiB | 1.24 GiB | +-----------+----------+ MySQL > SET persist innodb_redo_log_capacity=1.24*1024*1024*1024;
76. © Copyright 2023 Percona® LLC. All rights reserved Know Your Workload! SELECT object_schema, CONCAT(ROUND((SUM(count_read)/SUM(count_star))*100, 2),"%") `reads`, CONCAT(ROUND((SUM(count_write)/SUM(count_star))*100, 2),"%") `writes` FROM performance_schema.table_io_waits_summary_by_table WHERE count_star > 0 GROUP BY object_schema; +---------------+---------+--------+ | object_schema | reads | writes | +---------------+---------+--------+ | sys | 100.00% | 0.00% | | test | 87.50% | 12.50% | | world | 100.00% | 0.00% | +---------------+---------+--------+ 3 rows in set (0.0013 sec) 76
77. © Copyright 2023 Percona® LLC. All rights reserved Percona Live - https://www.percona.com/live/conferences May 22–24 at the Denver Marriott Tech Center! https://www.perconalive.com/eventregistration?discountcode=PGSVDiscountPass 10% off good for 1 month until May 20th :)
78. Thank You! percona.com David.Stokes@Percona.com @Stoker https://speakerdeck.com/stoker
79. Extras
80. © Copyright 2023 Percona® LLC. All rights reserved 80 Poppins Bold 24 Poppins Normal 16 Poppins Bold Size 17 Poppins Normal 12 = Poppins Bold Size 17 Poppins Normal 12 Poppins Bold Size 17 Poppins Normal 12 Poppins Bold Size 17 Poppins Normal 12
81. © Copyright 2023 Percona® LLC. All rights reserved 81 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Title here -Poppins Medium Size 34 Subtitle - Poppins Semibold Size 18
82. © Copyright 2023 Percona® LLC. All rights reserved 82 Title Subhead Title Subhead Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 • Poppins Normal size 13 • Poppins Normal size 13 • Poppins Normal size 13 • Poppins Normal size 13 Title Subhead Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 • Poppins Normal size 13 • Poppins Normal size 13 • Poppins Normal size 13 • Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 • Poppins Normal size 13 • Poppins Normal size 13 • Poppins Normal size 13 • Poppins Normal size 13
83. © Copyright 2023 Percona® LLC. All rights reserved 83 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Poppins Normal size 13 Title here Subtitle
84. © Copyright 2023 Percona® LLC. All rights reserved 84 Timeline March Mid-March — Mid-April February January Poppins Semi Bold Size 14 April Poppins Semi Bold Size 14 Poppins Semi Bold Size 14 Poppins Semi Bold Size 14
85. © Copyright 2023 Percona® LLC. All rights reserved 85 Call out Text Call out Text Call out Text Title here Call out Text Call out Text Text Goes Here Text Goes Here Text Goes Here Text Goes Here Text Goes Here