MySQL中索引和视图

数据库小白一枚，正在不断学习积累知识，现将学习到的知识记录一下，也是将我的所得分享给大家！而今天这篇文章《MySQL中索引和视图》带大家来了解一下MySQL中索引和视图，希望对大家的知识积累有所帮助，从而弥补自己的不足，助力实战开发！

索引

一、概述

所有的Mysql列类型都可以被索引。

mysql支持BTREE索引、HASH索引、前缀索引、全文本索引(FULLTEXT)【只有MyISAM引擎支持，且仅限于char,varchar,text列】、空间列索引【只有MyISAM引擎支持，且索引的字段必须非空】，但不支持函数索引。

MyISAM和InnoDB存储引擎的表默认创建BTREE索引，
MEMORY存储引擎的表默认创建HASH索引。

二、创建索引

create [unique|fulltext|spatial] index index_name
    [using index_type]
on tbl_name(index_col_name, ...);

index_col_name:
    col_name [(length)] [asc/desc]

也可使用

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
  ...
  ADD INDEX [index_name] [index_type] (index_col_name,...)
  ...

例如：为city表创建10个字节的前缀索引

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

三、查看索引

可以使用

drop index index_name on tbl_name;

五、BTREE索引和HASH索引

MEMORY存储引擎的表可以选择使用BTREE索引和HASH索引

BTREE索引：

• 当使用>、=、create [or replace][algorithm = {undefined|merge|temptable}] view view_name[(column_list)] as select_statement [with [cascade|local] check option]

  修改视图语法为：

  alter [algorithm = {undefined|merge|temptable}]
      view view_name[(column_list)]
      as select_statement
      [with [cascade|local] check option]

  mysql对视图的定义有一些限制，例如

  -- 包含聚合函数
  mysql > create or replace view payment_sum as 
       -> select staff_id,sum(amount) 
       -> from payment
       -> group by staff_id;
  
  -- 常量视图
  mysql > create or replace view pi as 
       -> select 3.1415926 as pi;
  
  -- select中包含子查询
  mysql > create view city_view as
       -> select ( select city from city where city_id = 1);
  

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

  例如删除视图pay_view

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

  五、查看视图

  MySQL从5.1版本开始，

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

  例子

  mysql> show table status like 'pay_view' \G
  *************************** 1. row ***************************
             Name: pay_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
  1 row in set (0.00 sec)

  如果要查看某个视图的定义，可以使用

  mysql> show create view pay_view \G
  *************************** 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` AS `amount` from `pay` where (`pay`.`amount` 

  最后，还可以通过查看系统表

  mysql> select * from information_schema.views where table_name = 'pay_view' \G
  *************************** 1. row ***************************
         TABLE_CATALOG: def
          TABLE_SCHEMA: mysqldemo
            TABLE_NAME: pay_view
       VIEW_DEFINITION: select `mysqldemo`.`pay`.`pid` AS `pid`,`mysqldemo`.`pay`.`amount` AS `amount` from `mysqldemo`.`pay` where (`mysqldemo`.`pay`.`amount` 

  参考资料

  • 唐汉明等著，《深入浅出MySQL》，人民邮电出版社，2014

  到这里，我们也就讲完了《MySQL中索引和视图》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！