关于数据库调优，存储过程的了解

数据库调优：

分为四个维度进行优化

1.硬件配置

2.参数配置

3.表结构设计

4.SQL语句及索引

一：硬件配置

1.对磁盘进行扩容

2.硬盘换为SSD，把CPU的核数往上提升增强数据库计算能力

3.内存扩容，让Buffer Pool(缓冲池)存入更多的数据

二：参数配置

1.保证从内存的读取

--查看缓冲池的大小
show global status like 'innodb_buffer_pool_pages_%';

修改为总内存的 60%~80% ，当然不能影响系统程序的运行。

这个参数是只读的，可以在 MySQL 的配置文件（my.cnf 或 my.ini）中进行修改。Linux 的配置文件为 my.cnf。

2.数据预热

将磁盘中的数据提前放入BufferPool内存缓冲池内

3.降低磁盘的写入次数

3.1增大redo log，减少落盘次数

redo log是重做日志，用于保护数据的一致性，减少落盘相当于减少了系统 IO 操作。

3.2通用查询日志，慢日志可以不开，binlog可开启

通用查询和慢查询日志也是要落盘的，binlog 用于恢复和主从复制

# 慢查询日志
show variables like 'slow_query_log%'
# 通用查询日志
show variables like '%general%';
# 错误日志
show variables like '%log_error%'
# 二进制日志
show variables like '%binlog%';

三：系统调优参数

back_log：栈堆的数量。MySQL的连接数据达到最大值时，新的请求将会被存放在栈堆中，以等待某一连接释放资源。等待连接的数量超过back_log将不被授予连接资源。默认：50-500

wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。默认：8h-0.5h

max_user_connection：最大连接数。默认：0-无上限

thead_concurrency：并发线程数，设为CPU核数的两倍

skip_name_resolve:禁止对外部连接进行DNS解析

key_buffer_size:索引块的缓存大小，增加会提升索引处理速度

innodb_buffer_pool_size:缓存数据块和索引块

innodb_log_buffer_size:事务日志所使用的缓冲区

read_buffer_size:读入缓冲区大小

sort_buffer_size:排序使用的缓存大小

read_rnd_buffer_size:随机读缓冲区大小

四：表结构设计

1.设计聚合表：针对于统计分析功能或者实时性不高的需求（空间+时延性换时间）

2.设计冗余字段：为减少关联查询，创建合理的冗余字段。冗余字段过多对系统复杂度和插入性能会有影响

3.分表：垂直拆分：适用于字段太多的达标

​ 水平拆分：解决查询性能问题，数据写操作的热点征用问题

4.字段的设计:

​ 使用可以存下数据最小的数据类型

​ 尽可能使用not null定义字段

​ 尽量少用text类型，最好考虑拆表

五：SQL语句及索引

索引创建和使用原则：

​ 单表查询：哪列作为查询条件，该列创建索引

​ 多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段

​ 不对索引列进行任何操作（计算，函数，类型转换）

​ 索引列不使用符号（！=，<>）

​ 字符字段只建前缀索引，最好不做主键

​ 不用外键，由程序保证约束

​ 索引列不要为空，且不使用is null或is not null判断

​ 索引字段是字符串类型，查询条件的值要加’'单引号，避免底层类型自动转换

sql语句尽可能简单：

​ 一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库

对于连续值使用betwee不用in

sql语句中in包含的值不应过多

select语句指定字段名称

排序字段加上索引

如果限制条件中其他字段没有索引，尽量少用or

尽量用union all代替union

使用合理的分页方式以提高分页效率

避免使用%前缀模糊查询

避免在where字句中对字段进行表达式操作

避免隐式类型转换

使用联合索引时注意范围查询

小表驱动大表

主从:读写分离模式：所有写操作和实时性要求较高的by id查询走主库

​ 链路隔离模式：写操作和核心操作对应sql走主库

分库：基于业务场景指定（按照年月分库和按照角色分库）

数据库调优总结

1.确保合适的数据库设计：即正确的表结构设计，适当的数据类型选择，合理的表关联关系

2.优化查询语句：避免使用过多的join操作，避免使用全表扫描等低效的操作

3.创建合适的索引：索引的创建可以提高查询速度，但过多的索引会增加数据修改的开销

4.配置合理的缓存：配置合适的缓存大小，减少磁盘IO的开销

5.调整数据库参数：对于不同的数据库系统，通过调整数据库参数来提升性能。内存设置，并发连接数，线程池大小

6.分区和分表：将数据分散存储在多个磁盘上，提高I/O并行性能

7.定期数据清理和优化：定期清理无用数据和索引，进行数据库的表压缩，重建，统计信息更新

8.定位和解决性能瓶颈：通过数据库性能监控工具来定位数据库性能瓶颈

数据库常见索引：

1.B数索引：最常见和最基本的索引类型。键值对顺序组织成一种树型结构，可以快速定位和查找特定的键值

2.唯一索引：确保索引的值是唯一的，用于避免数据库表中重复数据。加快唯一性检查，并提高查询性能

3.主键索引：一种特殊的唯一索引，用于标识表中的主键。主键索引可加快主键和关联查询的性能

4.复合索引：基于多个列的索引，通过多个列的组合来加快查询。适用于多个列的联合查询，排序和过滤

5.全文索引：用于非结构化文本数据（文章，文件）的全文搜索。提供高效的文本搜索和模糊匹配功能

6.空间索引：引用与处理具有空间特性的数据（地理信息系统GIS中的地理数据）。加速空间查询和空间关系的计算

7.哈希索引：使用哈希函数将索引键映射为索引项的物理地址。适用于等值查询，但不支持范围查询和排序

8.位图索引：基于位图的数据结构，适用于低基数列的查询。加速高选择性的条件查询，交集和并集查找

数据库存储过程：

一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数来调用

存储过程是可编译的函数，在数据库中创建并保存，可由SQL语句来控制结构组成

优点：

​ 存储过程可封装，并隐藏复制的商业逻辑

​ 存储过程可以回传值，并接受参数

​ 存储过程无法使用SELECT，子程序与查看表，数据表和用户定义函数不同

​ 存储过程用在数据检验，强制实习商业逻辑

缺点：

​ 存储过程，定制与特定的数据库（支持的编程语言不同）

​ 存储过程的性能调校与撰写受限与各种数据库系统

集群中组件的依赖关系：

zookeeper ->hdfs\yarn\hive\hbase\hue
hdfs->yarn\hbase\impala
yarn->spark\hive
hbase->hive\spark\impala\hue
spark->hive
hive->hue\impala
kudu->impala
impala->hue
hue