mysql优化

ysql优化需要了解各种关于mysql数据库的知识,也需要很多的实践才能一步一步深入地了解.本篇文章我们来简单地谈谈mysql优化.

为了更好的分析mysql语句,要先开启慢查询.

linux系统下开启慢查询的操作:

1.编辑配置文件

　　vim　/etc/my.cnf

2.在[mysqld]下写入:

　　slow-query-log=1
　　slow-query-log-file=slow.log #文件默认存放在mysql的data目录下
　　long-query-time=1 #不能使用小数　　

　　重启mysql服务:
　　service mysql restart

　　存储目录 /usr/local/mysql/data/slow.log

windows系统下开启慢查询的操作:

1.修改配置文件 my.ini 

　　写入内容同linux系统下一样

2.重启mysql服务

　　存储目录  D:\wamp\bin\mysql\mysql5.7.11\data

查看mysql语句之后,就可以对mysql语句进行优化.

一般来说.mysql语句进行增删改操作并不会占用很长的时间,如果时间过长,只会出现下面三种情况:

　　1.电脑配置太垃圾

　　2.操作的表的数据内容过多

　　3.检查表的索引是不是太多了

`　　重点分析一下数据查询操作的语句.使用explain指令对语句进行分析

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

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

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

mysql优化可以通过以下几个方面:

1.添加索引

　　通过对字段添加索引能够提高查询速度,mysql中索引分为四大类:主键索引,唯一索引,普通索引,全文索引.

　　mysql添加索引语句:

　　　　ALTER TABLE table_name ADD INDEX 索引名(字段名)

　　　　ALTER TABLE table_name ADD PRIMARY KEY (字段名)

　　　　ALTER TABLE table_name DROP INDEX 索引名

　　　　ALTER TABLE table_name DROP PRIMARY KEY

　　注意：删除主键索引,字段中不能有auto_increment,如果有先修改掉再删.

　　查看索引: show index from table_name;

 　  添加索引不一定就是好的,因为索引也占用资源,比如磁盘空间的占用,并且索引还会影响增删改语句的执行效率

 2.分割数据表

　有时候表的数据过多,也会造成数据查询慢.这时我们需要对表进行分割.表的分割分为垂直分割和水平分割.　

　　　垂直分割是对表的字段进行分割,每个表包含一部分字段,两张表通过关联字段进行关联.如user表和user_info表.

　　　一般将常用的信息数据放入第一张表中,不常用的数据信息放入第二张表中,需要使用的时候才通过关联将数据查询出来.

    　　水平分割是将一张表的信息放入多张表中,从而减少所查询表的数据量.水平分割的步骤:

　　　　 1.创建几张同主表数据结构相同的表(以good表为例)

　　　　create table goods1 like good

　　　　create table goods2 like good

　　　　create table goods3 like good

　　　   2.将原表的数据查出放入新建的表中

　　　　

<?php 
    
    //首先获取goods表里面所有的信息
    $pdo = new PDO('mysql:host=localhost;dbname=teach;charset=utf8','root','');

    $sql = 'select * from goods';

    //执行  返回的是预处理对象
    $stmt = $pdo->query($sql);

    // var_dump($stmt);
    //返回的是二维数组
    $res = $stmt->fetchAll(2);


    //把这里面提取出来的数据 放到3张表里面去  goods0  goods1  goods2
    foreach($res as $k => $v){

        $tablename = 'goods'.$v['id'] % 3;

        $sqls = 'insert into '.$tablename.'(id,title,pic,price,url) values ("'.$v['id'].'","'.$v['title'].'","'.$v['pic'].'","'.$v['price'].'","'.$v['url'].'")';

        $pdo->exec($sqls);

    }

 　　　3.查询数据

<?php 

    $id = 134;

    //根据id获取表名
    $tablename = 'goods'.$id % 3;

    $pdo = new PDO('mysql:host=localhost;dbname=teach;charset=utf8','root','');

    $sql = "select * from $tablename where id = $id";

    //执行  返回的是预处理对象
    $stmt = $pdo->query($sql);

    $res = $stmt->fetch(2);

    var_dump($res);

3.设置主从服务器

　　设置主从服务器.可以对重要数据进行备份,还可以分担数据库读取的压力.可以实现主服务器进行增删改操作,从服务器进行单独的查询操作.

　　1.主服务器配置:　

　　修改主服务器的mysql 配置文件
　　vim /etc/my.cnf

　　[mysqld]

　　log-bin=mysql-bin //[必须]启用二进制日志
　　server-id=1

　　重启mysql服务
　　service mysql restart

　　主服务器创建mysql用户
　　授权给从数据库服务器
　　GRANT REPLICATION SLAVE ON *.* to 'slaver'@'%' identified by '123456'

　　查看当前主服务器状态
　　show master status

 　  2.从服务器配置:

　　修改从服务器的mysql配置文件

　　vim /etc/my.cnf

　　[mysqld]

　　 server-id设置和主数据库服务器不要一样
　 　server-id=2

　　重启mysql服务
　　service mysql restart

　　执行同步SQL语句
　　change master to master_host='主服务器的ip址',master_user='slaver',master_password='123456',master_port =3306,master_log_file='mysql-bin.000006',master_log_pos=xxxxxx;

　　start slave; 开启slave同步进程
　　stop slave; 停止slave同步进程

　　主从同步检查
　　show slave status\G;

　　3.php文件代码

　　配置文件:

<?php 

return [
    'mysql' => [
        'driver' => 'mysql',
        'write'=>[
            'host' => '192.168.248.17',
        ],
        'read'=>[
            'host' => '192.168.248.18',
        ],
        'port' => '3306',
        'database' =>'212',
        'username' => 'root',
        'password' => '123456',
        'charset' => 'utf8',     
    ]
];

　　主服务器代码:

<?php 

    $res = include 'config.php';

    $pdo = new PDO('mysql:host='.$res['mysql']['write']['host'].';dbname=212;charset=utf8','root','123456');

    $sql = 'insert into uname(uname,age) values ("dsads",30)';

    $rs = $pdo->exec($sql);

    var_dump($rs);

　　从服务器代码:

<?php 

    $res = include 'config.php';

    $pdo = new PDO('mysql:host='.$res['mysql']['read']['host'].';dbname=212;charset=utf8','root','123456');

    $sql = 'select * from uname';

    $stmt = $pdo->query($sql);

    $rs = $stmt->fetchAll(PDO::FETCH_ASSOC);

    var_dump($rs);