Mysql 总结

1.基本操作

1.1 登陆创建：

mysql -u root -p 666666

show databases;

use blog;

show tables;

show columns from article; # 显示列名等信息

describe article; # 查询表结构

show index from article;

create database mydb;

drop database mydb;

create table mytb;

drop table mytb;

alter table mytb # 修改表

　　add [column] create_definition [first | after column_name]　　//添加新字段

　　add primary key （index_col_name,...）　　//添加主码名称

       add primary key(id, title); 

　　alter [column] col_name {set default literal |rop default}　　//修改字段名称

　　change [column] old_col_name create_definition　　//修改字段名及类型

　　modify [column] create_definition　　//修改字段类型

　　drop [column] col_name　　//删除字段

　　drop primary key　　//删除主码

　　rename [as] new_tablename　　//更改表名

create index idx on article(title)

1.2 增删查改：

insert into 表名 values（值1，值2，...）（自增长的列应写null）

insert into 表名（字段1，字段2，...） values （值1，值2，...）

insert into 表名 set 字段1=值1，字段2=值2，...

update 表名 set 字段1=值1 where 查询条件 ;# 若无查询条件，表中所有数据行都会被修改。

delete from 表名 where 查询条件; #若无查询条件，表中所有数据行都会被删除。

select * from 表名；

select * from 表名 limit[start] length # 查询，limit限制查询返回行数，start:表示从第几行记录开始输出，0表示第1行

select id from student

    where age > 18 # where不能跟聚合函数

    group by class

    having age > 18 # 与group by连用，用于筛选组

    ordering by DESC #默认ASC升序

    limit 0, 5

1.3 where 或者 having 的条件:

= 检查是否相等

！= 检查是否不等

< (或<=) 检查左边值是否小于(或小于等于)右边值

> (或>=) 检查左边值是否大于(或大于等于)右边值

[not] between 检查左边值是否在某个范围内

[not] in 检查左边是否某个特定集的成员

[not] like '_小明%' # _表示任意一个字符，%表示任意字符串

is [not] null 检查左边是否为空值

AND，OR，NOT

1.4 聚合函数：

sum (exepression) 计算表达式的和

avg (exepression) 计算表达式的平均值

count (exepression) 对表达式进行简单的计数

count (*) 统计记录数

max (exepression) 求最大值

min (exepression) 求最小值

GROUP_CONCAT(score) # 将score按照分组放在一行，用逗号隔开 select name, GROUP_CONTCAT(score) from stu group by name;

1.5 关键字：

DISTINCT # 去重 select distinct name from article;

LIMIT 5 #返回指定范围的数据，limit 5表示默认从0开始返回后面的五行，limit 5,5 表示从5开始往后的5行

order by DESC|ASC|列名1, 列名2 # 按升序降序或者某一列进行排序

and | or

in | not in

like

update # 更新表格里某个值，alter是更新表格信息，update article set author_name="xixi" where title_name = "hahah"

union 连接操作，合并多个select的结果集select name from article union select name from author，会默认去重，使用union all就不去重

1.6 连接及优化：

1.7 基本操作

SHOW VARIABLES LIKE '%partition%'; # 是否支持分区

show plugins; # 是否支持分区partition是active的

2.常识

2.1 mysql锁， 死锁问题

2.2 为什么要分区，分表，以及如何

为什么分表和分区？

当一张表的数据十分庞大时，比如有几百万数据，查询一次的时间会变多，插入的时间也会变长，性能低，如果有联合查询，性能更低。分表和分区的目的就是减少数据库负担，提高数据库效率。

适用于高并发情况下，分表和分区可以相互配合，访问量大且表数据多的表，可以采用分表+分区，访问量小且表数据多的表，可以使用分区等。分表操作需要手动创建子表，应用需要计算子表名(采用merge可以避免，但也要创建子表和配置子表间的union关系)

什么是分表？

将一个大表按照一定的规则分解成多张独立存储空间的实体表，每个表都有三个文件.MYD.MYI.frm，这些子表可以分布在同一块磁盘上，也可以在不同的机器上，读写的时候按照实现定义好的规则得到子表操作它。

什么是分区？

分区也是将大表分解，但是分区是将数据分段划分在多个位置存放。分区后，表面上还是一张表，但是数据散列到多个位置。应用读写的时候操作大表的名字，db自动去组织分区数据。减少特定sql的响应和读取时间。

分表的具体作用？

在查询上比较快。

分区具体的作用？

因为分区是让数据分段存储，比如十年的数据，每一年存在一个表里，当查询的时候，想查某一年的，只需要扫描一张表，而不是全部，减少查询的范围增加查询速度；当需要清理数据时，只需要删除这一个表就可以了，而不是从整个的表中一个个删掉，这样效率低且表锁影响其他对数据库操作。

分表和分区的区别？

分表是真正的划分成很多表，每一个小表都是一个完整的表，对应三个文件

分区后还是一张表，不会变成两个表，但是存放数据的块变多了(.idb?)

分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。

分区，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表，数据处理还是由自己来完成。

分表后，单表的并发能力提高了，磁盘 I/O 性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。

mysql 提出了分区的概念，主要是想突破磁盘 I/O 瓶颈，想提高磁盘的读写能力，来增加mysql 性能。在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高 mysql 并发能力上;而分区呢，如何突破磁盘的读写能力，从而达到提高 mysql 性能的目的。

分表的方法有很多，用 merge 来分表，是最简单的一种方式。这种方式跟分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。

分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。

分表的方式？

水平切分：将行进行切分，使用hash，取模等方式进行拆分，user分成4个user_i，id取模存入和读取。

垂直切分：按列切分，将不常用的字段单独放在一张表，将textblob大字段查分出放在附表，经常查询的组合放在一张表（一般在建表时，就要执行切分，查询时用join连接）

1. mysql集群

它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写压力。从而提升数据库性能。比如10个sql队列，在5个集群中排队，每个队列只有2个sql，所以等待时间是缩短了，但是单表的数据量每边，查询一次所用的时间不变。

2. 预先估计会出现大数据量并且访问频繁的表，将其分为若干个表

使用一定的算法规则如：

Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key 进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

Key （键值）-上面Hash模式的一种延伸，这里的Hash Key 是MySQL系统产生的。

List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。

Composite（复合模式） –以上模式的组合使用　

将user表实现拆分成多个表user_00...user_99，存入数据时，根据算法规则找到那张表进行存取。

所以在应用里需要写上，比如按range时间划分，需要加上判断，if year <1999,then tablename=user_99，然后对这种表进行增查删改。

3. 利用merge引擎实现分表

因为分表需要改代码(因为程序里sql已经写好了)，所以把已经有大量数据的表分开可以采用merge实现分表。merge分表不需要实现划分，他分成子表和merge主表，主表相当于一个壳子，逻辑上封装了子表，实际的数据存储在子表中。可以对主表进行增查删改，如果知道子表也可以对子表，效果一样，但是对主表查询是全部扫描，体现不出分表的好处了，所以还是查子表好。

使用方法：

创建普通子表，分别，创建主表声明为merge引擎，去union所有子表。必须保证所有表结构一样。

原数据表member，希望拆分成user_0，user_1，按规则导入数据，主表为user(可以删掉原数据表，并原数据表重名)，按规则查找子表，或直接搜主表(但是搜主表就相当于全文搜了，分表的意义不大)。

原数据表

mysql> CREATE TABLE IF NOT EXISTS `member` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
 ->   `name` varchar(50) DEFAULT NULL,  
 ->   `sex` int(1) NOT NULL DEFAULT '0',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
Query OK, 0 rows affected (0.05 sec)  

加入数据insert into member...

创建子表user_0, user_1和merge主表：

mysql> CREATE TABLE IF NOT EXISTS `user_0` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
 ->   `name` varchar(50) DEFAULT NULL,  
 ->   `sex` int(1) NOT NULL DEFAULT '0',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
Query OK, 0 rows affected (0.05 sec)  

create table user_1 like user_0;

mysql> CREATE TABLE IF NOT EXISTS `user` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
 ->   `name` varchar(50) DEFAULT NULL,  
 ->   `sex` int(1) NOT NULL DEFAULT '0',  
 ->   INDEX(id)  
 -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;  
Query OK, 0 rows affected, 1 warning (0.00 sec)  

member数据分到两个子表

insert into user_0 (id, name, sex) select id, name, sex from member where id %2 = 0;
insert into user_0 (id, name, sex) select id, name, sex from member where id %2 = 1;

这样可以select 两个子表进行查询，也能通过select主表查询。
分区的方式？

水平分区：每个列在每个数据集都能找到，按行切分，比如十个分区，一个分区包含一年的记录。

垂直分区：减少列宽度，把不经常用的，或者较大的放在单独分区，在保证相关性的情况下还能提高访问速度

五种方式：

range：对一个给定的连续区间的列值分配，使用values less than。下面指定条件都必须是整形

create table range(

　　id int (11),

　　money int (11) unsigned not null ,

　　 date datetime

　　)partition by range( year ( date ))(

　　partition p2007 values less than (2008),

　　partition p2008 values less than (2009),

　　partition p2009 values less than (2010),

　　partition p2010 values less than maxvalue

)；

list：同上，但是存的是离散值，如离散的b值；这里没有maxvalue所有的值都必须找到，否则插入失败

create table list(

　　a int (11),

　　b int (11)

　　)(partition by list (b)

　　partition p0 values in (1,3,5,7,9),

　　partition p1 values in (2,4,6,8,0)

　);

hash：将指定的列值，均匀的映射到不同分区,mysql自动划分出谁在哪个分区

create table hash(

　　a int (11),

　　b datetime

　　)partition by hash ( YEAR (b))

　　partitions 4;

key：hash是用户自定义的函数分区，key是mysql数据库提供的函数进行分区。NDB cluster 使用 MD5 函数来分区，对于其他存储引擎，mysql 使用内部的 hash 函数。

create table t_key(

　　a int (11),

　　b datetime)

　　partition by key (b)

　　partitions 4;

columns分区：对非整形进行分区

mysql-5.5 开始支持 COLUMNS 分区，可视为 RANGE 和 LIST 分区的进化，COLUMNS 分区可以直接使用非整形数据进行分区。COLUMNS 分区支持以下数据类型：所有整形，如 INT SMALLINT TINYINT BIGINT。FLOAT 和 DECIMAL 则不支持。日期类型，如 DATE 和 DATETIME。其余日期类型不支持。字符串类型，如 CHAR、VARCHAR、BINARY 和 VARBINARY。BLOB 和 TEXT 类型不支持。COLUMNS 可以使用多个列进行分区。

过程：如range方式，创建分区表如何分区，主表插入，自动划分，插入一些数据会保存在不同的分区文件里，之后可以对分区操作查询了。

分区管理：

查询分区：select * from mydb.user partition(p201010);

新增分区：ALTER TABLE sale_dat ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

删除分区：ALTER TABLE sale_data DROP PARTITION p201010; 删除分区及所有数据

合并分区：查看/var/lib/mysql里合并结果

ALTER TABLE sale_data

REORGANIZE PARTITION p201001,p201002,p201003,

p201004,p201005,p201006,

p201007,p201008,p201009 INTO

(

PARTITION p2010Q1 VALUES LESS THAN ( 201004 ),

PARTITION p2010Q2 VALUES LESS THAN ( 201007 ),

PARTITION p2010Q3 VALUES LESS THAN ( 201010 )

);

http://www.kokojia.com/article/24132.html

2.3 char，varchar，text区别
2.4 join哪几种，左连接右连接等区别
2.5 索引

索引类型，BTree，hash索引区别，以及在磁盘结构上区别

2.6 查询命令优化
2.7 事务
2.8 范式

2.9 引擎

数据库的存储引擎是什么？

数据库中的表是储存和组织信息的数据结构，表存数据，但是有的表简单，有的复杂，有的表不用来长期存储，有的表你希望他读取快，有的表你希望他插入写入快...总之实际开发中你可能希望这些表有不同的性能支持你的需要，这就意味着这些表需要用不同的技术存储，有不同的数据处理的方式，不同的存储机制，索引技巧，以及提供的不同的功能。这些就是不同的存储引擎能提供的，能让我们最大限度的利用mysql，开发出高性能的软件。所以存储引擎，其实就是使用该引擎对表进行了某种设置，使得表在数据的存储方式，数据更新方式，查询性能以及索引方面提供不同的效果。

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。
而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

怎么查看，使用？

show engines; #查看mysql支持的引擎

show variables like '%storage_engine%' #查看默认使用的引擎    

show global variables like '%datadir%'; # 查看数据库在磁盘中存储的位置

show variable like '%innodb_data%'

show variables like 'innodb_file_per_table%'; # 显示on表示Innodb文件单独存储

set global innodb_file_per_table=1;  # 如果上面显示off，可通过这一句改为on

show  create table article; # 查看创建数据库表的时候使用的engine，最后一行

Alter table tableName engine =engineName #修改上面查询到的值

修改独占空表空间配置，配置以下参数（或者在conf文件中修改）

innodb_data_home_dir = "/user/local/mysql/var"  数据库文件所存放的目录

innodb_log_group_home_dir = "/user/local/mysql/var" 日志存放目录

innodb_data_file_path=ibdata1:10M:autoextend  设置配置一个可扩展大小的尺寸为10MB的单独文件（共享数据文件），名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。

innodb_file_per_table=1  是否使用共享还是独占表空间 (1:为使用独占表空间,0:为使用共享表空间)

种类：

ISAM：读取速度快，且不占用大量内存和存储资源；但是不支持事务处理，不支持外键，不支持索引，不能容错。mysql5.1以上废弃

MyISAM：基于ISAM

HEAP（也称为MEMORY）、

CSV、

BLACKHOLE、

ARCHIVEPERFORMANCE_SCHEMA、

InnoDB、

Berkeley、

Merge：和myisam组合使用，用于分表。主表(Merge表)中实际并没有数据，数据都存在子表中，对主表的操作实际上都是对子表的操作

Federated

Cluster/NDB

2.3 Innodb和myisam：

（数据结构都是B+树）

Innodb：（因为支持事务和故障恢复，所以处理大数据容量的数据库系统，mysql启动时，Innodb会在内存建立缓冲池，用于缓冲数据和索引）

提供了对数据库ACID事务的支持(所以对事务的操作，该引擎是首选)

提供了行级锁和外键的约束(锁的粒度小，写操作不会锁定全表，所以并发下能提高效率)

可以利用数据日志来进行数据的恢复

不支持全文搜索

启动较慢

不保存表的行数(count时，需要扫描全表获得)

myisam：（表的读操作多于写操作，且不需要事务支持的时候，myisam是首选）

保存了行数(select count(*)的时候，不扫描全表，直接读取已保存的值)

不支持事务

不支持行级锁和外键(因此插入更新时，会锁定全表，导致效率低)

区别：

1.使用场景

select多用myisam：

insert和update多用innodb，且需要事务ACID等

因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。

如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了

2.使用的数据结构都是B+树：

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

(B+树：为了文件系统和数据库系统使用的数据结构，能高效的磁盘存取，读写代价低，效率更稳定，利于数据库扫描)

3.文件存储方式：

myisam存储成三个文件：frm文件存储表结构(就是每个表的元数据meta信息)，MYD文件是数据文件，MYI是索引文件(索引和数据是分开放的)

innodb存储成：frm文件存表结构，表空间文件，日志文件

4.myisam不支持事务，innodb支持事务

5.myisam支持表锁，innodb支持行锁也支持表锁

 表级锁：直接锁定整张表，在锁定期间，其他进程无法对该表进行写操作，如果设置的是写锁，那么其他进程读也不允许，因此myisam支持的并发量低，但myisam不会出现死锁；
 行级锁：只对指定的行进行锁定，其他进程还是可以对表中的其他行进行操作的。因此行锁能大大的减少数据库操作的冲突，但有时会导致死锁。

6.myisam不支持外键，innodb支持外键

7.myisam内置计数器可直接返回行数，innodb返回行数需要扫描全表
8.myisam表格可以被压缩，innodb很难？

9.myisam很难跨平台使用，innodb支持直接快平台拷贝使用？？？

10.myisam支持全文索引，innodb不支持

综合分析：

myisam：针对读select操作多推荐使用，而且索引和数据是分开的，且索引是有压缩的，所以提高内存使用率，增加效率，而innodb是索引和数据紧密捆绑的，没有使用压缩从而会使innodb的体积庞大一些；select count(*)语句使用很频繁；innodb的行级锁只有在where主键的时候有效，非主键还是会锁全局表的；

2.10 Innodb文件存储：

表结构(表定义)文件frm：在数据库建立一张表就会生成一个.frm文件，保存表的元数据信息，包括表结构定义，与存储引擎无关，任何引擎都会有这个文件。

独占表空间文件idb(一个表占用一个表空间)：系统为每个表单独生成一个idb文件，此文件就存这一个与该表相关的数据，索引，表的内部数据字典等信息，但是每个表对应的撤销（undo）信息，系统事务信息，二次写缓冲等还是存放在了原来的共享表空间内（ibdata1文件）。[

• 回滚段
• 所有innodb表元数据信息（这就是为什么innodb无法像myisam表一样，直接将表定义文件  表名.frm 和表数据文件 表名.ibd 拷贝到 另一个库中，因为还有部分元数据信息在ibdata1文件中）
• double write，insert buffer dump 等等

]

优点：
1．每个表都有自已独立的表空间。
2．每个表的数据和索引都会存在自已的表空间中。
3．可以实现单表在不同的数据库中移动。
4．空间可以回收 

对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

a)Drop table操作自动回收表空间

b）如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

c) 对于使innodb-plugin的Innodb使用truncate table也会使空间收缩。 

共享表(系统)空间文件ibdata1(所有表共用一个表空间)：ibdata1是默认的，初始化为10m，可扩展。使用这种方式时，Innodb的所有数据保存在一个单独的表空间里面，这个表空间可以由多个文件组成，一个表可以跨多个文件存在，所以一个表的限制不再是文件大小的限制了，而是官方给出的64TB的表限制。

优点：可以放表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。数据和文件放在一起方便管理。

缺点：
所有的数据和索引存放到一个文件中，则将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日志系统这类应用最不适合用共享表空间。

日志文件ib_logfile0/1：

(独占和共享都是数据的存储方式，)

innodb的表空间结构：

tablespace-segment-extent-page-row

一个表空间是由分散的段组成，段包括数据段(即B+数的叶节点)，索引段(即B+树的非叶子结点)和回滚段

一个段是由多个区组成

一个区是由64个连续的页组成，一个页大小是16k，所以一个分区大小是1M，创建新表时，先使用32页大小的碎片页存放数据，使用完后才是区的申请（InnoDB最多每次申请4个区，保证数据的顺序性能）页类型有：数据页、Undo页、系统页、事务数据页、插入缓冲位图页、以及插入缓冲空闲列表页。

页是innodb磁盘管理最小的单位，一个b树的节点就是一个页？？B+节点树的叶子节点前后生成双向链表

数据是按行存放的

innodb的特性：

插入缓冲，两次写，自适应hash引擎

2.11 

读写分离，集群

存储过程