mysql索引优化

sql优化的几种方式

建表时选取最适用的字段属性，可以将表中字段的宽度设得尽可能小，字段尽量非空

使用JOIN代替子查询

查询过程不产生临时表

索引的字段上尽量不要使用函数进行操作

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语句的，分析你的查询语句或是表结构的性能瓶颈，具体包含：表的读取速度，数据读取操作的操作类型，哪些索引可以被使用，哪些索引被实际使用，表之间的引用，每张表有多少行被优化器查询

• 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作使用了覆盖索引，避免了访问表的数据行，效率不错

备注：system>const>eq_ref>ref>range>index>all 

system:系统表，少量数据，往往不需要进行磁盘IO
const:使用常数索引，MySQL只会在查询时使用常数值进行匹配。
eq ref:唯一索引扫描，只会扫描索引树中的一个匹配行
ref:非唯一索引扫描，只会扫描索引树中的一部分来查找匹配的行。
range:范围扫描，只会扫描索引树中的一个范围来查找匹配的行。
index:全索引扫描，会遍历索引树来查找匹配的行
ALL:全表扫描，将遍历全表来找到匹配的行。

索引失效

• 遵循最佳左前缀法则，不在索引列上做任何操作（计算，函数，自动或者手动类型转换）否则索引会失效转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列，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子句条件组合满足索引最左前列

为什么不适用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 (其它系统变量也是)

查看有多少条慢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影响性能

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

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

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

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