MySQL 数据库 高级

数据库存储原理

• 其过程是一个可编程的函数，它在数据库中创建并保存
• 它可以由 SQL 语句和一些特殊的控制语句组成
• 但在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常重要的
• 数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式

增删改查

库

• 创建数据库：create database 库名 charset=utf8
• 查看数据库创建语句：show create database 库名
• 修改 MySQL 数据库字符编码为 UTF-8：alter database 数据库名称 charset set utf-8;

表

• 创建表：create table 表名 （id int auto_increment primary key not null,...）
• 修改表结构：alert table 表名称 add | modify | change | drop 列 约束类型
  • change：修改字段名称及类型
  • modify：只能修改字段名称
• 删除表：drop table 表名称
• 修改表名：rename table 原名 to 新名
• 增：
  • insert into 表名 (列) values(内容1，内容2)
  • insert into 表名 values(一一对应写出内容)
• 删：delete from 表名 where 条件
• 改：update 表名称 set 列=内容 where 条件
• 查：select * from 表名，SQL 语句查询顺序
  • from 子句组装不同数据源的数据
  • where 子句基于指定的条件对记录进行筛选
  • group by 子句将数据划分为多个分组
  • 使用聚类函数进行计算
  • 使用 having 子句进行筛选分组
  • 计算所有的表达式
  • select 的字段
  • 使用 order by 对结果集进行排序

-- 分页
-- start：从第几页开始，起始位置为0
-- count：每页显示多少条数据
select * from students limit start, count

-- 统计
select count(列名) from 表名 where 条件

MySQL 的事务

• 原子性：书屋中的全部操作在数据中是不可分割的，要么全部完成，要么全部失败，要么都不执行
• 一致性：几个并行执行的事务，其执行结果必须与某一顺序串行执行的结果相一致
• 隔离性：事务的执行不受其他事务的干扰，事务执行的中奖结果对其他事务必须是透明的
• 持久性：对于任意已提交的事务，系统必须保证该事务对数据的改变不被丢失，即使数据库出现故障

数据库的回滚

• BEGIN：开始一个事务
• ROLLBACK：事务回滚
• COMMIT：事务确认

数据库的读写分离

提高读的性能：多配置几个数据库，并在读取时，随机读取，写的时候写到主库中

实现 APP 之间的数据库分离：分库分表

• 手动读写分离：通过.using (db_name) 来指定要使用的数据库
• 自动读写分离：
  • 定义类：如 Router
  • 配置 Router：settings.py 中指定：DATABASE_ROUTERS=[“myrouter.Router”]

数据库优化

1. 优化索引、SQL 语句、分析慢查询；
2. 设计表的时候严格根据数据库的设计范式来设计数据库；
3. 使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘 IO
4. 优化硬件；采用 SSD，使用磁盘队列技术 (RAID0,RAID1,RDID5) 等
5. 采用 MySQL 内部自带的表分区技术，把数据分层不同的文件，能够提高磁盘的读取效率；
6. 垂直分表；把一些不经常读的数据放在一张表里，节约磁盘 I/O；
7. 主从分离读写；采用主从复制把数据库的读操作和写入操作分离开来；
8. 分库分表分机器（数据量特别大），主要的的原理就是数据路由；
9. 选择合适的表引擎，参数上的优化
10. 进行架构级别的缓存，静态化和分布式；
11. 不采用全文索引；
12. 采用更快的存储方式，例如 NoSQL 存储经常访问的数据

优化查询

1. 储存引擎选择：
   • 如果数据表需要事务处理，应该考虑使用 InnoDB，因为它完全符合 ACID 特性
   • 如果不需要事务处理，使用默认存储引擎 MyISAM 是比较明智的
2. 分表分库，主从。
3. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
4. 尽量避免在 where 中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
5. 尽量避免在 where 中使用！= 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
6. 尽量避免在 where 中使用 or 来连接条件，eg: 一个字段有索引一个没有，将导致引擎放弃使用索引而进行全表扫描
7. Update：如果只更改 1/2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志
8. 对于多张大数据量 (这里几百条就算大了) 的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，性能很差

索引优化

索引是数据库管理系统中一个排序的数据访问，以协助快速查询、更新数据库表中的数据索引是实现：通常使用 B_TREE，B_TREE 索引加速了数据访问，因为引擎不会再去扫描整张表得到所需数据，相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据

索引建立原则

• 定义主键的数据列一定要建立索引。
• 定义有外键的数据列一定要建立索引。
• 对于经常查询的数据列最好建立索引。
• 对于需要在指定范围内的快速或频繁查询的数据列；
• 经常用在 WHERE 子句中的数据列。
• 经常出现在关键字 order by、group by、distinct 后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
• 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
• 对于定义为 text、image 和 bit 的数据类型的列不要建立索引。
• 对于经常存取的列避免建立索引
• 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过 3 个，最多不要超过 5 个
• 复合索引，按照字段在查询条件中出现的频度建立索引。

索引优化

• 创建索引：创建索引，但是较简单的不需要，会造成过度索引
• 复合索引：
  • 创建了 (area, age, salary) 的复合索引，那么其实相当于创建了 (area,age,salary)、(area,age)、(area) 三个索引，这被称为最佳左前缀特性
  • 因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减
• 索引不会包含有 NULL 值的列
  • 只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的
  • 所以我们在数据库设计时不要让字段的默认值为 NULL
• 使用短索引：对串列进行索引，如果可能应该指定一个前缀长度
• 排序的索引问：mysql 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的
• like 语句操作：
  • 一般情况下不鼓励使用 like 操作，如果非使用不可，如何使用也是一个问题
  • like “% aaa%” 不会使用索引而 like “aaa%” 可以使用索引
• 不要在列上进行运算

select * from users where YEAR(adddate)<2007;

-- 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;

• 不使用 NOT IN 的操作：NOT IN 和操作都不会使用索引将进行全表扫描。NOT IN 可以 NOT EXISTS 代替，id3 则可使用 id>3 or id<3 来代替。

索引的分类及创建

普通索引

最基本的索引，没有任何限制，比如 title 字段创建的索引就是一个普通索引

MyIASM 中默认的 B_TREE 类型的索引，也是我们大多数情况下使用的索引

-- 直接创建索引
create index_name on table(column(length));

-- 修改表结构的方式添加索引
alert table table_name add index index_name on (column(length));

-- 创建标的同事创建索引
create table "table_name"(
	'id' int(11) not null auto_increment,
	'title' char(225) character set utf8 collate utf8_general_ci not null,
	'content' text character set utf8 collate utf8_general_ci null,
	'time' int(to) null default null,
	primary key ('id'),
	index index_name(title(length))
)

-- 删除索引
drop index index_name on table;

唯一索引

与普通索引类似

不同的是：索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似

-- 创建唯一索引
create unique index index_name on table(column(length));

-- 修改表结构的形式添加索引
alert table table_name add unique index_name on(column(length));

-- 创建表的同时创建索引
create table "table_name"(
	'id' int(11) not null auto_increment,
	'title' char(225) character set utf8 collate utf8_general_ci not null,
	'content' text character set utf8 collate utf8_general_ci null,
	'time' int(to) null default null,
	primary key ('id'),
	unique index_name(title(length))
)

全文索引

MySQL 从 3.23.23 版本开始支持全文索引和全文检索，FULLTEXT 索引仅可用于 MyISAM 表

他们可以从 char、varchar 或 text 列中作为 create table 语句的一部分被创建，或是税后使用 alter table 或 create index 添加

-- 直接创建索引
create fulltext index index_content on article(content)

-- 修改表结构的同事添加全文索引
alert table article add fulltext index_content(content)

-- 创建表的时候添加索引
create table "table_name"(
	'id' int(11) not null auto_increment,
	'title' char(225) character set utf8 collate utf8_general_ci not null,
	'content' text character set utf8 collate utf8_general_ci null,
	'time' int(to) null default null,
	primary key ('id'),
	FULLTEXT(content)
)

组合索引（最左前缀）

平时用的 SQL 查询语句一般有比较多的限制条件，所以为了进一步榨取 MySQL 的效率，就要考虑组合索引了

-- 使用上面的索引
select * from article where title="测试" and time = 123123;
select * from article where utitle="测试";

-- 不使用上面的索引
select * from article where time=123123;