数据库优化（索引优化）

一. 索引：

1.索引的概述

索引（index）：是帮助mysql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这些数据结构就是索引，如下图所示：

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上，索引是数据库中用来提高性能的最常用的工具。

2. 索引的优势和劣势

• 优势：

1. 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本
2. 通过索引列对数据库进行排序，降低数据排序的成本，降低cpu的消耗

• 劣势：
  1. 实际上索引也是一张表，该表中保存了主键和索引字段，并指向实体类的记录，索引列也是要占用空间的
  2. 虽然索引大大提高了查询效率，同时降低更新表的速度，，如果对表进行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特性如下：

1. 树中每个节点最多包含m个孩子
2. 除根节点与叶子结点外，每个节点至少有（cell(m/2)）个孩子
3. 若跟节点不是叶子结点，则至少有两个孩子
4. 所有的叶子结点都在同一层
5. 每个非叶子结点由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的区别为“

1. n叉B+Tree最多含有n个key，而Btree最多含有n-1个key
2. B+Tree的叶子结点保存所有的Key信息，依key大小顺序排序
3. 所有的飞叶子结点都可以看做是key的索引部分
   
   B+Tree只有叶子结点可以保存key的信息，查询任何key都要从root走到叶子，所以B+Tree的查询效率更加稳定
   
   叶子结点相邻的叶子结点有了指针，主要是为了范围搜索

4. 索引的分类：

1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，但允许有空值
3. 复合索引：即一个索引包含多个列

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 类型（列名）

1. 添加一个主键，索引值必须唯一，且不能为NULL

alter  table table_name add primary key(column_list);

2. 添加唯一索引，值必须是唯一的，且可以为空

alter table table_name add unique index_name(column_list)

3. 添加普通索引，索引值可以出现多次

alter table tb_name add index index_name(column_list)

4. 索引为FULLTEXT 用于全文索引

alter table tb_name add fulltext index_name(column_list);

5.5 索引设计原则：

1. 对查询频次较高，且数据量比较大的表建立索引
2. 索引字段的选择，最佳候选列应从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用，过滤效果最好的列的组合
3. 使用唯一索引，区分度越高，使用索引的效率越高
4. 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就越大，对于插入、更新 、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加响应操作的时间消耗，另外索引过多的话，Mysql也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但是无疑提高了选择的代价
5. 使用短索引，索引创建之后也是使用硬盘来存储，因此提升索引访问的IO效率，也可以提升总体的访问效率
6. 利用最左前缀，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;
如果没有指定数据库  就默认当前数据库