MySQL index type-form-use time-deficiencies-considerations

I. Type of index 1, normal index   add create  index  index_name on table (colume (length));         &N Bsp             Example: Create INDEX index_order_no on T_insruance_new_order (Order_no ( )                       Delete drop index index_name on Table_name/al TER table table_name DROP INDEX index_name2, unique index   create unique  index  index_name on table (Colume (l Ength) 3, primary key index   primary key are indexed by default 4, full-text index   FULLTEXT5, combined index   CREATE index index_name_age on T_insruance_new_order_ No (name (), age)//reduce the length of the index to increase the speed of the insert                       The combined index can be is---name,age or name, but cannot be an age (following the "leftmost prefix principle")-----Index Method: B_tree and hash methods, usually using B-tree,hash index, is the hash value after the hash processing, the size is no order, But the efficiency is greater than b-tree, because B-tree is from the root node to the side point, in to the leaf node efficiency is low   second, the form of the index 1, single-column index, multicolumn index   query, MySQL can only perform an index, So the multi-column index will find the most restrictive index in these indexes, query three, use the index time       Generally speaking, in where and join needThe index is created, but not entirely, because MySQL uses the index,      example only for >, >=,  =,<=,<,between,in, and some like times: Use like time to pay special attention to this      for like, using a wildcard symbol (% or _) does not use the index      select  * from MyTable Where name like "% handsome";   does not use index      select *  from mytable where name like "Yang%";   uses index four, the shortcomings of the index      1, indexing increases the speed of the query, slows down the update table, and updates the table (Update.delete.insert.) Updating the table will also update the index file     &NBSP;2, indexing the index file that takes up disk space, usually without much problem, but in a big data table, creating multiple combinations of indexes, the index file will swell very much           fast. V. Considerations for using Indexes       1, columns with null values, indexes are invalidated, so the Lieme that created the index consider non-null      2, using short indexes to improve retrieval speed and reduce disk space and IO operations       3, index column sorting, MySQL query use only one index, so multiple index query only with one of the most strict index, in order to improve the efficiency of the query can add composite index,      4, like fuzzy query, Wildcard characters first, index invalidation       5, do not calculate on column select *  from mytable where date (time) > ' 2007 '; index invalidation---where time& gt; ' 2007-01-01 '       6, do not apply to and <> operate  

MySQL index type-form-use time-deficiencies-considerations