• MySQL性能优化


    性能优化的思路:

    1. 首先需要使用慢查询功能去获取所有查询时间比较长的SQL语句

    2. 使用explain去查看该SQL的执行计划

    3. 使用show profile去查看该SQL执行时的性能问题

    慢查询:

    数据库查询快慢是影响项目性能的一大因素,对于数据库,我们除了要优化 SQL,更重要的是得先找到需要优化的 SQL

    MySQL 数据库有一个“慢查询日志”功能,用来记录查询时间超过某个设定值的SQL,这将极大程度帮助我们快速定位到症结所在,以便对症下药。

    MySQL的慢查询日志功能,默认是关闭的,需要手动开启

    查询慢查询是否开启

    slow_query_log    :是否开启慢查询日志,ON 为开启,OFF 为关闭,如果为关闭可以开启。

    slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log

    临时开启慢查询功能

    MySQL 执行 SQL 语句设置,但是如果重启 MySQL 的话将失效

    set global slow_query_log = ON;

    set global long_query_time = 1;

    永久开启慢查询功能

    修改/etc/my.cnf配置文件,重启 MySQL, 这种永久生效.

    [mysqld]

    slow_query_log = ON

    slow_query_log_file = /var/log/mysql/slow.log

    long_query_time = 1

    查看执行计划explain:

    MySQL 提供了一个 EXPLAIN 命令, 它可以 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.

    使用explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描,这都可以通过explain命令来查看。

    可以通过explain命令深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。

    EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 explain 就可以了, 例如:

     

    id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

    select_type: SELECT 查询的类型.

    table: 查询的是哪个表

    partitions: 匹配的分区

    type: join 类型  (性能关系:ALL < index < range ~ index_merge < ref < eq_ref < const < system)

    possible_keys: 此次查询中可能选用的索引

    key: 此次查询中确切使用到的索引.

    ref: 哪个字段或常数与 key 一起被使用

    rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

    filtered: 表示此查询条件所过滤的数据的百分比

    extra: 额外的信息

    使用show profile:

    Query Profiler是MYSQL自带的一种query诊断分析工具,通过它可以分析出一条SQL语句的性能瓶颈在什么地方。

    通常我们是使用的explain,以及slow query log都无法做到精确分析,但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况,比如CPU,IO等,以及该SQL执行所耗费的时间等。不过该工具只有在MYSQL 5.0.37以及以上版本中才有实现。

    语句:

    show profile  show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源使用情况.

    show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况

    show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列

     开启Profile功能

    * select @@profiling;
    * show variables like ‘%profil%’;查询是否开启Profile功能

      * set profiling=1; --1是开启0是关闭

    MySQL的性能优化细节:

    1.合理的创建及使用索引 (考虑数据的增删情况)。

    2.合理的冗余字段 (尽量建一些大表 ,考虑数据库的三范式和业务设计的取舍)。

    3.使用SQL要注意- 些细节: select语句中尽量不要使用*,count(*) , WHERE语句中尽量不要使用1=1、in语句(建议使用exists人注意组合索引的创建顺序按照顺序组着查询条件、尽量查询力度大的SQL放到最左边、尽量建立组合索引

    4.合理利用慢查询日志、 explain 执行计划查询、show profile 查看SQL执行时的资源使用情况

    MySQL事务处理

    MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务

    事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。

    事务用来管理DDL、DML、DCL操作,比如 insert,update,delete 语句

    一般来说,事务是必须满足4个条件(ACID):

    Atomicity(原子性)

    Consistency(稳定性、一致性)

    Isolation(隔离性)

    Durability(可靠性、持久性)

    MySQL事务支持:

    BEGIN或START TRANSACTION;显式地开启一个事务;

    COMMIT:COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的;

    ROLLBACK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;

    事务并发问题:

    在事务的并发操作中可能会出现一些问题:

    脏读:一个事务读取到另一个事务未提交的数据。

    不可重复读:一个事务因读取到另一个事务已提交的数据。导致对同一条记录读取两次以上的结果不一致。update操作

    幻读:一个事务因读取到另一个事务已提交的数据。导致对同一张表读取两次以上的结果不一致。insert、delete操作

    事务隔离级别(用来解决事务并发问题(级别由低到高):

    ① Read uncommitted (读未提交):最低级别,任何情况都无法保证。

    ② Read committed (读已提交):可避免脏读的发生。

    ③ Repeatable read (可重复读):可避免脏读、不可重复读的发生。

    ④ Serializable (串行化):可避免脏读、不可重复读、幻读的发生。

    隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。

    如何查看和设置隔离级别

    MySQL数据库中查看当前事务的隔离级别:

    select @@tx_isolation;

    MySQL数据库中设置事务的隔离 级别:

       set  [glogal | session]  transaction isolation level 隔离级别名称;
       set tx_isolation=’隔离级别名称;’

    MySQL锁:

    * 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;

    * 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;

    * 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

    表级锁:

    MySQL的表级锁定有两种模式:表共享读锁Table Read Lock)和表独占写锁Table Write Lock)。

    MySQL 实现的表级锁定的争用状态变量:show status like 'table%';

     

    * table_locks_immediate:产生表级锁定的次数;
    * table_locks_waited:出现表级锁定争用而发生等待的次数;
    手动增加表锁
    
    lock table 表名称 read(write),表名称2 read(write),其他;
    
    查看表锁情况
    
    show open tables;
    
    删除表锁
    
    unlock tables;

    行级锁:

    InnoDB的行级锁定同样分为两种类型,共享锁和排他锁,而在锁定机制的实现过程中为了让行级锁定和表级锁定共存,InnoDB也同样使用了意向锁(表级锁定)的概念,也就有了意向共享锁和意向排他锁这两种。

    
    

    Innodb所使用的行级锁定状态查看:show status like 'innodb_row_lock%';

    * Innodb_row_lock_current_waits:当前正在等待锁定的数量;

    * Innodb_row_lock_time:从系统启动到现在锁定总时间长度;

    * Innodb_row_lock_time_avg:每次等待所花平均时间;

    * Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;

    * Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

    对于这5个状态变量,比较重要的主要是:

    Innodb_row_lock_time_avg(等待平均时长)

    Innodb_row_lock_waits(等待总次数)

    Innodb_row_lock_time(等待总时长)这三项。

    集群搭建之主从复制:

    主服务器配置

    第一步:修改my.conf文件:

    [mysqld]段下添加:

    #启用二进制日志
    
    log-bin=mysql-bin
    
    #服务器唯一ID,一般取IP最后一段
    
    server-id=133

    第二步:重启mysql服务

    service mysqld restart

    第三步:建立帐户并授权slave

    mysql>GRANT FILE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
    mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%' identified by '123456'; 

    刷新权限

    mysql> FLUSH PRIVILEGES;

      

    查看mysql现在有哪些用户

    mysql>select user,host from mysql.user;

    第四步:查询master的状态

    mysql> show master status;

     从服务器配置:

    第一步:修改my.conf文件

    [mysqld]

    server-id=134

    第二步删除UUID文件

    第三步:配置从服务器

    mysql>change master to master_host='192.168.25.134',master_port=3306,master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=120 

    第四步:启动从服务器复制功能

    mysql>start slave;

    第五步:检查从服务器复制功能状态:

    mysql> show slave status

    集群搭建之读写分离:

    MySQL-Proxy配置

    创建mysql-proxy.cnf文件

    MySQL-Proxy启动域测试

    mysql-proxy  --defaults-file=mysql-proxy.cnf配置文件的地址

     

     

     

     

     

     



  • 相关阅读:
    CTF-cookies欺骗
    CTF-速度要快100
    php实现登录注册界面
    CTF-秋名山老司机 100
    三大类型语句
    PHP运算符
    标记风格和注释
    连接串口服务器时的注意事项
    RS485转以太网的概述和应用领域
    工业级路由器的优点有哪些
  • 原文地址:https://www.cnblogs.com/caocw/p/12104259.html
Copyright © 2020-2023  润新知