跳转至

7. 调优和事务

MySQL 调优

创建索引

select / where / order by 常涉及到的字段上建立索引

选择合适的存储引擎

读操作多: MyISAM
写操作多: InnoDB

SQL语句优化

(避免全表扫描)

1. where 子句尽量不使用 != , 否则放弃索引,全表扫描

2. 尽量避免NULL判断, 全表扫面
    优化前:
        select number from t1 where number is null;
    优化后:
        number字段设置默认值为0, 确保 number 字段无NULL
        select number from t1 where number = 0;

3. 尽量避免用 or 连接条件, 否则全表扫面
    优化前:
        select id from t1 where id = 10 or id = 20
    优化后:
        select id from t1 where id=10
        union all
        select id from t1 where id=20;

4. 模糊查询尽量避免使用前置的%, 否则全表扫描
    select variable from t1 where name='secure_file%';

5. 尽量避免使用in, not in, 否则全表扫面
    优化前:
        select id from t1 where id in (1, 2, 3, 4)
    优化后:
        select id from t1 where id between 1 and 4;

6. 不能使用 select * from ...
    用具体打字段来代替*, 不要返回用不到的任何字段

事务和事务回滚

定义: 一件事从开始发生到结束的整个过程
作用: 确保数据打一致性

事务和事务回滚的应用

  1. SQL命令会 autocommit 到数据库执行
  2. 事务操作
    1. 开启事务
        mysql> begin;  start transactions;
        mysql> SQL命令...
        mysql> SQL命令...
            ## 此时autocommit 被禁用  ##
    
    2. 终止事务
        mysql> commit;  成功
        mysql> rollback;  失败
    

示例

1. 背景
    : 建行卡
    朋友: 工行卡
    你在建行ATM给你朋友转5000元钱

2. 建表
    1 CCB
        create table CCB(
            name varchar(20),
            money decimal(20, 2));

        insert into CCB values
        ('有钱人', 100000);

    2 ICBC
        create table ICBC(
            name varchar(20),
            money decimal(20, 2));
        insert into ICBC values
        ('没钱人', 0);

3. 开始转账
    begin;
    update CCB set money=money-5000 where name='有钱人';
    update ICBC set money=money+5000 where name='没钱人';
    commit;
    ### 转账成功 ###

    begin;
    update CCB set money=money-5000 where name='有钱人';
    update ICBC set money=money+5000 where name='没钱人'; (故障)
    rollback;
    ### 转账失败 ###