字符类型:
-
tinyint(4):占1个字节,4代表字段值长度,用0填充,搭配zero fill使用
有符号:取值范围 负128 ~ 正127;
无符号:取值范围 0 ~ 255;
默认无符号; -
int(11):占4个字节,11代表字段值长度,用0填充,搭配zero fill才起作用
有符号:取值范围 负2开头10位数 ~ 正2开头10位数;
无符号:取值范围 0 ~ 正4开头10位数,使用unsigned设置;
默认无符号; -
bigint(20):占8个字节,20代表字段值长度,用0填充,搭配zero fill才起作用
有符号:取值范围 负9开头19位数 ~ 正9开头19位数;
无符号:取值范围 0 ~ 正18 开头20位数,使用unsigned设置;
默认无符号;
注1:在32位操作系统中,可能会出现内存溢出问题;
注2:手机号是字符串,不是数字,所以不建议使用bigint; -
char(n):定长字符串,英文字符占n个字节,中文utf8格式,占用n*3个字节;n表示字符长度,中英文相同;
注:字段全部是国内手机号时,建议使用char(11); -
varchr(n):可变长字符串,有效字节数65532;1~ 3个字节存储数据大小
utf8格式:n最大值 = 65532/3;
gbk格式:n最大值 = 65532/2;
注:当一个字段用来存储:座机和手机号时,建议使用; -
其余字符类型:
datetime:时间格式
text:文本,最多可存储64 kb
decimal:金额
mysql函数
max:最大值
min:最小值
count:行数
sum:总和
avg:平均值
ifnull:是否为空
if:控制语句
case:搜索语句
如何设计数据库?
1:三范式:
原子性:列、字段的不可拆分
唯一性:设置主键
冗余性:设置字段与主键的直接关联关系
2:表的拆分:根据业务场景进行逻辑拆分(一对一、一对多、多对多)
3:选择数据库引擎 innodb 或 myisam
4:添加索引(注:单表索引最好不超过5个)
5:单表字段最好不超过20个
6:合适的字段类型(使用小类型替代大类型,比如tinyint 代替int,或int代替bigint)
7:逻辑删除代替物理删除
8:字段添加注释
一张表最多能创建多少个字段?
innodb引擎:一张表最多可创建1000个字段
如果创建一个没有主键的表会怎样?
可以创建成功,但mysql会默认创建一个隐藏的聚簇索引,后续再创建主键时,会更改布局,新建一个聚簇索引
myisam和innodb引擎的区别?
innodb引擎:支持事务、支持外键,是聚簇索引,行级锁;
5.5之后的默认引擎,5.7之后支持全文检索
myisam引擎:不支持事务和外键,是非聚簇索引,表级锁;
MySQL常见索引?
主键索引、普通索引、联合索引、唯一索引(数据的唯一,一张表可以有多个唯一索引)、空间索引
如何创建索引?注意事项?
哪些适合创建索引?
1:字段值有唯一性限制时,必须建成唯一索引
2:where子句中的条件字段,创建索引
3:group by和order by的列,创建索引
4:连表时的关联字段,加索引,且在所有表中,字段名和字段类型保持一致
5:使用类型小的列,创建索引;如:能用int就不用bigint
6:字符串字段,使用前缀索引
7:使用最频繁的列,放在联合索引的左侧
8:多个字段都要创建索引时,联合索引优于单值索引
哪些不适合创建索引?
1:where子句中不使用的字段,不建索引
2:数据量小的表,不建索引(300条以内不用加)
3:大量重复数据的列,不建索引(重复度高于10%,不适合建索引)
4:经常更新的表,少建索引
5:不建议用无序的值作为索引;例:哈希,md5,无序长字符串
6:删除不再使用或使用率少的索引
7:不重复定义索引;例:联合索引中的字段,不需要单独再创建索引
主键索引和唯一索引的区别?
1:主键一定包含唯一,但唯一不一定是主键
2:主键不能为空,唯一可以为空
3:一个表只能有一个主键,但可以有多个唯一
4:主键可以做为其他表的外键
注:null会破坏索引结构,可能会导致该列不走索引,唯一索引可以有多个null,因为null是未知,多个未知不代表重复,但null值不被记录在索引上;唯一索引上只能有一个空字符串,不能有多个
主键可以被修改吗?
1:主键值不能修改,只能删除后,重新添加
2:主键字段,可以修改(id是主键改为name是主键),但一般不这么做,一般情况会将ID的主键属性删除,然后给name添加主键
索引优化方案?
前缀索引、覆盖索引、主键自增、not null、防止索引失效
前缀索引:在字段中字符串的前几个字符建立索引,如:邮箱字段中@符号之前的字符创建索引;
注:order by 无法使用前缀索引,进行索引覆盖
覆盖索引:select 查询字段,可以通过where子句中的索引列来实现,既查询字段值在索引的叶子节点中存在,不需要回表就可以返回结果
什么是MySQL回表?
以innodb为例,如果不使用主键索引查询,且查询结果列不在where子句中的索引列中,就会先由子句索引中的二级索引,查询索引树,找到对应的主键ID,再通过主键ID的索引树,去查询所在行数据,但myisam不同,它的主键索引也没保存行数据,所以无论怎样都会回表;回表会导致多次io,消耗更多资源
简答:如果innodb,不使用主键做条件查询,并且查询结果包含了除条件外的其他字段,就无法一次性得到结果,就必须先查到主键, 再使用主键去索引树中,找到对应的全部数据
索引失效原因?
1:模糊查询以%开头
2:where子句中,对索引做运算,函数
3:联合索引要正确使用最左匹配原则
4:where子句中,or前是索引,or后不是索引,也会导致索引失效
5:索引列存在null值,也会导致索引失效
6:字符串类型,不加引号不走索引,加引号才走索引,int类型加不加都走索引
索引最左匹配原则?
以a、b、c三个字段,做联合索引为例:它会生成三个索引,分别是:(a)、(a、b)、(a、b、c)
在联合索引查询时,优先走最左侧索引:where子句中必须以a为起点,直到遇到范围查询(>,<)为止,之前的字段(包括范围查询字段)都会走索引,但范围之后的索引失效
原因:范围查询之前的索引是有序的,而范围之后的索引是无序的
MySQL优化方案:
1:尽量全值匹配(=)
2:最左匹配原则
3:不对索引列做任何操作
4:尽量使用覆盖索引
5:不等于、like要慎用
6:字符串类型索引,要加引号
SQL性能优化
1:不能使用select *,失去了覆盖索引的效果
2:小表驱动大表时使用in,不使用exists
3:批量插入代替循环插入
4:用limit限制返回条数
5:分页查询优化,页码增大时,查询效率降低,可以借助上次查询最大id,做条件过滤
6:缩小数据集的条件放前面,能排除大量数据的条件放前面
7:连表查询尽量不超过3张表
i
in和exists的区别?
exists:是先查前表中所有数据,然后过滤后面的子句,是否有包含
in:是先执行子句,再通过结果去前表中查询结果集
in走不走索引?
in通常是走索引的,但当in后面的数据集,在表中超过30%的匹配时,是不走索引,而是全表扫描
between、in、or的效率对比
between最快,in次之,or最慢
between > in > or
全文检索
字段类型:fulltext(全文索引)
设置全文索引:fulltext(字段名) with parser ngram comment ‘index_type=smartcn’
查询方式:match(字段名) against(‘搜索信息’)
smartcn:分词器插件,提高中文查询准确率
boolean:高级搜索【‘+要查询信息 且不包含信息’in boolean mode】
自定义分词器:用特殊符号,将字段按自己需求切割成自定义 分词