MySQL

一、编码方式

1、gbk编码：是国家标准编码方式， 不论英文和中文都是由双字节来表示，中文最高位为 1，通用性比较差。
2、utf8编码：国际编码，是多字节编码（中文24，英文8），可以包括世界上所有国家用
到的字符，通用性较好。

二、MySQL日志

• 1、错误日志（-log-err）：不仅用于存储Mysql运行时所产生的较为严重的警告或错误信息。还记录了:
  • 服务器启动和关闭过程中的信息;
  • 服务器运行过程中的错误信息;
  • 事件调度器运行一个事件产生的信息;

查询错误日志存储路径

image.png
配置错误日志文件

log-error="error.log"

• 2、查询日志（my.cnf）：记录建立的客户端连接和执行的所有语句（包括错误的);
  查询是否启动查询日志及存储路径
  image.png
  配置查询日志

//日志输出类型
log-output=FILE
general-log=1
general_log_file="filename.log"

• 3、慢查询日志（-log-slow-queries）：（配置文件 my.ini）
  查询路径
  image.png
  配置

log-output=NONE
log_slow_queries=0
slow_query_log_file="filename.log"
//用来记录MySQL中响应时间超过阀值（临界值）的语句
long_query_time=10

执行select sleep（3） SQL语句存入慢查询日志的语句。

• 4、更新日志（-log-update）
• 5、二进制日志（-log-bin）：热备 主从复制
  查询路径
  image.png

三种格式：
Statement 记录每一次修改数据库数据时的sql语句。不用记录修改的内容
Row不记录sql上下文相关信息，仅保存被修改的记录。详细的记录了被修改的信息。
Mixedlevel以上两种的混合。
热备（主从复制）：Master服务器将自己的每一次改动信息存入到Binarylog日志中，从服务器Slave用master帐号登录，并读取主服务器内的Binarylog文件，在从服务器上运行该日志，将数据进行更新转移。（包括三个进程：1从服务器发出请求访问Binarylog日志 2住服务器将日志内容传给从服务器 3从服务器运行日志）

三、分析慢查询(explain)

在Mysql的配置文件中配置一个阈值，所有sql语句执行时间超过这个阈值的将被存储到慢查询日志中，在终端通过explain该条sql语句

mysql> explain select * from users where name = 'dd';
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | name_index    | name_index | 258     | const |    1 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.04 sec)
// 主要关注type、key、rows三个属性
//type代表mysql找到所需行的方式，可以取值为：ALL（全表扫描）、ref（哪些列或常量被用于查找索引列上的值）
// key代表实际使用的索引
// rows代表sql查询读取的行数

具体信息在这里和这里。

四 、索引

索引精讲
分类：

• 1.普通索引：index，普通的
• 2.唯一索引：unique，不允许重复
• 3.主键索引：primary，唯一且不能为空
• 4.全文索引：fulltext，用于一篇文章中检索文本信息

创建索引的条件：

• 1.经常用作where条件或者join子句中常出现的列；
• 2.索引列值的分布，基数越大索引效果越好；
• 3.使用短索引，如对字符串进行索引，制定一个前缀长度节省索引空间提升速率；
• 4.不要过度索引，索引占用额外的磁盘空间，索引过多也会降低写操作性能；
• 5.查询条件中有or时，只有查询条件都创建索引才能使用索引，有其中一个没有建立索引，则这条sql不会使用索引；
• 6.最左匹配原则：
  假如有个联合索引 key (col1,col2)。那么以下查询是索引无效的

select * from test where col2 = 3;
select * from test where col1 like '%3';

• 7.索引列不能是表达式或函数的一部分，否则索引无效；

注意：

• 1、不同的存储引擎支持不同形式的索引,同一种索引在不同的存储引擎下也会有不同的实现方式;例如MyISAM和InnoDB两种就不支持哈希索引,两者都支持B+Tree索引,但实现方式有所区别;
• 2、查看索引使用情况

show status like 'Handler_read%';
// Handler_read_key 值越高表示索引使用率越高
// Handler_read_rnd_next 值越高索引使用率越低

为减少IO操作,每一次IO读取数据时都会将其相邻的数据读取到内存的缓冲区,因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到;
B+Tree索引原理:

image.png
将数据分散存储在不同的磁盘块中,叶子节点用于存储数据,非叶子节点用于存储搜索方向;

五、视图：是一张虚拟表，select语句执行的结果集

视图的优点:

• 1、使用视图，可以定制用户数据，聚焦特定的数据。
  解释:在实际过程中，公司有不同角色的工作人员，我们以销售公司为例的话， 采购人员，可以需要一些与其有关的数据，而与他无关的数据，对他没 有任何意义，我们可以根据这一实际情况，专门为采购人员创建一个视 图，以后他在查询数据时，只需select * from view_caigou 就可以啦。
• 2、使用视图，可以简化数据操作。
  解释：我们在使用查询时，在很多时候我们要使用聚合函数，同时还要
  显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能
  会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我
  们只需要select * from view1就可以啦，这样很方便。
• 3、使用视图，基表中的数据就有了一定的安全性
  因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集合，数据是随着基表的更新而更新。同时，用户对视图，不可以随意的更改和删除，可以保证数据的安全性。
• 4、可以合并分离的数据，创建分区视图
  随着社会的发展，公司的业务量的不断的扩大，一个大公司，下属都设有很多的分公司，为了管理方便，我们需要统一表的结构，定期查看各公司业务情况，而分别看各个公司的数据很不方便，没有很好的可比性，如果将这些数据合并为一个表格里，就方便多啦，这时我们就可以使用union关键字,将各分公司的数据合并为一个视图。

六.数据一致性(处理高并发)

修改下表中的username,打开两个终端窗口,第一个输入

//手动提交事务 为1时自动提交
set autocommit=0; 
begin work; 
select * from users where id=1 for update;

在另一个窗口输入

update users set username=123 where id=1;

此时第一个窗口正在更新该条数据,并为其家锁,因此第二个窗口无法都数据进行更改,数据库报错;

image.png

第一个窗口提交事务后,其余进程才可以修改数据

update users set username=23 where id=1;commit work;

七.函数、存储过程、存储引擎

信息函数：
显示要查询内容的信息（数据库版本 数据的编码方式等）
自定义函数（UDF）：必要条件（参数 返回值）
创建自定义函数：
create function 函数名称(参数列表)
returns 返回值类型
函数体（sql语句）
create function f_name
returns{string|interger|real|decimal}
函数体

• 存储过程：
  SQL语句和控制语句的预编译集合，以一个名称存储作为一个单元处理。
• 存储过程的优点：
  　　 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
  　　2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
  　　3.存储过程可以重复使用,可减少数据库开发人员的工作量
  　　4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权
• 创建存储过程：
  create procedure sp1(参数)
  select version();
  调用存储过程：
  call sp1[()];
  IN：表示该参数的值必须在调用存储过程时指定，不可以返回
  OUT：表示该参数的值可以被存储过程改变，并且可以返回
  INOUT：表示该参数的值在调用时指定，并且可以被改变和返回
  存储过程与函数的区别：
  函数只能通过return语句返回单个值或者表对象。而存储过程不允许执行return，但是可以通过out参数返回多个值，并且定义时的情况也有不同，存储过程的参数需要判断为输入参数还是输出参数，对应IN和OUT，在参数前面注明。函数定义时使用FUNCTION，存储过程定义使用PROCEDURE。存储过程的效率高于函数的效率。
  距离创建一个存储过程，根据age删除记录，并输出删除内容和删除后的内容
• 存储过程与自定义函数的区别：
  1、存储过程实现的功能相对复杂，函数针对性较强
  2、存储过程可以返回多个值，函数只能有一个返回值
  3、存储过程一般独立执行，函数可以作为 sql 语句的组成部分来出现

create procedure sp1(in p_age smallint unsigned,out deleteUser smallint unsigned,out userCount smallint unsigned)
begin
delete from users where age=p_age;(删除年龄为p_age的用户名)
select row_count into deleteUser;(将删除的用户存入deleteUser表;row_count函数返回前一个被sql更新，删除，插入的行)
select count(id) from users into userCount;(将删除后的信息存入userCount表)
end
call sp1(20,@a,@b);

begin—end语句中间声明的局部变量之在begin—end之间有效。
set @num：set或者select 后跟的@num叫做用户变量。
存储引擎：
根据每张表经常的不同操作，而选择不同的存储引擎。一种存储数据，查询数据的技术。

• 1 MyISAM：管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。

• 2 InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。

• 3 Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

• 4 CSV：存储引擎把数据以逗号分隔的格式存储在文本文件中。

• 5 Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

• 并发控制：当多个连接对数据进行修改时保证记录的一致性和完整性。

• 共享锁（读锁）：在多个用户同时读取一条数据时，数据不会发生改变。

• 排他锁（写锁）：在任何情况下只能有一个用户进行写操作，此时阻止其他用户进行读操作或者写操作。

八、范式

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。