MySQL优化

MySQL优化

概念

1. 在应用的的开发过程中，由于初期数据量小,开发人员写 SQL 语句时更重视功能的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题．对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化．
3. MySQL 的优化方式有很多，大致我们可以从以下几点来优化 MySQL:
4.         ① 从设计上优化
5.         ② 从查询上优化
6.         ③ 从索引上优化
7.         ④ 从存储上优化

复制代码

定位低效率执行SQL

1. ① 通过慢查询日志定位那些执行效率较低的SQL语句
2. ② show processlist 该命令查询当前mysql在进行的线程,包括线程的状态,是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操作进行优化.

复制代码

分析执行计划-Explain

1. 通过explain命令获取mysql如何执行select 语句的信息,包括在select语句执行过程中表如何连接和连接的顺序.

复制代码

字段含义idselect查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序select_type表示select的类型,常见的类型有simple(简单表,即不使用表连接或子查询)、primary(主查询,即外层的查询)、union(union中的第二或者后面的查询语句)、subquery(子查询中的第一个select)等table输出结果集的表type表示表的连接类型,性能由好到差的连接类型为(system–>const–>eq_ref–>ref–>ref_or_null–>index_merge–>index_subquery–>range–>index -->all)possible_keys表示查询时可能使用的索引key表示实际使用的索引key_len索引字段的长度rows扫描行的数量extra执行情况的说明和描述分析执行计划-Explain之id

1. ① id相同表示加载表的顺序从上倒下
2. ② id不同 id值越大 优先级越高 越先被执行
3. ③ id有相同 也有不同 同时存在 id相同的可以认为是一组,从上往下执行,在所有的组中,id的值越大,优先级越高,越先执行

复制代码

分析执行计划-Explain之select_type

1. simple:没有子查询和union
2. primary:主查询,也就是子查询中的最外层查询
3. subquery:在select 和where中包含子查询
4. derived:在from包含子查询,被标记为临时表
5. union:存在union语句

复制代码

分析执行计划-Explain之type

type含义NULLMySQL不访问任何表,索引,直接返回结果system系统表,少量数据,往往不需要进行磁盘IO,如果是5.7及以上版本的话就不是system了而是all,即使只有一条记录const命中主键或者唯一索引,被链接的部分是一个常量值eq_ref对于前表的每一行,后表只有一行被扫描.①join查询,②命中主键或者非空唯一索引③等值连接ref非唯一索引扫描,返回匹配某个单独值的所有行,对于前表的每一行,后表可能有多余一行的数据被扫描range只检索给定返回的行,使用一个索引来选择行,where 之后出现between,,in等操作index需要扫描索引上的全部数据all全表扫描,此时id上无索引结果值从好到坏依次是:system > const > eq_ref > ref > range > index > all

1. eg:
2. -- NULL
3.         select now();select rand(); ....等等函数
5. -- system
6.         select * from mysql.tables_priv
8. -- const
9.         select * from user where uid=2;
11. -- eq_ref 左表有主键,而且左表的每一行和右表的每一行刚好匹配
12.     select  * from a,b where a.id=b.id; -- a表id为主键
13.    
14. -- ref  左表是普通索引,和右表匹配时可能会匹配多行
15.         select  * from a,b where a.id=b.id; -- a表id为普通索引
16. -- range
17.         select  * from user where id > 2;
19. -- index 扫描索引列
20.         select id from user;
22. -- all
23.         select * from user;

复制代码

分析执行计划-Explain之table

1. 显示这一步所访问数据库中表名称,有时不是真实的表名,可能是简称

复制代码

分析执行计划-Explain之rows

1. 扫描行的数量

复制代码

分析执行计划-Explain之key

1. possible_key:显示可能应用在这张表的索引,一个或者多个
2. key:实际使用的索引,如果是null,则没有使用索引
3. key_len:表示索引中使用的字节数,改值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好.

复制代码

索引优化

索引是数据库优化最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题
避免索引失效应用

1. ① 避免索引失效应用-全值匹配
2. ② 避免索引失效应用-最左前缀法则
3. ③ 避免索引失效应用-其他匹配法则
4.         范围查询右边的列,不能使用索引
5.         不要在索引列上进行运算操作、函数,索引将失效
6.         字符串不加单引号,造成索引失效
7.        
8. -- 效率低 需要从原表以及磁盘上读取数据
9. select * from t_user
10. -- 效率高 从索引树中就可以查询到所有数据
11. select name from t_user
12. -- 用or分隔开的条件,那么涉及的索引都不会被用到
13. -- 以%开头的like模糊查询,索引失效 弥补不足就是select 后面跟索引列
14.         eg: select name from t_user where name like '%你%' -- 会使用索引
15. -- 如果mysql评估使用索引比全表更慢,这不使用索引,这种是由数据本身决定的,
16.         eg:user表的address字段有索引,100条数据其中99条address字段的值都是北京
17.         则select * from user where address='北京'; 索引失效.
18. -- is null,is not null 有时有效,有时索引失效
19.         select * from user where address is null; -- 走索引,因为数据中为空的数据少,所以走索引,反之当address为空的数据多时候,就不走索引了. is not null 同理相反而已
20. -- in 搜索引 not in 索引失效
21. -- 如果一张表有多个单列索引,即使where中都使用了这些索引列,则只有一个最优索引生效.

复制代码

① 大批量插入数据,保证主键的顺序性,会提高插入效率
② 理论上连接查询要比子查询的效率高,一些子查询还要在内存中创建临时表.
真诚地邀请您加入我们的大家庭,在这里不仅有技术知识分享,还有博主们之间的互帮互助.还不定期发红包,每月更有抽奖环节,游戏机和实体书相赠(包邮),让我们抱团取暖,抱团内卷.打造美好C站.期待您的加入.


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！