精华内容
下载资源
问答
  • MySQL创建复合索引

    千次阅读 2016-01-29 10:30:01
    MySQL数据库中,创建复合索引的时候,不知道在创建过程中哪个列在前面,哪个列该在后面,用以下方式即可: select count(distinct first_name)/count(*) as first_name_selectivity, count(distinct last_...
    在MySQL数据库中,创建复合索引的时候,不知道在创建过程中哪个列在前面,哪个列该在后面,用以下方式即可:


    select count(distinct first_name)/count(*) as first_name_selectivity,
    count(distinct last_name)/count(*) as last_name_selectivity,
    count(*)
    from actor\G


    mysql> select count(distinct first_name)/count(*) as first_name_selectivity,
        -> count(distinct last_name)/count(*) as last_name_selectivity,
        -> count(*)
        -> from actor\G
    *************************** 1. row ***************************
    first_name_selectivity: 0.6400
     last_name_selectivity: 0.6050
                  count(*): 200
    1 row in set (0.01 sec)


    first_name_selectivity: 0.6400,因此 first_name 列的选择性更高,所以答案将其作为索引列的第一列:

    mysql> alter table yoon add key (first_name,last_name);

    展开全文
  • #mysql复合索引和单列索引的单表查询分析 @[mysql, 复合索引, 索引] ##前言 MySql的索引对查询速度的提高非常明显,但是索引种类很多,如复合索引、单列索引,那它们有什么区别和联系呢?下面我会对两者进行分析。 ##...

    mysql复合索引和单列索引的单表查询分析

    前言

    MySql的索引对查询速度的提高非常明显,但是索引种类很多,如复合索引、单列索引,那它们有什么区别和联系呢?下面我会对两者进行分析。

    数据库创建语句

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `age` int(4) DEFAULT NULL,
      `name` varchar(20) DEFAULT NULL,
      `sex` int(3) DEFAULT NULL,
      `nickname` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `testKey` (`name`,`age`,`nickname`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
    
    INSERT INTO `user` VALUES ('1', '20', 'test1', '1', 'ntest1');
    INSERT INTO `user` VALUES ('2', '21', 'test2', '0', 'ntest2');
    INSERT INTO `user` VALUES ('3', '24', 'test1', '1', 'ntest3');
    INSERT INTO `user` VALUES ('4', '23', 'test4', '0', 'ntest4');
    INSERT INTO `user` VALUES ('5', '24', 'test5', '1', 'ntest5');
    INSERT INTO `user` VALUES ('6', '25', 'test6', '0', 'ntest6');
    

    部分关键字说明

    explain:mysql查看执行计划的关键字,放在sql语句之前。
    type:访问类型,表示找到所查询数据的方法,常见的有ref、range、index、all等。
    keys:索引类型,表示mysql此次查询中使用的索引,多个用逗号分开。
    rows:遍历行数,表示mysql此次查询遍历的行数大小,该值越小,查询速度会越快,是一个估计值,非绝对正确的。

    单表复合索引(name, age, nickname)触发条件

    执行SQLtype查询条件keysrows
    explain select * from user where name='test1'refname=‘test1’testKey2
    explain select * from user where age='21'ALLage=‘21’/6
    explain select * from user where nickname='ntest1'ALLnickname=‘ntest1’/6
    explain select * from user where name='test1' and age='21'refname=‘test1’ and age=‘21’testKey1
    explain select * from user where name='test1' and nickname='ntest1'refname=‘test1’ and nickname=‘ntest1’testKey2
    explain select * from user where age='21' and nickname='ntest1'ALLage=‘21’ and nickname=‘ntest1’/6
    explain select * from user where name='test1' and age='21' and nickname='ntest1'refname=‘test1’ and age=‘21’ and nickname=‘ntest1’testKey1

    通过上面表格,我们会发现,复合索引(name,age,nickname)和它们三列的单个索引是有区别的(该案例不做复合索引和单列索引的性能分析)主要区别有以下几点:

    1. 复合索引中,只有最左边的一列单独使用才会触发索引,其他的列单个使用无法触发索引。
    2. 复合索引中,从最左边开始,相连的两个或多个会触发索引(相连和不相连的性能不同),如果没有最左边的列,后面的无论是否相连都不会触发索引
    3. 通过分析我们可以发现,几个列的复合索引,就相当与有几个索引,如复合索引(name,age,nickname)相当与name索引,(nameage)索引,(name,age,nickname)索引(注意,后面两个索引不能再按复合索引算,只是为了解释说明
    4. where条件后面的顺序不影响复合索引的触发如age=‘21’ and name=‘test1’一样会触发复合索引–mysql会对查询条件顺序进行优化,我们无需担心顺序问题,但是为了更好理解,建议合理安排顺序

    单表复合索引的性能分析

    执行SQLtype查询条件keysrows
    explain select * from user where name='test1' and age='21'refname=‘test1’ and age=‘21’testKey1
    explain select * from user where name='test1' and nickname='ntest1'refname=‘test1’ and nickname=‘ntest1’testKey2
    explain select * from user where name='test1' and age='21' and nickname='ntest1'refname=‘test1’ and age=‘21’ and nickname=‘ntest1’testKey1
    explain select * from user where name='test1' and sex=1refname=‘test1’ and sex=1testKey2

    上面表格中,第一行和第二行都走了索引,但是第一行是相连的两列,rows是1,这里我们可以说是使用了(nameage)索引–该索引并发真实存在,只是为了区分效果;第二行是不相连的两列rows是2,然后第四行是使用了复合索引的第一列name和非复合索引中的列作为查询条件,rows同样是2,非相连的两列作为查询条件时,复合索引相当与使用了第一列作为查询条件。产生的原因:mysql在进行查询时,会根据索引筛选出复合索引的行,如果存在查询条件不在索引中的列,会进行二次筛选(即根据筛选出来的行进行二次查询),导致遍历的行数增加

    这里指出部分查询条件会导致全表扫描

    执行SQL原因
    explain select * from user where name='test1'+'1';字符串拼接
    explain select * from user where name!='test1'!=
    explain select * from user where name in('test1','test2')in
    explain select * from user where name is not nullnot null
    explain select * from user where name like 'test%'like
    explain select * from user where name='test1' or name='test2'or
    explain select * from user where age BETWEEN 21 and 24;between value1 and value2

    特殊注意

    1. 使用Mysql的CONCAT函数拼接条件一样会使用索引。
    2. 在使用in时,如果只有一个值,则等价于使用 =符号,会触发索引,包含两个或多个值,则索引失效。
    3. 在使用not in时,无论多少个值,索引都会失效。
    4. 使用null关键字查询时,无论值是否有为空的,都会触发索引。
    5. 在使用like关键字时,只要使用了%号进行模糊匹配,就会使索引失效。
    6. 网上说使用is null会使索引失效,我测试的结果是,使用is null,无论是复合索引还是单列索引都能触发索引。

    总结

    在我们使用单列索引和复合索引时,需要注意以下几点:

    1. 常用的字段放在第一列,经常和第一列一起使用的字段放在第二列,如用户表的电话和姓名,身份证表的身份照号和姓名,如果超过两列,则注意其顺序。
    2. 条件查询时,尽可能所有字段都有索引(如sex这种情况例外,因为sex的值只有三个,冗余性太高,定位比较差,不如全表检索快),这样能提高很多效率。
    3. 查询时避免会使索引失效的情况发生,如or条件,可以使用union或者union all来达到相同效果。
    4. 索引能提高查询效率,但是过多的索引,同样会降低我们的修改操作效率,对此,我们创建索引需要合理,在使用频率较低的情况下,尽量不要创建索引。
    5. select *或许性能和指定字段相差不是非常大,但是代码的可读性降低了很多,不推荐使用。
    展开全文
  • 关于MySQL复合索引优化

    千次阅读 2012-08-18 11:45:29
    最近对两个开源系统进行反向工程ER图生成后,对比发现一个系统其中一个表中的复合索引的列个数对查询的效率有较大的影响~~ 于是上网查了下相关的资料:(关于复合索引优化的) 两个或更多个列上的索引被称作复合...

    最近对两个开源系统进行反向工程ER图生成后,对比发现一个系统其中一个表中的复合索引的列个数对查询的效率有较大的影响~~
    于是上网查了下相关的资料:(关于复合索引优化的)

    两个或更多个列上的索引被称作复合索引。
    利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处。
    所以说创建复合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。
    如:建立 姓名、年龄、性别的复合索引。


    复合索引的建立原则:

    如果您很可能仅对一个列多次执行搜索,则该列应该是复合索引中的第一列。如果您很可能对一个两列索引中的两个列执行单独的搜索,则应该创建另一个仅包含第二列的索引。
    如上图所示,如果查询中需要对年龄和性别做查询,则应当再新建一个包含年龄和性别的复合索引。
    包含多个列的主键始终会自动以复合索引的形式创建索引,其列的顺序是它们在表定义中出现的顺序,而不是在主键定义中指定的顺序。在考虑将来通过主键执行的搜索,确定哪一列应该排在最前面。
    请注意,创建复合索引应当包含少数几个列,并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值,其后果是内存溢出和性能降低。

            
    复合索引对排序的优化:

    复合索引只对和索引中排序相同或相反的order by 语句优化。
    在创建复合索引时,每一列都定义了升序或者是降序。如定义一个复合索引:

    	CREATE INDEX idx_example 
    	ON table1 (col1 ASC, col2 DESC, col3 ASC)


    其中 有三列分别是:col1 升序,col2 降序, col3 升序。现在如果我们执行两个查询
    1:Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC
      和索引顺序相同
    2:Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC
     和索引顺序相反
    查询1,2 都可以别复合索引优化。
    如果查询为:
    Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC
      排序结果和索引完全不同时,此时的 查询不会被复合索引优化。


    查询优化器在在where查询中的作用:

    如果一个多列索引存在于 列 Col1 和 Col2 上,则以下语句:Select   * from table where   col1=val1 AND col2=val2 查询优化器会试图通过决定哪个索引将找到更少的行。之后用得到的索引去取值。
    1. 如果存在一个多列索引,任何最左面的索引前缀能被优化器使用。所以联合索引的顺序不同,影响索引的选择,尽量将值少的放在前面。
    如:一个多列索引为 (col1 ,col2, col3)
        那么在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜索会有作用。

    SELECT * FROM tb WHERE  col1 = val1
    SELECT * FROM tb WHERE  col1 = val1 and col2 = val2
    SELECT * FROM tb WHERE  col1 = val1 and col2 = val2  AND col3 = val3


    2. 如果列不构成索引的最左面前缀,则建立的索引将不起作用。
    如:

    SELECT * FROM  tb WHERE  col3 = val3
    SELECT * FROM  tb  WHERE  col2 = val2
    SELECT * FROM  tb  WHERE  col2 = val2  and  col3=val3


     
    3. 如果一个 Like 语句的查询条件不以通配符起始则使用索引。
    如:%车 或 %车%   不使用索引。
        车%              使用索引。
    索引的缺点:
    1.       占用磁盘空间。
    2.       增加了插入和删除的操作时间。一个表拥有的索引越多,插入和删除的速度越慢。如 要求快速录入的系统不宜建过多索引。

    展开全文
  • mysql复合索引

    千次阅读 2019-01-20 21:31:23
    问题:MYSQL INNodb建立复合索引 a,b,c;那么 查询条件 where a =xxx and c= xxx 能用到索引嘛? 回答:可以。 概念: 单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上; 用户可以在多个列上建立...

    问题:MYSQL INNodb建立复合索引 a,b,c;那么 查询条件 where a =xxx and c= xxx 能用到索引嘛?

    回答:可以。

    概念:     单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上;     用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);     复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;     同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引;     设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效;

    使用:     创建索引     create index idx1 on table1(col1,col2,col3)      查询     select * from table1 where col1= A and col2= B and col3 = C     这时候查询优化器,不在扫描表了,而是直接的从索引中拿数据,因为索引中有这些数据,这叫覆盖式查询,这样的查询速度非常快;  

     

    注意事项:     1、对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;     select * from table1 where col1=A AND col2=B AND col3=D     如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引
        2、何时是用复合索引     根据where条件建索引是极其重要的一个原则;     注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中

        3、复合索引会替代单一索引么     如果索引满足窄索引的情况下可以建立复合索引,这样可以节约空间和时间

    备注:     对一张表来说,如果有一个复合索引 on   (col1,col2),就没有必要同时建立一个单索引 on col1;     如果查询条件需要,可以在已有单索引 on col1的情况下,添加复合索引on (col1,col2),对于效率有一定的提高     同时建立多字段(包含5、6个字段)的复合索引没有特别多的好处,相对而言,建立多个窄字段(仅包含一个,或顶多2个字段)的索引可以达到更好的效率和灵活性

    展开全文
  • MySQL 创建主键,外键和复合主键的方法,需要的朋友可以参考下。
  • 用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引); 复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引; 同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引...
  • mySql的普通索引和复合索引

    千次阅读 2018-02-22 18:10:08
    mySql的普通索引和复合索引有关普通索引和组合索引问题:索引分单列索引和组合索引:单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引;组合索引,即一个索包含多个列。 MySQL索引...
  • 创建复合索引,包含a,b,c三个字段,并查看索引性信息 <1> 使用a,b,c作为where查询条件,explain查看执行计划:使用了索引 <2> 使用a,c作为where查询条件,explain查看执行计划:使用了索引 ...
  • Mysql索引与 Sql语句优化表例子索引关于表设计(DDL)中创建索引条件中建有索引的字段, 导致索引失效语句 表例子 CREATE TABLE IF NOT EXISTS `order` ( `id` int UNSIGNED NOT NULL COMMENT '订单编号', `buyer_id...
  • Mysql创建索引

    2019-02-14 09:38:47
    一、MySQL 索引 1、MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 打个比方,如果合理的设计且使用索引MySQL是一辆兰博基尼的话,那么没有设计和使用索引MySQL就是一个...
  • 两个或更多个列上的索引被称作复合索引。 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿...所以说创建复合索引时,应该仔细考虑列的顺序。
  • MySQL索引类型包括: 一、普通索引 这是最基本的索引,它没有任何限制。它有以下几种创建方式: 1.创建索引 复制代码代码如下: CREATE INDEX indexName ON mytable(username(length)); 如果是CHA
  • MySQL创建管理索引与数据完整性

    多人点赞 2020-05-24 23:50:18
    今天我们谈一谈MySQL数据库的创建管理索引与数据的完整性。 你好WO SHI 七七卡卡 ^ _ ^ 先说明一下,数据库的的编译器有很多,我用的是php study2016版本;(MySQL的注意事项我就不说了) 创建管理索引与数据完整性...
  • mysql创建索引

    2015-07-28 12:10:02
    mysql 如何创建索引呢,这个其实很简单 create index或者为己有字段增加索引 ALTER TABLE `table_name` ADD UNIQUE (`column`)即可了。 mysql索引作用 在索引列上,除了有序查找之外,数据库利用各种各样的快速...
  • Mysql复合索引最左匹配原则以及索引失效条件复合索引最左匹配原则用EXPLAIN 来查看语句是否用到了索引索引失效的条件一般性建议 复合索引最左匹配原则 复合索引又叫联合索引。两个或更多个列上的索引被称作复合索引...
  • 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧...
  • mysql复合索引优化

    千次阅读 2012-04-16 23:10:52
    很多时候,我们在mysql创建索引,但是某些查询还是很慢,根本就没有使用到索引! 一般来说,可能是某些字段没有创建索引,或者是组合索引中字段的顺序与查询语句中字段的顺序不符。 看下面的例子: 假设有...
  • ( 转 ) mysql复合索引、普通索引总结 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行...
  • mysql 创建索引

    2016-12-20 12:36:35
    mysql的索引有以下几种  1,主键索引  2,唯一索引 ... 4,复合索引  5,全文索引  添加索引的sql语句分别是   主键索引 alter table tablenane add primary key (column);  唯一索引 alter
  • 那么当查询条件为2个及以上时,我们是创建多个单列索引还是创建一个联合索引好呢?他们之间的区别是什么?哪个效率高呢?我在这里详细测试分析下。 一、联合索引测试 注:Mysql版本为 5.7.20 创建测试表(表记录数...
  • MySQL循环语句批量创建数据 mysql> delimiter // mysql> create procedure pr3() -> begin -> declare i int; -> set i=2; -> while i<100000 do ->insert into runoob_tbl(runoob_id,...
  • 【推荐】mysql联合 索引(复合索引)的探讨

    万次阅读 多人点赞 2019-02-16 22:43:06
    Mysql联合 索引(复合索引)的使用原则 命名规则:表名_字段名 需要加索引的字段,要在where条件中。 数据量少的字段不需要加索引。最窄的字段放在键的左边。 如果where条件中是OR关系,必须所有的or条件都必须...
  • 能够写出创建索引的SQL语句 1. 索引的介绍 索引MySQL中也叫做“键”,它是一个特殊的文件,它保存着数据表里所有记录的位置信息,更通俗的来说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 应...
  • mysql复合索引注意事项

    千次阅读 2017-05-03 19:59:04
     用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);  复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;  同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也...
  • mysql 复合索引 总结

    千次阅读 2016-06-30 13:47:59
    对于复合索引:MySQL从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 24,662
精华内容 9,864
关键字:

mysql创建复合索引语句

mysql 订阅