MySQL数据库优化
前言:文章经过本人呕心沥血,摸爬滚打,结合自身种种经验,参考各位大神种种经验,终于出炉,谨以此献给xx的后台们,觉得有用就看,觉得没用就点赞
- 什么导致了数据库慢
- 硬件问题
- 非硬件问题
- 建表引擎无差异,各引擎特点不同,没有充分利用
- Sql语句.....算了,概括来讲就是随心随性
- 索引创建不合理,或没用上
- 有些业务单次返回数据量过大
- 没用缓存
- 相关问题
-
InnoDB与MyISAM引擎差异及建表参考
1.1InnoDB支持事物,且默认开启,每一条sql都会提交一次事物
MyISAM 不支持事物
1.2 InnoDB支持外键
MyISAM 不支持外键
1.3InnoDB支持行锁,当where范围不可确定时,锁全表
MyISAM 不支持行锁,只锁全表
1.4InnoDB不存整张表的count
MyISAM 存储整张表的count,但是有where条件查询的时候速度和InnoDB差不多
1.5 InnoDB 使用聚集索引, B+Tree的数据域存储的内容为实际数据的地址,索引本身是数据文件,索引的key是数据表的主键,InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大
MyISAM 使用非聚集索引,索引和实际数据分开
1.6建表参考
- 可靠性要求高,需要事物,且更新查询频繁用InnoDB
- 有很多count计算,插入修改不频繁,查询非常频繁的用MyISAM
- 数据库表的初始数据如果没有需要指定0或者1,避免null导致索引不可用
2.Sql语句问题
- 查询语句中,如果where和order by后面的条件常常用到,需要给该字段加索引
- 避免null值判断,如果为null的字段有索引,则索引效率会下降,因为InnoDB的索引不存null值
- 关于!= 操作的问题,如果数据量均衡,索引失效,例如男性数据1000条,女性数据20条,则使用!=男,会使用索引,由MySQL自己选择是否使用
- <,>,<=,>=,between,in都可以使用索引,但是在in的使用过程中,会根据条件从左到右启用索引
- Like的使用如需使用索引,则需要左限定查询”a%”
- Or 的使用,如果两个字段都有建立索引,用UNION代替or,效率更高,如果仅有一个字段建立了索引,则对效率提升不明显
- 外表大于内表用IN,内表大于外表用EXISTS.因为in是把外表和内表做hash连接,exists是对外表loop循环,每次loop循环再对内表进行查询;not exists:对子查询先查,有个虚表,有确定值,所以就算子查询有null最终也有结果返回;not in:对子查询表简历内存数组,用外表匹配,如果子查询有null,外表没匹配最终无返回值
- Between 对于连续数值查询,最好用between,不用in
- 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
- select id from t where num=@num
- 可以改为强制查询使用索引：
- select id from t with(index(索引名)) where num=@num
- Explain + sql语句 可查询语句执行情况,索引使用情况
select_type:
- SIMPLE:简单SELECT(不使用UNION或子查询)
- PRIMARY:最外面的SELECT
- UNION:UNION中的第二个或后面的SELECT语句
- DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
- UNION RESULT:UNION 的结果
- SUBQUERY:子查询中的第一个SELECT
- DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
- DERIVED:导出表的SELECT(FROM子句的子查询)
table：
显示这一行的数据是关于哪张表的
type：
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL;type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说，得保证查询至少达到range级别，最好能达到ref。
- system:表仅有一行(=系统表)。这是const联接类型的一个特例。
- const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
- eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
- ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
- ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
- index_merge:该联接类型表示使用了索引合并优化方法。
- unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
- index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
- range:只检索给定范围的行,使用一个索引来选择行。
- index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
- ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
possible_keys：
显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key：
实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len：
使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：
显示索引的哪一列被使用了，如果可能的话，是一个常数,但是我没见到常数
rows：
MYSQL认为必须检查的用来返回请求数据的行数
Extra：
关于MYSQL如何解析查询的额外信息
- Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
- Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
- range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
- Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
- Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
- Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
- Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
- Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
- Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
- 当只要一行数据时使用 LIMIT 1
- 建表时字段设置问题
- 时间字段：datetime和timestamp, datetime占用8个字节，而timestamp占用4个字节，只用了一半，而timestamp表示的范围是1970—2037适合做更新时间
- 应该使用MEDIUMINT而不是BIGIN来定义整型字段。
- 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，
而数值型数据被处理起来的速度要比文本类型快得多。 - 字符串数据类型：char，varchar，text选择区别
- text用来保存字符数据（如文章，日记等），blob用来保存二进制数据（如照片等）。
- 使用存储过程,提高效率
这个东西是dba做的,如果需要可以参考:
https://www.cnblogs.com/mark-chan/p/5384139.html
分库分表
有两种方法来做,一个是垂直分表,另一个是水平分表
垂直切分:把一些关联度较低的表,分开放在不同的主机中,缓解数据库压力
优点:
数据库的拆分简单明了，拆分规则明白；
应用程序模块清晰明白，整合easy;
数据维护方便易行，easy定位。
缺点:
部分表关联无法在数据库级别完毕。须要在程序中完毕;
对于訪问极其频繁且数据量超大的表仍存在性能平静不一定能满足要求; 事务处理相对更为复杂；
切分达到一定程度之后，扩展性会遇到限制；
过读切分可能会带来系统过渡复杂而难以维护。
水平切分:在一张表中,根据某一个字段的某一个逻辑规则,将这张表分散到另外几张表中(包括表中的相关数据)
优点:
表关联基本能够在数据库端全部完毕；
不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；
应用程序端总体架构修改相对较少；
事务处理相对简单；
仅仅要切分规则能够定义好。基本上较难遇到扩展性限制；
缺点:
切分规则相对更为复杂，非常难抽象出一个能够满足整个数据库的切分规则；
后期数据的维护难度有所添加，人为手工定位数据更困难；
应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。
垂直切分和水平切分结合使用,避免缺陷
方案一:在每一个应用程序模块中配置管理自己需要的一个或者多个数据源,直接访问各个数据库,在模块内完成数据的整合
方案二:通过中间代理层来统一管理全部的数据源,代理参考MySQLProxy的使用
缓存
合理利用缓存,可以提高查询效率,我们要做的就是尽量命中缓存,拿到数据
如果需要直接使用缓存中的数据,必须满足如下几点:
6.1 SQL文本相同
当前后两次查询使用了同一条sql语句,空格都要相同才行,另,大小写是不敏感的,所以两条sql的中某些地方的大小写不同,不影响
6.2 表未做更改
在前后两次查询的过程中,数据库表的字段结构,表的数据,如有任何改变,将清空与这个表相关的任何缓存
6.3 默认字符集需要相同
这个很好理解,多注意就可以了,字符大小写、空格或者注释有一点点不同，查询缓存就认为这是一个不同的查询
提高命中率:
6.1水平分表
由于表结构和数据都不能变,才能使用索引,那么可以根据年份或者时间分表,如2017年的数据放一张表,2018年的数据放一张表
6.2 配置查询和修改
配置和适用查询缓存的方法:
查看当前查询缓存相关参数状态：
SHOW VARIABLES LIKE '%query_cache%';
状态结果解析:
query_cache_type(0[off], 1[on], 2[demand];
0不使用;
1默认使用缓存, 如果查询语句用SELECT SQL_NO_CACHE ...开头则不缓存;
2默认不使用缓存, 如果查询语句使用SELECT SQL_CACHE开头则使用缓存)
have_query_cache: 为YES表示缓存开启
query_cache_type: on表示默认使用缓存
query_cache_limit: 允许缓存的单条查询结果集的最大容量，默认是1MB， 超过此参数设置的查询结果集将不会被缓存；
query_cache_size: 缓存使用的总内存空间大小,单位是字节,这个值必须是 1024的整数倍,否则MySQL 会自动调整降低最小量以达到1024的倍 数,一般设置为256M
query_cache_min_res_unit: 分配内存块时的最小单位大小，设置查询缓存 Query Cache每次分配内存的最小空间大小，即每个查询的缓存最小占 用的内存空间大小；
query_cache_wlock_invalidate: 如果某个数据表被锁住,是否仍然从缓存中 返回数据,默认是OFF,表示仍然可以返回控制当有写锁定发生在表上 的时刻是否先失效该表相关的Query Cache，如果设置为 1(TRUE)，则 在写锁定的同时将失效该表相关的所有Query Cache，如果设置为 0(FALSE)则在锁定时刻仍然允许读取该表相关的Query Cache。
查询缓存状态: show stats like 'Qcache%';
Qcache_free_blocks: 查询缓存中空闲的block数目
Qcache_free_memory: 空闲的内存总量
Qcache_cache_hits: 命中的次数
Qcache_cache_inserts: 向缓存中插入新的缓存结果的次数(也就是没有命 中的次数)
Qcache_lowmem_prunes: 当 Query Cache内存容量不够，需要从中删除老 的单条查询结果以给新的查询结果对象使用的次数
Qcache_not_cached: 没有被缓存的SQL数, 包括无法被缓存的SQL以及由 于query_cache_type设置的不会被缓存的SQL
Qcache_queries_in_cache: 目前在内存中的SQL数量
Qcache_total_blocks: 内存中总的Block数量
SET GLOBAL query_cache_size = 134217728;设置配置参数
SHOW STATUS LIKE 'Qcache_hits'查看缓存命中次数（是个累加值）
FLUSH QUERY_CACHE; 清理查询缓存内存碎片
RESET QUERY_CACHE; 从查询缓存中移出所有查询
FLUSH TABLES; 关闭所有打开的表，同时该操作将会清空查询缓存中的内容
考虑是否开启缓存:
1. 通过缓存命中率判断, 缓存命中率 = 缓存命中次数 (Qcache_hits) / 查询次 数 (Com_select)
2. 通过缓存写入率, 写入率 = 缓存写入次数 (Qcache_inserts) / 查询次数 (Qcache_inserts)
3. 通过 命中-写入率 判断, 比率 = 命中次数 (Qcache_hits) / 写入次数 (Qcache_inserts), 高性能MySQL中称之为比较能反映性能提升的指数,一般 来说达到3:1则算是查询缓存有效,而最好能够达到10:1
如果有密集的写入操作,开启缓存会导致频繁的缓存失效.增加了内存和cpu开销
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
