mysql面试

原创

已于 2025-09-14 16:24:05 修改 · 653 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #面试 #数据库

于 2022-11-14 15:24:48 首次发布

mycat与shardingsphere比较

数据库的优化方案

1.优化索引，sql语句，分析慢查询
2.设计表的时候严格按照数据库设计规范来设计数据库
3.使用缓存，把京城访问并且不需要经常变化的数据放在缓存中，能够节约磁盘IO
4.优化硬件，采用ssd，使用磁盘队列技术（RAID0， RAID1，RAID5）
5.采用mysql自带的表分区技术，把数据分析分成不同文件，能够磁盘的读写效率
6.垂直分表，把一些不经常读的数据放在一张表当中，节约磁盘IO
7.主从分离读写，采用主从复制把数据库的读操作和写操作分离开来
8.分库分表分机器，数据量特别大的时候，主要的原理是数据路由
9.选择合适的表引擎，参数上的优化
10进行架构级别的缓存，静态化和分布式
11不采用全文检索
12.采用更快的存储方恨少，例如nosql存储经常访问的数据

表优化

1.优化表结构
* 尽量将表字段定义为NOT NULL约束（即使可以为空，补空字符串），这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
* 对于只包含特定类型的字段，可以使用enum、set 等数据类型。
* 数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如IP地址可以使用int类型。
* 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED。但对整数类型指定宽度，比如INT(11)，没有任何用，因为指定的类型标识范围已经确定。
* VARCHAR的长度只分配真正需要的空间
* 尽量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
* 单表不要有太多字段，建议在20以内
* 合理的加入冗余字段可以提高查询速度。
* 列名不要过长，控制20个字符以内，不要中文字段名
2. 表拆分
2.1 垂直拆分
   垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。
   插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作。但是我们可以在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。
   查询单表时，效率大大提升
2.2 水平拆分
   水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。
   有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。
3.表分区
   分区适用于例如日志记录，查询少。一般用于后台的数据报表分析。对于这些数据汇总需求，需要很多日志表去做数据聚合，我们能够容忍1s到2s的延迟，只要数据准确能够满足需求就可以。
   MySQL主要支持4种模式的分区：range分区、list预定义列表分区，hash 分区，key键值分区。
4.读写分离
   大型网站会有大量的并发访问，如果还是传统的数据存储方案，只是靠一台服务器处理，如此多的数据库连接、读写操作，数据库必然会崩溃，数据丢失的话，后果更是不堪设想。这时候，我们需要考虑如何降低单台服务器的使用压力，提升整个数据库服务的承载能力。
   我们发现一般情况对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，这样分析可以采用数据库集群的方案。其中一个是主库，负责写入数据，我们称为写库；其它都是从库，负责读取数据，我们称为读库。这样可以缓解一台服务器的访问压力。
   MySql自带主从复制功能，我们可以使用主从复制的主库作为写库，从库和主库进行数据同步，那么可以使用多个从库作为读库，已完成读写分离的效果。

SQL怎么优化

1.建索引
2.减少表之间的关联
3.优化 sql，尽量让 sql 很快定位数据，不要让sql 做全表查询，应该走索引,把数据量大的表排在前面
4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据
5.尽量用PreparedStatement 来查询，不要用 Statement
从sql上优化
不要在列上使用函数和进行运算
不要在列上使用函数，这将导致索引失效而进行全表扫描。
尽量避免使用 != 或 not in或 <> 等否定操作符
尽量避免使用 or 来连接条件
多个单列索引并不是最佳选择，复合索引的最左前缀原则
查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。
索引不会包含有NULL值的列
当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。
like 语句的索引失效问题
做过mysql的查询优化?
1.select子句中尽量避免使用*
2.where子句比较符号左侧避免函数
3.尽量避免使用in和not in,如用between
4.尽量避免使用or,用union连接
5.使用limit子句限制返回的数据行数
6.尽可能不使用子查询,使用连接（JOIN）来代替子查询(Sub-Queries)
7.LIKE双百分号无法使用到索引,组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
8.update语句如果只改一两个字段，不要update全部字段，否则频繁调用，会引起明显的性能消耗，同时带来的大量的日志

mysql索引的好处

a.创建索引可以大大提高系统的性能:
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

b.增加索引也有许多不利的方面:
1.创建索引和维护索引要耗费时间，这种