mysql 按时间范围查找优化_mysql 时间范围查找 - CSDN
精华内容
参与话题
  • 一、索引的作用 索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过...提升查询速度的方向一是提升硬件(内存、cpu、硬盘),二是在软件上优化(...

    转载自:https://www.cnblogs.com/phpdragon/p/8231533.html

    一、索引的作用

    索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询sql执行往往需要几十秒甚至更多,5秒以上就已经让人难以忍受了。

    提升查询速度的方向一是提升硬件(内存、cpu、硬盘),二是在软件上优化(加索引、优化sql;优化sql不在本文阐述范围之内)。

    能在软件上解决的,就不在硬件上解决,毕竟硬件提升代码昂贵,性价比太低。代价小且行之有效的解决方法就是合理的加索引。

    索引使用得当,能使查询速度提升上万倍,效果惊人。

    二、MySQL索引类型:

    mysql的索引有5种:主键索引、普通索引、唯一索引、全文索引、聚合索引(多列索引)。

    唯一索引和全文索引用的很少,我们主要关注主键索引、普通索引和聚合索引。

    1)主键索引:主键索引是加在主键上的索引,设置主键(primary key)的时候,mysql会自动创建主键索引;

    2)普通索引:创建在非主键列上的索引;

    3)聚合索引:创建在多列上的索引。

    三、索引的语法:

    查看某张表的索引:SHOW INDEX FROM 表名;

    创建普通索引:ALTER TABLE 表名 ADD INDEX  索引名 (加索引的列) 

    创建聚合索引:ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列1,加索引的列2) 

    删除某张表的索引:DROP INDEX 索引名 ON 表名;

    四、EXPLAIN 分析SQL执行的状态

    EXPLAIN列的解释

    table                    显示这一行的数据是关于哪张表的

    type                     这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

    possible_keys     显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

    key                      实际使用的索引。如果为NULL,则没有使用索引。

    key_len               使用的索引的长度。在不损失精确性的情况下,长度越短越好

    ref                       显示索引的哪一列被使用了,如果可能的话,是一个常数

    rows                    MYSQL认为必须检查的用来返回请求数据的行数

    Extra                   关于MYSQL如何解析查询的额外信息。

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Extra字段值含义:

    Distinct                   一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

    Not exists               MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了

    Range checked for each Record(index map:#)      没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一

    Using filesort          看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

    Using index            列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候

    Using temporary    看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上

    Where used           使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)


    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    type字段值含义:

    const       表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待

    eq_ref     连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用

    ref           这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

    range      这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况

    index       这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)

    ALL         这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免

    五、性能测试

    、测试环境

    测试环境:博主家用台式机

    处理器为AMD FX(tm)-8300 Eight-Core Processor 3.2GHz;

    内存8G;

    64位 windows 7。

    MySQL: 5.6.17

    (二、MyISAM引擎测试

    1). 创建一张测试表

    复制代码

    DROP TABLE IF EXISTS `test_user`; 
    CREATE TABLE `test_user` (  
        `id` bigint(20)  PRIMARY key not null AUTO_INCREMENT,  
        `username` varchar(50) DEFAULT NULL,  
        `email` varchar(30) DEFAULT NULL,  
        `password` varchar(32) DEFAULT NULL,
        `status`  tinyint(1) NULL DEFAULT 0
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

    复制代码

     

    存储引擎使用MyISAM是因为此引擎没有事务,插入速度极快,方便我们快速插入千万条测试数据,等我们插完数据,再把存储类型修改为InnoDB。

     

    2).  使用存储过程插入1千万条数据

    复制代码

    create procedure myproc()
    begin   
        declare num int;   
        set num=1;   
        while num <= 10000000 do   
            insert into test_user(username,email,password) values(CONCAT('username_',num), CONCAT(num ,'@qq.com'), MD5(num));   
            set num=num+1;  
        end while;  
    end

    复制代码

     

    3).  执行  call myproc();  

    由于使用的MyISAM引擎,插入1千万条数据,仅耗时246秒,若是InnoDB引擎,插入100万条数据就要花费数小时了。

    MyISAM引擎之所以如此之快,一个原因是使用了三个文件来存储数据,frm后缀存储表结构、MYD存储真实数据、MYI存储索引数据。

    每次进行插入时,MYD的内容是递增插入,MYI是一个B+树结构,每次的索引变更需要重新组织数据。

    但相对于InnoDB来说,MyISAM更快。

     

    4). sql测试

    1. SELECT id,username,email,password FROM test_user WHERE id=999999

    耗时:0.114s。

    因为我们建表的时候,将id设成了主键,所以执行此sql的时候,走了主键索引,查询速度才会如此之快。

     

    2. 我们再执行: SELECT id,username,email,password FROM test_user WHERE username='username_9000000'
    耗时:4.613s。

    用EXPLAIN分析一下:

    信息显示进行了全表扫描。

     

    3. 那我们给username列加上普通索引。

    ALTER TABLE `test_user` ADD INDEX index_name(username) ;

    此时,Mysql开始对test_user表建立索引,查看mysql 数据目录:

     

    查看目录文件列表,可以看到新建了三个临时文件,新的临时数据表MYD文件大小并未变更,临时索引文件MYI文件大小增加了很多。

    查看执行结果:

    此过程大约耗时 221.792s,建索引的过程会全表扫描,逐条建索引,当然慢了。

    等执行完毕后,mysql把旧的数据库文件删除,再用新建立的临时文件替换掉之。(删除索引过程也是同样的步骤)。

     

    4. 再来执行:select id,username,email,password from test_user where username='username_9000000'
    耗时:0.001s。

    可见查询耗时提高的很可观。

    用EXPLAIN分析一下:

    Extra 字段告诉我们使用到了索引 index_name,和之前的EXPLAIN结果对比,未建立索引前进行了全部扫描,建立索引后使用到了索引,查询耗时对比明显。 

     

    5. 再用username和password来联合查询

    SELECT id, username, email, PASSWORD FROM test_user WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' AND username = 'username_9000000';

    耗时:0.001s

    执行 EXPLAIN :

    显示使用到了 index_name 索引,条件语句不分password、useranme先后顺序,结果都是一样。说明sql优化器优先用索引命中。

     

    6. 我们再执行:SELECT id, username, email, PASSWORD FROM test_user WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' OR username = 'username_900000'

    此时虽然我们已经对 username 加了索引,但是password列未加索引,索引执行password筛选的时候,还是会全表扫描,因此此时查询速度立马降了下来。

    耗时:5.118s。

    EXPLAIN一下:

    使用OR条件的时候,虽然WHERE 语句中有用到索引字段,但还是进行了全表扫描。

     

     

    7. 当我们的sql有多个列的筛选条件的时候,就需要对查询的多个列都加索引组成聚合索引:

    加上聚合索引:ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)

    通过临时文件的大小来看,索引文件的大小已经超过了数据文件很多了。索引侧面来说,索引要合理利用,索引就是用空间换时间。

    [SQL]ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)

    受影响的行: 10024725。
    时间: 1399.785s。

     


    8. 再来执行:[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username = 'username_900000' OR `password` = '7ece221bf3f5dbddbe3c2770ac19b419'

    耗时:4.416s。

    EXPLAIN:

    竟然是全表扫描,不可思议!!! 使用 OR 语句竟然没有启用聚合索引,也没使用到单索引username,,,

     

     

    9. 再来执行:[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username = 'username_900000' AND `password` = '7ece221bf3f5dbddbe3c2770ac19b419'

    耗时:0.001s。

    EXPLAIN:

    AND 语句才使用到了聚合索引,聚合索引必须使用AND条件,同时要符合最左原则,请戳我

     

    10. 主键区间查询

    [SQL]EXPLAIN SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999990 AND id < 8999999
    受影响的行: 0
    时间: 0.001s。

    命中7行,查询时间很短。

     

    [SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999900 AND id < 8999999
    受影响的行: 0
    时间: 0.010s

     

    [SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999000 AND id < 8999999
    受影响的行: 0
    时间: 0.029s

     

    [SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8990000 AND id < 8999999
    受影响的行: 0
    时间: 0.139s

     

    通过不断加大区间来看,查询时间跟查询的数据量成相对的正比增长,同时使用到了主键索引。

     

    11. 字符串区间查询

    [SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE username > 'username_800000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419' 
    受影响的行: 0
    时间: 6.059s

    EXPLAIN: 

    未使用索引和聚合索引,进行了全表扫描。

     

    [SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE username > 'username_900000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419'
    受影响的行: 0
    时间: 11.488s

    EXPLAIN: 

    也使用到了索引和聚合索引。

    对比得出,字符串进行区间查询,是否能使用到索引的条件得看mysql是如何优化查询语句的。

     

    12.最左原则

    1]. 新建 A、B、C 聚合索引

    [SQL]ALTER TABLE `test_user` ADD INDEX index_union_name_email_password(username,email,password)

    受影响的行: 10024725
    时间: 3171.056s

    2]. SQL 测试 

    慎用 OR 条件,可能将会导致全表扫描。

     

     

    覆盖了 A、B、C 索引:

    该语句使用了覆盖索引,WHERE 语句的先后顺序并不影响。MySQL会对SQL进行查询优化,最终命中ABC索引。

     

     

    命中了 A、B、C 索引中的 AB组合,查询耗时很短:

     

     没有命中到 A、B、C 索引,所以进行了全表扫描,查询耗时长。

     

    小结:

    要使用覆盖索引必须都是 AND 条件,慎用 OR 条件。

    要使用覆盖索引如ABC,需满足条件语句中有 A、AB、ABC才会使用覆盖索引,采用最左原则。

     

     

    (三InnoDB引擎测试

    1). 新建 InnoDB  表

    根据上文的步骤,新建一个 test_user_innodb  表,引擎使用MyISAM,然后将存储引擎修改回InnDB。

    使用如下命令:  ALTER TABLE test_user_innodb ENGINE=InnoDB; 此命令执行时间大约耗时5分钟,耐心等待。

    [SQL]ALTER TABLE test_user_innodb ENGINE=InnoDB;
    受影响的行: 10024725
    时间: 692.475s

     

    执行完毕后, test_user_innodb 表由之前的 三个文件 变为 两个文件,test_user_innodb.frm 和 test_user_innodb.idb。

    其中frm文件记录表结构,idb文件记录表中的数据,其实就是一个B+树索引文件,不过该树的叶子节点中的数据域记录的是整行数据记录。

    所以 Innodb 的查找次数比 MyISAM 表减少一次磁盘IO查找逻辑,但相对来说,插入数据也就没有MyISAM 快了,有所求就有所得吧!

    同时 InnoDB 支持行锁、表锁,InnoDB 的锁机制是建立在索引上的,所以如果没命中索引,那么将是加表锁。

     

    2). SQL 测试 

    1. [SQL]SELECT id,username,email,password FROM test_user_innodb WHERE username='username_9000000'

    受影响的行: 0
    时间: 14.540s

    显示进行了全表扫描,但跟MyISAM表对比来说,扫描的行数小了很多,可能这就是底层B+树布局不一样导致的吧。

     

    2. 那我们给username列加上普通索引。

    ALTER TABLE `test_user_innodb` ADD INDEX index_name(username) ;

    此时,Mysql开始对 test_user_innodb 表建立索引,查看mysql 数据目录:

    仔细观察,发现只生成了一个表结构临时文件。ibd文件容量在不断增大。这个跟MyISAM表加索引逻辑不一样。

    [SQL]ALTER TABLE `test_user_innodb` ADD INDEX index_name(username) ;
    受影响的行: 0
    时间: 157.679s

    此过程大约耗时 157.679s, 貌似建索引的过程未进行全表扫描,对比MyISAM表减少60s左右。为何如何?估计需要看底层实现了! 

     

    3. 再执行 SELECT id,username,email,password FROM test_user_innodb WHERE username='username_9000000'

    [SQL]SELECT id,username,email,password FROM test_user_innodb WHERE username='username_9000000'

    受影响的行: 0
    时间: 0.001s

    可见查询耗时减少的很可观,对比与未加索引。用EXPLAIN分析一下,和MyISAM表没有多少差别。

     

    4. 再用username和password来联合查询

    SELECT id, username, email, PASSWORD FROM test_user_innodb  WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' AND username = 'username_9000000';

    耗时:0.001s

    执行 EXPLAIN :

     

    显示使用到了 index_name 索引,条件语句不分password、useranme先后顺序,结果都是一样。说明sql优化器优先用索引命中。

     

    5. 我们再执行:SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' OR username = 'username_900000'

    此时虽然我们已经对 username 加了索引,但是password列未加索引,索引执行password筛选的时候,还是会全表扫描,因此此时查询速度立马降了下来。

    [SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' OR username = 'username_900000'

    受影响的行: 0
    时间: 10.719s

    EXPLAIN一下:

    使用OR条件的时候,虽然WHERE 语句中有用到索引字段,但还是进行了全表扫描。

    对比MyISAM 表来说,没有多大却别,唯一的就是rows行数不一样。

     

    6. 加上聚合索引:ALTER TABLE `test_user_innodb` ADD INDEX index_union_name_password(username,password)

     此时,Mysql开始对 test_user_innodb 表建立索引,查看mysql 数据目录,和之前的一样,新增了一个临时表结构文件,ibd文件不断增大。

    [SQL]ALTER TABLE `test_user_innodb` ADD INDEX index_union_name_password(username,password)

    受影响的行: 0
    时间: 348.613s

    建立索引的时间比MyISAM 快。

     

    7. 再来执行:[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE `password` = '7ece221bf3f5dbddbe3c2770ac19b419' OR username = 'username_900000'

    受影响的行: 0
    时间: 10.357s

    对比MyISAM 竟然是慢了6s左右? 和MyISAM 的全表扫描无差别。

    InnoDB的OR查询性能没有MyISAM 快,应该是为了实现事务导致的性能损失?

     

    8. 再来执行:[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username = 'username_900000' AND `password` = '7ece221bf3f5dbddbe3c2770ac19b419'

    耗时:0.001s。

    EXPLAIN:

    AND 语句才使用到了聚合索引,聚合索引必须使用AND条件,同时要符合最左原则,请戳我

     

    9. 主键区间查询

    [SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id > 8999990 AND id < 8999999

    受影响的行: 0
    时间: 0.000s

     

    [SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id > 8999900 AND id < 8999999

    受影响的行: 0
    时间: 0.001s

     

    [SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id > 8999000 AND id < 8999999

    受影响的行: 0
    时间: 0.003s

     

    [SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id > 8990000 AND id < 8999999

    受影响的行: 0
    时间: 0.022s

     

    通过不断加大区间来看,查询时间跟查询的数据量成相对的正比增长,同时使用到了主键索引。

    相对于MyISAM 表来说,主键区间查询的耗时小很多很多!看来只能用底层的B+树的实现不一样来解释了!

    MyISAM 的B+树子节点的叶子节点数据域,存储的是数据在MYD文件中的数据地址。

    InnoDB  的B+树子节点的叶子节点数据域,存储的是整行数据记录,这个节省了一次硬盘IO操作,应该是这个特点导致了主键区间查询比MyISAM 快的原因。

    原因请戳我

     

    10. 字符串区间查询

    [SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE username > 'username_800000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419'

    受影响的行: 0
    时间: 12.506s

    未使用索引和聚合索引,进行了全表扫描。

     

    缩小区间在查询 

    [SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE username > 'username_900000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419'

    受影响的行: 0
    时间: 12.213s

     

    [SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE username > 'username_1000000' AND `password` > '7ece221bf3f5dbddbe3c2770ac19b419'

    受影响的行: 0
    时间: 19.793s

     

    11.最左原则

    1]. 新建 A、B、C 聚合索引

    [SQL]ALTER TABLE `test_user_innodb` ADD INDEX index_union_name_email_password(username,email,password)

    受影响的行: 0
    时间: 588.579s

     

    对比MyISAM 表来说,建立该索引的时间是其的1/6之一。建立索引的时间相对可观。磁盘占用来说InnoDB总量更小。

     

    2]. SQL 测试 

    和MyISAM 表对比,竟然没使用到全表扫描,而且使用到了聚合索引。

     

    覆盖了 A、B、C 索引:

    该语句使用了覆盖索引,WHERE 语句的先后顺序并不影响。MySQL会对SQL进行查询优化,最终命中ABC索引。

     

    命中了 A、B、C 索引中的 AB组合,查询耗时很短:

     

    没有命中到 A、B、C 索引最左原则,竟然不是全表扫描,而是使用了索引。

    和MyISAM 表对比,MyISAM 表是全表扫描,而InnoDB却是使用到了索引。

     

     

    六、总结

    两大引擎MyISAM、InnoDB分析:

    背景:

    数据记录:10024725行

    表索引:  主键、A、AB、ABC

     

    相同点:

    1.都是B+树的底层实现。

    2.WHERE条件都符合索引最左匹配原则。

     

    不同点:

    1.MyISAM的存储文件有三个,frm、MYD、MYI 文件;InnoDB的存储文件就两个,frm、ibd文件。总文件大小InnoDB引擎占用空间更小。

    2.InnoDB的存储文件本身就是索引构成,建立新索引的时间比MyISAM快。

    3.MyISAM比InnoDB查询速度快,插入速度也快。

    4.主键区间查询,InnoDB查询更快。字符串区间查询,MyISAM相对更快。

    5.有A、AB、ABC索引的情况下,A OR B 查询,InnoDB查询性能比MyISAM慢。不建议使用OR 条件进行查询。

    6.InnoDB表没有命中到 A、B、C 索引最左原则时,BC组合查询命中了索引,但还是完全扫描,比全表扫描快些。MyISAM是全表扫描。

     

     

    开篇也说过软件层面的优化一是合理加索引;二是优化执行慢的sql。

    此二者相辅相成,缺一不可,如果加了索引,还是查询很慢,这时候就要考虑是sql的问题了,优化sql。

    实际生产中的sql往往比较复杂,如果数据量过了百万,加了索引后效果还是不理想,使用集群、垂直或水平拆分。

     

     

    ps:

    MySQL中B+Tree索引原理

    B+树原理及mysql的索引分析

     

    展开全文
  • 索引有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。...索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引...

    索引有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

    建索引的几大原则

    1、最左前缀匹配原则,非常重要的原则

    对于多列索引,总是从索引的最前面字段开始,接着往后,中间不能跳过。比如创建了多列索引(name,age,sex),会先匹配name字段,再匹配age字段,再匹配sex字段的,中间不能跳过。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。比如a = 1 and b = 2 and c > 3 and d = 4,如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

    2、尽量选择区分度高的列作为索引

    区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录。(比如,我们会选择学号做索引,而不会选择性别来做索引。

    3、=和in可以乱序

    比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

    4、索引列不能参与计算,保持列“干净”

    比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

    例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<'2007-01-01'。

    比如:Flistid+1>‘2000000608201108010831508721‘。原因很简单,假如索引列参与计算的话,那每次检索时,都会先将索引计算一次,再做比较,显然成本太大。

    5、尽量的扩展索引,不要新建索引

    比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

    6. 索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    7. 使用短索引

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    8. 索引列排序

    MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

    9. like语句操作

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    最后总结一下,MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引,不过除非是数据量真的很多,否则过多的使用索引也不是那么好玩的,比如我刚才针对text类型的字段创建索引的时候,系统差点就卡死了。

    使用索引优化查询

    使用索引的典型场景

    1、匹配全值

    对索引中所有列都指定具体值,即对索引中的所有列都有等值匹配的条件。

    #设置组合索引(rental_date,inventory_id,customer_id)为唯一索引。
    EXPLAIN 
    SELECT 
      * 
    FROM
      rental 
    WHERE rental_date = '2005-05-25 17:22:10' 
      AND inventory_id = 373 
      AND customer_id = 343 ;

    这里写图片描述

    2、匹配值的范围查询

    对索引的值能够进行范围查找。

    #设置索引idx_fk_customer_id(customer_id)
    EXPLAIN 
    SELECT 
      * 
    FROM
      rental 
    WHERE customer_id >= 373 
      AND customer_id < 400 ;

    这里写图片描述

    类型type为range说明优化器选择范围查询,索引key为idx_fk_customer_id说明优化器选择索引idx_fk_customer_id来加速访问,Extra为using where说明优化器除了利用索引加速访问外,还需要根据索引回表查询数据。

    3、匹配最左前缀

    仅仅使用索引中的最左边列进行查询。比如组合索引(col1,col2,col3)能够被col1,col1+col2,col1+col2+col3的等值查询利用到的。

    #创建索引idx_payment_date(payment_date,amount,last_update);
    EXPLAIN 
    SELECT 
      * 
    FROM
      payment 
    WHERE payment_date = '2006-02-14 15:16:03' 
      AND last_update = '2006-02-15 22:12:32' ;

    这里写图片描述

    从结果可以看出利用了索引,但又row为182行,所有只使用了部分索引。

    EXPLAIN 
    SELECT 
      * 
    FROM
      payment 
    WHERE amount = 3.98 
      AND last_update = '2006-02-15' ;

    这里写图片描述

    从结果看出,这次查询没有利用索引,进行了全表查找。

    4、仅对索引进行查询

    当查询列都在索引字段中。即select中的列都在索引中。

    EXPLAIN 
    SELECT 
      last_update 
    FROM
      payment 
    WHERE payment_date = '2005-08-19 21:21:47' 
      AND amount = 4.99 ;

    这里写图片描述

    extra部分Using index,说明不需要通过索引回表,Using index就是平时说的覆盖索引扫描(即找到索引,就找到了要查询的结果,不用再回表查找了)。

    5、匹配列前缀

    仅仅使用索引的第一列,并且只包含索引第1列的开头部分进行查找。

    #创建索引idx_title_desc_part(title(10),description(20));
    EXPLAIN 
    SELECT 
      title 
    FROM
      film_text 
    WHERE title LIKE 'AFRICAN%' ;

    这里写图片描述

    6、索引部分等值匹配,部分范围匹配

    EXPLAIN 
    SELECT 
      inventory_id 
    FROM
      rental 
    WHERE rental_date = '2006-02-14 15:16:03' 
      AND customer_id >= 300 
      AND customer_id <= 400 ;

    这里写图片描述

    type=ref,说明使用了索引。key为idx_rental_date说明优化器选择使用索引加速查询,同时由于只查询索引字段inventory_id,故Extra部分有using index,表示查询使用了覆盖索引扫描。

    7、若列名是索引,则使用column_name is null就会使用索引

    EXPLAIN 
    SELECT 
      * 
    FROM
      payment 
    WHERE rental_id IS NULL ;

    这里写图片描述

    索引存在但不能使用索引的典型场景

    1、以%开头的like查询

    EXPLAIN 
    SELECT 
      * 
    FROM
      actor 
    WHERE last_name LIKE '%NI%' ;

    这里写图片描述

    因为B-Tree索引的结构,所以以%开头的查询自然没法使用索引。InnoDB的表都是聚簇表,一般索引都会比表小,扫描索引比扫描表更快,而InnoDB表上二级索引idx_last_name实际上存储字段last_name和主键actor_id,故先扫描二级索引idx_last_name获得满足条件last_name like '%NI%'的主键actor_id列表,之后根据主键回表去检索记录,这样避免了全表扫面演员表actor产生的大量IO请求

    #优化
    EXPLAIN 
    SELECT 
      * 
    FROM
      (SELECT 
        actor_id 
      FROM
        actor 
      WHERE last_name LIKE '%NI%') a,
      actor b 
    WHERE a.actor_id = b.actor_id ;

    这里写图片描述

    2、数据类型出现隐式转化,不会使用索引

    EXPLAIN 
    SELECT 
      * 
    FROM
      actor 
    WHERE last_name = 1 ;

    这里写图片描述

    #使用索引
    EXPLAIN 
    SELECT 
      * 
    FROM
      actor 
    WHERE last_name = '1' ;

    这里写图片描述

    3、组合索引,不满足最左原则,不使用符合索引

    EXPLAIN 
    SELECT 
      * 
    FROM
      payment 
    WHERE amount = 3.98 
      AND last_update = '2006-02-15 22:12:32' ;

    这里写图片描述

    4、估计使用索引比全表扫描还慢,则不要使用索引

    update film_text set title=concat('S',title);

    如查询以“S”开头的标题的电影,返回记录比例比较大,mysql预估索引扫描还不如全表扫描。

    EXPLAIN 
    SELECT 
      * 
    FROM
      film_text 
    WHERE title LIKE 'S%' ;

    这里写图片描述

    5、用or分割条件,若or前后只要有一个列没有索引,就都不会用索引

    EXPLAIN 
    SELECT 
      * 
    FROM
      payment 
    WHERE customer_id = 203 
      OR amount = 3.96 ;

    这里写图片描述

    应尽量避免在where子句中使用or来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
    低效:select * from t_credit_detail where Flistid = '2000000608201108010831508721' or Flistid = '10000200001';

    可以用下面这样的查询代替上面的 or 查询:
    高效:select from t_credit_detail where Flistid = '2000000608201108010831508721' union all select from t_credit_detail where Flistid = '10000200001';

    6 使用!= 或 <> 操作符时

    尽量避免使用!= 或 <>操作符,否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。

    select * from t_credit_detail where Flistid != '2000000608201108010831508721'\G

    7 对字段进行null值判断

    应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
    低效:select * from t_credit_detail where Flistid is null ;

    可以在Flistid上设置默认值0,确保表中Flistid列没有null值,然后这样查询:
    高效:select * from t_credit_detail where Flistid =0;

    避免select *

    在解析的过程中,会将'*' 依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。

    所以,应该养成一个需要什么就取什么的好习惯。

    优化子查询

    使用连接查询(join)代替子查询可以提高查询效率。

    MySQL4.1版开始支持子查询(一个查询的结果作为另一个select子句的条件),子查询虽然灵活但执行效率不高,因为使用子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表,然后外层查询语句从临时表中查询记录,查询完毕后 再撤销这些临时表,因此子查询的速度会相应的受到影响。而连接查询不需要建立临时表其查询速度快于子查询!

    优化插入记录的速度

    innoDB引擎的表常见的优化方法

    (1)、禁用唯一性检查

    插入数据时,MySQL会对插入的记录进行唯一性校验。这种唯一性校验会降低插入记录的速度。为了降低这种情况对查询速度的影响可以在插入记录之前禁用唯一性检查,等到记录插入完毕后再开启。

    Set  unique_check=0; 开启唯一性检查的语句如下:set  unique_checks=1;

    (2)、禁用外键检查

    插入数据之前禁止对外键的检查,数据插入完成之后再恢复对外键的检查。

    Set foreign_key_checks=0; 恢复外键检查:set foreign_key_checks=1;

    (3)、禁止自动提交

    插入数据之前禁止事务的自动提交,数据导入之后,执行恢复自动提交操作。

    禁止自动提交的语句 set autocommit=0;恢复自动提交:set autocommit=1;

    Myisam引擎表常见的优化方法

    (1)、禁用索引 alter table table_name disable keys

                导入数据  loading the data

        开启索引 alter table table_name enable keys

    (2)禁用唯一性检查

    (3)使用批量插入

    (4)当需要批量导入数据时,使用load data infile

    优化insert语句

    1. 一条SQL语句插入多条数据。
    常用的插入语句如:

    修改成:

    修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量(MySQL的binlog和innodb的事务让日志)减少了,降低日志刷盘的数据量和频率,从而提高效率。通过合并SQL语句,同时也能减少SQL语句解析的次数,减少网络传输的IO。
    这里提供一些测试对比数据,分别是进行单条数据的导入与转化成一条SQL语句进行导入,分别测试1百、1千、1万条数据记录。

    2. 在事务中进行插入处理。

    把插入修改成:

    使用事务可以提高数据的插入效率,这是因为进行一个INSERT操作时,MySQL内部会建立一个事务,在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗,所有插入都在执行后才进行提交操作。
    这里也提供了测试对比,分别是不使用事务与使用事务在记录数为1百、1千、1万的情况。

    3. 数据有序插入。
    数据有序的插入是指插入记录在主键上是有序排列,例如datetime是记录的主键:

    修改成:

    由于数据库插入时,需要维护索引数据,无序的记录会增大维护索引的成本。我们可以参照innodb使用的B+tree索引,如果每次插入记录都在索引的最后面,索引的定位效率很高,并且对索引调整较小;如果插入的记录在索引中间,需要B+tree进行分裂合并等处理,会消耗比较多计算资源,并且插入记录的索引定位效率会下降,数据量较大时会有频繁的磁盘操作。

    优化order by语句


    ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序

    MySQL支持二种方式的排序,FileSort和Index,后者效率高,它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

    ORDER BY满足以下情况,会使用Index方式排序:

    a)ORDER BY 语句使用索引最左前列。参见第1句
    b)使用Where子句与Order BY子句条件列组合满足索引最左前列。参见第2句.


    以下情况,会使用FileSort方式的查询

    a)检查的行数过多,且没有使用覆盖索引。

    第3句,虽然跟第2句一样,order by使用了索引最左前列uid,但依然使用了filesort方式排序,因为status并不在索引中,所以没办法只扫描索引。

    b)使用了不同的索引,MySQL每回只采用一个索引.

    第4句,order by出现二个索引,分别是uid_fuid和聚集索引(pk)

    c)对索引列同时使用了ASC和DESC。 通过where语句将order by中索引列转为常量,则除外。

    第5句,和第6句在order by子句中,都出现了ASC和DESC排序,但是第5句却使用了filesort方式排序,是因为第6句where uid取出排序需要的数据,MySQL将其转为常量,它的ref列为const。

    d)where语句与order by语句,使用了不同的索引。参见第7句。
    e)where语句或者ORDER BY语句中索引列使用了表达式,包括函数表达式。参见第8,9句

    f)where 语句与ORDER BY语句组合满足最左前缀,但where语句中使用了条件查询。

    查见第10句,虽然where与order by构成了索引最左有缀的条件,但是where子句中使用的是条件查询。

    g)order by子句中加入了非索引列,且非索引列不在where子句中。

    h)order by或者它与where组合没有满足索引最左前列。

    参见第11句和12句,where与order by组合,不满足索引最左前列. (uid, fsex)跳过了fuid

    i)当使用left join,使用右边的表字段排序。

    参见第13句,尽管user.uid是pk,依然会使用filesort排序。


    FileSort排序算法

    算法一:双路排序算法

    只利用ORDERBY子句中包括的列对象进行排序(适用于有BLOB、TEXT类型的列对象参与的排序)

    MySQL4.1之前的排序算法,完整实现过程如下:

    1) 按索引键或全表扫描的方式,读取所有的元组,不匹配WHERE子句的元组被跳过;第一步需要从存储读入数据,引发I/O操作

    2) 对于每一行,在缓冲区中存储一对值(对值,包括排序关键字和元组指针)。缓冲区的大小是系统变量的sort_buffer_size设定的值。

    3) 当缓冲区已满,运行快排算法(快速排序,qsort)对一个块中的数据进行排序,将结果存储在一个临时文件。保存一个指向排序后的块的指针(如果第二步所说的对值都能被缓冲区容纳,则不会创建临时文件)。

    4) 重复上述步骤,直到所有的行已经被读取。

    5) 执行一个多路归并操作(操作对象是第三步生成的每一个有序的块)汇集到“MERGEBUFF域”,然后存放到在第二个临时文件中。重复操作,直到第一个文件的所有块归并后存入到第二个文件;“MERGEBUFF域”是代码sql_sort.h中定义的宏,值为7。

    6) 重复以下操作(第7步和第8步),直到留下少于“MERGEBUFF2域”标明的块数为止;“MERGEBUFF2域”是代码sql_sort.h中定义的宏,值为15。

    7) 在最后一次多路归并操作中,把元组的指针(排序关键字的最后部分)写入到一个结果文件。

    8) 在结果文件中,按照排列的顺序使用元组指针读取元组(为了优化这项操作,MySQL读入元组指针进入一个大的块,对块中元组指针进行排序而不是直接对数据排序,然后再用有序的元组指针获取元组到元组缓存,元组缓冲区的大小由read_rnd_buffer_size参数控制)。第8步需要从存储读入数据,引发I/O操作

    算法二:单路排序算法

    除利用ORDERBY子句中包括的列对象外,还利用查询目标列中的所有列对象进行排序(适用于除BLOB、TEXT类型外的所有的其他类型的排序)

    MySQL4.1之后出现的改进算法,减少一次I/O,需要增加缓冲区大小容纳更多信息。其具体实现过程如下:

    1) 获取与WHERE子句匹配的元组。这一步需要从存储读入数据,引发I/O操作。

    2) 对于每一个元组,记录排序键值、行的位置值、查询所需的列。这一步记录更多内容,需要更大缓存,内存存储一条元组的信息的长度比算法一的“对值”大许多,这可能引发排序速度问题(排序对象的长度变长,但是内存有限,所以就需把一次内存排序变为多次,进而影响排序的速度),为了控制这个问题,MySQL引入一个参数“max_length_for_sort_data”,如果这一步得到的元组长度大于这个值,则不使用算法二。需要MySQL的使用者特别注意的是,在排序中,如果存在“很高磁盘I/O和很低的CPU利用率”的现象,则需要考虑调整“max_length_for_sort_data”的大小以变更换排序算法。

    3) 按照排序的键值,对元组(元组是第二步的结果)进行排序。

    算法二直接从缓冲区中的排序的元组中获取有序的列信息等(查询的目的对象),而不是第二次访问该表读取所需的列。相比算法一减少一次I/O。

    FileSort优化策略

    当无法使用索引列排序时,为了提高Order By的速度,应该尝试一下优化: 
    1、避免使用 “select * ” 。查询的字段越多导致元组长度总合可能

    超过max_length_for_sort_data的设置,导致无法使用单路排序算法,只能用双路排序算法。 
    超过sort_buffer_size的设置,超出后会创建tmp文件进行合并,导致多次IO

    2、适当增大sort_buffer_size参数的设置

    3、适当增大max_length_for_sort_data参数的设置


    优化group by语句

    默认情况下,MySQL对所有group by col1,col2,...的字段进行排序,若查询包括group by 但用户想避免排序结果的消耗,可以指定order by null禁止排序。

    提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉

    低效:

    SELECT JOB , AVG(SAL)

    FROM EMP

    GROUP by JOB

    HAVING JOB = ‘PRESIDENT'

    OR JOB = ‘MANAGER'

    高效:

    SELECT JOB , AVG(SAL)

    FROM EMP

    WHERE JOB = ‘PRESIDENT'

    OR JOB = ‘MANAGER'

    GROUP by JOB

    优化嵌套查询

    用关联查询代替子查询。使用join优化子查询(in)

    优化or条件

    (1) where 语句里面如果带有or条件, myisam表能用到索引, innodb不行。

    1)myisam表:
     CREATE TABLE IF NOT EXISTS `a` (
      `id` int(1) NOT NULL AUTO_INCREMENT,
      `uid` int(11) NOT NULL,
      `aNum` char(20) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `uid` (`uid`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

    mysql> explain select * from a where id=1 or uid =2;
    +----+-------------+-------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
    | id | select_type | table | type        | possible_keys | key         | key_len | ref  | rows | Extra                                 |
    +----+-------------+-------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
    |  1 | SIMPLE      | a     | index_merge | PRIMARY,uid   | PRIMARY,uid | 4,4     | NULL |    2 | Using union(PRIMARY,uid); Using where |
    +----+-------------+-------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
    1 row in set (0.00 sec)

    2)innodb表:

    CREATE TABLE IF NOT EXISTS `a` (
      `id` int(1) NOT NULL AUTO_INCREMENT,
      `uid` int(11) NOT NULL,
      `aNum` char(20) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `uid` (`uid`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

    mysql>  explain select * from a where id=1 or uid =2;
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | a     | ALL  | PRIMARY,uid   | NULL | NULL    | NULL |    5 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    1 row in set (0.00 sec)


    (2) 含有or的查询子句,如果要利用索引,则or之间的每个条件列都必须用到索引,若没有索引,则应考虑增加索引。

    (3) 用union替换or:

       通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 

           注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 

           在下面的例子中, LOC_ID 和REGION上都建有索引.
           高效: 

    1. select loc_id , loc_desc , region from location where loc_id = 10
    2. union
    3. select loc_id , loc_desc , region from location where region = "melbourne"
         低效: 
    select loc_id , loc desc , region from location where loc_id = 10 or region = "melbourne"

    如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

    (4) 用in替换or:

    实际执行效果还需检验:

    低效: 
    select…. from location where loc_id = 10 or loc_id = 20 or loc_id = 30 
    高效 
    select… from location where loc_in in (10,20,30);

    优化分页查询

    一般分页查询

    一般的分页查询使用简单的 limit 子句就可以实现。limit 子句声明如下:

    SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

    LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点:

    第一个参数指定第一个返回记录行的偏移量
    第二个参数指定返回记录行的最大数目

    如果只给定一个参数:它表示返回最大的记录行数目
    第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行
    初始记录行的偏移量是 0(而不是 1)

    下面是一个应用实例:

    select * from orders_history where type=8 limit 1000,10;

    该条语句将会从表 orders_history 中查询第1000条数据之后的10条数据,也就是第1001条到第10010条数据。

    数据表中的记录默认使用主键(一般为id)排序,上面的结果相当于:

    select * from orders_history where type=8 order by id limit 10000,10;

    这种分页查询方式会从数据库第一条记录开始扫描,越往后,查询速度越慢,而且查询的数据越多,也会拖慢总查询速度。

    (1) 使用子查询优化

    这种方式先定位偏移位置的 id,然后往后查询,这种方式适用于 id 递增的情况。

    select * from orders_history where type=8 limit 100000,1;
     
    select id from orders_history where type=8 limit 100000,1;
     
    select * from orders_history where type=8 and
    id>=(select id from orders_history where type=8 limit 100000,1) 
    limit 100;
     
    select * from orders_history where type=8 limit 100000,100;

    4条语句的查询时间如下:

    1
    2
    3
    4
    第1条语句:3674ms
    第2条语句:1315ms
    第3条语句:1327ms
    第4条语句:3710ms

    针对上面的查询需要注意:

    比较第1条语句和第2条语句:使用 select id 代替 select * 速度增加了3倍
    比较第2条语句和第3条语句:速度相差几十毫秒
    比较第3条语句和第4条语句:得益于 select id 速度增加,第3条语句查询速度增加了3倍
    这种方式相较于原始一般的查询方法,将会增快数倍。

    (2) 使用 id 限定优化

    这种方式假设数据表的id是连续递增的,则我们根据查询的页数和查询的记录数可以算出查询的id的范围,可以使用 id between and 来查询:

    select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;
    这种查询方式能够极大地优化查询速度,基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况,不过一般建立表的时候,都会添加基本的id字段,这为分页查询带来很多便利。

    还可以有另外一种写法:

    select * from orders_history where id >= 1000001 limit 100;

    当然还可以使用 in 的方式来进行查询,这种方式经常用在多表关联的时候进行查询,使用其他表查询的id集合,来进行查询:

    select * from orders_history where id in (select order_id from trade_2 where goods = 'pen') limit 100;

    这种 in 查询的方式要注意:某些 mysql 版本不支持在 in 子句中使用 limit。

    select film_id, description from film order by title limit 50,5;

    优化后:

    select film_id, description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id=b.film_id;

    能用UNION ALL就不要用UNION

    UNION ALL不执行SELECT DISTINCT函数,这样就会减少很多不必要的资源。

    在Join表的时候使用相当类型的列,并将其索引

    如果应用程序有很多JOIN 查询,你应该确认两个表中Join的字段是被建过索引的。这样,MySQL内部会启动为你优化Join的SQL语句的机制。

    而且,这些被用来Join的字段,应该是相同的类型的。例如:如果你要把 DECIMAL 字段和一个 INT 字段Join在一起,MySQL就无法使用它们的索引。对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集有可能不一样)

    能用DISTINCT的就不用GROUP BY

    SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

    可改为:

    SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

    使用 varchar/nvarchar 代替 char/nchar

    尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

    展开全文
  • mysql查找附近优化思路

    千次阅读 2017-04-05 17:58:53
    mysql 计算两点的距离很复杂,如果数据量很大,会导致等待时间过长,这是远不能接受的,目前我的思路主要分为两点 1是优化搜索速度,2是降低cpu的占用率,提高cpu的使用率,提高最大并发量 一. 优化查询速度思路就是...

    场景 :
    各种LBS的应用,例如滴滴打车,转转,微信,qq我周围的朋友,以及地图中各种poi搜索等。
    mysql 计算两点的距离很复杂,如果数据量很大,会导致等待时间过长,这是远不能接受的,目前我的思路主要分为两点 1是优化搜索速度,2是降低cpu的占用率,提高cpu的使用率,提高最大并发量

    一. 优化查询速度

    思路就是一,怎么使用索引,如何缩小范围

    缩小范围思路 :

    1.按照地区进行缩小范围

    例如中国,就比全球要小,北京又比中国要小,甚至精确到某个区,县等

    2.通过geohash算法,创建索引

    其实就是通过一种编码方式,将二维的经纬度转换为一维的,类似于那将整个地球划分成4000 * 4000,每个格子再划分成10 * 10,最终的粒度是1公里*1公里,这样,我们就可以对这个一维的数据创建索引进行查询,网上搜索一下很多这方面的知识,这里不细说,参考GeoHash核心原理解析

    上面所说的只是优化速度,只是找出附近,不能排序,无论按照高中的知还是google推荐的算法,查两点的经纬度都很复杂

    二. 减少计算复杂度,降低cpu的消耗

    方法一: 坐标转换法
    考验自己的数学啊,我是自己推了好久
    设有两点A B ,坐标分别为 (lat1,lng1) (lat2,lng2),地球中心点为O,地球半径为R=3979
    假如B点是数据库中存好的,A点是根据用户所在地取出来的,那么,网上最常见的计算距离的方法如下,(下面方程高中内容,不再证明)

    AB弧度=3979*△AOB=3979*arccos(cos△AOB)=
    3979arccos{ cos(lat1)cos(lat2)cos(lng2-lng1)+sin(lat1)sin(lat2)}

    我们对大括号里面的进行分解,得到:

    cos(lat1)cos(lat2)cos(lng2-lng1)+sin(lat1)sin(lat2)=
    cos(lat1)cas(lng1)*cos(lat2)cos(lng2)+cos(lat1)sin(lng1)cos(lat2)sin(lng2)+sin(lat1)sin(lat2)

    注意我标记的部分,此时,我们可以对数据库加三列,把经纬度坐标这两列给去掉,数据库保存的是经过下面的公式计算过的

    x=cos(lat)cos(lng)
    y=cos(lat2)sin(lng2)
    z=sin(lat2)
    

    那么这两点的距离就是

    3979arccos(x1*x2+y1*y2+z1*z2)

    对于arccos函数在-1到1单调递减
    arccos函数
    arccos的函数,-1到1是单调递减函数,在最下面有附录:
    其实也很显然AOB的角度越小两点距离越近

    假如我们的需求是只需要取前20个最近的post。所以,我们只需要找出 x1*x2+y1*y2+z1*z2,这种计算对于数据库中cpu的占用是非常小的,我查出最大的20个值即可
    然后再去程序中,计算3979*arc(取出的20个值)。程序中只需计算20次arc的函数,是非常快的
    这里不再需要使用估算,不再使用sqrt,数据库中只是计算乘法,经过一些措施减少查询的条数,这种查询占cpu是非常少的

    其他的一些办法

    1. 根据某些特点区域的经纬度,我们知道每一经度大概的距离,每一纬度大概的距离,然后,地球上两点,就可看成平面,勾股定理就可以了。在特点场合,这种效率很高,准确率也还可以,甚至,我们可以直接经度上的距离,加上纬度上的距离,这样不是非常准确,对于不是非常高准确率要求的,我们可以直接做
      例如我们可以用三阶去方程拟合cos,没一纬度110公里,每一经度110公里*cos纬度
      如何拟合参考: http://blog.csdn.net/T1DMzks/article/details/69267678

    2. 直接对整个地球(或者你业务所在的某个区域)进行区域的划分,每一个区域指向一些你要查找的经纬度的点,我们都可以建立索引,用户一处于这个区域,就把这个区域的所有点给取出来,这种方法的优点是快,缺点是前期需要做很大的处理工作,其实这类似与geohash

    展开全文
  • mysql千万级数据量根据索引优化查询速度

    万次阅读 多人点赞 2018-08-31 09:43:24
    (一)索引的作用 索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,...优化sql不在本文阐述范围之内)。 能在软件上解决的,就不在硬件上解决,毕竟硬件提升...

    (一)索引的作用

    索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询sql执行往往需要几十秒甚至更多,5秒以上就已经让人难以忍受了。

    提升查询速度的方向一是提升硬件(内存、cpu、硬盘),二是在软件上优化(加索引、优化sql;优化sql不在本文阐述范围之内)。

    能在软件上解决的,就不在硬件上解决,毕竟硬件提升代码昂贵,性价比太低。代价小且行之有效的解决方法就是合理的加索引。

    索引使用得当,能使查询速度提升上万倍,效果惊人。

    (二)mysql的索引类型:

    mysql的索引有5种:主键索引、普通索引、唯一索引、全文索引、聚合索引(多列索引)。

    唯一索引和全文索引用的很少,我们主要关注主键索引、普通索引和聚合索引。

    1)主键索引:主键索引是加在主键上的索引,设置主键(primary key)的时候,mysql会自动创建主键索引;

    2)普通索引:创建在非主键列上的索引;

    3)聚合索引:创建在多列上的索引。

    (三)索引的语法:

    查看某张表的索引:show index from 表名;

    创建普通索引:alter table 表名 add index  索引名 (加索引的列) 

    创建聚合索引:alter table 表名 add index  索引名 (加索引的列1,加索引的列2) 

    删除某张表的索引:drop index 索引名 on 表名;

    (四)性能测试

    测试环境:博主工作用台式机

    处理器为Intel Core i5-4460 3.2GHz;

    内存8G;

    64位windows。

    1:创建一张测试表

     

    DROP TABLE IF EXISTS `test_user`;
    CREATE TABLE `test_user` (
      `id` bigint(20)  PRIMARY key not null AUTO_INCREMENT,
      `username` varchar(11) DEFAULT NULL,
      `gender` varchar(2) DEFAULT NULL,
      `password` varchar(100) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    存储引擎使用MyISAM是因为此引擎没有事务,插入速度极快,方便我们快速插入千万条测试数据,等我们插完数据,再把存储类型修改为InnoDB。

     

    2:使用存储过程插入1千万条数据

     

    create procedure myproc() 
    begin 
    declare num int; 
    set num=1; 
    while num <= 10000000 do 
    insert into test_user(username,gender,password) values(num,'保密',PASSWORD(num)); 
    set num=num+1;
    end while;
     end
    call myproc();
    

    由于使用的MyISAM引擎,插入1千万条数据,仅耗时246秒,若是InnoDB引擎,插入100万条数据就要花费数小时了。

     

    然后将存储引擎修改回InnDB。使用如下命令:  alter table test_user engine=InnoDB;此命令执行时间大约耗时5分钟,耐心等待。

    tips:这里是测试,生产环境中不要随意修改存储引擎,还有alter table 操作,会锁整张表,慎用。其次:myisam引擎没有事务,且只是将数据写到内存中,然后定期将数据刷出到磁盘上,因此突然断电的情况下,会导致数据丢失。而InnDB引擎,是将数据写入日志中,然后定期刷出到磁盘上,所以不怕突然断电等情况。因此在实际生产中能用InnDB则用。

    3:sql测试

    select id,username,gender,password from test_user where id=999999

    耗时:0.114s。

    因为我们建表的时候,将id设成了主键,所以执行此sql的时候,走了主键索引,查询速度才会如此之快。

     

    我们再执行select id,username,gender,password from test_user where username='9000000'
    耗时:4.613s。

     

    我们给username列加上普通索引。

    ALTER TABLE `test_user` ADD INDEX index_name(username) ;

    此过程大约耗时 54.028s,建索引的过程会全表扫描,逐条建索引,当然慢了。

    再来执行:selectid,username,gender,password from test_user where username='9000000'
    耗时:0.043s。

     

    再用username和password来联合查询

    select id,username,gender,password  from test_user where username='9000000' and `password`='*3A70E147E88D99888804E4D472410EFD9CD890AE'

    此时虽然我们队username加了索引,但是password列未加索引,索引执行password筛选的时候,还是会全表扫描,因此此时

    查询速度立马降了下来。

    耗时:4.492s。

     

    当我们的sql有多个列的筛选条件的时候,就需要对查询的多个列都加索引组成聚合索引:

    加上聚合索引:ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
    再来执行:

    耗时:0.001s。

     

    开篇也说过软件层面的优化一是合理加索引;二是优化执行慢的sql。此二者相辅相成,缺一不可,如果加了索引,还是查询很慢,这时候就要考虑是sql的问题了,优化sql。

    实际生产中的sql往往比较复杂,如果数据量过了百万,加了索引后效果还是不理想,使用集群。

     

    Tips:

    1:加了索引,依然全表扫描的可能情况有:

    索引列为字符串,而没带引号;

    索引列没出现在where条件后面;

    索引列出现的位置没在前面。

    2:关联查询不走索引的可能情况有:

    关联的多张表的字符集不一样;

    关联的字段的字符集不一样;

    存储引擎不一样;

    字段的长度不一样。

     

    展开全文
  • mysql 根据时间范围查询

    万次阅读 2019-03-08 14:09:36
    时间格式为 第一种写法: select * from test where create_time between '2019-03-05 13:04:07' and '2019-03-08 13:04:07'; 第二种写法: select * from test where create_time &gt;= '2019-03-05 ...
  • 财务平台进行分录分表以后,随着数据量的日渐递增,业务人员对账务数据的实时分析响应时间越来越长,体验性慢慢下降,之前我们基于mysql的性能优化做了一遍,可以说基于mysql该做的优化已经基本上都做了,本次是基于...
  • 关于MySQL百万级数据量查询的优化(PHP版) 最近在开发一个销售数据统计的网站时,客户提供的数据量在百万级以上,这些数据作为基础数据,从中提取,组合出各种类型的字段进行计算汇总,之前没有遇到过这么大的...
  • MySQL 优化原理

    万次阅读 2018-05-28 22:42:31
    MySQL逻辑架构如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图,有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。 MySQL逻辑架构,来自:高性能MySQLMySQL逻辑架构整体分为三层,最上层为...
  • mysql 索引( mysql index )

    万次阅读 2010-07-28 22:16:00
    以下是查阅《mysql_administrators_bible》的摘录和总结     索引 mysql index      索引是一种数据结构,可以是B-tree, R-tree, 或者 hash 结构。其中R-tree 常用于查询...
  • Mysql的常见面试题 + 索引原理分析

    万次阅读 多人点赞 2019-03-08 15:39:55
    今天给大家分享一篇干货,面试必备之Mysql索引底层原理分析 Mysql索引的本质 Mysql索引的底层原理 Mysql索引的实战经验 面试 问:数据库中最常见的慢查询优化方式是什么? 同学A:加索引。 问:...
  • swx_order表和swx_order_provider表和swx_order_servicer表联查,where条件是last_modify_time,三张表的...time时间区间很小时走索引,时间区间大之后就不走索引了,原因是如果加了索引的行数比较多,mysql优化...
  • mysql进阶(二十七)数据库索引原理

    万次阅读 2016-11-08 09:15:44
    mysql进阶(二十七)数据库索引原理前言  本文主要是阐述MySQL索引机制,主要是说明存储引擎Innodb。   第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础。   第二部分结合MySQL数据库中...
  • MySQL优化面试准备

    万次阅读 多人点赞 2018-11-28 21:42:18
    想了很久要不要发一篇关于MySql优化的文章最后还是决定把它写出来。以输出倒逼自己复习与输入。以下内容大都参考了《高性能MySQL》一书也好把一些的章节做一个总结。全文的聊到的三个主要能容是: MySql的特点与...
  • MySQL逻辑架构及性能优化原理

    千次阅读 多人点赞 2017-08-01 18:52:17
    说起MySQL的查询优化,相信所有人都了解一些最简单的技巧:不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型….. 你是否真的理解这些优化技巧?是否理解其背后的工作原理?在实际场景下...
  • 曾经,我以为我很懂MySQL索引

    万次阅读 多人点赞 2020-08-26 09:04:17
    MySQL索引,我们真的了解么?腾讯云数据库负责人林晓斌说过:“我们面试MySQL同事时只考察两点,索引和锁”。言简意赅,MySQL索引的重要性不言而喻。MySQL索引历经了多个版本的迭代,从语法到底层数据结构都有很多...
  • mySQLmysql 分页查询

    万次阅读 多人点赞 2020-09-28 03:45:11
    MySQL:limit分页公式、总页数公式 1) limit分页公式 (1)limit分页公式:curPage是当前第几页;pageSize是一页多少条记录 limit (curPage-1)*pageSize,pageSize (2)用的地方:sql语句中 select * from student...
  • MySQL之SQL语句优化步骤

    万次阅读 2018-01-18 10:41:23
    MySQL查询截取分析步骤:一、开启慢查询日志,捕获慢SQL二、explain+慢SQL分析三、show profile查询SQL语句在服务器中的执行细节和生命周期四、SQL数据库服务器参数调优一、开启慢查询日志,捕获慢SQL1、查看慢查询...
  • 深入理解MySQL索引底层数据结构与算法

    千次阅读 多人点赞 2019-06-10 14:27:18
    (五) B+Tree(MySQL索引的真正存储结构) 三. 联合索引底层存储结构 一 理解索引的特性 索引是帮助MySQL高效获取数据的排好序的数据结构 索引存储在文件里 二 索引的各种存储结构及其优缺点 在开始讲这一小节...
  • 深入理解 MySQL 底层实现

    万次阅读 多人点赞 2017-12-25 08:54:10
    本文来自作者 默默 在 GitChat 上分享 「深入理解 MySQL 底层实现」,「阅读原文」查看交流实录。「文末高能」编辑 | 哈比MySQL 的常用引擎1. InnoDBInnoDB 的存储文件有两个,后缀名分别是 .frm 和 .idb,其中 ...
  • MySQL优化技巧

    万次阅读 多人点赞 2017-09-10 14:14:02
    MySQL优化三大方向① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。② 对MySQL配置参数进行优化(my.cnf)此优化需要进行压力测试来进行参数调整。③ 对SQL语句以及表优化MySQL参数优化1:MySQL 默认的最大...
1 2 3 4 5 ... 20
收藏数 37,524
精华内容 15,009
关键字:

mysql 按时间范围查找优化