mysql 理论

'数据库三大范式'

第一范式：如果数据库表中的所有字段值都是不可分解的原子值

第二范式:数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式:数据表中的每一列数据都和主键直接相关，而不能间接相关。

'mysql 有关权限的表有哪几个'

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

db权限表：记录各个帐号在各个数据库上的操作权限。

table_priv权限表：记录数据表级的操作权限。

columns_priv权限表：记录数据列级的操作权限。

host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

'mysql的binlog有几种录入格式，分别有什么区别'
MySQL binlog日志有三种格式，分别为:
Statement           MiXED             ROW.

'Statement'
1.Statement：每一条会修改数据的sql都会记录在binlog中。

优点:

binlog文件较小

日志是包含用户执行的原始SQL,方便统计和审计

出现最早，兼容较好

缺点：

存在安全隐患，可能导致主从不一致

对一些系统函数不能准确复制或是不能复制

'ROW'

2.ROW不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点:

相比statement更加安全的复制格式

在某些情况下复制速度更快（SQL复杂，表有主键）

系统的特殊函数也可以复制

更少的锁

在复制时，对于更新和删除语句检查是否有主键，如果有则直接执行，如果没有，看是否有二级索引，如再没有，则全表扫描

缺点：

binlog比较大（myql5.6支持binlog_row_image）

单语句更新（删除）表的行数过多，会形成大量binlog

无法从binlog看见用户执行SQL（5.6中增加binlog_row_query_log_events记录用户的query）

'Mixed'

3.Mixed: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

优点:

混合使用row和statement格式，对于DDL记录statument,对于table里的行操作记录为row格式。

如果使用innodb表，事务级别使用了READ_COMMITTED or READ_UMCOMMITTED日志级别只能使用row格式。

但是使用ROW格式中DDL语句还是会记录成statement格式。

缺点:

mixed模式中，那么在以下几种情况下自动将binlog模式由SBR模式改成RBR模式。

当DML语句更新一个NDB表

当函数中包含UUID时

2个及以上auto_increment字段的表被更新时

行任何insert delayed语句时

用UDF时

视图中必须要求使用RBR时，例如创建视图使用了UUID()函数

'数据类型'
tinyint 很小整数 1字节（[0~255]、[-128~127]）; 255=2^8-1;127=2^7-1

smallint 小整数 2字节（0~65535、-32768~32767） ;65535=2^16-1

mediumint 中等 3字节（0~16777215） ;16777215=2^24-1

int(integer) 普通 4字节（0~4294967295） ;4294967295=2^32-1

bigint 大整数 8字节（0~18446744073709551615）;2^64-1

float单精度浮点数4字节

double双精度浮点数8字节

decimal压缩的“严格”定点数M+2字节

2.时间/日期类型：

year范围：1901~2155;

time格式：‘HH:MM:SS’（如果省略写，并且没有冒号，则默认最右起2位为秒，再到分，最后到时）；

插入系统当前时间：insert into 表名 values(current_date()),(now());

date类型：‘YYYY-MM-DD’；

datetime（日期+时间）：‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’，取值范围：‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’；

timestamp格式同datetime，但在存储时需要4个字节(datetime需要8字节)，并且以UTC（世界标准时间）进行存储（即timestamp会随设置的时区而变化，而datetime存储的绝不会变化）；timestamp的范围：1970-2037。

3.字符串类型： 

text类型：tinytext、text、mediumtext、longtext;

tinytext255=2^8-1

text65535=2^16-1

mediumtext16777215=2^24-1

longtext4294967295=4GB=2^32-1

'存储引擎'

1.MYISAM 和 Innodb之间的区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务； 

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败； 

Innodb & MYISAM索引的区别

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

       MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

       也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

那么为什么InnoDB没有了这个变量呢？

    因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。

    如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS

#5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

       InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

例如：

    t_user(uid, uname, age, sex) innodb;

    uid PK   无其他索引

    update t_user set age=10 where uid=1;             命中索引，行锁。

    update t_user set age=10 where uid != 1;           未命中索引，表锁。

    update t_user set age=10 where name='chackca';    无索引，表锁。

8、InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有

9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

        Innodb：frm是表定义文件，ibd是数据文件

        Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

存储引擎选择：

    1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

    2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

    3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

    4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

'索引'

1.什么是索引

索引(Index)是帮助MySQL高效获取数据的数据结构。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

Mysql索引主要有两种结构：B+Tree索引和Hash索引。

2.索引有哪些优缺点

索引的优点

1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 

2、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 

3、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 

4、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 

5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

索引的缺点

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 

2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 

3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引使用场景

1. 当数据多且字段值有相同的值得时候用普通索引。

2. 当字段多且字段值没有重复的时候用唯一索引。

3. 当有多个字段名都经常被查询的话用复合索引。

4. 普通索引不支持空值，唯一索引支持空值。

5. 但是，若是这张表增删改多而查询较少的话，就不要创建索引了，因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引，

6. 若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集，

7. 若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集，

8. 若是删，则会把索引中以这个字段为名的索引的子集删掉。

9. 所以，会对增删改的执行减缓速度，

10. 所以，若是这张表增删改多而查询较少的话，就不要创建索引了。

11. 更新太频繁地字段不适合创建索引。

12. 不会出现在where条件中的字段不该建立索引。

CREATE [UNIQUE|CLUSTERED] INDEX INDEX_NAME ON TABLE_NAME(PROPERTY_NAME)；

'索引有几种类型'

1.普通索引

2.唯一索引

3.主键索引

4.组合索引

5.全文索引

'索引的数据结构'：

B+ 树 和 HASH

索引的基本原理

索引算法有哪些

B+树索引时最为常见的，也是在数据库中使用最为频繁的一种索引。在介绍该索引之前先介绍与之密切相关的一些算法与数据结构。二分查找法也称折半查找法，其基本思想是：将记录按有序化（递增或递减）排列，在查找过程中采用跳跃式方式查找，即先以有序数列的中点位置为比较对象，如果要找的元素值小于该中点元素，则将带查找序列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。

'索引设计的原则'：

1.被频繁使用的字段设置索引 

字段被频繁用在where 和order 等条件里面

数据表创建完毕，要预估那些字段被经常使用，就给其创建索引

2.执行时间长的sql 语句考虑设计索引

可以利用“慢查询日志”收集这样的sql 语句并优化设计索引

3.逻辑非常重要的sql 设计索引

例如商城系统里面，会员给自己账户充值就比较重要。

还有会员下订单购物，进行付款的时候也比较重要。

4.字段内容足够花样化，可以考虑设计索引

例如性别，不适合做索引