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

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行