首页 > 数据库 >mysql之sql调优

mysql之sql调优

时间:2022-08-30 13:57:01浏览次数:58  
标签:index sql 查询 索引 调优 mysql tb id

一、性能分析

  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配置多数据源,增加切面在相关代码中添加相关注解来区分具体该方法调用哪个数据源

 

 

 

         

          

    

 

标签:index,sql,查询,索引,调优,mysql,tb,id
From: https://www.cnblogs.com/void--main/p/16636646.html

相关文章

  • mysql查询
    目录in和exists的区别结论原理in和exists的区别结论A.idin(B)适合子表b比主表a表数据量小的情况。A.idexists(B)则相反原理in子表驱动主表,是先查出(B)的数据,2个......
  • Sql语句查询慢(持续整理中。。。。。。)
    1.坚决不使用“*”来写查询。建索引,2.减少表之间的关联3.优化sql,尽量让sql很快定位数据,不要让sql做全表查询,应该走索引,把数据量大的表排在前面,简化查询字段,没用的......
  • SQL Server 错误:找不到SQL Server Configuration Manager配置管理工具
    问题描述:SQLServer配置管理器可用来管理与SQLServer相关联的服务、配置SQLServer使用的网络协议以及从SQLServer客户端计算机管理网络连接配置。但是升级到win11......
  • SQL Server 错误:无法连接到本地服务器
    问题描述:在与SQLServer建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且SQLServer已配置为允许远程连接。prov......
  • mysqldump导入慢的解决方法
    mysqldump导入慢的解决方法MySQL导出的SQL语句在导入时有可能会非常非常慢,经历过导入仅45万条记录,竟用了近3个小时。在导出时合理使用几个参数,可以大大加快导入的速度。......
  • MySQL中JSON使用
    在使用数据库存储数据时,经常会用到json方式;比如产品的各种属性,这时候又不能确定每一样产品有多少属性,因此直接存json可以避免扩展数据库字段,使之更为灵活.MySQL自5.......
  • MySQL提取字符串中的数字
    1--方法12selectreplace(reverse(FORMAT(reverse('国械注准20173463309'),0)),',','');34--方法25CREATEFUNCTIONget_number(paramvarchar(50))......
  • Mysql Count的区别
    1.count(1)和count(*)执行计划从执行计划来看count(1)和count()的效果是一样的。当表的数据量大些时(1W以上),对表作分析之后,使用count(1)比使用count()用时多。当......
  • MYSQL中的COLLATE是什么?
    在mysql中执行showcreatetable<tablename>指令,可以看到一张表的建表语句,example如下:CREATETABLE`table1`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENT,......
  • 12. SQL--重命名表
    1.前言SQL允许对表进行重命名。有时候我们为表使用了无意义的名称,因此需要更正。2.语法SQL支持两种重命名表的方式:altertableold_table_namerenametonew_ta......