MySQL中的索引和视图

前言

索引是数据库中用来提高性能的最常用工具,所有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;

查看索引:

1
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权限。

创建视图语法为:

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等)、distinctgroup byhavingunion或者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)

评论

Ajax Android AndroidStudio Animation Anroid Studio AppBarLayout Babel Banner Buffer Bulma ByteBuffer C++ C11 C89 C99 CDN CMYK COM1 COM2 CSS Camera Raw, 直方图 Chrome Class ContentProvider CoordinatorLayout C语言 DML DOM Dagger Dagger2 Darktable Demo Document DownloadManage ES2015 ESLint Element Error Exception Extensions File FileProvider Flow Fresco GCC Git GitHub GitLab Gradle Groovy HTML5 Handler HandlerThread Hexo Hybrid I/O IDEA IO ImageMagick IntelliJ Intellij Interpolator JCenter JNI JS Java JavaScript JsBridge Kotlin Lab Lambda Lifecycle Lint Linux Looper MQTT MVC MVP Maven MessageQueue Modbus Momentum MySQL NDK NIO NexT Next Nodejs ObjectAnimator Oracle VM Permission PhotoShop Physics Python RGB RS-232 RTU Remote-SSH Retrofit Runnable RxAndroid RxJava SE0 SSH Spring SpringBoot Statubar Style Task Theme Thread Tkinter UI UIKit UML VM virtualBox VS Code VUE ValueAnimator ViewPropertyAnimator Vue Vue.js Web Web前端 Workbench api apk bookmark by关键字 cli compileOnly computed css c语言 databases demo hexo hotfix html iOS icarus implementation init jQuery javascript launchModel logo merge methods mvp offset photos pug query rxjava2 scss servlet shell svg tkinter tomcat transition unicode utf-8 vector virtual box vscode watch webpack 七牛 下载 中介者模式 串口 临潼石榴 主题 书签 事件 享元模式 仓库 代理模式 位运算 依赖注入 修改,tables 光和色 内存 内核 内部分享 函数 函数式编程 分支 分析 创建 删除 动画 单例模式 压缩图片 发布 可空性 合并 同向性 后期 启动模式 命令 命令模式 响应式 响应式编程 图层 图床 图片压缩 图片处理 图片轮播 地球 域名 基础 增加 备忘录模式 外观模式 多线程 大爆炸 天气APP 太白山 头文件 奇点 字符串 字符集 存储引擎 宇宙 宏定义 实践 属性 属性动画 岐山擀面皮 岐山肉臊子 岐山香醋 工具 工厂模式 年终总结 开发技巧 异常 弱引用 恒星 打包 技巧 指令 指针 插件 插值 摄影 操作系统 攻略 故事 数据库 数据类型 数组 文件 新功能 旅行 旋转木马 时序图 时空 时间简史 曲线 杂谈 权限 枚举 架构 查询 标准库 标签选择器 样式 核心 框架 案例 桥接模式 检测工具 模块化 模板 模板引擎 模板方法模式 油泼辣子 泛型 洛川苹果 浅色状态栏 渲染 源码 源码分析 瀑布流 热修复 版本 版本控制 状态栏 状态模式 生活 留言板 相册 相对论 眉县猕猴桃 知识点 码云 磁盘 科学 笔记 策略模式 类图 系统,发行版, GNU 索引 组件 组合模式 绑定 结构 结构体 编码 网易云信 网格布局 网站广播 网站通知 网络 美化 联合 脚手架 膨胀的宇宙 自定义 自定义View 自定义插件 蒙版 虚拟 虚拟机 补码 补齐 表单 表达式 装饰模式 西安 观察者模式 规范 视图 视频 解耦器模式 设计 设计原则 设计模式 访问者模式 语法 责任链模式 贪吃蛇 转换 软件工程 软引用 运算符 迭代子模式 适配器模式 选择器 通信 通道 配置 链表 锐化 错误 键盘 闭包 降噪 陕西地方特产 面向对象 项目优化 项目构建 黑洞
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×