数据库分类

列式数据库	按列存储，方便查找，但是随着内存和SSD压缩列所占百分比的提高，它也可以用来事务处理和数据更新。适合大量数据。 优点： 1.极高的装载速度 (最高可以等于所有硬盘IO 的总和） 2.适合大量的数据而不是小数据 3.高效的压缩率，不仅节省储存空间也节省计算内存和CPU. 4.非常适合做聚合操作 缺点： 1.不适合随机更新 2.不适合含有删除和更新的实时操作
行式数据库	按行存储，便于事务处理

安装

下载

https://downloads.mysql.com/archives/community/

选择你需要的版本，下载源码，编译安装

安装

centos7 源码编译安装 mysql5.7

存储引擎对比

MyISAM	InnoDB	NDBCluster
特点：
1.不支持事务 2.表级锁定，限制了并发 3.读写互相阻塞(即读取时阻塞写，写时阻塞读) 4.只会用key_buffer缓存索引	1.支持4个事务隔离级别 2.行级锁定 3.读写阻塞与事务隔离级别相关 4.高效缓存，整个表和主键以cluster方式存储，组成一颗平衡树	1.分布式存储引擎，存储整体中的部分数据 2.支持事务 3.内存需求巨大
适用场合：
1.不需要支持事务 2.并发相对较低 3.数据修改较少 4.以读为主 5.数据一致性要求不是非常高 对于相对静态的数据，利用query cache可以极大提高访问效率	1.事务支持 2.高并发支持，但注意查询用索引 3.数据更新较为频繁 4.数据一致性要求高 避免全表扫描 大数据插入自己控制事务 避免主键更新	1.非常高并发需求 2.对单个请求响应不严格 3.查询简单，过滤条件较为固定

服务操作

服务开启、关闭、重启、查看状态

Mysql.server start/restart/stop/status

Mysql.server 文件安装时在安装目录下可以找到

查看Mysql服务

ps -ef |grep mysql

连接

连接

安装后，第一次连接不需要密码，直接回车就进去了。

mysql -u root -p 输入之后回车，再回车。设置密码之后第一次回车之后输入密码即可。

进入指定库：

mysql -u root -p dbname

退出

exit 、quit

帮助命令

进入命令行输入help或者? + command即可查看对应语法。

举例：

mysql> help select;

或者

mysql> ? select;

用户管理

用户

添加

命令格式:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

host一般为%代替匹配任何字符

重命名

rename USER user to user1

删除用户

drop user user1

密码

设置密码

1. 命令行设置

   mysqladmin -u root password "123456" 这个命令在mysql安装目录的bin目录下，最好将这个目录设置到环境变量path中

2. 进入mysql设置

   use mysql; 说明使用那个数据库

   root用户：

   SET PASSWORD = PASSWORd('biscuit');

   指定用户：

   SET PASSWORD FOR 'jeffrey'@'%' = PASSWORd('biscuit');

修改密码

更新已有账户密码：

UPDATE user SET Password = PASSWORd('bagel') WHERe Host = '%' AND User = 'francis';

权限

授权

命令格式：

GRANT ALL PRIVILEGES ON *.*(库.表) TO 'backlion'(用户)@'%'(地址,%为匹配符) IDENTIFIED BY 'backlion123'(密码) WITH GRANT OPTION; 括号里面是说明，实际不写括号

被授权用户不能再授权，如果想，则需要在授权命令末尾加上 WITH GRANT OPTION

查看指定用户权限

show grants for user1

撤销权限

撤销某个表的部分权限：

REVOKE all privilege(select,update,delete,insert) ON dbname.tablename FROM 'username'@'host';

撤销所有权限：

revoke ALL PRIVILEGES on *.* from user1

重新载入授权表

FLUSH PRIVILEGES

数据库操作

常用命令

–	–
查看数据库状态	mysql> status;
查看编码	mysql> show variables like 'char%';
修改编码	alter database db_name CHARACTER SET utf8;
显示数据库当前状态	show status;
显示线程表	show processlist;
查看mysql提供引擎	show engines;
看当前默认的存储引擎	show variables like '%storage_engine%';
修改表引擎	alter table table_name engine=innodb;
查询当前sql模式	SELECt @@sql_mode
杀死对应线程id	KILL thread_id;

库

创建库

create database dbname default character set utf8mb4 collate utf8_general_ci;

使用库

use dbname;

修改库

重命名：

rename database oldname to newname;

修改字符集：

alter database character set utf8mb4;

删除库

drop database IF EXISTS dbname;

查看库

显示所有库：

show databases;

查看创建库的sql语句：

show create database test;

表

列类型

查看官网的数据库表列类型

创建表

CREATE TABLE  IF NOT EXISTS `person` (
  `number` int(11) DEFAULT NULL AUTO_INCREMENT COMMENT '机器ID',
  `name` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL
	PRIMARY KEY (number),--------主键
	UNIQUE KEY  `unique_fname_lname` (`fisrtname`,`lastname`),
	KEY `fname_lname_age`  (`firstname`,`lastname`,`age`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

创建临时表：

CREATE TEMPORARY TABLE table_name();

举例：

create temporary table student_copy(
　　id int primary key,
　　name varchar(20) 
)Engine=InnoDB default charset utf8;

临时表只对当前连接有效。

修改表

修改表的引擎：

alter table tableName engine=innodb;

修改表的字符集：

alter table tableName convert to character set utf8mb4;

移动表到其他数据库：

rename TABLE current_db.tbl_name TO other_db.tbl_name;

重命名表：

rename table 原表名 to 新表名;

修改表字段：

增：

alter table table1 add transactor varchar(10) not Null;

删：

alter table table_name drop column 字段名;

改：

alter table 表名称 change 旧名称 新名称 字段类型 [是否允许非空]; #修改字段名

alter table 表名称 modify 字段名 字段类型 [是否允许非空]; #修改字段其他属性

删除表

删除指定表：

drop table table_name；

删除数据库中的所有表：

select concat('drop table ',table_name,';') from TABLES where table_schema='dbname';

查看表

show tables;

显示所有列信息：

desc tableName;

删除表中的数据

delete from table1; 范围删除，之后插入id不会从1开始

truncate table table1; 清空表，索引从1开始

SELECt

语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [MAX_STATEMENT_TIME = N]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERe where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVINg where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILe 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILe 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATe | LOCK IN SHARE MODE]]

单表查询

举例：

where:

select id, tag_name from test where id = 22

GROUP BY:

HAVINg子句可以引用总计函数，而WHERe子句不能引用

SELECt user, MAX(salary) FROM users GROUP BY user HAVINg MAX(salary)>10;

LIMIT分页:

SELECt * FROM tbl LIMIT 5,10; # Retrieve rows 6-15; 从索引5开始找10个数据

INTO OUTFILe查询结果写入服务器文件:

SELECt a,b,a+b INTO OUTFILe '/tmp/result.text' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM test_table;

UNIOn：将多个查询结果放到一个结果集中

SELECt ...
UNIOn [ALL | DISTINCT]
SELECT ...
[UNIOn [ALL | DISTINCT]
SELECT ...]

ORDER BY排序：

ASC (升序，默认)

DESC (降序)

多表查询

join

• 内连接：inner join，笛卡尔积匹配；
• 左连接：left join，右表匹配左表
• 右连接：right join，左表匹配右表

举例：

SELECT * from facts t1 LEFT JOIN oper_log t2 ON t1.id=t2.id;

一般不会三个不同连接一起使用

INSERT

语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATe
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

举例

批量插入

INSERT INTO oper_log(oper_name, oper_detail) VALUES("admin1", "insert data"),("admin2", "insert data");

插入时存在更新

这种情况必须存在主键或者唯一索引，否则直接插入

insert into record_detail (logid, detail) VALUES (2,'bob2'),(3,'bob3') on DUPLICATE KEY UPDATE detail = VALUES(detail);

或者：

1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_name=value, ...

使用查询结果插入

insert into tablename (id, col_name) select id, col_name from t_table where 1=1;

UPDATe

语法

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERe where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERe where_condition]

举例

多表联合更新：

UPDATE record_detail t1 LEFT JOIN oper_log t2 ON t1.logid=t2.id SET t1.detail = t2.oper_detail WHERe t2.oper_name = '用户2';

一般不建议使用多表关联更新

单表更新：

UPDATE record_detail SET detail = 'update data' WHERe id = 1;

DELETE

语法

Single-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name,...)]
    [WHERe where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
    
Multiple-Table Syntax

DELETe [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERe where_condition]

Or:

DELETe [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERe where_condition]

举例

单表删除

delete from record_detail WHERe id = 1;

多表删除

delete t1, t2 from record_detail t1 LEFT JOIN oper_log t2 on t1.logid=t2.id WHERe t2.id = 6;

函数

类型转换

SELECt CAST(logid as char) FROM record_detail;

SELECt CONVERT(logid, char) FROM record_detail;

可转换的类型:

BINARY[(N)]

CHAr[(N)]

DATE

DATETIME

DECIMAL

SIGNED [INTEGER]

TIME

UNSIGNED [INTEGER]

数学运算

mysql> select abs(-8); ##绝对值
±--------+
| abs(-8) |
±--------+
| 8 |
±--------+

mysql>select ceiling(9.4); ##向上取整
±-------------+
| ceiling(9.4) |
±-------------+
| 10 |
±-------------+

mysql> select floor(3.4); ##向下取整
±-----------+
| floor(3.4) |
±-----------+
| 3 |
±-----------+

mysql> select rand(); ##返回0–1的随机数
±-------------------+
| rand() |
±-------------------+
| 0.7959931481592293 |
±-------------------+

字符串函数

mysql> select char_length('length'); ##计算字符串长度
±----------------------+
| char_length(‘length’) |
±----------------------+
| 6 |
±----------------------+

mysql> select concat('aaa','bbb','ccc'); ##拼接字符串
±--------------------------+
| concat(‘aaa’,‘bbb’,‘ccc’) |
±--------------------------+
| aaabbbccc |
±--------------------------+

mysql> selectinsert('abcdefghijk', 2,4,'xxxxxxxx'); ##具体位置插入字符串替代几个字符，也可作为替换使用。说明：从第2个位置开始的4个字符删除，用后面的字符串替换。
±--------------------------------------+
| insert(‘abcdefghijk’, 2,4,‘xxxxxxxx’) |
±--------------------------------------+
| axxxxxxxxfghijk |
±--------------------------------------+

mysql>select lower('ABCDEF'); ##大写转小写 upper():小写转大写
±----------------+
| lower(‘ABCDEF’) |
±----------------+
| abcdef |
±----------------+

mysql> select instr('abcdefgh', 'g'); ##查找第一个出现的位置
±-----------------------+
| instr(‘abcdefgh’, ‘g’) |
±-----------------------+
| 7 |
±-----------------------+

mysql> select substr('abcdefghi',3,2); ##获取从某个位置开始长度为n的字串
±------------------------+
| substr(‘abcdefghi’,3,2) |
±------------------------+
| cd |
±------------------------+

mysql> select replace('abcdefghijk','j','xx'); ##字符串替换
±--------------------------------+
| replace(‘abcdefghijk’,‘j’,‘xx’) |
±--------------------------------+
| abcdefghixxk |
±--------------------------------+

时间函数

时间获取

mysql> select curdate(); ##当前日期
±-----------+
| curdate() |
±-----------+
| 2020-04-26 |
±-----------+

mysql> select now(); ##当前时间
±--------------------+
| now() |
±--------------------+
| 2020-04-26 09:14:12 |
±--------------------+

mysql> select year(NOW()); ##获取当前时间的年，month，day，hour，minute，second
±------------+
| year(NOW()) |
±------------+
| 2020 |
±------------+

日期和天数转换

SELECT TO_DAYS(NOW()); -- 737908
SELECT FROM_DAYS(737908); -- 2020-04-28

时间和秒数转换

SELECt TIME_TO_SEC(NOW()); -- 56744
SELECT SEC_TO_TIME(56609); -- 15:43:29

时间拼凑

SELECT MAKETIME(2,3,5); -- 02:03:05
SELECT MAKEDATE(2021,300); -- 2021-10-27

日期计算

– HOUR,MINUTE,SECOND,WEEK,MONTH,YEAR
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 加一天
SELECT DATE_SUB(NOW(), INTERVAL 1 QUARTER); -- 减三个月

时间差

SELECT DATEDIFF('2008-12-30','2008-12-29'); --1

SELECT TIMEDIFF('15:45:44','15:47:44'); -- 00:02:00

时区转换

select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00

聚合函数

函数	说明
count()	计算个数,count(字段)：会忽略所有的null；count(*)与count(1)区别：1只查一列，二者不会忽略null列
sum()	求和
avg()	平均分
max()	最高分
min()	最低分

数据库级别MD5加密

mysql>select md5('22222'); ##加密
±---------------------------------+
| md5(‘22222’) |
±---------------------------------+
| 3d2172418ce305c7d16d4b05597c6a59 |
±---------------------------------+

事务

原则

原子性(A)

一致性©

隔离性(I)

脏读

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

幻读

系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A修改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

总结

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

持久性(D)

#设置是否自动提交事务，默认自动提交
#关闭自动提交
set autocommit = 0;  

#开启事务
start TRANSACTION

#操作
insert
insert 

#提交
COMMIT
#回滚
ROLLBACK
#开启自动提交
set autocommit = 1; 

索引

快速获取数据库中的数据

分类

• 主键索引(PRIMARY KEY)

  不可重复

• 唯一索引(UNIQUE KEY)

  避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

• 常规索引(KEY/INDEX)

  默认的，index和key设置

• 全文索引(FullText)

  快速定位数据，myisam引擎下有效

操作

增

Syntax:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

示例：

create index index_name on test(col_name);

说明：

1.只有当正在使用MyISAM, BDB或InnoDB表类型时，可以向BLOB(二进制流)或TEXT列中添加索引，也可以向有NULL值的列中添加索引

2.FULLTEXT索引只能对CHAR, VARCHAR和TEXT列编制索引，并且只能在MyISAM表中编制。

3.SPATIAL索引只能对空间列编制索引，并且只能在MyISAM表中编

删

alter table table_name Drop index index_name;

drop index index_name on table_name;

删除主键索引：

alter table table_name drop primary key;

改

添加主键索引：

alter table table_name add primary key(id);

添加唯一索引：

alter table table_name add unique name_unique_index (col_name);

添加普通索引：

alter table table_name add index index_name (col_name);

禁用/激活非唯一索引：

alter table table_name disable | enable keys;

查

show index from table_name;

批量插入

-- 写函数之前必须要写的标志,避免语句总有分号直接停止后续语句的执行
DELIMITER $$  
create FUNCTION mock_data()		-- 函数名
RETURNS INT			-- 返回值
BEGIN	-- 函数体
	-- 变量名
	DECLARE num int DEFAULT  1000000;
	declare i int default 0;
	-- 开启循环
	while i < num do
		-- 执行的语句
		insert into oper_log(oper_name,oper_detail) values (CONCAt('用户',i),CONCAt('{\"json1\":',i,'}'));
		set i = i+1;
	end while;
	
	return i;
END;

#执行函数
SELECt mock_data();

#删除函数
DROP PROCEDURE mock_data;

索引使用原则

• where 子句或者join子句中出现的列

分析SQL效率

explain

mysql> explain select * from facts;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | facts | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

结果显示说明：
table：这是表的名字。
type：连接操作的类型。匹配行数，最好显示const，all最差
possible_keys：可能可以利用的索引的名字
Key：MySQL实际使用的索引的名字
key_len：索引中被使用部分的长度，以字节计
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数

分析慢查询sql

show variables like '%slow_query_log%'; 查看慢查询是否开启和日志保存路径

配置

slow_query_log = 1
slow_query_log_file = /tmp/mysql_slow.log
#查询阈值，大于这个值记录到日志
long_query_time = 10  

使用工具分析慢查询日志

mysqldumpslow -s r -t 10 /usr/local/mysql/data/slow.log

命令说明：

-s	自定义按那种排序方式输出结果 c：总次数 t：总时间 l：锁的时间 r：总行数 at,al,ar：平均数的意思
-t	指定取前几条数据

备份

归档模式与非归档模式对比

归档模式	非归档模式
做热备份,并且可以做增量备份,可以做部分恢复	非归档就是不保存旧事务日志 只能做冷备份,并且恢复时只能做完全备份.最近一次完全备份到系统出错期间的数据不能恢复.

冷热备份

对比	冷备份	热备份
	指在数据库关闭后，进行备份，适用于所有模式的数据库	针对归档模式的数据库，在数据库仍旧处于工作状态时进行备份

数据库使用归档方式运行时才可以进行灾难性恢复

如果数据库处于非归档模式,联机日志在切换时就会丢弃. 而在归档模式下，当发生日志切换的时候，被切换的日志会进行归档

导出数据库

导出表结构

mysql的bin目录找到mysqldump：

mysqldump -u 用户名 -p 密码 -d 数据库名 > 数据库名.sql

导出数据和表结构，包含存储过程、函数

mysqldump -u 用户名 -p 密码 -R -d 数据库名 > 数据库名.sql

具体其他参数，可以使用 ./mysqldump --help 查看

导入数据库

导入前需要确定数据库是否存在。

如果数据库名包含符号，使用反引号将库名包裹起来。

命令行

mysql -u用户名 -p密码 数据库名 < 数据库名.sql

Mysql命令行：

use DbName;

source /home/abc/abc.sql; #这里是sql文件的绝对路径

动态备份

基于mysqldump通常就是：完整备份 + 二进制日志 来进行恢复

https://www.cnblogs.com/xsuid/archive/2018/08/03/9416593.html

数据库设计

三大范式

第一范式

每张表的每列都是不可再分的。列的原子性。

第二范式

前提 满足第一范式

每一列都和主键相关(针对联合主键而言)

即一个表只描述一件事情。

第三范式

前提 满足第二范式

消除传递依赖

命名

• 库名(可以显示项目信息)，表名(显示功能)，字段(合理使用字段类型，字段命名可以查看功能，非负使用unsigned，范围小使用tinyint)，单条记录最好不超过8K
• myisam:建议使用国定长度的数据列代替可变长度。
• 能用tinyint就不用integer
• 如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的(但是会占用更多的存储空间)，所以更加精确，java中与之对应的数据类型为BigDecimal
• 对于金额直接使用bigint存储
• tinyint(2)，对于整型来说小括号中的2不是指的存储长度，而是指的零填充达到长度2

分表

单表行数超过500万行或者单表容量超过 2GB，才推荐进行分库分表

分区

将数据库的表分割成多个表，当然看到的还是一个表，只不过物理保存的时候是多个文件。使用分区的前提是表是独立空间存储的。

可以在建表时分区，也可以在之后分区。

举例：

create table emp_date(
id int not null,
separated date not null default '9999-12-31'
)
#以某个字段分区
partition by range columns(separated) (
partiontion p0 values less than ('1990-01-01'),
partiontion p0 values less than ('2001-01-01'),
partiontion p0 values less than ('2018-01-01')
);

参考

数据切分

水平切分	垂直切分
定义：
按照一定规则切分单个表	根据功能模块分类多个表
特点：
◆ 数据库的拆分简单明了，拆分规则明确； ◆ 应用程序模块清晰明确，整合容易； ◆ 数据维护方便易行，容易定位；	◆ 表关联基本能够在数据库端全部完成； ◆ 不会存在某些超大型数据量和高负载的表遇到瓶颈的问题； ◆ 应用程序端整体架构改动相对较少； ◆ 事务处理相对简单； ◆ 只要切分规则能够定义好，基本上较难遇到扩展性限制；
缺点：
◆ 部分表关联无法在数据库级别完成，需要在程序中完成； ◆ 对于访问极其频繁且数据量超大的表仍然存在性能平静，不一定能满足要求； ◆ 事务处理相对更为复杂； ◆ 切分达到一定程度之后，扩展性会遇到限制； ◆ 过读切分可能会带来系统过渡复杂而难以维护。	◆ 切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则； ◆ 后期数据的维护难度有所增加，人为手工定位数据更困难 ◆ 应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。
整合方案：自行编写中间代理层	使用开源框架： AmoebaForMySQL：只能是mysql协议连接与数据源 AmoebaForAladin：mysql协议连接，可以多种数据源 HiveDB：只支持水平切分，
切分和整个之后问题：
分布式事务问题：可以在应用解决为单一数据源 跨节点join问题：可由应用解决，通过查询驱动 跨节点合并排序分页问题：可由应用解决

注意：

实际设计时需要根据实际业务需求设计。

数据库优化

有哪些优化方面：
字段类型、分表存储、事务处理、数据库参数优化(最大连接、占用内存等)、读写分离(主从复制)、增加缓存(redis, memcached等)、升级硬件设备(磁盘IO读写速度，CPU，内存，带宽)

SQL优化

1. GROUP BY NULL 禁止排序避免对结果排序的消耗

2. ORDER BY 使用索引

3. INSERT 插入多行使用values一次插入，不要多次插入

4. INSERT DELAYED 多个连接同时插入时，使用延迟插入提高插入速度

5. 如果只是查询，设置配置mysql的low_priority_updates=1,降低写的优先级

6. 大量数据操作时分批操作，这样不会导致一个线程占用表太长时间

7. 不要在where条件表达式写计算表达式，尽量少使用函数对字段操作，这样会导致不会使用索引。简单判断即可

8. 对以下操作符才使用索引：
   <，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。
   不要写==!=,<>==,用between或者> and < 代替

9. 少用子查询,使用连接（JOIN）来代替子查询(Sub-Queries)

10. where 条件中不要使用 or(导致全表扫描)，使用联合替代

    举例：

    select id from t where num=10 or Name = 'admin'
    替换：
    select id from t where num = 10
    union all
    select id from t where Name = 'admin'
    

11. in 和not in最好别用，容易导致全表扫描，一些情况下，可以使用exists替换in。
    使用exists先查询主表，再与子查询作对比；
    in先查询子查询，在和主表笛卡尔。
    如果子查询记录多，用exists；如果子查询少，用in

12. 最好不要使用like ’ ', 全表扫描

13. 字段尽量设置为not null

14. or只有两边都是索引才使用索引

15. 查询时字段类型要正确，否则不使用索引

16. 模糊查询只有最前缀使用索引，即%在后面匹配

17. 使用连接（JOIN）来代替子查询(Sub-Queries)

JDBC

对数据库使用统一接口

代码实现步骤

1. 注册Driver

   Class.forName("com.mysql.jdbc.Driver");

2. 连接数据库

   //用户与连接信息
   String  url = "jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=utf8&useSSL=true";
   String username = "root";
   String password = "123456"
   
   //连接获取connection
   Connection connection = DriverManager.getConnect(url, username, password);
   
   //获取执行对象
   Statement statement = connection.createStatement();
   

3. 操作数据库

   //sql 语句
   String sql = "SELECt * from test";
   
   //执行sql
   ResultSet resultSet = statement.executeQuery(sql);
   
   //访问结果,具体方法查看对应类或者使用时.选择对应函数
   resultSet.getObject("columnName");
   

4. 关闭资源

   resultSet.close();
   statement.close();
   connection.close();
   

SQL注入

参数没有处理导致注入sql，实现非法操作

'or 1=1'

PreparedStatement 对象防止sql注入

连接池

druid

阿里提供的数据库连接池

C3P0

xml配置参数

DBCP

需要jar包

commons-dbcp-1.4

commons-pool-1.6

FAQ

mysql启动出现错误

mysql.sock 文件丢失
或者
Manager of pid-file quit without updating file.[FAILED] 

方法：

/usr/local/bin/mysql_install_db

忘记密码

方法一：
修改配置文件my.conf,添加skip-grant-tables
重启mysql并登陆(无密码)
修改mysql数据库中root用户密码
update user set authentication_string=password('123456') where user='root';
刷新权限：
flush privileges;
退出，删除配置文件中添加项

方法二：
不检查权限方式启动
mysql -u root -p --skip-grant-tables &(可加选项)
登陆修改密码即可
update MySQL.user set password=PASSWORd('newpassword') where User='root';
flush privileges;

字段中存在引号

数据库插入字段中包含单引号，只需要将单引号替换为双引号即可。

删除表中重复字段，保留一个

DELETE from t_entity_terminal WHERe termid in(SELECt termid from (SELECt max(termid) as termid from t_entity_terminal GROUP BY machinesid HAVINg COUNT(machinesid) > 1) as tmpresult);

需要建立临时表，否则删除不成功

数据库表crash修复

1.关闭mysql服务
2.myisamchk -f -o tablename.MYI

或者

检查
mysqlcheck -u root -p wordpress
备份
mysqldump -u root -p wordpress > wordpress.sql
修复
mysqlcheck -u root -p wordpress --auto-repair

更新不同字段为不同值

UPDATe mytable 
SET myfield = CASE id 
WHEN 1 THEN 'value'
WHEN 2 THEN 'value'
WHEN 3 THEN 'value'
END
WHERe id IN (1,2,3)

查询结果导入临时表

CREATE TEMPORARY TABLE a_test as SELECT id  FROM t_log_terminalupgrade  GROUP BY machineid,planid HAVINg COUNT(1) > 1 ;

批量修改表的引擎

#使用concat函数重组sql语句即可，
#table_name:表名
#table_schema:数据库名

查看sql:
SELECt CONCAt(table_name,'  ', engine)
FROM information_schema.tables WHERe table_schema="testdb" AND ENGINE="MyISAM";
修改sql：
SELECt CONCAt('ALTER TABLE ',table_name,' ENGINE=InnoDB;') FROM information_schema.tables
WHERe table_schema="testdb" AND ENGINE="MyISAM";

查看哪些表被锁定

show OPEN TABLES where In_use > 0;