精华内容
下载资源
问答
  • 2021-11-06 11:22:04

    索引高性能优化

    1. 使用独立的索引列

    错误的查询语句会使得索引无效…

    select user_id from user where user_id-1=4;
    
    • 第一个例子中,对索引列user_id进行了一次方程运算,然后进行查询,然而这样的操作MySQL无法自动识别,导致主键索引失效了。
    select date_col from t where TO_DAYS(CURRENT_DATE) -TO_DAYS(data_col)<=15;
    select date_col from t where TO_DAYS(CURRENT_DATE) -15>=TO_DAYS(data_col);
    
    • 第二个例子中,目的是获取最近15天的日期数据,对索引列data_col进行了运算,两种不同的表达式方式,前一种导致索引失效,而后一种则正常使用了索引。

    • 综合上面两种错误使用,不难看出,为了防止索引失效,不能将索引作为表达式的一部分,而是应该单独放在表达式符号的一侧。(作为独立的列参与语句)

    2. 索引的选择性

    在介绍前缀索引的优化原则之前,我们先来介绍B+树索引中的前缀查找:

    1. 以B+树为数据结构的索引,都具有以下几种前缀索引匹配规则

      • 匹配最左前缀:当一个索引为多列索引(联合索引),即由多个字段构成时,如有一个3列索引(col1,col2,col3),则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引,即优先使用索引的第一列。
      • 匹配列前缀:指的是可以将索引列字段的开头的一部分进行匹配。
    2. 索引的选择性:

      • 不重复的索引值(cardinality)数据表中的记录的总数的比值称为索引的选择性。
      • 索引的选择性越高则查询效率越高,由此可以推出,唯一索引的选择性是1。
    3. 前缀索引中选择性优化

      前面我们说,B+树的前缀索引由匹配列前缀实现,那么对于一些如TEXT等类型的VARCHAR列来说,我们使用前缀索引来查询其部分字符。

      • 选择性计算:使用DISTINCT函数获取不重复的索引值,除以COUNT(*)记录总数,

        select COUNT(DISTINCT city)/COUNT(*) FROM tcity;
        
      • 限制匹配长度:根据上面获取的选择性值,用LEFT()函数试验不同的长度限制取得的选择性,找到最接近最佳选择性的限制数量。

        SELECT COUNT(DISTINCT LEFT(city,4)/COUNT(*)) FROM tcity;
        SELECT LEFT(city,3) FROM tcity;
        
    4. 平均选择性优化的劣势

      • 当数据分布很不平均的时候,我们使用平均选择性的值作为匹配手段效果会很差。

      • 使用前缀索引,MySQL无法形成自带的ORDER BY 和GROUP BY操作,一定程度上来说,这削减了索引的功能。

    3. 使用多列索引时选择合适的索引列顺序

    1. 索引合并策略的糟糕效率:

      • 索引合并:指同一个表上多个但列索引进行定位查询,然而其带来的效率却差强人意。很多时候单列索引间的AND与OR算法操作,会给CPU、内存资源在算法缓存,排序及合并上消耗大量资源。
      • 当出现多个单列索引做相交操作(多个AND):说明需要一个多列索引包括这些单列字段,而不是多个单列索引。
    2. 当使用多列索引的时候,同样遵循最左列原则,即先按照最左列排序,其次是第二列。那么一般情况下,适应选择性高的列作为多列索引的最左列,可以提高查询效率。

    3. 然而整体查询性能,还着重受影响于值的分布,WHERE字句的排序,分组和范围约束条件等。

    4. 使用聚簇索引和覆盖索引

    1. 聚簇索引的概念上篇中已经详细描述了,在这里仅补充一些聚簇索引使用的劣势:
      • 聚集索引的插入速度严重依赖插入顺序:当插入顺序按照主键顺序时,插入效率自然很高,这是由聚簇索引的叶子节点按照键值大小顺序排序的结构导致的。然而不按这个顺序插入的时候,效率就会降低,因此全部数据加载完成后,应该使用OPTIMIZE TABLE进行重新组织。
      • 更新聚簇索引的代价很高:这会导致每个被更新的行强制移动到对应位置。
      • “页分裂”问题:当聚簇索引的主键值要求某行插入到一个已满的页时,会导致页分裂,之前介绍过,页分裂会导致页的利用率**降低至50%**左右。
      • 聚簇索引可能导致全表扫描变慢:尤其是行比较稀疏,或者由页分裂导致的数据不连续情况下
      • 聚簇索引的二级索引有时体量可能过大。
    2. 覆盖索引:
      1. 定义一个索引中包含了要查询的字段的值,为索引覆盖。
      2. 优势:
        • 加快读取速度,减少数据访问量:我们知道索引的数目远远小于数据的条数,只需要读取索引意味着减少了缓存的负载
        • 减少内存访问次数:因为索引按照顺序存储,对于I/O密集型范围查询时,从较小的索引文件中读取数据,会比从磁盘读取数据的I/O代价小得多
        • 对于InnoDB中的聚簇索引,如果二级索引能够作为覆盖索引,那么避免了对主键索引的回表问题
      3. 只有B+树索引支持覆盖索引
      4. MySQL不支持对索引进行LIKE操作,只能进行简单的比较操作

    5. 前缀压缩索引

    MyISAM通过前缀压缩可以减少索引的大小,从而让更多的索引进入内存。

    1. 前缀索引压缩默认只压缩字符串,可以通过设置参数来对整数做压缩。

    2. 压缩方法如下:

      • 先完全保存索引块的第一个值。

      • 然后将后续的值与其比较

      • 得到相同的前缀字节数和不同的后缀

      • 将其按照“字节数,不同后缀"的形式存储

    6. 去除冗余和重复索引

    1. 重复索引:按照相同的排列顺序,对相同的列设置了同样类型的索引。

      CREATE TAVLE T(ID INT NOT NULL PRIMARY KEY,
                     UNIQUE(ID),
                     INDEX(ID)
                    ) ENGINE=InnoDB;
      

      这种情况下就是创建了三个重复索引,因为用户对ID字段的唯一限制和主键限制实际是都是通过隐式创建索引实现的。

    2. 冗余索引:在创建索引(col1,col2)的基础上,又创建了索引(col1),此时(col1)被称为冗余。当然,也需要注意,如果对(col1,col2)创建索引(col2)则不是冗余,这里对应最左前缀原则。

    更多相关内容
  • 优化mysql几种常用方法

    千次阅读 2021-02-05 13:52:43
    优化mysql几种常用方法发布时间:2020-05-25 14:54:07来源:PHP中文网阅读:154作者:三月下文我给大家简单讲讲关于优化mysql几种常用方法,大家之前了解过相关类似主题内容吗?感兴趣的话就一起来看看这篇文章...

    优化mysql的几种常用方法

    发布时间:2020-05-25 14:54:07

    来源:PHP中文网

    阅读:154

    作者:三月

    下文我给大家简单讲讲关于优化mysql的几种常用方法,大家之前了解过相关类似主题内容吗?感兴趣的话就一起来看看这篇文章吧,相信看完优化mysql的几种常用方法对大家多少有点帮助吧。

    9d7944858b6de11962c3748bf3bcb25d.png

    1. 选择合适的存储引擎: InnoDB

    除非你的数据表使用来做仅仅读或者全文检索 (相信如今提到全文检索,没人会用 MYSQL 了)。你应该默认选择 InnoDB 。

    你自己在測试的时候可能会发现 MyISAM 比 InnoDB 速度快。这是由于: MyISAM 仅仅缓存索引,而 InnoDB 缓存数据和索引,MyISAM 不支持事务。可是 假设你使用 innodb_flush_log_at_trx_commit = 2 能够获得接近的读取性能 (相差百倍) 。

    1.1 怎样将现有的 MyISAM 数据库转换为 InnoDB:mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;" > alter_table.sql

    perl -p -i -e 's/(search_[a-z_]+ ENGINE=)InnoDB//1MyISAM/g' alter_table.sql

    mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql

    1.2 为每一个表分别创建 InnoDB FILE:innodb_file_per_table=1

    这样能够保证 ibdata1 文件不会过大。失去控制。尤其是在运行 mysqlcheck -o –all-databases 的时候。

    2. 保证从内存中读取数据。讲数据保存在内存中

    2.1 足够大的 innodb_buffer_pool_size

    推荐将数据全然保存在 innodb_buffer_pool_size ,即按存储量规划 innodb_buffer_pool_size 的容量。这样你能够全然从内存中读取数据。最大限度降低磁盘操作。

    2.1.1 怎样确定 innodb_buffer_pool_size 足够大。数据是从内存读取而不是硬盘?

    方法 1mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';

    +----------------------------------+--------+

    | Variable_name | Value |

    +----------------------------------+--------+

    | Innodb_buffer_pool_pages_data | 129037 |

    | Innodb_buffer_pool_pages_dirty | 362 |

    | Innodb_buffer_pool_pages_flushed | 9998 |

    | Innodb_buffer_pool_pages_free | 0 | !!!!!!!!

    | Innodb_buffer_pool_pages_misc | 2035 |

    | Innodb_buffer_pool_pages_total | 131072 |

    +----------------------------------+--------+

    6 rows in set (0.00 sec)

    发现 Innodb_buffer_pool_pages_free 为 0,则说明 buffer pool 已经被用光,须要增大 innodb_buffer_pool_size

    InnoDB 的其它几个參数:innodb_additional_mem_pool_size = 1/200 of buffer_pool

    innodb_max_dirty_pages_pct 80%

    方法 2

    或者用iostat -d -x -k 1 命令,查看硬盘的操作。

    2.1.2 server上是否有足够内存用来规划

    运行 echo 1 > /proc/sys/vm/drop_caches 清除操作系统的文件缓存。能够看到真正的内存使用量。

    2.2 数据预热

    默认情况,仅仅有某条数据被读取一次,才会缓存在 innodb_buffer_pool。所以,数据库刚刚启动,须要进行数据预热,将磁盘上的全部数据缓存到内存中。

    数据预热能够提高读取速度。

    对于 InnoDB 数据库,能够用下面方法,进行数据预热:

    1. 将下面脚本保存为 MakeSelectQueriesToLoad.sqlSELECT DISTINCT

    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,

    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache

    FROM

    (

    SELECT

    engine,table_schema db,table_name tb,

    index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist

    FROM

    (

    SELECT

    B.engine,A.table_schema,A.table_name,

    A.index_name,A.column_name,A.seq_in_index

    FROM

    information_schema.statistics A INNER JOIN

    (

    SELECT engine,table_schema,table_name

    FROM information_schema.tables WHERE

    engine='InnoDB'

    ) B USING (table_schema,table_name)

    WHERE B.table_schema NOT IN ('information_schema','mysql')

    ORDER BY table_schema,table_name,index_name,seq_in_index

    ) A

    GROUP BY table_schema,table_name,index_name

    ) AA

    ORDER BY db,tb

    ;

    2. 运行mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql

    3. 每次重新启动数据库,或者整库备份前须要预热的时候运行:mysql -uroot < /root/SelectQueriesToLoad.sql > /dev/null 2>&1

    2.3 不要让数据存到 SWAP 中

    假设是专用 MYSQL server。能够禁用 SWAP,假设是共享server,确定 innodb_buffer_pool_size 足够大。或者使用固定的内存空间做缓存,使用 memlock 指令。

    3. 定期优化重建数据库

    mysqlcheck -o –all-databases 会让 ibdata1 不断增大。真正的优化仅仅有重建数据表结构:CREATE TABLE mydb.mytablenew LIKE mydb.mytable;

    INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;

    ALTER TABLE mydb.mytable RENAME mydb.mytablezap;

    ALTER TABLE mydb.mytablenew RENAME mydb.mytable;

    DROP TABLE mydb.mytablezap;

    4. 降低磁盘写入操作

    4.1 使用足够大的写入缓存 innodb_log_file_size

    可是须要注意假设用 1G 的 innodb_log_file_size 。假如server当机。须要 10 分钟来恢复。

    推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

    4.2 innodb_flush_log_at_trx_commit

    这个选项和写磁盘操作密切相关:

    innodb_flush_log_at_trx_commit = 1 则每次改动写入磁盘

    innodb_flush_log_at_trx_commit = 0/2 每秒写入磁盘

    假设你的应用不涉及非常高的安全性 (金融系统),或者基础架构足够安全,或者 事务都非常小,都能够用 0 或者 2 来减少磁盘操作。

    4.3 避免双写入缓冲innodb_flush_method=O_DIRECT

    5. 提高磁盘读写速度

    RAID0 尤其是在使用 EC2 这样的虚拟磁盘 (EBS) 的时候,使用软 RAID0 很重要。

    6. 充分使用索引

    6.1 查看现有表结构和索引SHOW CREATE TABLE db1.tb1/G

    6.2 加入必要的索引

    索引是提高查询速度的唯一方法。比方搜索引擎用的倒排索引是一样的原理。

    索引的加入须要依据查询来确定。比方通过慢查询日志或者查询日志,或者通过 EXPLAIN 命令分析查询。

    ADD UNIQUE INDEX

    ADD INDEX

    6.2.1 比方,优化用户验证表:

    加入索引ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);

    ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

    每次重新启动server进行数据预热echo “select username,password from users;” > /var/lib/mysql/upcache.sql

    加入启动脚本到 my.cnf[mysqld]

    init-file=/var/lib/mysql/upcache.sql

    6.2.2 使用自己主动加索引的框架或者自己主动拆分表结构的框架

    比方。Rails 这种框架。会自己主动加入索引。Drupal 这种框架会自己主动拆分表结构。

    会在你开发的初期指明正确的方向。所以,经验不太丰富的人一開始就追求从 0 開始构建,实际是不好的做法。

    7. 分析查询日志和慢查询日志

    记录全部查询。这在用 ORM 系统或者生成查询语句的系统非常实用。log=/var/log/mysql.log

    注意不要在生产环境用。否则会占满你的磁盘空间。

    记录运行时间超过 1 秒的查询:long_query_time=1

    log-slow-queries=/var/log/mysql/log-slow-queries.log

    8. 激进的方法。使用内存磁盘

    如今基础设施的可靠性已经非常高了,比方 EC2 差点儿不用操心server硬件当机。并且内存实在是廉价。非常easy买到几十G内存的server,能够用内存磁盘。定期备份到磁盘。

    将 MYSQL 文件夹迁移到 4G 的内存磁盘mkdir -p /mnt/ramdisk

    sudo mount -t tmpfs -o size=4000M tmpfs /mnt/ramdisk/

    mv /var/lib/mysql /mnt/ramdisk/mysql

    ln -s /tmp/ramdisk/mysql /var/lib/mysql

    chown mysql:mysql mysql

    9. 用 NOSQL 的方式使用 MYSQL

    B-TREE 仍然是最高效的索引之中的一个,全部 MYSQL 仍然不会过时。

    用 HandlerSocket 跳过 MYSQL 的 SQL 解析层。MYSQL 就真正变成了 NOSQL。

    10. 其它

    ●单条查询最后添加 LIMIT 1,停止全表扫描。

    ●将非”索引”数据分离,比方将大篇文章分离存储,不影响其它自己主动查询。

    ●不用 MYSQL 内置的函数。由于内置函数不会建立查询缓存。

    ●PHP 的建立连接速度很快,全部能够不用连接池。否则可能会造成超过连接数。当然不用连接池 PHP 程序也可能将

    ●连接数占满比方用了 @ignore_user_abort(TRUE);

    ●使用 IP 而不是域名做数据库路径。避免 DNS 解析问题

    大家觉得优化mysql的几种常用方法这篇文章怎么样,是否有所收获。如果想要了解更多相关,可以继续关注我们的行业资讯板块。

    展开全文
  • 优化MySQL数据库性能的八种方法, 1、选取最适用的字段属性 2、使用连接(JOIN)来代替子查询(Sub-Queries) 3、使用联合(UNION)来代替手动创建的临时表
  • mysql 语句优化几种方式(总结)

    千次阅读 2021-11-04 19:49:53
    一、避免操作多余数据 1、使用where条件语句限制要查询的数据...三、where查询字句优化 7、避免在where 子句中的 “=” 左边进行内置函数、算术运算或其他表达式运算。 8、避免在 where 子句中使用 != 或 <> 操作符。 9

    网上有很多关于SQL优化的文章,写的很好,但我觉得不够系统,不方便记忆。结合实际使用的一些经验,对SQL优化的功能点进行梳理、总结,方便大家使用。

    一、避免操作多余数据

    1、使用where条件语句限制要查询的数据,避免返回多余的行。

    查询学生张三的成绩的年纪是否为18岁
    优化前:

    select * from student where age = 18;
    

    再从查询到的结果种判断是否包含张三
    优化后:

    select name from student where age = 18 and name = "张三";
    

    2、 尽量避免select *,改使用select 列名,避免返回多余的列。

    查询所有18岁的学生姓名
    优化前:

    select * from student where age = 18;
    

    优化后:

    select name from student where age = 18;
    

    3、若插入数据过多,考虑批量插入。

    批量插入学生数据

    优化前:

    ##伪代码
    for(User u :list){
    	 insert into student(age, name, height, weight) values(#{age}, #{name}, #{height}, #{weight}); 
    }
    

    优化后:

    insert into student(age, name, height, weight) values 
        <foreach collection="list" separator="," index="index" item="item">
          (#{age}, #{name}, #{height}, #{weight})
        </foreach>
    

    原因:
    批量插入性能好,插入效率更高

    4、尽量避免同时修改或删除过多数据。

    尽量避免同时修改或删除过多数据,因为会造成cpu利用率过高,从而影响别人对数据库的访问,建议分批操作。

    5、尽量避免向客户端返回大数据量。

    1. 若数据量过大,应该考虑相应需求是否合理。
    2. 大数据量的查询,优先使用分页查询。
    3. 仍不能满足需求的,考虑使用es 或者 分库分表。

    二、避免删库跑路

    6、删除数据时,一定要加where语句。

    这个不多说,删库跑路 必备技能。

    三、where查询字句优化

    7、避免在where 子句中的 “=” 左边进行内置函数、算术运算或其他表达式运算。

    优化前:

    select * from student where Date_ADD(updated_time,Interval 7 DAY) >=now();
    select * from student where age + 1 = 18;
    select * from student where substring(name,1,1) = `z`;
    

    优化后:

    select * from student where updated_time >= Date_ADD(NOW(),INTERVAL - 7 DAY);
    select * from student where age = 18 - 1;
    select * from student where name like = "z%";
    

    原因:
    将导致系统放弃使用索引而进行全表扫描。

    8、避免在 where 子句中使用 != 或 <> 操作符。

    查询年龄不是18岁的学生

    优化前:

    select * from student where age != 18;
    select * from student where age <> 18;
    

    优化后:

    select * from student where age < 18 union all select * from student where age > 18;
    

    原因:
    将导致系统放弃使用索引而进行全表扫描。

    9、避免在 where 子句中使用or操作符。

    查询年龄为17和18岁的学生信息

    优化前:

    select * from student where age = 18 or age = 17;
    

    优化后:

    select * from student where age = 18 union all select * from student where age = 17;
    

    原因:
    将导致系统放弃使用索引而进行全表扫描。

    10、where子句中考虑用默认值代替null。

    查询未填写城市的学生

    优化前:

    select * from student where city is null;
    

    优化后:

    select * from student where city = "0";
    

    原因:
    不用is null 或者 is not null 不一定不走索引了,这个跟mysql版本以及查询成本有关。把null值,换成默认值,很多时候让走索引成为可能。

    11、不要在where字句中使用not in。

    查询年龄不是17岁的学生信息

    优化前:

    select * from student where age not in (17);
    

    优化后:

    select * from student a left join (select * from student where age = 17 ) b on a.id = b.id where b.id is null;
    

    原因:
    not in 不走索引,建议使用not exsitsleft join优化语句。

    11、合理使用exist & in。

    in是把外表和内表作hash连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。如果查询的两个表大小相当,那么用in和exists差别不大,则子查询表大的用exists,子查询表小的用in。

    12、谨慎使用distinct关键字。

    查询所有不重复的用户年龄

    优化前:

    select distinct * from student 
    

    优化后:

    select distinct name from student;
    

    原因:
    当查询很多字段时,如果使用distinct,数据库引擎就会对数据进行比较,过滤掉重复数据,然而这个比较,过滤的过程会占用系统资源,cpu时间。

    四、limit查询优化

    13、查询一条或者最大/最小一条记录,建议使用limit 1。

    查询身高最高的学生

    优化前:

    select name from student order by height desc;
    

    优化后:

    select name from student order by height desc limit 1;
    

    14、优化limit分页。

    年龄从大到小,分页查询学生姓名

    优化前:

    select name,age from student order by age desc limit 1, 20;
    

    优化后:

    ## #{age}代表上次查询的最小年龄
    select name,age from student where age < #{age} order by age desc limit 20;
    

    注意,此处的age字段应为唯一索引,如果不是唯一索引,会出现数据重复的问题。

    原因:
    当偏移量最大的时候,查询效率就会越低,因为Mysql并非是跳过偏移量直接去取后面的数据,而是先把偏移量+要取的条数,然后再把前面偏移量这一段的数据抛弃掉再返回的。

    五、like语句优化

    15、优化like语句。

    查询姓名包含张三的学生信息

    优化前:

    select * from student where name like "%张三%";
    

    优化后:

    select * from student where name like "张三%";
    

    原因:
    like语句后跟"%%"走不了索引。

    like语句的优化方案,我觉得很有必要新写一篇文章,后面会把链接贴过来。

    关于该部分的优化,可以参考另一篇文章链接:
    模糊查询优化 like语句(已亲测)

    六、索引优化

    16、查询时应避免全表扫描,首先考虑在where和order by设计的列上建立索引。

    select * from student where age = 18;
    select height from student order by height;
    

    需要为age 和 height 字段加上索引。

    17、使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则。

    存在索引:

    KEY `name_age_height` (`name`, `age`, `height`)
    

    存在下面的查询语句:

    select * from student where name = "张三";
    
    select * from student where name = "张三" and age = 18;
    
    select * from student where age = 18;
    

    根据最左匹配原则,上面第三条查询语句,是不会走索引的。

    18、索引不要超过6个。

    索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。

    19、删除冗余和无效的索引。

    存在索引:

    KEY `height_weight` (`height`, `weight`);
    KEY `height` (`height`);
    

    上面第二个索引属于冗余索引,需要删除掉。

    20、尽量使用数字型字段。

    若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

    21、尽可能的使用 varchar/nvarchar 代替 char/nchar。

    因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

    22、建议使用自增主键。

    参考链接:
    https://zhuanlan.zhihu.com/p/299051996
    https://www.cnblogs.com/starcrm/p/12971702.html
    https://bbs.csdn.net/topics/397417232?list=1149931
    https://zhuanlan.zhihu.com/p/169456404
    https://blog.csdn.net/wangji5487/article/details/102701330
    http://www.360doc.com/content/19/0131/15/16619343_812364509.shtml

    展开全文
  • 本篇文章是对MySQL优化sql语句查询常用的30种方法进行了详细的分析介绍,需要的朋友参考下
  • 本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下:1、EXPLAIN做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:type列,连接类型。一个...

    本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下:

    1、EXPLAIN

    做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。

    下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。

    key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。

    key_len列,索引长度。

    rows列,扫描行数。该值是个预估值。

    extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。

    2、SQL语句中IN包含的值不应过多

    MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from t where num in(1,2,3) 对于连续的数值,能用between就不要用in了;再或者使用连接来替换。

    3、SELECT语句务必指明字段名称

    SELECT*增加很多不必要的消耗(CPU、IO、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段名。

    4、当只需要一条数据的时候,使用limit 1

    这是为了使EXPLAIN中type列达到const类型

    5、如果排序字段没有用到索引,就尽量少排序

    6、如果限制条件中其他字段没有索引,尽量少用or

    or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用union all或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

    7、尽量用union all代替union

    union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。

    8、不使用ORDER BY RAND()

    select id from `dynamic` order by rand() limit 1000;

    上面的SQL语句,可优化为:

    select id from `dynamic` t1 join (select rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nidlimit 1000;

    9、区分in和exists、not in和not exists

    select * from 表A where id in (select id from 表B)

    上面SQL语句相当于

    select * from 表A where exists(select * from 表B where 表B.id=表A.id)

    区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

    关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句?

    原SQL语句:

    select colname … from A表 where a.id not in (select b.id from B表)

    高效的SQL语句:

    select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

    取出的结果集如下图表示,A表不在B表中的数据:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    10、使用合理的分页方式以提高分页的效率

    select id,name from product limit 866613, 20

    使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。

    优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。SQL可以采用如下的写法:

    select id,name from product where id> 866612 limit 20

    11、分段查询

    在一些用户选择页面中,可能一些用户选择的时间范围过大,造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段进行查询,循环遍历,将结果合并处理进行展示。

    如下图这个SQL语句,扫描的行数成百万级以上的时候就可以使用分段查询:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    12、避免在where子句中对字段进行null值判断

    对于null的判断会导致引擎放弃使用索引而进行全表扫描。

    13、不建议使用%前缀模糊查询

    例如LIKE“%name”或者LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

    那如何查询%name%?

    如下图所示,虽然给secret字段添加了索引,但在explain结果并没有使用:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    那么如何解决这个问题呢,答案:使用全文索引。

    在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like ‘%zhangsan%’; 。这样的语句,普通索引是无法满足查询需求的。庆幸的是在MySQL中,有全文索引来帮助我们。

    创建全文索引的SQL语法是:

    ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

    使用全文索引的SQL语句是:

    select id,fnum,fdst from dynamic_201606 where match(user_name) against(‘zhangsan’ in boolean mode);

    注意:在需要创建全文索引之前,请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

    14、避免在where子句中对字段进行表达式操作

    比如:

    select user_id,user_project from user_base where age*2=36;

    中对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成:

    select user_id,user_project from user_base where age=36/2;

    15、避免隐式类型转换

    where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定where中的参数类型。

    16、对于联合索引来说,要遵守最左前缀法则

    举列来说索引含有字段id、name、school,可以直接用id字段,也可以id、name这样的顺序,但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面。

    17、必要时可以使用force index来强制查询走某个索引

    有的时候MySQL优化器采取它认为合适的索引来检索SQL语句,但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

    18、注意范围查询语句

    对于联合索引来说,如果存在范围查询,比如between、>、

    19、关于JOIN优化

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    LEFT JOIN A表为驱动表,INNER JOIN MySQL会自动找出那个数据少的表作用驱动表,RIGHT JOIN B表为驱动表。

    注意:

    1)MySQL中没有full join,可以用以下方式来解决:

    select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;

    2)尽量使用inner join,避免left join:

    参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。

    3)合理利用索引:

    被驱动表的索引字段作为on的限制字段。

    4)利用小表去驱动大表:

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    从原理图能够直观的看出如果能够减少驱动表的话,减少嵌套循环中的循环次数,以减少 IO总量及CPU运算的次数。

    5)巧用STRAIGHT_JOIN:

    inner join是由MySQL选择驱动表,但是有些特殊情况需要选择另个表作为驱动表,比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序,在STRAIGHT_JOIN左边的表名就是驱动表,右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接,也就是inner join。其他链接不推荐使用STRAIGHT_JOIN,否则可能造成查询结果不准确。

    f0a8fc09eb01d4561babd6ba1989e9bc.png

    这个方式有时能减少3倍的时间。

    客服微信:(id1234562011)本文链接:https://www.changchenghao.cn/n/174426.html

    版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 394062665@qq.com 举报,一经查实,本站将立刻删除。

    展开全文
  • MySQL常见的几种优化方案

    千次阅读 2021-08-12 19:00:52
    思考sql优化个地方,我把他做了个分类,方便理解 select [字段 优化1] from [表格 优化2] where [条件 优化3] union [联合查询 优化4] 新建表格 CREATE TABLE `student` ( `id` int(11) NOT NULL ...
  • mysql 优化sql语句的几种方法

    千次阅读 2019-06-10 22:38:09
    优化sql语句的几种方法 1、通过show status命令了解SQL的执行效率 show [session|global]status like 'com_%'; 2、定位执行效率较低的SQL语句 1)慢查询  开启方法 linux: 配置文件(/...
  • MySQL数据库优化几种方式

    万次阅读 2021-06-17 15:37:38
    方法一 使用连接来代替子查询(Sub-Queries)。...不是所有的数据库操作都可以只用一条或少数条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。可以保持数据库中数据的完整性和一致性 BEG
  • 主要介绍了MySQL优化之对RAND()的优化方法,本文详细分析了Mysql中对RAND()的几种优化方法,并最终得出一个结论,需要的朋友可以参考下
  • sql优化常用的几种方法

    千次阅读 2022-03-06 05:45:01
    1.EXPLAIN type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列,使用到的索引名。...MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好
  • 几种优化mysql方法

    千次阅读 2017-12-04 18:27:00
    2. 使用join,union来代替子查询,这样mysql不需要在内存中创建临时表来满足查询操作。 3. 事务【优点:数据完整性,缺点:独占性】---锁定表  解决独占性,锁定表的方法可以维护数据的完整性,但是它却不能
  • MySQL优化

    千次阅读 2022-03-18 20:28:08
    MySQL优化方向: 在设计上:字段类型,存储引擎,范式 在功能上:索引,缓存,分库分表 在架构上:集群,主从复制,负载均衡,读写分离 SQL优化 1.插入优化 大量数据采用批量插入形式 事务设置手动提交,...
  • sql优化几种方法

    万次阅读 多人点赞 2017-08-17 20:59:55
    在sql查询中为了提高查询效率,我们常常会采取一些措施对查询语句进行sql优化,下面总结的一些方法,有需要的可以参考参考。 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上...
  • MySQL数据库优化的八方式(经典必看)

    万次阅读 多人点赞 2019-03-13 15:48:28
    MySQL数据库优化的八方式(经典必看) 引言: 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿...
  • mysql大数据优化方法

    千次阅读 2018-12-24 14:46:48
    mysql数据量过大的时候,用一般的查询语句会相对较慢,下面从数据库设计、SQL语句方面来说说怎样优化提高查询效率,如果感觉小编那里写的不好不对或者可以进一步优化的地方,欢迎在评论区,指正留言。 一、数据库...
  • 插入记录时,影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况,可以分别进行优化,本节将介绍优化插入记录速度的几种方法。下面跟着小编一起来看下吧
  • mysql优化方案

    千次阅读 2021-12-27 08:34:10
    一、优化方向二、硬件方面优化三、软件配置1、网络方面的配置,要修改/etc/sysctl.conf文件2、mysql本身的一些优化mysql配置文件 /etc/my.cnf3、innodb方案4、MyISAM 参数配置4.1,设置索引缓存区大小4.2,设置读...
  • MySQL查询优化几种思路和方法

    千次阅读 2017-03-08 20:44:40
    整理的一些mysql查询存储的优化思路和方法
  • Mysql优化高级篇(全)

    千次阅读 多人点赞 2021-12-23 16:32:58
    本篇文章主要涉及mysql的高级篇,主要是mysql的架构介绍、索引优化分析、查询截取分析、mysql锁机制以及主从复制等 在这之前的学习可参考我之前的文章进行学习 数据库知识 链接 数据库查询常用语句语法 博客...
  • MySQL优化系列14-优化MySQL内存

    千次阅读 2021-07-15 14:09:35
    备注:测试数据库版本为MySQL 8.0 文章目录一. MySQL如何使用内存二.监控MySQL内存使用三.开始large page支持参考: 一. MySQL如何使用内存 MySQL分配缓冲区和缓存来提高数据库操作的性能。默认配置被设计为允许MySQL...
  • mysql优化方面的面试题

    万次阅读 多人点赞 2019-08-15 18:25:48
    1.MySQL数据库作发布系统的存储,一天五万条以上的增量,预计运维三年,怎么优化? a. 设计良好的数据库结构,允许部分数据冗余,尽量避免join查询,提高效率。 b. 选择合适的表字段数据类型和存储引擎,适当的添加...
  • MySQL索引优化总结

    千次阅读 2021-09-29 23:17:15
    前言:相信大家都知道索引可以大大提高MySQL的检索速度,但是真正在平时工作中写SQL,真的会考虑到这条SQL如何能够用上索引提升执行效率?本篇博客详细的介绍了索引优化的20个原则,只要在工作中能够随时应用到,...
  • 宝塔面板下实现MySQL性能优化处理

    千次阅读 2021-01-19 11:54:50
    在PHP+MYSQL架构网站运行过程中,往往会遇到各种性能问题影响,如MySQL、PHP、CPU、磁盘IO、缓存等,其中MySQL瓶颈就是最常见也最难解决的一影响网站性能的因素;通常,我们会使用redis、memcached等缓存软件来...
  • Mysql的sql优化方法

    万次阅读 多人点赞 2018-08-17 16:04:36
     Mysql是一关系型数据库,可以很好地支持大数据量的存储,但是一般来说,数据库中的表越小,在它上面执行的查询也就越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度舍得尽可能小。 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 191,320
精华内容 76,528
关键字:

mysql优化的几种方法

mysql 订阅