Optimize tables for enhanced performance. This section covers various techniques, including proper indexing, data types, and storage engine choices, to improve query speed and efficiency.
OPTIMIZE TABLE has two main functions. It can either be used to defragment tables, or to update the InnoDB fulltext index.
Set the lock wait timeout. See .
OPTIMIZE TABLE works for (before , only if the server system variable is set), , and tables, and should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length
rows (tables that have , , , or columns). Deleted rows are maintained in a
linked list and subsequent INSERT operations reuse old row positions.
This statement requires for the table.
By default, OPTIMIZE TABLE statements are written to the and will be . The NO_WRITE_TO_BINLOG keyword (LOCAL is an alias) will ensure the statement is not written to the binary log.
OPTIMIZE TABLE statements are not logged to the binary log if is set. See also .
OPTIMIZE TABLE is also supported for partitioned tables. You
can use[ALTER TABLE](../../../../reference/sql-statements-and-structure/sql-statements/data-definition/alter/alter-table.md) ... OPTIMIZE PARTITION
to optimize one or more partitions.
You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. With other storage engines, OPTIMIZE TABLE does nothing by default, and returns this message: " The storage engine for the table doesn't support optimize". However, if the server has been started with the --skip-new option, OPTIMIZE TABLE is linked to , and recreates the table. This operation frees the unused space and updates index statistics.
The storage engine supports for this statement.
If a table is fragmented, will not be performed until an OPTIMIZE TABLE statement is executed on that table, unless the server system variable is set to ALWAYS.
When rows are added or deleted to an InnoDB , the index is not immediately re-organized, as this can be an expensive operation. Change statistics are stored in a separate location. The fulltext index is only fully re-organized when an OPTIMIZE TABLE statement is run.
By default, an OPTIMIZE TABLE will defragment a table. In order to use it to update fulltext index statistics, the system variable must be set to 1. This is intended to be a temporary setting and should be reset to 0 once the fulltext index has been re-organized.
Since fulltext re-organization can take a long time, the variable limits the re-organization to a number of words (2000 by default). You can run multiple OPTIMIZE statements to fully re-organize the index.
merged the Facebook/Kakao defragmentation patch, allowing one to use OPTIMIZE TABLE to defragment InnoDB tablespaces. For this functionality to be enabled, the system variable must be enabled. No new tables are created and there is no need to copy data from old tables to new tables. Instead, this feature loads n pages (determined by ) and tries to move records so that pages would be full of records and then frees pages that are fully empty after the operation. Note that tablespace files (including ibdata1) will not shrink as the result of defragmentation, but one will get better memory utilization in the InnoDB buffer pool as there are fewer data pages in use.
See for more details.
This page is licensed: GPLv2, originally from
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[WAIT n | NOWAIT]Open-ended set of "attributes" (key=value) for each "entity". That is, the list of attributes is not known at development time, and will grow in the future. (This makes one column per attribute impractical.)
"ad hoc" queries testing attributes.
Attribute values come in different types (numbers, strings, dates, etc.)
Scale to lots of entities, yet perform well.
It goes by various names
EAV -- Entity - Attribute - Value
key-value
RDF -- This is a flavor of EAV
MariaDB has dynamic columns that look something like the solution below, with the added advantage of being able to index the columns otherwise hidden in the blob. (There are caveats.)
Table with 3 columns: entity_id, key, value
The "value" is a string, or maybe multiple columns depending on datatype or other kludges.
a JOIN b ON a.entity=b.entity AND b.key='x' JOIN c ON ... WHERE a.value=... AND b.value=...
The SELECTs get messy -- multiple JOINs
Datatype issues -- It's clumsy to be putting numbers into strings
Numbers stored in do not compare 'correctly', especially for range tests.
Bulky.
Decide which columns need to be searched/sorted by SQL queries. No, you don't need all the columns to be searchable or sortable. Certain columns are frequently used for selection; identify these. You probably won't use all of them in all queries, but you will use some of them in every query.
The solution uses one table for all the EAV stuff. The columns include the searchable fields plus one . Searchable fields are declared appropriately (, , etc). The BLOB contains JSON-encoding of all the extra fields.
The table should be , hence it should have a PRIMARY KEY. The entitity_id is the 'natural' PK. Add a small number of other indexes (often 'composite') on the searchable fields. is unlikely to be of any use, unless the Entities should purged after some time. (Example: News Articles)
You have included the most important fields to search on -- date, category, etc. These should filter the data down significantly. When you also need to filter on something more obscure, that will be handled differently. The application code will look at the BLOB for that; more on this later.
You are not really going to search on more than a few fields.
The disk footprint is smaller; Smaller --> More cacheable --> Faster
It needs no JOINs
The indexes are useful
Build the extra (or all) key-value pairs in a hash (associative array) in your application. Encode it. COMPRESS it. Insert that string into the .
JSON is recommended, but not mandatory; it is simpler than XML. Other serializations (eg, YAML) could be used.
COMPRESS the JSON and put it into a (or ) instead of a field. Compression gives about 3x shrinkage.
When SELECTing, UNCOMPRESS the blob. Decode the string into a hash. You are now ready to interrogate/display any of the extra fields.
Schema is reasonably compact (compression, real datatypes, less redundancy, etc, than EAV)
Queries are fast (since you have picked 'good' indexes)
Expandable (JSON is happy to have new fields)
Compatible (No 3rd party products, just supported products)
Posted Jan, 2014; Refreshed Feb, 2016.
MariaDB's
This looks very promising; I will need to do more research to see how much of this article is obviated by it: ,
If you insist on EAV, set .
Rick James graciously allowed us to use this article in the documentation.
has other useful tips, how-tos, optimizations, and debugging tips.
Original source:
This page is licensed: CC BY-SA / Gnu FDL
MySQL 5.7 Has JSON datatype, plus functions to access parts
MongoDB, CouchDB -- and others -- Not SQL-based.
Dedupping the values is clumsy.
Performance is as good as the indexes you have on the 'searchable fields'.
Optionally, you can duplicate the indexed fields in the BLOB.
Values missing from 'searchable fields' would need to be NULL (or whatever), and the code would need to deal with such.
If you choose to use the JSON features of MariaDB or 5.7, you will have to forgo the compression feature described.
MySQL 5.7.8's JSON native JSON datatype uses a binary format for more efficient access.
(Drawback) Cannot use the non-indexed attributes in WHERE or ORDER BY clauses, must deal with that in the app. (MySQL 5.7 partially alleviates this.)
Your data includes a large set of non-overlapping 'ranges'. These could be IP addresses, datetimes (show times for a single station), zipcodes, etc.
You have pairs of start and end values; one 'item' belongs to each such 'range'. So, instinctively, you create a table with start and end of the range, plus info about the item. Your queries involve a WHERE clause that compares for being between the start and end values.
Once you get a large set of items, performance degrades. You play with the indexes, but find nothing that works well. The indexes fail to lead to optimal functioning because the database does not understand that the ranges are non-overlapping.
I will present a solution that enforces the fact that items cannot have overlapping ranges. The solution builds a table to take advantage of that, then uses Stored Routines to get around the clumsiness imposed by it.
The instinctive solution often leads to scanning half the table to do just about anything, such as finding the item containing an 'address'. In complexity terms, this is Order(N).
The solution here can usually get the desired information by fetching a single row, or a small number of rows. It is Order(1).
In a large table, "counting the disk hits" is the important part of performance. Since InnoDB is used, and the PRIMARY KEY (clustered) is used, most operations hit only 1 block.
Finding the 'block' where a given IP address lives:
For start of block: One single-row fetch using the PRIMARY KEY
For end of block: Ditto. The record containing this will be 'adjacent' to the other record.
For allocating or freeing a block:
2-7 SQL statements, hitting the clustered PRIMARY KEY for the rows containing and immediately adjacent to the block.
One SQL statement is a DELETE; if hits as many rows as are needed for the block.
The other statements hit one row each.
This is crucial to the design and its performance:
Having just one address in the row. These were alternative designs; they seemed to be no better, and possibly worse:
That one address could have been the 'end' address.
The routine parameters for a 'block' could have be start of this block and start of next block.
The IPv4 parameters could have been dotted quads; I chose to keep the reference implemetation simpler instead.
The interesting work is in the Ips, not the second table, so I focus on it. The inconvenience of JOINing to the second table is small compared to the performance gains.
Two, not one, tables will be used. The first table (Ips in the reference implementations) is carefully designed to be optimal for all the basic operations needed. The second table contains other infomation about the 'owner' of each 'item'. In the reference implementations owner is an id used to JOIN the two tables. This discussion centers around Ips and how to efficiently map IP(s) to/from owner(s). The second table has "PRIMARY KEY(owner)".
In addition to the two-table schema, there are a set of Stored Routines to encapsulate the necessary code.
One row of Ips represents one 'item' by specifying the starting IP address and the 'owner'. The next row gives the starting IP address of the next "address block", thereby indirectly providing the ending address for the current block.
This lack of explicitly stating the "end address" leads to some clumsiness. The stored routines hide it from the user.
A special owner (indicated by '0') is reserved for "free" or "not-owned" blocks. Hence, sparse allocation of address blocks is no problem. Also, the 'free' owner is handled no differently than real owners, so there are no extra Stored Routines for such.
Links below give "reference" implementations for IPv4 and IPv6. You will need to make changes for non-IP situations, and may need to make changes even for IP situations.
These are the main stored routines provided:
IpIncr, IpDecr -- for adding/subtracting 1
IpStore -- for allocating/freeing a range
IpOwner, IpRangeOwners, IpFindRanges, Owner2IpStarts, Owner2IpRanges -- for lookups
IpNext, IpEnd -- IP of start of next block, or end of current block
None of the provided routines JOIN to the other table; you may wish to develop custom queries based on the given reference Stored Procedures.
The Ips table's size is proportional to the number of blocks. A million 'owned' blocks may be 20-50MB. This varies due to
number of 'free' gaps (between zero and the number of owned blocks)
datatypes used for ip and owner
overhead Even 100M blocks is quite manageable in today's hardware. Once things are cached, most operations would take only a few milliseconds. A trillion blocks would work, but most operations would hit the disk a few times -- only a few times.
This specific to IPv4 (32 bit, a la '196.168.1.255'). It can handle anywhere from 'nothing assigned' (1 row) to 'everything assigned' (4B rows) 'equally' well. That is, to ask the question "who owns '11.22.33.44'" is equally efficient regardless of how many blocks of IP addresses exist in the table. (OK, caching, disk hits, etc may make a slight difference.) The one function that can vary is the one that reassigns a range to a new owner. Its speed is a function of how many existing ranges need to be consumed, since those rows will be DELETEd. (It helps that they are, by schema design, 'clustered'.)
Notes on the :
Externally, the user may use the dotted quad notation (11.22.33.44), but needs to convert to INT UNSIGNED for calling the Stored Procs.
The user is responsible for converting to/from the calling datatype (INT UNSIGNED) when accessing the stored routine; suggest /.
The internal datatype for addresses is the same as the calling datatype (INT UNSIGNED).
Adding and subtracting 1 (simple arithmetic).
(The reference implementation does not handle CDRs. Such should be easy to add on, by first turning it into an IP range.)
The code for handling IP address is more complex, but the overall structure is the same as for IPv4. Launch into it only if you need IPv6.
Notes on the :
Externally, IPv6 has a complex string, VARCHAR(39) CHARACTER SET ASCII. The Stored Procedure IpStr2Hex() is provided.
The user is responsible for converting to/from the calling datatype (BINARY(16)) when accessing the stored routine; suggest /.
The internal datatype for addresses is the same as the calling datatype (BINARY(16)).
Communication with the Stored routines is via 32-char hex strings.
The INET6* functions were first available in MySQL 5.6.3 and
Adapting to a different non-IP 'address range' data
The external datatype for an 'address' should be whatever is convenient for the application.
The datatype for the 'address' in the table must be ordered, and should be as compact as possible.
You must write the Stored functions (IpIncr, IpDecr) for incrementing/decrementing an 'address'.
An 'owner' is an id of your choosing, but smaller is better.
"Owner" needs a special value to represent "not owned". The reference implementations use "=" and "!=" to compare two 'owners'. Numeric values and strings work nicely with those operators; NULL does not. Hence, please do not use NULL for "not owned".
Since the datatypes are pervasive in the stored routines, adapting a reference implementation to a different concept of 'address' would require multiple minor changes.
The code enforces that consecutive blocks never have the same 'owner', so the table is of 'minimal' size. Your application can assume that such is always the case.
Original writing -- Oct, 2012; Notes on INET6 functions -- May, 2015.
Rick James graciously allowed us to use this article in the documentation.
has other useful tips, how-tos, optimizations, and debugging tips.
Original source:
This page is licensed: CC BY-SA / Gnu FDL
The IPv6 parameters are 32-digit hex because it was the simpler that BINARY(16) or IPv5 for a reference implementation.
The datatype of an 'owner' (MEDIUMINT UNSIGNED: 0..16M) -- adjust if needed.
The address "Off the end" (255.255.255.255+1 - represented as NULL).
The table is initialized to one row: (ip=0, owner=0), meaning "all addresses are free See the comments in the code for more details.
Inside the Procedures, and in the Ips table, an address is stored as BINARY(16) for efficiency. HEX() and UNHEX() are used at the boundaries.
Adding/subtracting 1 is rather complex (see the code).
The datatype of an 'owner' (MEDIUMINT UNSIGNED: 0..16M); 'free' is represented by 0. You may need a bigger datatype.
The address "Off the end" (ffff.ffff.ffff.ffff.ffff.ffff.ffff.ffff+1 is represented by NULL).
The table is initialized to one row: (UNHEX('00000000000000000000000000000000'), 0), meaning "all addresses are free.
You may need to decide on a canonical representation of IPv4 in IPv6. See the comments in the code for more details.
A special value (such as 0 or '') must be provided for 'free'.
The table must be initialized to one row: (SmallestAddress, Free)
When rows are deleted from an InnoDB table, the rows are simply marked as deleted and not physically deleted. The free space is not returned to the operating system for re-use.
The purge thread will physically delete index keys and rows, but the free space introduced is still not returned to operating system. This can lead to gaps in the pages. If you have variable length rows, new rows may be larger than old rows and cannot make use of the available space.
You can run OPTIMIZE TABLE or ALTER TABLE
ENGINE=InnoDB to reconstruct the table. Unfortunately running OPTIMIZE TABLE against an InnoDB table stored in the shared table-space file ibdata1 does two things:
Makes the table’s data and indexes contiguous inside ibdata1.
Increases the size of ibdata1 because the contiguous data and index pages are appended to ibdata1.
The feature described below has been deprecated in and was removed in . See and .
merged Facebook's defragmentation code prepared for MariaDB by Matt, Seong Uck Lee from Kakao. The only major difference to Facebook's code and Matt’s patch is that MariaDB does not introduce new literals to SQL and makes no changes to the server code. Instead, is used and all code changes are inside the InnoDB/XtraDB storage engines.
The behaviour of OPTIMIZE TABLE is unchanged by default, and to enable this new feature, you need to set the system variable to 1.
No new tables are created and there is no need to copy data from old tables to new tables. Instead, this feature loads n pages (determined by ) and tries to move records so that pages would be full of records and then frees pages that are fully empty after the operation.
Note that tablespace files (including ibdata1) will not shrink as the result of defragmentation, but one will get better memory utilization in the InnoDB buffer pool as there are fewer data pages in use.
A number of new system and status variables for controlling and monitoring the feature are introduced.
: Enable InnoDB defragmentation.
: Number of pages considered at once when merging multiple pages to defragment.
: Number of defragment stats changes there are before the stats are written to persistent storage.
: Number of records of space that defragmentation should leave on the page.
: Number of defragment re-compression failures
: Number of defragment failures.
: Number of defragment operations.
After these CREATE and INSERT operations, the following information can be seen from the INFORMATION SCHEMA:
Deleting three-quarters of the records, leaving gaps, and then optimizing:
Now some pages have been freed, and some merged:
See on the Mariadb.org blog for more details.
This page is licensed: CC BY-SA / Gnu FDL
innodb_defragment_fill_factor: Indicates how full defragmentation should fill a page.
innodb_defragment_frequency: Maximum times per second for defragmenting a single index.
[mysqld]
...
innodb-defragment=1SET @@global.innodb_file_per_table = 1;
SET @@global.innodb_defragment_n_pages = 32;
SET @@global.innodb_defragment_fill_factor = 0.95;
CREATE TABLE tb_defragment (
pk1 BIGINT(20) NOT NULL,
pk2 BIGINT(20) NOT NULL,
fd4 TEXT,
fd5 VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (pk1),
KEY ix1 (pk2)
) ENGINE=InnoDB;
DELIMITER //
CREATE PROCEDURE innodb_insert_proc (repeat_count INT)
BEGIN
DECLARE current_num INT;
SET current_num = 0;
WHILE current_num < repeat_count DO
INSERT INTO tb_defragment VALUES (current_num, 1, REPEAT('Abcdefg', 20), REPEAT('12345',5));
INSERT INTO tb_defragment VALUES (current_num+1, 2, REPEAT('HIJKLM', 20), REPEAT('67890',5));
INSERT INTO tb_defragment VALUES (current_num+2, 3, REPEAT('HIJKLM', 20), REPEAT('67890',5));
INSERT INTO tb_defragment VALUES (current_num+3, 4, REPEAT('HIJKLM', 20), REPEAT('67890',5));
SET current_num = current_num + 4;
END WHILE;
END//
DELIMITER ;
COMMIT;
SET autocommit=0;
CALL innodb_insert_proc(50000);
COMMIT;
SET autocommit=1;SELECT COUNT(*) AS Value FROM information_schema.innodb_buffer_page
WHERE table_name LIKE '%tb_defragment%' AND index_name = 'PRIMARY';
Value
313
SELECT COUNT(*) AS Value FROM information_schema.innodb_buffer_page
WHERE table_name LIKE '%tb_defragment%' AND index_name = 'ix1';
Value
72
SELECT COUNT(stat_value) FROM mysql.innodb_index_stats
WHERE table_name LIKE '%tb_defragment%' AND stat_name IN ('n_pages_freed');
COUNT(stat_value)
0
SELECT COUNT(stat_value) FROM mysql.innodb_index_stats
WHERE table_name LIKE '%tb_defragment%' AND stat_name IN ('n_page_split');
COUNT(stat_value)
0
SELECT COUNT(stat_value) FROM mysql.innodb_index_stats
WHERE table_name LIKE '%tb_defragment%' AND stat_name IN ('n_leaf_pages_defrag');
COUNT(stat_value)
0
SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables
WHERE engine LIKE 'InnoDB' AND table_name LIKE '%tb_defragment%';
TABLE_NAME data_free_MB table_rows
tb_defragment 4.00000000 50051
SELECT table_name, index_name, SUM(number_records), SUM(data_size) FROM information_schema.innodb_buffer_page
WHERE table_name LIKE '%tb_defragment%' AND index_name LIKE 'PRIMARY';
TABLE_NAME index_name SUM(number_records) SUM(data_size)
`test`.`tb_defragment` PRIMARY 25873 4739939
SELECT table_name, index_name, SUM(number_records), SUM(data_size) FROM information_schema.innodb_buffer_page
WHERE table_name LIKE '%tb_defragment%' AND index_name LIKE 'ix1';
TABLE_NAME index_name SUM(number_records) SUM(data_size)
`test`.`tb_defragment` ix1 50071 1051775DELETE FROM tb_defragment WHERE pk2 BETWEEN 2 AND 4;
OPTIMIZE TABLE tb_defragment;
TABLE Op Msg_type Msg_text
test.tb_defragment OPTIMIZE status OK
SHOW status LIKE '%innodb_def%';
Variable_name Value
Innodb_defragment_compression_failures 0
Innodb_defragment_failures 1
Innodb_defragment_count 4SELECT COUNT(*) AS Value FROM information_schema.innodb_buffer_page
WHERE table_name LIKE '%tb_defragment%' AND index_name = 'PRIMARY';
Value
0
SELECT COUNT(*) AS Value FROM information_schema.innodb_buffer_page
WHERE table_name LIKE '%tb_defragment%' AND index_name = 'ix1';
Value
0
SELECT COUNT(stat_value) FROM mysql.innodb_index_stats
WHERE table_name LIKE '%tb_defragment%' AND stat_name IN ('n_pages_freed');
COUNT(stat_value)
2
SELECT COUNT(stat_value) FROM mysql.innodb_index_stats
WHERE table_name LIKE '%tb_defragment%' AND stat_name IN ('n_page_split');
COUNT(stat_value)
2
SELECT COUNT(stat_value) FROM mysql.innodb_index_stats
WHERE table_name LIKE '%tb_defragment%' AND stat_name IN ('n_leaf_pages_defrag');
COUNT(stat_value)
2
SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables
WHERE engine LIKE 'InnoDB';
TABLE_NAME data_free_MB table_rows
innodb_index_stats 0.00000000 8
innodb_table_stats 0.00000000 0
tb_defragment 4.00000000 12431
SELECT table_name, index_name, SUM(number_records), SUM(data_size) FROM information_schema.innodb_buffer_page
WHERE table_name LIKE '%tb_defragment%' AND index_name LIKE 'PRIMARY';
TABLE_NAME index_name SUM(number_records) SUM(data_size)
`test`.`tb_defragment` PRIMARY 690 102145
SELECT table_name, index_name, SUM(number_records), SUM(data_size) FROM information_schema.innodb_buffer_page
WHERE table_name LIKE '%tb_defragment%' AND index_name LIKE 'ix1';
TABLE_NAME index_name SUM(number_records) SUM(data_size)
`test`.`tb_defragment` ix1 5295 111263