MySQL优化实例(详细)

原创 2017年10月06日 21:55:26
一、打开MySQL,输入show status命令查看数据库状态


 主要查看当前连接数、当前运行的线程数、总共进行的查询数。
二、使用数据统计工具awk(Linux系统)
1
awk '{printf("%s\n",$0)}' a.txt   //按行打印文件a.txt
2
    正则表达式+处理方式+文件名
3
    
4
mysqladmin -uroot ext|awk 
5
'/Queries/{printf("%d\n",$4)}'
6
    //统计查询次数
7
    
8
mysqladmin -uroot ext|awk '/Threads_connected/{printf("%d\n",$4)}'
9
    //统计当前连接数
10
    
11
mysqladmin -uroot ext|awk '/Threads_running/{printf("%d\n",$4)}'
12
    //统计当前运行的线程数
三、列字段类型选择
    1.列选择原则(优先级):整形>date,time>char,varchar(需要考虑校对集比较策略)
    2.字段大小尽量选择刚好够用(节约内存)
    3.尽量避免使用null字段
四、索引优化策略(查询频繁、区分度高、长度小、覆盖查询字段)
    1.索引类型:B-tree索引--InnoDB、Myisam(查找快,修改慢)
                         Hash索引---memory表(查找快O1,但在磁盘随机放置,取数据慢,无法排序优化)
    2.使用联合索引(左前缀优化)
1
-- 以index(A,B,C)为例(A\B\C都单独建立索引)
2
SELECT * FROM table_name WHERE A=1 AND B=2 AND C=3;
3
-- 只有 A 字段的索引发挥作用,B\C索引失效
4
5
-- 若A\B\C建立联合索引
6
SELECT * FROM table_name WHERE A=1 AND B=2 AND C=3;
7
-- 只有 A\B\C 字段的索引都发挥作用
8
-- A满足的条件必须是范围最小的(左前缀优化)
9
10
-- 对于index(A,B,C)使用索引必须从左到右严格按照顺序
11
WHERE A=1; -- A可以使用索引
12
WHERE A=1,B=2,C=3; -- A、B可以使用索引
13
WHERE A=1,C=3; -- A可以使用索引,C不可以
14
WHERE A=1,B>10,C=3; -- A、B可以使用索引,C不可以
     3.InnoDB和MyIsam索引的区别:
        InnoDB:次索引指向对主键的引用(数据存在叶子结点,聚簇索引,二级索引指向主键)
        MyIsam:次索引和主索引都指向物理行(磁盘查找)
 

     注意:如果没有声明主键,InnoDB会以Unique key字段做主键,没有unique key则内部生成rowid做主键
        4.聚簇索引的优缺点(InnoDB):
1
1.随着数据量的增大,BTree的聚簇索引结点会分裂
2
2.InnoDB结点存储了行数据,分裂时要移动行数据(移动的是大量数据),效率低
3
3.MyIsam结点存储的是数据位置,分裂时不需要移动数据
4
4.对InnoDB的主键选择很重要,尽量使用递增主键,防止结点频繁分裂
        5.索引区分度、长度的考虑
1
-- 区分度:索引长度越长,区分度越高
2
-- 长度:索引长度越长,所占内存空间越大(矛盾)
3
-- 两者要达到平衡
4
-- 如果区分度足够(90%以上),没必要对整个字段都建立索引,而是截取几个字符建立索引
5
ALTER TABLE table_name ADD INDEX user_index username(username(4)) -- 指定索引长度为4个字符
        6.伪hash算法降低索引长度
1
-- 将字符串转化成hash值当成索引储存(int型节省空间)
2
update t9 set crcurl = crc32(url);
3
-- 对url字段进行crc32的hash算法转换成hash值作为主键索引
4
alter table t9 add index crcurl(crcurl);
         7.大数据下分页优化(减少行扫描数)
         对于LIMIT子句,起始值越大,查询越慢(页数越多越慢),因为MySQL是逐行查询,越到后面越慢
1
-- 1.普通分页
2
-- 当前是第N页,每页显示M条
3
SELECT * FROM table_name LIMIT (N-1)*M, M
4
5
-- 2.大数据量分页(页数越大,查询速度越慢)
6
---- a. 业务逻辑上优化(不允许翻到很后面的页数)
7
---- b. 减少行扫描数,使用索引查询
8
SELECT * FROM table_name WHERE id>5000000 LIMIT (N-1)*M, M -- 从id = 5000000开始
9
---- c. 延迟关联
五、in子查询的效率分析及优化
1
table1有10000000条数据
2
table2有10条数据
3
4
-- 1.使用in子查询
5
SELECT id,name FROM table1 WHERE id in
6
(SELECT id FROM tables);
7
-- 两张表的id都加了索引
8
-- 但整条语句的id索引失效,效率低
9
-- MySQL从table1里每次取一行的id和table2进行比较,如果相等则选出来,进行了全表扫描(扫描10000000行)
10
11
-- 2.优化成连接查询
12
SELECT table1.id,name FEOM table1 INNER JOIN table2 
13
ON table1.id = table2.id;
14
-- 先进行table2的全表扫描(扫描10行)
15
-- 再根据索引找到table1的数据行(扫描1行)
16
六、Exists查询效率分析及优化
1
-- 1.使用连接查询进行GROUP BY操作
2
SELECT table1.id,name FROM table1 INNER JOIN table2 
3
ON table1.id = table2.id GROUP BY name;
4
-- 先进行table2的全表扫描(扫描10行)
5
-- 再根据索引找到table1的数据行(扫描1行)
6
-- 由于使用了GROUP BY,建立了临时表和文件排序(效率低)
7
8
-- 2.使用EXISTS子句进行优化
9
SELECT id,name FROM table1 WHERE EXISTS 
10
(SELECT * FROM table2 WHERE table2.id = table1.id);
11
-- 先进行table2的全表扫描(扫描10行)
12
-- 再根据索引找到table1的数据行(扫描1行)
13
-- 没有使用了GROUP BY,没有建立了临时表和文件排序(效率高)
七、Min和Max函数优化
1
-- 查找pid=2000时id的最大值(id有主键索引)
2
-- 全表扫描,找到pid=2000的所有数据,再进行MAX计算最大的id值
3
SELECT MAX(id) FROM table1 WHERE pid = 2000;
4
5
-- 优化1:给pid加索引
6
-- 优化2:强制沿着id索引查找(id是顺序存储的)
7
SELECT MAX(id) FROM table1 USE INDEX(PRIMARY) WHERE pid = 2000;
8
八、COUNT函数优化
1
-- MyIsam的count速度特别快(因为系统缓存)
2
SELECT COUNT(*) FROM table1 -- 速度快(有系统缓存)
3
SELECT COUNT(*) FROM table1 WHERE id >= 1000 -- 速度慢(缓存失效)
4
SELECT COUNT(*) FROM table1 WHERE id < 1000 -- 速度快(缓存失效但数据少)
5
6
-- 优化!!将两个速度快的查询相减
7
SELECT
8
(SELECT COUNT(*) FROM table1) - 
9
(SELECT COUNT(*) FROM table1 WHERE id < 1000) 
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_17305249/article/details/78167361

关于MSSQL占用过多内存的问题

经常看见有人问,MSSQL占用了太多的内存,而且还不断的增长;或者说已经设置了使用内存,可是它没有用到那么多,这是怎么一回事儿呢?首先,我们来看看MSSQL是怎样使用内存的。最大的开销一般是用于数据缓...
  • ju_feng
  • ju_feng
  • 2003-08-18 14:13:00
  • 689

Mysql性能优化之几个实际优化示例

数据库性能优化的文章铺天盖地,但最重要的是把这些恰当的应用到实际生产环境中,本文以真实的优化案例来详细的介绍Mysql数据库方面的先化技巧,主要的优化技术为:(1)把逐个循环的子查询变为一个查询统计语...
  • cpaqyx
  • cpaqyx
  • 2014-12-06 17:37:35
  • 2869

MySQL性能优化——易实现的MySQL优化方案汇总

1、索引优化及索引失效情况汇总2、表结构优化小技巧3、临时表的优化4、其它优化技巧...
  • zhangliangzi
  • zhangliangzi
  • 2016-08-26 16:44:10
  • 6972

resids 客户端

  • 2018年01月24日 11:38
  • 476KB
  • 下载

MySQL性能管理及架构设计:SQL查询优化、分库分表

一、SQL查询优化(重要) 1.1 获取有性能问题SQL的三种方式 通过用户反馈获取存在性能问题的SQL; 通过慢查日志获取存在性能问题的SQL; 实时获取存在性能问题的SQL; 1.1.2...
  • whs_321
  • whs_321
  • 2018-03-27 10:55:12
  • 50

MySQL 索引最佳实践

  • 2013年03月14日 17:05
  • 640KB
  • 下载

我的mysql 优化日记

  • 2009年03月29日 22:43
  • 87KB
  • 下载

mysql性能调优讲解

  • 2009年08月14日 11:57
  • 834KB
  • 下载

MySQL优化实例

  • 2012年08月28日 08:51
  • 3KB
  • 下载

MySQL优化实例

MySQL优化实例来源: ChinaUnix博客  日期: 2008.10.10 23:42 (共有0条评论) 我要评论                 在Apache, PHP,MySQL的体系架构...
  • rovige
  • rovige
  • 2008-12-16 15:45:00
  • 416
收藏助手
不良信息举报
您举报文章:MySQL优化实例(详细)
举报原因:
原因补充:

(最多只允许输入30个字)