MySQL是PHP面试中必考的知识点，以下是我自己总结的几个考察点
1.数据类型
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型
面试中经常考察的一般就是让你说出你知道的数据类型，或者说一下char和varchar的区别等等
1.1数值类型
- 整数类型
整数类型长度可以指定宽度，如：int(11)，对大多数应用没有意义，不会限制值的合法范围，只会影响字符个数
int(5)，存123时前面会自动填充0变成00123
- 实数类型
DECIMAL可存储比BIGINT还大的整数，可以存储精确的小数
FlOAT和DOUBLE类型支持使用标准的浮点进行近似计算
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
1.2日期和时间类型
- 尽量使用TIMESTAMP，比DATRTIME空间效率高
- 用整数保存时间戳的格式不方便处理
- 存储微秒，可以使用bigint
| 类型 | 大小 (字节) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
1.3字符串类型
- varchar类型存储可变长度字符串，char存储定长字符串
- varchar使用1或2个额外字节记录字符串长度，列长度小于255字节则使用1字节表示，否则用2表示
- varchar存储内容超出指定长度，会被截断
- char存储定长，采用空格填充
- char适合存储很短的字符串，或者所有值都接近统一同一长度
- char超出长度，会被截断
- 对于经常变更的数据，char比varchar好，char不容易产生碎片
- 只分配真正需要的空间，更长的列会消耗更多内存
- 尽量避免使用blob/text类型，查询会使用临时表，导致严重性能开销
- 有时可以使用枚举类型代替常用字符类型
- 枚举类型会把不重复的集合存储成一个预定义的集合
- 枚举类型非常紧凑，把列表值压缩到一个或两个字节
- 枚举类型内部存储的是整数
- 尽量避免使用数字作为枚举的常量
- 枚举表会使表大小大大减小
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
1.4列属性
auto_increment、default、not null、zerofill
2.基础操作
连接数据库
mysql -u -p -h -P
3.数据表引擎
3.1 InnoDB
- 默认事务型引擎，最重要最广泛使用的数据引擎，性能优秀
- 数据存储在共享表空间，可以通过配置分开
- 对主键查询性能高于其他类型存储引擎
- 内部做了很多优化，从磁盘读取数据时自动在内存构建hash索引，插入数据时自动构建插入缓冲区
- 通过一些机制和工具支持真正的热备份
- 支持崩溃后的安全恢复
- 支持行级锁
- 支持外键
3.2 MyISAM
mysql5.1版本前数据库默认的存储引擎
- 拥有全文索引、压缩、空间函数
- 不支持事务和行级锁，不支持崩溃后的安全恢复
- 表存储两个文件，MYD和MYI
- 设计简单，某些场景下性能很好（以读操作为主的）
3.3 其他引擎
Archive
- CSV
- Blackhole
- Memory
4.MySQL锁机制
表锁是日常开发中常见问题，当多个查询统一时刻进行数据修改是，就会产生并发控制的问题
4.1 读锁
共享的，不堵塞，多个用户可以同时读一个资源，互不干扰
4.2 写锁
排他的，一个写锁会阻塞其他的写锁和读锁，这样可以只允许一个人进行写入，防止其他用户读取正在写入的资源
4.3 锁粒度
- 表锁
系统性能开销最小，会锁定整张表，MyISAM使用表锁
- 行锁
最大程度支持并发处理，但是也带来了最大的锁开销，InnoDB使用行锁
5.事务、存储、触发器
5.1 事务
5.1.1 条件：
- 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失
5.1.2 说明：
- 提供事务处理的引擎：InnoDB
- 服务器层不管理事务，由下层引擎实现，所以同一事务中，使用多种存储引擎不靠谱
- 在非事务的表上执行事务操作MySQL不会发出提醒，也不会报错
5.2存储过程
- 为以后使用而保存的一条或多条mysql语句的集合
- 存储过程就是有业务逻辑和流程的集合
- 可以在存储过程中创建表，更新数据，删除等等
使用场景
- 通过吧封装在容易使用的单元中，简化复杂的操作
- 保证数据的一致性
- 简化对变动的管理
5.3触发器
提供给程序员和数据分析员来保证数据完整性的一种方法，他是与表事件相关的特色的存储过程
使用场景
- 通过数据库中的相关表实现级联更改
- 实时监控某表中的某个字段的更改而需要作出相应的处理
- 某些业务编号的生成
- 滥用会造成数据库及应用程序的维护困难
6.索引
索引相关知识点在以前的博客中写到过，这里就不在说明
参考：https://blog.yiqiesuifeng.cn/index.php/archives/123/
7.SQL语句编写
7.1 建表
CREATE TABLE `cmf_app` (
`id` int(10) NOT NULL,
`create_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表';
7.2 关联查询
可以用join查询或者联合查询（UNION、UNION ALL）等
7.3 关联更新
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
7.4 SQL语句优化
8.MySQL高可扩展和高可用性
8.1 分区表
对用户而言，分区表示一个独立的逻辑表，但是底层mysql将其分成多个物理子表，这对用户来说是透明的，每一个分区表都会使用一个独立的表文件
创建表时使用partition by子句定义每个分区存放的数据，执行查询时，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询只需要查询所需数据所在的分区即可
8.1.1 适用场景：
- 表非常大，无法全部存在内存，或者只在表的最后又热点数据，其他的都是历史数据
- 分区表的数据更易维护，可以对独立的分区进行独立的操作
- 分区表的数据可以分布在不同的机器上，从而高效实用资源
- 可以使用分区表避免某些特殊瓶颈
- 可以备份和恢复独立的分区
8.1.2 限制：
- 一个表最多1024个分区
- 5.1版本中，分区表表达式必须是整数，5.5可以使用列分区
- 分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来
- 分区表无法使用外键约束
- 需要对现有表的结构进行修改
- 所有表的分区必须使用相同的存储引擎
- 分区函数中可以使用的函数和表达式有所限制
- 有些引擎不支持分区
- 对于MyISAM的分区表，不能使用load index into cache
- 对于MyISAM表，使用分区表时需要打开更多的文件描述符
8.2 分库分表
通过一些hash算法或者工具实现将一张数据表垂直或者水平进行物理切分
8.2.1 适用场景：
- 单表记录条数达到百万或者千万级别
- 解决表锁的问题
8.2.2 缺点：
- 有些策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差
- 对于应用层来说，逻辑算法无疑增加开发成本
8.2.3 水平分割
表很大，分割后可以降低在查询是需要读的数据和索引的页数，同时也降低了所以的层数，提高查询速度
使用场景：
- 表中的数据本身就有独立性，例如表中分别记录某个地区的数据或者不同时期的数据，特别失败有些数据常用，有些不常用
- 需要把数据放在多个介质上
缺点：
- 给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需要UNION操作
- 这种复杂性会超过它所带来的优点，查询时会增加读一个索引层的磁盘次数
8.2.4 垂直分表
把主键和一些列放在一个表中，然后把主键和另外的列放在另一个表中
使用场景：
- 如果一个表中某些列常用，而某些列不常用
- 可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数
缺点：
管理冗余列，查询时都需join操作
8.3.MySQL的复制原理和负载均衡
8.3.1 主从复制
原理：
- 在主库把数据更改记录到二进制日志
- 从库将主库的日志复制到自己的中继日志
- 从库读取中继日志中的事件中国，将其着重放到从库数据中
解决问题：
- 数据分布：随意停止或开始复制，并在不同地理位置分布数据备份
- 负载均衡：降低单个服务器压力
- 高可用和故障切换：帮助应用程序避免单点失败
- 升级测试：可以使用高版本的mysql作为从库
9.MySQL安全性问题
- 使用预处理语句防sql注入
- 写入数据库的数据需要进行特殊字符的转义
- 查询错误信息不要返回给用户，将错误记录到错误日志
注意：
PHP端尽量使用PDO对数据库进行相关操作，PDO拥有对预处理语句很好的支持的方法，mysqli也有，但是扩展性不如PDO，效率略高于PDO，mysqli函数在新版本中已经趋于淘汰，所以不建议使用，而且没有很好的支持预处理的方法
其他安全设置：
- 定期做数据备份
- 不给查询用户root权限，合理分配权限
- 关闭远程访问数据库权限
- 修改root口令，不使用默认口令，使用较复杂的口令
- 删除多余用户
- 改变root用户名称
- 限制一般用户浏览其他库
- 限制用户对数据文件的访问权限
还不快抢沙发