一、count函数
count(1)、count(*)、count(field)三者之间的区别
T1表中有如下数据：
| t1 | t2 | t3 | t4 |
|---|---|---|---|
| 1 | a | 1 | |
| 2 | b | ||
| 3 | 3 | 0 |
SELECT COUNT(1) FROM T1; -- 3
SELECT COUNT(*) FROM T1; -- 3
SELECT COUNT(t1) FROM T1; -- 2
SELECT COUNT(t4) FROM T1; -- 1
count(*) 和 count(1)会统计NULL值
当count(field)时 eg：count(t4)则会过滤NULL值
当时用count()函数时，count(*) 和count(1)都会统计NULL值，而count(字段)时则不会统计NULL值
在MyISAM中，会有单独的地方记录表的行，所以在MyISAM中执行count()是比较快的，当然前提条件是sql语句中没有where条件，因为MyISAM记录的就是无条件下的表中总共的行数。
lmnodb中没有这种机制，因为lnnodb支持事务，事务又有不同的隔离级别，对于同一个表来说，不同的事务可能同时在操这表，并目每个事务是独立的，A事务插入了一条数据，B事务可能是不需要知道的，这样就导致Innodb不能像MyISAM那样在某一个地方记录记录总行数了。
那么Innodb中的count()是怎么执行的呢? 会利用索引。
比在执行ount()时，会选择表中的某一个索引，因为索B+树中就记录了表中的所有数据行（每行数的某些字段），所以利用索引顶可以更快的统计出总行数
- 1.
MyISAM中count(*)比较快，因为可以直接取到MyISAM帮我们统计的总行数- 2.
Innodb中count(*)会选择索引，然后利用索引统计出来总行数- 3.
count(1)和count(*)是一样的，不管是Innodb还是MyISAM- 4.
count(某个字段)会选择该字段可用的索引进行统计，如果没有则进行全部扫描，只要是count(某个字段)就会过null值，不管走没走索引
二、索引失效场景
对字段进行操作导致索引失效
1、对字段进行了操作
2、字段的类型转换（字符类型会全部转为0，之后再与1比较，相当耗时）
3、左模糊和全模糊查询
4、is null可以使用索引，is not null无法使用索引
5、不等于(!= 或者<>)导致索引失效
-- 1、对字段进行了操作,索引失效
explain select * from T1 where t1+1 = 1;
explain select * from T1 where t1-1 = 1;
-- 2、字段的类型转换,t3的类型为字符型,此时索引失效
explain select * from T1 where t3 = 1; -- 0
select 'a' = 0; -- 1
select 'b' = 0; -- 1
select 'c' = 1; -- 0
-- MySQL中，所有的字符都将会被转成0
-- 3、左模糊和全模糊查询,索引失效
explain select * from T1 where t3 like '%a' ;
explain select * from T1 where t3 like '%a%' ;
-- 表中没有t3中并没有'f'值，但是执行SQL后最后一条数据会被查询出来，因为'f'被转成了0
select * from T1 where t3 = 'f'; -- 1
三、聚簇索引和非聚簇索引
- 聚簇索引: 将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即: 只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的
- 非聚簇索引: 叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。
-
聚簇索引优势:
- 1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询(非覆盖索引的情况下) 效率要高
- 2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的
- 3、聚簇索引适合用在排序的场合，非聚簇索引不适合
-
聚簇索引劣势:
- 1、维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page sp1it)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以
- 2、表因为使用UUId(随机ID)作为主键，使数据存储稀疏，这就会出现聚族索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键
- 3、如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值;过长的主键值，会导致非叶子节点占用占用更多的物理空间
-
innoDB中一定有主键，主键一定是聚簇索引，不手动设置、则会使用unique索引l，没有unique索引，则会使用数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值
-
MVISM使用的是非聚簇索引，没有聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MISAM占优势些，因为索引所占空间小这些操作是需要在内存中完成的。
四、索引设计的原则?
查询更快、占用空间更小
- 1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列
- 2.基数较小的类，索引效果较差，没有必要在此列建立索引
- 3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
- 4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
- 5.定义有外键的数据列一定要建立索引。
- 6.更新频繁字段不适合创建索引
- 7.若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
- 8.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
- 9.对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
- 10.对于定义为text、image和bit的数据类型的列不要建立索引。
五、最左前缀原则
当一个SQL想要利用索引是，就一定要提供该索引所对应的字段中最左边的字段，也就是排在最前面的字段，比如针对a,c三个字段建立了一个联合索引，那么在写一个sql时就一定要提供a字段的条件，这样才能用到联合索引，这是由于在建立a,b,c三个字段的联合索引时，底层的B+树是按照a.b,三个字段从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则
-- 联合索引 INDX_t2_t3_t4
-- 使用到索引
select * from T1 where t1 = '1' and t2 = '1' ;
-- 不会使用索引
select * from T1 where t3 = '1' and t4 = '1' ;
-- 最左匹配原则：在where条件中，联合索引中（t2_t3_t4）最左侧的索引t2被使用才会走索引，否则不会走索引
六、事物特性和隔离级别
事务基本特性ACID分别是：
原子性：指的是一个事务中的操作要么全部成功，要么全部失败。一致性：指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设A只有90块，支付之前我们数据库里的数据都是符合约束的,但是如果事务执行成功了,我们的数据库数据就破坏约束了,因此事务不能成功,这里我们说事务提供了一致性的保证隔离性：指的是一个事务的修改在最终提交前，对其他事务是不可见的。持久性：指的是一旦事务提交，所做的修改就会永久保存到数据库中。隔离性有4个隔离级别：
read uncommit 读未提交
- 可能会读到其他事务未提交的数据，也叫做脏读。用户本来应该读取到id=1的用户age应该是10，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。
read commit 读已提交
- 两次读取结果不一致，叫做不可重复读。不可重复读解决了脏读的问题，他只会读取已经提交的事务，用户开启事务，读取id=1的用户数据，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。
repeatable read 可重复复读
- 这是mysgl的默认级别，就是每次读取结果都一样，但是有可能产生幻读。
serializable 串行：一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。
- 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
- 不可重复读(Non-repeatable read)：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中中间插入了一个事务更新了原有的数据。
- 幻读(Phantom Read)：在一个事务的两次查询中数据不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
七、ACID靠什么保证的?
A原子性： 由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性： 由其他三大特性保证、程序代码要保证业务上的一致性
I隔离性： 由MVCC来保证
D持久性： 由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redolog恢复 InnoDB redo log 写盘，InnoDB 事务进入prepare状态。
如果前面prepare成功，binlog 写盘，再继续将事务日志持久化到 binlog，如果持久化成功，那么 InnoDB 事务 则进入commit状态(在 redo log 里面写一个 commit 记录)
redolog的刷盘会在系统空闲时进行
八、mysql主从同步原理
- mysql主从同步的过程:
- Mysql的主从复制中主要有三个线程:
master (binlog dump thread)、slave (I/0 thread、SQLthread)，Master一条线程和Slave中的两条线程。- 主节点 binlog：主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
- 主节点 log dump 线程：当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
- 从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。
- 从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。
注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。
由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。
全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的性能会受到严重影响。
半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。
九、MyISAM和InnoDB的区别
MyISAM:
- 不支持事务，但是每次查询都是原子的；
- 支持表级锁，即每次操作是对整个表加锁；
- 存储表的总行数；
- 一个MYISAM表有三个文件: 索引文件、表结构文件、数据文件；
- 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯代。
InnoDb:
- 支持ACID的事务，支持事务的四种隔离级别；
- 支持行级锁及外键约束:因此可以支持写并发；
- 不存储总行数；
- 一个InnoDb引擎存储在一个文件空间(共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里)，也有可能为多个(设置为独立表空，表大小受操作系统文件大小限制，一般为2G)，受操作系统文件大小的限制；
- 主键索采用聚集索引(索引的数据域存储数据文件本身)，辅索引的数据域存储主键的值，因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。
十、mysql中索引类型及对数据库的性能的影响
普通索引： 允许被索引的数据列包含重复的值。
唯一索引： 可以保证数据记录的唯一性。
主键： 是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY 来创建。
联合索引： 索引可以覆盖多个数据列，如像INDEX(columnA,columnB)索引。
全文索引： 通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引 。
索引可以极大的提高数据的查询速度。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
对数据库的性能的影响
但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。（影响性能）
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。
十一、B树和B+树的区别，为什么Mysql使用B+树
B树的特点：
- 1，节点排序
- 2.一个节点了可以存多个元素，多个元素也排序了
B+树的特点：
- 1.拥有B树的特点
2- .叶子节点之间有指针- 3，非叶子节点上的元素在叶子节点上都几余了，也就是叶子节点中存储了所有的元素，并且排好顺序。
Mysql索引使用的是B+树，因为索引是用来加快查询的，而B+树通过对数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元素，从而可以使得B+树的高度不会大高，在MySQL中一个Innodb页就是一个B+树节点，一个Innodb页默认16kb，所以一般情况下一颗两层的B+树可以存2000万行左右的数据，然后通过利用B+树叶子节点存储了所有数据并且进行了排序，并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等SQL语句。
十二、Innodb是如何实现事务的
Innodb通过Buffer Pool，LogBufer，Redo Log，Undo Log来实现事务，以一个update语句为例：
- Innodb在收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在Buffer Pool中。
- 执行update语句，修改Buffer Pool中的数据，也就是内存中的数据。
- 针对update语句生成一个RedoLog对象，并存入LogBuffer中。
- 针对update语何生成undolog日志，用于事务回滚。
- 如果事务提交，那么则把RedoLog对象进行持久化，后续还有其他机制将Buffer Pol中所修改的数据页持久化到磁盘中。
- 如果事务回滚，则利用undolog日志进行回滚。
十三、Explain中的字段含义
十四、Mysql慢查询的优化
- 检查是否走了索引，如果没有则优化SQL利用索引
- 检查所利用的索引，是否是最优索引
- 检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
- 检查表中数据是否过多，是否应该进行分库分表了
- 检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源
十五、昨天、今天、明天
-- 昨天
select DATE_SUB(curdate(),INTERVAL 1 DAY) ;
-- 今天
select DATE_SUB(curdate(),INTERVAL 0 DAY) ;
-- 明天
select DATE_SUB(curdate(),INTERVAL -1 DAY) ;
-- 前一个小时时间
select date_sub(now(), interval 1 hour);
-- 后一个小时时间
select date_sub(now(), interval -1 hour);
-- 前30分钟时间
select date_add(now(),interval -30 minute)
-- 后30分钟时间
select date_add(now(),interval 30 minute)
-- 当前日期
select curdate();
1029
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
