mysql个人理解博客园_MySQL深入理解

【存储引擎】

InnoDB表引擎

默认事务型引擎，最重要最广泛的存储引擎，性能非常优秀。

数据存储在共享表空间，可以通过配置分开。

对主键查询的性能高于其他类型的存储引擎。

内部做了很多优化，从磁盘读取数据时自动在内存构建hash索引，插入数据时自动构建插入缓冲区。

通过一些机制和工具支持真正的热备份，支持崩溃后的安全恢复，支持行级锁，支持外键。

MyISAM表引擎

mysql5.1版本之前的默认存储引擎 ，拥有全文索引、压缩、空间函数。

不支持事务和行级锁，支持表锁，不支持奔溃后的安全恢复。

表存储在两个文件，.MYD(数据文件)和.MYI(索引文件)。

设计简单，某些场景下性能很好。

其他的表引擎

Archive、Blackhole、CVS、Memory

【索引】

索引对性能的影响：

大大减少服务器需要扫描的数据量，帮助服务器避免排序和临时表、将随机I/O变顺序I/O，大大提高查询速度，降低写的速度、占用磁盘

索引的使用场景：

小表：大部分情况下全表扫描效率更高。

中到大型表：索引非常有效。

特大型的表：建立和使用索引的代价随之增长，可以使用分区技术来解决

索引的类型：

一个表只能有一个主键索引，可以有多个唯一索引。

主键索引一定是唯一索引，唯一索引不是主键索引。

主键可以与外键构成参照完整性约束，防止数据不一致

对比说明：

普通索引   最基本的索引，没有任何约束限制

唯一索引   与普通索引类似，但是具有唯一性约束

主键索引   特殊的唯一索引，不允许有空值

组合索引   将多个列组合在一起创建索引，可以覆盖多个列

外键索引   只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作

全文索引   MySQL自带的全文索引只能用于MyISAM，并且只能对英文进行全文索引

MySQL索引的创建原则：

最适合索引的列是在where子句中的列，或连接子句中的列而不是出现在select关键字后的列

索引列的基数越大。索引的效果越好

对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间

根据情况创建复合索引，复合索引可以提高查询效率

避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率

主键尽可能选择短的数据类型，可以有效减少索引的磁盘占用提高查询效率

MySQL索引的注意事项：

复合索引遵循前缀原则

like查询。%不能在前，可以使用全文索引

column is null可以使用索引

如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引

如果or前的条件中的列有索引，后面的没有，索引都不会被用到

列类型是字符串，查询时一定要给值加引号，否则索引失效

【查询优化】

查找分析SQL查询慢的原因

记录慢查询日志： 分析查询日志，不要直接打开查询日志进行分析，这样比较浪费时间和精力，可以使用pt-query-digest工具进行分析

使用show profile： set profiling=1;开启，服务器上执行的所有语句会检测消耗的时间，存到临时表中(show profiles；show profiles for query 临时表ID)

使用show status： show status 会返回一些计数器，show global status 查看服务器级别的所有计数；有时根据这些计数，可以猜测出哪些操作代价较高或者消耗时间多

使用show processlist： 观察是否有大量线程处于不正常的状态

使用explain： 分析单条SQL语句

优化查询过程中的数据访问

访问数据太多导致查询性能下降

确定应用程序是否在检索大量超过需要的数据，可能是太多行或列

确认MySQL服务器是否在分析大量不必要的数据行

避免使用如下SQL语句：

查询不需要的记录，使用limit解决

多表关联返回全部列，指定A.id,B.age

总是取出全部列，SELECT * 会让优化器无法完成索引覆盖扫描的优化

注意：重复查询相同数据，可以缓存数据，下次直接读取缓存！

是否在扫描额外的记录？

使用explain来进行，如果发现查询需要扫描大量数据但只返回少数的行，可以通过如下技巧去优化：使用索引覆盖扫描，把所有用的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果

改变数据库和表的结构，修改数据表范式(冗余)。

重写SQL语句，让优化器可以以更优的方式执行查询

优化长难的查询语句

一个复杂查询还是多个简单查询？

MYSQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多

使用尽可能少的查询是好的，但是有时将一个大的查询分解为多个小的查询时很有必要的

切分查询：

将一个大的查询分为多个小的相同的查询

一次性删除1000万的数据要比一次删除一万，暂停一会的方案更加耗损服务器开销

分解关联查询：

可以将一条关联语句分解成多条SQL来执行

让缓存的效率更高

执行单个查询可以减少锁的竞争

在应用层做关联可以更容易对数据库进行拆分

优化特定类型的查询语句

优化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

【MySQL高可扩展和高可用】

分区表的原理

对用户而言，分区表是一个独立的逻辑表，但是底层MySQL将其分成了多个物理子表，这对用户来说是透明的，每一个分区表都会使用一个独立的表文件。

创建表时使用partition by子句定义每个分区存放的数据，执行查询时，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询只需要所需数据在的分区即可

分区的主要目的是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关的数据存放在一起，而且如果想一次性删除整个分区的数据也很方便

适用场景：

表非常大，无法全部存在内存或者只在表最后有热点数据，其他都是历史数据

分区表的数据更易维护，可以对独立的分区进行独立的操作

分区表的数据可以再不同的机器上，从而高效适用资源

可以使用分区表来避免某些特殊的瓶颈

可以备份和恢复独立的分区

限制：

一个表最多只能有1024个分区

5.1版本中，分区表表达式必须是整数，5.5可以使用列分区

分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来

分区表中无法使用外键约束

需要对现有表的结构进行修改

所有分区都必须使用相同的存储引擎

分区函数中可以使用的函数和表达式会有一些限制

某些存储引擎不支持分区

对于MyISAM的分区表，不能使用load index into cache

对于MyISAM表，使用分区表时需要打开更多的文件描述

分库分表的原理

通过一些 hash 算法或者工具实现将一张数据表垂直或者水平进行物理切分。

适用场景：

单表记录条数达到百万或者千万级别时

解决表锁的问题

分表方式：

水平分割

垂直分表

分库分表缺点：

有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差

对于应用层来说，逻辑算法无疑会增加开发成本

[水平分表]

定义：表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度

使用场景:表中的数据本身就有独立性，例如表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用，比如切分历史数据和活跃数据

需要把数据存放到多个介质上

缺点:给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需要 union 操作

在许多数据库应用中，这种复杂性会超过带来的优点，查询时会增加读一个索引层的磁盘次数

[垂直分表]

定义：把主键和一些列放在一张表，然后把主键和另外的列放在另一张表中

使用场景:表中某些列常用，而另外一些列不常用

可以使数据行变小，一个数据页能存储更多数据，查询时减少 I/O 次数

缺点:管理冗余列，查询所有数据都需要JOIN操作

【MySQL的复制原理及负载均衡】

MySQL 主从复制工作原理：

在主库上把数据更改记录到二进制文件(即所有的写操作都记录到binlog)

从库将主库的日志复制到自己的中继日志

从库读取中继日志中的事件，将其重放到从库数据中，即执行了日志中的 SQL 语句

解决了哪些问题？

数据分布：随意停止或开始复制，并在不同地理位置分布数据备份

负载均衡：降低单个服务器的压力

高可用和故障切换：帮助应用程序避免单点失败

升级测试：可以使用更高版本的 MySQL 作为从库

思考题：

设定网站的用户数量在千万级，但是活跃用户的数量只有1%，如何通过优化数据库提高活跃用户的访问速度？

答：我们可以根据用户的活跃程度，把活跃的用户提取出来放到另外一张表里面，每次活跃的用户登陆的时候就直接到活跃用户表中进行查询，这样就提高了数据库的查询速度。

【MySQL安全性】

SQL查询的安全方案

1、使用预处理语句防SQL注入

2、写入数据库的数据要进行特殊字符的转义

3、查询错误信息不要返回给用户，将错误记录到日志

MySQL的其他安全设置

1、定期做数据备份

2、不给查询用户root权限，合理分配权限

3、关闭远程访问数据库权限

4、修改root口令，不用默认口令，使用较复杂的口令

5、删除多余的用户

6、改变root用户的名称

7、限制一般用户浏览其他库

8、限制用户对数据文件的访问权限