PHP之sql优化

/*
在使用tp框架时，在后台我们做商城项目时，后台需要显示很多列表，在做列表展示时，有时候是一张表，有时候会是多张表
对于一张表来说在查询时【我们查表时只需要得到我们需要的字段就ok了，就是要限制查询字段field，不要做全部字段查询】。比如我们要查询user表，它的字段有【username,password,account,nickname,mobile,email,age,addtime,updatetime,logintime,loginip,...】那我们在做登录时，如果是用户名登录，那么我们就只需要查询username,password这两个字段就ok
*/
$user = new User();//模型
$info = request()->param();//得到全部参数
//验证参数
//....
//得到查询结果
$result = $user->field('username,password')->where('username','eq',$info['username'])->find();

/*
在做多表查询时呢，在tp中有三种方法【只是我自己常用的三种，不喜勿喷哈】
1.首先是join查询，就是自己去写，用到那个表自己去一个一个写出来
比如：有一张用户表【user】，用户图片表【img】，
*/
//创建user表
drop table if exists `user`;
create table if not exists `user`(
`id` int primary key auto_increment comment '主键',
`pid` int(11) not null default 0 comment '父id',
`username` varchar(30) not null comment '用户名',
`password` char(64) not null comment '密码',
`account` varchar(30) not null comment '账户',
`nickname` varchar(30) not null comment '昵称',
`mobile` varchar(20) not null comment '手机号',
`email` varchar(20) not null comment '邮箱',
`delete` tinyint(1) not null default 1 comment '是否删除，默认1没删除，0删除'，
`addtime` int(11) not null comment '添加时间',
`updatetime` int(11) not null comment '更新时间',
`logintime` int(11) not null comment '登录时间',
`loginip` varchar(20) not null comment '登录ip',
)engine=innodb default charset=utf8 comment '用户表';
//engine=innodb 声明表的存储引擎，default charset=utf8指定表的字符集，comment '用户表' 表名注释
//注意：【InnoDB存储引擎支持事务、支持行锁、支持非锁定读、支持外键 || MyISAM不支持事务，不支持行级锁，支持表锁，支持全文索引，最大的缺陷是崩溃后无法安全恢复。】

//创建用户图片表
drop table if exists `img`;
create table if not exists `img`(
`id` int primary key auto_increment comment '主键',
`u_id` int not null comment 'user表主键',
`path` varchar(100) not null comment '图片路径',
`addtime` int not null comment '添加时间',
`updatetime` int not null comment '修改时间'
)engine=innodb default charset=utf8 comment '用户图片表';

在这里我们就创建好两张表了，下面就是在tp中使用多表查询

//首先创建两个模型
//1.user模型
class User extends Model
{
    //开启时间戳自动写入和更行
    // 定义时间戳字段名
    protected $createTime = 'addtime';
    protected $updateTime = 'updatetime';

    //关联表user（在这里我们可以使用一对一关联）
    public function img()
    {
        //hasOne('关联模型名','外键名','主键名',['模型别名定义'],'join类型');
        return $this->hasOne('Img','id','id')->setEagerlyType(1);
    }
}

//2.img模型
class Img extends Model
{
    //开启时间戳自动写入
    protected $autoWriteTimestamp = true;
    //定义字段
    protected $createTime = 'addtime';
    protected $updateTime = 'updatetime';

    //关联user表
    public function user()
    {
        //belongsTo('关联模型名','外键名','关联表主键名',['模型别名定义'],'join类型');
        return $this->beLongsTo('User','u_id','id')->setEagerlyType(0);
    }
}

注意在TP中，hasOne和beLongsTo是相对的关系
hasOne是在user表中，关联别的表，即img表，img表中有user表主键，而user表中没有与img表有关的。
beLongsTo是在img表中，关联别的表，在img表中有user表主键，它们是在一个表里可以看到的
【一上我会继续查证实验】

//在user控制器里查询
class User extends Controller
{
    public function index()
    {
        $user = new User();
        $list = $user-field('username,account,nickname,mobile,email')->with(['img'=>function($query){$query->field('path');}])->order('addtime DESC')->select();
        $this->assign('list',$list);
        return $this->fetch();
    }
}

在控制器里自己写join

class User extends Controller
{
    public function index()
    {
        $list = Db::name('user')->alias('u')->join('img i','u.id=i.u_id')->field('u.username,u.account,i.path')->order('u.addtime DESC')->select();
        $thsi->assign('list',$list);
        return $this->fetch();
    }
}

先查一张表，再用foreach

class User extends Controller
{
    $list = (new User())->field('id,username,account')->order('addtime DESC')->select();
    foreach ($list as $k => $v) {
        $path = (new Img())->field('path')->where('u_id','eq',$v['id'])->find();
        $list[$k]['path'] = $path['path']
    }
    $this->assign('list',$list);
    return $this->fetch();
}

上面这三种方法都可以进行多表查询，至于怎么选择就是看你的选择了，我记得我上次在做多表查询时，首先用的是tp提供的多表关联，一共是5张表这样关联的，效果不是怎么好，最后选择的是最后一中方法，关联了一张表后，用foreach得到剩下的数据，效果稍微好点。当然你也可以加缓存，这样可能会好点，但是需要注意的是你的表数据不需要变动的，对表的数据只是展示是没有问题的，如果有操作，在家缓存时就需要注意了。一般老手都知道的，新手可能会踩坑。那就是你加缓存时，你为缓存起的名字。是需要和你的where条件进行关联的，当你的where条件变动时，就需要重行查询，不能用缓存了

---

上面说到sql优化的方法
1.就是在查询时做字段限制，不要做select * form …这样的查询
2.就是我们在创建数据表时对存储引擎的选择
innodb是对数据的insert，update，delete时尽量选择这个【行级锁】
MyISAM是对数据select时选择这个【表级锁】
3.使用jion来代替子查询
4.使用联合（union）来代替手动创建零时表
5.创建表时对字段名的选择，字段属性的选择（选择合适的长度属性）；比如知道字符知道具体的大小长度的选择char（–），对于可变的不固定的选择varchar（–），对于性别这些选择tinyint(1),状态选择的最好用tinyint(—);
6.在做sql查询时尽量少做模糊查询，做了模糊查询那么我们在创建数据库时建立的索引将不起作用了

//尽量少用模糊查询，做的话做这样的稍微好点
select username form user where username like "{$key} . %";
//不要做
select username form user where username like "% . {$key} . %";

7.创建合适的索引

//主键索引
`id` int primary key auto_increment
//唯一索引
alter table `user` add unique `user_account` name('account');
//普通索引
alter table `user` add index `user_model` name('mobile');
//删除索引
alter table `user` drop index name('user_mobile');

注意：

• 避免在索引字段进行计算操作
• 避免在索引字段上使用 not <> !=
• 避免在索引字段上使用空值
• 避免在索引再断上使用函数
• 避免在索引字段上进行数据类型转换
• 避免在索引字段上使用is null 或者 is not null

7.在创建字段时字段不要给null值，尽量都是not null;
8.数据库主从配置，读写分离
9.分库分表
【8.9.这两个我都没有使用过】