Shulou(Shulou.com)06/01 Report--

Index: a special type of data stored in a specific storage format for SQL query indexes: clustered and nonclustered indexes: whether the data is stored with the index primary key index and secondary index: whether the index is on the primary key with the left prefix index and overlay index: whether only the first few bytes on the left are truncated as the index By default, mysql uses the left prefix index to store the left prefix index: the first few bytes on the left in the field of the specified index are intercepted to generate the index, and the index is sorted. The sorted index is grouped and managed to generate source data. When the user uses the index field as the query condition, mysql scans the metadata. And locate the upper index management of the metadata corresponding data: CREATE INDEX index name ON table name (field name) # create the index SHOW INDEX FROM table name; # View the index DROP INDEX index name of the specified table; # delete the index Note: the index does not need to be modified because it is meaningless example: USE mysql # use the mysql library CREATE INDEX test_index ON user (User); # check whether the index is created successfully in EXPLAIN SELECT * FROM user WHERE User='root'\ G in the User field index SHOW INDEX FROM user; # of the user table # use EXPLAIN to view the execution process of the command (not really execute it) # query process using index id: 1 # No. Select_type: SIMPLE # query type SIMPLE: represents a simple query table: user # query associated table Type: ref # access type possible_keys: test_index # possible index key: test_index # final index key_len: 48 # number of bytes used in the index ref: const # one For a query rows: 3 # rows read to find the target row Extra: Using where # Common access types: ALL: full table scan index: full table scan based on index range: range scan ref: more indexed returns matching values in the table Const/system: return a single row directly (usually based on primary key query) # query process without using index id: 1 select_type: SIMPLE table: user type: ALL # full table scan query possible_keys: NULL Key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using where View Management: views are also called virtual tables It is generated by select statement. The view of mysql is unstable. It is not recommended to use CREATE VIEW view name AS select statement # create view DROP VIEW view name # delete view example: CREATE DATABASE mydb # create a test database USE mydb; # use the test database CREATE VIEW test_view AS SELECT User,Host,Password FROM mysql.user; # create a view SELECT * FROM test_view; # View the view content SHOW TABLE STATUS LIKE 'test_view'\ G # View view status 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: the modification of the view is actually the modification of the data of the base table (use with caution)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.