mysql 中的sql优化

r囧r小猫 2023-10-10 16:12 45阅读 0赞

#### 文章目录 ####

*   *   *   *  \*\*SQL优化\*\*
             *   *  插入数据的优化
                 *  主键优化
                 *   *  \*\*order By优化\*\*
                     *  \*\*group by的优化\*\*
                     *  \*\*分页优化\*\*
                     *  \*\*count优化\*\*
                     *  update 优化

##### **SQL优化** #####

###### 插入数据的优化 ######

**insert**

\*\*批量操作：\*\*数据数量500-1000条

insert into 表 values(),(),()...;

**手动提交事务优化sql**

如何优化的：不影响其他并行线程资源，处理完成后统一提交

start transaction ;
    insert into 表 values(),(),()...;
    insert into 表 values(),(),()...;
    insert into 表 values(),(),()...;
    insert into 表 values(),(),()...;
    commit；

**主键顺序插入**  
后续讲解，见后

-- 主键乱序插入(速度慢)
    -- 主键顺序插入(速度快)

**Load命令**

插入大量数据，百万/千万条数据，这是根据表结构创建的有顺序的数据文件

-- 客户端一次性插入大批量数据，使用insert语句插入性能较低，此时可以使用mysql数据库提供的load命令
    -- 
    mysql --local-infile -u root -p
    -- 设置全局参数，local_infine = 1 ,开启本地加载文件数据的开关
    set global local_infile = 1;
    -- 执行load 指令，准备好数据加载到数据表格中
    local data local infile '/root/sql.log' 'tb_user' fields into terminated by ',' lines termin;

示例

CREATE TABLE `tb_user` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(50) NOT NULL,
      `password` VARCHAR(50) NOT NULL,
      `name` VARCHAR(20) NOT NULL,
      `birthday` DATE DEFAULT NULL,
      `sex` CHAR(1) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `unique_user_username` (`username`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

###### 主键优化 ######

数据组织方式

innoDB存储引擎中 **，表数据都是根据主键顺序组织存放的**，这种存储方式的表称为**索引组织表**，表数据都存放在页中。

**页分裂：当乱序插入时会产生页分裂情况，大大降低了查询效率  
页合并：顺序插入，可以接连合并页**

主键的设计原则：

*  尽量降低主键的长度。空间时间
 *  插入数据时，尽量选择顺寻插入，选择AUTO\_INCREMENT 自增主键。
 *  尽量不要使用UUID做主键或者是其他自然主键，如身份证ID（无需）。
 *  业务操作时，尽量减少主键的修改。修改了主键，很多索引结构都会发生改变。

###### **order By优化** ######

Using fileSort : 指的是 用过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer 中完成排序操作，索引不是通过索引直接排序数据的。此排序被称为 FileSort 排序

Using index : 指的是，通过有序索引顺序扫描直接返回结果。这种情况视为using index ，不需要额外的排序，操作效率高。

> 在构建索引，如聚集索引，覆盖索引 时就将数据存储在叶子节点中，构建了每个字段的顺序。

-- 语法
    -- 创建索引，根据age 升序，phone 降序,在创建索引时就开始排序好徐，索引创建完毕，数据也排好了序
    create index idx_user_age_phone_ad on tb_user (age asc ,phone desc);
    -- 查看详情
    explain select id ,age ,phone from tb_user order by age asc ,phone desc;

*  根据排序字段建立合适的索引，多字段排序是=时，也遵循最左前缀法则。
 *  尽量使用覆盖索引
 *  多字段排序，一个升序，一个降序时，此时需要注意联合索引在创建的时候的规则
 *  如果不避免出现filesort ，大数据量时，可以适当增大排序缓冲区的大小 sort\_buffer\_size (默认256k)。

###### **group by的优化** ######

using tempary : 临时表，创建历临时表来进行分组的话，效率是非常低的

-- 语法
    -- 创建索引
    create index idx_user_profession_age on tb_user (profession ,age);
    -- 根据索引进行分组查询 
    select profession ,count(*) from tb_user group by profession;

在分组操作时，可以通过索引来提高检索的效率

分组操作时，索引的使用也是满足最左前缀法则的

###### **分页优化** ######

一个常见又非常头疼的问题：limit 2000000 ,10 ，此时需要mysql 排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询的代价非常大。

优化思路：一般分页查询时，通过创建索引，覆盖索引能够较好地提高性能，可以通过覆盖索引加子查询形式进行优化

select s.* from tb_user t ,(select id from tb_user limit 9000000,10 ) a where s.id = a.id;

###### **count优化** ######

1.  myISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(\*) 的时候会直接返回这个数，效率很高；
2.  InnoDB 引擎就麻烦了，他执行count(\*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数

**优化思路**

*  count()是一个聚合函数，对于返回的结果，一行行的判断，如果count()函数的参数不是null 累积值就加一，否则不加，最后返回累计值
 *  用法count(\*)，count(字段)，count(1);

count 的几种用法

count(主键)