精华内容
下载资源
问答
  • 1、可以使用ensure_index或者create_index方法 首先,连接数据库目标集合: col = MongoClient(the_...然后,创建唯一索引,不unique话默认是普通的索引,即unique=False: col.create_index([("索引...

    1、可以使用ensure_index或者create_index方法 

    首先,连接数据库中的目标集合:

    col = MongoClient(the_client).get_database(the_db).get_collection(the_col)

     然后,创建唯一索引,不加unique的话默认是普通的索引,即unique=False:

    col.create_index([("索引字段名", 1)], unique=True)
    

    或者, 

    col.ensure_index([("索引字段名", 1)], unique=True)

     其中的1和-1分别表示正序与负序排列。注意,索引要用中括号——[ ("索引",1)],具体参见下方源码说明。

    实例:

    # -*- coding:utf-8 -*-
    # 给mongodb集合创建索引
    from pymongo import MongoClient
    
    
    def create_mongodb_index(the_data_client, the_data_db, the_data_cl, index_name, unique=False):
        data_client = MongoClient(the_data_client)
        data_db = data_client.get_database(the_data_db)
        data_col = data_db.get_collection(the_data_cl)
    
        print "start, the index is:", index_name
    
        data_col.ensure_index([(index_name, 1)], unique=unique)
        print "run over"
    
    
    if __name__ == '__main__':
        DataClient = ''
        DataDB = ''
        DataCol = ''
    
        IndexName = ''
    
        create_mongodb_index(DataClient, DataDB, DataCol, index_name=IndexName, unique=False)
    
    

    2、若目标集合已经存在指定名称的索引

    使用create_index或则ensure_index创建与之前“唯一性unique”不同的同名索引,会出现“OperationFailure”错误。

    解决方法:

    首先,判断有无同名索引;然后判断同名索引是否为唯一索引;

    若有同名索引,且非唯一索引,则删除已有的同名索引,然后重新建立索引。

    # -*- coding:utf-8 -*-
    
    from pymongo import MongoClient
    
    
    def create_unique_index():
        """
        创建唯一索引
        :return:
        """
        the_col = MongoClient('localhost').get_database('test_db').get_collection('test_col_1')
        all_index = the_col.index_information()
        print all_index  # 打印所有索引信息
    
        has_id_index = all_index.get("id_1", False)  # 判断有无“id”索引
        if has_id_index:
            if all_index['id_1'].get('unique', False):   # id为唯一索引
                pass
            else:
                the_col.drop_index([("id", 1)])
                the_col.create_index([("id", 1)], unique=True)  # 尝试创建唯一索引
        else:
            the_col.create_index([("id", 1)], unique=True)  # 尝试创建唯一索引
    
    
    if __name__ == "__main__":
        create_unique_index()
    

     

    展开全文
  • mysql索引优化比普通查询速度快多少

    千次阅读 2019-03-19 02:05:00
    1、软件层面优化数据库查询的方法索引 优化sql 少用or 一是合理加索引;二是优化执行慢的sql(比如少用or)。 此二者相辅相成,缺一不可,如果索引,还是查询很慢,这时候就要考虑是sql的问...

    mysql索引优化比普通查询速度快多少

    一、总结

    一句话总结:普通查询全表查询,速度较慢,索引优化的话拿空间换时间,一针见血,所以速度要快很多。

    索引优化快很多
    空间换时间

     

    1、软件层面优化数据库查询的方法?

    索引
    优化sql 少用or

    一是合理加索引;二是优化执行慢的sql(比如少用or)。

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

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

     

    2、千万条数据全表查询没索引的时间量级是多少?

    几十秒

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

     

    3、MyISAM和Innodb存储数据的数据结构是什么?

    B+树

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

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

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

     

    4、MySQL分析sql语句的执行效率用的是什么?

    EXPLAIN关键字 在sql语句前加上EXPLAIN关键字即可

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

    EXPLAIN分析一下:

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

     

     

     

    二、MySQL 千万 级数据量根据(索引)优化 查询 速度(转)

    转自:MySQL 千万 级数据量根据(索引)优化 查询 速度 - phpdragon - 博客园
    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的索引分析

     

     

    转载于:https://www.cnblogs.com/Renyi-Fan/p/10556274.html

    展开全文
  •  虽然本书是针对SQL Server这个特定数据库平台来阐述理论和实践,然而,本书所体现思想和方法,完全可以运用到其他关系数据库平台上。..  翻译时间很紧,如果没有家人支持和理解,很难想象如何完成这项...
  •  虽然本书是针对SQL Server这个特定数据库平台来阐述理论和实践,然而,本书所体现思想和方法,完全可以运用到其他关系数据库平台上。..  翻译时间很紧,如果没有家人支持和理解,很难想象如何完成这项...
  • 索引是存放在模式(schema)中的一个数据库对象,索引的作用就是提高对表的检索查询速度, 索引是通过快速访问的方法来进行快速定位数据,从而减少了对磁盘的读写操作。 索引是数据库的一个对象,它不能独立存在,...
  • mysql——索引的简单介绍

    千次阅读 2020-07-19 20:32:33
    这是阿鲤对mysql的索引和事务的介绍,适合初学者;如果你之前不了解数据库,请看这两篇文章。 mysql基础mysql表的基础操作 1:索引的创建 2:索引的原理 ...我们首先说一下这些索引的创建方法: 主键..

    这是阿鲤对mysql的索引和事务的介绍,适合初学者;如果你之前不了解数据库,请看这两篇文章。

    mysql基础  mysql表的基础操作

    1:索引的创建

    2:索引的原理


    索引,是提高数据库性能的必要之选,是物美价廉的;不用加内存,调sql;只需要create index就可以了。只要执行了正确的索引,查询速度会提高百倍,千倍;

    常见的索引有:主键索引(primary key),唯一索引(unique),普通索引(index),全文索引(fulltext)

    我们首先说一下这些索引的创建方法

    主键索引:

    //在创建表的时候,直接在字段名后指定primary key
    create table user1(id int primary key, name varchar(30));
    
    //在创建表的最后,指定某列或某几列为主键索引
    create table user2(id int, name varchar(30), primary key(id));
    
    //创建表以后再添加主键 
    create table user3(id int, name varchar(30)); 
    alter table user3 add primary key(id);
    主键索引的特点:
    1:一个表中,最多有一个主键索引,当然可以使符合主键
    2:主键索引的效率高(主键不可重复)
    3:创建主键索引的列,它的值不能为null,且不能重复
    4:主键索引的列基本上是int

     

    唯一索引:

    //在表定义时,在某列后直接指定unique唯一属性。 
    create table user4(id int primary key, name varchar(30) unique); 
    
    //创建表时,在表的后面指定某列或某几列为
    unique create table user5(id int primary key, name varchar(30), unique(name)); 
    
    //创建表以后再添加唯一键
    create table user6(id int primary key, name varchar(30)); 
    alter table user6 add unique(name);
    唯一索引的特点:
    1:一个表中,可以有多个唯一索引
    2:查询效率高
    3:如果在某一列建立唯一索引,必须保证这列不能有重复数据
    4:如果一个唯一索引上指定not null,等价于主键索引

     

    普通索引:

    create table user8(
        id int primary key, 
        name varchar(20), 
        email varchar(30), 
        index(name) --在表的定义最后,指定某列为索引 
    );
    
    create table user9(
        id int primary key, 
        name varchar(20), 
        email varchar(30)); 
        alter table user9 add index(name); --创建完表以后指定某列为普通索引 
    
    create table user10(
        id int primary key, 
        name varchar(20), 
        email varchar(30)); -- 创建一个索引名为 idx_name 的索引 
       
    create index idx_name on user10(name);
    普通索引的特点:
    1:一个表中可以有多个普通索引,普通索引在实际开发中用的比较多
    2:如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引
     
    全文索引:
    当对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是有要求,要求
    表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文。如果对中文进行全文检索,可以使用
    sphinx的中文版(coreseek)。             
     
    CREATE TABLE articles ( 
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 、
        title VARCHAR(200), 
        body TEXT, 
        FULLTEXT (title,body) 
    )engine=MyISAM;

    如果你想看一次查询是否用到了索引:

    我们可以使用explain查看一次检索,是否用到了索引

    eg:

    mysql> explain select * from articles where body like '%database%'\G *************************** 1. row ***************************
            id: 1 
        select_type: SIMPLE 
        table: articles 
        type: ALL 
        possible_keys: NULL 
        key: NULL <== key为null表示没有用到索引 
        key_len: NULL 
        ref: NULL 
        rows: 6 
        Extra: Using where 
    1 row in set (0.00 sec)

    我们看来索引的闯将方式,接下来让我们看看索引的原理把;

    再看索引的原理之前,请你先看一下阿鲤写的这篇B树的文章,将有助于你了解下面的内容。

    B树的介绍

    首先让我们看看索引的底层数据结构:hash 和 B+树;

    但是我们这里注重说B+树,因为mysql更多用的是B+树;如果你了解B树,那么B+树你就很容易理解了;

    B+树相比B树多了一下两方面:

    1:B+树相比B树去除了非叶子节点的荣誉的一个,他会把一个key对应一个孩子节点

    2:B+树把每一层的叶子节点链接了起来,这样就可以提高范围查找的效率

    提出一个问题:B+树里一个节点的大小应该是多少?

    B+树为了提高IO效率,一般每个节点的大小为一个页的倍数最为合适;那么为什么呢?

    因为如果一个节点的大小小于1页,那么读取这个节点的时候其实也会读出1页,造成资源的浪费。如果一个节点的大小大于1页,比如1.2页,那么读取这个节点的时候会读出2页,也会造成资源的浪费。所以为了不造成浪费,所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

    mysql里的页:

    首先Mysql的基本存储结构是页(记录都存在页里边):各个数据页可以组成一个双向链表;而每个数据页中的记录又可以组成一个单向链表。每个数据页都会为存储在它里边儿的记录生成一个页目录,在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录

    以其他列(非主键)作为搜索条件:只能从最小记录开始依次遍历单链表中的每条记录。

    所以说,如果我们写 select * from user where username='你好'这样没有进行任何优化的sql语句,默认会这样做:

    1:定位到记录所在的页

    2:需要遍历双向链表,找到所在的页

    3:从所在的页内中查找相应的记录

    4:由于不是根据主键查询,只能遍历所在页的单链表了

    关于数据库的页,阿鲤在mysql基础中有提到过,链接就在该文章的第一行,大家可以看看。

    那么B+树在索引匹配的时候是怎样的呢?

    1:在B+树中比较查找;

    2:最左匹配原则:针对联合索引,优先匹配左边的条件:

    eg:如有索引 (a,b,c,d),查询条件 a=1 and b=2 and c>3 and d=4,则会在每个节点依次命中a、b、c,无法命中d。(c已经是范围查询了,d肯定是排不了序了)

    以上我们讲的主键索引就是聚合索引,那么什么是非聚合索引呢

    非聚合索引,也被细分为上面说的三种索引:唯一索引,普通索引,全文索引;

    我们在建立一个非聚合索引的时候,mysql会建立一个属于改元素和主键的B+树。这样我们在使用该元素进行索引的时候,mysql首先会找到该元素对应的主键,然后根据主键再进行聚合索引,找到对应的元素。

    展开全文
  • 什么是NoSQL数据库

    2014-07-21 23:24:20
    这是非常简单的方法,程序方面也不需要进行变更,但需要一些费用。若要购买性能翻倍的服务器,需要花费的资金往往不只是原来的2倍,可能需要多达5到10倍。这种方法虽然简单,但是成本较高。 另一方面,增大规模指...
  • 如果myisamchk失败,试试下面的方法。请注意只有用--log-bin选项启动了MySQL从而启用二进制日志它才工作; 1. 恢复原mysqldump备份,或二进制备份。 2. 执行下面的命令重新更新二进制日志: 3. shell> mysqlbinlog ...
  • 1.5 普通Oracle DBA日常工作 12 1.6 几点建议 12 1.6.1 知道何时寻求帮助 12 1.6.2 记住你并不孤单 13 1.6.3 站在全局看问题 13 1.6.4 首先是不要造成损害 13 第2章 关系数据库建模及数据库设计 14 ...
  •  9.2 索引的类型和特点  9.2.1 堆  9.2.2 聚集索引  9.2.3 非聚集索引  9.2.4 其他类型的索引  9.2.5 访问数据的方式  9.3 创建索引  9.3.1 直接方法和间接方法  9.3.2 使用CREATE INDEX语句  9.3.3 ...
  • 因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引) 14、说明:前10条记录 select top 10 * form ...
  • B Tree索引的结构及特点 218 Oracle如何扫描索引 223 案例二:巧用索引降序扫描解决性能问题 224 通过索引访问数据时的成本计算 225 案例三:降低clustering factor,解决性能问题 227 总结 228 SQL优化与...
  • 11.6 关于索引的常见问题和神话 426 11.6.1 视图能使用索引吗 427 11.6.2 Null和索引能协作吗 427 11.6.3 外键是否应该索引 429 11.6.4 为什么没有使用我的索引 430 11.6.5 神话:索引中从不重用空间 435 ...
  • 11.6 关于索引的常见问题和神话 472 11.6.1 视图能使用索引吗? 472 11.6.2 Null和索引能协作吗? 472 11.6.3 外键是否应该索引? 475 11.6.4 为什么没有使用我的索引? 476 11.6.5 神话:索引中从不重用...
  • mysql中like模糊查询优化

    千次阅读 2018-11-02 09:37:30
    这是我在一个百万级数据库数据导出遇到问题 比如这个语句在这个数据库查询很慢: select a from tms where b ...//将haha字段和time字段索引(联合索引还是普通索引自己看情况) select a from news where b li...

    这是我在一个百万级数据库数据导出遇到的问题
    比如这个语句在这个数据库查询很慢:
    select a from tms where b like ‘%haha%’ order by time limit 100;

    第一种优化方法(注意:这种方法只适用于haha开头的):
    //将haha字段和time字段加索引(联合索引还是普通索引自己看情况)
    select a from news where b like ‘haha%’ order by time limit 100;

    第二种优化方法(注意:这种方法只适用于mysql引擎是myisam的):
    //语句不变,将haha字段加为全文索引,time字段变为普通索引
    select a from news where b like ‘%haha%’ order by time limit 100;

    第三种方法:
    这是因为我必须用 %haha% 这种方式,而且还不能更换数据库引擎的情况下。
    我是把like的操作放到了java程序中来处理,一次取定量数据进行筛选,如果没取够,再取,再筛选……这样就避免了完全扫表了,只扫部分数据。成功解决。

    PS: 如果要select很多字段,其中又有超大类型的字段,比如 longtext,尽量先查询其他字段,然后根据id或者其他唯一字段来查询这个longtext字段。因为大字段影响效率。

    展开全文
  • 16-索引的知识及索引创建多种方法实战.avi 17-索引生效的基本条件说明.avi 18-主键-唯一-普通索引创建及删除小结.avi 19-DML语句之insert知识讲解.avi 20-DQL之select知识实战讲解.avi 21-DQL之select多表连表查询...
  • 6.2.1 使用FREETEXT来搜索全文索引的列 194 6.2.2 使用CONTAINS来搜索单词 195 6.3 高级搜索 196 6.3.1 使用CONTAINS和通配符来搜索 196 6.3.2 使用CONTAINS来搜索变形匹配 196 6.3.3 使用CONTAINS根据...
  • mysql中like模糊查询优化

    万次阅读 2016-01-28 18:36:27
    这是我在一个百万级数据库遇到问题 比如这个语句在这个数据库查询很慢:  select a from news where b like ‘%haha%’ order by time ... //将haha字段和time字段索引(联合索引还是普通索引自己看情况)  
  • 也是一个能用于数据集成、数据分析和报表解决方案商业智能平台,为用户提供了强大、集成、便于使用工具,使系统管理员与普通用户能更方便、更快捷地管理数据库或设计、开发应用程序。 目录 封面 -33 封底 803 ...
  • 也是一个能用于数据集成、数据分析和报表解决方案商业智能平台,为用户提供了强大、集成、便于使用工具,使系统管理员与普通用户能更方便、更快捷地管理数据库或设计、开发应用程序。 目录 封面 -33 封底 803 ...
  • MYSQL培训经典教程.rar

    2009-11-16 21:37:03
    对于每一种的方法,虽然不一定都是非常实用的方法,读者能通过这些方法,熟悉MySQL的特点和强大的功能。 第一章简单介绍了MySQL的历史、特点,同时对SQL的语法进行了简单的介绍。如果读者对第一章的内容不能很好的...
  • Toad 使用快速入门

    2008-11-27 15:22:14
    在schema browser帮助下,可以方便浏览数据库内部各类数据库对象,比如表,索引,序列,存储过程等,而且可以方便倒出DDL语句和进行各种修改,重建工作。定位到某一类对象上,这列对象允许操作都会自动列...

空空如也

空空如也

1 2 3 4 5 6
收藏数 112
精华内容 44
关键字:

数据库加普通索引的方法