Mysql数据库

Ocean7732
93 阅读3分钟

Mysql数据库

存储引擎

特点MyISAMInnoDBMEMORY
存储限制256TB64TBRAM
事务安全NYN
锁机制表锁行锁表锁
B树索引YYY
HASH索引NNY
全文索引YNN
数据缓存NYY
索引缓存YYY
数据可压缩YNN
空间使用低高N/A
内存使用低高中等
支持外键NYN
批量插入速度高低高

索引

  • 索引类型 Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。
  1. FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

  1. HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

  1. BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（多路树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

  1. RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

  • 索引种类 普通索引（normal）：仅加速查询

唯一索引（unique）：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引（full text）：对文本的内容进行分词，进行搜索

  • 索引失效的场景
1、使用like ‘%%’ 酱紫的模糊查询
2、使用=或<>不等于符号
3、使用or的情况下，如果or的所有查询条件都是索引的话仍是索引仍是不会失效的，否则会失效
4、计算式子如 where num-1=10 可以改成num=11。包括使用datediff等好函数的情况下，只要涉及到参数的一系列计算则都会索引失效。
5、如果MySQL估计使用全表扫描要比使用索引快，则不使用索引；（不是很懂原理，不知道是怎么评估时间的）
  • 索引的创建修改
--创建普通索引
CREATE INDEX index_name ON table_name(col_name);
--创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
--创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
--创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

--通过修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name);

--创建表时直接指定索引
CREATE TABLE table_name (ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name));

--直接删除索引
DROP INDEX index_name ON table_name;
--修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

-- 查看索引
SHOW INDEX FROM table_name;

JDBC事务

同Spring事务

存储过程

  • 优点 增强SQL语言的功能和灵活性

标准组件式编程

较快的执行速度

减少网络流量

作为一种安全机制来充分利用

  • 语法
CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

触发器

  • 优缺点 触发器的优点在于，可以使用简单的配置来实现复杂的功能，这些功能如果想要在应用层面去实现往往需要比较多的代码量

使得系统的耦合度变高

  • 语法
CREATE TRIGGER `insert_into_table2_too` AFTER INSERT ON `table1`
FOR EACH ROW 
BEGIN
	INSERT INTO `table2` (`col1`,`col2`) VALUES(NEW.`col1`,NEW.`col2`);
END

数据库优化

  • 数据库优化纬度 优化成本:硬件>系统配置>数据库表结构>SQL及索引

优化效果:硬件<系统配置<数据库表结构<SQL及索引

binlog_cache_size （global）

key_buffer_size （global）

bulk_insert_buffer_size （thread）

innodb_buffer_pool_size（global）

innodb_additional_mem_pool_size（global）

innodb_log_buffer_size （global）

innodb_max_dirty_pages_pct （global）

DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间

建议区分开 TINYINT / INT / BIGINT 的选择

定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR

尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半

对于只需要精确到某一天的数据类型，建议使用DATE类型

对于状态字段，可以尝试使用 ENUM 来存放，因为可以极大的降低存储空间

存放可预先定义的属性数据，可以尝试使用SET类型

强烈反对在数据库中存放 LOB 类型数据

字符编码

适当拆分

适度冗余

尽量使用 NOT NULL

NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间

  • 索引优化 我们应该尽量让查找条件尽可能多的在索引中，尽可能通过索引完成所有过滤，回表只是取出额外的数据字段

字段的顺序对组合索引效率有至关重要的作用，过滤效果越好的字段需要更靠前。

当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候，使用索引并不一定优于全表扫描。

在实际使用过程中，一次数据访问一般只能利用到1个索引，这一点在索引创建过程中一定要注意，不是说一条SQL语句中Where子句里面每个条件都有索引能对应上就可以了

  • SQL优化 优化目标:减少 IO 次数,降低 CPU 计算

优化方法:改变 SQL 执行计划

常见误区:count(1)和count(primary_key) 优于 count()、count(column) 和 count() 是一样的、select a,b from … 比 select a,b,c from … 可以让数据库访问更少的数据量、order by 一定需要排序操作、执行计划中有 filesort 就会进行磁盘文件排序

基本原则：尽量少 join、尽量少排序、尽量避免 select *、尽量用 join 代替子查询、尽量少 or、尽量用 union all 代替 union、尽量早过滤、避免类型转换（where 子句中出现 column 字段的类型）、优先优化高并发的 SQL，而不是执行频率低某些“大”SQL、从全局出发优化，而不是片面调整、尽可能对每一条运行在数据库中的SQL进行 explain

MySQL的索引结构为什么使用B+树，而不是其他树形结构

聚簇索引和非聚簇索引的区别

mysql mvcc

数据库DDL语句造成从库延迟怎么解决

mysql索引原理细节

数据库字符串主键与自增主键的区别

在数据量较大的情况下，用自增id作为主键，和用随机字符串作为主键，插入性能有比较大的差异，自增id主键性能较好。

一张订单表，这张表的QPS很高，现在需要在表里面增加一个字段，但是又需要不影响线上业务，怎么实现？

数据库索引类型，何时失效

like查询已 '%...'开头，以'xxx%'结尾会继续使用索引。
where语句中使用 <>和 ![图片][图片][图片][图片][图片]=
where语句中使用 or，但是没有把or中所有字段加上索引。
where语句中对字段表达式操作
where语句中使用not In
对于多列索引，需要使用第一个索引才会命中索引

char与varchar的区别

char的长度是不可变的，而varchar的长度是可变的
char的存取数度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找；

但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。

char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；
而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节。
avatar
文章
阅读
粉丝