mysql一些常见问题

mysql有哪些存储引擎

InnoDB,MyISAM等等

InnoDB和MyISAM的区别

存储结构

  • MyISAM

    每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)。

  • InnoDB

    所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

存储空间

  • MyISAM

    可被压缩,存储空间较小。支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。

  • InnoDB

    需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

事务支持

  • MyISAM

强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。

  • InnoDB

支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

CURD操作

MyISAM

如果执行大量的SELECT,MyISAM是更好的选择。(因为没有支持行级锁),在增删的时候需要锁定整个表格,效率会低一些。 相关的是innodb支持行级锁,删除插入的时候只需要锁定改行就行,效率较高

InnoDB

如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。 DELETE 从性能上InnoDB更优,但DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除,在innodb上如果要清空保存有大量数据的表,最好使用truncate table这个命令。

外键

MyISAM不支持,InnoDB支持

mysql的索引

  • B+树
  • hash索引
  • 全文索引 (倒排索引)

hash索引和B+树索引有什么区别

hash索引底层就是hash表,查询时,通过hash函数计算出数据存储位置,从而获得实际数据。 B+树底层实现是 多路平衡查找树,对于每一次查询都是从根节点出发,向下查找,直到查找到叶子节点,得到要查询的数据。

  • hash索引查询效率更高
  • hash索引无法排序
  • hash索引无法根据前缀查询,如like ‘aaa%’
  • hash索引无法用于比较
  • hash索引虽然在等值查询上较快,但是不稳定,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差。 B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.

数据库索引为什么不用hash表而用b+树

  1. hash表只能匹配是否相等,不能实现范围查找 select * from xx where id > 23; 这时就没办法索引了
  2. 当需要按照索引进行order by时,hash值没办法支持排序 select * from xx order by score desc; 如果score为建立索引的字段,hash值没办法辅助排序。
  3. B树种的组合索引可以支持部分索引查询 如(a,b,c)的组合索引,查询中只用到了a和b也可以查询的, 如果使用hash表,组合索引会将几个字段合并hash,没办法支持部分索引
  4. 当数据量很大时,hash冲突的概率也会非常大

什么情况下无法使用索引

  • 列参与了数学运算或者函数
  • 在字符串like时左边是通配符.类似于’%aaa’.
  • 当mysql分析全表扫描比使用索引快的时候不使用索引.
  • 当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.

事务相关

什么是事务

事务是一系列的操作,他们要符合ACID特性

最常见的理解就是:事务中的操作要么全部成功,要么全部失败

事务的四大特征ACID

  • 原子性(Atomicity) 要么全部成功,要么全部失败.不可能只执行一部分操作.
  • 一致性(Consistency) 系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态.
  • 隔离性(Isolation) 通常来说:一个事务在完全提交之前,对其他事务是不可见的.也有例外情况
  • 持久性(Durability) 一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果.

同时有多个事务在进行会怎么样呢

多事务的并发进行一般会造成以下几个问题:

  • 脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.
  • 不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.
  • 幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成”幻觉”.

MySQL的事务隔离级别

MySQL的四种隔离级别如下:

  • 未提交读(READ UNCOMMITTED)

    这就是上面所说的例外情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).

    这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.

  • 已提交读(READ COMMITTED)

    其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.

  • REPEATABLE READ(可重复读) 可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是 幻读,当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

  • SERIALIZABLE(可串行化)

    这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

Innodb使用的是哪种隔离级别

InnoDB默认使用的是可重复读隔离级别.

对MySQL的锁了解吗?

当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制。

MySQL都有哪些锁

从锁的类别上来讲,有共享锁排他锁.

  • 共享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个.

  • 排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥.

锁的粒度取决于具体的存储引擎,InnoDB实现了行级锁,页级锁,表级锁。

他们的加锁开销从大到小, 并发能力也是从大到小。

mysql开发规范

数据库命令规范

  • 所有数据库对象名称必须使用小写字母并用下划线分割
  • 所有数据库对象名称禁止使用mysql保留关键字 (如果表名中包含关键字查询时,需要将其用单引号括起来)
  • 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符
  • 临时库表必须以tmp_为前缀并以日期为后缀,备份表必须以bak_为前缀并以日期(时间戳)为后缀
  • 所有存储相同数据的列名和列类型必须一致 (一般作为关联列,如果查询时关联列类型不一致会自动进行数据类型隐式转换,会造成列上的索引失效,导致查询效率降低)

数据库基本设计规范

所有表必须使用Innodb存储引擎

没有特殊要求(即Innodb无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam,5.6以后默认的为Innodb)Innodb 支持事务,支持行级锁,更好的恢复性,高并发下性能更好

数据库和表的字符集统一使用UTF8

兼容性更好,统一字符集可以避免由于字符集转换产生的乱码,不同的字符集进行比较前需要进行转换会造成索引失效

所有表和字段都需要添加注释

使用comment从句添加表和列的备注 从一开始就进行数据字典的维护

尽量控制单表数据量的大小,建议控制在500万以内

500万并不是MySQL数据库的限制,过大会造成修改表结构,备份,恢复都会有很大的问题 可以用历史数据归档(应用于日志数据),分库分表(应用于业务数据)等手段来控制数据量大小

谨慎使用MySQL分区表

分区表在物理上表现为多个文件,在逻辑上表现为一个表 谨慎选择分区键,跨分区查询效率可能更低 建议采用物理分表的方式管理大数据

尽量做到冷热数据分离,减小表的宽度

MySQL限制每个表最多存储4096列,并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率(表越宽,把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO) 更有效的利用缓存,避免读入无用的冷数据 经常一起使用的列放到一个表中(避免更多的关联操作)

禁止在表中建立预留字段

预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型,所以无法选择合适的类型 对预留字段类型的修改,会对表进行锁定

禁止在数据库中存储图片,文件等大的二进制数据

通常文件很大,会短时间内造成数据量快速增长,数据库进行数据库读取时,通常会进行大量的随机IO操作,文件很大时,IO操作很耗时 通常存储于文件服务器,数据库只存储文件地址信息

禁止在线上做数据库压力测试

禁止从开发环境,测试环境直接连接生成环境数据库

数据库字段设计规范

优先选择符合存储需要的最小的数据类型

  • 原因 列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少也越少 在遍历时所需要的IO次数也就越多, 索引的性能也就越差

  • 方法 1)将字符串转换成数字类型存储,如:将IP地址转换成整形数据。 2)对于非负型的数据(如自增ID、整型IP)来说,要优先使用无符号整型来存储

避免使用TEXT、BLOB数据类型

最常见的TEXT类型可以存储64k的数据

  • 建议把BLOB或是TEXT列分离到单独的扩展表中
  • TEXT或BLOB类型只能使用前缀索引

避免使用ENUM类型

  • 修改ENUM值需要使用ALTER语句
  • ENUM类型的ORDER BY操作效率低,需要额外操作
  • 禁止使用数值作为ENUM的枚举值

尽可能把所有列定义为NOT NULL

原因:

  • 索引NULL列需要额外的空间来保存,所以要占用更多的空间;
  • 进行比较和计算时要对NULL值做特别的处理

使用TIMESTAMP(4个字节)或DATETIME类型(8个字节)存储时间

TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。TIMESTAMP 占用4字节和INT相同,但比INT可读性高超出TIMESTAMP取值范围的使用DATETIME类型存储。 经常会有人用字符串存储日期型的数据(不正确的做法)

  • 缺点1:无法用日期函数进行计算和比较
  • 缺点2:用字符串存储日期要占用更多的空间

同财务相关的金额类数据必须使用decimal类型

  • 非精准浮点:float,double
  • 精准浮点:decimal

Decimal类型为精准浮点数,在计算时不会丢失精度。占用空间由定义的宽度决定,每4个字节可以存储9位数字,并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。

索引设计规范

限制每张表上的索引数量,建议单张表索引不超过5个

每个Innodb表必须有个主键

不要使用更新频繁的列作为主键,不适用多列主键(相当于联合索引) 不要使用UUID、MD5、HASH、字符串列作为主键(无法保证数据的顺序增长)。 主键建议使用自增ID值。

常见索引列建议·

  • 出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列
  • 包含在ORDER BY、GROUP BY、DISTINCT中的字段
  • 并不要将符合1和2中的字段的列都建立一个索引,通常将1、2中的字段建立联合索引效果更好
  • 多表join的关联列

如何选择索引列的顺序

建立索引的目的是:希望通过索引进行数据查找,减少随机IO,增加查询性能,索引能过滤出越少的数据,则从磁盘中读入的数据也就越少。

  • 区分度最高的放在联合索引的最左侧(区分度=列中不同值的数量/列的总行数)
  • 尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小,一页能存储的数据量越大,IO性能也就越好);
  • 使用最频繁的列放到联合索引的左侧(这样可以比较少的建立一些索引)。

如何进行mysql的语句优化

  • 一看索引;
  • 二看not,有not的话会导致索引无效,会强行全局搜索;
  • 三看语句嵌套,嵌套太多了,人看着都费劲,机器看着也费劲

什么是SQL注入(SQL Injection)

所谓SQL注入式攻击,就是攻击者把SQL命令插入到Web表单的输入域或页面请求的查询字符串,欺骗服务器执行恶意的SQL命令。

在某些表单中,用户输入的内容直接用来构造(或者影响)动态SQL命令,或作为存储过程的输入参数,这类表单特别容易受到SQL注入式攻击。

分库分表

当mysql数据库单表数据量破千万以后,MySQL性能将下降厉害,并且随着业务的规模化,单表将很难抗住并发压力,出现故障后恢复时间变长对业务影响也会变大,因此需要考虑数据水平扩展。

想水平扩展,就需要分库分表的支持,分库分表这四个字说起来很容易,按照id取模将数据打散分摊压力,但是干完这些之后需要解决由此带来的问题,这些很难:

  1. 事务支持,扩库/扩表后事务就成分布式的了,问题难度显然上升了一个级别
  2. 查询结果合并,这个看起来不难,但是把order by/limit/查询中不带分表字段等加上,要解决的问题还也不少
  3. join,这个更难
  4. 分库?分表?还是分库分表?这个需要考虑并做一个决定
  5. 分完后能否合并?分容易,和很难
  6. 。。。