mysql八股文
优化，索引，事务等
优化
在MySQL中，如何定位慢查询？
通常情况下，慢查询出现在聚合查询，多表查询，表数据量过大，深度分页查询等情况下，导致页面加载时间过慢，接口测试响应时间过长。
我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟；我们系统中当时采用了运维工具 ( Skywalking )，可以监测出哪个接口，最终确定是因为sql的问题；然后在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中(调试阶段)
慢查询日志记录了所有执行时间超过指定参数 (long_query_time，单位:秒，默认10秒)的所有SQL语句的日志
如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过指令/var/lib/mysql/localhost-slow.log重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
接口测试响应时间过长。如何确定就是MySQL的问题？如何找到？
接口测试响应时间过长，可以通过调试工具或者在MySQL中开启慢日志查询来确定是MySQL的问题。
开源调试工具Arthas，Promethus，Skywalking
MySQL自带慢日志，把一些执行速度慢的sql记录到一个日志文件，
MySQL自带的慢日志记录了所有执行时间超过指定参数的所有SQL语句的日志；开启慢查询日志的话，需要在MySQL的配置文件中配置。
这条SQl执行的很慢，如何优化呢?
先通过SQL执行计划看能不能找到SQL执行的慢的原因，如果是聚合查询可以尝试增加一个临时表去解决，如果是多表查询可以优化sql的结构，表数据量过大的话可以添加索引；如果是深度分页就要使用覆盖索引
如果一条sql执行很慢的话，我们通常会使用mysql自带的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和key len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了,可以尝试添加索引或修改返回字段来修复
SQL执行计划
MySQL自带的分析工具EXPLAIN
通过key和key_len检查是否命中了索引(索引本身存在是否有失效的情况)
通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复
语法：直接在select语句之前加上关键字 explain / desc，会返回一个表
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
possible key当前sql可能会使用到的索引
key 当前sql实际命中的索引
key_len 索引占用的大小
通过它们两个查看是否可能会命中索引
Extra额外的优化建议
-
Using where; Using Index：查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数护
-
Using index condition：查找使用了索引，但是需要回表查询数据
type：这条sql的连接的类型，性能由好到差为NULL、system、const、eg ref、ref、range、 index、all
了解过索引吗?
索引是帮助MySQL高效获取数据的数据结构，可以提高数据检索的效率，降低数据库的IO成本(不需要全表扫描)；通过索引列对数据进行排序，可以降低数据排序的成本，降低了CPU的消耗，
因为创建索引的时候就需要排序了
索引是帮mysql高效获取数据的数据结构，MySQL除了存储数据，数据库系统还维护着B+树这种数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法；这种数据结构就是索引。
索引的底层数据结构了解过嘛?
MySQL的InnoDB引擎采用的B+树的数据结构来存储索引；b+树阶数更多，路径更短；磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据；B+树便于扫库和区间查询，因为叶子节点是一个双向链表
因为二叉树不太稳定，可能退化成链表
红黑树可以保证平衡，但是红黑树也是二叉树，每个节点只能有两个分支，数据量过大，树的高度过高，查找数据时叶需要找很多层级，效率不高
b树是一种多叉路平衡查找树，每个节点可以有多个分支
B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，lnnoDB存储引擎就是用B+Tree实现其索引结构
【以一棵最大度数max degree为5（5阶）的bTree为例，这个b树每个节点最多存储4个key；5阶b树，5个指针存4个数】
B树与B+树对比:
磁盘读写代价B+树更低：非叶子节点只存储指，不查数据了
查询效率B+树更加稳定：查找路径差不多
B+树便于扫库和区间查询：叶子结点之间用指针连接，进行范围查询时更方便，不需要再次从根节点再查询一次
什么是聚簇索引，什么是非聚簇索引?
聚簇索引(聚集索引):数据与索引放到一起，B+树的叶子节点保存了整行数据，有且只有一个，必须有
非聚簇索引(二级索引):数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个
聚集索引选取规则:
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一 (UNIQUE)索作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
知道什么是回表查询嘛 ?
通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表
单独给字段创建的索引大部分是二级索引
在其他地方比如name添加了索引，非聚簇索引，在树的叶子结点只存储name和id，查询gender时就查不到，需要用id回聚集索引的树中查询gender，就是回表
知道什么是覆盖索引吗?
覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到
使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *
MySQL超大分页分页如何处理？
可以使用覆盖索引解决
先分页查询获取表中的id，对表的id进行排序，就能筛选出分页之后的id集合，因为id是覆盖索引，操作id的时候，效率相对来说比较高，最后通过id集合到原来的表中做关联查询，效率就会得到提升
select * from tb limit 0,10;
select * from tb limit 900000000000,10;
因为，当在进行分页查询时，如果执行 limit 9000000,10，此时需要MySQL排序前9000010 记录，仅仅返回9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大。
一般分页查询时，通过创建覆盖索引提高性能，可以通过覆盖索引加子查询进行优化
select * from tb t
(select id from tb order by id limit 900000000000,10) a
where t.id = a.id
索引创建原则有哪些？
数据量较大，且查询比较频繁的表（重要）
常作为查询条件、排序、分组的字段字段（重要）
内容区分度高
内容较长，使用前缀索引
尽量联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。（重要）
要控制索引的数量，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。（重要）
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它
针对于数据量较大，且查询比较频繁的表建立索引。 单表超过10万数据 (增加用户体验)
针对于常作为查询条件 (where)、排序 (order by)、分组(group by) 操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
【给tb_seller创建联合索引tb_seller_index，字段顺序: name，status，address】可能会失效
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪索引最有效地用于查询。
什么情况下索引会失效?
违反最左前缀法则
范围查询右边的列，不能使用索引
不要在索引列上进行运算操作，索引将失效
字符串不加单引号，造成索引失效。(类型转换)
以%开头的Like模糊查询，索引失效
索引失效的情况有很多，可以说一些自己遇到过的，不要张口就得得得说一堆背诵好的面试题(适当的思考一下，回想一下，更真实)
给tb_seller创建联合索引tb_seller_index，字段顺序: name，status，address
- 违反最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引:
explain select * from tb_seller where status ='1' and address ='北京市';
- 范围查询右边的列，不能使用索引
根据前面的两个字段 name，status 查询是走索引的，但是最后一个条件address 没有用到索引。
explain select * from tb_seller where name ='小米科技 and status >'1' and address ='北京市';
- 不要在索引列上进行运算操作，索引将失效。
explain select * from tb_seller where substring(name,3,2) ='科技';
- 字符串不加单引号，造成索引失效。
由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效
explain select * from tb_seller where status =1;
- 模糊查询可能会造成索引失效
以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
explain select sellerid, name from tb_seller where name like '%张';
谈一谈你对sql的优化的经验
表的设计优化
索引优化:创建原则和索引失效
SQL语句优化
主从复制、读写分离
分库分表
- 表的设计优化
(参考阿里开发手册《嵩山版》
比如设置合适的数值 (tinyint int bigint)，要根据实际情况选择
比如设置合适的字符串类型 (char和varchar) char定长效率高，varchar可变长度，效率稍
- SQL语句优化
SELECT语句务必指明字段名称 (避免直接使用select *)
SQL语句要避免造成索引失效的写法
尽量用union all代替union；nuion会把重复的数据过滤掉；多一次过滤，效率低
避免在where子句中对字段进行表达式操作
Join优化 能用innerjoin 就不用left join right join，如必须使用，一定要以小表为驱动
内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序
for(i=0;i<3;i++){
for(j=0;j<1000;j++){
}
}
- 主从复制，读写分离
如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。
读写分离解决的是，数据库的写入，影响了查询的效率。
MySQL其他面试题
事务的特性是什么?可以详细说一下吗?
嗯，这个比较清楚，
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
事务的特性是ACID，分别指的是: 原子性、一致性、隔离性、持久性:我举个例子:A向B转账500，转账成功，A扣除500元，B增加500元，
原子操作体现在要么都成功，要么都失败；
在转账的过程中，数据要一致，A扣除了500，B必须增加500
在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰；
持久性体现在事物提交后，要把数据持久化
原子性( Atomicity )、一致性( Consistency)、隔离性(lsolation)、持久性( Durability )
原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的操作要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
如果事务成功的完成，那么数据库的所有变化将生效。
如果事务执行出现错误，那么数据库的所有变化将会被回滚(撤销)，返回到原始状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。
一个事务的成功或者失败对于其他的事务是没有影响。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的；哪怕数据库发生异常，重启之后数据亦然存在。
并发事务带来哪些问题?--怎么解决这些问题呢?-- MySQL的默认隔离级别是?
脏读、不可重复读、幻读
解决方案：对事物进行隔离
隔离级别:读未提交、读已提交、可重复读、串行化
脏读：一个事务读到另外一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted 未提交读 | |||
| Read committed 读已提交 | 1 | ||
| Repeatable Read(默认)可重复读 | 1 | 1 | |
| Serializable串行化 | 1 | 1 | 1 |
串行化是一个事物提交之后其他的事物才能接着运行，几乎放弃了并发，效率低
事务隔离级别越高，数据越安全，但是性能越低，默认可重复读
undo log和redo log的区别?
都属于MySQL的日志文件
redo log记录的是数据页的物理变化，服务宕机可用来同步数据；
undo log记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据；
redolog保证了事务的持久性，undolog保证了事务的原子性和一致性
缓冲池(buffer pool):主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
数据页(page):是lnnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据
增删改数据的时候，会先在【内存结构】缓冲池Buffer pool中记录，然后同步到【磁盘结构】磁盘数据页(xxx.ibd)中，先操作Buffer pool，看有没有需要操作的数据，没有的话就从磁盘把数据加载到内存，把某一页的数据加入到缓冲池，操作内存效率高，操作完后按一定频率把数据同步到磁盘，减少磁盘IO，加快处理速度。
当前已经操作完的数据，在内存中存储的页还没有存储到磁盘，被称为脏页，脏页需要同步到磁盘中才算是持久化了，但是同步过程中服务器宕机了，同步失败，内存中的数据可能会消失，数据就可能丢失，已经操作完的数据也丢失了，就违背了持久化，
redo log重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲(redo log buffer)以及重做日志文件(redo log file)，前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘,发生错误时，进行数据恢复使用。
当有增删改的时候，buffer pool已经发生了变化，同时redolog buffer中记录这些数据页的变化，一旦redolog buffer发生了变化，就会同步的把数据记录到磁盘文件redolog file中；如果同步失败，就会从redolog中恢复数据
为什么不直接从buffer pool同步到磁盘？
在操作时可能会有多条记录，如果同步刷新，在保存数据到磁盘的时候，都是随机的磁盘IO，性能比较低，使用redo log之后，进行数据同步的时候是顺序的磁盘IO，因为日志文件都是追加的，顺序的磁盘IO性能就提升了很多
以上也叫WAL机制，write ahead logging
当脏页数据能正常同步到磁盘时，redolog就没什么用了，每隔一段时间清理一下，磁盘中有两份redolog日志，是循环写的
undo log回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚 和 MVCC(多版本并发控制)。它是逻辑日志
可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然
当update一条记录时，它记录一条对应相反的update记录。当执行rolback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
undo log可以实现事务的一致性和原子性
好的，事务中的隔离性是如何保证的呢?
事务的隔离性是由锁和mvcc实现的。
排他锁(如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁)
主从同步原理，主从如何进行同步的
MySQL主从复制的核心就是二进制日志binlog日志，记录了所有的 DDL(数据定义语言)DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句.
主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中，此时从库有一个IOthread线程专门从主库的binlog日志中读取数据，写入到从库的中继日志Relay Log。再由从库的SQLthread线程读取中继日志的文件，把里面的命令重新执行一下，然后就保持同步了
你们项目用过分库分表吗？
业务介绍
1，根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）2，达到了什么样的量级(单表1000万或超过20G)
具体拆分策略
1，水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题2，水平分表，解决单表存储和性能的问题
3，垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数
4，垂直分表，冷热数据分离，多表互不影响
同时大量访问数据库，主从复制读写分离只是减轻访问压力，不能解决存储压力
分库分表，解决存储压力
时机
- 一个数据量较大业务(请求数多或业务累积大；单表1000万或超过20G)
- 优化已解决不了性能问题 (主从读写分离、查询索引...
- IO瓶颈 （磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）
具体拆分策略
- 水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题【中间件】
- 水平分表，解决单表存储和性能的问题【中间件】
- 垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数【微服务】
- 垂直分表，冷热数据分离，多表互不影响
优化
在MySQL中，如何定位慢查询？
通常情况下，慢查询出现在聚合查询，多表查询，表数据量过大，深度分页查询等情况下，导致页面加载时间过慢，接口测试响应时间过长。
我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟；我们系统中当时采用了运维工具 ( Skywalking )，可以监测出哪个接口，最终确定是因为sql的问题；然后在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中(调试阶段)
慢查询日志记录了所有执行时间超过指定参数 (long_query_time，单位:秒，默认10秒)的所有SQL语句的日志
如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过指令/var/lib/mysql/localhost-slow.log重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
接口测试响应时间过长。如何确定就是MySQL的问题？如何找到？
接口测试响应时间过长，可以通过调试工具或者在MySQL中开启慢日志查询来确定是MySQL的问题。
开源调试工具Arthas，Promethus，Skywalking
MySQL自带慢日志，把一些执行速度慢的sql记录到一个日志文件，
MySQL自带的慢日志记录了所有执行时间超过指定参数的所有SQL语句的日志；开启慢查询日志的话，需要在MySQL的配置文件中配置。
这条SQl执行的很慢，如何优化呢?
先通过SQL执行计划看能不能找到SQL执行的慢的原因，如果是聚合查询可以尝试增加一个临时表去解决，如果是多表查询可以优化sql的结构，表数据量过大的话可以添加索引；如果是深度分页就要使用覆盖索引
如果一条sql执行很慢的话，我们通常会使用mysql自带的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和key len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了,可以尝试添加索引或修改返回字段来修复
SQL执行计划
MySQL自带的分析工具EXPLAIN
通过key和key_len检查是否命中了索引(索引本身存在是否有失效的情况)
通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复
语法：直接在select语句之前加上关键字 explain / desc，会返回一个表
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
possible key当前sql可能会使用到的索引
key 当前sql实际命中的索引
key_len 索引占用的大小
通过它们两个查看是否可能会命中索引
Extra额外的优化建议
-
Using where; Using Index：查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数护
-
Using index condition：查找使用了索引，但是需要回表查询数据
type：这条sql的连接的类型，性能由好到差为NULL、system、const、eg ref、ref、range、 index、all
了解过索引吗?
索引是帮助MySQL高效获取数据的数据结构，可以提高数据检索的效率，降低数据库的IO成本(不需要全表扫描)；通过索引列对数据进行排序，可以降低数据排序的成本，降低了CPU的消耗，
因为创建索引的时候就需要排序了
索引是帮mysql高效获取数据的数据结构，MySQL除了存储数据，数据库系统还维护着B+树这种数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法；这种数据结构就是索引。
索引的底层数据结构了解过嘛?
MySQL的InnoDB引擎采用的B+树的数据结构来存储索引；b+树阶数更多，路径更短；磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据；B+树便于扫库和区间查询，因为叶子节点是一个双向链表
因为二叉树不太稳定，可能退化成链表
红黑树可以保证平衡，但是红黑树也是二叉树，每个节点只能有两个分支，数据量过大，树的高度过高，查找数据时叶需要找很多层级，效率不高
b树是一种多叉路平衡查找树，每个节点可以有多个分支
B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，lnnoDB存储引擎就是用B+Tree实现其索引结构
【以一棵最大度数max degree为5（5阶）的bTree为例，这个b树每个节点最多存储4个key；5阶b树，5个指针存4个数】
B树与B+树对比:
磁盘读写代价B+树更低：非叶子节点只存储指，不查数据了
查询效率B+树更加稳定：查找路径差不多
B+树便于扫库和区间查询：叶子结点之间用指针连接，进行范围查询时更方便，不需要再次从根节点再查询一次
什么是聚簇索引，什么是非聚簇索引?
聚簇索引(聚集索引):数据与索引放到一起，B+树的叶子节点保存了整行数据，有且只有一个，必须有
非聚簇索引(二级索引):数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个
聚集索引选取规则:
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一 (UNIQUE)索作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
知道什么是回表查询嘛 ?
通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表
单独给字段创建的索引大部分是二级索引
在其他地方比如name添加了索引，非聚簇索引，在树的叶子结点只存储name和id，查询gender时就查不到，需要用id回聚集索引的树中查询gender，就是回表
知道什么是覆盖索引吗?
覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到
使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *
MySQL超大分页分页如何处理？
可以使用覆盖索引解决
先分页查询获取表中的id，对表的id进行排序，就能筛选出分页之后的id集合，因为id是覆盖索引，操作id的时候，效率相对来说比较高，最后通过id集合到原来的表中做关联查询，效率就会得到提升
select * from tb limit 0,10;
select * from tb limit 900000000000,10;
因为，当在进行分页查询时，如果执行 limit 9000000,10，此时需要MySQL排序前9000010 记录，仅仅返回9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大。
一般分页查询时，通过创建覆盖索引提高性能，可以通过覆盖索引加子查询进行优化
select * from tb t
(select id from tb order by id limit 900000000000,10) a
where t.id = a.id
索引创建原则有哪些？
数据量较大，且查询比较频繁的表（重要）
常作为查询条件、排序、分组的字段字段（重要）
内容区分度高
内容较长，使用前缀索引
尽量联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。（重要）
要控制索引的数量，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。（重要）
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它
针对于数据量较大，且查询比较频繁的表建立索引。 单表超过10万数据 (增加用户体验)
针对于常作为查询条件 (where)、排序 (order by)、分组(group by) 操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
【给tb_seller创建联合索引tb_seller_index，字段顺序: name，status，address】可能会失效
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪索引最有效地用于查询。
什么情况下索引会失效?
违反最左前缀法则
范围查询右边的列，不能使用索引
不要在索引列上进行运算操作，索引将失效
字符串不加单引号，造成索引失效。(类型转换)
以%开头的Like模糊查询，索引失效
索引失效的情况有很多，可以说一些自己遇到过的，不要张口就得得得说一堆背诵好的面试题(适当的思考一下，回想一下，更真实)
给tb_seller创建联合索引tb_seller_index，字段顺序: name，status，address
- 违反最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引:
explain select * from tb_seller where status ='1' and address ='北京市';
- 范围查询右边的列，不能使用索引
根据前面的两个字段 name，status 查询是走索引的，但是最后一个条件address 没有用到索引。
explain select * from tb_seller where name ='小米科技 and status >'1' and address ='北京市';
- 不要在索引列上进行运算操作，索引将失效。
explain select * from tb_seller where substring(name,3,2) ='科技';
- 字符串不加单引号，造成索引失效。
由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效
explain select * from tb_seller where status =1;
- 模糊查询可能会造成索引失效
以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
explain select sellerid, name from tb_seller where name like '%张';
谈一谈你对sql的优化的经验
表的设计优化
索引优化:创建原则和索引失效
SQL语句优化
主从复制、读写分离
分库分表
- 表的设计优化
(参考阿里开发手册《嵩山版》
比如设置合适的数值 (tinyint int bigint)，要根据实际情况选择
比如设置合适的字符串类型 (char和varchar) char定长效率高，varchar可变长度，效率稍
- SQL语句优化
SELECT语句务必指明字段名称 (避免直接使用select *)
SQL语句要避免造成索引失效的写法
尽量用union all代替union；nuion会把重复的数据过滤掉；多一次过滤，效率低
避免在where子句中对字段进行表达式操作
Join优化 能用innerjoin 就不用left join right join，如必须使用，一定要以小表为驱动
内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序
for(i=0;i<3;i++){
for(j=0;j<1000;j++){
}
}
- 主从复制，读写分离
如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。
读写分离解决的是，数据库的写入，影响了查询的效率。
MySQL其他面试题
事务的特性是什么?可以详细说一下吗?
嗯，这个比较清楚，
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
事务的特性是ACID，分别指的是: 原子性、一致性、隔离性、持久性:我举个例子:A向B转账500，转账成功，A扣除500元，B增加500元，
原子操作体现在要么都成功，要么都失败；
在转账的过程中，数据要一致，A扣除了500，B必须增加500
在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰；
持久性体现在事物提交后，要把数据持久化
原子性( Atomicity )、一致性( Consistency)、隔离性(lsolation)、持久性( Durability )
原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的操作要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
如果事务成功的完成，那么数据库的所有变化将生效。
如果事务执行出现错误，那么数据库的所有变化将会被回滚(撤销)，返回到原始状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。
一个事务的成功或者失败对于其他的事务是没有影响。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的；哪怕数据库发生异常，重启之后数据亦然存在。
并发事务带来哪些问题?--怎么解决这些问题呢?-- MySQL的默认隔离级别是?
脏读、不可重复读、幻读
解决方案：对事物进行隔离
隔离级别:读未提交、读已提交、可重复读、串行化
脏读：一个事务读到另外一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted 未提交读 | |||
| Read committed 读已提交 | 1 | ||
| Repeatable Read(默认)可重复读 | 1 | 1 | |
| Serializable串行化 | 1 | 1 | 1 |
串行化是一个事物提交之后其他的事物才能接着运行，几乎放弃了并发，效率低
事务隔离级别越高，数据越安全，但是性能越低，默认可重复读
undo log和redo log的区别?
都属于MySQL的日志文件
redo log记录的是数据页的物理变化，服务宕机可用来同步数据；
undo log记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据；
redolog保证了事务的持久性，undolog保证了事务的原子性和一致性
缓冲池(buffer pool):主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
数据页(page):是lnnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据
增删改数据的时候，会先在【内存结构】缓冲池Buffer pool中记录，然后同步到【磁盘结构】磁盘数据页(xxx.ibd)中，先操作Buffer pool，看有没有需要操作的数据，没有的话就从磁盘把数据加载到内存，把某一页的数据加入到缓冲池，操作内存效率高，操作完后按一定频率把数据同步到磁盘，减少磁盘IO，加快处理速度。
当前已经操作完的数据，在内存中存储的页还没有存储到磁盘，被称为脏页，脏页需要同步到磁盘中才算是持久化了，但是同步过程中服务器宕机了，同步失败，内存中的数据可能会消失，数据就可能丢失，已经操作完的数据也丢失了，就违背了持久化，
redo log重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲(redo log buffer)以及重做日志文件(redo log file)，前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘,发生错误时，进行数据恢复使用。
当有增删改的时候，buffer pool已经发生了变化，同时redolog buffer中记录这些数据页的变化，一旦redolog buffer发生了变化，就会同步的把数据记录到磁盘文件redolog file中；如果同步失败，就会从redolog中恢复数据
为什么不直接从buffer pool同步到磁盘？
在操作时可能会有多条记录，如果同步刷新，在保存数据到磁盘的时候，都是随机的磁盘IO，性能比较低，使用redo log之后，进行数据同步的时候是顺序的磁盘IO，因为日志文件都是追加的，顺序的磁盘IO性能就提升了很多
以上也叫WAL机制，write ahead logging
当脏页数据能正常同步到磁盘时，redolog就没什么用了，每隔一段时间清理一下，磁盘中有两份redolog日志，是循环写的
undo log回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚 和 MVCC(多版本并发控制)。它是逻辑日志
可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然
当update一条记录时，它记录一条对应相反的update记录。当执行rolback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
undo log可以实现事务的一致性和原子性
好的，事务中的隔离性是如何保证的呢?
事务的隔离性是由锁和mvcc实现的。
排他锁(如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁)
主从同步原理，主从如何进行同步的
MySQL主从复制的核心就是二进制日志binlog日志，记录了所有的 DDL(数据定义语言)DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句.
主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中，此时从库有一个IOthread线程专门从主库的binlog日志中读取数据，写入到从库的中继日志Relay Log。再由从库的SQLthread线程读取中继日志的文件，把里面的命令重新执行一下，然后就保持同步了
你们项目用过分库分表吗？
业务介绍
1，根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）2，达到了什么样的量级(单表1000万或超过20G)
具体拆分策略
1，水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题2，水平分表，解决单表存储和性能的问题
3，垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数
4，垂直分表，冷热数据分离，多表互不影响
同时大量访问数据库，主从复制读写分离只是减轻访问压力，不能解决存储压力
分库分表，解决存储压力
时机
- 一个数据量较大业务(请求数多或业务累积大；单表1000万或超过20G)
- 优化已解决不了性能问题 (主从读写分离、查询索引...
- IO瓶颈 （磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）
具体拆分策略
- 水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题【中间件】
- 水平分表，解决单表存储和性能的问题【中间件】
- 垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数【微服务】
- 垂直分表，冷热数据分离，多表互不影响
分库之后的问题
- 分布式事务一致性问题
- 跨节点关联查询，分页，排序函数
- 主键避重
解决
- 应用程序和数据库中间加一个分库分表中间件MyCat关联
分库之后的问题
- 分布式事务一致性问题
- 跨节点关联查询，分页，排序函数
- 主键避重
解决
- 应用程序和数据库中间加一个分库分表中间件MyCat关联
