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

Preface

This article mainly introduces the relevant content about the use and difference between index and view in MySQL, and shares it for your reference and study. There is no more to say below, let's take a look at the detailed introduction.

Indexes

I. Overview

All Mysql column types can be indexed.

Mysql supports BTREE index, HASH index, prefix index, full-text index (FULLTEXT) [only supported by MyISAM engine and limited to char,varchar,text columns], spatial column index [only supported by MyISAM engine, and the fields of the index must be non-empty], but does not support functional index.

Tables of MyISAM and InnoDB storage engines create BTREE indexes by default

The table of the MEMORY storage engine creates HASH indexes by default.

Second, create an index

The create index syntax is:

Create [unique | fulltext | spatial] index index_name [using index_type] on tbl_name (index_col_name,...); index_col_name: col_name [(length)] [asc/desc]

You can also use alter table to add indexes with the syntax:

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification]... alter_specification:... ADD INDEX [index_name] [index_type] (index_col_name,...)...

For example: create a 10-byte prefix index for the city table

Mysql > create index cityName on city (Name (10)); mysql > alter table city add index cityName (Name (10))

Third, view the index

You can use show index from table; to view all current indexes of the table table.

IV. Delete index

Drop index index_name on tbl_name

5. BTREE index and HASH index

Tables of the MEMORY storage engine can choose to use BTREE indexes and HASH indexes

BTREE Index:

When using >, =, create or replace view payment_sum as-> select staff_id,sum (amount)-> from payment-> group by staff_id;-- constant view mysql > create or replace view pi as-> select 3.1415926 as pi;-- select contains subquery mysql > create view city_view as-> select (select city from city where city_id = 1)

The with [cascaded | local] check option option determines whether to update the data so that the record no longer meets the condition of the view, which defaults to cascaded. This option is similar to the options in the Oracle database.

Local: cascaded can be updated as long as the conditions for this view are met: all conditions for all views under that view must be met to update.

4. Delete views

You can delete one or more views at a time, but you must have drop permission for that view.

Drop view [if exists] view_name [, view_name]... [restrict | cascaded]

For example, delete the view pay_view

Mysql > drop view pay_view1,pay_view2;Query OK, 0 rows affected (0.00 sec)

5. View the view

Starting with MySQL version 5.1, the show tables command displays not only the name of the table, but also the name of the view, and there is no show views command that displays the view separately.

Similarly, you can also view it with the following command:

Show table status [from db_name] [like 'pattern']

Examples

Mysql > show table status like 'pay_view'\ gateway * 1. Row * * Name: pay_view Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULLMax_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: VIEW1 row in set (0.00 sec)

If you want to see the definition of a view, you can use show create view to view it.

Examples

Mysql > show create view pay_view\ payment * 1. Row * * View: pay_view Create View: CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost` SQL SECURITY DEFINER VIEW `pay_ view`AS select `pay`.`pid`AS `pid`, `pay`.`amount t`AS `amount `payt`pay` where (`pay`.`moneyt`

Select * from information_schema.views where table_name = 'pay_view'\ gateway * 1. Row * * TABLE_CATALOG: def TABLE_SCHEMA: mysqldemo TABLE_NAME: pay_view VIEW_DEFINITION: select `mysqldemo`.`pay`.`pid`AS `pid` `mysqldemo`.`pay`.`amount t` AS `amount t` from `mysqldemo`.`pay` where (`mysqldemo`.`pay``amount t` < 10) CHECK_OPTION: CASCADED IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINERCHARACTER_SET_CLIENT: gbk_chinese_ci1 row in set (0.03 sec)

QroomA:

Can MySQL views be indexed?

I think the answer is yes, the index is based on the real table behind the view, not on the view.

The index is a database object stored in the schema. The function of the index is to improve the query speed of the table. The index is to quickly locate the data through the method of fast access, thus reducing the read and write operations to the disk. An index is an object in a database. It cannot exist independently and must be dependent on a table object.

A view is the query result of one or more tables. It is a virtual table because it cannot store data.

references

Tang Hanming waiting, MySQL, people's posts and Telecommunications Publishing House, 2014

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.