mysql性能优化

扎实基础（点到即止）

`where` `group by` `order by` 共用时，需要遵从的规定：

group by 子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果在group by 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
group by 子句中列出的每个列都必须是检索\列或有效的表达式（但不能是聚集函数）。如果在select 中使用表达式，则必须在group by 中指定相同的表达式。不能使用别名。
除聚集计算语句外，select 语句中的每个列都必须在group by 子句中给出。
如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将分为一组。
group by 子句必须出现在where 子句之后，order by 子句之前。

tips: 可以在group by 字段 后面使用with rollup 关键字，可以得到每个分组以及每个分组汇总级别的值。

全文本搜索和查询扩展

create table productnotes
(
	note_id int not null auto_increment,
    prod_id char(10) not null,
    note_text text ,
    primary_key(note_id),
	fulltext(note_text) 
)engine=myisam 
# 注意必须是myisam 引擎

搜索时候：select note_text from productnotes where match(note_text) against('rabbit') 即可。

也要注意 match(note_text) against('rabbit') 是一个字段可以单独用来查询，比如select note_text match(note_text) against('rabbit') as rank from productnotes 最后的rank是每一句的匹配得分。

如果想要查询此行的相关行，则需要查询扩展，相关行指的是与查询到的字段所匹配行相近的附件行。具体如下语句：select note_text from productnotes where match(note_text) against('rabbit' with query expansion )

全文本布尔操作符

使用关键语句in boolean mode 可以匹配到布尔模式的文本，但是可以不需要fulltext索引,举例如下select note_text from productnotes where match(note_text) against('rabbit' -rape* in boolean mode) ,其中*匹配的是任意-表示非。

mysql优化

insert low_priority into

insert 语句可能耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的select 语句的性能。如果数据检索是最重要的（一般也是这样），则你可以通过在insert 和 into之间添加关键字low_priority,指示mysql 降低insert语句的优先级，如下所示：insert low_priority into 另外，update 和 delete语句也可以。

insert into table(a,b) values(c,d),(e,f)

单条批量插入，可以插入多条数据，效率比多条insert要高。

insert into table(a,b) select (a,b) from othertable

注意，如果a或者b是唯一主键时候的问题。还有 select 返回的列名其实不重要，都会添加到 table 的对应位置去。

update ignore table

可以忽略更新失败的语句，继续执行。

sql语句

alter 语句
常用来添加外键 alter table orderitems add constraint fk_orderitems_orders foreign key(order_num) references orders (order_num)
已知国内各区各省的人口如下表，请写出以区分组的总人口大于20，并且按人口总数排序且地区名以华开头的地区和地区总人数。（where group by order by 共用）

region_id	region_name	country	population
1	华中	湖南	10
2	华中	湖北	20
3	华南	海南	15
4	华南	广东	13
5	华东	浙江	10
6	华东	安徽	5
7	华北	北京	3
8	华北	内蒙	7
9	东北	吉林	3
10	东北	黑空间	7

分析：需要结果是地区和地区总人数 select region_name sum(population) ,且 where group by orde by 的顺序，所以分析的角度也是按这个顺序来（从执行角度来看，既符合引擎执行顺序，也符合逻辑）。

条件1：以华开头where region_name like '华%' 或者 where region_name regexp '^华.*'

条件2：区分组且总人口大于20 group by region_name having sum(population) > 20

条件3：以总人口排序order by sum(population)

最后整理结果是

SELECT region_name, SUM(population) FROM region WHERE region_name REGEXP '^华.*' GROUP BY region_name HAVING SUM(population) > 20 ORDER BY SUM(population)

将表中的数据查询出来做统计

题目要求如图：

sqlYearSeason

sql 语句：

SELECT 
  YEAR,
  SUM(
    CASE
      WHEN season = '1' 
      THEN number 
      ELSE 0 
    END
  ) '1季度',
  SUM(
    CASE
      WHEN season = '2' 
      THEN number 
      ELSE 0 
    END
  ) '2季度',
  SUM(
    CASE
      WHEN season = '3' 
      THEN number 
      ELSE 0 
    END
  ) '3季度',
  SUM(
    CASE
      WHEN season = '4' 
      THEN number 
      ELSE 0 
    END
  ) '4季度' 
FROM
  sale 
GROUP BY YEAR

思想就是没有的字段，用函数去构建，至于case when 用多了就熟了，如果不是四个季度呢，而是未知的呢？怎么写？

性能和架构问题

mysql的复制原理和流程

从3个线程的关联来分析：

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；

从：sql执行线程——执行relay log中的语句；

mysql引擎

三种主要引擎的区别

innodb 是可靠的事务处理引擎，但它不支持全文搜索。

myisam是一个性能极高的引擎，它支持全文本搜索，但不支持事务。

memory 功能等同于myisam,存于内存，速度很快（适合临时表）

注意：外键不可以跨引擎使用。

myisam与innodb的区别

索引、事务、行级锁、外键、mvcc(多版本并发控制，其实与事务相关)

UndoLog 实现了数据的多版本，而并发控制通过锁来实现,UndoLog 除了实现mvcc外，还用于事务的回滚。

innodb引擎的4大特性

插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

select count(*) 哪个更快

myisam 更快，内部维护了一个计数器可以直接读取。

MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别char是一种固定长度的类型，varchar则是一种可变长度的类型

(2)、varchar(50)中50的涵义最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)

(3)、int（20）中20的涵义是指显示字符的长度但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加020表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

(4)、mysql为什么这么设计对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

innodb的事务与日志的实现方式

(1)、有多少种日志；错误日志：记录出错信息，也记录一些警告信息或者正确的信息。查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。二进制日志：记录对数据库执行更改的所有操作。中继日志：事务日志：

(2)、事物的4种隔离级别隔离级别读未提交(RU)读已提交(RC)可重复读(RR)串行

(3)、事务是如何通过日志来实现的，说得越深入越好。事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”。

MySQL binlog的几种日志录入格式以及区别

(1)、binlog的日志格式的种类和分别

(2)、适用场景；

(3)、结合第一个问题，每一种日志格式在复制中的优劣。Statement：每一条会修改数据的sql都会记录在binlog中。优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).使用以下函数的语句也无法被复制：

LOAD_FILE()
UUID()
USER()
FOUND_ROWS()
SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

1.statement:每一条会修改数据的sql都会记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行多相同的sql再执行。
优点：在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能。因为他只需要记录在master上所执行的语句的细节以及执行语句的上下文信息。
缺点：在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row是基于每一行来记录的变化，所以不会出现，类似的问题。

2.Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。
3.Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

MySQL数据库cpu飙升到500%怎么处理

列出所有进程 show processlist 观察所有进程多秒没有状态变化的(干掉)查看超时日志或者错误日志。或者突然大批量的插入和删除。

索引

索引能够轻易将查询性能提升几个数量级。

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。对于中到大型的表，索引就非常有效。但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

B-Tree

定义一条数据记录为一个二元组 [key, data]，B-Tree 是满足下列条件的数据结构：

所有叶节点具有相同的深度，也就是说 B-Tree 是平衡的；
一个节点中的 key 从左到右非递减排列；
如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

查找算法：首先在根节点进行二分查找，如果找到则返回对应节点的 data，否则在相应区间的指针指向的节点递归进行查找。

由于插入删除新的数据记录会破坏 B-Tree 的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持 B-Tree 性质。

B+Tree

与 B-Tree 相比，B+Tree 有以下不同点：

每个节点的指针上限为 2d 而不是 2d+1（d 为节点的出度）；
内节点不存储 data，只存储 key；
叶子节点不存储指针。

顺序访问指针

一般在数据库系统或文件系统中使用的 B+Tree 结构都在经典 B+Tree 基础上进行了优化，在叶子节点增加了顺序访问指针，做这个优化的目的是为了提高区间访问的性能。

B+Tree 和 B-Tree 优势

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+Tree 和 B-Tree 作为索引结构，主要有以下两个原因：

（一）更少的检索次数

平衡树检索数据的时间复杂度等于树高 h，而树高大致为 O(h)=O(logdN)，其中 d 为每个节点的出度。

红黑树的出度为 2，而 B+Tree 与 B-Tree 的出度一般都非常大。红黑树的树高 h 很明显比 B+Tree 和 B-Tree 大非常多，因此检索的次数也就更多。

B+Tree 相比于 B-Tree 更适合外存索引，因为 B+Tree 内节点去掉了 data 域，因此可以拥有更大的出度，检索效率会更高。

（二）利用计算机预读特性

为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，因此速度会非常快。

操作系统一般将内存和磁盘分割成固态大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点，并且可以利用预读特性，相邻的节点也能够被预先载入。

自我要求

基本 SQL 语句，索引优化
存储引擎(InnoDB，MyISAM)，索引原理
事务 ACID，隔离级别
分库分表，主从复制，读写分离

参考

数据库优化

20个面试题

MySQL 索引背后的数据结构及算法原理

posted @ 2019-03-06 01:01 Hoult丶吴邪阅读(250) 评论(0) 编辑收藏举报

刷新页面返回顶部

Hoult丶吴邪

A lot can happen between now and never.

mysql性能优化

扎实基础（点到即止）

`where` `group by` `order by` 共用时，需要遵从的规定：

相关子查询问题

全文本搜索和查询扩展

全文本布尔操作符

mysql优化

sql语句

性能和架构问题

mysql的复制原理和流程

mysql引擎

三种主要引擎的区别

myisam与innodb的区别

innodb引擎的4大特性

select count(*) 哪个更快

MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

innodb的事务与日志的实现方式

MySQL binlog的几种日志录入格式以及区别

MySQL数据库cpu飙升到500%怎么处理

索引

B-Tree

B+Tree

顺序访问指针

B+Tree 和 B-Tree 优势

自我要求

参考

公告

Hoult丶吴邪

A lot can happen between now and never.

mysql性能优化

扎实基础（点到即止）

where group by order by 共用时，需要遵从的规定：

相关子查询问题

全文本搜索和查询扩展

全文本布尔操作符

mysql优化

sql语句

性能和架构问题

mysql的复制原理和流程

mysql引擎

三种主要引擎的区别

myisam与innodb的区别

innodb引擎的4大特性

select count(*) 哪个更快

MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

innodb的事务与日志的实现方式

MySQL binlog的几种日志录入格式以及区别

MySQL数据库cpu飙升到500%怎么处理

索引

B-Tree

B+Tree

顺序访问指针

B+Tree 和 B-Tree 优势

自我要求

参考

公告

`where` `group by` `order by` 共用时，需要遵从的规定：