mysql之sql调优

一、性能分析

　　1.慢查询日志：慢查询日志记录了所有执行时间超过指定时间的所有sql 配置方法修改my.cnf文件如下：

　　　　#开启慢日志查询

　　　　slow_query_log=1

　　　　#设置慢日志的时间(单位为秒 默认是10)

　　　　long_query_time=10

　　　　配置完毕之后重启mysql，查看慢日志的位置在 mysql/localhost-slow.log

　　2.profile详情：他能够帮助我们了解sql时间都耗费到了哪里

　　　　其实最简单的查看方法navcat自带了一个剖析功能就可以了

　　　　

 

 

　　　　通过have_profiling参数查看是否当前数据库是否支持profile,返回YES或者NO

　　　　　　select @@have_profiling; 

　　　　查询profiling是否开启

　　　　　　select @@profiling;

　　　　默认是关闭的  通过SET指令打开

　　　　　　set profiling = 1;

　　　　执行了一系列操作之后通过show profiles;查看近期的sql速度 返回有query_id字段,然后通过这个query_id可以查看这条sql具体的分步执行时间

　　　　　　show profile for query query_id;

　　3.执行计划explain

　　　　在任意的sql语句前加上 explain或desc 就能查看该语句的执行计划 

　　　　

 

 　　　　　　id：查询中执行顺序 id相同执行顺序从上到下  id不同 值越大越先执行

　　　　　　*type:访问类型，重要指标 NULL>system >const（主键） > eq_ref （唯一索引）> ref （非唯一索引）> range (索引范围查询)> index (索引读全表)> ALL(遍历全表)

　　　　　　*possible_keys:可能用的索引

　　　　　　*key:实际用的索引如果没有就是null

　　　　　　*key_len:索引中使用的字节数 最大可能长度 并非实际长度 不损失精度的情况越短越好

　　　　　　row:预估查询行数

　　　　　　filtered:结果行数占读取行数的百分比 越大越好

　　　　　　extra:额外信息

二、使用规则

　　1.索引的效率

　　　　一张一千万数据的sku表通过主键id查询速度为0.1s  通过sn字段查询速度为20s！！！

　　　　通过给sn字段添加索引：create index idx_sku_sn on tb_sky(sn);重新执行语句执行速度为0.01秒！！！

　　　　结论：索引的效率提升不止一个数量级

　　*2.联合索引-最左前缀法则

　　　　如果索引使用了联合索引 查询从索引的最左列开始 并且不跳过索引中的列，如果跳跃了某一列 索引将部分失效(后面的索引失效)

　　　　注：左右指的是索引添加时候的顺序 和sql语句中的顺序无关，也就是说联合索引的设计尽量把最常用的放最左边 依次往右

　　　　联合索引中出现范围查询（<  ,>）右侧的列索引失效，解决方案为业务允许的情况下尽量使用<= ,>= 可以解决

　　*3.索引失效

　　　　对索引列进行运算操作，例如where substring(phone,10,2) = '15',索引失效

　　　　字符串字段查询不加单引号，索引失效

　　　　模糊查询时前面加%，索引失效（只有后面加不会失效）

　　　　or连接两侧有任何一侧没有索引 两侧都会失效，只有两侧都有索引才会生效

　　　　mysql发现全表扫描比索引更快的情况，会自动进行全表扫描

　　4.sql提示：在sql语句中增加人为提示来优化操作

　　　　use index:用哪个索引

　　　　　　select * from tb_user use index(idx_user_pro) where pro = '张三'

　　　　ignore index:不用哪个索引

　　　　　　select * from tb_user use index(idx_user_pro) where pro = '张三'

　　　　force index:必须用哪个索引

　　　　　　select * from tb_user use index(idx_user_pro) where pro = '张三'

　　　5.覆盖索引

　　　　　　select 的字段中全部都在索引中能找到，则不需要回表查询 性能最高

　　　6.前缀索引：当字段类型为text或varchar时 有时候字段很长，此时可以只将字符串的一部分前缀建立一个索引 节约索引空间，提高索引效率

　　　　　　create index idx_user_name on tb_user(name(5))

　　　*7.单列索引和多列索引的选择

　　　　　　如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引而非单列索引

　　　***8.索引设计原则

　　　　　　1.数据量较大，并且查询比较频繁的表建立索引(10万以上)

　　　　　　2.常作为查询条件、排序、分组的字段建立索引

　　　　　　3.尽量选择区分度高的列作为索引，尽量建立唯一索引，性别状态这种没必要建索引

　　　　　　4.如果是字符串字段，且长度较长。可以针对于字段的特点建立前缀索引

　　　　　　5.尽量使用联合索引减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省储存空间避免回表，提高效率

　　　　　　6.要控制索引的数量，索引越多，增删改的效率越低，而且占用磁盘空间

　　　　　　7.如果索引列不能存储NULL值，尽量加上NOT NULL约束。可以帮助mysql确定哪个索引最有效

三、SQL优化

　　1.插入多条数据的优化方法 ：

　　　　1.批量插入:批量插入一次性不要超过1000条，500-1000条为宜

　　　　　　insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');

　　　　2.手动提交事务

　　　　　　start transaction;

　　　　　　insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');

　　　　　　insert into tb_test values(4,'toma'),(5,'cdat'),(6,'jerffry');

　　　　　　commit;

　　　　3.主键顺序插入

　　2.主键优化：

　　　　满足业务需求的情况下，尽量降低主键长度，搜索效率大大提高。

　　　　最好是自增主键

　　　　尽量不要使用UUID或者其他自然主键如身份证号

　　　　业务操作时，避免对主键的修改

　　3.order by 优化

　　　　排序的计划有两种using filesort 表示不走索引，using index表示走索引

 

　　　　对order by 的字段加上合适的索引，多字段排序用联合索引，也遵循最左前缀法则

　　　　*多字段排序一个升序一个降序，此时需要注意联合索引在创建时的规则

　　　　　　create index idx_user_age_pho_ad on tb_user(age asc,phone desc);

　　　　如果有不可避免的filesort，出现大数据量排序，可以适当增大排序缓冲区大小 sort_buffer_size(默认256K)。

　　4.group by 优化

　　　　对group by 的字段加上合适的索引，多字段分组用联合索引，也遵循最左前缀法则

　　5.limit 优化   大数据量的limit查询非常浪费性能 可以改用覆盖索引加子查询的形式提高性能

　　　　select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where t.id = a.id

 

　　6.count 优化

　　　　count用法:count(*)  count(id) count(字段) count(1)

　　　　注：count字段时统计的是不为null的数据

　　　　执行效率：count(*)>=count(1)>count(id)>count(字段)

　　7.update优化

　　　　更新语句也需要添加索引，并且这个索引不能失效！！否则sql行锁将会升级为表锁  锁住整个表 阻塞其他操作

四、运维

　　1.主从复制

　　　　1.主库出现问题，可以快速切换到从库提供服务

　　　　　读写分离降低主库压力

　　　　　可以在从库执行备份以免影响主库服务

　　　　2.主库配置

　　　　　　1.修改配置文件/etc/my.cnf

　　　　　　　　#mysql服务id 保证整个集群唯一

　　　　　　　　server-id=1

　　　　　　　　#是否只读1只读 0读写

　　　　　　　　read-only=0

　　　　　　　　#忽略某个数据库

　　　　　　　　#binlog-ignore-db=mysql

　　　　　　　　#指定需要同步的数据库

　　　　　　　　#binlog-do-db=mydb

　　　　　　2.重启mysql

　　　　　　3.创建从库连接的账号，并赋予主从复制权限

　　　　　　　　create user  'ittest'@'%' identified with mysql_native_password by 'Root!123456';

　　　　　　　　grant replication slave on *,* to 'ittest'@'%';

　　　　　　4.查看二进制日志坐标

　　　　　　　　show master status;

　　　　　　　　

 

 

 　　　　　　

　　　　3.从库配置

　　　　　　

　　　　　　　　1.修改配置文件/etc/my.cnf

　　　　　　　　　　#mysql服务id 保证整个集群唯一

　　　　　　　　　　server-id=2

　　　　　　　　　　#是否只读1只读 0读写

　　　　　　　　　　read-only=1

　　　　 　　　　2.重启mysql

　　　　　　　　3.同步语句

　　　　　　　　

 

 　　　　　　　　4.开启同步操作

　　　　　　　　　　start replica 8.0.22之后

　　　　　　　　　　start slave   8.0.22之前

　　　　　　　　5.查看主从同步的状态

　　　　　　　　　　show replica status 8.0.22之后

　　　　　　　　　　show slave status  8.0.22之前

　　　2.读写分离

　　　　　　读写分离可以在代码中实现也可以直接在数据库层面中实现

　　　　　　目前使用代码层面实现，springboot  druid配置多数据源，增加切面在相关代码中添加相关注解来区分具体该方法调用哪个数据源