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) 
版权声明:本文为博主原创文章,未经博主允许不得转载。

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

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

MySQL优化的步骤详解

在开发过程中,虽然感觉优化sql语句很重要,但是往往更加重视的是功能实现,为了使自己以后写Mysql语句效率更高,有必要对Mysql优化做一个小小归纳。 步骤一、通过show status 命令了解各...
  • hsd2012
  • hsd2012
  • 2016年04月09日 20:30
  • 1449

sql优化经典例子

场景 我用的数据库是mysql5.6,下面简单的介绍下场景 课程表 create table Course( c_id int PRIMARY KEY, name varchar...
  • fangqun663775
  • fangqun663775
  • 2017年05月16日 16:37
  • 834

二十种实战调优MySQL性能优化的经验

本文将为大家介绍的是二十条MySQL性能优化的经验,这些小经验有助于大家更好的使用MySQL进行WEB开发。今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性...
  • renwotao2009
  • renwotao2009
  • 2016年06月11日 16:58
  • 1564

mysql5.7配置文件优化

mysql5.7的配置文件优化,引用了别人的模板,根据自己的情况修改了一些参数,加上注释,留着备用,感兴趣的朋友可以借鉴一下,如果无法启动可以查看一下error_log,修改相应的参数。[mysqld...
  • qq_34605594
  • qq_34605594
  • 2017年04月25日 17:30
  • 4930

MySQL的索引单表优化案例分析

建表 建立本次优化案例中所需的数据库及数据表 CREATE DATABASE db0206; USE db0206;CREATE TABLE `db0206`.`article`( `id` ...
  • github_36379934
  • github_36379934
  • 2017年02月08日 12:13
  • 457

MySQL性能调优my.cnf详解

提供一个MySQL 5.6版本适合在1GB内存VPS上的my.cnf配置文件: [client] port = 3306 socket = /tmp/mysql.sock [mysqld] por...
  • Solmyr_biti
  • Solmyr_biti
  • 2016年02月16日 12:41
  • 4126

MySQL优化之——安全地关闭MySQL实例

关闭过程: 1、发起shutdown,发出  SIGTERM信号2、有必要的话,新建一个关闭线程(shutdown thread) 如果是客户端发起的关闭,则会新建一个专用的关闭线程 如果是直接收到...
  • l1028386804
  • l1028386804
  • 2015年07月09日 09:26
  • 1166

MySQL配置文件my.cnf参数优化和中文详解(转)

原文地址:http://www.jb51.net/article/48082.htm 这篇文章主要介绍了MySQL配置文件my.cnf参数优化和中文详解,非常详细的用中文注释了各个参数的作用...
  • lijingkuan
  • lijingkuan
  • 2015年11月11日 16:51
  • 686

mysql千万级大数据SQL查询优化

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎...
  • u014421556
  • u014421556
  • 2016年07月29日 13:27
  • 31338
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL优化实例(详细)
举报原因:
原因补充:

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