数据类型

整形：

bit：

储存0,1值，可以使用true，false存入数据库是自动转换成0,1

tinyint：一个字节

smallint:两个字节

mediumint：三个字节

int：四个字节

bigint：八个字节

所有的int类型都支持unsigned
int(4):其中4代表的时显示长度，并自动进行zerofill

浮点型（近似值）

float：单精准，八位精度，4字节

float（6,3）：6代表共六位，3代表小数位

double：双精准，16位精度，8字节

定点数（精确值）

decimal：decimail(M,D)占M+2个字节

字符型：

char：定长，默认长度255，尾部用空格填充，超出定义的长度会被截断，空间效率更高，不容易产生空间碎片，存储时末尾不能出现空格，查询的时候开头和结尾的空格会被删掉，储存过程中不进行大小写的转换，索引效率高。

varchar：变长，可变长度省空间，会有1或2个字节储存长度，一位记录是否为null，小于255用一个字节，大于255两个字节，最长65535，暂时未验证，超出的部分会被截断，超出部分会强转场text

blob/text：尽量不要使用，查询时会使用临时表，开销大，储存或检索时不转换大小写。

枚举：

用来代表常用的字符串类型
不重复的集合存储长一个预定义的集合
列表的值压缩到一个或连个字节
内部存储的时整数
避免使用数字，容易混乱
排序按照内部的整数排序
使表更小

时间类型

timestamp比datatime空间效率更高
储存秒时可以使用，bigint

列属性

auto_increment,default,notnull,zerofill

常用的命令

mysql -u(用户名） -p(密码) -h(主机） -P(端口)
\G:垂直显示
\c:取消当前命令
\q:退出
\s:服务器状态
\h:帮助
\d:改变执行符

引擎

InnoDB

事务性
数据存储在共享表空间，一个文件，可以通过配置使用多个文件
行级锁（最大限度的支持并发，系统开销大），可能会锁全表
支持外键 （尽量不用）
可以安全恢复
可以热备份
主键查询性能高于其他引擎
内部优化，读取数据时自动在内存构建hash索引，插入数据时自动构建缓存区

事务：

事务处理有数据库引擎实现，同一个事务中的多个表要使用相同的引擎
在非事务的表上执行事务操作，没有异常，不会报错

事务的隔离级别：

read uncommitted : 会出现脏数据
read commited ：不可重复读（update操作）
repeatable read（默认): 幻读（insert）
serializable

MyISAM

全文检索，仅英文（一般不用）
不支持事务，不支持安全恢复
表级锁（系统开销最小，所整张表）
会保存表的行数
储存两个文件MYD(数据) MYI(索引）

锁

读锁

共享的，不阻塞，可以多个用户同时读

写锁

排他的，一个写锁会阻塞其他的写/读操作，只允许一个人进行操作防止其他用户读取真在写入的资源

储存过程

是为了方便使用，保存一条或者多条mysql命令集合，由业务逻辑和流程组成，可以创建，更新，删除

使用场景

简化复杂的操作
保证数据的一致性
简化对变动的管理

触发器

保证数据的完整性的一种方式，与表事件关联的特殊存储过程

使用场景

可以对数据库中的相关表进行级联更新，实现监控表中的字段并作出相应的处理：例如生成业务编号
滥用会造成数据库和应用程序的维护困难

索引

索引对性能的影响

减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机IO变为顺序IO
提升查询速度
降低写的速度，占用磁盘空间

使用场景

小的表，不用索引效率可能会更高
中，大型表，效果很好
特大型表，索引的代价增大，可以使用分区，分库技术

索引的类型

普通索引（index）：最基本的索引，没有约束限制
唯一索引（unique index)：可以有多个唯一索引，索引的值必须是唯一的，可以是null
主键索引：特殊的唯一索引，不能有null，只能有一个主键索引，可以做外键
组合索引：多个列组合起来做索引，遵守最左原则，可以一个顶多个索引，筛选出的数据更精准
全文索引：一般不用，MYISAM支持，仅支持英文

注意事项

索引不包含null
对字符串进行索引时，只有加引号才能索引到,可以指定一个前缀长度，节省空间，减少IO操作
mysql执行查询是只能执行一个限制最严格的索引，如果在where中使用的索引，order by就不会使用索引
like,以%和_开头的不会使用索引
不要再列上进行运算,会导致全表扫描
如果使用or，or条件中的每一个列都要使用索引
如果表太小，mysql可能会自动进行全表扫面

索引的实现

磁盘的读取数据是一盘块为单位的，位于一个盘块的所有数据可以被一次读取，磁盘的IO代价主要花费在查找时间上，因此应该将相关信息存放到同一块磁盘或者尽量相近

MyISAM对索引的实现（b+tree）

索引文件和数据文件分离，叶子节点保存的是数据记录的地址属于非聚簇索引，使用堆表，写入性能好

InnoDB对索引的实现

数据文件本身就是索引文件 ，表结构本身就是B+tree结构组成的索引结构
在叶子节点上保存着完整的数据记录，所有的索引都引用主键作为data域

创建原则

最适合的索引列是where子句中的列或者连接子句中的列，不是select关键字后的列
索引的基数越大效果越好
对字符串进行索引时，应制定一个前缀长度，可以节省索引空间
避免创建过多的索引，索引会占用空间，降低写的效率
根据情况创建组合索引，提高查询效率
主键尽量选择较短的数据类型，减少空间占用，提高查询效率

关联查询

交叉（cross join）：没有任何条件，结果是笛卡尔积，结果集大，没有意义，很少用
内连接（inner join）：多表中同时符合某种条件的数据记录的集合，等值（=），不等值（><),自连接
左连接（left join）：以左表为主，先查左表，按照on后的条件匹配左表，没有的匹配的内容用null填充
右连接（right join）：以右表为主，先查右表，按照on后的条件匹配右表，没有的匹配的内容用null填充
联合查询（union，union all）：把多个结果集集中在一起，以前面的结果为基准，列数要相等，相同的记录会合并。union all效率高，不会合并重复的行
全连接：mysql没有全连接，left join union right join 代替

查询优化

查找原因

慢查询日志：使用工具
show profile ---> set profiling= 1 :开启，服务器上执行的所有的语句检测消耗时间，存在临时表profiles中
show profiles : 查看命令记录
show profile for query 命令id：查看语句具体消耗的时间
show status : 返回计数器
show global status : 查看服务器级别的计数器，根据计数器找出代价较高的操作
show processlist : 查看是否有大量线程处于不正常的状态或特征
explain : 分析单条sql语句

优化方案

访问数据过多会导致查询效率性能下降
确定是否检索大量超过需求的数据行或列
确定是否在分析大量不必要的行
避免使用sql查询不需要的记录，使用limit解决
多表关联返回全部的列，指定列明解决
总是取出所有的列，*号会让优化器无法完成索引覆盖所描的优化
是否扫描额外的记录，扫描大量的列只返回很少的数据：解决方案1.使用索引，2.改变数据库的表结构修改表范式，3重写sql让优化器可以更优的方式查询

长难句的优化

mysql : 扫描很快，相应慢
尽量使用少的查询，有时候可以将一个大的查询分解为多个小的查询查询
分解关联查询：一条语句分解为多个sql执行

1.让缓存的效率更高
2.执行单个查询可以减少锁的竞争
3.在应用层可以更容易对数据库进行拆分

特殊类型语句

count(*) 直接统计所有的列数

explain ： 全表扫描，用近似值代替
增加汇总表
使用缓存
关联查询
确保on或using子句的列有索引
确保group by和order by只有一个表的列，才能使用索引
子查询：使用关联查询代替
group by / distinct
使用索引
关联查询使用主键列
group by不需要排序时使用 order by null ，不进行排序
with roll up （超聚合）：在应用程序中进行处理
limit
偏移量大的时候，效率低
记录是那个一次查询的id
union all 效率高于union

扩展和高可用

分区原理

对用户而言分区表是一个独立的逻辑表，但是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.许多数据库应用中，这种复杂性会超过他带来的好处，查询时会增加读一个索引层的磁盘次数

垂直分表

把主键和一些列放在一张表中，把主键和另一些列放在另一张表中

垂直分表的使用场景

1. 一个表中某些列常用，另外一些列不常用
   2.使用数据行变小，一个数据表能存储更多数据,查询时减少IO

垂直分表的缺点

管理冗余列，查询所有数据需要join操作

分库分表的缺点

1. 有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表的逻辑都要改变
   2.对于应用层来说，增加开发成本

复制原理和负载均衡

主从复制的原理

在主库上把数据记录二进制文件
从库将主库的日志复制到总计的中断日志
从库读取中断日志中的事件，将其中放进数据库中

主从赋值解决的问题

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

安全性

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

提高安全性

1.定期做数据备份
2.不给查询用户root权限，合理分配权限

3. 关闭远程连接权限
   4.修改root密码
   5.删除多余的用户
   6.改变root的名称
   7.限制一般用户浏览其他库
   8.限制用户对数据文件的访问权限