mysql索引优化

以你之姓@ 2022-12-13 14:20 178阅读 0赞

## mysql常见的瓶颈 ##

cpu：CPU饱和一般发生在数据装入内存或从磁盘上读取数据的时候

IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

## 数据库查询慢该怎么办： ##

1.  先执行show status like '%innodb\_row\_lock%'看看
2.  然后打开慢查询日志，设定到3秒以上跑一下，在慢查询日志中会记录下 
3.  在这条sql前加explain查索引是否实现
4.  在用show profile 查看资源消耗情况，是否有临时表产生

## Explain ##

Mysql中有专门负责优化SELECT语句的优化器模块，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈，具体包含：表的读取速度，数据读取操作的操作类型，哪些索引可以被使用，哪些索引被实际使用，表之间的引用，每张表有多少行被优化器查询

![20201012175842879.png][]

*  id：在一个大的查询语句中每个select关键字都对应一个唯一的id，id越大优先级越高
 *  select\_type：select 关键字对应的查询是类型，普通查询，联合查询，子查询等
 *  table：显示这一句sql是关于哪种表
 *  type：显示查询使用了何种类型  system>const>eq\_ref>ref>range>index>all  一般要求至少达到range
 *  possible\_keys：可以应用到这种表中的索引，一个或多个
 *  key：实际使用的索引，如果为null则没有用到索引
 *  key\_len：表示索引中使用的字节数，不影响精度的情况下，越小越好（节省空间，减少磁盘IO）
 *  ref：索引是怎么用的，const表示赋予clo2常量， shared.t2.col1 :库名.表名.列名  表示col1被当做主外键的连接条件
 *  rows：大致估算出吵到所需记录 要读取的行数，越小越好
 *  extra：其他额外的信息，

Using filesort : 无法使用到索引完成排序操作，不行 ，

Using temporary : 使用了临时表，不行

Using index : 表示相应的select操j作使用了覆盖索引，避免了访问表的数据行，效率不错

## 索引失效 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70][]

*  遵循最佳左前缀法则，不在索引列上做任何操作（计算，函数，自动或者手动类型转换）否则索引会失效转向全表扫描
 *  存储引擎不能使用索引中范围条件右边的列，between and 会导致索引失效
 *  is null , is not null , like 以通配符开头（'%abc...'）索引会失效，只有'abc%'这种不会失效
 *  如果非要使用'%abc%'且不想让索引失效，可以通过覆盖索引的方式来解决
 *  or语句会导致索引失效，可以用in语句来代替（in最多200个值否则会出问题）
 *  大于小于号会导致索引失效

// 都可以用到索引，mysql会自动优化，但是不推荐
    explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
    explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

*  order by 导致索引失效的情况：

// 只有c1 和 c2 用到了索引，c3作用在排序而不是查找 
    explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
    
    // 都只是c1和c2索引起作用，但是下面这条出现了filesort
     explain select * from test03 where c1='a1' and c2='a2' order by c3;
     explain select * from test03 where c1='a1' and c2='a2' order by c4;
    
    // 只用c1一个字段索引，但是c2、c3用于排序,无filesort
    explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;
    
    // 出现了filesort，我们建的索引是1234，它没有按照顺序来，3 2 颠倒了
    explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
     
    // 本条是可以的，本例有常量c2的情况，和上面一条比较
    explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;

## order by 使用索引排序 ##

永远是小表驱动大表

mysql支持两种排序方式：fileSort和Index,Index效率高

order by 子句，尽量使用index方式排序，避免使用FileSort方式排序

order by语句使用索引最左前列，使用where子句与order by子句条件组合满足索引最左前列

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 1][]

## 为什么不适用select \* ##

*  mysql拿到一条命令，会去解析命令、优化查询，然后去存储引擎执行查找.SELECT \* 语句取出表中的所有字段，会解析更多的 对象，字段，权限，属性相关，这会对服务器资源造成浪费，导致优化和效率问题,对服务器的性能产生一定的影响，例如：采用order by 排序造成不必要的字段也进行了排序，影响效率
 *  使用SELECT \* 语句将不会使用到覆盖索引，不利于查询的性能优化.(索引覆盖:索引覆盖是一种速度极快,效率极高,业界推荐的一种查询方式.就是select的数据列只用从索引中就能够获得,不必从数据表中读取,也就是查询列要被所使用的索引覆盖)
 *  SELECT \* 语句没有说明将要取出哪些字段进行操作，不具备针对性，不推荐，不利于后期代码维护

## group by 关键字优化同order by  ##

where 高于having， 能写在where限定的条件不要写在having里面

## 慢查询日志 slow\_query\_log ##

用来记录mysql中响应时间超过阀值long\_query\_time的语句，mysql默认阀值是10秒，可以通过set global long\_query\_time = 3去修改时间，执行时间大于阈值的sql将会被记录下来

如果不是调优需要的话，一般不建议启动该参数，多少w都会影响性能

查看是否开启及如何开启：show variables like '%slow\_query\_log%';

开启： set global slow\_query\_log = 1 只对当前数据库生效 ，mysql重启后会失效，永久生效，就必须修改配置文件my.ini (其它系统变量也是)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 2][]

查看有多少条慢sql：show global status like '%Slow\_queries%';

## 日志分析工具mysqldumpslow     ##

具体可以使用 mysqldumpslow --help 查询命令帮助

s :表示按何种方式排列， r 返回记录 ， t 查询时间

得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

## show profile ##

show profile 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况的，默认情况下，参数处于关闭状态，并保存最近15次的运行结果

查看是否开启命令：Show variables like 'profiling';   开启 set profiling = on;

命令show profiles 可以查看哪天sql影响性能

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 3][]

进一步查看这条sql从开始到结束资源消耗情况

show profile cpu,block io for query 8;  //可以查cpu,内存，io等所有信息，可以查看执行过程中是否产生了临时表

临时表慢的原因： 要拷贝数据导临时表，用完再删除

![20201012205956121.png][]

Creating tmp table 创建临时表，Copying to tmp table on disk 把内存中临时表赋值到磁盘上，危险

##  ##

[20201012175842879.png]: /images/20221123/18e78c908ce04adab0e88229303a51cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70]: /images/20221123/760b27159d66432d94d6d5ea00fb98e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 1]: /images/20221123/33ab008b0afd44dcb477eef5f0f15d88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 2]: /images/20221123/5abc5e46ff1c4100ac4044e1a985ae12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MDQyOTM4_size_16_color_FFFFFF_t_70 3]: /images/20221123/485e8f6fae0541e895f3eb7f16442b36.png
[20201012205956121.png]: /images/20221123/925e161d40bb453ea58b7159ca4ecea1.png