MYSQL一些知识点的总结

    本人之前也是MySql小白一枚，之前学习了一些MySql的知识比较零散，写这篇文章也是为了能够加深记忆，总结一下知识，以便日后回顾。如果能够和大家一起交流学习那更好了，如果文章中有错误或者有歧义的地方希望大家能够指正。
MySql

简单命令
CREATE DATABASE(SCHEMA) IF NOT EXISTS dbName DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE table_name (id int,name char(10) CHARACTER SET utf8 COLLATE utf8_general_ci);
ALTER TABLE tablename DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
insert into tablename values(...);
update tablename set ... where ...;
delete from tablename where ...;
select ... from tablename where ...;
SELECT * FROM ... ORDER BY ...;
表复制
INSERT INTO copy_clone (id, `name`) SELECT id, `name` FROM copy_source;

SQL语句
ALTER用于修改字段含义
update用于修改具体的数据

字符编码
utf8编码和utf8mb4的区别
utf8最多支持三个字节，utf8mb4支持4个字节的字符。
utf8可以说是utf8mb4的一个子集；utf8mb4在utf8的基础上新支持表情等复杂的字符。

乱码的解决方案：
首先明确客户端使用的是何种编码格式，保证connection的字符集包含client的字符集，保证数数据库使用的是utf8的编码格式，所有的编码类型通吃。

libmysqlclient库的常用函数
MYSQL* mysql_init(MYSQL* mysql);  // 初始化mysql
MYSQL* mysql_real_connect(MYSQL* mysql, const char* host, const char* user, const char* password, const char* dbname, unsigned int port, const char *unix_socket,usigned long flag);  // 连接mysql
mysql_query(MYSQL* mysql, const char* command);   // 执行相关指令
MYSQL_RES *mysql_use_result(MYSQL *mysql);  // 检索查询的结果，但是不会读取到客户端
MYSQL_RES *mysql_store_result(MYSQL *mysql);  // 检索查询的结果，会保存并读取到客户端
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);  // 获取结果集的一行，循环读取可以将结果全部读出，当读到NULL时退出

Explain关键字
Explain关键字可以模拟优化器执行你的mysql语句从而可以知道mysql是如何处理SQL语句的，用于分析查询语句以及表结构的性能瓶颈。
但是Explain只能解释select操作，其他语句需要重写为select语句才能解释。
Explain不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；
Explain不考虑各种Cache
Explain不能显示mysql在执行查询时所作的优化工作，部分统计信息是估算的，并非精确值

慢查询日志
Mysql内部提供的一种记录日志的方式，当SQL语句执行的时间超过long_query_time,就会记录到日志内。long_query_time默认为10s

临时表何时释放空间？
临时表仅对当前的连接有效，在连接结束时就会释放掉这部分的空间。

视图：
create view viewname as ...(具体的操作)；
作用：
    视图相当于是一个函数，可以提高mysql的重用性；
    可以提高数据库的重构性，比如可以将两个表中的某一个字段合成一个新的表等；
    提高数据的安全性，对数据库设置可见性。通过SQL语句设置访问的属性。
    可以使得数据的访问更加清晰。
    
自定义函数

delimiter $$  // 为了防止函数内部的分号被当做是结束符，我们将结束符暂时换为$$
create func myfunc(cname varchar(15)) returns int
begin
    select * from english where name=cname;
    return 1;
end;

存储过程
是一组为了完成特定功能的mysql语句集。MYSQL 5.0以上支持存储过程。
参数分为输入参数，输出参数和输入输出参数。如果是输入值用IN，返回值用OUT，INOUT尽量少用
作用：
增加了SQL语言的功能及灵活性；标准式的组件编程，可以被多次利用
提高了SQL语句的执行效率，因为存储过程语句是预编译的，第一次执行时就经过优化得到了最合适的执行方式
可以对执行的存储过程设置权限，提高了安全性
由于只需要执行call这个语句减少了网络流量

函数和存储过程的区别：
函数只有一个返回值，不允许返回一个结果集。                                                                                                                   

事务
在MySql中只有InnoDB的存储引擎支持事务；事务可以保证数据库的完整性，事务内的sql语句要么不执行，要么全部执行。
事务的自动提交
若autocommit=0，那么执行commit之前的一系列SQL操作作为一个事务，不执行commit则默认事务回滚。
若autocommit=1，如果先执行start transaction，然后执行一系列sql操作，然后再commit，此时就会将这些操作作为一个事务，
如果自动开启自动提交，也就是不执行start transaction，那么系统会将每一条SQL操作作为一个单独的事务，也就是说用户每进行一次操作系都会即时提交或者即时回滚。

事务的四大特性：
原子性：每一个事务操作是不能被打断的，要么全部完成，要么全部不完成；
隔离性：数据库允许多个并发事务同时修改数据库，事务的隔离性保证多个事务交叉执行时可以保证数据的一致性。
        事务隔离的级别：读未提交，读提交，可重复读，串行化
数据读取可出现的问题
脏读：事务A可读到事务B未提交的数据
不可重复读：事务A前后两次取数据，得到的数据不一致，针对update操作。
解决：使用行级锁，锁定操作的行，执行完之后才能释放。
幻读：事务A前后两次取数据，得到的数据不一致，针对insert和delete操作。
解决：使用表级锁，锁定整张表。
一致性：事务执行前和事务执行后，数据库的完整性不被破坏。
持久性：事务结束后，对数据的修改是永久的，即使系统出现故障也不会丢失。

MySql存储引擎
行级锁、表级锁和页级锁
行级锁 行级锁是 MySQL 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁分为共享锁和排他锁。
特点：加锁开销大，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度最高
页级锁 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。采取了折衷的页级锁，一次锁定相邻的一组记录。BDB支持页级锁。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。 
开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

MyISAM 存储引擎 内部使用b+树索引
不支持事务和外键，因此访问的数据较快，用到频繁的插入和读取可以选择MYISAM存储引擎。
MYISAM的索引和数据是分开存储的，索引首先找到数据存放所在的位置，通过这个指针找到具体的数据，

InnoDB存储引擎 内部使用b+树索引
具有提交、回滚、崩溃修复等的事务特性。如果需要频繁的删除和更新的操作或者需要保持事务的完整性，并发控制等建议选择InooDB。
由于节点索引和数据没有分开，因此不支持全文索引

MERGE存储引擎
是几个表结构完全相同的MYISAM表的组合，数据存储在MYISAM表中。

MEMORY存储引擎
内部使用HASH索引，速度上比B+索引快，但是由于数据存储在内存中，因此数据具有不稳定性。

ARCHIVE存储引擎
拥有很好的压缩机制，插入快速，因此适合日志表的存储；前提是不频繁进行查询操作。
...还有许多不是主流的存储引擎，后续补充

InnoDB和MYISAM的区别：
（1）InnoDB支持事务，MYISAM不支持事务
（2）InnoDB为行级锁，MYISAM为表级锁，因此前者更容易出现死锁，上锁的开销也会更大，锁冲突的概率也更大。
（3）灾备份容灾上，InnoDB支持在线热备。
（4）查询数据方面MYISAM更加快速，因为MYISAM可以直接找到存储数据的内存地址，然后找到数据，而InnoDB的话需要先定位行所在的数据块，然后找到所在的行。
（5）如果查询的级别在千万以上，MYISAM可以快速查找，而InnoDB很缓慢。因为MYISAM行数是单独存储的，而InnoDB则需要逐行统计；因此InnoDB需要查询行数的话需要特殊处理，离线查询和缓存。
（6）表结构不同，MyISAM 的表结构文件包括: .frm 表结构定义, .MYD(索引), .MYD(数据); 而 InnoDB 的表 数据文件为: .ibd 和.frm(表结构定义)

选择存储引擎需要考虑的因素
1) 使用场景是否需要事务支持；
2) 是否需要支持外键；
3) 是否需要支持高并发，InnoDB 的并发度远高于 MyISAM；
4) 高效缓冲数据，InnDB 对数据和索引都做了缓冲，而 MyISAM 只缓冲了索引。
5) 索引，不同存储引擎的索引并不太一样

B+ Tree的实现

InnoDB 和 MyISAM 实现都是使用 B+树
MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。 MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则 取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。
MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
InnoDB 中，表数据文件（数据和索引放在一块）本身就是按 B+Tree 组织的一个索引结构。 这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键。因此 InnoDB 表数据文件本身就是主索引。

MYSQL优化
1.MYSQL语句及索引的优化
（1）MYSQL优化
     首先应避免全表扫描，在where或orderby用到的列上建立索引；
     尽量在where后面不要使用！或者> < 操作，因为这样会导致全表扫描；
     避免在where子句后面对NULL进行判断，也会导致引擎放弃索引而使用全表扫描。可以将NULL值设置为0；
     避免在where子句中使用or条件语句
     避免使用in和not in，连续的数值能用between就不用in，也可以用exists代替in
     下面的操作也会导致全表扫描：
     select id from t where name like '%abc%' 
     若要提高效率，可以考虑全文检索
     如果在where子句中使用参数也会导致全表扫描。因为SQL需要在运行时才会执行参数，但是优化程序不会将计划推迟到运行时。
     尽量避免在where语句后面对字段进行表达式操作：select * from mydb where id*2=100；应该改成num=50；
     尽量避免where语句后面进行函数操作
     不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将 可能无法正确使用索引。
     在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让 字段顺序与索引顺序相一致。
     应控制索引的数量以及重复索引的数量，提高效率
     应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的 资源。
     尽可能使用数字字段作为索引
     避免返回不需要使用的字段，因此当有具体需求是避免使用select * from t;
     尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
     避免频繁创建和删除临时表，以减少系统表资源的消耗
     临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
     创建临时表时如果数据量很大的话，可以直接使用select into或者create，如果数据量不大的话可以先create table然后再insert。
     使用临时表时需要及时删除
     尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过 1 万行，那么就应该考虑改写。
     与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优 于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合 计”的例程通常要比使用游标执行的速度快。
     如果开发时间允许，基于游标的方法和基于集 的方法都可以尝试一下，看哪一种方法的效果更好。
     在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
     尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
     尽量避免大事务操作，提高系统并发能力。
（2）索引优化
     索引的方式：主键索引、唯一索引、普通索引、全文索引、组合索引
     索引类型 特点
     普通索引 最基本的索引，没有任何限制
     唯一索引 与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
     主键索引 它是一种特殊的唯一索引，不允许有空值。
     全文索引 仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
     组合索引 为了更多的提高 mysql 效率可建立组合索引，遵循”最左前缀“原则。创建复 合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。
     索引类型的选择：一般选择数据类型越小，越简单的，同时要避免NULL，可以设置为0

选择主键的类型
     需要保证所有相关的表都是用相同的数据类型，首选整型，尽量避免字符串，因为消耗空间并且索引很慢
     主键的优化：
     不能为NULL
     如果在表中没有合适的列作为主键，则应该新加一列，以 auto-increment 方式增长，作为主键。
     外键优化
     如果表有很多的列，查询也有很多的组合，那么有必要将使用率较低的列划分到关联的不同表中，并使用主表主键进行关联。这样，每个小表都有个主键来提供快速查询使用，对于综合查询，可以使用相关的表进行联合查询。
     数据存储分布的不同及具体数据的组织形式不同，会对查询缓存需求及I/O访问产生较大影响。为了尽可能的提高性能，应该尽量减少磁盘I/O，一些具有较少列的表可以尽量一次将较多的行数据查询到内存。

2.数据库表结构的优化

    首先尽量使用能存下你的数据的最小，最简单的数据类型来存你的数据，其次在数据存储的时候尽量使用not null的格式存储，还有就是尽量使用int代替text类型。

    结构上的优化还可以垂直分表，将比较少使用的字段分在一个表中，将一个常用的字段单独建立一个表。水平拆分表也是一种解决办法，能够解决数据量过大的问题，将一些id可以拆成多个进行存储。常用的方法是hash，将内部的id进行hash，然后对hash的结果进行id的分类，然后拆分大表。如果 Qcache_lowmem_prunes 很大，就要增加 Query_cache_size 的值。

3.系统配置的优化

    系统配置的优化有一——部分是硬件上的优化，主要是磁盘，CPU以及内存方面，而另一方面的话就是对于MySql的配置文件my.conf的修改。通过key_reads/keyrequest至少1/100以上，越小越好。                                                                                                                                                                         ；
4.硬件的优化

硬件上的优化可以分为操作系统级别的优化以及服务器级别的优化。分细了说的话，操作系统包括IO调度策略，CPU等，服务器级别的优化包括服务器选型，CPU个数，核数等。