杂20190531

SQL：结构化查询语句
分类：
	DDL (Data Definition Language)数据定义语句 create drop alter
	DML (Data Manipulation Language)数据操作语句 insert delete update select
	DCL (Data Control language)数据控制语句 用户权限 安全级别 grant revoke 

索引：MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
	索引是数据库中用来提高性能的最常用的工具，所有的MySQL的列都可以被索引，通过对相关的列使用索引从而提高select操作性能。
    不同的存储引擎对于每个表至少可以建立16个索引，总索引长度至少为256个字节。创建索引的时候，符合某些原则，便于提高索引的使用效率。它最好是在where后面的列中，或者连接字句指定列中。
	包括：主键索引，唯一索引，普通索引，全文索引，
	
	索引的实现是在MySQL的存储引擎层实现的。
	MySQL目前提供的有以下几种索引：
		B-Tree索引：最常见的索引类型，大部分的引擎都支持B树索引。表示平衡树，它并不是二叉树。
		Hash索引:  只有Memory支持，使用场景比较简单。使用于key-value查询，这样查询速度更快，但是不利于范围查询。即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
		R-Tree索引:空间索引，是Myisam的一个特殊的索引类型，用于地理空间数据类型，不常用。
		Full-text索引:全文索引，也是Myisam的一个特殊索引类型，主要用于全文索引，Innodb后来也支持。
		
	Myisam和InnoDb存储引擎的表默认是创建的btree索引。Memory默认创建的是Hash索引。
	前缀索引，全文索引(char,varchar,text)，空间索引(是Myisam,索引字符字段非空)
	
	创建索引使用 create index index_name on table_name
	删除索引使用 drop index index_name on table_name

	普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
	唯一索引：索引列中的值必须是唯一的，但是允许为空值，加速查询 + 列值唯一（可以有null）
	主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引），只能有一个主键一个表：
加速查询 + 列值唯一（不可以有null）+ 表中只有一个，主键上面才可用auto_increment
	复合索引：
		create index idx_fuhe (datae,cid,id) ，复合索引通常也会和最左前缀原则联系在一起。在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
	全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。
	
	
	
	最左前缀原则(Match a leftmost prefix)：
		在创建n个索引的时候，实际上创建了MySQL可以利用的n个索引，多列索引可以起多个索引的作用，因为可利用索引最左边的列集，来匹配行。create add index idx_a_b_c(a,b,c) on table；类似这种，如果在where中使用前几个列，那么会使用到这个符合索引。最左匹配原则是B-tree中很重要的一个。
	范围查找匹配(Match a range of values):对索引的值能够进行范围查找，也就是通常是type=range，另外extra=Using where 回表查询数据，可能还有其他要查询的列不是索引。
	仅仅对索引查询（Index only query）：
		如果查询的列都在索引字段中，这个时候查询的效率更好，而且查询计划中，不需要回表获取数据，直接访问索引就可以获取到,也就是extra中，Using index覆盖索引扫描。大大提高数据访问的效率。
	
	前缀索引：也就是在一个字段上，例如标题字段，对前5个字符进行索引，对索引文件大大缩小，但是在order by和group by的时候就无法使用。create index idx_title on table(title(10));
	全值匹配（Match the full value）：
		对索引中所有的列都有指定具体值，这样type=const常量。
		
	没有利用到索引：
		以%号开头的like查询不能利用b-tree索引，执行计划中key=null
	    数据类型出现隐士转化的时候也不会利用索引，例如name=1这种情况
	    最左原则不符合的话，也是不会用到复合索引。
	    筛选性越高的越容易使用到索引
	    or前后两列，如果有一个字段没用到索引，则全表扫描。
	
btree索引和hash索引
	对于memory存储引擎的表，可以使用btree索引或者hash索引，默认是hash索引。
	hash索引实际上是全表扫描；
	对索引字段进行范围查询的时候，只有btree索引可以通过索引访问;
	对索引字段进行in范围查询，可以用hash索引
	
	
优化：
Option:有三种analyze分析表 check检查表 optimize优化表，在之执行期间会锁表，所以要选择一个数据库不繁忙的时候执行。
	分析表：用于分析和存储 表的关键字分布，分布的结果将可以使系统得到准确的统计信息。使SQL能够生成正确的执行计划，在分析期间使用一个读取锁定表，这对于myisam,dbd,innodb有作用。
	mysql> analyze table news_user;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| news.news_user | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set (0.03 sec)
	检查表：检查一个表或者多个表是否有错误，对于myisam和innodb有作用。对于myisam表关键字统计数据被更新
	mysql> check table news_user;
+----------------+-------+----------+----------------------------------------------------------+
| Table          | Op    | Msg_type | Msg_text                                                 |
+----------------+-------+----------+----------------------------------------------------------+
| news.news_user | check | warning  | 2 clients are using or have not closed the table properly |
| news.news_user | check | status   | OK                                                       |
+----------------+-------+----------+----------------------------------------------------------+
2 rows in set (0.03 sec)
	优化表：如果已经删除表的一大部分，或者已经对含有可变的长度的行例如varchar text的表，进行很多更改，呢吗可以使用optimize table来优化表，它可以将表中空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费。对于myisam innodb bdb 起作用。
	mysql> optimize table news_user;
+----------------+----------+----------+----------+
| Table          | Op       | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| news.news_user | optimize | status   | OK       |
+----------------+----------+----------+----------+
1 row in set (0.02 sec)
	
SQL语句优化：
	[1]load导入数据的时候 load data infile 'txt' into table table_name;
	当大批量导入数据load时候，对于myisam存储的表，我们使用alter table table_name disable_keys/
enable_keys 来开启或者关闭myisam表的非唯一索引的更新。将数据导入一个非空的myisam表中，通过设置这两个命令可以提高导入效率。
	对于innodb这种，因为表的类型是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入的数据的效率。
	在导入数据前执行，set unique_checks=0 关闭唯一校验，然后导入后执行，set unique_check=1,恢复唯一性校验，提高导入效率。
	另外应用使用自动提交的话，建议导入前使用set autocommit=0关闭自动提交，然后导入后执行，set autocommit=1 开启自动提交。
	[2]insert语句
		如果要同时插入多条数据，建议使用多个值表的insert语句，这样可以大大缩减客户端与数据库之间的连接，关闭等消耗。insert into table values(1),(2),(3)....  
		如果是不同的客户端插入很多行，可以使用Insert delayed使插入语句更快，立马执行。
		可以用一个文本文件载入一个表，load data infile 通常比插入语句insert快20倍。
	[3]order by
		通常排序有两种，一个使通过有序索引，另外是通过返回数据进行排序filesort排序。
	[4]group by
		默认情况下mysql对group by后面的字段进行排序，如果想要避免排序带来的消耗，那么我们可以指定order by null 禁止排序。
	[5]or
		对于含有or的查询子句，如果要使用到索引，则or之间的每个条件列都必须用到索引，如果没有索引，可以考虑添加。
		

Myiam与Innodb的区别：
Innodb:
	提供事务(supports transactions)事务安全型存储引擎 数据完整安全 
	行级锁定(row-level locking) ：一定情况下可进行行级锁来提升并发性
	外键约束(foreign keys)
	数据与索引集中存储在同一个表空间文件。*.frm文件 数据表的结构定义 *.idb文件 InnoDB的数据与索引 
	操作方面： 
        读锁（共享锁S-lock，阻塞其他客户端的写操作）， 
        写锁（独占锁或排他锁X-lock，阻塞其他客户端的读写操作） 
        锁定粒度（范围） 
        行级 对于并发性比较好 row-level locking 实现了行级锁，但是锁的开销大 
        表级 不利于并发性
    优点：数据完整性【事务，外键约束】 
    并发行处理【行级锁定，多版本控制】 
    缺点 : 高速检索 
    高速插入【需要进行排序后插入】
Myisam:
插入速度快，空间占用量小 
	*.frm 数据表的结构定义 
    *.MYD myisam的表数据(mydata) 
    *.MYI myisam的索引数据(myindex) 
全文索引支持（>=5.6 innodb也支持）查询非常频繁。
R-Tree索引:空间索引，是Myisam的一个特殊的索引类型，用于地理空间数据类型，不常用
数据压缩存储，压缩优势空间减少，减少磁盘IO开销 。

索引：
	myisam的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少，而Innodb是索引和数据放在一起，且没有使用压缩，因而Innodb比myisam体积更大。myisam每张表都包括三种文件：表定义文件（.frm）、数据文件（.myd)和索引文件（.myi)，而Innodb通常许多表保存在同一个文件中。*.frm文件 数据表的结构定义 
*.idb文件 InnoDB的数据与索引。InnoDB不支持全文索引，而myisam支持。全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。

事务：
	myisam类型的表强调的是性能，其执行数度比Innodb类型更快，但是不提供事务支持，而InnoDB类型支持,可靠性要求比较高. 
innodb的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度,但是比较可靠，对于金钱股票等数据要求性比较高使用Innodb.

操作
	如果执行大量的select ，myisam是更好的选择. 
	如果你的数据执行大量的insert 或update ，出于性能方面的考虑，应该使用InnoDB表 
	清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
	在执行数据库写入的操作（insert,update,delete）的时候，mysiam表会锁表，而innodb表会锁行
 

【原理】
Nginx本身不会对PHP进行解析，终端对PHP页面的请求将会被Nginx交给FastCGI进程监听的IP地址及端口，
由php-fpm作为动态解析服务器处理，最后将处理结果再返回给nginx。其实，Nginx就是一个反向代理服务器。
Nginx通过反向代理功能将动态请求转向后端php-fpm，从而实现对PHP的解析支持，这就是Nginx实现PHP动态解析的原理。

【CGI】:通用网关接口（Common Gateway Interface/CGI）
	CGI 是Web 服务器运行时外部程序的规范接口，按CGI 编写的程序可以扩展服务器功能。
CGI 应用程序能与浏览器进行交互，还可通过数据库API与数据库服务器等外部数据源进行通信，从数据库服务器中获取数据。
格式化为HTML文档后，发送给浏览器，也可以将从浏览器获得的数据放到数据库中。
几乎所有服务器都支持CGI,可用任何语言编写CGI。
传统CGI接口方式的主要缺点是性能很差，
因为每次HTTP服务器遇到动态程序时都需要重新启动脚本解析器来执行解析，然后将结果返回给HTTP服务器。这在处理高并发访问时几乎是不可用的.

【FastCGI】
	是一个可伸缩地、高速地在HTTP server和动态脚本语言间通信的接口.
FastCGI接口方式采用C/S结构，可以将HTTP服务器和脚本解析服务器分开，
同时在脚本解析服务器上启动一个或者多个脚本解析守护进程。
当HTTP服务器每次遇到动态程序时，可以将其直接交付给FastCGI进程来执行，
然后将得到的结果返回给浏览器.可以让HTTP服务器专一地处理静态请求或者将动态脚本服务器的结果返回给客户端，
这在很大程度上提高了整个应用系统的性能。
	
【Nginx+FastCGI】
 	Nginx不支持对外部程序的直接调用或者解析，所有的外部程序（包括PHP）必须通过FastCGI接口来调用。
FastCGI接口在Linux下是socket（这个socket可以是文件socket，也可以是ip socket）。
 	wrapper：为了调用CGI程序，还需要一个FastCGI的wrapper（wrapper可以理解为用于启动另一个程序的程序），
 	这个wrapper绑定在某个固定socket上，如端口或者文件socket。当Nginx将CGI请求发送给这个socket的时候，
 	通过FastCGI接口，wrapper接收到请求，然后Fork(派生）出一个新的线程，
 	这个线程调用解释器或者外部程序处理脚本并读取返回数据；
 	接着，wrapper再将返回的数据通过FastCGI接口，沿着固定的socket传递给Nginx；
 	最后，Nginx将返回的数据（html页面或者图片）发送给客户端。

 	如果是tcp的socket 那么在nginx配置文件中，fastcgi_pass 127.0.0.1:9000; 
          而tcp需要经过loopback，还要申请临时端口和tcp相关资源;tcp这样的面向连接的协议，多少可以保证通信的正确性和完整性。
 	如果是unix的socket那么在nginx配置文件中，fastcgi_pass unix:/var/run/php7.0-fpm.sock; 
          unix socket减少了不必要的tcp开销。unix socket高并发时候不稳定，连接数爆发时，会产生大量的长时缓存
 	
 	选择建议：如果是在同一台服务器上运行的nginx和php-fpm，并发量不超过1000，选择unix socket，
 	因为是本地，可以避免一些检查操作(路由等)，因此更快，更轻。 
 	如果面临高并发业务，我会选择使用更可靠的tcp socket，以负载均衡、内核优化等运维手段维持效率。
 	
 浏览器<->Nginx<->socket<->fastCgi常驻进程[wrapper]创建一个个线程<->PHP-FPM管理器->php应用