深入理解MySQL系列之优化

查询语句优化
  1. 选取最适用的字段属性。设置合适表中字段的宽度,尽量把字段设置为NOTNULL,这样在将来执行查询的时候,数据库不用去比较NULL值。对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多

  2. 使用连接(JOIN)来代替子查询

  3. 使用联合(UNION)来代替手动创建的临时表

  4. 使用事务保证数据一致性和完整性

  5. 锁定表

  6. 使用外键

  7. 使用索引

  8. 应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

  9. 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null。可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0

  10. 很多时候用 exists 代替 in 是一个好的选择

  11. 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

  12. 先进行排序再使用limit进行排序(https://mp.weixin.qq.com/s/F5g0tKo__63ykVZfez38Dw)

  13. 注意隐式转换,函数作用于表字段,会使得索引失效。

  14. 不能利用索引进行混合排序

索引优化

建议创建索引列

  • 在经常需要搜索的列上,可以加快搜索的速度;

  • 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

  • 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;

  • 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;

  • 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

不建议创建索引列

  • 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

  • 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

  • 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

  • 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。

索引优化:

  • 最左前缀匹配原则,上面讲到了
    主键外检一定要建索引
    对 where,on,group by,order by 中出现的列使用索引
    尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0

  • 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键

  • 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
    为较长的字符串使用前缀索引

  • 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

  • 不要过多创建索引, 权衡索引个数与DML之间关系,DML也就是插入、删除数据操作。这里需要权衡一个问题,建立索引的目的是为了提高查询效率的,但建立的索引过多,会影响插入、删除数据的速度,因为我们修改的表数据,索引也需要进行调整重建

  • 对于like查询,”%”不要放在前面。
    SELECT * FROM table WHERE uname LIKE'xx%' -- 走索引
    SELECT * FROM table WHERE uname LIKE "%xx%" -- 不走索引

  • 查询where条件数据类型不匹配也无法使用索引
    字符串与数字比较不使用索引;
    CREATE TABLEa(achar(10));
    EXPLAIN SELECT * FROMaWHEREa="1" – 走索引
    EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引
    正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因

数据库结构优化
  1. 范式优化: 比如消除冗余(节省空间。。)

  2. 反范式优化:比如适当加冗余等(减少join)

  3. 拆分表: 垂直拆分和水平拆分。对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

  4. 增加中间表: 对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

  5. 增加冗余字段:
    类似于创建中间表,增加冗余也是为了减少连接查询.

分析表、检查表、优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1.分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

2.检查表: 使用 CHECK关键字,如CHECK TABLE user [option](option 只对MyISAM有效)

3.优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

服务器硬件优化
  1. 硬件服务器优化
  • 配置多核心和频率高的cpu,多核心可以执行多个线程
  • 配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
  • 配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.
  1. 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

key_buffer_size:索引缓冲区大小
table_cache:能同时打开表的个数
query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
sort_buffer_size:排序缓冲区

  1. 分库分表
    对系统做分库分表 + 读写分离,也就是把一个库拆分为多个库,部署在多个数据库服务上,这时作为主库承载写入请求。然后每个主库都挂载至少一个从库,由从库来承载读请求。

  2. 缓存集群

在写数据库的时候同时写一份数据到缓存集群里,然后用缓存集群来承载大部分的读请求。

explain 执行计划

慢查询分析

https://mp.weixin.qq.com/s/1r6lFQE4pxeo0zsV3yVkXg

mysql> show variables like 'long%';

long_query_time | 10.000000


mysql> set long_query_time=1;


mysql> show variables like 'slow%';

slow_launch_time    | 2

slow_query_log      | ON

slow_query_log_file | /tmp/slow.log

mysql> set global slow_query_log='ON'

日志分析工具mysqldumpslow

得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

其他一些性能排除命令
  1. top查看内存、cpu

  2. 线程堆栈信息

printf "%x\n" pid
jstack pid|grep -A 30 "nid=0x1df9"

  1. 检查数据库线程
    show full processlist

  2. 查看存储引擎状态:show engine InnoDB status;

  3. 查看最大连接数与响应最大连接数

show variables like '%max_connections%';
show variables like '%max_user_connections%';

更全面维度思考优化

https://mp.weixin.qq.com/s/-RpDVsOVU5Wyqmy7XWsEow

在这里插入图片描述

https://mp.weixin.qq.com/s/Puh-JGb5Y3kd_Dlmga1zVw
https://mp.weixin.qq.com/s/F5g0tKo__63ykVZfez38Dw
https://mp.weixin.qq.com/s/-RpDVsOVU5Wyqmy7XWsEow
https://mp.weixin.qq.com/s/ZBgv-s-_ojWMiTZYTVx-iQ

posted @ 2020-12-29 13:46  曹自标  阅读(119)  评论(0编辑  收藏  举报