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 profiles
    • show profile for query 临时id
      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权限没合理分配权限
  • 关闭远程访问数据库权限
  • 删除多余的用户