Mysql的奇技巧淫

慢查询优化基本步骤

先运行看看是否真的很慢，注意设置SQL_NO_CACHE
where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
order by limit 形式的sql语句让排序的表优先查
了解业务方使用场景
加索引时参照建索引的几大原则
观察结果，不符合预期继续从0分析

mysql查询技巧

分析查询

explain  select sleep(1);

explain 可以分析查询语句的性能
sleep秒

变量赋值

set @current =0;
select @current :=@current +1;

在查询中可以通过使用:=对变量进行重新赋值

查看数据库大小

SELECT table_schema 'DB Name',
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1)   MB
FROM information_schema.tables
GROUP BY table_schema order by MB desc;

utf-8的表里面搜索带中文的数据

select number from Chinese_Test where HEX(contents) REGEXP '^(..)*(E[4-9])';

the Chinese Character is from E4 to E9

一些套路

不要使用”utf-8”

utf-8不是真正的UTF-8,应该使用utf8mb4作为替代。系统中有一个参数character_set_server,改为utf8mb4

显示数据库引擎

SELECT * FROM INFORMATION_SCHEMA.ENGINES;

显示长事务

select * from information_schema.innodb_trx;

暴力改表

改表期间,运行

SELECT 
    CONCAT('kill ', thread_id, ';')
FROM
    (SELECT DISTINCT
        (i.trx_mysql_thread_id) thread_id
    FROM
        information_schema.innodb_trx i, (SELECT 
        id, time
    FROM
        information_schema.processlist
    WHERE
        time = (SELECT 
                MAX(time)
            FROM
                information_schema.processlist
            WHERE
                state = 'Waiting for table metadata lock'
                    AND SUBSTRING(info, 1, 5) IN ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p
    WHERE
        TIMESTAMPDIFF(SECOND, i.trx_started, NOW()) > p.time
            AND i.trx_mysql_thread_id NOT IN (CONNECTION_ID() , p.id)) t;

执行 alter 的同时, kill 掉除了 select * from INFORMATION_SCHEMA.innodb_trx里面除了 alter 以外的进程

PS:虽然mysql 5.7支持onlineDDL,但是实测修改数据类型的时候不支持DML.这个时候可以考虑使用pt-online-schema-change,例子见pt-online-schema-change使用说明、限制与比较

参考: pt-online-schema-change的原理解析与应用说明

允许 mysql 远程访问

vi /etc/mysql/my.cnf
```
[mysqld]
bind-address    = 0.0.0.0
```

修改大数据表

被修改的表 Table A 需要有一个记录时间戳的字段，这个时间戳就是每次数据更新，都会更新的字段，这个字段需要有索引，在django里可以使用 auto_now=True
创建一个新的临时表 Table B，不是tmp_table, 是一个新的表，但是是临时使用的。这个表和要修改的表拥有一模一样的数据结构，加上你要修改的部分，比如增加的字段；
记录下Table A 的索引
删除 Table B 的全部索引
把Table A 的数据全部复制到Table B, 是不是执行 INSERT INTO B(field1, field2) SELECT field1, field2 FROM A？当然不是，这么做不还是锁死了Table A 么，这里的迁移就是一个需要细分的地方，需要写一个脚本，让程序每次读取比如5000条数据出来，插入到Table B里面，因为Table B 是没有索引的，所以要当心不要使用多进程来做；如果是多进程，要确保插入到B的时候是不会有重复数据的；如果是1000万的数据，每次5000条，假设这个操作需要500ms，那么 2000*200ms = 16 分钟。这只是一个估值，具体情况和服务器当时的情况有关，不好细说。另外，我们要记录这个迁移开始的时间点，记为t1; 5 为B建立索引，待索引全部好了之后，再继续6

那么这个时候Table A 的数据是不是都进入了Table B 呢，应当说差不多大部分都进入了，但5中说，这大概需要16分钟，这么长的时间里，可能有新的数据进入了，也有可能已有的数据发生了更新，所以我们要把Table A 中在t1 之后发生变化的数据查找出来，然后更新到Table B 中，我们的做法是：

 记录这个操作对应的时间点 t2
 BEGIN;
 DELETE FROM B WHERE updated_time > t1;
 INSERT INTO B(field1, field2) SELECT field1, field2 FROM A WHERE updated_time >t1;
 COMMIT;

现在A 和 B 差不多該同步了吧？差不多了，但是6 执行完之后， A仍然在写，子子孙孙无穷尽也 … , 但这个时候 A 和 B 的差异已经非常非常小了，所以在下一步，我们在一个transaction 里执行下面的操作：

 BEGIN;
 DELETE FROM B WHERE updated_time > t2;
 INSERT INTO B(field1, field2) SELECT field1, field2 FROM A WHERE updated_time >t2;

 ALTER TABLE A RENAME TO C;
 ALTER TABLE B RENAME TO A;
 COMMIT;

MySQL Workbech

允许update不where更新

SET SQL_SAFE_UPDATES = 0;

客户端提示

Error Code: 2013. Lost connection to MySQL server during query
Error Code: 2006. MySQL server has gone away

Go to Edit -> Preferences -> SQL Editor and set to a higher value this parameter: DBMS connection read time out (in seconds). For instance: 86400.

修改为F5执行选中当前选中语句

方法:修改C:\Program Files\MySQL\MySQL Workbench 6.3 CE\data\main_menu.xml里面的内容

        <value type="object" struct-name="app.MenuItem" id="com.mysql.wb.menu.query.execute_current_statementwin"> 
          <link type="object" key="owner" struct-name="app.MenuItem">com.mysql.wb.menu.query</link> 
          <value type="string" key="caption">Execute Current Statement</value> 
          <value type="string" key="name">query.execute_current_statement</value> 
          <value type="string" key="command">builtin:query.execute_current_statement</value> 
          <value type="string" key="itemType">action</value> 
          <value type="string" key="shortcut">F5</value>
          <value type="string" key="platform">windows</value>
        </value> 

故障处理

忘记密码

mysql 5.7 Ubuntu 64

sudo service mysql status
sudo service mysql stop
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
sudo mysqld_safe --skip-grant-tables --skip-networking &  

mysql -u root --socket=/tmp/mysql.sock

use mysql; 
update user set authentication_string=PASSWORD("aaaaaaaaaaa") where User='root';
flush privileges;

sudo service mysql restart

磁盘已满

阿里云的RDS,磁盘满了，会出现 --rds-read-drop-only 状态。此时只能读取数据或者删库（毕竟升级磁盘要重启）。

此时最快的解决办法是删库。

退而求其次，转移数据库，然后删库。

或者在业务低峰期优化数据表结构

SELECT 
    ROW_FORMAT,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    MAX_DATA_LENGTH,
    DATA_FREE,
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE
FROM
    information_schema.TABLES
    order by data_free desc, TABLE_NAME

OPTIMIZE TABLE  db.tables

释放那些无效的空间