PHP开发基础知识总结03_yii2并发控制-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_41360604/article/details/101801061

MySQL数据库基础知识

MySQL数据类型

整数类型

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT

属性：UNSIGNED(非负)
长度：可以为整数类型指定宽度。(对大多数应用是没有意义的，他不会限制值的合法范围，只会影响显示字符的个数)

实数类型

FLAOT,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基础操作

常见操作：

MySQL的链接和关闭：mysql -u -p -h -P
其他：\G, \c, \q, \s, \h, \d

MySQL数据表引擎

InnoDB表引擎

默认事务性引擎，最重要最广泛的存储引擎，性能非常优秀
数据存储在共享表空间，可以通过配置打开
对主键查询的性能高于其他类型的存储引擎
内部做了很多优化，从磁盘读取数据时自动在内存构建hash所以，插入数据时自动构建缓冲区
可以通过一些机制和工具支持真正的热备份
支持崩溃后的安全恢复
支持行级锁
支持外键

MyISAM表引擎

5.1版本前，MyISAM是默认的存储引擎
拥有全文索引、压缩、空间函数
不支持食物和行级锁，不错支持崩溃后的安全恢复
表存储在俩个文件，MYD和MYI
设计简单，某些场景下性能很好

俩种表的区别：
1.InnoDB支持行锁，MyISAM支持表锁
2.InnoDB支持事务处理，MyISAM不支持
3.InnoDB数据存储在共享表空间，MyISAM数据保存在.MYD文件中，索引保存在.MYI文件中

其他表引擎

Archive,Blackhole,CSV,Memory

优先选择InnoDB

MySQL锁机制

当多个查询同一时刻进行数据修改时，就会产生并发控制的问题。

共享锁和排它锁(读锁和写锁)

读锁

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

写锁

排他的，一个写锁会阻塞其他的写锁和读锁，这样可以只允许一个人就行写入，防止其他用户读取正在写入的资源。

锁粒度

表锁，系统性能开销最小，会锁定整张表，MyISAM使用表锁
行锁，最大程度地支持并发处理，但是也带来了最大锁开销，InnoDB使用行级锁

事务处理

MySQL提供食物处理的表引擎，InnoDB
服务器层不管理事物，由下层的引擎实现，锁同一个事物中，不可以使用多种存储引擎。
在非事务的表上执行实务操作MySQL不会发出提醒，也不会报错

存储过程

为以后的使用而保存的一条或多条MySQL语句的集合
存储过程就是有业务逻辑和流程的集合
可以在存储过程中创建表，更新数据，删除等等

使用场景：
通过把处理封装在容易使用的单元中，简化复杂的操作
保证数据的一致性
简化对变动的管理

MySQL的触发器

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

使用场景：
可通过数据库中相关实现级联更改
实现监控某张表中的某个字段的更改而需要作出相应的处理
某些业务编号的生成等
滥用会造成数据库及应用程序的维护困难

MySQL创建高性能索引

存储引擎先去索引中找到对应的值，再根据匹配的索引找到对应的数据行。

索引对性能的影响：

大大减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机I/O变顺序I/O
大大提高查询速度，但是会降低写的速度、占用磁盘

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

索引的类型

索引有很多种类型，都是实现存储引擎层的

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

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

一个表只能有一个主键索引，但可以有多个唯一索引
主键索引一定是唯一索引，唯一索引不一定是主键索引
主键可以与外键构成参照完整性约束，防止数据不一致

MySQL索引的创建原则

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

MySQL索引的注意事项

1.符合索引遵循前缀原则
2.like查询，%不能在前，可以使用全文索引
3.column is null 可以使用索引
4.如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引
5.如果or前的条件中的列有索引，后面的没有，索引都不会被用到
6.列类型是字符串，查询时一定要给值加引号，否则索引失效

MySQL的SQL语句

MySQL的关联查询语句

六种关联查询：

1.交叉连接CROSS JOIN：

SELECT * FROM A,B(,C)或者
SELECT * FROM A CROSS JOIN B (CROSS JOIN C)

没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用

2.内连接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

3.外连接LEFT JOIN/RIGHT JOIN：

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

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

4.联合查询UNION与UNION ALL：

SELECT * FROM A UNION SELECT * FROM B UNION...

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

如果使用UNION ALL,不会合并重复的记录行

5.全连接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

6.嵌套查询

用一条SQL语句的结果作为另外一条SQL语句的条件
SELECT * FROM A WHERE id IN (SELECT id FROM B)

MySQL查询优化

查找分析查询速度的原因

1.记录慢查询日志

使用pt-query-digest工具进行分析

2.使用show profile

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

3.使用show status

show status 会返回一些计时器，show global status 查看服务器级别的所有计数

4.使用show processlist

5.使用explain

分析单条SQL语句

6.优化查询中的数据访问

*访问数据过多会导致查询性能下降
确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
确认MySQL服务器是否在分析大量不需要的数据行

避免使用如下SQL语句：
查询不需要的记录，使用limit解决
多表关联返回全部列，指定A.id，A.name，B.age
总是取出全部列，SELECT *会让优化器无法完成索引覆盖扫描的优化
重复查询相同的数据，可以缓存数据，下次直接读取缓存

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

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

重写SQL语句，让优化器可以以更优的方式执行查询

7.优化长难的查询语句

一个复杂查询还是多个简单查询：
使用尽可能少的查询时好的，但是有时将一个大的查询分解为多个小的查询也是很有必要的

①切分查询：
将一个大的查询分为多个小的相同的查询

②分解关联查询：
可以将一条关联语句分解成多条SQL来执行
让缓存的效率更高
执行多个查询可以减少锁的竞争
在应用层做关联可以更容易对数据库进行拆分
查询效率会有大幅提升

8.优化特定类型的查询语句

①优化count()查询：

count(*)会忽略所有的列，直接统计所有列数，因此不要使用count(列名)
MyISAM中，没有任何WHERE条件的count(*)非常快
当有WHERE条件是，MyISAM的count统计不一定比其他表引擎快

使用explain查询近似值，用近似值替代count(*)
增加汇总表
使用缓存

②优化关联查询：

确定ON或者USING子句的列上有索引
确保GROUP BY和ORDER BY中只有一个表中的列，这样MySQL才有可能使用索引

③优化子查询

尽可能使用关联查询替代

④优化GROUP BY 和DISTINCT

这俩种查询均可使用索引来优化，最有效的优化方法
关联查询中，使用标识列进行分组非效率会更高
如果不需要ORDER BY ，进行GROUP BY 时使用ORDER BY NULL，MySQL不会再进行文件排序
WITH ROLLUP 超级聚合，可以挪到应用程序处理

⑤优化LIMIT分页

LIMIT偏移量大的时候，查询效率较低
可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

⑥优化UNION查询

UNION ALL的效率高于UNION

MySQL的高可扩展和高可用

分区表的原理

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

创建表时使用partition by子句定义每个分区存放的数据，执行查询时，优化会根据分区定义过滤那些没有我们需要数据的分区，资源查询只需要查询所需数据在的分区即可

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

适用场景

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

限制

1.一个表最多只能有1024个分区
2.5.1版本中，分区表表达式必须是整数，5.5可以使用列分区
3.分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来
4.分区表中无法使用外键约束
5.需要对现有表的结构进行修改
6.所有分区都必须使用相同的存储引擎
7.分区函数中可以使用的函数和表达式会有一些限制
8.某些存储引擎不支持分区
9.碎玉MyISAM的分区表，不能使用load index into cache
10.对于MyISAM表，使用分区表时需要打开更多的文件描述符

分表的工作原理

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

适用场景

1.单表记录条数达到百万甚至千万级别时
2.解决表锁的问题

分表方式

1.水平分割

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

应用场景：

①表中的数据本身就有独立性
②需要把数据存放在多个介质上

缺点：

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

2.垂直分表

把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中

应用场景：

①如果一个表中某些列常用，而另外一些列不常用
②可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数

缺点：

管理冗余列，查询所以数据需要JOIN操作

分表缺点：

有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差。
对于应用层来说，逻辑算法无疑增加开发成本。

MySQL的复制原理和负载均衡

MySQL主从复制工作原理

1.从主库上把数据更改记录到二进制日志中
2.从库将主库中的日志复制到自己的中继日志
3.从库读取中继日志中的事件，将其重新放到从库数据中

中从复制解决的问题

1.数据分布：随意停止或开始复制，并在不同地理位置分布数据备份
2.负载均衡：降低单个服务器的压力
3.高可用和故障切换：帮助程序避免单点失败
4.升级测试：可以使用更高版本的MySQL作为从库

MySQL的安全性

SQL查询的安全方案

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

PHP端尽量使用PDO对数据库进行相关操作，PDO拥有对预处理语句很好的支持的方法，MySQLi也有，但是可扩展性不如PDO，效率略高于PDO，MySQL函数在新版本中已经趋向于淘汰，所以不建议使用，而已它没有很好的支持预处理的方法

MySQL的其他安全设置

1.定期做数据备份
2.不给查询用户root权限，合理分配权限
3.关闭远程访问数据库权限
4.修改root口令，不用默认口令，使用较为复杂的口令
5.删除多余的用户
6.改变root用户名称
7.限制一般用户浏览其他库
8.限制用户对数据文件的访问权限

程序功能设计

PHP链接数据库的方式

PDO：可扩展性更好、支持预处理、面向对象
MySQLi：只支持MySQL操作、支持预处理、面向对象和过程、效率较高
msyql：只支持MySQL数据库、没有预处理、面向过程

PDO的基础操作：

<?php
try{
	//操作数据库代码
}catch(PDOException $e){
	echo $e->getMessage();
}

//操作数据库代码
$pado = new PDO($dsn,$username,$password,$attr);
$sql = 'SELECT id,title,content FROM message WHERE user_name=:username';
$stmt = $pdo->prepare($sql);
$stmt->execute([':username' => $user_name ]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>