PHP题（mysql 一）

MySQL数据类型

整形：

TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
属性：UNSIGNED
长度：INT（11），不会限制值的合法范围，只会影响显示字符的个数，和zerofill配合使用，如果长度不足则用0填充

实数类型：

FLOAT，DOUBLE， DECIMAL
float和double求SUM的结果都是不精确的，只有decimal求SUM得到的是精准数值：
所以，decimal 类型是适合财务和货币计算的128位数据类型。

字符串类型

VARCHAR，CHAR，TEXT，BLOB
VARCHAR 可变长字符串
CHAR 定长字符串(空格填充)
尽量避免使用BLOB/TEXT类型，查询会使用临时表，导致严重的性能开销。

日期和时间类型

timestamp币datetime空间效率高
用整数存储时间戳的格式通常不方便处理
如果需要存储微秒，可以使用bigint存储

MySQL数据表引擎

Innodb

数据存储在共享表空间，可通过配置独立表空间
主键查询性能高于其他类型引擎
内部做了很多优化，从磁盘读取数据时自动在内存构建hash索引，插入数据时自动构建插入缓冲区
热备份
行级锁
外键
事务
新版的MySQL5.6.24上InnoDB引擎也加入了全文索引

MyISAM

支持表锁
全文索引
表存储在MYD和MYI文件

1. 请写出下面MySQL数据类型表达的意义 int(0), char(16), varchar(16), datetime, text

MySQL锁机制

类型

共享锁和排他锁 （读锁和写锁）
共享锁：多个用户读一个资源，互不干扰
排他锁：阻塞其他的写锁和读锁，只允许一个用户写入，防止其他用户读取正在写入的数据

锁粒度

表锁

性能开销最小，锁表，MyISAM使用表锁，并发效率低

行锁

注意：
同一个事务中不应该使用多种引擎

MySQL 创建高性能的索引

类型

普通索引： 最基本的索引，没有任何约束限制
唯一索引：索引列的所有值都只能出现一次，即必须唯一
主键索引：不允许有空值
组合索引：多个列组合在一起创建索引，可以覆盖多个列,联合索引where查询需要按照建立索引的顺序

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

1. 唯一性约束所在的列允许空值，但是主键约束所在的列不允许空值。
2. 可以把唯一性约束放在一个或者多个列上，这些列或列的组合必须有唯一的。
3. 主键可以和外键构成完整性约束，防止数据不一致

创建原则

1. 在Where, Order By，Join的列
2. 索引列的基数越大，效果越好
3. 对字符串索引，应该制定一个索引长度，可以节省大量索引空间
4. 根据情况创建复合索引，复合索引可以提高查询效率
5. 过多的索引会降低写操作效率

如何确定当前字段设置了合适的索引长度呢？

select count(distinct left(password, 5))/count(*) from user

联合索引的匹配原则：

1. 不包含最左侧列，不使用索引
2. OR不使用索引
3. 最左侧列被大于，小于，不等于比较的，不使用索引

SQL编写

1. 关联更新

UPDATE A,B SETA.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

2. 联合查询

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

多个结果集集合在一起，联合查询的列数要相等，相同的记录行会合并，
UNION ALL 不会合并重复的记录行

MySQL查询优化

请简单叙述项目中优化SQL语句执行效率的方法，从哪些方面看，SQL语句性能如何分析.

1. 开启慢查询日志,使用pt-query-digest进行分析
2. 使用show profiles

set profiling=1; 开启，服务器上执行的所有语句会检测消耗的时间，存入临时表
show profiles; 查看所有查询及其消耗的时间
show profile for quiery 临时表id； 查看查询过程中，各个环节消耗的时间

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

4. show processlist
   观察是否有大量线程处于不正常的状态或者特征

5. explain

MySQL高可扩展和高可用考点

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

分区表

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

工作原理

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

适用场景

1. 表非常大，或者只在表的最后有热点数据，其他都是历史数据
2. 分区表的数据更容易维护，可以对独立的分区进行独立的操作
3. 分区表的数据可以分布在不同的机器上，从而高效使用资源

例子

range 分区：

创建表

create table if not exists `user` (  
    `id` int(11) not null auto_increment comment '用户id',  
    `name` varchar(50) not null default '' comment '名称',  
    `sex` int(1) not null default '0' comment '0为男，1为女',  
    primary key (`id`)  
) engine=innodb default charset=utf8 auto_increment=1  

分区前物理结构

-rw-r----- 1 polkitd input   8645 Sep  2 19:03 user.frm
-rw-r----- 1 polkitd input  98304 Sep  2 19:03 user.ibd

分区

# 会创建一个分区表，然后自动进行数据copy然后删除原表。
alter user partition by range (id) (  
    partition p0 values less than (3),  
    partition p1 values less than (6),  
    partition p2 values less than (9),  
    partition p3 values less than (12),  
    partition p4 values less than maxvalue  
); 

分区后物理结构

-rw-r----- 1 polkitd input   8645 Sep  2 19:13 user.frm
-rw-r----- 1 polkitd input  98304 Sep  2 19:13 user#P#p0.ibd
-rw-r----- 1 polkitd input  98304 Sep  2 19:13 user#P#p1.ibd
-rw-r----- 1 polkitd input  98304 Sep  2 19:13 user#P#p2.ibd
-rw-r----- 1 polkitd input  98304 Sep  2 19:13 user#P#p3.ibd
-rw-r----- 1 polkitd input  98304 Sep  2 19:13 user#P#p4.ibd

查询分区信息

select * from information_schema.partitions where table_schema='liying_order' and table_name='user';

插入数据

insert into `user` (`name` ,`sex`) values 
('tank', '0') ,('zhang',1),('ying',1),('张',1),
('映',0),('test1',1),('tank2',1),('tank1',1),
('test2',1),('test3',1),('test4',1),('test5',1),
('tank3',1),('tank4',1),('tank5',1),('tank6',1),
('tank7',1),('tank8',1),('tank9',1),('tank10',1),
('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1); 

查询总数

select count(id) as count from user;
+-------+
| count |
+-------+
|    25 |
+-------+
1 row in set (0.00 sec)

删除分区四

alter table user drop partition p4;

再次查询

select count(id) as count from user;
+-------+
| count |
+-------+
|    11 |
+-------+
1 row in set (0.00 sec)

查看分区四的物理文件
被删除

-rw-r----- 1 polkitd input   8645 Sep  2 19:23 user.frm
-rw-r----- 1 polkitd input  98304 Sep  2 19:20 user#P#p0.ibd
-rw-r----- 1 polkitd input  98304 Sep  2 19:20 user#P#p1.ibd
-rw-r----- 1 polkitd input  98304 Sep  2 19:20 user#P#p2.ibd
-rw-r----- 1 polkitd input  98304 Sep  2 19:20 user#P#p3.ibd

新增分区四

alter table user add partition(partition p4 values less than maxvalue);  

list 分区

create table if not exists `list_part` (  
    `id` int(11) not null auto_increment comment '用户id',  
    `province_id` int(2) not null default 0 comment '省',  
    `name` varchar(50) not null default '' comment '名称',  
    `sex` int(1) not null default '0' comment '0为男，1为女',  
    primary key (`id`)  
) engine=innodb  default charset=utf8 auto_increment=1  

partition by list (province_id) (  
      partition p0 values in (1,2,3,4,5,6,7,8),  
      partition p1 values in (9,10,11,12,16,21),  
      partition p2 values in (13,14,15,19),  
      partition p3 values in (17,18,20,22,23,24)  
);  
创建list分区时，如果有主銉的话，分区时主键必须在其中，不然就会报错。

hash分区

hash分区主要用来确保数据在预先确定数目的分区中平均分布，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

create table if not exists `hash_part` (  
    `id` int(11) not null auto_increment comment '评论id',  
    `comment` varchar(1000) not null default '' comment '评论',  
    `ip` varchar(25) not null default '' comment '来源ip',  
    primary key (`id`)  
) engine=innodb  default charset=utf8 auto_increment=1

partition by hash(id)  
partitions 3; 

key分区

按照key进行分区类似于按照hash分区，除了hash分区使用的用 户定义的表达式，而key分区的 哈希函数是由mysql 服务器提供。