Mysql基础操作

Posted on | In | | Visitors:

Mysql基础操作

连接和关闭:mysql -u用户名 -p密码 -h主机地址 -P端口号

其他:\G,\c,\q,\s,\h,\d

InnoDB 表引擎

  • 默认事务型引擎，最重要最广泛的存储引擎
  • 数据存储到共享表空间，可以通过配置分开
  • 对主键查询的性能高于其他类型的存储引擎
  • 内部做了很多的优化，从磁盘读取数据时自动创建哈希索引
  • 通过一些机制和功能支持真正的热备份
  • 支持崩溃后的安全回复
  • 支持行级锁
  • 支持外键

MyISAM表引擎

  • 拥有全文索引、压缩、空间函数
  • 不支持事务和行级锁，不支持崩溃后的安全回复
  • 表存储在两个文件 MYD和MYI
  • 设计简单，某些场景下性能很好

其他表引擎
Archive、Blackhole、CSV

Mysql锁机制

基础概念：
表锁是日常开发中年常见的问题，因此是面试中最常见的考察点，当多个查询同一时刻进行数据修改时，就会产生并发控制问题

分为共享锁和排他锁

读锁

共享的，不堵塞，多个用户可以同时读取一个资源，互不干扰

写锁

排他的，一个写锁会阻塞其他的写锁和读锁，这样可以只允许一个人进行写入，防止其他用户进行的操作

锁粒度

表锁，系统性能开销最小，会锁定整张表，MyISAM

行锁，最大程度的支持并发处理，但是也带来了最大的锁开销InnoDB

事务处理

服务器层不管事务管理，由下层的引擎实现，所以一个事务中，使用多种存储引擎不靠谱

在非事务的表上执行事务操作Mysql不会提醒不会报错

存储过程

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

存储过程就是有业务逻辑和流程的集合

可以在存储过程中创建表，更新数据、删除等等

使用场景:：

通过把处理封装在容易使用的单元中，简化复杂的操作

保证数据的一致性

简化对变动的管理

触发器

提供给程序员和数据分析员来保证数据完整性的一种方法，他是与表事件相关的特殊的存储过程

使用场景:

通过数据库中的相关表实现级更改

实时健康空某张表的某个字段的更改而需要做出相应的处理

某些业务编号的生成

滥用会造成数据库以及应用程序的维护困难

MySQL索引

简单描述Mysql中索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响？

Mysql索引的基础和类型：

索引类似于数据的目录，想要找到一本书的某个特定的主题，需要先查找书的目录，定位对应的页码

存储引擎使用类似的方式进行数据查询，先去索引中找到对应的值，然后根据匹配的索引找到对应的数据行
索引对性能的影响:

  • 大大减小服务器需要扫描到数据量

  • 帮助服务器避免排序和临时表

  • 将随机I/O变为顺序I/O
  • 大大提高查询速度，降低些的速度，占用磁盘

索引的使用场景

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

索引的类型

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

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

  • 一个表只能有一个主键索引，可以有多个唯一索引

  • 主键索引一定是唯一索引，唯一索引不是主键索引

  • 主键可以与外键构成参照完整的约束防止数据不一致，唯一索引不可以

Mysql索引的创建原则:

  • 最适合索引的列是出现在where子句的列，或者链接子句中的列，尾部是在select关键字后的列
  • 索引列的技术越大，效果越好
  • 对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间
  • 根据情况创建复合索引，复合索引可以提高查询效率
  • 避免创建过多的索引，会占用额外的磁盘空间，降低写操作的效率
  • 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用

Mysql索引的注意事项:

  • 复合索引遵循前缀原则
    key(a,b,c)
    where a= 1 and b = 2 and c = 3
    where a= 1 and b = 2
    where a = 1
    where b = 2 and c = 3

  • like查询，%不能在前，可以使用全文检索 where name like “%wang%” 索引不起作用

  • column is null 可以使用索引
  • 如果mysql估计使用索引的时间比全表扫描要慢，会自动放弃索引
  • 如果or前的条件的列有索引，后面的没有，索引都不会被用到
  • 列类型是字符串时，一定要加引号，否则索引会失效

Mysql的SQL语句编写考点

问题：有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

UPDATE A INNER JOIN B ON A.ID = B.ID SET A.C1 = B.C1,A.C2 = B.C2

答案：

update A,B set A.c1 = B.c1,A.c2 = B.c2 where A.id = B.id and 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

六种关联查询：

  • 交叉连接 cross join

    select * from A,B,C

select * from A cross join B cross join C

没有任何的关联条件，结果是笛卡尔积，意义不大

  • 内连接 inner join

    select * from A,B where A.id = B.id

select * from A inner join B on A.id = B.id

多表中同事符合某种条件记录的集合，分为三类：

等值连接: ON A.id = B.id

不等值连接：ON A.id > B.id

自连接：select * from A T1 inner join A T2 ON T1.id=T2.pid

INNER JOIN 可以缩写成 JOIN

  • 外连接

    左外连接：LEFT OUTER JOIN 以左表位主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写为LEFT JOIN

右外连接：RIGHT OUTER JOIN 简写为 right join

  • 联合查询

    select from A UNION select from B UNION …

就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的就是联合查询的列数要相等，相同的记录会合并

如果使用UNION ALL，不会合并重复行

  • 全连接
    Mysql 不支持全连接

可以使用left join和union和right join 联合使用

select 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)

Mysql查询优化

项目中优化sql语句执行效率的方法，从哪些方面，sql语句性能如何分析？

  • 查找分析查询速度慢的原因
  • 优化查询过程中的数据访问
  • 优化长难的查询语句
  • 优化特定类型的查询语句

分析SQL查询慢的方法:

  • 记录慢查询日志

    分析查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用pt-query-digest工具进行分析

  • 使用show profile

    set profiling = 1 开启，服务器上执行的所有语句会检测消耗的时间，存到临时表中

show profiles;查看临时表

show profiles for query QUERY ID 查看某一条记录的具体时间分配

  • show status

    show status 会返回一些计数器，show global status 查看服务器级别的所有计数
    有时候根据这些计数，可以猜出哪些操作代价较高或者消耗时间较多

  • show processlist

    观察是否有大量的线程处于不正常的状态或者特使

  • explain

    分析单条SQL语句，也可以使用desc 来达到一样的效果
    desc select * from a

优化查询过程中的数据访问

  • 访问数据太多当值查询性能下降
  • 确定应用程序是否在检索大量超过需要的数据，可能是太多行或者列
  • 确认Mysql服务器是否在分析大量不必要的数据行

避免使用如下SQL:

  • 查询不需要的记录，使用limit解决
  • 多表关联返回全部列，指定A.id,A.name
  • 总是取出全部列，select * 会让优化器完成索引覆盖扫描
  • 重复查询相同的数据使用缓存

是否在扫描额外的记录：

使用explain进行分析，如果发现查询需要扫描大量的数据但是只返回少数的行，可以通过以下技巧去优化：
使用索引覆盖扫描，把所有的列都放在索引中，这样索引不需要回标获取对应行就可以返回结果

修改表数据库和表的结构，修改数据表的范式

重写sql，让优化器可以以更优的方式执行

优化长难的查询语句:

mysql 内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢的多
使用尽可能少的查询十号的，但是有的时候将一个大的查询分为几个小的查询也是有必要的

  • 切分查询

    将一个大的查询分为多个小的相同的查询，比如一次性删除1000万的数据要比一次删除1万，暂停一会儿的方案跟那个价损耗服务器的开销

  • 分解关联查询

    可以将一条关联语句分解成多条sql来执行

让缓存的效率更高

执行单个查询可以减少锁的竞争

在应用层做关联可以更容易对数据库进行拆分

优化特定类型的查询语句:

  • 优化count()查询

    count()中的 会忽略所有的列，直接统计所有的列数，因此不要使用count(列名)

myisam中，没有任何where条件的count(*)非常快

当有where条件时，myisam的count统计不一定比其他表引擎快

可以使用explain查询近似值，用近似值替代count(*)

增加汇总表

使用缓存

  • 优化关联查询

    确定on或者using自居的列上有没有索引

确保group by 和order by 中只有一个表的列，这样才有可能使用索引

  • 优化子查询

    用关联查询替代

  • 优化group by和distinct

这两种查询均可以使用所索引来优化，是最有效的优化方法

关联查询中，使用标识列进行分组的效率会更高

如果不需要order by，进行group by时使用 order by null ,mysql不会再进行文件排序

with rollup超级聚合

  • 优化limit分页

    limit偏移量大的时候，查询效率较低

可以记录上次查询的最大ID,下次查询时直接根据该ID来查询

  • 优化union查询

    union all 的效率高于union，去重放到应用层去解决

首先说明如何定位低效sql

根据sql语句可能低效的原因做排查，从索引着手，考虑数据访问的问题，长难句的问题，特定类型优化的问题

Mysql高可扩展和高可用

简述mysql分表操作和分区的工作原理，分别说说分区和分表的使用场景和各自的优缺点

分区表的原理

对用户而言，分区表是一个独立的逻辑表，但是底层Mysql将其分层了多个物理子表，这对用户来说是透明的，每一个分区表都会使用一个独立的表文件

创建表的时候使用partition by 子句定义每个enquiry存放的数据，执行查询的时候，优化器会根据分区定义过滤哪些没有我们需要数据的分区，这样查询只需要查询所需数据在的分区既可

主要目的是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关的数据放在一起，而且如果想一次性删除整个去的数据也很方便

适用场景：

  • 表非常大，无法全部存在内存，或者旨在表的最后有热点数据，其他都是历史数据
  • 分区表的数据更易维护，可以对独立的分区进行独立的操作
  • 分区表的数据可以分布在不同的及其上，从而高效利用资源
  • 可以使用分区避免某些特殊瓶颈
  • 可以备份和恢复独立的分区

限制：

  • 一个表中只能有1024个分区
  • 5.1版本中，分区表表达式必须是证书，5.5可以使用列分区
  • 无法使用外键
  • 需要对现有表的结构进行修改
  • 所有分去都必须使用相同的存储引擎
  • 分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来
  • 分区函数中可以使用的函数和表达式有一定的限制
  • 某些存储引擎不支持分区
  • 对于myisam分区表不能使用load index to cache
  • 对于myisam使用分区表时需要打开更多的文件描述符

分库分表的原理

通过一些hash算法或者工具实现将一张数据垂直或者水平进行物理切分

使用场景：

  • 单表记录的数据达到百万或者千万
  • 可以解决表锁的问题

分表方式:

  • 水平分割

表很大，分割后可以降低在查询时需要读取的数据和索引的页数，同时也降低了索引的层数，提高查询速度

使用场景:表中的数据本身就有独立性，例如表中分别记录各个地区的数据或者不同时期的数据，也别是有数据常用，有些不常用

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

缺点：

  • 给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需要unoin操作
  • 在许多数据库应用中，这种负责性会超过它带来的有点，查询时会增加读取一个索引层的磁盘次数

垂直分表：

把主键和列放在不同的表中

使用场景：

  • 一个表中某些列常用，有些不常用
  • 可以使数据量变小，一个数据页能存储更多的数据，查询时减少I/O次数

缺点：

管理冗余列，查询所有的数据需要join操作

整体缺点：有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，不易于扩展

对于应用层来说，逻辑算法会增加开发成本

Mysql复制原理和负载均衡

在主库上把数据更改记录到二进制日志

从库将主库的日志复制到自己的中继日志中

从库读取中继日志中的事件，将其放在自己的库中执行

解决的问题：

  • 数据分布：随意停止或者开始复制，并在不同地理位置分布数据备份

  • 负载均衡：降低单个服务器的压力

  • 高可用和故障切换：帮助应用程序避免单点失败

  • 升级测试：可以使用更高版的Mysql作为从库

设定网站的用户数据量在千万级，但是活跃用户的数据量只有1%,如何通过优化数据库提高用户的访问速度？

Mysql安全性考点

SQL语句应该考虑哪些安全性问题

SQL查询的安全方案

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

Mysql其他安全设置

  • 定期做数据备份
  • 不给查询用户root权限，合理分配权限
  • 关闭远程访问数据库权限
  • 修改root口令
  • 删除多余用户
  • 修改root用户名称
  • 限制一般用户浏览其他库
  • 限制用户对数据文件的访问权限