MYSQL数据库架构

前提：

  数据库存储引擎：myisam不支持事务即不进行回滚,innodb支持事务等。

  读写锁：即共享锁（读锁）不阻塞和排他锁（写锁）相互阻塞。

  锁粒度：为提高系统并发数，把锁进行细分以减少加解锁资源消耗。

    表锁：为正张表进行上锁，锁消耗较小，但增删改操作多时，耗时长。

    行级锁：为每条数据进行上锁，但锁消耗较大。

事务：一组原子性sql数据或一个独立工作单元，要么全部执行成功，要么全部执行失败。（四大特性：ACID）事务处理在存储引擎上，并不是在数据库服务器，所以同一种事务，不同存储引擎不可靠，所以为每张表选择那种存储引擎很重要。

atomicity(原子性)：对于一个事务，事务里面的操作要么全部提交成功，要么全部失败回滚。

consistency(一致性)：对于一个事务里面操作的修改状态，数据库总是从一个一致性状态转换为另一个一致性状态。

isolation(隔离性)：两个事务之间互不影响，不可见。

durability(持久性)：一旦事务进行提交，无论系统是否奔溃，都将事务操作数据提交到数据库。

事务隔离级别：定义事务之间那些可见，那些不可见。隔离级别越低，并发数越好。

read uncommitted(未提交读)：一个事务未提交就被另一个事务读取信息，引发脏读（事务操作读取的信息未必正确）。

read committen(可提交读)：一个事务在开始到提交之前对其他事务是不可见的，引发不可重复读（两次事务执行相同查询，可能结果会不一样，因为在修改完成状态还没提交，另一个事务就再次查询状态引发结果不一致）。

repeatable read(可重复读)：保证两次事务执行相同查询，返回结果的一致性。解决了脏读问题，但会引发幻读（当前事务在一个范围内读取数据，另一个事务也在该范围内插入数据，当前事务再次读取数据就会有幻行信息（未必正确））。

serializable(可串行化)：是隔离级别最高一种，解决了脏读和幻读。指事务在读取每行数据都会加锁，强制执行事务串行化，但会导致大量超时和锁争用问题。场景：要求保证数据一致性和并发不高。

死锁：两个或两个以上事务对同一资源相互竞争占有，导致恶性循环，产生死锁。

解决：数据库系统实现死锁检测和死锁超时机制。能检测是否死锁来立马返回一个错误或锁超时放弃请求。（innodb存储引擎最少行级锁进行回滚）。

innodb：它采用mvcc支持高并发，默认隔离级别可重复读，通过间隙锁来防止幻读；它的表基于聚簇索引建立的；

--创建MyISAM模式表方便批量跑数据
 
CREATE TABLE `logs1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `logtype` varchar(255) DEFAULT NULL,
  `logurl` varchar(255) DEFAULT NULL,
  `logip` varchar(255) DEFAULT NULL,
  `logdz` varchar(255) DEFAULT NULL,
  `ladduser` varchar(255) DEFAULT NULL,
  `lfadduser` varchar(255) DEFAULT NULL,
  `laddtime` datetime DEFAULT NULL,
  `htmlname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  AUTO_INCREMENT=1811 DEFAULT CHARSET=utf8 COMMENT='日志表';
 
 
--创建存储过程
 
DROP PROCEDURE IF EXISTS my_insert;
CREATE PROCEDURE my_insert()
BEGIN
   DECLARE n int DEFAULT 1;
        loopname:LOOP
            INSERT INTO `logs1`(`logtype`,`logurl`,`logip`,`logdz`,`ladduser` ,`lfadduser`,`laddtime`,`htmlname`) VALUES ( 2, '/index', '0:0:0:0:0:0:0:1', null, null, 'null', '2018-05-03 14:02:42', '首页');
            SET n=n+1;
        IF n=10000000 THEN
            LEAVE loopname;
        END IF;
        END LOOP loopname;
END;
 
 
--执行存储过程
CALL my_insert();
 
 
--数据插入成功后修改表模式InnoDB 时间稍微久点
 alter table `logs1` engine=InnoDB;

数据类型

整数类型：tinyint（8）、smallint（16）、mediumint（24）、int（32）、bigint（64）。数值-2（n-1）到2（n-1），n为位数。如：int(11) 设置类型为11大多数是没有意义的，因为它不能限制合法范围，只是对于客户端显示多少个字符个数，int(1)和int(10)存储和计算是一样的。

实数类型：decimal(它比bigint还大)可以存储带有点小数值，支持精确度。对小数进行精确计算可以使用，精确计算需要更长时间，所以金钱最好存储单位为分，使用bigint存储。

浮点类型：float（4）、double（8）存储带有小数点数值。

字符类型：char、varchar存储字符串

区别：存储引擎决定类型存储类型方法，char存储的数据未尾空格会清除（这个是服务器进行处理方法，所以是固定的），固定存储数据长度。（但它比varchar存储效率高，因为当存储一个Y和N固定值时，char(1)只需要一个字节，varchar(1)需要两个字节，因为它还需要一个记录长度的额外字节）varchar可变字符根据真实数据分配存储空间大小，对于修改长度发生较大变化时使用。（虽然varchar(11)和varchar(128)存储空间一样，但是它们的内存临时表进行排序和操作时和利用磁盘临时表时效率低，因为mysql通常分配固定内存保存内部值，所以越长列消耗内存越多）

二进制字符串类型：binary、varbinary存储字节码（它们不但存储二进制数据，还可以在比较数值是根据该字节比较，所以比字符串比较数值效率更高）

大型字符串类型：blob(tinyblob,smallblob,blob,mediumblob,longblob)、text(tinytext,smalltext,text,mediuntext,longtext)，分别采用二进制（即字节数据）和字符存储大型数据。

blob,text区别：blob存储二进制数据，没有排序规则和字符集，text有排序规则和字符集。

时间类型：datetime（以整数方式存储时间YYYYMMDDHHMMSS）、timestamp（以时间戳方式存储时间yyyy-mm-dd hh:mm:ss）区别：timestamp有时区。

 

mysql索引：索引是在存储引擎执行，所以不同存储引擎对同一种索引内部执行是不一样的。

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据

B+树索引：索引是以最左数据列开始（如：123最左列为1）。

hash(哈希)索引：基于hash表实现，只有精确匹配索引列查询才能生效。它先对索引列计算得出一个hashcode值，然后将这些值存储在索引中，同时hash表保存了指向数据行的指针。注意：哈希索引不包含字段值，也不是按索引顺序存储所以无法排序，不支持部分索引和范围查询，访问哈希虽然速度快，但会有哈希冲突（链表解决），冲突较多维护代价就高。场景：它有这么多缺点但还是有一定优点的，关联表查询适合，它非常适合查找表需要。使用范围列查询也会导致无法使用索引

B+ Tree索引和Hash索引区别？

哈希索引适合等值查询，但是无法进行范围查询 

哈希索引没办法利用索引完成排序 

哈希索引不支持多列联合索引的最左匹配规则 

如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

 

空间索引（r-tree）：不同与b-tree索引，它不用前缀查询，查询时使用任何维度组合查询。（gis函数）

全文索引：它查询文本的关键字查询信息。

innodb：使用B+树索引，但同时引入自适应哈希索引（这样做还可以防止一个二级索引的二次B树查询，因为它叶子点保存的不是数据行指针，而是一个主键值，再次查询聚簇索引才能得到真正的数据行），一旦某个索引列被频繁使用，它就会在b+树索引上创建一个哈希索引，使得它具有哈希优点能够快速查询信息。

 

怎么使用高性能索引策略：独立索引列、前缀索引列、多列索引列、聚簇索引。

 

聚簇索引：它里面包含索引和数据行，如在innodb保存b+树索引和数据行（主键列）。

优点：把相关数据存储在一起，使得数据访问速度更快。（如innodb只使用主键条件查询信息）

缺点：1.如果把数据放在内存中，那这样主键条件查询就没有任何意义（可以用主键条件去查询就没有必要缓存到内存中）。2.插入速度依赖于插入顺序，所以尽量使用自增按主键顺序插入数据速度最快（但在高并发下，主键自增锁竞争激烈，可以使用uuid）。3.更新聚簇索引列代价大，因为要强制每个被更新的行移到新的位置（二级索引维护的是主键值，所以它不需要更新指针）。4.可能导致全表扫描变慢，页分裂导致数据存储不连续。

 

非聚集索引

定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引.

除了聚集索引以外的索引都是非聚集索引,分成普通索引,唯一索引和全文索引.

注意:非聚集索引查询在索引没覆盖到对应列的时候需要进行二次查询,索引非聚集查询较慢.

 

如何解决非聚集索引的二次查询问题：

覆盖索引：索引中包含或者覆盖所需要查询字段的值，就叫覆盖索引。

建立两列以上的索引,即可查询复合索引里的列的数据而不需要进行回表二次查询,如index(col1,col2),执行下面的语句

select col1,col2 from 表名 where col1=‘xxx’;

要注意使用复合索引需要满足最左侧索引的原则,也就是查询的时候如果where条件里面没有最左边的一到多列,索引就不会起作用.

 

查询优化器执行计划：

客户端发送sql给服务器查询，服务器先检查是否命中缓存，有就返回，没有就对sql进行解析，预处理再由优化器生成执行计划，根据执行计划调用存储引擎API执行查询，然后返回结果给客户端。

union：它是先将一个查询结果集放到临时表中，等待全部完成就再读取临时表信息，一起返回客户端。

关联表：最多只需要在第二张表字段创建索引，避免无谓的索引创建影响性能。

group by 不能使用索引优化时，可以考虑使用临时表方式。

 

 

数据库结构设计：

  需求分析（根据需求把需要的表，字段，扩展字段定义好）

  逻辑设计（先把表内容符合数据库第三范式，再根据现实需求select的sql联表查询优化进行反第三范式设计适当添加冗余字段）

  概念设计（可以采用uml的powerdesinger对表字段类型进行设计。

 

影响mysql性能？

1.服务器的硬件，系统。（cpu大小频率，内存，硬盘io，系统参数优化有关）

2.选择正确的数据库引擎【myisam适用于大量查询，操作时进行表锁，不支持事物；innodb支持事物，行锁，大量.并发操作执行效果好。】

3.数据库设计是主要因素：

1.数据库结构设计优化：存储类型越低，它占用更少磁盘、内存、cpu缓存，cpu处理上效率越好；整型比字符操作效率高，因为字符集和排序规则使得字符比整型更复杂。根据现实需求select的sql联表查询优化进行反第三范式设计适当添加冗余字段。

2.数据库索引优化：(create index 索引名 on 表名(需要索引字段))可以在where,order by之后添加索引大大减少扫描数据量;帮助mysql服务器避免排序和临时表（b+tree存储索引值并且是顺序排序的）;将随机i/o变为顺序i/o（b+tree查询速度更快）使查询更快。

3.sql数据库优化：

当只要一行数据时使用 limit 1

用 not exists 代替 not in

对操作符的优化，尽量不采用不利于索引的操作符 对于一些如in、not in、 is null、is not null (null字判断)、<>、like、统计数据、表达式进行索引字段计算、联合索引只使用其中一个索引字段，使存储引擎放弃使用索引。避免重复和沉余索引，如唯一索引，主键索引等。

可以使用left join 再null值判断，统计字段单独写成一张表，定时执行之前数+当天数。

影响查询响应时间：服务时间（执行查询真正时间）和排队时间（等待I/O操作和等待锁），还可能存储引擎锁高并发竞争，硬件配置。大多情况下，一条sql复制语句比执行多次sql语句要好，减少服务器连接和断开消耗性能和网络速度宽带消耗。但有写特殊情况，有写定时删除大量数据，可能会阻塞服务器的一些比较重要sql执行，所以切分删除，暂停一下再删除其他部分，降低对服务器的影响和减少锁的持有时间。

根据sql查询语句执行顺序以小表驱动大表优化内循环的次数。

尽量可能使用关联查询代替子查询，因为查询优化器未必按你想的执行顺序，可以先执行全表扫描如in中子条件查询。

union会为表添加distinct去除重复行，除非必要，否则使用union all方式

对于limit页面表数据量大，当偏移值较大。当知道需要查询位置和有索引时，使用between and进行查询字段索引查询。没有时，使用延迟关联，利用覆盖索引使用子查询先去查询获取需要记录然后根据主键关联原表数据查询，这样索引就包含查询的字段值使得速度更快。

索引会降低表添加更改删除的速度，如对表进行这些等操作时，MySQL额外还需要保存一下索引文件更新的信息。

这类不适合创建索引：表数据少、索引字段相同值较多、频繁更新的字段、不能使用到索引的字段。

SQL Select 语句完整的执行顺序:
1、from 子句组装来自不同数据源的数据;
2、where 子句基于指定的条件对记录行进行筛选;
3、group by 子句将数据划分为多个分组;
4、使用聚集函数进行计算;
5、使用 having 子句筛选分组;
6、计算所有的表达式;
7、select 的字段;
8、使用 order by 对结果集进行排序。
SQL 语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中，代码按编码顺序被处

理。但在 SQL 语句中，第一个被处理的子句式 FROM，而不是第一出现的 SELECT。SQL 查询处理的步骤序号: (1) FROM <left_table>
(2) <join_type> JOIN <right_table>
(3) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list>

(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(8) SELECT
(9) DISTINCT
(9) ORDER BY <order_by_list>
(10) <TOP_specification> <select_list>

以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应 用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在查询中指定某一个子句， 将跳过相应的步骤。
逻辑查询处理阶段简介:

1、 FROM:对 FROM 子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表 VT1。
2、 ON:对 VT1 应用 ON 筛选器，只有那些使为真才被插入到 TV2。
3、 OUTER (JOIN):如果指定了 OUTER JOIN(相对于 CROSS JOIN 或 INNER JOIN)，保留表中未找到

匹配的行将作为外部行添加到 VT2，生成 TV3。如果 FROM 子句包含两个以上的表，则对上一个联接生成的 结果表和下一个表重复执行步骤 1 到步骤 3，直到处理完所有的表位置。

4、 WHERE:对 TV3 应用 WHERE 筛选器，只有使为 true 的行才插入 TV4。
5、 GROUP BY:按 GROUP BY 子句中的列列表对 TV4 中的行进行分组，生成 TV5。 6、 CUTE|ROLLUP:把超组插入 VT5，生成 VT6。
7、 HAVING:对 VT6 应用 HAVING 筛选器，只有使为 true 的组插入到 VT7。
8、 SELECT:处理 SELECT 列表，产生 VT8。
9、 DISTINCT:将重复的行从 VT8 中删除，产品 VT9。

10、 ORDER BY:将 VT9 中的行按 ORDER BY 子句中的列列表顺序，生成一个游标(VC10)。 11、 TOP:从 VC10 的开始处选择指定数量或比例的行，生成表 TV11，并返回给调用者。 where 子句中的条件书写顺序。

 

 

EXPLAIN简介

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 如：EXPLAIN SELECT * FROM 表名

执行计划各字段含义如下：

id的结果共有3中情况

 1. id相同，执行顺序由上至下 。

 2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

 3. id相同不同，同时存在，即执行顺序由上至下，到相同id时最大值先执行。

select_type 用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

1.SIMPLE 简单的select查询，查询中不包含子查询或者UNION

2.PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

3.SUBQUERY 在SELECT或WHERE列表中包含了子查询
4.DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
5.UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
6.UNION RESULT 从UNION表获取结果的SELECT

table指的就是当前执行的表

type所显示的是查询使用了哪种类型

从最好到最差依次是：system > const > eq_ref > ref > range > index > all

一般来说，得保证查询至少达到range级别，最好能达到ref。

1.system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
2.const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。 

首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的相当于只查询一条记录，所以type为system。
3.eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
4.ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。 

5.range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。 

6.index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的） 

id是主键，所以存在主键索引
7.all Full Table Scan 将遍历全表以找到匹配的行 

possible_keys 和 key

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效） 

查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref

显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好 （索引优化）

Extra

包含不适合在其他列中显式但十分重要的额外信息

1.Using filesort（可能需要在order by 字段中加索引） 
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。 

2. Using temporary（需要在字段中加索引）   
使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。 

3.Using index（执行效率可以）
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。 

4.Using where
表明使用了where过滤

5.Using join buffer（表连接要以小表驱动大表方式进行）
表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

6.impossible where
where子句的值总是false，不能用来获取任何元组  如：SELECT * FROM t_user WHERE id = '1' and id = '2'

7.select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8.distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作