Skip to content

Latest commit

 

History

History
598 lines (459 loc) · 32.4 KB

mysql.md

File metadata and controls

598 lines (459 loc) · 32.4 KB
Raw

MySQL字段类型和取值范围 好未来

数据类型 字节长度 范围或用法
Bit 1 无符号[0,255]，有符号[-128,127]，天缘博客备注：BIT和BOOL布尔型都占用1字节
TinyInt 1 整数[0,255]
SmallInt 2 无符号[0,65535]，有符号[-32768,32767]
MediumInt 3 无符号[0,2^24-1]，有符号[-2^23,2^23-1]]
Int 4 无符号[0,2^32-1]，有符号[-2^31,2^31-1]
BigInt 8 无符号[0,2^64-1]，有符号[-2^63
Float(M,D) 4 单精度浮点数。天缘博客提醒这里的D是精度，如果D<=24则为默认的FLOAT，如果D>24则会自动被转换为DOUBLE型。
Double(M,D) 8  双精度浮点。
Decimal(M,D) M+1或M+2 未打包的浮点数，用法类似于FLOAT和DOUBLE
Date 3 以YYYY-MM-DD的格式显示，比如：2009-07-19
DateTime 8 以YYYY-MM-DD
TimeStamp 4 以YYYY-MM-DD的格式显示，比如：2009-07-19
Time 3 以HH:MM:SS的格式显示。比如：11：22：30
Year 1 以YYYY的格式显示。比如：2009
Char(M) M 定长字符串。
VarChar(M) M 变长字符串，要求M<=255
Binary(M) M 类似Char的二进制存储，特点是插入定长不足补0
VarBinary(M) M 类似VarChar的变长二进制存储，特点是定长不补0
Tiny Text Max:255
Text Max:64K 大小写不敏感
Medium Text Max:16M
Long Text Max:4G
TinyBlob Max:255 大小写敏感
Blob Max:64K 大小写敏感
MediumBlob Max:16M 大小写敏感
LongBlob Max:4G 大小写敏感
Enum 1或2 最大可达65535个不同的枚举值
Set 可达8 最大可达64个不同的值
Geometry    
Point    
LineString    
Polygon    
MultiPoint    
MultiLineString    
MultiPolygon    
GeometryCollection  

原子性是通过什么实现的。

undo log。

隔离性怎么理解？

MVCC 当前读和快照读。

mvcc 是通过日志和事务id来解决问题的。

MySQL优化

  1. 加索引
  2. 解决SQL语句没有命中索引的问题？
  3. 没有使用索引，或者使用了索引，但是优化器没有选择。

分库分表有什么实践

 1. 目前公司的表，已经很大了，但是目前没有拆表的需求。因为数据量不会再剧增了。

查询一个人数超过20人的班级

CREATE TABLE `class` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `class_name` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

select class_name, count(id) as nums from class group by class_name having nums > 20;

crash-safe

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

主要是两阶段提交吧？

  • 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；
  • 否则，需要先从磁盘读入内存，然后再返回。
  • 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  • 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。
  • 然后告知执行器执行完成了，随时可以提交事务。执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
  • 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

如果不使用日志的话，可能会导致恢复出来日志里面的数据，与原来数据库里面的数据不一致、

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

可重复读解决了哪些问题，还有哪些问题没有解决，为什么默认的级别是可重复读？

可重复读解决了不可重复读的问题。（读未提交的情况下，一个事务读取了一个还未提交或者还未回滚的事务）

可重复读没有解决幻读的问题，是通过日志和事务id来解决的不可重复读的问题。

因为性能比较高，可以通过next key lock 解决幻读的问题。

串行化解决了什么问题？（2月16，头条）

事务的安全问题。但是性能比较慢

除了主键索引，还用过什么（2月16，头条）

  1. 唯一索引
  2. 普通索引
  3. 覆盖索引
  4. 前缀索引

主键索引和唯一索引的区别（2月 16 头条）为什么会回表。

  1. 主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；
  2. 主键不允许为空值，唯一索引列允许空值；
  3. 一个表只能有一个主键，但是可以有多个唯一索引；
  4. 主键可以被其他表引用为外键，唯一索引列不可以；
  5. 主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质的差别

为什么使用B+树，而不是用B*树

因为B*树非叶子节点使用了指向兄弟节点的指针。如果一个节点满了之后，自己的兄弟节点还没有满，需要将一部分数据转移到自己的兄弟节点去。如果兄弟节点也满了，就在自己和兄弟节点之间添加新的节点。因为兄弟之间分配新节点的概率还是比较低的，所以空间利用率还是比较高的。 是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的指针；

MVCC解决了什么问题？（2月 16 头条）

  1. 行锁，并发，事务回滚。 只会读取事务开始之前提交的数据。针对每一个事务都有一个事务id 的概念，是严格递增的，回滚的时候，也会按顺序回滚的。

索引设计成树形，和SQL的需求相关。为什么不是哈希。

对于这样一个单行查询的SQL需求：

select * from t where name=”zhaoyang”;

确实是哈希索引更快，因为每次都只查询一条记录。

画外音：所以，如果业务需求都是单行访问，例如passport，确实可以使用哈希索引。

但是对于排序查询的SQL需求：

分组：group by

排序：order by

比较：<、>

哈希型的索引，时间复杂度会退化为O(n)，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率。

有个很大的日志表会选用哪个索引？

innodb，还有就是可以根据时间做分表。还有就是加索引。

MYSQL 总结

  1. 常见并发控制保证数据一致性的方法有锁，数据多版本；
  2. 普通锁串行，读写锁读读并行，数据多版本读写并行；
  3. redo日志保证已提交事务的ACID特性，设计思路是，通过顺序写替代随机写，提高并发；
  4. undo日志用来回滚未提交的事务，它存储在回滚段里；
  5. InnoDB是基于MVCC的存储引擎，它利用了存储在回滚段里的undo日志，即数据的旧版本，提高并发；
  6. InnoDB之所以并发高，快照读不加锁；
  7. InnoDB所有普通select都是快照读；

什么样的select是快照读？InnoDB并发如此之高

除非显示加锁，普通的select语句都是快照读，例如： select * from t where id>2;

这里的显示加锁，非快照读是指：

select * from t where id>2 lock in share mode;
select * from t where id>2 for update;

快照读（Snapshot Read），这种一致性不加锁的读（Consistent Nonlocking Read），就是InnoDB并发如此之高的核心原因之一。 这里的一致性是指，事务读取到的数据，要么是事务开始前就已经存在的数据（当然，是其他已提交事务产生的），要么是事务自身插入或者修改的数据。

MVCC 是支持并发的原因

数据多版本是一种能够进一步提高并发的方法，它的核心原理是：

  • 写任务发生时，将数据克隆一份，以版本号区分；
  • 写任务操作新克隆的数据，直至提交；
  • 并发读任务可以继续读取旧版本的数据，不至于阻塞；

如上图：

  1. 最开始数据的版本是V0；
  2. T1时刻发起了一个写任务，这是把数据clone了一份，进行修改，版本变为V1，但任务还未完成；
  3. T2时刻并发了一个读任务，依然可以读V0版本的数据；
  4. T3时刻又并发了一个读任务，依然不会阻塞；

可以看到，数据多版本，通过“读取旧版本数据”能够极大提高任务的并发度。

提高并发的演进思路，就在如此：

普通锁，本质是串行执行

读写锁，可以实现读读并发

数据多版本，可以实现读写并发

画外音：这个思路，比整篇文章的其他技术细节更重要，希望大家牢记。

InnoDB是高并发互联网场景最为推荐的存储引擎，根本原因，就是其多版本并发控制（Multi Version Concurrency Control, MVCC）。行锁，并发，事务回滚等多种特性都和MVCC相关。

redo log和undo log

redo log是将随机写，变为了顺序写。会保证acid特性。 undo日志会恢复数据。不会对acid造成影响。insert 回滚会删除，delete或恢复。

MyISAM和InnoDB都使用B+树来实现索引：

  1. MyISAM的索引与数据分开存储
  2. MyISAM的索引叶子存储指针，主键索引与普通索引无太大区别
  3. InnoDB的聚集索引和数据行统一存储
  4. InnoDB的聚集索引存储数据行本身，普通索引存储主键
  5. InnoDB一定有且只有一个聚集索引
  6. InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK

InnoDB的锁

  1. InnoDB的索引与行记录存储在一起，这一点和MyISAM不一样；
  2. InnoDB的聚集索引存储行记录，普通索引存储PK，所以普通索引要查询两次；
  3. 记录锁锁定索引记录；
  4. 间隙锁锁定间隔，防止间隔中被其他事务插入；
  5. 临键锁锁定索引记录+间隔，防止幻读；

如何实现 MySQL 的读写分离？

其实很简单，就是基于主从复制架构，简单来说，就搞一个主库，挂多个从库，然后我们就单单只是写主库，然后主库会自动把数据给同步到从库上去。

MySQL 主从复制原理的是啥？

主库将变更写入 binlog 日志，然后从库连接到主库之后，从库有一个 IO 线程，将主库的 binlog 日志拷贝到自己本地，写入一个 relay 中继日志中。接着从库中有一个 SQL 线程会从中继日志读取 binlog，然后执行 binlog 日志中的内容，也就是在自己本地再次执行一遍 SQL，这样就可以保证自己跟主库的数据是一样的。

这里有一个非常重要的一点，就是从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行 SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。

而且这里还有另外一个问题，就是如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。 所以 MySQL 实际上在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。

这个所谓半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。 所谓并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

MySQL 主从同步延时问题

以前线上确实处理过因为主从同步延时问题而导致的线上的 bug，属于小型的生产事故。 是这个么场景。有个同学是这样写代码逻辑的。先插入一条数据，再把它查出来，然后更新这条数据。在生产环境高峰期，写并发达到了 2000/s，这个时候，主从复制延时大概是在小几十毫秒。线上会发现，每天总有那么一些数据，我们期望更新一些重要的数据状态，但在高峰期时候却没更新。用户跟客服反馈，而客服就会反馈给我们。

我们通过 MySQL 命令：

show status

查看 Seconds_Behind_Master，可以看到从库复制主库的数据落后了几 ms。 一般来说，如果主从延迟较为严重，有以下解决方案：

  1. 分库，将一个主库拆分为多个主库，每个主库的写并发就减少了几倍，此时主从延迟可以忽略不计。【此时是主库的执行性能可能不好】
  2. 打开 MySQL 支持的并行复制，多个库并行复制。如果说某个库的写入并发就是特别高，单库写并发达到了 2000/s，并行复制还是没意义。
  3. 重写代码，写代码的同学，要慎重，插入数据时立马查询可能查不到。
  4. 如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库。不推荐这种方法，你要是这么搞，读写分离的意义就丧失了。

count为什么这么这样操作。

myisam保存了一个行数。所以查询起来会比较快。但是不支持事务。 innodb是需要将所有数据一行一行读入进来，然后统计计数。准确，但是会导致性能问题。

由于MVCC，innodb对于返回多少行，是不确定的。 如果都加where的count数据时一致的。

脏读：

当数据库中一个事务A正在修改一个数据但是还未提交或者回滚， 另一个事务B 来读取了修改后的内容并且使用了， 之后事务A提交了，此时就引起了脏读。

此情况仅会发生在： 读未提交的的隔离级别.

主从

  1. 主库和从库之间的网络问题
  2. 从库和主库机器的性能可能有差异。可能从库执行比较慢。

幻读

一个事务读取2次，得到的记录条数不一致：

上图很明显的表示了这个情况，由于在会话 1 之间插入了一个新的值，所以得到的两次数据就不一样了。 就问了我的项目经历，有啥能说得出的亮点。问我的规划和对教育行业的看法。项目开发流程是啥。问我并发是多少。就聊了20分钟

隔离级别：（2月16头条问过）

  1. 读未提交：就是他能读取到别人未提交的内容。可以读取到未提交的内容。【会出现脏读的情况】
  2. 读提交；也就是只能读取到别人提交后的内容。别人提交后才能读。【会出现不可重复读的情况】
  3. 可重复度：就是前后读取时一致的。事务执行过程中的数据，跟执行后读取到的数据是一致的。【会出现幻读的情况】
  4. 串行化：就是事务吧，这种的话，就是效率会比较慢。写会加写锁，读会加读锁。读写冲突的时候，必须等到前一个事务结束之后，才能之后后一个事务。（加锁， 来避免访问。）【会出现超时和锁竞争的情况】

锁的类型

全局锁，用来做全库逻辑备份。。Flush tables with read lock 这个是一个全局锁。

表锁，表级锁，lock tables t1 read, t2 write; unlock tables

表锁的语法是 lock tables … read/write

另一类表级的锁是 MDL（metadata lock)(默认会启动)

如果想要拿到表的结构，可以选择等待多长时间，如果等待能拿到的话，最好。拿不到的话，也不会阻塞。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

行锁

一个SQL语句的执行过程

逻辑优化，是优化SQL语句，使SQL语句执行起来更高效。

优化器，会判断扫描行数，会选择一个行数，最小的方式，去进行查询。 会通过抽样的方式来进行。

innodb会默认选n个数据页去进行采样，然后乘以索引的数据页的数量， 就会得到索引的数量。

当数据页变更的数量超过1/10之一的数据，会重新计数。

删除数据之后

标记为可复用，alter table t engine = innodb; 可以重建表。

mysql where in (几个) where in (几万个) 有什么区别

mysql --help | grep max-allowed-packet mysql: [Warning] World-writable config file '/usr/local/etc/my.cnf' is ignored. --max-allowed-packet=# max-allowed-packet 16777216

in 没有大小限制。但是受max-allowed-packet的限制，最多也就2000个吧

数据库设计规范

  1. 命名规范
  2. 索引设计，命名要规范
  3. 数据类型选择
  4. 数据表命名规范，表与表之间相同的字段，命名要一致。
  5. 为每个表创建，主键索引。
  6. 根据需要，尽量使用覆盖索引，能加快查询效率。
  7. 索引字段不要过长，因为索引页占用空间。
  8. 尽量不使用唯一索引吧，在业务端去进行处理，保证数据唯一。内存操作，要比读取磁盘io要快。

索引

对常用的字段上面加索引

不建议主键的长度过长。

MySQL的优化

可以通过explain来分析语句的执行效率

可以加索引

可以分库分表

最左匹配原则？

where a = 1 and b=1 and c = 1.  能命中abc
where a = 1 and b > 1 and c = 1 不能命中c 因为b是范围索引。范围索引的话，意味着b可能是无序的。
where a > 1 and b = 1 and = 1  bc不能命中索引，因为范围查询是不能命中索引的。

6、mysql的悲观锁和乐观锁区别和应用，ABA问题的解决

7、mysql索引的底层B+树，说说为什么使用B+树，跟红黑树有什么区别，B树和B+树的区别？

主要考虑的是IO影响吧。因为B+ 树只有叶子节点存储数据，B树内部也存储数据。在查询相同数据量的情况下，B树高度更高，IO次数更多，然后只能一点点加载数据页。 B树的话，所有的节点都是数据地址。需要在内部节点和叶子之间去查询数据。b树的分支节点也有数据。 b树范围查询只能中序遍历。

B+树只有叶子节点数据，而且叶子节点之间由链表构成的，在叶子节点直接顺序查询会比较快。b+树的数据都集中在叶子节点。分支节点只负责索引。b+树的层高 会小于 B树 平均的Io次数会远大于 B+树（因为B+树是顺序查找）b+树更擅长范围查询。叶子节点 数据是按顺序放置的双向链表。 b+树可以把索引完全加载至内存中。支持多路，多路的好处：可以每次只加载一个节点的数据进去，因为内存的容量是有限的。【这个就是多路的好处了

普通索引和覆盖索引

普通所以的话，需要先查询出主键id，还得需要回表一次。覆盖索引的话，不需要回表。

9、mysql如果发生了抖动，怎么排查问题

  1. 在flush
  2. redo log 满了需要刷一下数据
  3. 内存不够了，需要刷一下数据
  4. MySQL在空闲的时候，需要刷一下数据
  5. MySQL在关闭的时候需要刷一下数据

explain

https://www.cnblogs.com/tufujie/p/9413852.html 其实主要考察的就是

select_type

all 全表查询。index是使用的索引。range也相当于是范围查询，比index好一些。ref是使用了普通索引。ref_eq说明是使用了主键索引或者是唯一索引。const应该是对一个主键进行了where查询

keys 用到的索引

possible_key 可能用到的索引

extra 详情吧？？

  1. Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
  2. Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
  3. Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
  4. Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
  5. Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
  6. Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
  7. No tables used：Query语句中使用from dual 或不含任何from子句

rows 影响了多少行。

死锁

多个事务在同一资源上相互占用，并请求锁定对方占用资源，从而导致恶性循环的现象 InnoDB 目前处理方法：将持有最少行级排他锁的事务进行回滚

MySQL数据库中的字段类型varchar和char的主要区别是什么?

Varchar是变长，节省存储空间，char是固定长度。查找效率要char型快，因为varchar是非定长，必须先查找长度，然后进行数据的提取，比char定长类型多了一个步骤，所以效率低一些。

列举出常见的两种mysql存储引擎，及它们有什么区别？

答：MyISAM，InnoDB 区别：

  • • MyISAM是非事务安全型的，而InnoDB是事务安全型的。
  • • MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
  • • MyISAM支持全文类型索引，而InnoDB不支持全文索引。
  • • MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
  • • MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。

InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）

写出mysql中，插入数据，读出数据，更新数据的语句

INSERT INTO 表名 VALUES (””,””)； SELECT * FROM 表名；。 UPDATE 表名 SET 字段名1=’a’,字段名2=’b’ WHERE 字段名3=’c’；。

写入数据时，聚簇索引所在的列的内容是随机的，会引起什么性能问题？

聚簇索引情况下，写入数据时，插入速度严重依赖插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。

基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。最好是重建索引，会比较好。

alter table t engine = innodb;

请描述一下mysql主从服务器之间是如何同步数据的，什么样的sql会造成主从无法正确同步？

从库生成两个线程，一个I/O线程，一个SQL线程；

i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；

主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog； SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

mysql中创建表user的脚本？

user(uid,name,groupid)其中groupid是外键
create table user(
    uid int not null auto_increment,
    name var100) not null,
    groupid int not null，
    primary key(uid),
    foreign key(groupid) references group(groupid) on cascade
)

mysql命令行下连接数据库，选择数据库test，查询表user结构，查询数据库test的所有表信息？

mysql -u root -p （连接本地mysql数据库，提示输入密码）
 mysql -u root -h db.imainary.com -p (连接远程数据库，提示输入密码)
• 连接之后到mysql>命令提示符下：
mysql>use test（选择test数据库）
mysql>connect test localhost(连接到本地数据库test)
mysql>describe user(显示表user的结构)
mysql>show tables（显示数据库test的所有表信息）

php防止sql注入式攻击，用什么函数转换字符串？

htmlspecialchars addslashes(); 其实pdo也已经处理好的。

常用的mysql工具？

• phpmyadmin; Navicat for mysql

php程序中连接数据库，host是localhost,账号是root, 密码是123,查询数据库test中表user的记录并显示出来,？

$connect= mysql_connect("localhost","root","123");
$sql="select * from user";
$query=mysql_query("test",$sql,$connect);
while($result=mysql_fetch_array($query)){
    echo ...;
}
mysql_close($connect);

MySQL优化方法

  1. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。

  2. 选择合适的表字段数据类型和存储引擎，适当的添加索引。

  3. mysql库主从读写分离。

  4. 找规律分表，减少单表中的数据量提高查询速度。

  5. 添加缓存机制，比如memcached，apc等。

  6. 不经常改动的页面，生成静态页面。

  7. 书写高效率的SQL。

比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

JOIN和UNION区别

join 是两张表做交连后里面条件相同的部分记录产生一个记录集，

union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集 。

int(4)和int(11)的区别

超过4的话，其实是没有影响的， 然后使用zerofill的时候其实是能看到的吧。不满足长度的时候，会在前面补充字段。

select sum(colomn3),column2 from tableA where column1>0 group by column2 having sum(colomn3)>0 order by column2;语句的执行顺序

  1. from <left_table><join_type>
  2. on <join_condition>
  3. <join_type> join <right_table>
  4. where <where_condition>
  5. group by <group_by_list>
  6. having <having_condition>
  7. select
  8. distinct <select_list>
  9. order by <order_by_condition>
  10. limit <limit_number>

https://blog.csdn.net/jiadajing267/article/details/81269067

你常用的mysql命令有啥？

select where from updata delete drop truncate create alter insert

use table; show tables; show databases;

解决死锁。

  1. 尽量以相同的顺序访问表。

drop delete truncate

drop 删除表和数据

delete 删除数据 带where

truncate 不带where 的删除，不太安全。

慢SQL优化

  1. 数据库CPU负载高。一般是查询语句中有很多计算逻辑，导致数据库cpu负载。
  2. IO负载高导致服务器卡住。这个一般和全表查询没索引有关系。
  3. 查询语句正常，索引正常但是还是慢。如果表面上索引正常，但是查询慢，需要看看是否索引没有生效。

另一套答案：

解答思路：针对SQL语句的优化，我们不要一上来就回答添加索引，这样显得太不专业。我们可以从如下几个角度去分析：

  1. 回归到表的设计层面，数据类型选择是否合理。
  2. 大表碎片的整理是否完善。
  3. 表的统计信息是不是准确的。
  4. 审查表的执行计划，判断字段上面有没有合适的索引。
  5. 针对索引的选择性，建立合适的索引（就又涉及大表DDL的操作问题。所以说，我们要有能力把各个知识点联系起来）

order by 能用上索引么？

CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `city` varchar(16) NOT NULL,
 `name` varchar(16) NOT NULL,
 `age` int(11) NOT NULL,
 `addr` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `city` (`city`)) ENGINE=InnoDB;

这种情况下对name进行排序的话，是不会用上索引的。因为是对全文进行排序。

select city,name,age from t where city='杭州' order by name limit 1000  ;

如果认为字段值过大的话，会进行rowid排序，也就是每行，根据city 取到行数据之后。只取 id 和 name，然后去按name进行排序。这种情况下，其实是内存不够的情况。这种情况下，name是无序的，需要多一次排序的操作。

如果在city 和name上面建立联合索引的话，根据city取的值，name就是有序的，减少排序的操作。

alter table t add index city_user(city, name);

这种情况下， 不需要临时表，也不需要排序。

using index 说明使用了覆盖索引，覆盖索引的效率还是比较高的。

什么是mysql的锁机制，以及什么是死锁，以及发生死锁的场景

MySQL的锁机制，就是数据库为了保证数据的一致性而设计的面对并发场景的一种规则。最显著的特点是不同的存储引擎支持不同的锁机制，InnoDB支持行锁和表锁，MyISAM支持表锁。

  1. 表锁就是把整张表锁起来，特点是加锁快，开销小，不会出现死锁，锁粒度大，发生锁冲突的概率高，并发相对较低。
  2. 行锁就是以行为单位把数据锁起来，特点是加锁慢，开销大，会出现死锁，锁粒度小，发生锁冲突的概率低，并发度也相对表锁较高。

锁等待是指一个事务过程中产生的锁，其他事务需要等待上一个事务释放它的锁，才能占用该资源，如果该事务一直不释放，就需要继续等待下去，直到超过了锁等待时间，会报一个超时错误。

出现死锁的问题并不可怕，解决死锁通常有如下办法：

  1. 不要把无关的操作放到事务里，小事务发生冲突的概率较低。
  2. 如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样事务就会形成定义良好的查询并且没有死锁。
  3. 尽量按照索引去查数据，范围查找增加了锁冲突的可能性。
  4. 对于非常容易产生死锁的业务部分，可以尝试升级锁粒度，通过表锁定来减少死锁产生的概率。

引用： https://www.cnblogs.com/xxcn/p/9941365.html

一个大表（数据有1000w）该怎么加索引？

存在锁住表的可能哦。

建一个一样的 tpm表，给 tmp表加索引，然后两个表rename，给主表加索引，再RENAME回来，把TMP表的 新增数据在主表中没有的给INSERT回主表。

引用《mysql45讲》里面的一个留言

  1. 数据库——解决数据存储的问题
  2. WAL——解决数据一致性问题
  3. 多线程——解决性能差异的问题
  4. 锁——解决多线程并发导致数据不一致的问题
  5. 索引——解决数据查询或者操作慢的问题
  6. 日志——解决数据备份、同步、恢复等问题
  7. 数据库主备——解决数据高可用的问题
  8. 数据库读写分离——解决数据库压力的问题
  9. 数据库分库分表——解决数据量大的问题

sql 优化题

select * from  order where status in （2,3,4,5） and price>0 and channel is null limit 10;

SQL很慢，且没有索引，也不让修改表结构，该怎么办？

那就是每次通过id去查询，然后记录id值，这样的话，是能命中id这个索引的， 查询效率还是比较高的。

mysql 保存表情

MYSQL_CHARSET = 'utf8mb4' 将字符集改为utf8mb4就可以了。

按各个省份前三名的城市数量人口 MySQL

select a.province, a.city from table where (select * from table where province = a.province group by city) limit 3;

mysql实现分组查询每个班级的前三名

select a.class,a.score from student a where (select count(*) from student where a.class=class and a.score<score)<3 order by a.class, a.score desc;

MySQL如何保证 事务

https://www.cnblogs.com/jianzh5/p/11643151.html