什么是DDL、DML、DQLDDL数据定义语言用来定义创建删除修改数据库对象数据库、表、字段DML数据操纵语言用来对数据库表中的数据进行增删查改insert、delete、select、updateDQL数据库查询语言用来查询数据库表中的记录DCL数据控制语言用来创建数据库用户、控制数据库访问权限等常用关键字有 GRANT、REVOKE 等如何定位慢查询慢查询出现的原因 多表查询、表数据量大、聚合查询、深度分页等等使用开源工具调试工具arthas运维工具Prometheus、Skywalking自带慢查询日志mysql配置文件配置slow_query_log 1slow_query_time 10s表示当sql执行时间10s默认视为慢查询并记录sql执行语句日志使用explain或desc 查询sql的执行计划需要关注的字段字段描述possible_key可能用到的索引key实际命中的索引key_len命中索引的大小extra额外的优化建议见表1type连接类型见表2表1extra字段描述Using where; Using Index查找使用了索引需要的数据都在索引列中能找到不需要回表查询数据Using index condition查找使用了索引但是需要回表查询数据表2上到下性能好到差尽量避免index、alltype字段描述NULL没有使用到表systemmysql系统表const主键索引查询eq_ref主键索引查询或唯一索引查询ref索引查询range走索引查询 但是是范围查询index索引树扫描all全表扫描了解过索引吗什么是索引索引是帮助mysql高效获取数据的数据结构有序。mysql InnoDB索引的底层实现是B树。索引的数据结构B树前言B树是一种多路平衡查找树相对于二叉树b树每个节点可以有多个分支以一颗最大度数max-degree为5的b树为例该b树的每个节点最多可以存储4个keyb树是b树的优化非叶子节点只存储指针不存储数据叶子节点存储数据。叶子节点之间是一个双向循环链表。MySQL的InnoDB引擎采用的B树的数据结构来存储索引的优点1、阶数更多路径更短2、磁盘读写代价B树更低非叶子节点只存储指针叶子阶段存储数据3、B树便于扫库和区间查询叶子节点是一个双向链表面试问题什么是索引回答 索引(index)是帮助MySQL高效获取数据的数据结构(有序)提高数据检索的效率降低数据库的IO成本不需要全表扫描)通过索引列对数据进行排序降低数据排序的成本降低了CPU的消耗。什么是聚簇索引什么是非聚簇索引聚簇索引将数据存储与索引放到了一块索引结构的叶子节点保存了行数据必须有且只有一个物理上有序。聚集索引选取规则如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一(UNIQUE)索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。非聚簇索引将数据与索引分开存储索引结构的叶子节点指向了数据对应的位置id可以多个逻辑上有序物理上无序聚集索引和非聚集索引详解回表查询通过非聚集索引找到对应的主键值然后拿着主键值到聚集索引中查找整行的数据这个过程就是回表查询。上述图中select * … 通过二级索引查询不到全部的数据需要回表查询因此尽量避免使用select *覆盖索引是指查询使用了索引且需要返回的列在该索引中全部能找到不需要回表查询。索引创建原则△ 针对数据量大且查询频繁的表建立索引单表数据超过10w△ 针对常作为查询条件where、排序order by、分组group by操作的字段建立索引 *尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高索引的效率越高若是字符串类型的字段字段的长度较长可以建立前缀索引△ 尽量使用联合索引减少单索引联合索引很多时候是覆盖索引节省内存空间避免回表查询提高效率。△ 控制索引的数量索引不是越多越好索引越多维护索引结构的代价就越大会影响增删改的效率。若索引列不能存储null值请在创建表时使用not null约束。当优化器知道每列是否包含null值时它可以更好的确定哪个索引最有效地用于查询。索引失效情场景假设namestatusaddress字段是一个组合索引 idx_nsa1、违反最左前缀法则最左前缀法则是指查询从索引的最左列开始不跳过索引中的列# 符合最左前缀法则情况wherenamexxxwherenamexxxandstatus1wherenamexxxandaddressxxxwherenamexxxandstatus1andaddressxx#违反最左前缀法则情况wherestatus1andaddressxx2、范围查询右边的列索引失效# status 范围查询,address索引失效wherenamexxxandstatus1andaddressxx3、在索引列上使用运算操作或使用函数该索引列失效# 在name列上使用了substring 索引失效wheresubstring(name,3,2)xx# 在id列上使用操作运算符 索引失效whereid124、字段类型不同# 字符串不加单/双引号造成索引失效whereaddressxx5、以%开头的模糊查询可能会使索引失效。若是尾部模糊匹配索引不会失效# 可能会使索引失效whereaddresslike%xxx# 索引不会失效whereaddresslikexxx%6、select *# 查询使用了select * 且查询条件不是索引列select*fromtable_namewheresex17、列对比select*fromuserwhereidheight8、使用or关键字where namexxxor status19、not in 和 not exists 聚集索引不会失效非聚集索引会失效# 索引不会失效whereidnotin(1,2)# 如果是组合索引且符合最左前缀法则索引也不会失效wherenamenotin(t,b)# 索引失效wherestatusnotin(1,2)索引的优缺点优点通过创建唯一索引可以保证每一行数据的唯一性△ 加快数据的检索速度可以加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义在使用分组和排序子句进行数据检索时同样可以显著减少查询中分组和排序的时间。通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能缺点创建索引和维护索引要耗费时间时间随着数据量的增加而增加。索引需要占物理空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果要建立聚簇索引那么需要的空间就会更大。当对表中的数据进行增加、删除和修改的时候索引也要动态的维护这样就降低了数据的维护速度。面试题谈谈你对sql优化的经验表的设计优化参考阿里开发手册《嵩山版》根据实际情况选择合适的数值类型tinyintintbigint根据实际情况选择合适的字符串类型charvarcharchar是定长的效率高varchar是可变长度的效率稍低…sql语句优化select语句必须指明字段禁止使用select *sql语句避免索引失效的写法尽量使用union all 代替 unionunion all和union的区别取结果的交集union对两个结果集进行并集操作不包括重复行相当于distinct同时进行默认规则的排序;union all对两个结果集进行并集操作包括重复行即所有的结果全部显示不管是不是重复。join优化 能用inner join 就不用left/right join 若必须使用要与小表为驱动内连接会对两个表优化优先把小表放外边大表放里边。left/right join 不会调整顺序。超大数据分页问题优化思路一般分页查询时通过覆盖索引能够比较好地提高性能可以通过覆盖索引子查询的方式进行优化。示例select*fromstu s,(selectidfromstuorderbyidlimit9000000,10)twheres.idt.id面试题了解mysql事务吗mysql事务事务是一组操作的集合它是一个不可分割的工作单位事务会把所有操作视为一个不可分割的整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。事务的特性ACID原子性Atomicity事务是不可分割的最小操作单元要么全成功要么全失败一致性Consistency事务完成时必须使所有的数据都保持一致状态隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行持久性Durability事务一旦提交或回滚它对数据库的数据的改变时永久性的并发事务带来哪些问题怎么解决这些问题mysql的默认隔离级别并发事务问题脏读一个事务读取到了另一个事务尚未提交的数据也就是读取到了脏数据。如果后续未提交的事务回滚了则读取到的数据就是无效的。脏读会导致数据不一致因此需要避免。不可重复读一个事务在同一个时间点内多次读取同一行数据但是读取到的数据不一致。这是因为在读取过程中另一个事务修改了该行数据并提交了事务。不可重复读也会导致数据不一致因此也需要避免。幻读一个事务在同一个时间点内多次执行相同的查询但是返回的结果集不一致。这是因为在查询过程中另一个事务插入了符合查询条件的新数据并提交了事务。幻读也会导致数据不一致因此也需要避免。隔离级别读未提交、读已提交、可重复读、串行化低到高这四个概念都是数据库中的事务隔离级别用来控制事务之间的隔离性和数据一致性。读未提交Read Uncommitted最低的隔离级别一个事务可以读取到另一个事务尚未提交的数据也就是脏读。读未提交级别可以提高并发性但是会导致数据不一致。读已提交Read Committed一个事务只能读取到已经提交的数据避免了脏读的问题。但是在同一个事务中多次读取同一行数据可能会出现不一致的情况也就是不可重复读。可重复读Repeatable Read一个事务在同一个时间点内多次读取同一行数据读取到的数据是一致的避免了不可重复读的问题。但是在同一个事务中多次查询可能会出现不一致的情况也就是幻读。串行化Serializable最高的隔离级别通过强制事务串行执行来避免脏读、不可重复读和幻读的问题。但是串行化会导致并发性降低因为多个事务需要串行执行。总结