关闭

MySQL优化实例(详细)

30人阅读 评论(0) 收藏 举报
分类:
一、打开MySQL,输入show status命令查看数据库状态


 主要查看当前连接数、当前运行的线程数、总共进行的查询数。
二、使用数据统计工具awk(Linux系统)
三、列字段类型选择
    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):
        5.索引区分度、长度的考虑
1
-- 区分度:索引长度越长,区分度越高
2
-- 长度:索引长度越长,所占内存空间越大(矛盾)
3
-- 两者要达到平衡
4
-- 如果区分度足够(90%以上),没必要对整个字段都建立索引,而是截取几个字符建立索引
5
ALTER TABLE table_name ADD INDEX user_index username(username(4)) -- 指定索引长度为4个字符
        6.伪hash算法降低索引长度
         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子查询的效率分析及优化
六、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函数优化
八、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) 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:3936次
    • 积分:719
    • 等级:
    • 排名:千里之外
    • 原创:65篇
    • 转载:4篇
    • 译文:0篇
    • 评论:0条