1. 存储引擎
1.1 InnoDB表引擎
- 默认事务型引擎,最重要最广泛的存储引擎,性能非常优秀。数据存储在
共享表空间
,可以通过配置分开。对主键查询的性能高于其他类型的存储引擎
。内部做了很多优化,从磁盘读取数据时自动在内存构建hash索引,插入数据时自动构建插入缓冲区
- 通过一些机制和工具支持真正的
热备份
,支持崩溃后的安全恢复
,支持行级锁
,支持外键
1.2 MyISAM表引擎
- mysql5.5版本之前的默认存储引擎 ,拥有全文索引、压缩、空间函数。
不支持事务和行级锁
,支持表锁
,不支持奔溃后的安全恢复。表存储在两个文件,.MYD
(数据文件)和.MYI
(索引文件),.frm
(结构文件,所有存储引擎都有)。设计简单,某些场景下性能很好
1.3 其他的表引擎
- Archive、Blackhole、CVS、Memory
参考资料
mysql 存储引擎 · Issue #28 · chengfengjie/chengfengjie.github.io
2. 索引
2.1 索引对性能的影响
- 大大减少服务器需要扫描的数据量,帮助服务器避免排序和临时表、将随机I/O变顺序I/O,大大提高查询速度,降低写的速度、占用磁盘
2.2 索引的使用场景
- 小表:大部分情况下全表扫描效率更高。
- 中到大型表:索引非常有效。
- 特大型的表:建立和使用索引的代价随之增长,可以使用分区技术来解决
2.3 索引的类型
- 注意
- 一个表只能有一个主键索引,可以有多个唯一索引。
- 主键索引一定是唯一索引,唯一索引不是主键索引。
- 主键可以与外键构成参照完整性约束,防止数据不一致
类别 | 说明 |
---|---|
普通索引 | 最基本的索引,没有任何约束限制 |
唯一索引 | 与普通索引类似,但是具有唯一性约束 |
主键索引 | 特殊的唯一索引,不允许有空值 |
组合索引 | 将多个列组合在一起创建索引,可以覆盖多个列 |
外键索引 | 只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作 |
全文索引 | MySQL自带的全文索引只能用于MyISAM,并且只能对英文进行全文索引 |
2.4 MySQL索引的创建原则
- 最适合索引的列是在where子句中的列,或连接子句中的列而不是出现在select关键字后的列
- 索引列的基数越大。索引的效果越好
- 对字符串进行索引,应该制定一个前缀长度,可以节省大量的索引空间
- 根据情况创建复合索引,复合索引可以提高查询效率
- 避免创建过多索引,索引会额外占用磁盘空间,降低写操作效率
- 主键尽可能选择短的数据类型,可以有效减少索引的磁盘占用提高查询效率
2.5 MySQL索引的注意事项
- 复合索引遵循前缀原则
- like查询。%不能在前,可以使用全文索引
- column is null可以使用索引
- 如果MySQL估计使用索引比全表扫描更慢,会放弃使用索引
- 如果or前的条件中的列有索引,后面的没有,索引都不会被用到
- 列类型是字符串,查询时一定要给值加引号,否则索引失效
3. 查询优化
3.1 查找分析SQL查询慢的原因
- 记录慢查询日志:
- 分析查询日志,不要直接打开查询日志进行分析,这样比较浪费时间和精力,可以使用
pt-query-digest
工具进行分析
- 分析查询日志,不要直接打开查询日志进行分析,这样比较浪费时间和精力,可以使用
- 使用show profile:
- set profiling=1;开启,服务器上执行的所有语句会检测消耗的时间,存到临时表中(
show profiles;show profiles for query 临时表ID
)
- set profiling=1;开启,服务器上执行的所有语句会检测消耗的时间,存到临时表中(
- 使用show status:
- show status 会返回一些计数器,show global status 查看服务器级别的所有计数;
- 有时根据这些计数,可以猜测出哪些操作代价较高或者消耗时间多
- 使用show processlist:
- 观察是否有大量线程处于不正常的状态
- 使用explain:
- 分析单条SQL语句
3.2 优化查询过程中的数据访问
原因
- 访问数据太多导致查询性能下降
- 确定应用程序是否在检索大量超过需要的数据,可能是太多行或列
- 确认MySQL服务器是否在分析大量不必要的数据行
避免使用如下SQL语句
- 查询不需要额记录,使用limit解决
- 多表关联返回全部列,指定A.id,B.age
- 总是取出全部列,SELECT * 会让优化器无法完成索引覆盖扫描的优化
注意:重复查询相同数据,可以缓存数据,下次直接读取缓存!
是否在扫描额外的记录?
- 使用explain来进行,如果发现查询需要扫描大量数据但只返回少数的行,可以通过如下技巧去优化:使用索引覆盖扫描,把所有用的列都放到索引中,这样存储引擎不需要回表获取对应行就可以返回结果
- 改变数据库和表的结构,修改数据表范式(冗余)。
- 重写SQL语句,让优化器可以以更优的方式执行查询
3.3 优化长难的查询语句
一个复杂查询还是多个简单查询
- MYSQL内部每秒能扫描内存中上百万行数据,相比之下,响应数据给客户端就要慢得多
- 使用尽可能少的查询是好的,但是有时将一个大的查询分解为多个小的查询时很有必要的
切分查询
- 将一个大的查询分为多个小的相同的查询
- 一次性删除1000万的数据要比一次删除一万,暂停一会的方案更加耗损服务器开销
分解关联查询
- 可以将一条关联语句分解成多条SQL来执行
- 让缓存的效率更高
- 执行单个查询可以减少锁的竞争
- 在应用层做关联可以更容易对数据库进行拆分
3.4 优化特定类型的查询语句
优化count()查询
- count( * )中*会忽略所有的列,直接统计所有列数,因此不要使用count(列名)
- MyISAM中,没有任何where条件的count( * )非常快
- 当有where条件,MyISAM的count统计不一定比其他表引擎快
优化方案
- 可以使用explain查询近似值,用近似值替代count(*)
- 增加汇总表
- 使用缓存
优化关联查询
- 确定on或者using子语句的列上有索引
- 确保group by和order by中只有一个表中的列,这样MySQL才有可能使用索引
优化子查询
- 尽可能使用关联查询来替代,尽量少使用子查询
优化group by 和distinct
- 这两种查询均可使用索引来优化,是最有效的优化方法
- 关联查询中,使用标识列进行分组的效率会更高
- 如果不需要order by,进行group by时使用order by null,MySQL不会再进行文件排序
- with rollup超级聚合,可以挪到应用程序处理
优化limit分页
- limit偏移量大的时候,查询效率低
- 可以记录上一次查询的最大ID,下次查询时直接根据该ID来查询
优化UNION查询
- UNION ALL的效率高于UNION
4. MySQL高可扩展和高可用
4.1 分区表的原理
工作原理:
- 对用户而言,分区表是一个独立的逻辑表,但是底层MySQL将其分成了多个物理子表,这对用户来说是透明的,每一个分区表都会使用一个独立的表文件。
- 创建表时使用
partition by
子句定义每个分区存放的数据,执行查询时,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询只需要所需数据在的分区即可 - 分区的主要目的是将数据按照一个较粗的粒度分在不同的表中,这样可以将相关的数据存放在一起,而且如果想一次性删除整个分区的数据也很方便
适用场景
- 1、表非常大,无法全部存在内存或者只在表最后有热点数据,其他都是历史数据
- 2、分区表的数据更易维护,可以对独立的分区进行独立的操作
- 3、分区表的数据可以再不同的机器上,从而高效适用资源
- 4、可以使用分区表来避免某些特殊的瓶颈
- 5、可以备份和恢复独立的分区
限制
- 1、一个表最多只能有1024个分区
- 2、5.1版本中,分区表表达式必须是整数,5.5可以使用列分区
- 3、分区字段中如果有主键和唯一索引列,那么主键列和唯一列都必须包含进来
- 4、分区表中无法使用外键约束
- 5、需要对现有表的结构进行修改
- 6、所有分区都必须使用相同的存储引擎
- 7、分区函数中可以使用的函数和表达式会有一些限制
- 8、某些存储引擎不支持分区
- 9、对于MyISAM的分区表,不能使用load index into cache
- 10、对于MyISAM表,使用分区表时需要打开更多的文件描述
4.2 分库分表的原理
工作原理
- 通过一些 hash 算法或者工具实现将一张数据表垂直或者水平进行物理切分。
适用场景
- 单表记录条数达到百万或者千万级别时
- 解决表锁的问题
分表方式
- 水平分割
- 垂直分表
分库分表缺点
- 有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差
- 对于应用层来说,逻辑算法无疑会增加开发成本
4.2.1 水平分表
定义:表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询速度
使用场景
- 表中的数据本身就有独立性,例如表中分别记录各个地区的数据或者不同时期的数据,特别是有些数据常用,有些不常用,比如切分历史数据和活跃数据
- 需要把数据存放到多个介质上
缺点
- 给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需要 union 操作
- 在许多数据库应用中,这种复杂性会超过带来的优点,查询时会增加读一个索引层的磁盘次数
4.2.2 垂直分表
定义:把主键和一些列放在一张表,然后把主键和另外的列放在另一张表中
使用场景
- 表中某些列常用,而另外一些列不常用
- 可以使数据行变小,一个数据页能存储更多数据,查询时减少 I/O 次数
缺点
- 管理冗余列,查询所有数据都需要JOIN操作
4.3 MySQL的复制原理及负载均衡
- MySQL 主从复制工作原理
- 在主库上把数据更改记录到二进制文件(即所有的写操作都记录到binlog)
- 从库将主库的日志复制到自己的中继日志
- 从库读取中继日志中的事件,将其重放到从库数据中,即执行了日志中的 SQL 语句
解决了哪些问题?
- 数据分布:随意停止或开始复制,并在不同地理位置分布数据备份
- 负载均衡:降低单个服务器的压力
- 高可用和故障切换:帮助应用程序避免单点失败
- 升级测试:可以使用更高版本的 MySQL 作为从库
思考题
- 设定网站的用户数量在千万级,但是活跃用户的数量只有1%,如何通过优化数据库提高活跃用户的访问速度?
- 答:我们可以根据用户的活跃程度,把活跃的用户提取出来放到另外一张表里面,每次活跃的用户登陆的时候就直接到活跃用户表中进行查询,这样就提高了数据库的查询速度。
4.4 MySQL安全性
4.4.1 SQL查询的安全方案
- 1、使用预处理语句防SQL注入
- 2、写入数据库的数据要进行特殊字符的转义
- 3、查询错误信息不要返回给用户,将错误记录到日志
4.4.2 MySQL的其他安全设置
- 1、定期做数据备份
- 2、不给查询用户root权限,合理分配权限
- 3、关闭远程访问数据库权限
- 4、修改root口令,不用默认口令,使用较复杂的口令
- 5、删除多余的用户
- 6、改变root用户的名称
- 7、限制一般用户浏览其他库
- 8、限制用户对数据文件的访问权限