MySQL Basics (ix) Indexes and views

Index: is a special data stored in a specific storage format for the type of SQL query index:     clustered and nonclustered indexes: whether the data is stored together with the index    Primary key index and secondary index: whether the index is above the primary key    left prefix index and overwrite index: whether to intercept only the first few bytes on the left is the most indexed, MySQL defaults to the left prefix index of the storage:   Intercepts the first few bytes on the left in the field of the specified index, and sorts the indexes, groups the sorted indexes    generates the source data, and when the user takes the indexed field as the query condition, MySQL scans the metadata, and navigate to the metadata corresponding to the index management of the data:  create index  index name  ON  table name   (field name); #创建索引   show  INDEX FROM  table name; #查看指定表的索引     DROP INDEX  index name  ON  table name; #删除索引      Note: Indexes do not need to be modified because there is no meaning for example:    use mysql; #使用mysql库      CREATE INDEX test_index ON user  (user); #在user表的User字段索引      show index from user; #查看索引是否创建成功     explain select * from  user where user= ' root ' \g;         #使用EXPLAIN查看命令的执行过程 ( Not really going to do it) #使用索引的查询过程    id: 1# number   select_type: simple# Query Type    simple: Represents the table to which a simple query     table: user# query is associated    type: ref     #访问类型  possible_keys: test_index# Indexes that may be used        key: test_index# End-Use index key_len: 48# bytes used in the index            ref: const# one-to-one enquiry                rows: 3# rows read to find the target row  extra: using where# additional information       Common Access type: All: Full table scan   index: Full table scan based on index   range: Range scan      Ref: More indexes Returns the row of a matching value in a table  const/system: Directly returns a single line (typically based on primary key query) #没有使用索引的查询过程    id: 1   select_type: simple table: user      type: all# full table scan query   possible_keys: null      key: null         key_len: null &nBsp;ref: null  rows: 6  extra: using where View Management:      View is also called virtual table, is generated by the SELECT statement, MySQL view is more unstable, do not recommend using the   CREATE VIEW  view name  as select Statement # Create a view   DROP VIEW  View name   #删除视图示例:  create database mydb;      #创建测试数据库   USE mydb;     #使用测试数据库   create view test _view as select user,host,password from mysql.user; #创建视图   select *  FROM test_view;             #查看视图内容   SHOW TABLE STATUS LIKE  ' Test_view ' \g;     #查看视图状态     Name: test_view  Engine: NULL  Version: NULL     Row_format: NULL   Rows: NULL  Avg_row_length: NULL     data_length: null  max_data_length: null   index_length: null      data_free: null  auto_increment: null    create_time: null     Update_time: NULL    Check_time: NULL         collation: null checksum: null    create_ Options: null       comment: view Note: Changes to the view are actually data that modifies the base table (use caution)

This article is from the "Automated Operations" blog, please be sure to keep this source http://hongchen99.blog.51cto.com/12534281/1934020

MySQL Basics (ix) Indexes and views