系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

SQL性能优化方法及性能测试

时间:2022-05-17来源:www.pcxitongcheng.com作者:电脑系统城

目录
  • 笛卡尔连接
  • 分页limit的sql优化的几种方法
  • count 优化方案

笛卡尔连接

例1: 没有携带on的条件字句,此条slq查询的结构集等价于,a表包含的条数*b表包含的乘积:

1 select * from table a cross join table b;

例2:拥有携带on字句的sql,等价于inner join

1 select * from table a cross join table b on a.id=b.id;

分页limit的sql优化的几种方法

规则;表包含的数据较少的数据量,作为驱动表(小表驱动大表,一般mysql的优化器会做出相应的优化的,但是为了防止一些抽风现象可以用STRAIGHT_JOIN,作用会强制使用左边的表作为驱动表)。

例1:

1 select * from table c straight_join table d on c.id=d.id;

覆盖索引:

1 select 主键字段或者创建过索引的字段 from table limit 300000,10

索引覆盖+inner (业界常用的优化方案)

1
2
3
4
select * from table a
inner join (
select 创建索引的字段 from table  limit 30000,10) b
on b.创建索引的字段=a.创建索引的字段 (也可以更换为 using (创建索引的字段))

索引覆盖+子查询 先获取分页起始的最小值,然后再获取后10条 (业界常用的优化方案)

1
2
3
4
5
select * from table
where 主键字段或者创建过索引的字段
                  >=
(select 主键字段或者创建过索引的字段 from table 300000,1)
limit 10;

范围查询+limit语句 获取上一页的主键最大值,然后进行获取后面的数据;

例1; 上一页的最大主键值为100

1
2
3
select * from table
   where id > 100
   limit 10;

需要获取起始主键值和结束主键值

1
2
select * from table
          where id between 起始主键值 and 结束主键值;

禁止传入过大的页码 (例如;百度就是采用这种方式)

count 优化方案

实例1:

1
2
3
4
5
6
7
8
9
10
11
12
    /**
    * 1:如果不包含非主键的索引,就会使用主键索引
    * 2:如果包含非主键的索引就会使用非主键索引;
    * 3:如果存在多个非主键索引,会使用key_len值较小的索引
    * 为什么会有这种规律呢?
    *  -innodb非主键索引:叶子结点储存的是:索引+主键
    *   主键索引叶子结点储存的是:主键+表数据
    *    在1page里面,非主键索引可以存储更多的条目,对于一张表,假如拥有10000000数据
    *    使用非主键索引,扫描page 500,主键索引 100  非主键索引扫描的条目多,可以减少扫描的次数
    *
    **/
select count(*) from table

实例2:

1
2
3
4
5
6
7
8
9
10
11
    /**
     * count(字段) 只会针对该字段进行统计,使用这个字段上的索引(如果包含索引的情况)
     * count(子段) 会排出字段值为null的数据
     * count(*) 不会排出字段值为null的数据
     * count(*) 和 count(1) 没有区别
     * 对于MyISAM引擎,如果 count(*) 没有where条件,查询效率会特别的快,因为把数据存储到MyISAM引擎里了
     * 对于MySQL 8.0.13,InnoDB引擎,如果count(*) 没有where条件查询速度,也是特别的快,做出了相应的优化
     *
     *
    **/
select count(某个字段) from table 会把此字段的值为null过滤掉,仅仅只统计字段值不为null

实例3:

1
2
3
//做完本条查询,去执行count的操作
select sql_calc_found_rows * from table limit 0,10;
select found_rows() as count ;  通过此sql来获取count的结果(须在终端进行执行)

注意:缺点在mysql8.0.17这种用法已经被废弃,未来会被永久删除

实例4:优点不操作具体的表,无论表的数据量有多大,都可以迅速执行. 缺点:统计的是一个估算值,适合要求统计数的精度不是太高的场景。

1
2
3
4
5
select * from information_schema.TABLES
where
   TABLE_SCHEMA='数据库名称'
and
   TABLE_NAME ='表的名称';

实例5: //优点不操作具体的表,无论表的数据量有多大,都可以迅速执行. 缺点:统计的是一个估算值,适合要求统计数的精度不是太高的场景。

1 show table status where NAME='表的名称隔行'

实例6: //优点不操作具体的表,无论表的数据量有多大,都可以迅速执行. 缺点:统计的是一个估算值,适合要求统计数的精度不是太高的场景。

1 explain select * from table

实例7: 优化案例; 目前有一张数量非常大的表,需要统计id值大于100的有多少条

  • 一般写法:select count(*) from table where id>100;
  • mysql8.18版:逆向思维的写法: select count()-(select count() from table where id <100) from table
  • order by 的优化:原则利用索引,避免排序。
1 //first_name,last_name已经在表里创建了组合索引,emp_no为主键;

实例1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
//此sql是不能利用到索引的,原因是:mysql的优化器,是根据成本计算的,如果全表扫描比使用索引,成本更低时会使用全表扫描
//如何鉴定是否使用索引避免了排序呢? 通过explain 查看sql的性能如果Extra的值为null时,说明是可以通过索引避免排序的.如果Extra的值是Using filesort 是不可以进行索引排序的
select * from table order by first_name,last_name;
//此sql可以使用索引避免排序的
select * from table order by first_name,last_name limit 10;
//此sql可以使用索引避免排序的
/**
  *[Bader,last_name,emp_no]
  *[Bader,last_name,emp_no]
  *[Bader,last_name,emp_no]
  *[Bader,last_name,emp_no]
  *
**/
select * from table where fist_name='Bader' order by last_name;
//此sql可以使用索引避免排序的
/**
  *[Bader,last_name,emp_no]
  *[Ba,last_name,emp_no]
  *[Bad,last_name,emp_no]
  *[Bade,last_name,emp_no]
  *
**/
select * from table where fist_name<'Bader' order by last_name
//此sql可以使用索引避免排序的
 select * from table where fist_name='Bader' and last_name>'Peng' order by last_name
 //此sql可以使用索引避免排序的,原因排序的俩个字段,分别存在俩个索引中
 select * from table  order by first_name,emp_no;

索引失效的场景:

  • 1: join 字段的类型不一致
  • 2: 在=号的左边,进行加减操作

实例1:

1
2
3
4
select * from employees e
left join dept_emp de on e.emp_no=de.emp_no
left join departments d on de.dept_no=d.dept_no
where e.emp_no=1001;

拆分后:

1
2
3
select * from employees where emp_no='1001';
select * from dept_emp where emp_no='1001';
select * from departments where dept_no='d005';

表的设计原则-三范式:

  • 范式:表的字段都是原子性,既每个表的字段都是不可分割的,不是集合,数组,记录等非原子数据项。
  • 范式:在第一范式的基础上,每一行数据的唯一性,非主键字段要完全依赖于主键字段。
  • 范式:在满足第二范式的基础上,不能存在传递依赖。
分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载