前言
索引是数据库中用来提高性能的最常用工具,所有MySQL的列类型都可以被索引,不同的存储引擎支持的最大索引数和最大索引长度不同,大多数存储引擎至少支持16个索引,总索引藏毒至少为256字节。
MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引,默认情况下MEMORY存储引擎使用HASH索引,但也支持BTREE索引。
索引操作
索引在创建表的时候可以同时创建,也可以使用alter table
随时增加索引:
1
2
3
4
5
6
7
8
9
| 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 | |
+----------+---------------+------+-----+---------+-------+
|
1
2
| mysql> create index empindex on emp (ename(2));
Query OK, 0 rows affected (0.10 sec)
|
1
2
| mysql> alter table emp add index empindex(ename(2));
Query OK, 0 rows affected (0.10 sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| 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被使用了,删除索引的语法为:
1
| drop index empindex on 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
权限。
创建视图语法为:
1
2
3
4
| create [or replace][algorithm = {undefined|merge|temptable}]
view view_name[(column_list)]
as select_statement
[with [cascade|local] check option]
|
修改视图语法为:
1
2
3
4
| alter [algorithm = {undefined|merge|temptable}]
view view_name[(column_list)]
as select_statement
[with [cascade|local] check option]
|
一个简单的单表关联创建例子:
1
2
3
4
5
6
7
8
9
10
| 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
语句中的表。
1
2
3
4
5
6
7
8
9
10
11
12
13
| -- 包含聚合函数
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
权限。
1
| drop view [if exists] view_name [,view_name] ... [restrict|cascaded]
|
1
2
| mysql> drop view pay_view1,pay_view2;
Query OK, 0 rows affected (0.00 sec)
|
查看视图
MySQL从5.1版本开始,show tables
命令不仅会显示表的名字,还会显示视图的名字,而不存在单独显示视图的show views
命令。
同样,可以也可以通过下面的命令查看:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| 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
查看。
1
2
3
4
5
6
7
| 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
来查看视图的相关信息。
1
2
3
4
5
6
7
8
9
10
11
12
13
| 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)
|