MySQL中的索引和视图

2019-05-01 · 大约 600 个字 · 预计 3 分钟读完

前言

索引是数据库中用来提高性能的最常用工具，所有MySQL的列类型都可以被索引，不同的存储引擎支持的最大索引数和最大索引长度不同，大多数存储引擎至少支持16个索引，总索引藏毒至少为256字节。

MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引，默认情况下MEMORY存储引擎使用HASH索引，但也支持BTREE索引。

索引操作

索引在创建表的时候可以同时创建，也可以使用alter table随时增加索引：

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

mysql> create index empindex on emp (ename(2));
Query OK, 0 rows affected (0.10 sec)

mysql> alter table emp add index empindex(ename(2));
Query OK, 0 rows affected (0.10 sec)

mysql> explain select * from emp where ename = 'xiaoming' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
possible_keys: empindex
          key: empindex
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

可以发现empindex被使用了,删除索引的语法为：

drop index empindex on emp;

查看索引：

show index from emp;

索引设计原则

搜索的索引列，不一定是要选择的列。最适合索引的列是出现在where子句中的列，而不是出现select后中的列。
使用唯一索引。选择容易数值容易区分的列进行索引。例如对生日的索引要比对性别的索引要好，因为生日的列具有不同的值，比较容易区分，而性别列只有M和F，此时索引用处不大，每次索引都得出大约一半的行。
使用短索引。对字符串的前缀索引中通常会指定一个前缀长度，如果在前10到20个字符内，多数值是唯一的，那么就可以不必对整个列进行索引，而是对前10到20个字符进行索引。这样能够节省索引空间，减少I/O时间，提高查询效率。
不要过度索引。每个额外索引都会占用额外的空间，降低写操作的性能，表修改时需要更新索引，甚至可能会重构，因此索引越多，花费的时间越长。另外MySQL在生成执行计划的时候会考虑到各个索引，多余的索引让查询优化的工作变得更加繁重。

BTREE和HASH索引

HASH索引的特点：

只能用于使用 =或<=>操作符的等式比较。
优化器不能使用HASH索引来加速order by操作。
MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY表，会影响一些查询的执行效率。
只能使用整个关键字来搜索一行。

而对于BTREE索引，当使用>、<、>=、<=、between、!=或者<>、like操作符时，都可以使用相关列上的BTREE索引。

视图

视图是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的，视图并不在数据库中实际存在，行和列数据来自于自定义视图的查询中所使用的表。

创建和修改视图

创建视图需要由create view权限，并且对查询涉及的表和列要有select权限。

如果使用create or replace或者alter权限修改视图，还需要有该视图的drop权限。

创建视图语法为：

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> create or replace view empview as select e.ename, e.sal from emp as e where ename='xiaoming';
Query OK, 0 rows affected (0.10 sec)

mysql> desc empview;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| ename | varchar(10)   | YES  |     | NULL    |       |
| sal   | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

MySQL对视图的定义有一些限制，例如from关键字后不能包含子查询，这和其他数据库不同。

视图的可更新性

视图的可更新性和视图中查询的定义有关，以下类型的视图为不可更新的。

包含聚合函数(sum,min,max,count等)、distinct、group by、having、union或者union all。
常量视图。
select中包含子查询。
join。
from一个不能更新的视图。

where语句的子查询中引用了from语句中的表。

-- 包含聚合函数
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);

with[cascaded|local] check option选项决定了是否允许更新数据使记录不再满足视图的条件，默认为cascaded。这个选项与Oracle数据库中的选项是类似的。

local：只要满足本视图的条件就可以更新
cascaded：必须满足所有针对该视图下的所有视图的条件才可以更新。

删除视图

可以一次删除一个或多个视图，但必须要有该视图的drop权限。

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

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

查看视图

MySQL从5.1版本开始，show tables命令不仅会显示表的名字，还会显示视图的名字，而不存在单独显示视图的show views命令。

同样，可以也可以通过下面的命令查看：

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)

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

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` < 10) WITH CASCADED CHECK OPTION
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.00 sec)

最后，还可以通过查看系统表information_schema.views来查看视图的相关信息。

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` < 10)
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
1 row in set (0.03 sec)

前言