SQL调优

为什么数据查询速度慢

• 数据量大
• 全表扫描
• 运算

数据量大

        分表分库，以MySQL来说，底层索引使用的是B+Tree，单表数据量应该避免超过2000万，原因是当数据超过两千万的话，树的高度就为3了，影响查询效率。

全表扫描

        添加索引，以MySQL底层索引结构B+Tree来说，索引能够大大减少IO查询次数，可以避免全表扫描。

运算

        避免在SQL语句中进行运算，可以拿到程序中，在内存中进行计算操作。

        

分库分表

分库分表的场景

        1、高并发场景，会造成IO读写频繁，对数据库造成压力，导致读写缓慢甚至宕机。一般情况下单机不要超过2K并发。

        2、大数据量场景，第一、由于底层索引导致，数据量大会导致索引树十分庞大，以MySQL来说，底层索引使用的是B+Tree，单表数据量应该避免超过2000万，原因是当数据超过两千万的话，树的高度就为3了，影响查询效率；第二、innodb最大的存储限制为64TB。

        注意：不是出现上面情况就一定需要分库分表，比如优先应该考虑的是：缓存、读写分离、索引等。

分库分表方式

        垂直拆分

        就是将原表中的字段拆分成多个表，或者多个库上去。每个库表结构都不同，都包含着部分字段。 

        例：原USER表： id        name        age        phone        city        email

       拆分后：USER_BASE：id        name        age

                      USER_INFO：id        phone        city        email

        特点：

                1、每个表的结构都不一样

                2、拆分出的表关联起来就是全量数据

                3、拆分出的表主键是一样的

        水平拆分

        就是将原表中的数据按照一定的规律拆分到不同的表中。

        例：原USER表： id        name        age        phone        city        email

                                     1            张           18          185***       ***        *** 

                                     2            王           25          176***       ***        ***                

                                     3            赵           23          177***       ***        ***

       拆分后：

                USER_I:       id        name        age        phone        city        email

                                     1            张           18          185***       ***        ***           

                                     3            赵           23          177***       ***        ***

                USER_I:       id        name        age        phone        city        email

                                     2            王           25          176***       ***        ***     

        特点：

                1、拆分出的表机构都相同

                2、拆分出表数据的并集是全量数据

水平拆分具体策略

1、Range依据字段范围

• 年份，USER_2024        USER_2025
• 月份，USER_202404        USER_202405 
• 日期，USER_20240425        USER_20240526
• 主键ID，依据ID进行分区，比如1000万条数据，分为四个区，每个区限制存储数据250万条。

缺点：大部分的请求，访问的可能都是最新的数据，所以有可能绝大部分请求还是达到了最新的表、库上了。

2、依据某个字段Hash操作

        比如某用户编号8410216，用户表拆分成64张表，用户编号%64张表

        计算出该用户信息该存储在哪张表。

缺点：如果继续分表扩容的话，就需要做数据迁移，比如原先64张表，扩容到128张表时，就需要重新计算做迁移工作了。

数据迁移问题：如何不影响正常使用做数据迁移，数据迁移都会有一个迁移进度，可以根据迁移进度来判断去新表还是旧表中取数据。

3、List预定义：列表匹配List函数对编号进行分区，比如编号为101，105，108的存储在一个分区，相比于Range，List分区是固定的。

缺点：不易扩展。

如何实现分库分表

   分库分表组件：

• cobar：属于proxy层方案，不支持读写分离、存储过程、跨库Join和分页等操作
• TDDL：属于client层方案，不支持join、多表查询等语法，支持读写分离，使用不多，因为依赖淘宝的diamond配置管理。
• sharding-jdbc：当当开源，属于client层方案，支持SQL语法较多，支持分库分表，读写分离。

分表后的问题

• 分片事务一致性难以解决
• 跨节Join性能差，逻辑复杂 
• 数据多次扩展，难度、维护量大
• 查询所有表集合
• 分布式全局唯一id

索引

什么是索引？

        索引是一个数据结构，相当于字典中的目录，帮助快速查询数据，避免全表查询，同时也会占用磁盘空间来存储索引，并且数据被修改时也要对索引进行重构。

从应用维度来讲都什么什么索引？

• 普通索引
• 主键索引
• 非空索引
• 组合索引
• 全文索引
• 空间索引

从物理存储维度来讲都有什么索引？

• 聚簇索引（索引与数据存储在一起- InnoDB）
• 非聚簇索引（索引与数据分开存储- MyIsam）

从数据结构维度来讲都有什么索引？

• hash索引（优：查询效率高，缺：无法范围查询）
• B+Tree索引（优：可以范围查询，缺：相对来说等值查询较慢）

详细说说B+Tree结构

相比于B-Tree，B+Tree的每个非叶子节点可以包含更多的分支、叶子节点通过双向链表连接，顺序查询性能更高。

操作索引

1. 创建表的同时创建索引
2. 表创建之后创建索引
3. 查看索引
4. 删除索引

--创建表时创建索引
create table table_name(
    id int primary key auto_increment,
    phone varchar(4) not null,
    index index_name(phone) using hash
)engine innodb default character set utf8mb4;

-表创建后创建索引
create index index_name on table(column,column)

alter table table_name add index index_name(column)

-查看表中有哪些索引
show index from table;

-删除索引
drop index index_name on table(column,column)

如何查看是否应用索引

explain select column from table

查询等级排序

system >const（主键形式）>eq_ref >ref >range（范围查询）>index >All,一个好的语句至少要达到range级别，最好达到ref，要杜绝All的出现。

常见可能引起索引失效、查询效率低下的场景

• 条件中存在隐式转换
• Join字段编码不一致
• 不等于判断条件
• like左模糊查询
• in范围查询
• 查询条件中使用函数
• 对索引列进行运算
• 使用or连接条件
• select *

为什么说要product中要杜绝使用select *

1. 增加网络传输开销，虽然单个查询的消耗微乎其微，但架不住量大。
2. 增加服务器内存压力，映射的实体类对象变大。
3. 没有添加索引的字段会进行回表查询，影响效率。

什么是回表查询

        回表查询是数据库中一种常见的查询优化技术。当执行一个SQL查询时，如果查询语句无法直接从索引中获取所需的所有列，就需要进行回表查询。