MYSQL-mysql数据库性能及查询优化

水深无声 2022-10-29 09:18 43阅读 0赞

# MYSQL-mysql数据库性能及查询优化 #

**1. 查询下mysql服务器的状态**

/* mysql数据库的性能在mysql长时间运行，并且有大量用户进行频繁操作时得以体现。 查询优化：如果mysql数据库需要进行大量的查询操作，则需要对查询语句进行优化。 mysql数据库优化：如果连接mysql数据库的用户很多，则需要对mysql数据库进行优化。大量用户同时连接mysql数据库，可能会造成数据库系统崩溃。 */
    SHOW STATUS LIKE 'Connections'; -- 连接mysql数据库的次数
    SHOW STATUS LIKE 'Uptime';   -- mysql服务器的上线时间
    SHOW STATUS LIKE 'Slow_queries';   -- 慢查询的次数
    SHOW STATUS LIKE 'Com_select';  -- 查询操作的次数
    SHOW STATUS LIKE 'Com_insert';  -- 插入操作的次数
    SHOW STATUS LIKE 'Com_update';  -- 更新操作的次数
    SHOW STATUS LIKE 'Com_delete';  -- 删除操作的次数
    SHOW STATUS LIKE 'Innodb_rows%'  -- 增删改查涉及的行数

**2. 查询语句的执行计划**

-- EXPLAIN | DESCRIBE select语句
    EXPLAIN select e.ename,e.empno,d.dname from emp e inner join dept d on e.deptno=d.deptno;  -- 方法1
    DESCRIBE select e.ename,e.empno,d.dname from emp e inner join dept d on e.deptno=d.deptno;  -- 方法2
    /* id：指出在整个查询中select的位置 table：存放查询的表名 type：连接类型 possible_keys：为了提高查询速度，在mysql中可以使用的索引 key：实际使用的索引 rows：需要在对应的表中查找多少行才能返回查询结果 Extra：其他信息 */

**3. 索引对查询速度的影响**

EXPLAIN select * from emp where empno='7521';
    -- 可以看到，上述查询语句中使用了主键索引，rows=1，即只需要查询一行即可返回查询结果。
    
    EXPLAIN select * from emp where ename='WARD';
    -- 该语句没有使用索引，rows=14，即需要进行全表扫描才能查出对应的结果。
    
    create index ind_ename on emp(ename);
    EXPLAIN select * from emp where ename='WARD';
    -- 给ename列加上索引，再次查询，可以看到已经使用了索引ind_ename，rows=1，比不使用索引效率高很多

**4. 如何使用索引进行查询**

-- like关键字优化索引：前面不能是模糊查询，是的话则不使用索引
    EXPLAIN select * from emp where ename like'A%';    -- 使用索引
    EXPLAIN select * from emp where ename like'%A%';   -- 不使用索引
    EXPLAIN select * from emp where ename like'_A%';   -- 不使用索引
    
    
    -- 多列索引的使用：对于多列索引，只有在使用多列的第一个字段时才会使用索引，使用其他字段不会使用索引
    create index ind_ename_job on emp(ename,job);
    EXPLAIN select * from emp where ename='JONES';  -- 使用索引
    EXPLAIN select * from emp where job='MANAGER';  -- 不使用索引
    
    
    -- 查询语句中使用关键字or不使用索引，可以改为union 或 union all 优化索引
    show index from emp;  -- 查询表有哪些索引
    
    EXPLAIN select * from emp where ename='WARD' or job='SALESMAN';  -- 不使用索引
    
    EXPLAIN
    select * from emp where ename='WARD'   -- 使用索引
    UNION
    select * from emp where job='SALESMAN';

**5. 优化数据库结构：**

*  对于**字段特别多**且**某些字段的使用频率很低**的表，可以将其分解为多个表。
 *  **增加中间表**：对于经常需要关联某些大表来查询的几个字段，建议做成中间表来提高查询速度。（`insert | replace into table select 语句`）
 *  **优化插入记录的速度**：

1. 在批量插入数据前，先禁用索引，等数据插入后，再启用索引。
    alter table emp disable keys;  -- 禁用某个表的所有索引
    alter table emp enable keys;   -- 启用某个表的所有索引
    
    2. 在批量插入数据前，禁用唯一性检查，等数据插入后，再启用唯一性检查
    set unique_checks=0  -- 禁用
    set unique_checks=1  -- 启用
    
    3. 优化insert语句，方法1较于方法2，减少与数据库之间的连接，速度较快。
    -- 方法1【建议使用这种】：
    insert into table_name values
    (),
    (),
    ();
    
    -- 方法2：每次插入都要与数据库建立连接
    insert into table_name values();
    insert into table_name values();
    insert into table_name values();

*  **分析表、检查表、优化表**

ANALYZE table emp;  -- 分析表，分析关键字的分布，加只读锁，只能分析InnoDB 和 MyISAM类型的表
    
    CHECK table emp [option];  -- 检查表，检查表是否存在错误，加只读锁，只能分析InnoDB 和 MyISAM类型的表
    -- option可以选quick、fast、changed、medium、extended，只适用于MyISAM表
    
    OPTIMIZE table emp;  -- 优化表，消除删除或者更新造成的空间浪费，加只读锁，适用于列的数据类型为text和blob类型的数据表
    
    -- Table：操作的表名
    -- Op：操作名，analyze表示进行分析操作、check表示进行检查操作、optimize表示进行优化操作
    -- Msg_type：信息类型，通常是状态、警告、错误或信息。
    -- Msg_text：具体信息内容

**6. 查询高速缓存**

-- 优化查询缓存
    show variables like'%query_cache%';
    have_query_cache  -- 在默认情况下，是否已经配置了查询缓存，可以看到是配置了的
    query_cache_size  -- 查询缓存分配的空间大小，单位KB
    query_cache_type  -- 判断查询缓存开启状态，0/OFF即关闭查询缓存、1/ON即开启查询缓存、2/DEMAND即要用SQL_CACHE/SQL_NO_CACHE选项决定是否开启查询缓存
    select SQL_CACHE * from emp;  -- 开启查询缓存

**7. 优化多表查询**

https://blog.csdn.net/weixin_36522099/article/details/103603956

**8. 优化表设计**

*  设计数据表时优先考虑**定长字段**，再考虑**变长字段**。【在InnoDB或BDB类型表中使用定长字段，不会提升性能。】
 *  可以使用**优化表**的方式去优化**经常需要更新和删除记录**的表。
 *  适当**分解表**，加快查询速度；适当**合并表**，提升数据库性能。

**9. sql语句各子句执行顺序**

from
    join
    on
    where
    group by
    avg,sum,max,min,count
    having
    select
    distinct
    order by
    limit

**10. 拓展一些优化技巧**

1. in子句包含的值要少，对于连续值，使用between效率更高
    SELECT * FROM sys_user WHERE ACCOUNT IN(1,2,3);
    SELECT * FROM sys_user WHERE ACCOUNT BETWEEN 1 AND 3;   -- 效率更高
    
    2. 对于列数较多的表，不建议直接使用 SELECT * 获取所有列，建议 ：select 字段名
    select empno,ename from emp;
    
    3. 如果只需要1条或少量记录来查看数据的基本情况，建议使用limit n子句
    select empno,ename from emp limit 5;
    
    4. 使用 ORDER BY 排序，排序列最好是用到索引的，如果没有用到索引，尽量少排序。
    
    5. 尽量用 UNION ALL 替代 UNION ， UNION ALL 不去重直接将结果集合并， UNION 将结果集合并后还要去重，对于数据量较大的表要做唯一性过滤（去重），涉及排序，增加大量的cpu运算，加大资源消耗及延迟。
    
    6. RAND()函数可以产生0-1之间的随机数，根据该列排序，效率极低
    SELECT ACCOUNT,RAND() FROM sys_user ORDER BY RAND() LIMIT 100;   -- 效率低
    
    7. 注意：in和exists     not in和not exists的区别
     可以参考：https://blog.csdn.net/weixin_36522099/article/details/103639308
     
    8. 合理使用分页方式以提高分页的效率
    select * from emp limit 3,2;  -- 第3行开始的后两行，即第4第5行