首页 > 代码库 > 【mysql】mysql优化

【mysql】mysql优化

一,表设计
  1.1. E-R(entity relation)实体关系图
    长方形 实体 表
    椭圆形 属性 字段
    菱形 关系 一对一 多对一 属于 多对多
  1.2. 三范式标准
    原子性
      个人信息
      省市县乡镇
    唯一性
      主键 id
    无冗余性
      订单表中的商品名称与价格应该关联查询商品表
    三范式并不绝对。
  1.3. 选择合适的存储引擎
    查询效率
      myisam快
      innodb慢
    事务
      myisam不支持
      innodb支持
    全文索引
      myisam支持
      innodb不支持
      兄弟连,研究生命,和特朗普通话,别插嘴。
    锁机制
      myisam表锁
        即锁定一张表,如果进行读操作,则其他进程不允许写,如果进行写操作,则其他进程不允许读,更不允许写
      innodb行锁
        即锁定一条记录,其他进程可以对其他记录进行读写操作,
    文件存储区别
      myisam有三个文件
        .frm
        .myd
        .myi
      innodb有两个文件
        .frm
        .idb
      总结
        查询频繁的使用myisam,例如新闻系统
        安全性要求高的使用innodb,例如商城系统

  注意: 选择合适的字段类型
    数字->时间->枚举->字符串
    尽量不要使用 null 类型
二,备份
  2.1. 普通备份
    mysqldump -uroot -p123456 database > /tmp/bak.sql
    mysqldump -uroot -p123456 --all-databases --events > /tmp/bak.sql
  计划任务
    linux
    windows
  2.2. 增量备份
    增量备份(incremental backup)是备份的一个类型,备份上一次备份后的所有有变化的文件。
    1. 配置
      vim /etc/my.cnf
      log-bin=bin-log
      开启二进制日志
    2. 查看
      /usr/local/mysql/data/mysql-bin.000001
      二进制日志文件位置
      mysqlbinlog mysql-bin.000001
    3. 恢复
      (1)通过时间
        mysqlbinlog --stop-datetime="2017-05-06 06:01:30" mysql-bin.000001 | mysql -uroot -p
      (2)通过位置
        mysqlbinlog --stop-position="3068" mysql-bin.000001 |mysql -uroot -p
    4. 重置
      reset master
    5. 案例
      CREATE DATABASE lizhaohui;
      USE lizhaohui;
      CREATE TABLE users(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL);
      reset master
      在创建完库表之后,再执行二进制日志文件记录。
      INSERT INTO users(name) VALUE(‘aaaaa‘);
      INSERT INTO users(name) VALUE(‘bbbbb‘);
      INSERT INTO users(name) VALUE(‘ccccc‘);
      INSERT INTO users(name) VALUE(‘ddddd‘);
      INSERT INTO users(name) VALUE(‘eeeee‘);
      INSERT INTO users(name) VALUE(‘fffff‘);
三,优化
  3.1. 定位慢语句
    vim /etc/my.cnf
      [mysqld]
      slow-query-log=1 # 开启记录慢查询的操作
      slow-query-log-file=slow.log #文件默认存放在mysql的data目录下
      long-query-time=1 #不能使用小数
    重启 mysql
      mysqladmin -uroot -p shutdown
      mysqld_safe -u mysql &
    快速插入数据
      INSERT INTO users(name) SELECT name FROM users;
  3.2. 处理
    1. 增加,删除,修改
      查看服务器 cpu 和 内存使用率
      top命令
      一张表记录不要超过100万条
        SELECT COUNT(*) FROM users;
      检查表索引不宜过多
        SHOW INDEX FROM users;
      分库分表
    2. 查询
      缓存
      语句分析
        explain 指令
          EXPLAIN SELECT * FROM users;
        type(连接类型)
          好坏顺序
            system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
          system
          const
            表中满足条件的记录最多一条,使用主键或者 唯一索引查询
            EXPLAIN SELECT * FROM users WHERE id=1;
          eq_ref
            某一列等于带索引的列
            CREATE TABLE IF NOT EXISTS t1(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL, PRIMARY KEY(id))             ENGINE=InnoDB DEFAULT CHARSET=utf8;
            CREATE TABLE IF NOT EXISTS t2(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL, PRIMARY KEY(id))             ENGINE=InnoDB DEFAULT CHARSET=utf8;
            EXPALIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
            注意:当数据量足够大时,才会出现此连接类型。
          ref
            使用普通索引进行查询
            SELECT * FROM users WHERE name = ‘admin‘;
          ref_or_null
            通过普通索引检索,并且会检索null值
            EXPLAIN SELECT * FROM users WHERE name = ‘amdin‘ or name = null;
          range
            范围
            EXPLAIN SELECT * FROM users WHERE id<3;
          index
            跟all一样,不过只会扫描索引.
            EXPLAIN SELECT id FROM users;
          all
            全表扫描
            EXPLAIN SELECT * FROM users;
      索引优化
        创建
        主键
          CREATE TABLE test(id INT PRIMATY KEY AUTO_INCREMENT,name VARCHAR(50));
          ALTER TABLE test ADD PRIMARY KEY(id);
        普通索引
          CREATE TABLE test(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50), INDEX name(name));
          ALTER TABLE test ADD INDEX name(name);
        唯一索引
          CREATE TABLE test(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50), UNIQUE name(name));
          ALTER TABLE test ADD UNIQUE name(name);
        删除
        主键
          ALTER TABLE test MODIFY id INT(10);
          ALTER TABLE test DROP PRIMARY KEY;
        非主键
          ALTER TABLE test DROP INDEX name;
        查看
          SHOW INDEX FROM test;
          注意:索引,利弊,索引是一把双刃剑。
    多机配置
      主从
        好处
          单向备份
          读写分离,提高效率
          原理


实验
在主服务器上操作
1. 启动binlog日志
vi /etc/my.cnf
2. 在文件中添加
log-bin=mysql-bin
server-id=101
3. 重启mysql
service mysqld restart
4. 查看二进制日志是否开启
show global variables like ‘%log%‘;
5. 在 主服务器上 授权
grant replication slave on *.* to ‘zhang‘@‘192.168.103.102‘ identified by ‘123456‘;
6. 查看主机信息
show master status;
在从服务器上操作
1. 启动binlog日志
vi /etc/my.cnf
2. 在文件中添加
log-bin=mysql-bin
server-id=102
3. 重启mysql
service mysqld restart
4. 查看二进制日志是否开启
show global variables like ‘%log%‘;
5. 在 从服务器上 连接
change master to master_host=‘192.168.103.101‘,master_user=‘zhang‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=263;
6. 启动 sql 线程
show slave status\G;
start slave;
主主
好处
双向备份
高可用
负载均衡
原理
两台机器互为主从
实验
在主服务器上操作
1. 启动binlog日志
vi /etc/my.cnf
2. 在文件中添加
log-bin=mysql-bin
server-id=101
replicate-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
auto-increment-increment=2
auto-increment-offset=1
3. 重启 mysql
service mysqld restart
4. 以 101 为主,以 102 为从
grant replication slave on *.* to ‘zhang‘@‘192.168.103.102‘ identified by ‘123456‘;
show master status;
change master to master_host=‘192.168.103.101‘,master_user=‘zhang‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=106;
5. 查看 SQL线程
show slave status\G;
start slave;
在从服务器上操作
1. 启动binlog日志
vi /etc/my.cnf
2. 在文件中添加
log-bin=mysql-bin
server-id=102
replicate-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
auto-increment-increment=2
auto-increment-offset=2
3. 重启 mysql
service mysqld restart
4. 以 102 为主,以 101 为从
grant replication slave on *.* to ‘zhang‘@‘192.168.103.101‘ identified by ‘123456‘;
show master status;
change master to master_host=‘192.168.103.102‘,master_user=‘zhang‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=106;
5. 查看 SQL线程
show slave status\G;
start slave;
中文分词。
sphinx。=> coreseek。
scws。

【mysql】mysql优化