Skip to content

Latest commit

 

History

History
222 lines (174 loc) · 14.9 KB

optimize-common-sense.md

File metadata and controls

222 lines (174 loc) · 14.9 KB
Raw

SQL语法应该注意这些

  • 最左前缀法则
  • 在索引列上做类型转换，函数变换等操作
  • 存储引擎不能使用范围条件右边的列作为索引
  • != , > , <不能使用索引
  • B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
  • like 通配符开头不能使用索引
  • 尽量使用覆盖索引（只查询索引的列（索引列和查询列一致）），减少select *
  • 没有使用查询条件，或者查询条件上没有建立索引
  • 查询的数量是索引的大部分，30%以上
  • 对小表查询
  • 其他存储引擎认为使用索引反而查询性能更差时
  • in, not in ,not exist
  • 在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用
  • 在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用
  • 如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引
  • 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
  • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
  • 尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以 使用 union来查询
  • 如果在 where 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择到运行时；它必须在编译时进行选择。然而，如果在编译时简历访问计划，变量的值还是未知的，因而无法作为索引选择的输入项
  • 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
  • 很多时候用 exists 代替 in 是一个好的选择
  • 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
  • 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
  • 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
  • 避免频繁创建和删除临时表，以减少系统表资源的消耗
  • 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写
  • 尽量避免大事务操作，提高系统并发能力

SQL索引优化

  • where条件从句(经常需要搜索的列上)，groupBy分组从句，join连表的on语句出现的列。
  • 索引字段的长度越小越好（tinyint integer char varchar）
  • 联合索引 离散度大的列放在前面 （离散度可以通过分组求和语句判断个数 个数大的离散度就越大 如select id,count(id) from table GROUP BY id）
  • pt-duplicate-key-checker这个工具可以用来查询冗余索引
  • 索引不是越多越好，索引多了不但影响插入，还会增加数据库分析的时间，判断使用什么索引来查询的开销，所以，建立索引要适当才好。
  • 通过慢查询日志分析不常用的索引 （pt-duplicate-key-checker 检查多余key pt-index-usage 索引检查 pt-query-digest 慢查询分析）

慢SQL定位及优化

  • 查看sql是否涉及多表的联表查询或者子查询，如果有，看是否能进行业务拆分，相关字段冗余或者合并成零时表。
  • 涉及联表的查询，是否能进行分表查询，单表查询之后的结果进行字段整合。
  • 如果以上两种情况都不能操作，非要联表查询，那么考虑对相应的查询条件做索引，加快查询速度。
  • 针对数量大的表进行历史表分离
  • 数据库主从分离，读写分离，降低读写针对同一表时的压力
  • explain分析SQL语句，查看执行计划，分析索引是否用得上，分析扫描行数。
  • 查看Mysql执行日志，看看是否有其他方面的问题。

分页优化

SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 

初级优化

SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10; 

高级优化

SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010; 

总结：

  • limit语句的查询时间与起始记录的位置成正比
  • mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用

查看锁表信息

当前运行的所有事务

select * from information_schema.innodb_trx

当前出现的锁

select * from information_schema.innodb_locks

锁等待的对应关系

select * from information_schema.innodb_lock_waits  

优化配置

提高数据库插入性能的中心思想：

  • 尽量将数据一次性写入到Data File
  • 减少数据库的checkpoint 操作

innodb_buffer_pool_size

如果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。

innodb_log_file_size

此配置项作用设定innodb 数据库引擎UNDO日志的大小；从而减少数据库checkpoint操作。

对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间。

innodb_log_buffer_size

此配置项作用设定innodb 数据库引擎写日志缓存区；将此缓存段增大可以减少数据库写数据文件次数

默认值对于多数中等写操作和事务短的运用都是可以的。如 果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。

innodb_flush_log_at_trx_commit

  • Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.
  • the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file
  • the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it

抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

innodb_autoextend_increment

配置由于默认8M 调整到 128M

此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。

提高数据库插入性能中心思想：

  • 尽量使数据库一次性写入Data File
  • 减少数据库的checkpoint 操作
  • 程序上尽量缓冲数据，进行批量式插入与提交
  • 减少系统的IO冲突

Mysql表查询优化

Using index

意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)

Using where

  • 查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra中为Using where
  • 查询的列未被索引覆盖，where筛选条件非索引列，Extra中为Using where

using where 意味着通过索引或者表扫描的方式进程where条件的过滤，反过来说，也就是没有可用的索引查找，当然这里也要考虑索引扫描+回表与表扫描的代价。这里的type都是all，说明MySQL认为全表扫描是一种比较低的代价。

Using index condition

  • 查询的列不全在索引中，where条件中是一个前导列的范围
  • 查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）

Explain详细解析

  • table：表名
  • type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、 eq_reg、ref、range、index和ALL
  • possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关 的域从WHERE语句中选择一个合适的语句
  • key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足 的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使 用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
  • key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
  • ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
  • rows：MYSQL认为必须检查的用来返回请求数据的行数
  • extra：使用索引情况

extra列描述的意义

  • Distinct: 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

  • Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

  • Range checked for each Record（index map:#）: 没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

  • Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

  • Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

  • Using temporary: 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

  • Where used: 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

system 表只有一行：system表。这是const连接类型的特殊情况

  • const: 表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

  • eq_ref: 在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

  • ref: 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

  • range: 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

  • index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

  • ALL: 这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

表结构设计

  • 使用可存下数据的简单的数据类型，int<varchar
  • 可存下数据的最小的字段的长度
  • 尽可能使用not null定义字端，尽可能设置默认值

有关TEXT 类型

  • 只有当必要时才使用 text - BLOB 过长，varchar 放不下时 - 要做 FULLTEXT 索引时

  • 使用TEXT 和innoDB 时

    • 设定 innodb_file_format=barracuda
    • 设定 ROW_FORMAT=Dynamic

这样 text, blob 或varchar, varbinary 并长於20bytes 会分开block 存放，在确保row 的固定长度。

反范式化设计

  • 设计表的时候符合范式化是为了：减少数据冗余、减少表的插入、更新、删除异常
  • 设计表的时候使用反范式化是为了：以空间换时间、增强代码的水平扩展性和查询效率

第一范式

  1. 每一列属性都是不可再分的属性值，确保每一列的原子性
  2. 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

第二范式

  1. 每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。

第三范式

  1. 数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。像：a-->b-->c 属性之间含有这样的关系，是不符合第三范式的。

表的垂直拆分

拆分表的字段 1. 不经常用得放在同一个表中 2. 经常查询的字段放在同一个表中（经常查询） 3. 大的字段单独放在一个表中 4. 经常修改的字段单独放在一个表中（经常修改）

表的水平拆分

为了解决单表数据量过大的问题，每个水平拆分表的结构完全一致 常用拆分方法 1.对id进行hash运算，可以取mod，针对不同的hash值存到不同的表