文章目录
一. 索引:
1.索引的概述
索引(index):是帮助mysql高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护者满足特定查找算法的数结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这些数据结构就是索引,如下图所示:
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上,索引是数据库中用来提高性能的最常用的工具。
2. 索引的优势和劣势
- 优势:
- 类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本
- 通过索引列对数据库进行排序,降低数据排序的成本,降低cpu的消耗
- 劣势:
- 实际上索引也是一张表,该表中保存了主键和索引字段,并指向实体类的记录,索引列也是要占用空间的
- 虽然索引大大提高了查询效率,同时降低更新表的速度,,如果对表进行insert update delete,因为更新表时,mysql不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的简直变化后的索引信息
3.索引结构
索引是在MySql的存储引擎层中实现的,而不是在服务层实现的,所以每种引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型,Mysql目前提供了一下四种索引:
- BTREE 索引:最常见的索引类型,大部分索引都支持B树索引
- HASH索引:只有Memory引擎支持,使用场景简单
- R-tree索引 (空间索引):空间索引是MyKSAM引擎的一个特殊索引类型,主要用于地理空间数据类型,使用较少
- Full-text(全文索引):全文索引也是MyLSAM的一个特殊索引类型,主要用于全文索引,InnoDB,从mysql5开始支持全文索引
索引 | InnoDB索引 | MyISAM引擎 | Memory引擎 |
BTREE索引 | 支持 | 支持 | 支持 |
HASH索引 | 不支持 | 不支持 | 支持 |
R-tree | 不支持 | 支持 | 不支持 |
Full-text | 5.6版本账户支持 | 支持 | 不支持 |
我们平常说的索引,如果没有特被的指出,都是指B+树(多路搜索树,并不一定是二叉树的)结构组织的索引,其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引,统称为索引
3.1 BTREE结构
Btree又叫多路平衡搜索树,一颗m叉的BTree特性如下:
- 树中每个节点最多包含m个孩子
- 除根节点与叶子结点外,每个节点至少有(cell(m/2))个孩子
- 若跟节点不是叶子结点,则至少有两个孩子
- 所有的叶子结点都在同一层
- 每个非叶子结点由n个key与n+1个指针组成 其中cell(m/2)-1 <=n<=m-1
以5叉BTree为例,key的数量:公式推导cell(m/2)-1 <=n<=m-1 .所以2<=n<=4,当n>4时,中间节点分裂到父节点,两边节点分裂
插入 CNGAHEKQMFWLTZDPRXYS数据为例
到此,该BTREE树就已经构建完成了,BTREE树与二叉树相比,查询数据的效率更高,因为对于想听的数据量来说 BTREE的层次结构比二叉树小,因此搜索速度快
3.2 B+TREE结构
B+Tree为BTree的变种,B+Tree和BTree的区别为“
- n叉B+Tree最多含有n个key,而Btree最多含有n-1个key
- B+Tree的叶子结点保存所有的Key信息,依key大小顺序排序
- 所有的飞叶子结点都可以看做是key的索引部分
B+Tree只有叶子结点可以保存key的信息,查询任何key都要从root走到叶子,所以B+Tree的查询效率更加稳定
叶子结点相邻的叶子结点有了指针,主要是为了范围搜索
4. 索引的分类:
- 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引:索引列的值必须唯一,但允许有空值
- 复合索引:即一个索引包含多个列
5. 索引语法
索引在创建表的时候,可以同时创建,也可以所示增加新的索引
准备环境
/*
Navicat Premium Data Transfer
Source Server : MySql
Source Server Type : MySQL
Source Server Version : 60011
Source Host : localhost:3306
Source Schema : dome01
Target Server Type : MySQL
Target Server Version : 60011
File Encoding : 65001
Date: 22/02/2020 14:49:52
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for city
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of city
-- ----------------------------
INSERT INTO `city` VALUES (1, '西安', 1);
INSERT INTO `city` VALUES (2, 'NewYork', 2);
INSERT INTO `city` VALUES (3, '北京', 1);
INSERT INTO `city` VALUES (4, '上海', 1);
-- ----------------------------
-- Table structure for country
-- ----------------------------
DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`country_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of country
-- ----------------------------
INSERT INTO `country` VALUES (1, 'China');
INSERT INTO `country` VALUES (2, 'America');
INSERT INTO `country` VALUES (3, 'Japan');
INSERT INTO `country` VALUES (4, 'UK');
SET FOREIGN_KEY_CHECKS = 1;
如果在一张表中 有主键 系统会自动给主键配置一个主键索引
5.1 创建索引的方法
语法:
create 【UNIQUE|DULLTEXT|SPATIAL】 index index_name
create index idx_city_name on city(city_name);
5.2 查看索引
语法:
show index from table_name;
show index city;
5.3 删除索引
drop index 索引名 on 表名
drop index idx_city_name on city ;
5.4 Alter命令
alter table table_name add 类型(列名)
- 添加一个主键,索引值必须唯一,且不能为NULL
alter table table_name add primary key(column_list);
- 添加唯一索引,值必须是唯一的,且可以为空
alter table table_name add unique index_name(column_list)
- 添加普通索引,索引值可以出现多次
alter table tb_name add index index_name(column_list)
- 索引为FULLTEXT 用于全文索引
alter table tb_name add fulltext index_name(column_list);
5.5 索引设计原则:
- 对查询频次较高,且数据量比较大的表建立索引
- 索引字段的选择,最佳候选列应从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用,过滤效果最好的列的组合
- 使用唯一索引,区分度越高,使用索引的效率越高
- 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就越大,对于插入、更新 、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率,增加响应操作的时间消耗,另外索引过多的话,Mysql也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但是无疑提高了选择的代价
- 使用短索引,索引创建之后也是使用硬盘来存储,因此提升索引访问的IO效率,也可以提升总体的访问效率
- 利用最左前缀,N个列组合而成的组合索引,那么相当于创建N个索引,如果查询时where子句中使用了组成该索引的前几个字段,那么这条查询sql可以利用组合索引来提升查询效率
5.6 创建复合索引
Create index idx_name_email on tb_seller(Name,email,status)
相当于为 Name创建了索引
相当于为 Name email 创建了索引
相当于为 name email status 创建了索引
6. 最左前缀法则(只看包含不 和顺序无关)
如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始,并且不跳过索引中的列;
- 范围查询的后面的索引条件 会失效
- 在索引字段进行运算 索引会失效
- 字符串不加单引号,会造成索引失效
- 尽量使用覆盖索引,避免使用select*
- 如果使用select* 就会进行回掉索引
- using index 使用覆盖索引的时候就会出现
- using where 在查找使用索引的情况下,需要回表去查询所需要的数据
- using index condition 查询使用了索引,但是需要会表查询数据
- using index;using where 查找使用了索引,但是需要的数都在索引列中找到,不需要回表查询
- 用or分割开的条件,如果or前的条件中有列索引,而后面的列中没有索引,那么涉及的索引都不会被用到
- 模糊查询的时候 %加到前面 会引起索引失效(包含主键即可避免覆盖索引可以解决)
- Mysql评估使用索引 如果一条数据比例比较大,那么就可能走全表扫描
- isnull isNot Null 有时索引失效(比例小则走 比例大则不走)
- in会进行索引 但是not int不会
7. 单列索引和复合索引的选择
尽量使用复合索引,而少使用单列索引
复合索引创建的时候 会相当于创建多个索引
单列索引创建过多 数据库会进行最优选择(辨识度最高的)
8. 查看索引的使用情况
show global status like '索引名'
二. 游标
游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环处理,贯标的使用包括:声明 open fetch close
- 声明光标
declare cursor_name CURSOR FOR select_statment;
- open光标
open cursor_name;
-FETCH 光标
Fetch cursor_name Into var_name[,var_name]....
- close光标
close cursor_name;
- 循环游标
三. 触发器
1.介绍
触发器是与表有关的数据对象,旨在insert update delete之前或之后,触发并执行触发器中定义的SQL语句集合,触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的,现在触发器还只支持行级触发,不支持语句级触发
触发器类型 | NEW和OLD的使用 |
INSERT型触发器 | NEW表示将要或者已经新增的数据 |
UPDATE型触发器 | OLD表示修改之前的数据 NEW表示将要或已经修改后的数据 |
DELETE型触发器 | OLD表示将要或已经删除数据 |
2. 创建触发器
语法结构:
create trigger trigger_name
before/after insert/update/delete
on tbl_name
for each row --行级触发器
begin
触发器的主要内容
end
一. 通过触发器记录emp表的数据变更日志 包含增加 修改 删除
create trigger emp_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs(id,operation,operation_time,operation_id,operation_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,',name:',new.name,',age:'new.age,',salary:',new.salary,')'));
end
3. 查看触发器
SHOW TRIGGERS;
4. 删除触发器
drop trigger [schema_name.] trigger_name;
如果没有指定数据库 就默认当前数据库