MySQL Common interview problem summary (iii)

061 How do I delete a table?

Answer: Run command drop TABLE table_name;

062 Creating an Index

Indexing is especially important for queries as a major application. Most of the time the performance problem is simply because we forgot to add the index, or we didn't add a more efficient index. If you do not index, then look for any even just a specific data will be a full table scan, if a table of large amounts of data and meet the conditions of the result is very small, then no index can cause fatal performance degradation. But it is not always necessary to build an index, such as the gender may only have two values, the index not only has no advantage, but also affect the update speed, which is called an excessive index.

063 Composite Index

For example, there is a statement like this: SELECT * from users where area= ' Beijing ' and age=22;

If we were to create a single index on area and age, because the MySQL query can only use one index at a time, the full table scan is a lot more efficient when it is relatively non indexed, but creating a composite index on the area, the age two column can be more efficient. If we create a composite index (area, age, salary), it's actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix feature. So when we create a composite index, we should place the columns that are most commonly used as constraints to the left, decreasing in descending order.

064 The index does not contain columns with null values

This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as one column in the composite index contains null values. So we don't want the default value of the field to be null when designing the database.

065 using short Index

Index A string column, if possible, to specify a prefix length. For example, if you have a column with char (255), if most values are unique within the first 10 or 20 characters, do not index the entire column. Short indexing can not only improve query speed but also save disk space and I/O operations.

066 indexing problems for sorting

The MySQL query uses only one index, so the columns in the order by are not indexed if the index is already used in the WHERE clause. Therefore, do not use sort operations when the database default sort meets the requirements, and try not to include sorting of multiple columns, preferably if you need to create a composite index for these columns.

067 like statement operations

It is generally discouraged to use like operations, and how to use them is also a problem if not used. Like "%aaa%" does not use indexes and like "aaa%" can use indexes.

068 MySQL Database design data type selection what are the areas to be aware of?

varchar and char types, varchar are longer, require an additional 1-2 bytes of storage, can save space, may be useful for performance. But because it is longer, fragmentation can occur, such as updating data;

Instead of the string type, the data is actually stored as an integral type using the Enum (enum class for MySQL).

String type

Avoid using strings to do identifiers as much as possible, because they take up a lot of space and are usually slower than integer types. Be particularly careful not to use string identifiers on MyISAM tables. MyISAM uses a compressed index (Packed index) for a string by default, which makes the lookup slower. According to the test, the MyISAM table with compressed index performance is 6 times times slower.

Also pay special attention to completely ' random ' strings, such as those generated by MD5 (), SHA1 (), UUID (). Each new value they produce is arbitrarily kept in a large space, which slows inserts and some select queries. 1 They slow down the insert query because the inserted values are randomly placed in the index. This results in paging, random disk access, and clustered index fragmentation on the storage engine. 2 They slow down the select query because logically adjacent rows are distributed around the disk and in memory. 3 random values cause caching to be poor for all types of query performance because they invalidate the access locality that the cache relies on for work. If the entire dataset becomes equally "hot", there is no advantage in caching a particular part of the data into memory. And if the working set cannot be loaded into memory, the cache does a lot of brush-writing work and can cause many cache misses.

If you save the UUID value, you should remove the dash, preferably by using Uhex () to convert the UUID value to a 16-byte number and save it in the binary (16) column.

069 do not perform operations on the column

SELECT * from users where year (adddate) <2007;

will be performed on each row, which will cause the index to fail with a full table scan, so we can change it to

SELECT * from users where adddate< ' 2007-01-01 ';

Do not use not in and operations

None in and operations do not use the index to perform a full table scan. Not in can be substituted by not exists, and ID!= 3 can be replaced with id>3 or id<3.

070 is null with IS NOT NULL

cannot be indexed with NULL, and any column that contains null values will not be included in the index. Even if there are multiple columns in the index, the column is excluded from the index as long as one of the columns contains null. This means that if a column has a null value, even indexing the column does not improve performance.

Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.

071 Join columns

For columns with joins, the optimizer will not use the index even if the last join value is a static value.