高性能MySQL学习笔记(二)

最新推荐文章于 2024-11-05 11:19:17 发布

kikikind

最新推荐文章于 2024-11-05 11:19:17 发布

阅读量2.9k

点赞数

分类专栏： Mysql 文章标签： mysql actor 优化 subquery null table

本文链接：https://blog.csdn.net/kikikind/article/details/5633056

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前缀索引

(1)如果以多个字段作为索引（a,b，c)作为一个key,在查询时分根据匹配情况是否使用索引，所以，a/ a,b/ a,b,c这样的查询都能使用到，但是b,a/a,c/c,a等不符知前缀匹配就不能使用，所以在写sql时需要注意
(2)对于很长的字符串需要生成key时，可以找出最常使用的字符串的最常见前缀(前N个字条作为key,而不是将全个字符串作为key)，这样一方面牺牲少少的精确性（有多余数据，再过滤），另一方面大大提高了检索效率，同时减少了索引的维护开销（存储空间，更新等）

如有200w的url,统计结果如下：

select count(*) as cnt, url from iUrl group by url order by cnt desc limit 10;
+-----+-------------------+
| cnt | url |
+-----+-------------------+
| 380 | http://www.f.com, |
| 354 | http://www.r.com, |
| 340 | http://www.h.com, |
| 336 | http://www.u.com, |
| 336 | http://www.e.com, |
| 336 | http://www.i.com, |
| 332 | http://www.k.com, |
| 330 | http://www.t.com, |
| 330 | http://www.w.com, |
| 326 | http://www.l.com, |
+-----+-------------------+
10 rows in set (3.04 sec)

取前N个字符作为前缀,使得匹配效果与全统计接近为止(这里由于都是以http://www. 开头，所以看不出效果)
select count(*) as cnt, left(url,11) from iUrl group by url order by cnt desc limit 10; --left(url,11) 取字符串的左N个
也可以通过计算全列选择性，详细方法要细看书了

添加前缀索引

alter table iUrl add key ( url(11) );

前缀索引好处是：1.减少索引大小 2.提高速度，但也有不足：不能用于order by/group by, 不能作为覆盖索引

聚集索引

不是一种单独的索引，而是一种存储方式。InnoDB支持，保存了B-Tree和数据行，它将数据行保存在索引的叶子页中。每个表只有一个聚集索引。InnoDB按主键进行聚集。

聚集索引优点：
1.可能把相关数据保存在一起
2.数据访问快
3.结全覆盖索引查询可以使用包含叶子节点中的主键值

缺点：
1.聚集能最大限度提升I/O密集负载的性能，但如果数据能装入内存，顺序就无所谓了，这样聚集就没有什么用处了
2.插入速度严重依赖于插入顺序(按主键顺序插入是最快的)
3.更新聚集索引开销大
4.插入、更新时可能会发生分页，导致更多的磁盘空间
5.聚集表扫描比全表慢

覆盖索引

索引叶子节点包含了它们索引的数据，能满足查询需要的数据，不用再去读取所在行。

优势：
1.索引记录通常远小于全行大小，如果只读取索引，就能极大地减少数据访问量（MyISAM还会对索引压缩，也方便加载到内存）
2.索引是按索引值排序的，因此I/O密集型范围访问将会比随机地从磁盘上提取每一行数据快得多
3.大部分存储引擎缓存索引比缓存数据更好
4.对InnoDB特别有用，因为它使用聚集索引

注意：

1.如果选择了所有列，就不能用到覆盖索引了
2.不能执行LIKE操作

可能通过两步查询，合并索引，延伸覆盖

select * from products
    JOIN (
        select prod_id from products where actor = 'kejie' AND title LIKE '%ABC%'    --第一阶段会使用到覆盖索引
    ) AS t1 ON ( t1.prod_id = products.prod_id );

不过如果结果集太大或者太少，优化也不会明显，或者不如直接查询，所以需要按实际业务查询对比，再确定是否需要这样优化！

注意导致不能使用索引的排序查询：

1. 两种不同的排序方向
2.引用了一个不在索引中的列
3. 不能形成索引的最左前缀
4.第一列上有范围条件

...where rental_date > '2005-05-25'

压缩索引

MyISAM使用前缀压缩以减少索引大小，运行更多索引被装入内存，以在某些情况下极大地提高性能。

但也有缺点：压缩后索引查找对于CPU密集的负载会慢几倍，反向扫描甚至更慢

多余和重复索引

CREATE TABLE test (
    ID INT NOT NULL PRIMARY KEY,
    UNIQUE( ID ),
    INDEX( ID)
);

MySQL利用索引实现了UNIQUE约束和PRIMARY KEY约束，因此这样是在同一列上创建了三个索引！

更多的索引的表中插入新行慢得多，添加新索引会能insert/update/delete有较大的性能影响，特别新索引遇到内存限制时

1.对于数据服务器，要考虑有足够大的内存
2.内存了的估计可以参考数据索引的大小（尽可能将索引都装入内存）

索引策略总结

1.检查最常运行的查询
2.避免不知道什么查询会使用索引之前就创建这种错误
3.对索引形成一个优化配置
1) 检查响应时间
2)任何地方都要试着扩展索引，而不是新增索引

高性能查询的常用技巧

1.使用额外的索引
2.冗余字段
3.通过缓存表，汇总表加速读取

高性能查询优化

1.优化数据访问
    1) 减少向服务器请求不需要的数据 ->可以避免给服务器造成额外的负担, 网络开销, 内存消耗,CPU资源
    2) 使用limit限制返回记录数
    3) 对select *保持怀疑态度,是否真的需要 -> 会造成覆盖索引失效

2.MySQL检查
    1) 执行时间
    2) 检查的行数
    3) 返回的行数 -> 检查explain的type: 全表扫描(Full Table Scan), 索引扫描(Index Scan),范围扫描(Range Scan), 唯一索引查找(Unique Index Lookup), 常量(Constant)
--> 三种方式都会写入慢速日志,所以浏览该日志是检索查找了过多数据的最佳方式
慢速查询日志
要启用慢速查询日志需要在 my.cnf 中所做的配置, 也可以用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
清单 1. 启用 MySQL 慢速查询日志

[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don't use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes

如果数据量很大,而生成的结果中数据行很少,可以尝试更复杂的修改:
1) 使用覆盖索引, 它存储了部分列数据,不用去读取完整的行
2) 更改架构
3) 重写复杂的查询, 使得优化器可以优化执行

重构查询方式

1.将复杂的查询分解成多个简单的查询 -> 注意不是说将一个读取10行的查询分成10次读取
2.缩短查询,每次执行一小部分,减少受影响的行数
3.分解联接
    select * from tag,
            join tag_post on tag_post.tab_id = tag.id
            join post on tag_post.post_id = post.id
    where tag.tag = 'mysql';
可以用以下面的语句代替:
    select * from tag where tag = 'mysql';
    select * from tag_post where tag_id = 1234;
    select * from post where post.id in ( 123, 456, 9098,8904 );

这样的分解有以下重大优势:
1)缓存效率更高
2)每个表一个查询可以更用效地利用表锁, 不会长时间内锁住表
3)在应用程序端进行联接可以更方便地扩展数据库
4)可以减少多余行的访问
5)查询更高效,使用了IN使得MySQL可以对ID进行排序,更高效地取得数据

小结:什么时候在应用程序端进行联接效率更高?

1)可以缓存早期查询的大量数据
2)使用了更多的MyISAM表
3)数据分布在不同服务器上
4)对于大表使用IN代替了联接
5)一个联接引用了同一个表多次

关联子查询

 1. MySQL对子查询的优化很差,最差的是在where子句中使用了IN              
     select * from sakila.film where film_id in (
        select file_id from sakila.film_actor where actor_id = 1 );
mysql> explain select * from sakila.film where film_id in ( select film_id from sakila.film_actor where actor_id = 1 );                                             
+----+--------------------+------------+--------+------------------------+---------+---------+------------+------+-------------+
| id | select_type        | table      | type   | possible_keys          | key     | key_len | ref        | rows | Extra       |
+----+--------------------+------------+--------+------------------------+---------+---------+------------+------+-------------+
|  1 | PRIMARY            | film       | ALL    | NULL                   | NULL    | NULL    | NULL       | 1000 | Using where | 
|  2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id | PRIMARY | 4       | const,func |    1 | Using index | 
+----+--------------------+------------+--------+------------------------+---------+---------+------------+------+-------------+

也可改用exists
     select * from sakila.film where exists (
        select file_id from sakila.film_actor where actor_id = 1
        and film_actor.film_id = film.film_id  );
explain select * from sakila.film where exists ( select film_id from sakila.film_actor where actor_id = 1 and film_actor.film_id = film.film_id );           
+----+--------------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
| id | select_type        | table      | type   | possible_keys          | key     | key_len | ref                       | rows | Extra       |
+----+--------------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
|  1 | PRIMARY            | film       | ALL    | NULL                   | NULL    | NULL    | NULL                      | 1000 | Using where | 
|  2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id | PRIMARY | 4       | const,sakila.film.film_id |    1 | Using index | 
+----+--------------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+

改写成以联接方式:
    select film.* from sakila.film 
        inner join sakila.film_actor using( film_id )
    where actor_id = 1;
mysql> explain select film.* from sakila.film inner join sakila.film_actor using( film_id ) where actor_id = 1;  
+----+-------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys          | key     | key_len | ref                       | rows | Extra       |
+----+-------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id | PRIMARY | 2       | const                     |   19 | Using index | 
|  1 | SIMPLE      | film       | eq_ref | PRIMARY                | PRIMARY | 2       | sakila.film_actor.film_id |    1 |             | 
+----+-------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+

可以看出，使用IN/EXISTS子查询效果是一样的，而且性能方面，都是扫描了1000+1行，而使用内联接，只扫描了19 + 1 行！！
使用mysqlslap测试,在100并发查询下子查询方式用时0.448s, 联接方式只用了0.285, 速度提升近一倍.

但也不是绝对的,所以最好使用测试后数据说明问题, 而不是绝对的!

同时对一个表进行select / update

MySQL不可以同时对一个表进行select/update操作,如:
update tbl as outer_tbl 
    set cnt = (
           select count(*) from tbl as inner_tbl
            where inner_tbl.type = outer_tbl.type
);
一种变能的方法是使用临时表:
update tbl as outer_tbl 
    inner join (

           select type,  count(*) as cnt from tbl

           group by type
    ) as der using( type )
set tbl.cnt = der.cnt;

优化特定查询

1.count 
    1)只会统计表达式有值的次数!, 所以要统计所有行数,要使用count(*), 而不要使且count(列)
    2)如果要统计的量很大,可以试着反过来,统计总数 - 不需要部分
        select count(*) from world.city where id > 5;
        可以改写为:
        select ( select count(*) from world.city) - count(*) from world.city where id <= 5;

2.优化联接
    1) 确保ON或USING使用的列上有索引
    2)确保group by或order by只使用一个表的列,  这样MySQL才会对这些操作使用到索引

3.优化子查询
    1)尽可能使用联接!!
    2)子查询创建的临时表是没有索引的

kikikind

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录