【优化思路】MySql 优化的详细解释，知识点有点多，请耐心看完

黑口罩

已于 2024-12-20 15:35:40 修改

阅读量406

点赞数

CC 4.0 BY-SA版权

分类专栏：【MySql】系列文章标签： mysql

于 2019-05-18 11:49:46 首次发布

本文链接：https://blog.youkuaiyun.com/xudahai513/article/details/90313381

【MySql】系列专栏收录该内容

10 篇文章

订阅专栏

本文聚焦Linux系统下的MySQL，介绍了安装相关文件及存储引擎查看方法。分析了SQL慢的原因，阐述索引优劣势、添加方式及适用场景。还提及MySQL常见瓶颈，讲解explain关键字使用，介绍索引优化、查询截取分析、提高order by速度的方法，以及慢查询日志和锁的分类。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

id select_type table type possible_key key_len ref rows Extra

MySql安装（Linux 系统）

rm mysql-community-server-minimal-5.7.20-1.el7.x86_64.rpm
yum install -y mysql-community-*rpm
setenforce 0
vi /etc/sysconfig/selinux
getenforce  返回 Permissive
mysql_install_db --datadir=/var/lib/mysql --user=mysql

vi /etc/my.cnf

[root@localhost MYSQL]# systemctl start
Too few arguments.

systemctl start mysqld

//启动mysql
mysql -p -uroot -p

复制表结构+复制表数据
create table t2 like t1;
insert into t3 select * from t1;

chkconfig mysql on        开机自启动
ps -ef|grep mysql  安装目录

frm文件：存放表结构
MYD文件：存放表数据
MYI：存放表索引

查看mysql现在提供的存储引擎
show engines;
查看mysql当前默认的存储引擎
show variables like ‘%storage_engine%’

sql慢的原因

查询语句写的烂
索引失效
单值索引：用该表的某一个字段建的索引
复合索引
关联查询太多join
服务器调优及各个参数设置

索引的优势

提供数据检索的效率，降低数据库IO成本
通过索引列对数据进行排序，降低数据排序成本，降低CPU消耗

索引的劣势

索引是一张表，每次增删改，mysql不仅要保存数据，还要保存索引，就会降低更新的速度
数据量大的时候，需要花大量的时间研究建立优秀的索引

四种方式添加索引

alter table tbl_name add primary key(column_list);添加一个主键，意味着索引值是唯一的，且不能为null
alter table tbl_name add unique index_name(column_list);创建索引的值必须是唯一的（除了null外，null可能会出现多次）
alter table tbl_name add index index_name(column_list);添加普通索引，索引值可出现多次
alter table tbl_name add fulltext index_name(column_list);指定索引为fulltext，用于全文索引

哪些情况需要创建索引

主键字段建立的唯一索引
频繁作为查询条件的字段应该创建索引
外键关系建立索引
在高并发下倾向创建组合索引
查询中排序的字段，通过索引访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不需要创建索引

表记录太少（300万的数据以上）
经常增删改的表
数据重复且分布平均的表字段
范围的数据比如> < ，会导致索引失效

mysql常见瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top，free，iOStat和vmstat来查看系统的性能状态

explain 使用explain关键字可以模拟优化器执行申请了查询语句，从而知道mysql是如何处理你的sql语句，分析你的查询语句或者表结构的性能瓶颈
写法 explain+sql语句

id select_type table type possible_key key_len ref rows Extra

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下
id不同，如果查询子句，id的序号会递增，id值越大优先级越高，越先被执行
id相同和不同同时存在，参照前两种情况

select_type

simple：简单的 select 查询，查询中不包含子查询或者 union
primary：查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
subquery：在 select 或 where 列表中包含了子查询
derived：在 from 列表中保留的子查询标记为derived（衍生） mysql 会递归执行这些子查询，把结果放在临时表里
union：若第二个 select 出现在 union 之后，则被标记为 union，；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived
union result 从union表获取结果的select

table

显示这一行的数据是关于哪张表的

type

(从最好到最差system>const>eq_ref>ref>range>index>all)得保证查询至少达到range级别，最好能达到ref

system 表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略
const 表示通过索引一次就能找到了，const用于比较primary key或者unique索引
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
ref 非唯一索引扫描，返回匹配某个单独指值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range 只检索给你范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 where 语句中出现的 between、>、<、in 等的查询，这种范围扫描索引比全表扫描要好，因为他只需要开始于索引的某个点，而结束语另一点，不用扫描全部索引
index：full index scan,index 与 all 区别为 index 类型只遍历索引树这通常比all快，因为索引文件通常比数据文件下（也就是说虽然 all 和 index 都是读全表，但 index 是从索引中读取的，all 是从硬盘读取的）
all：full table scan 遍历全表

possible_key

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将别列出，但不一定被查询实际使用

key

实际使用的索引。如果为null，则没有使用索引，查询找那个若使用了覆盖索引，则该索引仅出现在key中

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

显示索引的那一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数（越小越好）

extra：（前三个最重要）

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引进行读取；mysql无法利用索引完成的排序成为“文件排序”
Using temporary:使用临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现usering where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
Using where 使用where过滤
using join buffer 使用了连接缓存
impossible where： where子句的值总是false，不能用来获取任何元组

join语句的优化
尽可能减少join语句中的 nestedLoop 的循环次数“永远用小结果集驱动大结果集”

索引优化（索引失效）

全值匹配
最佳左前缀法则（如果索引了多列，查询从索引的最左前列开始，并且不跳过索引中的列）
不在索引列做任何操作
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））
mysql 在使用不等于的时候无法使用索引，导致全表扫描
is null,is not null 也无法使用索引
like 以通配符开通（‘%abd..’）mysql 索引失效会变成全表扫描；如果非要是%号，可以用覆盖索引解决这个问题，即查询字段全部建了索引，顺序跟复合索引一致
字符串不加单引号，索引失效
少用 or，用它来连接会索引失效

查询截取分析

观察，至少跑一天，看看生产的慢 SQL 情况
开启慢查询日志，设置阀值，比如超过5秒钟就是慢 SQL，并抓取出来
explain + 慢SQL分析
show profile 查询 SQL 在 mysql 服务器里面的执行细节和生命周期情况
运维经理 or DBA，进行SQL数据库服务的参数调优

当B表数据集必须小于A表数据集时，用in优于exists
当A表数据集必须小于B表数据集时，用exists优于in

提高order by速度

Order by 时 select* 是一个大忌只query需要的字段，这点非常重要，在这里的影响是：
- 当query的字段大小总和小于 max_length_for_sort_data 而且排序字段不是TEXT|BLOB类型时，会用改进后的算法--单路排序，否则用老算法--多路排序
- 两种算法的数据都有可能超过sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size.
尝试提高sort_buffer_size
不管用哪种算法，听这个参数都会提高效率，当然，要根据系统的能力入提高，因为这个参数是针对每个进程的
尝试提高 max_length_for_sort_data 提高这个参数，会增加用改进算法的概率，但是如果设的太高，数据总容量超过 sort_buffer_size 的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

mysql 的慢查询日志

是 mysql 提供的一种日志记录，它用来记录在mysql中响应时超过阀值的语句，

具体指运行时间超过 long_query_time 的sql，则会被记录到忙查询日志中
具体指运行时间超过 long_query_time值的SQL，则会被记录到慢查询日志中。

long_query_time默认值为10，意思是运行10秒以上的语句，查看哪些sql超过了最大忍耐时间值，比如一条sql执行超过5秒钟，就算慢SQL，收集超过5秒的SQL，结合之前的explain进行全面分析

默认情况下，没有开启慢查询日志，需要手动设置，如果不是调优需要的话，一般不建议启动改参数，因为开启会影响性能

开启：默认show variables like ‘%slow_query_log’
    开启 set global slow_query_log=1;

开启profiling
    show variables like 'profiling'
    set profiling=on；
    查看结果 show profiles；
    诊断sql show profiles cpu,block io for query 上一步数字号码；    
    永远不要在生产环境开启这个功能