一. MySQL数据类型
-
整数
TINYINT SMALLINT MEDIUMINT INT BIGINT
属性 UNSIGINED
长度：可以为整数类型指定宽度，例如，INT(11),对大多数应用是没有意义的，它不会限制值的合法范围，只会影响显示 -
实数类型
FLOAT DOUBLE DECIMAL
DECIMAL可存储比BIGINT还大的整数，可以用于存储精确的小数
FLOAT和DOUBLE类型支持使用标准的浮点进行近似计算 -
字符串类型
VARCHAR CHAR TEXT BLOB
VARCHAR类型用于存储可变长字符串，他比定长类型更节省空间
VARCHAR类型使用1或2个额外字节记录字符串长度，列长度小于255字节，使用1个字节表示，否则用2个
VARCHAR长度，如果存储内容超出指定长度，会被截断
CHAR是定长的，根据定义的字符串长度分配足够的空间
CHAR会根据需要采用空格进行填充以方便比较
CHAR适合存储很短的字符串，或者所有的值都接近同一个长度
CHAR长度，超出设定的长度，都会被截断
对于经常变更的数据，CHAR比VARCHAR更好，CHAR不容易产生碎片
对于非常短的列，CHAR比VARCHAR在存储空间上更有效率
只分配正在需要的空间，更长的列会消耗更多的内存
尽量避免使用BLOB/TEXT类型，查询会使用临时表，导致严重的性能开销。
-
枚举
有时可以使用枚举代替常用的字符串类型
把不重复的集合存储成一个预定义的集合
非常紧凑，把列表值压缩到一个或两个字节
内部存储的是整数
避免使用数字作为ENUM枚举的常量，易混淆
排序是按照内部存储的整数进行排序
枚举表会是表的大小大大减小 -
日期和时间类型
尽量使用TIMESTAMP,比DATETIME空间效率高，用整数保存时间戳的格式通常不方便处理
如果需要存储微秒，可以使用bigint存储，成多少倍 -
列属性
auto_increment default not null zerofill
真题
请写出下面MySQL数据类型表达的意义（int(0)、char(16)、varchar(16)、datetime、text）
int(0) 整形，宽度是0
char(16) 定长字符串，长度16
varchar(16) 非定长字符串，长度16
datetime 日期时间
text 大文本
二. MySQL基础操作
- MySQL连接 关闭
mysql -u -p -h -P
\G格式化 \c取消当前命令 \q退出 \s服务器信息 \h帮助信息 \d改变执行符
三. MySQL存储引擎
-
InnoDB表引擎
默认事务型引擎，最重要最广泛的存储引擎，性能非常优秀
数据存储在共享表空间，可以通过配置分开
对主键查询的性能高于其他类型的存储引擎
内部做了很多优化，从磁盘读取数据时自动在内存构建hash索引，插入数据时自动构建插入缓冲区
通过一些机制和工具支持真正的热备份
支持崩溃后的安全恢复
支持行级锁
支持外键 -
MyISAM表引擎
5.1版本前， MyISAM表引擎是默认的存储引擎
拥有全文索引、压缩、空间函数
不支持事务和行级锁，不支持崩溃后的安全恢复
表存储在两个文件，MYD和MYI
设计简单，某些场景下性能很好 -
其他表引擎
Archive Blackhole CSV Memory
优先选择InnoDB
四. MySQL锁机制
- 基础概念
表锁是日常开发中常见的问题，当多个查询同一时刻进行数据修改时，就会产生并发控制问题
共享锁和排它锁，其实就是读锁和写锁
读锁
共享的，不堵塞，多个用户可以同时读取一个资源，互不干扰
写锁
排他锁，一个写锁会阻塞其他的写锁和读锁，这样可以只允许一个人进行写入，防止其他用户读取正在写入的资源
锁粒度
表锁，系统性能开销最小，会锁定整张表，MyISAM使用表锁
行锁，最大程度的支持并发处理，但也带来做大的锁开销
InnoDB实现行级锁
五. MySQL事务处理、存储过程、触发器
事务处理
MySQL提供事务处理的表引擎， InnoDB
服务器层不管事务，由下层的引擎实现，所以同一个事务中，使用多种存储引擎不靠谱
在非事务的表上执行事务操作MySQL不会发出提醒，也不会报错
存储过程
为以后的使用而保存一条或多条MySQL语句的集合
存储过程就是有业务逻辑和流程的集合
可以在存储过程中创建表，更新数据，删除等等
使用场景
通过把处理封装在容易使用的单元中，简化复杂的操作
保证数据的一致性
简化对变动的管理
触发器
提供给程序员和数据分析员来保证数据完整性的一种方法，他是与表事件相关的特殊的存储过程
使用场景
通过数据库中的相关表实现级联更改
实时监控某张表中的某个字段的更改而需要做出相应的处理
某些业务编号的生成
滥用会造成数据库和应用程序维护困难
六. MySQL索引
-
MySQL索引的基础和类型
索引基础
索引类似于书的目录，想找到一本书的某个特定主题，需先查看书的目录，定位对应的页码
存储引擎使用类似的方式进行数据查询，先去索引中找对应的值，然后根据匹配的索引找到对应的数据行 -
索引对性能的影响
大大减小服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机的I/O变顺序I/O
大大提高查询速度，降低写速度，占用磁盘 -
索引的使用场景
对于非常小的表，大部分情况下全表扫描效率更高
中大型表，索引非常有效
特大型的表，建立和使用索引的代价将随之增加，可以使用分区技术来解决 -
索引的类型
索引有很多类型，都是实现在存储引擎层的
普通索引：最基本的索引，没有任何约束限制
唯一索引：与普通索引类似，但是具有唯一性约束
主键索引：特殊的唯一索引，不允许有空值
组合索引：将多个列组合在一起创建索引，可以覆盖多个列
外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作
全文索引：MySQL自带的全文索引只能用于MyISAM,并且只能对英文就行全文检索 -
主键索引和唯一索引的区别？
一个表只能有一个主键索引，可以有多个唯一索引
主键索引一定是唯一索引，唯一索引不是主键索引
主键可以与外键构成参照完整性约束，防止数据不一致 -
MySQL索引的创建原则
（1）最合适索引的列是出现在where子句中的列，或连接子句中的列而不是出现在SELECT关键字后的列
（2）索引列的基数越大，索引的效果越好
（3）对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间
（4）根据情况创建复合索引，复合索引会提高查询效率
（5）避免创建过多的索引，索引会占用额外的磁盘空间，降低写操作效率
（6）主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用，提高查询效率 -
索引的注意事项
（1）复合索引遵循前缀原则
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
where a=1 and c=3
(2)like查询，%不能在前，可使用全文索引
where name like “%wang%” 索引会失效
（3）column is null可以使用索引
（4）如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引
where id >1 and id<100
(5)如果or前的条件中的列有索引，后面的没有，索引就不会被用到
（6）列类型是字符串，查询时一定要给值加引号，否则索引失效
七. MySQL的SQL语句
-
关联更新
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 … -
6种关联查询
交叉连接（CROSS JOIN）
SELECT * FROM A,B,© OR
SELECT * FORM A CROSS JOIN B (CROSS JOIN C)
没有任何关联的条件，结果为笛卡尔积，结果集会很大，没有意义，很少使用
内连接（INNER JOIN）
SELECT * FROM A,B WHERE A.id = B.id or
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
外连接（LEFT JOIN /RIGHT JOIN）
左外连接：LEFT OUTER JOIN,以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
右外连接：RIGHT OUTER JOIN,以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN
联合查询（UNION UNION ALL）
SELECT * FROM A UNION SELECT * FROM B UNION …
就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
如果使用UNION ALL，不会合并重复的记录行
全连接（FULL JOIN）
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)
真题
为了记录足球比赛的结果，设计表如下
team:参赛队伍表
| 字段名称 | 类型 | 描述 |
|---|---|---|
| teamID | int | 主键 |
| teamName | varchar(20) | 队伍名称 |
match：赛程表
| 字段名称 | 类型 | 描述 |
|---|---|---|
| matchID | int | 主键 |
| hostTeamID | int | 主队ID |
| guestTeamID | int | 客队ID |
| matchResult | varchar(20) | 比赛结果 |
| matchTime | Date | 比赛时间 |
其中 match赛程表中的hostTeamID和guestTeamID都和team表中的teamID关联，查询2006-6-1到2006-7-1之间举行的所有比赛，并且用一下形式列出：拜仁 2：0 不莱梅 2006-6-21
select T1.teamName,M.matchResult,M.guestTeamID,M.matchTime from match as M LEFT JOIN team AS T1 ON M.hostTeamID = T1.teamID, LEFT JOIN team as T2 ON M.guestTeamID = T2.teamID BETWEEN "2006-6-1" AND "2006-7-1";
八. MySQL的SQL查询优化
- 查找分析查询速度慢的原因
方法一：记录慢查询日志
分析查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间，可以使用pt-query-digest工具进行分析
方法二：使用show profile
set profiling = 1;开启，服务器上执行的所有语句会检测消耗的时间，存到临时表中
show profiles
show profiles for query 临时表ID
使用show status
show status会返回一些计数器，show global status查看服务器级别的所有计数
有时根据这些计数，可以猜测出哪些操作代价较高或者消耗时间多
使用show processlist
观察是否有大量线程处于不正常状态或者特征
使用explian（别名desc）
分析单条sql语句
-
优化查询过程中的数据访问
访问数据太多导致查询性能下降
确定应用程序是否在检索大量超过需要的数据，可能太多行或者列
确认MySQL服务器是否在分析大量不必要的数据行
避免使用如下的SQL语句
（1）查询不需要的记录，使用limit解决
（2）多表关联返回全部列，指定A.id,A.name,B.age
（3）总是取出全部列，SELECT * （4）会让优化器无法完成索引覆盖扫描的优化
（5）重复查询相同的数据，可以缓存数据，下次直接读取缓存
是否在扫描额外的记录
如使用explain来进行分析，如果发现查询需要扫描大量的数据但只返回少数的行，可以通过如下技巧去优化：
使用索引覆盖扫描，把所有用的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果
（6）改变数据库和表结构，修改数据表范式
（7）重写SQL语句，让优化器可以以更优的方式执行查询 -
优化长难的查询语句
(1)一个复杂的查询还是多个简单的查询好？
MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢的多
使用尽可能少的查询是好的，但是有时将一个大的查询分解成多个小的查询是很有必要的
(2)切分查询
将一个大的查询分为多个小的相同的查询
一次性删除1000万的数据比一次删除1万条，暂停一会的方案跟家消耗服务器开销
（3）分解关联查询
可以将一条关联语句分解成多条SQL来执行
让缓存效率更高
执行单个查询可以减少锁的竞争
在应用层做关联可以更容易对数据库进行拆分 -
优化特定类型的查询语句
（1）优化count()查询
count()中的会忽略所有的列，直接统计所有列数，因此不要使用count(列名)
MySAM中，没有任何where条件的count()非常快
当有where条件，MyISAM的count统计不一定比其他表引擎快
可以使用explain查询近似值，用近似值替代count()
增加汇总表
使用缓存
（2）优化关联查询
确定ON或者USING子句上的列是否有索引
确保GROUP BY 和 ORDER BY中只要一个表中的列，这样MySQL才有可能使用索引
（3）优化GROUP BY 和DISTINCT
这两种查询均可使用索引来优化，是最优效率的优化方法
关联查询中，使用标识列进行分组的效率会更高
如果不需要ORDER BY，进行排序GROUP BY时使用ORDER NULL,MySQL不会再进行文件排序
WITH ROLLUP超级聚合，可以挪到应用程序处理
（4）优化LIMIT分页
LIMIT偏移量大的时候，查询效率较低
可以记录上次查询的最大ID,下次查询时直接根据该ID来查询
（5）优化UNION查询
UNION ALL效率高于UNION
九. MySQL的高可扩展和高可用
-
分区工作原理
对用户而言，分区表是一个独立的逻辑表，但底层MySQL将其分成了多个物理子表，这对用户来说是透明的，每个分区表都会使用一个独立的表文件
创建表时使用partition by子句定义每个分区存放的数据，执行查询时，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询只需要查询所需数据在的分区即可
分区的主要目的就是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关数据存放在一起，而如果想一次性删除整个分区的数据也很方便 -
适用场景
表非常大，无法全部存在内存，或只在表的最后有热点数据，其他都是历史数据
分区表的数据更易维护，可以对独立的分区进行独立的操作
分区表的数据可以分布在不同的机器上，从而高效使用资源
可以使用分区表来避免某些特殊的瓶颈
可以备份和恢复独立的分区 -
限制缺点
一个表最多只能有1024个分区
5.1版本中，分区表表达式必须是整数，5.5可以使用列分区
分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来
分区表中无法使用外键约束
需要对现有表的结构进行修改
所有分区都必须使用同样的存储引擎
分区函数中使用的函数和表达式会有限制
某些存储引擎不支持分区
对于MyISAM分区表，不能使用load index into cache
对于MyISAM表，使用分区表时需要打开更多的文件描述符 -
分库分表工作原理
通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分 -
适用场景
单表记录条数达到百万到千万级别时
解决表锁的问题 -
分表方式
水平分割
表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低索引的层数，提高查询速度。
使用场景
表中的数据本身就有独立性，如表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用
水平分表缺点
给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需要UNION操作
在许多数据库应用中，这种复杂度会超过他带来的优点，查询时会增加一个索引层的磁盘次数
垂直分表
使用场景
如果一个表中某个列常用，另一些列不常用
可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数
垂直分表缺点
管理冗余列，查询所有数据需要JOIN操作
分表缺点
有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差
- MySQL复制原理及负载均衡
MySQL主从复制工作原理
在主库上把数据更改记录到二进制日志
从库将主库的日志复制到自己的中继日志
从库读取中继日志中的事件，将其重放到从库数据中
MySQL主从复制解决的问题
数据分布：随意停止或者开始复制，并在不同地理位置分布数据备份
负载均衡：降低单个数据器压力
高可用和故障切换：帮助应用程序避免单点失败
升级测试：可以使用更高版本的MySQL作为从库
真题
设定网站的用户数量在千万级，但活跃用户数量只有1%，如何通过优化数据库提高活跃用户的访问速度？
- 分区。定制策略，将活跃用户分到一个分区，非活跃用户分到另一个分区。
- 分库分表。水平分表，将活跃用户分到一张表，非活跃用户分到另一张表。
十. MySQL的安全性
- 使用预处理语句防SQL注入
- 写入数据库中的数据要进行特殊字符的转义
- 查询错误信息不要返回给用户，将错误记录到日志
注意：PHP端尽量使用PDO来对数据库进行啊哦做，PDO拥有对预处理语句很好的支持的方法。 - 定期做数据备份
- 不给查询用户root权限，合理分配权限
- 关闭远程访问数据库权限
- 修改root口令，不用默认口令，使用复杂口令
- 删除多余用户
- 改变root用户名称
- 限制一般用户浏览其他库
- 限制用户对数据文件的访问权限
真题
为什么使用PDO连接数据库会比mysql函数库更加安全
因为PDO支持预处理，可以防止sql注入
500
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
