面试题-Mysql优化

面试题-Mysql优化

一 数据库索引优化的原理

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库表中数据，索引 的实现通常使用 BTree 及其变种B+Tree. 所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是 通过建立特征值,然后根据特征值来快速查找,而用的最多,并且是mysql默认的就是二叉树算法 BTREE,通过BTREE算法建立索引的字段,比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果, MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。拿汉语字 典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要 的字。 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际 上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大 大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新 表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

二 索引是什么

索引，在MySQL中也叫“键（key）”，是存储引擎用于快速找到记录的一种数据结构。如果把数据库的 一张表比作一本书，那索引则是这本书的目录，通过目录，我们能快速找到我们想要的主题所对应的页 码。索引的作用即类似于书的目录，帮助我们快速定位到相关数据行的位置。 好的索引能使查询的性能提高几个数量级，而差的索引在大数据量的表中甚至会使性能急剧下降。 “最优”的索引有时比一个“好的”索引性能要好两个数量级。

三索引有哪些类型

索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而非服务器 层实现的，而不同的存储引擎的索引的工作方式并不一样，且不是所有的存储引擎都支持所有类型的索 引。同时，值得一提的是，不同的存储引擎对同一类型的索引，其底层的实现一般是不同的。 MySQL支持以下几种类型的索引。 （1）B-Tree索引 （2）哈希索引 （3）空间数据索引（R-Tree） （4）全文索引

1.B-Tree索引

B-Tree索引是最常见的索引类型，它使用B-Tree数据结构来存储数据，大多数MySQL引擎都支持这种 索引。（Archive引擎是一个例外：5.1之前Archive不支持任何索引，直到5.1才开始支持单个自增 列AUTO_INCREMENT的索引。） 在MySQL中，“B-Tree”只是一个术语的统称，因为不同的存储引擎可能使用的是其他存储结构来 实现这种索引，但仅仅只是命名为“B-Tree”。例如，NDB集群存储引擎内部实际上使用了T-Tree结构 存储这种索引；InnoDB则使用的是B+Tree结构存储这种索引。只是它们都将其命名为“B-Tree”。

四 SQL优化方法

1.一些常见的SQL实践

1.负向条件查询不能使用索引 select from order where status!=0 and status!=1 可以优化为in查询： select from order where status in(2,3) 2.前导模糊查询不能使用索引 select from order where desc like ‘%XX’ 3.而非前导模糊查询则可以 select from order where desc like ‘XX%’ 4.数据区分度不大的字段不宜使用索引 select from user where sex=1 原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。 经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少, 不宜使用索引，如果状 态值很多，能够过滤大量数据，则应该建立索引。 5.在属性上进行计算不能命中索引 select from order where YEAR(date) <=‘2017’ 6.即使date上建立了索引，也会全表扫描，可优化为值计算： select from order where date < = CURDATE() 或者： select from order where date <=‘2017-01-01’

2.并非周知的SQL实践

1.如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心 select from user where uid=? select from user where login_name=? 原因：B-Tree索引的时间复杂度是O(log(n)) ； Hash索引的时间复杂度是 O(1) -允许为null的列，查询有潜在大坑 2.单列索引不存null值，复合索引不存全为null的值，如果列允许为null , 可能会得到“不符合预 期”的结果集 select from user where name != ‘shenjian’ 如果name允许为null ,索引不存储null值，结果集中不会包含这些记录。 所以，请使用not null 约束以及默认值。 3.复合索引最左前缀，并不是值SQL语句的where顺序要和复合索引一致 用户中心建立了 (login_name, passwd)的复合索引 select from user where login_name=? and passwd=? select from user where passwd=? and login_name=? 4.都能够命中索引 select from user where login_name=? 5.也能命中索引，满足复合索引最左前缀 select from user where passwd=?

3.小众但有用的SQL实践

1.如果明确知道只有一条结果返回，limit 1能够提高效率 select from user where login_name=? 可以优化为: select from user where login_name=? limit 1 原因：你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动, 2.把计算放到业务层而不是数据库层，除了节省数据的CPU,还有意想不到的查询缓存优化效果 select from order where date < = CURDATE() 这不是一个好的SQL实践，应该优化为： $curDate = date(‘Y-m-d’); $res = mysqlquery(‘select from order where date <= $curDate’); 原因：释放了数据库的CPU,多次调用，传入的SQL相同，才可以利用查询缓存 3.强制类型转换会全表扫描 select * from user where phone=13800001234 你以为会命中phone索引么？大错特错了，这个语句究竟要怎么改？ 末了，再加一条，不要使用select * (潜台词，文章的SQL都不合格==)，只返回需要的列，能够大 大的节省数据传输量，与数据库的内存使用量。

五 MYSQL优化

1 表关联查询时务必遵循小表驱动大表原则； 2 使用查询语句where条件时，不允许出现函数，否则索引会失效； 3 LIKE语句不允许使用%开头，否则索引会失效； 4 组合索引一定要遵循从左到右原则，否则索引会失效；比如：SELECT* FROM table WHERE name ='张三’AND age = 18，那么该组合索引必须是 name,age 形式 5 索引不宜过多，根据实际情况决定，尽量不要超过10个； 6 每张表都必须有主键，达到加快查询效率的目的 7 分表，可根据业务字段尾数中的个位或十位或百位（以此类推）做表名达到 分表的目的 ； 8 分库，可根据业务字段尾数中的个位或十位或百位（以此类推）做库名达到 分库的目的； 9 表分区，类似于硬盘分区，可以将某个时间段的数据放在分区里，加快查询 速度，可以配合分表+表 分区结合使用；

六 神器 EXPLAIN 语句

七 MySQL索引使用的注意事项

1 索引不会包含有NULL的列 只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含 有NULL值，那么这一列 对于此符合索引就是无效的。 2 使用短索引 对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个 char(255)的列，如果在前 10个或20个字符内，多数值是唯一的，那么就 不要对整个列进行索引。短索引不仅可以提高查询速度 而且可以节省磁盘空间和 I/O操作。 3 索引列排序 MySql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那 么order by中的列是不 会使用索引的。因此数据库默认排序可以符合要求的情 况下不要使用排序操作，尽量不要包含多个列 的排序，如果需要最好给这些列建 复合索引。 4 like语句操作,—般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方 式。like ‘%aaa%'不会使用索引，而like ‘aaa%'可以使用索引。 5 不要在列上进行运算 6 不使用NOT IN 、<>、!=操作，但 < ,<=,=,> ,>=,BETWEEN , IN是可以用到索引的 7 -索引要建立在经常进行select操作的字段上 这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相 反，由于增加了索引， 反而降低了系统的维护速度和增大了空间需求。 8 索引要建立在值比较唯一的字段上 9 对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这 些列的数据量要么相当 大，要么取值很少 10 在where和join中出现的列需要建立索引 11 where的查询条件里有不等号(where column ! = …) , MySql将无法使用索引 12 如果where字句的查询条件里使用了函数(如：where DAY(column)=.) , MySql 将无法使用 索引 13 在join操作中(需要从多个数据表提取数据时)，MySql只有在主键和外键的数 据类型相同时才能 使用索引，否则及时建立了索引也不会使用

八 limit 20000加载很慢怎么解决

MySQL的性能低是因为数据库要去扫描N + M条记录，然后又要放弃之前N 条记录，开销很大 1 前端加缓存，或者其他方式，减少落到库的查询操作，例如某些系统中数据 在搜索引擎中有备份 的，可以用es等进行搜索 2 使用延迟关联，即先通过limit得到需要数据的索引字段，然后再通过原表和 索引字段关联获得需 要数据 select a.* from a,(select id from table_1 where is_deleted=‘N’ limit 100000,20) b where a.id = b.id 从业务上实现，不分如此多页，例如只能分页前100页，后面的不允许再查了 不使用limit N,M,而是使用limit N，即将offset转化为where条件。

九 MySQL有哪些存储引擎

8种，默认innodb •myisam:不支持事务和外键，访问速度快，对事务完整 性没要求的或以select, insert为主的都 可以用该引擎； •innodb:默认的，支持事务和外键，行级锁;更新较多，支 持事务，自动灾难恢复，外键约束，支持 自增列值； •memory: 为了得到最快的响应速度目标数据小，或者数据是临时的，或者数据丢失不会对服务产生实质的影响， 可以用该引擎; •merge: 是几个相同的myisam的组合； •archive: 归档，只支持最基本的插入和查询，5.5之后支持索引；

十 InnoDB存储引擎与MyiSAM存储引擎的 区别

1 InnoDB不支持fulltext类型的索引。 2 InnoDB中不保存表的具体行数，也就是说，执行select count() from table时，InnoDB要扫 描一遍整个表来计算有多少行，但是MylSAM只要简单的读出保存好的行数即可。注意的是，当 count()语句包含where条 件时，两种表的操作是一样的。 3 对于auto_increment类型的字段，InnoDB中必须包含只有该字段的索引,但是在MyISAM表中，可 以和其他字段一起建立联合索引。 4 DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。 5 LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把 InnoDB表改成 MylSAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适 用。另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不 能确定要扫描的范围， InnoDB表同样会锁全表，例如update table set num=1 where name like

十一 MySQL数据库设计规范

1.基础规范

•表存储引擎必须使用InnoDB •表字符集默认使用utf8，必要时候使用utf8mb4 •通用，无乱码风险，汉字3字节，英文1字节 如果是gbk,一个汉字占2个字节 如果是utf-8,一个汉字占3个字节 •utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它 •禁止使用存储过程，视图，触发器，Event •对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层 •调试，排错，迁移都比较困难，扩展性较差 •禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径 •禁止在线上环境做数据库压力测试 •测试，开发，线上数据库环境必须隔离

2.命名规范

1 库名，表名，列名必须用小写，采用下划线分隔 abc, Abc, ABC都是给自己埋坑 2 库名，表名，列名必须见名知义，长度不要超过32字符 tmp, wushan谁TM知道这些库是干嘛的 3 库备份必须以bak为前缀，以日期为后缀 4 从库必须以-s为后缀 5 备库必须以-ss为后缀

3.表设计规范

1 单实例表个数必须控制在2000个以内 2 单表分表个数必须控制在1024个以内 3 表必须有主键，推荐使用UNSIGNED整数为主键 删除无主键的表，如果是row模式的主从架构，从库会挂住 4 禁止使用外键，如果要保证完整性，应由应用程式实现 外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成 为数据库瓶颈

4.列设计规范

1 根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节 2 根据业务区分使用char/varchar 字段长度固定，或者长度近似的业务场景，适合使用char ,能够减少碎片，查询性能高 字段长度相差较大，或者更新较少的业务场景，适合使用varchar ,能 够减少空间 3 根据业务区分使用 datetime/timestamp 前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用 datetime 4 必须把字段定义为NOT NULL并设默认值 NULL的列使用索引,索引统计，值都更加复杂，MySQL更难优化 NULL需要更多的存储空间 NULL 只能采用 IS NULL 或者 IS NOT NULL，而在=/ ! =/in/not in 时有大坑 5 使用 INT UNSIGNED 存储 IPv4，不要用 char(15) 6 使用varchar(20)存储手机号，不要使用整数 牵扯到国家代号，可能出现+/-/()等字符，例如+86 手机号不会用来做数学运算 varchar可以模糊查询，例如like‘138%’ 7 使用 TINYINT 来代替 ENUM ENUM增加新值要进行DDL操作

5.索引规范

1 唯一索引使用uniq_［字段名］来命名 2 非唯一索引使用idx_［字段名］来命名 单张表索引数量建议控制在5个以内 互联网高并发业务，太多索引会影响写性能 生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选 择不到最优索引 异常复杂的查询需求，可以选择ES等更为适合的方式存储 3 组合索引字段数不建议超过5个 如果5个字段还不能极大缩小row范围，八成是设计有问题 4 不建议在频繁更新的字段上建立索引 5 非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须 类型相同，并建立索引 踩过因为JOIN字段类型不一致，而导致全表扫描的坑么？ 6 理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了 (a), (a,b), (a,b,c)

6.SQL规范

禁止使用select * ,只获取必要字段 。select*会增加cpu/io/内存/带宽的消耗 。指定字段能有效利用索引覆盖 。指定字段查询，在表结构变更时，能保证对应用程序无影响 insert必须指定字段 ，禁止使用insert into T values() 。指定字段插入，在表结构变更时，能保证对应用程序无影响 隐式类型转换会使索引失效，导致全表扫描 禁止在where条件列使用函数或者表达式 。导致不能命中索引，全表扫描 禁止负向查询以及%开头的模糊查询 对于!=，负向查询肯定不能命中索引 说明： 全表扫描，效率最低，所有方案中最慢 禁止使用负向查询 。导致不能命中索引，全表扫描 禁止大表JOIN和子查询 同一个字段上的OR必须改写问IN , IN的值必须少于50个 应用程序必须捕获SQL异常 。方便定位线上问题

十二 MySQL遇到的死锁问题

1.产生死锁的四个必要条件

-互斥条件：一个资源每次只能被一个进程使用。 -请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。 -不可剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。 -循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。 这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足， 就不会发生死锁。

2.最大限度地降低死锁

・按同一顺序访问对象。 ・避免事务中的用户交互。 ・保持事务简短并在一个批处理中。 ・使用低隔离级别。 ・使用绑定连接。

十三 JDBC流程

1 向DriverManager类注册驱动数据库驱动程序。 2 调用DriverManager.getConnection方法，通过 JDBC URL,用户名，密码取得数据库连接的 Connection对象。 3 获取Connection后，便可以通过createStatement创建Statement用以执行SQL语句。 4 有时候会得到查询结果，比如select，得到查询结果，查询(SELECT)的结果存放于结果集 (ResultSet)中。 5 关闭数据库语句，关闭数据库连接。

十四 mySql的执行顺序

mysql执行sql的顺序从 From 开始，以下是执行的顺序流程 1、FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成 Temp1 2、JOIN table2 所以先是确定表，再确定关联条件 3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2 4、WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp3 5、GROUP BY 对中间表Temp3进行分组，产生中间表Temp4 6、HAVING 对分组后的记录进行聚合 产生中间表Temp5 7、SELECT 对中间表Temp5进行列筛选，产生中间表 Temp6 8、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp7 9、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp8 10、LIMIT 对中间表Temp8进行分页，产生中间表Temp9

十五 触发器

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是 与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比 如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数 据的完整性约束和业务规则等

十六 mysql中char与varchar的区别

1.都是用来存储字符串的，只是他们zhi的保存方式不一样。 2.char有固定的长度，权而varchar属于可变长的字符类型。 char是一种固定长度的类型，varchar则是一种可变长度的类型， 它们的区别是： char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格 字符补足．在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度 的字节（即总长度为L+1字节）。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。