Mysql的索引、视图、触发器、存储过程(上)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Mysql的索引、视图、触发器、存储过程(上)

一、Mysql的索引


(1)什么是索引


  • 一般的应用系统中,读写比例一般在10:1,读是比写要多得多的,而且插入操作和一般的更新操作很少出现性能问题。


在实际环境中,工作人员遇到最多的,也是最容易出现问题的还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重,而索引的出现就是加快查询速度


(2)索引的作用


  • 索引可以加快客户端的查询数据的速度
  • 索引在Mysql中也叫做”键“,是存储引擎用于快速找到记录的一种数据结构


当表中的数据量越来越大时,索引对于性能的影响越来越重要,并且索引优化是对查询性能优化最有效的手段,可以轻而易举的将查询性能提升好几个度

可以把索引看作是字典中的音序表,想要查某个字时,如果不使用音序表,就需要把几百页的字典诶个翻一遍,而有了音序表就可以快速进行定位,从而找到指定的字


(3)索引原理


索引的目的就是提高查询效率,跟使用字典查询某个字一样,都是先看音序表或者偏旁表,然后定位到指定的章节,然后在较少的页数中寻找指定的字即可,不需要从几百页中诶个寻找


索引就是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说有了这种索引机制,我们可以总是可以使用同一种查找方式来锁定数据


(4)磁盘IO与预读


考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,会把当前磁盘地址的数据和相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到


每一次IO读取的数据称之为一页(page),具体一页有多大数据和操作系统有关,一般为4K或8K,也就是我们读取一页内的数据的时候,实际上才发生的一次IO读取,这个理论对于索引的数据结构设计非常有帮助


(5)索引的数据结构—B+树


任何一种数据结构都不是凭空产生的,每一种数据结构都会有他的背景和使用场景,而数据结构的作用就是每次查找数据的时候把磁盘IO读取的次数控制到一个很小的数量,最好是常数数量,那么如果一个高度可控的多路搜索树是否能满足要求呢,B+树就这样诞生了


  • -B+树的查找过程


可以看到当用户查找数据时,索引会由大到小缩小范围,最后成功找到数据


  • -B+树的性质


  1. 索引字段要尽量的小
  2. 索引的最左匹配特性(从左往右匹配)


(6)索引分类


索引总共分为5类:


  1. 普通索引 index:加速查找
  2. 唯一索引:


主键索引:primary key,加速查找+约束,不为空并且数据有唯一性

唯一索引:unique,加速查找+约束,唯一性


  1. 联合索引:


primary key(id,name):联合主键索引

unique(id,name):联合唯一索引

index(id,name):联合普通索引


  1. 全文索引fulltext:用于搜索很长的一篇文章时使用,效果最好
  2. 空间索引spatial:不常用


(7)索引的两大类型


在创建上面的索引时,为其指定索引类型,主要分为两类:

hash类型的索引: 查询单条快,范围查询慢

b+树类型的索引: 层数越多,数据量指数级增长,innoDB默认支持b+树


不同的存储引擎支持的索引类型是不一样的


存储引擎 支持的索引类型
InnoDB 支持事务,支持行级别锁定,支持B+树、Full-text等索引,不支持hash索引
MyISAM 不支持事务,支持表级别锁定,支持B+树、Full-text等索引,不支持hash索引
Memory 不支持事务,支持表级别锁定,支持B+树、hash等索引,不支持Full-text索引
NDB 支持事务,支持行级别锁定,支持hash索引,不支持B+树、Full-text等索引
Archive 不支持事务,支持标记别锁定,不支持B+树、hash、Full-text等索引


(8)创建、删除索引


-创建索引


  • 方法一: 创建表时创建索引


[root@rzy ~]# mysql -u root -p123123  #进入数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (58.38 sec)
mysql> create database aaa;  #创建aaa库
Query OK, 1 row affected (0.00 sec) 
mysql> use aaa;  #进入(选中)aaa库
Database changed
mysql> create table aaa(id int primary key,name char(10),age int);  #id为主键索引,写到项里
Query OK, 0 rows affected (0.14 sec)
mysql> create table bbb(id int ,name char(10),age int,index(id));  #id为普通索引,写到最后
Query OK, 0 rows affected (0.10 sec)
  • 方法二: 在已经创建的表上创建索引
mysql> show tables;  #查看所有的表
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
| bbb           |
+---------------+
2 rows in set (0.00 sec)
mysql> drop table aaa; #删除两个表
Query OK, 0 rows affected (0.00 sec)
mysql> drop table bbb;
Query OK, 0 rows affected (0.00 sec)
mysql> create table aaa(id int,name char(10),age int); #创建一个新的表,这次不指定索引
Query OK, 0 rows affected (0.01 sec)
mysql> create index a1 on aaa(id); #创建普通索引,索引名称为a1,为aaa表的id项创建索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> create index a2 on aaa(id,name);  #创建index普通联合索引,索引的是aaa表的id项和name项,索引名称是a2
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 方法三: 使用alter在已经存在的表上创建索引
mysql> alter table aaa add unique index(age);  #创建unique唯一索引,索引的是aaa表的age项
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table aaa add primary key(name); #创建primary主键索引,索引的是aaa表的name项
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

-查看所有索引

mysql> show index from aaa\G;  #查看aaa表的所有索引
*************************** 1. row ***************************
        Table: aaa  #索引的表
   Non_unique: 0
     Key_name: PRIMARY  #索引名称
 Seq_in_index: 1
  Column_name: name  #索引的项
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: aaa
   Non_unique: 0
     Key_name: age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: aaa
   Non_unique: 1
     Key_name: a1
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: aaa
   Non_unique: 1
     Key_name: a2
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: aaa
   Non_unique: 1
     Key_name: a2
 Seq_in_index: 2
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
5 rows in set (0.00 sec)
ERROR: 
No query specified

-删除索引

mysql> drop index a1 on aaa;  #删除aaa表的索引a1,a1是索引名称
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> drop index a2 on aaa;  #删除aaa表的索引a2
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> drop index age on aaa; #删除aaa表的索引age
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from aaa\G;
*************************** 1. row ***************************
        Table: aaa
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
ERROR: 
No query specified
mysql> alter table aaa drop primary key; #删除主键索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from aaa\G; #再次查看发现没有索引了
Empty set (0.00 sec)
ERROR: 
No query specified

(9)测试索引

******(1)准备表
mysql> drop table aaa; #删除刚才的表
Query OK, 0 rows affected (0.00 sec)
mysql> create table aaa(id int,name char(10),age int); #创建一个新表
Query OK, 0 rows affected (0.00 sec)
mysql> show tables ;  #查看所有表
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
+---------------+
1 row in set (0.00 sec)
******(2)插入数据
mysql> delimiter $$ #定义结束符号为$$,不定义的话,下面定义存储过程是无法写;的
mysql> create procedure a1()  #定义存储过程,有点像python的函数
    -> BEGIN
    -> declare i int default 1;  #相当于i=1
    -> while(i<3000000)do #使用while循环 
    -> insert into aaa values(i,concat('egon',i),i); #利用循环插入数据
    -> set i=i+1; 
    -> END while;
    -> END$$
Query OK, 0 rows affected (0.19 sec)
mysql> delimiter ; #把结束符变回;
mysql> show create procedure a1\G; #查看存储过程
*************************** 1. row ***************************
           Procedure: a1
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `a1`()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into aaa values(i,concat('egon',i),i);
set i=i+1;
END while;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
ERROR: 
No query specified
mysql> call a1(); #调用存储过程
mysql> select * from aaa;
。。。。。。
mysql> select * from aaa where id = 123456; #查询指定的id为123456的。可以看到花费了0.27秒
+--------+------------+--------+
| id     | name       | age    |
+--------+------------+--------+
| 123456 | egon123456 | 123456 |
+--------+------------+--------+
2 rows in set (0.27 sec)
******(3)加上索引进行查询,查询id,就给id加上索引
mysql> create index aaa on aaa(id);  #给aaa表的id项加上普通索引,名称为aaa
Query OK, 0 rows affected (1.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from aaa where id = 123456; #再次查看,可以看到花费0.00秒,加快了查询速度
+--------+------------+--------+
| id     | name       | age    |
+--------+------------+--------+
| 123456 | egon123456 | 123456 |
| 123456 | i          | 123456 |
+--------+------------+--------+
2 rows in set (0.00 sec)


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
11天前
|
存储 关系型数据库 MySQL
MySQL周内训参照5、存储过程创建
MySQL周内训参照5、存储过程创建
27 1
|
11天前
|
存储 SQL 关系型数据库
MySQL周内训参照4、触发器-插入-修改-删除
MySQL周内训参照4、触发器-插入-修改-删除
11 1
|
1天前
|
存储 SQL 关系型数据库
在MySQL中使用存储过程返回更新前的记录
在MySQL中使用存储过程返回更新前的记录
4 0
|
5天前
|
存储 关系型数据库 MySQL
索引的威力--记一次MySQL存储过程优化
在MySQL存储过程中,一个`INSERT INTO SELECT`语句起初执行超过130秒,优化后,执行时间降低到1秒内,实现了100倍的性能提升。问题在于`NOT IN`子查询导致的慢查询,最终通过创建单列索引获得了最佳效果。文章还介绍了索引创建的基本语法,并讨论了单列索引与组合索引的优缺点。作者强调,随着数据量增加,索引对于查询性能的重要性,计划未来采用读写分离来进一步优化处理大量插入和查询的场景。
|
11天前
|
存储 SQL 关系型数据库
MySQL存储过程_触发器_游标——Baidu Comate
MySQL存储过程_触发器_游标——Baidu Comate
12 0
|
18天前
|
存储 关系型数据库 MySQL
MySql创建带事务操作的存储过程
MySql创建带事务操作的存储过程
|
14天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
14天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
12天前
|
存储 SQL 关系型数据库
MySQL存储过程和存储函数的使用
MySQL的存储过程和存储函数在功能和用法上有明显的区别。存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,通过指定名称和参数(如果有)来调用执行,可以返回多个值或结果集,但不直接返回值。而存储函数则是一个有返回值的特殊存储过程,它返回一个值或表对象,可以直接嵌入SQL语句中使用,如SELECT语句中。两者都是为了提高SQL代码的重用性和性能,但使用场景和方式有所不同。
150 4

热门文章

最新文章