MySQL index types include: 1. General IndexThis is the most basic index and it has no limitations. It is created in the following ways:Create an indexCREATE INDEX indexname on mytable (username (length)); if it is a Char,varchar type, length can be less than the actual length of the field, and if it is a blob and text type, you must specify Length, hereinafter. Modify Table StructureALTER mytable ADD INDEX [IndexName] on (username (length)) When creating a table, specify directlyCREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, INDEX [IndexName] (username (length))); Syntax for dropping an index:DROP INDEX [indexname] on mytable; 2. Unique indexIt is similar to the previous normal index, except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:Create an indexCREATE UNIQUE INDEX indexname on mytable (username (length)) Modify table structureALTER mytable ADD UNIQUE [IndexName] on (username (length)) When creating a table, specify directlyCREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, UNIQUE [IndexName] (username (length))) ; 3. Primary KEY indexIt is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built:CREATE TABLE mytable (ID INT not NULL, username VARCHAR (+) NOT NULL, PRIMARY KEY (id)); Of course, you can also use the ALTER command. Remember: A table can have only one primary key. 4. Combined IndexTo visually compare single-column and composite indexes, add multiple fields to the table:CREATE TABLE mytable (ID int NOT NULL, username varchar (+) NOT NULL, City VARCHAR (+) NOT NULL, age INT not NULL); To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index:ALTER table MyTable ADD INDEX name_city_age (username (ten), city,age); When the table is built, the usernname length is 16, where 10 is used. This is because, in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the insert. If you set up a single-column index on Usernname,city,age, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient. The establishment of such a composite index, in fact, is equivalent to the following three sets of composite indexes:usernname,city,age usernname,city Usernname Why not city,age such a combination index? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not as long as the combined index is used for queries that contain these three columns, the following SQL uses this combined index:SELECT * FROM MyTable whree username= "admin" and city= "Zhengzhou" select * FROM MyTable whree username= "admin" and the next few will not be used: c2>SELECT * FROM MyTable whree age=20 and city= "Zhengzhou" select * FROM MyTable whree city= "Zhengzhou"
the time to build the indexhere we have learned to build an index, so where do we need to build the index? in general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like. For example:SELECT t.name from MyTable T left joins MyTable m on T.name=m.username WHERE m.age=20 and m.city= ' Zhengzhou ' need for City and AG at this time E is indexed, because the userame of the MyTable table also appears in the join clause, and it is necessary to index it. the shortcomings of the indexThe benefits of using indexes are described above, but excessive use of indexes will result in abuse. So the index has its drawbacks as well:1. Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file. 2. Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly. indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements.
From for notes (Wiz)
MySQL index type & timing of indexing & index deficiencies
