002-mysql-02

列的字符串类型可以是什么？

SET BLOB ENUM CHAR TEXT VARCHAR

date,datetime,time和timestamp数据类型有什么区别

date,datetime,time 区别在于精度：日期，日期+时间，时间

timestamp和datetime的相同点： 两者都可用来表示YYYY-MM-DD HH:MM:SS类型的日期，DateTime和TimeStamp都可以精确到毫秒

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为**UTC（世界标准时间）**进行存储。查询时，将其又转化为客户端当前时区进行返回。

而对于DATETIME，不做任何改变，基本上是原样输入和输出。

两者所能存储的时间范围不一样

timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。

datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

MySQL 数据库服务器性能分析的方法命令有哪些?

show status

Show session status

show profile

show processlist

LIKE 和 REGEXP 操作有什么区别？

LIKE 和 REGEXP 运算符用于表示 ^ 和％。

SELECT * FROM WHERE * REGEXP "^b";

SELECT * FROM WHERE * LIKE "%b";

BLOB 和 TEXT 有什么区别？

BLOB 是一个二进制对象，可以容纳可变数量的数据。有四种类型的 BLOB :TINYBLOB ,BLOB ,MEDIUMBLOB ,LONGBLOB ,它们只能在所能容纳价值的最大长度上有所不同。

TEXT是 以文本方式存储的，如果存储英文的话区分大小写

Blob是以二进制方式存储的，不区分大小写

MySQL 表中允许有多少个TRIGGERS？

在 MySQL 表中允许有六个触发器，如下： BEFORE INSERT

AFTER INSERT

BEFORE UPDATE

AFTER UPDATE

BEFORE DELETE and

AFTER DELETE

什么是通用 SQL 函数？

数学函数

Abs（num）求绝对值

floor（num）向下取整

ceil（num）向上取整

字符串函数

 insert (s1,index,length,s2) 替换函数

o S1 表示被替换的字符串

o s2 表示将要替换的字符串

o Index 表示被替换的位置, 从 1 开始

o Length 表示被替换的长度

 upper（str），ucase（str）将字母改为大写

 lower（str），lcase（str）将字母改为小写

 left（str，length）返回 str 字符串的前 length 个字符

 right（str，length）返回 str 字符串的后 length 个字符

 substring（str，index，length）返回 str 字符串从 index 位开始长度为 length 个字符（index 从 1 开始）

 reverse（str）将 str 字符串倒序输出

日期函数

curdate（）、current_date( ) 获取当前日期

curtime（）、current_time( ) 获取当前日期

now（）获取当前日期和时间

datediff（d1、d2）d1 和 d2 之间的天数差

adddate（date，num）返回 date 日期开始，之后 num 天的日期

subdate（date，num）返回 date 日期开始，之前 num 天的日期

聚合函数

Count（字段）根据某个字段统计总记录数（当前数据库保存到多少条数据）

sum（字段）计算某个字段的数值总和

avg（字段）计算某个字段的数值的平均值

Max（字段）、min（字段）求某个字段最大或最小值

UNION and UNION ALL

UNION 从两个互不关联的表中返回数据，这就意味着不会出现重复的行，同时也必须对数据进行排序.

UNION ALL 可以大大加快速度，如果你已经知道你的数据不会包括重复行,UNION ALL 不会进行排序

truncate delete drop 的区别

**drop(DDL 语句)：**是不可逆操作，会将表所占用空间全部释放掉；

**truncate(DDL 语句)：**只针对于删除表的操作，在删除过程中不会激活与表有关的删除触发器并且不会把删除记录放在日志中；当表被 truncate 后，这个表和索引会恢复到初始大小；

**delete(DML 语句)：**可以删除表也可以删除行，但是删除记录会被计入日志保存，而且表空间大小不会恢复到原来；

utf8和utf8mb4的区别

Mysql的utf8编码并不是真正的UTF-8编码，Mysql的utf8最多只支持3个字节，而emoji表情、一些特殊的中文字符则需要4个字节才能存储， 因此才会报错。

mysql在**MySQL 5.6+**版本之后支持4Bytes字节（utf8mb4）的存储，Mysql中的utf8mb4是原先utf8只能存储3个字节大小的字符的一种补充，是一种真正的UTF-8编码。

数据库死锁机制和解决⽅方案：

处理理机制：解决死锁最有⽤最简单的方法是不要有等待，将任何等待都转化为回滚，并且事务重新开始。但是有可能影响并发性能。

1、超时回滚，innodb_lock_wait_time设置超时时间；

2、wait-for-graph⽅法：跟超时回滚比起来，这是⼀种更加主动的死锁检测方式。InnoDB引擎也采用这种⽅式。

自增ID

一张表，里面有ID 自增主键，当insert 了17 条记录之后，删除了第15,16,17 条记录，再把Mysql 重启，再insert 一条记录，这条记录的ID 是18 还是15 ？

如果是myisam则是18，因为会把最大ID存储在数据文件中。

如果是InnoDB则是15，把最大ID存储在内存中，重启之后丢失。

优点：自动生成、唯一性，避免表的数据碎片问题，提高数据查询及读取性能。缺点：可预测、不可复用，不适用于分布式环境。对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争。Auto_Increment锁机制会造成自增锁的抢夺,有一定的性能损失。

如果主键为自增 id 的话，MySQL 在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。如果主键是非自增 id，为了确保索引有序，MySQL 就需要将每次插入的数据都放到合适的位置上。当往一个快满或已满的数据页中插入数据时，新插入的数据会将数据页写满，MySQL 就需要申请新的数据页，并且把上个数据页中的部分数据挪到新的数据页上。这就造成了页分裂，这个大量移动数据的过程是会严重影响插入效率的。自增id 可以保证每次插入时B+索引是从右边扩展的，可以避免B+树频繁合并和分裂（对比使用UUID而言）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

使用mysql8自带的uuid_to_bin可以方便的将时间相关的字符高低位进行互换，从而解决了这个性能上的问题。在通过 UUID()函数 生成的uuid值中，若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的"-"字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。

不适合以自增ID主键作为主键的情况：

  1. 数据量多需要分库分表，可能会造成ID重复

  2. 经常会遇到数据迁移的情况

  3. 新数据需要和老数据进行合并

当自增ID用完之后，如果定义了主键：如果您的表使用INT数据类型，最大值为2147483647，如果自增ID的值已经达到这个最大值，那么MySQL将无法再生成新的自增ID，这时您将无法插入新的记录。如果未定义主键：InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id。在代码实现时 row_id 是一个长度为8字节的无符号长整型 (bigint unsigned)。但是，InnoDB 在设计时，给 row_id 留的只是 6 个字节的长度，这样写到数据表中时只放了最后 6 个字节。

  • row_id 写入表中的值范围，是从 0 到 2^48-1；

  • 当 dict_sys.row_id=2^48时，如果再有插入数据的行为要来申请 row_id，拿到以后再取最后 6 个字节的话就是 0。

外部临时表

通过CREATE TEMPORARY TABLE 创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。

内部临时表

内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。内部临时表在SQL语句的优化过程中扮演着非常重要的角色， MySQL中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。

内部临时表有两种类型：一种是HEAP临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要IO操作。另一种是OnDisk临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk临时表用来处理中间结果比较大的操作。如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE，HEAP临时表将会被自动转换成OnDisk临时表。OnDisk临时表在5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎。

Heap 表是什么？

HEAP 表存在于内存中，用于临时高速存储。

BLOB 或TEXT 字段是不允许的

只能使用比较运算符=，<，>，=>，= <

HEAP 表不支持AUTO_INCREMENT

索引不可为NULL

列设置为AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用

怎样才能找出最后一次插入时分配了哪个自动增量？

LAST_INSERT_ID将返回由Auto_increment 分配的最后一个值，并且不需要指定表名称。

如何在Unix 和Mysql 时间戳之间进行转换？

UNIX_TIMESTAMP 是从Mysql时间戳转换为Unix时间戳的命令

FROM_UNIXTIME 是从Unix时间戳转换为Mysql时间戳的命令

Mysql 如何优化DISTINCT？

DISTINCT 在所有列上转换为GROUP BY，并与ORDER BY 子句结合使用。

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

可以使用多少列创建索引？

任何标准表最多可以创建16个索引列。

NOW（）和CURRENT_DATE（）有什么区别？

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。

CURRENT_DATE（）仅显示当前年份，月份和日期。

int 和tinyint

int类型占4个字节，tinyint占1个字节。int(1) 和 int(4) 从本身长度还是存储方式上都是一样的，区别就是显示的长度不同，但是要设置一个参数：如果列制定了zerofill 就会用0填充显示，int(4)指定后就会显示为0002。

int(1)和tinyint(4) 相比，肯定int 大。

注意数字类型后面括号中的数字，不表示长度，表示的是显示宽度，这点与 varchar、char 后面的数字含义是不同的。

mysql 里记录货币用什么字段类型好

NUMERIC 和DECIMAL类型被Mysql实现为同样的类型，这在SQL92 标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数salary DECIMAL(9,2)。 NUMERIC 是标准SQL的数据类型。DECIMAL是MySQL的扩展数据类型。

mysql查询优化

查询的生命周期的下一步是将一个SQL转换成执行计划，MySQL再依照这个执行计划和存储引擎进行交互,一条查询可以有很多种执行方式，最后都返回相同的结果。查询优化器的作用就是找到其中最好的执行计划.

  • 重新定义关联表顺序

  • 将外连接转化成内连接

  • 使用等价变换规则

  • 优化COUNT() 、MIN() 、 MAX()

  • 预估并转换为常数表达式

  • 覆盖索引扫描

  • 子查询优化

  • 提前终止查询

  • 等值传播

  • 列表IN()的比较

如何预估一个mysql语句的性能

  1. explain

  2. SHOW PROFILE命令，默认是没有开启这个命令支持的，需要用SET profiling = 1;来开启。开启以后，会输出任何命令的耗时和执行过程中的状态变化。每次执行命令的时候，评估数据会被记录到一个临时表中，这个表可以通过SHOW PROFILES;SHOW PROFILE FOR QUERY 1；

  3. SHOW STATUS命令，SHOW STATUS返回各种计数，这些值有全局的(整个MySQL服务器)，也有Session的(本次连接)。

SQL优化

  1. 表字段尽量使用数值型：因为数值型字段在MySQL底层应用的时候相比string类型的话性能更好

  2. 建立合理的索引：索引就是用某种数据结构来查找对应的信息，从而减低时间复杂度提高查找效率。建立索引的前提也要明确，综合考虑再打算是否需要建立索引，毕竟索引是需要占用存储空间的，有时候牺牲的空间却换不回时间。

  3. 尽量将要输出的字段写出来；不要使用 select * from where xxxxx ；这种形式的语句。我在这测试时是使用代替，但是记住在生产环境上尽量将字段替代。

  4. 合理使用连表查询；不仅是表的连接需要较大的内存消耗另外一方面如果表设计的不是很合理也会导致索引无效从而造成极坏的结果。

  5. 查询的时候要注意是否走索引：假如你在name列建立了一个 name_index索引，查询你使用 name Like'%xxxx' 或者 name Like'%xxxx%' 这种模糊查询，那么此时可能就不会走索引；你应该这样 name Like'xxxx%' 。

  6. 使用索引遵循最佳左前缀特性，建立联合索引的时候将常用的属性放在左边。

  7. 使用联合索引需要从左往右不间断，索引才会生效，也就是说联合索引使用的时候必须要连续但不要求全部使用

  8. 读写分离与分库分表

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5.下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

6.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id

select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

应改为:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...)

13.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30.尽量避免大事务操作，提高系统并发能力。

其他

全表扫描会消耗数据库的CPU。

上一页001-mysql-01下一页redis

最后更新于