PHP基础知识-数据库篇

1:请写出下面MySQL数据类型表达式的意义(int(0),char(16),varchar(16),datetime,text)

int(0):整形,宽度0
char(16)定长字符串,宽度16
varchar(16)变长字符串,宽度16
datetime事件日期类型
test字符串类型,存储大文本数据

延伸:MySQl数据类型

• 整数类型:TINYINT,SAMALLINT, MEDIUMINT,INT,BIGINT

  • 属性:可以配合UNSIGNED
  • 长度:不会限制值得合法范围,只会影响现显示字符的个数

• 实数类型:FLOAT,DOUBLE,DECIMAL

  • DECIMAL可存储比BIGINT还打的整数,可以用于储存精确的小数
  • FLOAT和DOUBLE支持使用标准的浮点型进行近似计算

• 字符串类型:VARCHAR,CHAR,test,blob

  • varchar用于可变长度字符串,比定长类型节省空间
  • varchar使用1个或2个字节记录字符串长度,列长度小语255字节使用1个字节表示,否则使用两个字节
  • varchar长度超多指定长度会被截断
  • char是定长的,根据定义长度分类空间
  • char是适合存储很短的字符串,或者所有的值都接近同一长度
  • 对于经常变更的数据,char比varchar更好,char不容易产生碎片

• 日期和事件类型

  • 尽量使用timestamp,比datetime空间效率高
  • 如过要存储微妙,可以使用bigint存储

  延伸:MySQL基础操作

• 常见操作

  • MySQL的连接和关闭:mysql -u -p -h -p

  延伸:MySQL数据表引擎

• InnoDB表引擎

  • 默认事务性引擎,最红要最广泛的存储引擎,性能非常优秀
  • 数据存储在共享表空间,可以通过配置分开
  • 对主键查询的性能高于其他的类型的存储引擎
  • 支持崩溃后的安全恢复
  • 支持行级锁
  • 支持外键

• MyISAM表引擎

  • 5.1版本前是默认存储引擎
  • 拥有全文索引,压缩,空间函数
  • 不支持事务和行级锁,不支持崩溃后的安全恢复
  • 表存储在两个文件,MYD和MYI
  • 设计简单,某些场景下性能很好

• 其他表引擎

  • Archive,Blackhole,CSV,Memory
  • 优先选择InnoDB

  延伸:MySQl锁机制

• 基础概念:当多个查询同一时刻进行数据修改时,就会产生并发控制的问,共享锁和排他锁其实就是读锁和写锁
• 读锁:共享的,不堵塞,多个用户可以同时读一个资源,互不干扰
• 写锁:排他的,一个写锁会阻塞其他的写锁和读锁,这样可以只允许一个人进行写入,防止其他用户读取正在写入的资源

• 锁粒度

  • MyISAM:表锁,性能开销最小,会锁定整张表
  • InnoDB:行锁,最大程度的支持并发处理,但也带来了最大的锁开销

  行锁如果没有索引，会变为表锁

  延伸:事务处理

  InnoDB支持事务处理

• 存储过程

  • 为以后使用而保存的一条或多条MySQL语句的集合

• MySQl触发器

  • 可以通过数据库中的相关表实现级联更改

2:简述MySQl索引.主键索引,唯一索引,联合索引的区别,对数据库性能的影响

索引类似于书签,现在索引里找到对应的值,然后根据匹配的索引找到对应的数据
主键索引一定是唯一索引,唯一索引不一定是主键索引
主键索引不允许有空值
混合索引是将多个列组合在一起创建索引,可以覆盖多个列
延伸

MySQL索引的基础和类型

• 索引的基础:类似于书籍的目录,先去索引里找到对应的值,然后根据匹配的索引找到对应的数据行
• 索引是帮助MySQl高效获取数据的数据结构，也就是说索引式数据结构，使用的B+树。

• 索引对性能的影响

  • 大大减少服务器需要扫描的数据量
  • 帮助服务器避免排队和临时表
  • 大大提高查询速度,降低写的速度,占用磁盘

• 索引的使用场景

  • 对于非常小的表,大部分情况下全表扫描效率更高
  • 中大型表,索引非常有效
  • 特大型表,建立和使用索引代价将随之提高,可以使用分区技术来解决

• 索引的类型

  • 普通索引:最近本的索引,没有任何约束限制
  • 唯一索引:与普通索引类似,但具有唯一性约束
  • 主键索引:特殊的唯一索引,不允许有空值
  • 组合索引:将多个列组合在一起创建索引,可以覆盖多个列
  • 外键索引:只有InnoDb才能使用,保证数据的一致性,完整性和实现级联操作
• 全文索引:MySQL自带全文索引只能用于MyISAM,并且只能对英文进行全文检索

• 唯一索引和主键索引的区别

  • 一个表只能有一个主键索引,可以有多个唯一索引
  • 主键索引一定是唯一索引,唯一索引不一定是主键索引
  • 主键索引只能有一个,唯一索引可以有多个
  • 主键可以与外键构成参照完整性约束,防止数据不一致

• MySQL索引的创建原则

  • 原则
  • 最适合索引的列是出现在where字句里的列,或者链接字句里的列而不是出现在select关键字后的列
  • 索引列的基数越大,索引的效果越好
  • 对字符串进行索引,应该制定一个前缀长度,可以节省大量所以空间
  • 根据情况创建复合索引,复合索引可以提高查询效率
  • 避免创建过多的索引
  • 逐渐尽可能选择较短的数据类型

• MySQL索引注意事项

  • 复合索引遵循前缀原则(连续概念)
  • like查询,\%不能在前,可以使用全文索引
  • cloumn is null可以使用索引
  • 如过MySQL估计使用索引比全表扫描慢,会放弃使用索引
  • 如过or前的条件有索引,后面没有索引,索引失效
  • 如果字符串类型,查询时一定要加引号,否则索引失效

3:有A(id,sex,par,c1,c2),B(id,age,c1,c2)两张表,其A.id与B.id关联,现要求写出一条SQL语句,将B表age>50的记录的c1,c2更新到A表统一记录的c1,c2字段

update A,B set A.c1 = B.c1,A.c2 = B.c2 where A.id = B.id where B.age > 50
update A inner join B on A.id = B.id set A.c1 = B.c1,A.c2 = B.c2 where b.age > 50

延伸:MySQL关联update语句

• 关联更新

  • update A,B set A.c1 = B.c1,A.c2 = B.c2 where A.id = B.id
  • update A inner join B on A.id = B.id set A.c1 = B.c1,A.c2 = B.c2 where...

  延伸:MySQL关联查询语句

• 交叉链接(cross join)

  • select * from a,b(,c)
  • select * from a cross join b (cross join c)

• 内连接(inner join)不以任何表为主,只找on后面的条件

  • select * from a,b where a.id = b.id
  • select * from a inner join b on a.id = b.id
  • select * from a t1 inner join a t2 on t1.id = t2.pid
• 外连接(left join/right join)

• 联合查询(union/union all)把多个结果集合在一起,union签的结果为准,需要注意的是联合查询的列数要相等,相同的记录会合并(union all不会合并重复记录,并且union all效率高于union)

  • select from a union select from b union ...

• 全连接(full join)MySql不支持全连接,可以使用left join 和 union 和 right join 联合使用

  • select 8 from a left join b on a.id = b.id union select * from a right join b on a.id = b.id

• 嵌套查询(不建议使用)用一条sql语句的结果作为另一条sql语句的条件

  • select * from a where id in (select id from b)

  例题

  
  

4:请简述项目优化sql语句执行效率的方法,从哪些方面入手,sql语句性能如何分析?

延伸:查找分析查询速度慢的原因和分析方法

• 记录慢查询日志

  • 分期查询日志,不要直接打开,比较浪费时间,可以使用pt-query-digest工具进行分析

• 使用show profile
  set profiling = 1;开启,服务器上执行的所有语句会检测小耗时间,存到临时表里

  • show profiles
    
  • show profile for query 临时id
    
• 使用show status:会返回一些计数器,show global status查看服务器级别的所有计数,可以CIA出那些操作消耗时间较多
• 使用show processlist:观察是否有大量线程处于不正常状态或者特征

• 使用explian(desc)分析单条语句

  • expian(desc) select * from a

延伸:查询速度慢的优化

• 优化数据访问的方法

  • 查询不需要的记录,使用limit
  • 多表关联返回全部列,指定a.id,a.name
  • 避免使用select *
  • 重复查询相同的数据是可以缓存数据
  • 是否在扫描额外的记录(优化:使用索引)
  • 改变数据库和表的结构,修改数据表的范式
  • 重写sql语句

• 优化长难的查询语句

  • 切分查询,将一个大的查询分为多个小的相同的查询,节省服务器开销
    列:如过查询1w条记录们可以先查询100条,过一会在查询100条
  • 分解查询:将一条sql语句差分成多条sql语句

• 优化特定类型的语句

  • 优化count()查询:count(*)会忽略所有列,直接直接统计所有列数,不要使用count(列名)
  • 优化关联查询:确定on或者using字句的列上有索引
  • 优化子查询:尽可能使用关联查询来替代

5:简述MySQL分表操作和分区操作的工作原理,分别说说分区和分表的使用场景和各自优缺点

分区表的原理

• 工作原理:对用户而言,分区表示一个独立的逻辑表,但底层MySQL将其分成了多个物理子表,这对用户来说是透明的,每一个分区表都会使用一个独立的表文件
• 创建表时使用partition by字句定义每个分区存放的数据,执行查询时,优化器会根据分区定义锅炉掉那些没有我们需要的数据分区
• 主要目的是将所有数据按着一个较粗的粒度放在同一个表,删除方便

• 使用场景

  • 表非常大,无法全部存在内存,或者只能在标的最后有热点数据,其他都是历史数据
  • 分区表的数据更易维护,可以对独立的分区进行独立操作
  • 分区表的数据可以分布在不同的机器上,从而高效利用资源
  • 可以备份和恢复独立的分区

• 缺点

  • 一个表最多只能有1024各分区
  • 5.1版本中,分区表表达式必须是整数,5.5可以使用列分区
  • 分区字段如过有主键和唯一索引列,name主键列和唯一索引列都必须包含进来
  • 无法使用外键索引
  • 需要对现有表的结构进行修改
  • 所有分区都必须使用相同存储引擎

  分库分表的原理

• 工作原理:通过一些hash算法或者工具将一张数据表垂直或水平进行物理分切

• 使用场景

  • 单表记录条数达到百万千万级别时
  • 解决表锁的问题

• 分表方式:水平分割
  

  • 使用场景
  • 有些数据常用,有些数据不常用

  • 需要把数据存放在多个介质上

    • 缺点
  • 给应用增复杂度,通常查询需要多个表名,查询所有数据都需要union操作
  • 降低效率

• 分表方式:垂直分割
  

  • 使用场景
  • 如过一个表里有的列常用,有的列不常用

  • 可以使数据行变小

    • 缺点
  • 增加操作复杂度,需要join

• 分库分表缺点:有些分表基于逻辑算法,扩展性较差,增加开发成本

  延伸:MySQL的复制原理及负载均衡

• 主从复制工作原理

  • 在主库上把数据更改记录到二进制日志
  • 从库将主库日志复制到自己的中继日志
  • 从库读取中继日志里的事件,将其重新放到数据库里

• 解决的问题

  • 数据分布:随意的停止或开始复制,并在不同的地理位置分布数据备份
  • 负载均衡:降低单个服务器的压力
  • 高可用和故障切换
  • 升级测试

6:sql语句应该考虑哪些安全性问题

MySQL的安全方案

• 使用预处理语句防sql注入
  
• 写入数据库的数据要进行特殊字符的转义
• 查询错误信息不要返回给用户,讲错记录到日志

• 尽量实用pdo连接数据库,mysql的函数库性能不如pdo,而且未来可能会淘汰

  MySQL的其他安全方案

• 定期做数据备份
• 不给查询用户root权限没合理分配权限
• 关闭远程访问数据库权限
• 删除多余的用户