MySQL index and advanced usage, MySQL index advanced usage

MySQL index and advanced usage, MySQL index advanced usage

http://tech.meituan.com/mysql-index.html

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

http://www.cnblogs.com/mailingfeng/archive/2012/09/26/2704344.html

All MySQL column types can be indexed. Define the maximum number of indexes and the maximum index length for each table based on the storage engine. All storage engines Support at least 16 indexes for each table, with a total index length of at least 256 bytes. Most storage engines have higher limits. Currently, there are only two types of indexes (btree and hash), which are related to the storage engine mode: MyISAM btreeInnoDB btreeMEMORY/Heap hash, by default, the MEMORY/Heap Storage engine uses the hash index. The difference between the B-tree index and the hash index of MySQL is the particularity of the hash index structure. The retrieval efficiency is very high, and the index retrieval can be located at a time, unlike B-Tree indexes, the I/O access from the root node to the branch node is not allowed until the page node is accessed, therefore, the query efficiency of hash indexes is much higher than that of B-Tree indexes. Although the hash index is highly efficient, the hash index itself has many limitations and drawbacks due to its particularity, mainly including the following. (1) The hash index can only meet the query conditions of =, <=>, IN, is null, or is not null, and range query cannot be used. Because the hash Index compares the hash value after hash calculation, it can only be used for equivalent filtering and cannot be used for range-based filtering, because the relationship between the size of hash values processed by the corresponding hash algorithm cannot be exactly the same as that before the hash operation. (2) hash indexes cannot be used to avoid data sorting. Hash indexes store hash values after hash calculation, and the relationship between hash values is not necessarily the same as that before hash calculation, therefore, the database cannot use the index data to avoid any sorting operations. (3) hash indexes cannot use some index keys for queries. For a composite index, when calculating the hash value, the hash value is calculated after the composite index is bonded, instead of separately calculating the hash value, therefore, when one or more index keys are used to query a combined index, the hash index cannot be used. (4) hash indexes cannot avoid table scanning at any time. As we already know, the hash index stores the hash value of the hash operation result and the row pointer information corresponding to the index key in a hash table, because different index keys have the same hash value, the query cannot be completed directly from the hash index even if the number of records that meet the hash key value is obtained, you still need to compare the actual data in the Access Table and obtain the corresponding results. (5) When the hash index encounters a large number of equal hash values, the performance is not necessarily higher than the B-Tree index. For low-selectivity index keys, if a hash index is created, a large amount of Record Pointer information is stored in the same hash value. In this way, it will be very troublesome to locate a record, which will waste multiple table data accesses, as a result, the overall performance is low. B-Tree indexes are the most frequently used index types in MySQL databases. All storage engines except the Archive storage engine support B-Tree indexes. Not only in MySQL, but in many other database management systems, the B-Tree index is also the most important index type, this is mainly because the storage structure of B-Tree indexes has excellent performance in database data query. Generally, the physical files of the B-Tree index in MySQL are mostly stored in the Balance Tree structure, that is, all the actual data is stored in the Leaf Node of the Tree, in addition, the shortest path length to any Leaf Node is exactly the same, so we all call it a B-Tree index. Of course, there may be various databases (or various storage engines of MySQL) when you store your own B-Tree indexes, the storage structure is slightly modified. For example, the actual storage structure used by the B-Tree index of the Innodb Storage engine is actually B + Tree, that is, a small transformation has been made on the basis of the B-Tree data structure, in addition to the index key information stored on each Leaf Node, the pointer information pointing to the next LeafNode adjacent to the Leaf Node is also stored, this is mainly to accelerate the efficiency of retrieving multiple adjacent Leaf nodes. There are two different types of indexes in the Innodb Storage engine, one is the Cluster form of Primary Key index (Primary Key ), the other is a common B-Tree Index that is basically the same as that of other storage engines (such as the MyISAM storage engine). This Index is called the Secondary Index in the Innodb Storage engine. In Innodb, if the data access efficiency is very high through the primary key, and if the data is accessed through the Secondary Index, Innodb first uses the relevant information of the Secondary Index, after the Leaf Node is retrieved through the corresponding index key, the corresponding data row needs to be obtained through the primary key value stored in the Leaf Node and then through the primary key index. The primary key index and non-primary key index of the MyISAM storage engine differ little, but the index key of the primary key index is a unique and non-empty key. In addition, the storage structure of MyISAM storage engine indexes is basically the same as that of Innodb's Secondary Index. The main difference is that MyISAM storage engine stores Index key information on Leaf Nodes, store the information of the corresponding data Row (such as Row Number) that can be directly located in the MyISAM data file, but does not store the key value information of the primary key. CREATE a TABLE */create table mytable (id INT, username VARCHAR (16), City VARCHAR (16), Age INT);/* the index is divided into single-column indexes and combined indexes. A single-column index contains only one column. A table can have multiple single-column indexes, but this is not a combination index. A composite index contains multiple columns. MySQL index types include: (1) general index, which is the most basic index and has no restrictions. It can be created in the following ways: */-- create index indexName ON mytable (username (10); -- Single-Column INDEX -- create index indexName ON mytable (username (10), city (10); -- combined INDEX -- indexName is the INDEX name, mytable table name, username and city are column names, and 10 are the prefix lengths. That is, the length of information stored by the index starting from the leftmost character in this column, in bytes-if it is CHAR or VARCHAR type, the prefix length can be smaller than the actual length of the field. For BLOB and TEXT types, you must specify the prefix length, the same below. -- Modify the TABLE structure to create an index alter table mytable add index indexName (username (10); -- Alter table mytable add index indexName (username (10), city (10); -- The indexName cannot be written here. The system automatically assigns username, username_2, username_3 ,... -- create table mytable (id INT, username VARCHAR (16), City VARCHAR (16), Age INT, INDEX indexName (username (10) -- INDEX indexName (username (10), city (10); -- here the indexName INDEX name can also be omitted/* (2) unique INDEX, it is similar to the previous normal index. The difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways: */-- CREATE an index create unique index indexName ON mytable (username (10); -- Modify the TABLE structure to create the index alter table mytable add unique index indexName (username (10); -- Can also be abbreviated to alter table mytable add unique indexName (username (10); -- when creating a TABLE, specify create table mytable (id INT, username VARCHAR (16), City VARCHAR (16), Age INT, unique index indexName (username (10) -- Can also be abbreviated to UNIQUE indexName (username (10);/* (3) primary key index, which is a special UNIQUE index and does not allow null values. When creating a table, the primary key created at the same time is the primary key index of the primary key index, which does not need to be named. A table can only have one primary key. The primary key index is a unique index or full-text index, but the unique index or full-text index cannot have the same index */-- modify the TABLE structure to create the index alter table mytable add primary key (id ); -- create table mytable (id INT, username VARCHAR (16), City VARCHAR (16), Age INT, primary key (id);/* (4) full-text index, the InnoDB Storage engine does not support full-text indexing */-- CREATE an index create fulltext index indexName ON mytable (username (10); -- Modify the TABLE structure to create the index alter table mytable add fulltext index indexName (username (10); -- Can also be abbreviated to alter table mytable add fulltext indexName (username (10); -- when creating a TABLE, specify create table mytable (id INT, username VARCHAR (16), City VARCHAR (16), Age INT, fulltext index indexName (username (10) -- Can also be abbreviated to FULLTEXT indexName (username (10) ENGINE = MYISAM; -- create a full-text index when creating a table and set the storage ENGINE of the table to MYISAM, the default InnoDB Storage engine of the new mysql version does not support full-text indexes. -- delete the index drop index indexName ON mytable;/* Mysql automatically uses the INDEX rule: btree indexes used <, <=, =,> =,>, BETWEEN, IN ,! = Or <>, and sometimes LIKE will use the btree index, because MySQL does not use the index when it starts with the wildcard "%" and. The btree index can be used to accelerate the order by operation of hash indexes. When the =, <=>, IN, is null or is not null operator IS used, the hash index IS used, it cannot be used to accelerate the order by operation, and the condition value must be an index column to search for a row. The entire value of this column must be enclosed BY quotation marks when the condition behind where is a string, if the string is a numeric mysql, it is automatically converted to a string, but no index is used. Mysql currently does not support function indexing. You can only index the first part of the column (specified length prefix). For char and varchar columns, using prefix indexes (the index is created from the start character to the specified length character) will greatly save space. The recommended mysql column is non-null. If null columns are allowed, the index will be affected (the index does not include NULL values ). They make the index and index statistics and comparison operations more complex. NULL values should be replaced by 0, a special value, or an empty string. Try NOT to use not in and <> operate username, city, and age to create a composite index for these three columns. IN fact, they are equivalent to creating the following three composite indexes: username, city, ageusername, cityusername uses composite indexes, such as where conditions. A column name must be a condition from the leftmost column to the rightmost consecutive column name of the composite index, the combination index is similar to the single index prefix */-- call the combination index SELECT * FROM mytable WHERE username = 'admin' AND city = 'dalian '; -- indexes can also be used for multi-table join queries -- SELECT * for full-text indexes, MATCH (username, city) AGAINST ('name100 name200 city500 thisisname ') FROM mytable where match (username, city) AGAINST ('name100 Name200 city500 thisisname '); -- returns "space", "and ". "lines of any word to be separated (broken characters:" spaces "", "and ". "However, if you do not use these symbols to break words, such as Chinese, you must manually break the words .) -- Words in the table column are also separated by spaces. -- MATCH... AGAINST is specified here twice. This will not cause additional overhead, because the MySQL optimizer will notice that the same MATCH... AGAINST is called twice and only the full-text search code is called once. /* The function MATCH () compares to a column name set (the column names of one or more columns in a FULLTEXT index ). The search string is given as a parameter of AGAINST. The search is executed in case-insensitive mode. The default search is case-insensitive. to case-sensitive, the character set of the column should be changed from utf8 to utf8_bin. Although the same table can have fields of different character sets, the fields in the same FULLTEXT index must be of the same character set and collation. Any words that appear on the stopword list or are too short (3 characters or less) will be ignored. (You can override the built-in stopword list. You can modify the setting of at least four characters.) A searched word has a weight. If the searched word contains too many rows in the table, the search term has a lower weight (or even a zero weight). Otherwise, it will get a higher weight. Then, the weights are combined to calculate the similarity of rows. If the search term appears in more than half of the rows in the table. It is effectively processed as a stopword (that is, a word with zero Semantic Value) without searching for the word. MATCH... AGAINST can be used with all MySQL syntaxes, such as JOIN or other filtering conditions. For each row of records in the table, MATCH... AGAINST returns a correlation value. That is, the similarity between the returned row and the Search Condition. When MATCH () is used in a WHERE clause, the returned results are automatically sorted by correlation in the descending order. If neither the WHERE clause nor the order by clause exists, the returned rows are not sorted. The correlation value is a positive floating point number. Zero correlation means they are not similar. The correlation calculation is based on: finding the number of words in the table row, the number of unique words in the row, the total number of words in the set, and the number of rows containing a special word. By 4.0.1, MySQL can also use the in boolean mode modifier to perform a logical full-text search. In boolean mode features: Do not remove rows that match more than 50%. It is not automatically sorted in reverse order of relevance. You can search for fields without FULLTEXT indexes, but it is very slow. Restrict the longest and shortest strings. Apply stopwords. Logical full-text search supports the following operators: + a leading plus sign, each row of the returned results must contain the word. -A leading minus sign indicates that the row containing the word cannot appear in the returned result.> The operator adds the base value that contains the similarity value of the row returned by the word. <Operator reduces the base value that contains the similarity value of the returned row. () Multiple words in parentheses are equivalent to one word. That is, only one word in the brackets can represent the combination of the brackets and words outside the brackets for query, however, each word in the brackets is represented by the brackets in turn. Therefore, words outside the brackets are combined in multiple forms to create query conditions in sequence. ~ Turning the relevance from positive to negative indicates that having this word will reduce the relevance, but not like-excluding it, it is only behind it. * An asterisk is a truncation operator. It should be appended to a word and not prefixed. The function is similar to % "in the LIKE statement. Multiple words in double quotation marks are used as one word. Here are some examples. In the returned results, 1. + apple + juice... both words are contained 2. + apple macintosh... it contains the word "apple", but if it also contains "macintosh", it will be arranged a little higher. + apple-macintosh... including "apple" but not "macintosh" 4. + apple + (> pie <strudel )... include "apple" and "pie", or include "apple" and "strudel" (in any order ), however, "apple pie" is arranged a little higher than "apple strudel. apple *... including "apple", "apples", "applesauce", and "applet" 6. "some words "... can contain "som" E words of wisdom ", but not" some noise words "*/SELECT *, MATCH (username, city) AGAINST ('> name300 + thisisname-city100' in boolean mode) FROM mytable where match (username, city) AGAINST ('> name300 + thisname-city100' in boolean mode);/* Full-text index limit MATCH () all parameters of the function must be columns from the same table, and must be part of the same FULLTEXT index, unless MATCH () is in boolean mode. The MATCH () column must exactly MATCH the column defined IN a FULLTEXT index of the table, unless MATCH () is in boolean mode. The parameter of AGAINST () must be a constant string. MySQL full-text search settings: Most of the parameters are startup parameters, that is, you must restart MySQL after modification. Some parameter modifications must re-generate the index file. Mysql> show variables like 'ft % '; ft_boolean_syntax +-> <()~ *: "" & | Ft_min_word_len 4 ft_max_word_len 84 ft_query_expansion_limit 20 ft_stopword_file (built-in) ft_min_word_len: the shortest index string. The default value is 4, and the index file. Ft_max_word_len: The longest index string. The default value varies by version. [Mysqld] ft_min_word_len = 1 ft_stopword_file: Specifies the stopword file path. If it is left blank, stopword filtering is disabled. After modification, MySQL must be restarted and indexes must be rebuilt; the content of the stopword file can be separated by a comma (,). The bottom line and single quotation marks are valid string characters. 50% threshold: the configuration file is stored in storage/myisam/ftdefs. h. Change # define GWS_IN_USE GWS_PROB to # define GWS_IN_USE GWS_FREQ and re-compile MySQL. Because near-low threshold affects data accuracy, we do not recommend this, if in boolean mode is available, the 50% limit can be avoided. Ft_boolean_syntax: the query character that changes in boolean mode. You do not need to restart MySQL or re-create indexes. Modify the identification of string characters, for example, identify "-" as a valid character of the string: Method 1: Modify storage/myisam/ftdefs. h true_word_char () and misc_word_char (), re-compile MySQL, and re-build the index. Method 2: Modify the character set file, use the character set in the FULLTEXT index field, and re-create the index. Re-indexing: This is required for every table with FULLTEXT index. Mysql> repair table tbl_name QUICK; note that if myisamchk is used, the above setting value will be returned to the default value, because myisamchk is not set with MySQL. Solution 1: add the modified value to the parameter of myisamchk. Shell> myisamchk -- recover -- ft_min_word_len = 1 tbl_name.MYI solution 2: both sides must be set. [Mysqld] ft_min_word_len = 1 [myisamchk] ft_min_word_len = 1 solution 3: replace myisamchk syntax with repair table, analyze table, optimize table, and alter table because these syntaxes are executed by MySQL. The Chinese full text index can be used to create two tables. One Table field contains Chinese characters, and the other table corresponds to Chinese pinyin. The two tables must correspond to rows, and the data is consistent. during insertion, Chinese characters are converted to Chinese pinyin, when both tables are inserted into the query, the full-text index is also converted to Chinese pinyin, and then the corresponding row of the Chinese table is located or the mysqlcft full-text index plug-in is used to check the index usage. If the index is working, the value of Handler_read_key is very high, which indicates the number of times a row is read by the index value. A very low value indicates that the performance improvement of increasing the index is not high, because the index is not frequently used. The high value of Handler_read_rnd_next means that the query operation is inefficient and the index remediation should be established. This value indicates the number of requests read from the next row in the data file. If you are performing a large number of table scans, this value is high. It usually indicates that the table index is incorrect or the index is not used for the written query. Syntax: show status like 'handler _ read % '; data files and index files in the MyISAM table are automatically separated. InnoDB data and indexes are stored in the same tablespace, however, the index can contain multiple files, which greatly improves the query speed and reduces the table UPDATE speed, such as INSERT, UPDATE, and DELETE. When updating a table, MySQL not only needs to save data, but also stores the index file. Index files that occupy disk space. This problem is not serious in general, but if you create multiple composite indexes on a large table, the index file will expand very quickly.