精华内容
下载资源
问答
  • Mysql创建索引

    2019-02-14 09:38:47
    一、MySQL 索引 1、MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 打个比方,如果合理的设计且使用索引MySQL是一辆兰博基尼的话,那么没有设计和使用索引MySQL就是一个...

    一、MySQL 索引

    1.1、什么是索引?

    索引是辅助存储引擎高效获取数据的一种数据结构,索引可以大大提高MySQL的检索速度。

     很多人形象的说索引就是数据的目录,便于存储引擎快速的定位数据。

    图片

    MySQL索引

    MySQL内部其实是分为server层和存储引擎层的:

    • server层负责处理一些通用的事情,诸如连接管理、SQL语法解析、分析执行计划之类的东西

    • 存储引擎层负责具体的数据存储,诸如数据是存储到文件上还是内存里,具体的存储格式是什么样的之类的。我们现在基本都使用InnoDB存储引擎,其他存储引擎使用的非常少了,所以我们也就不涉及其他存储引擎了。

    MySQL中一条SQL语句的执行是通过server层和存储引擎层的多次交互才能得到最终结果的。

    1.2、MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

    打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。比如以WordPress来说,其多个数据表都会对经常被查询的字段添加索引。

    转自网络

    注意:索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。

    1.3、创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

    实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

    1.4、上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

    a、虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要维护和保存一下索引文件。

    b、建立索引会占用磁盘空间的索引文件。


    二、Mysql索引创建

    在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

    Mysql不同类型的索引创建方式有细微区别:

    2.1、普通索引

    这是最基本的索引,它没有任何限制,比如上文中为title字段创建的索引就是一个普通索引,MyIASM中默认的BTREE类型的索引,也是我们大多数情况下用到的索引。

    CREATE INDEX indexName ON mytable(username(length)); //直接创建索引
    
    ALTER table tableName ADD INDEX indexName(columnName);//修改表结构的方式添加索引
    
    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    INDEX [indexName] (username(length))  
     
    );  //创建表的时候同时创建索引
    
    

    如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。

    2.2、唯一索引

    与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。

    01	–创建唯一索引
    02 	CREATE UNIQUE INDEX indexName ON table(column(length))
    03 	–修改表结构
    04 	ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
    05 	–创建表的时候直接指定
    06 	CREATE TABLE `table` (
    07 	`id` int(11) NOT NULL AUTO_INCREMENT ,
    08 	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    09 	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    10 	`time` int(10) NULL DEFAULT NULL ,
    11 	PRIMARY KEY (`id`),
    12 	UNIQUE indexName (title(length))

    2.3. 全文索引(FULLTEXT)

    MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

    01 	–创建表的适合添加全文索引
    02 	CREATE TABLE `table` (
    03 	`id` int(11) NOT NULL AUTO_INCREMENT ,
    04 	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    05 	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    06 	`time` int(10) NULL DEFAULT NULL ,
    07 	PRIMARY KEY (`id`),
    08 	FULLTEXT (content)
    09 	);
    10 	–修改表结构添加全文索引
    11 	ALTER TABLE article ADD FULLTEXT index_content(content)
    12 	–直接创建索引
    13 	CREATE FULLTEXT INDEX index_content ON article(content)

    2.4、组合索引(最左前缀匹配原则)

    平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引:ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:

    –title,time

    –title

    为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。

    简单的理解就是只从最左面的开始组合。联合索引遵循最左前缀原则(必须带着最左边的列做条件才能命中索引),且从出现范围开始索引失效。

    并不是只要包含这两列的查询都会用到该组合索引,如下面的几个SQL所示:

    1 	–使用到上面的索引
    2 	SELECT * FROM article WHREE title='测试' AND time=1234567890;
    3 	SELECT * FROM article WHREE utitle='测试';
    4 	–不使用上面的索引
    5 	SELECT * FROM article WHREE time=1234567890;

    2.5、Mysql删除索引和查看索引信息

    DROP INDEX index_name ON talbe_name;//删除索引
    
    SHOW INDEX FROM table_name; \G //查看索引信息,\G用来格式化输出信息

    三、索引使用情景

    3.1、什么情况下使用索引:

    表的主关键字

    自动建立唯一索引

    如zl_yhjbqk(用户基本情况)中的hbs_bh(户标识编号)

    表的字段唯一约束

    ORACLE利用索引来保证数据的完整性

    如lc_hj(流程环节)中的lc_bh+hj_sx(流程编号+环节顺序)

    直接条件查询的字段

    在SQL中用于条件约束的字段

    如zl_yhjbqk(用户基本情况)中的qc_bh(区册编号)

    select * from zl_yhjbqk where qc_bh=’7001’

    查询中与其它表关联的字段

    字段常常建立了外键关系

    如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)

    select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’

    查询中排序的字段

    排序的字段如果通过索引去访问那将大大提高排序速度

    select * from zl_yhjbqk order by qc_bh(建立qc_bh索引)

    select * from zl_yhjbqk where qc_bh=’7001’ order by cb_sx(建立qc_bh+cb_sx索引,注:只是一个索引,其中包括qc_bh和cb_sx字段)

    查询中统计或分组统计的字段

    select max(hbs_bh) from zl_yhjbqk

    select qc_bh,count(*) from zl_yhjbqk group by qc_bh

    3.2、什么情况下应不建或少建索引:

    表记录太少

    如果一个表只有5条记录,采用索引去访问记录的话,那首先需访问索引表,再通过索引表访问数据表,一般索引表与数据表不在同一个数据块,这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出,处理速度显然会比用索引快。

    如表zl_sybm(使用部门)一般只有几条记录,除了主关键字外对任何一个字段建索引都不会产生性能优化,实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引,而是自动执行全表访问。如:

    select * from zl_sybm where sydw_bh=’5401’(对sydw_bh建立索引不会产生性能优化)

    经常插入、删除、修改的表

    对一些经常处理的业务表应在查询允许的情况下尽量减少索引,如zl_yhbm,gc_dfss,gc_dfys,gc_fpdy等业务表。

    数据重复且分布平均的表字段

    假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。

    经常和主字段一块查询但主字段索引值比较多的表字段

    如gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作 标志来具体查询某一笔收款的情况,如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间,从实际上分析一笔收款如果按收费序号索引就已 经将记录减少到只有几条,如果再按后面的几个字段索引查询将对性能不产生太大的影响。


    四、千万级MySql在建立索引优化时需要注意的问题

    设计好MySQL的索引可以让你的数据库飞起来,大大的提高数据库效率。设计MySql索引的时候有以下几点注意事项:

    1.创建索引

    对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。

    如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。

    2.复合索引

    比如有一条语句是这样的:select * from users where area=’beijing’ and age=22;

    如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀

    特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

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

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

    4.使用短索引

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

    5.排序的索引问题

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

    6.like语句操作

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

    7.不要在列上进行运算

    select * from users where YEAR(add date)

    8.不使用NOT IN操作

    NOT IN操作不会使用索引将进行全表扫描,NOT IN可以用NOT EXISTS代替

    注意事项:

    首先,应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

    其次,在建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。


    参考链接:

    mysql索引总结----mysql 索引类型以及创建

    Mysql创建索引

    MySQL的LIMIT这么差劲的吗

    展开全文
  • mysql创建索引

    2015-07-28 12:10:02
    mysql 如何创建索引呢,这个其实很简单 create index或者为己有字段增加索引 ALTER TABLE `table_name` ADD UNIQUE (`column`)即可了。 mysql索引作用 在索引列上,除了有序查找之外,数据库利用各种各样的快速...

    转载自:http://www.111cn.net/database/mysql/53681.htm

    mysql 如何创建索引呢,这个其实很简单 create index或者为己有字段增加索引 ALTER TABLE `table_name` ADD UNIQUE (`column`)即可了。

    mysql索引作用

    在索引列上,除了有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。

    mysql索引创建规则:

    1、表的主键、外键必须有索引;
    2、数据量超过300的表应该有索引;
    3、经常与其他表进行连接的表,在连接字段上应该建立索引
    4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
    5、索引应该建在选择性高的字段上;
    6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
    7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
    A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
    B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,
    则可以建立复合索引;否则考虑单字段索引;
    C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
    D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
    E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
    8、频繁进行数据操作的表,不要建立太多的索引;
    9、删除无用的索引,避免对执行计划造成负面影响; 以上是一些普遍的建立索引时的判断依据。
    一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。
    因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,
    索引对于插入、删除、更新操作也会增加处理上的开销。另外,过多的复合索引,在有单字段索引的情况下,
    一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。


    2.  创建索引

    在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
    1.ALTER TABLE

    ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

     

     代码如下复制代码

    ALTER TABLE table_name ADD INDEX index_name (column_list)

    ALTER TABLE table_name ADD UNIQUE (column_list)

    ALTER TABLE table_name ADD PRIMARY KEY (column_list)

     

    其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。
    2.CREATE INDEX

    CREATE INDEX可对表增加普通索引或UNIQUE索引。

     

     代码如下复制代码

    CREATE INDEX index_name ON table_name (column_list)

    CREATE UNIQUE INDEX index_name ON table_name (column_list)

     

    table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。
    3.索引类型

    在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。

    PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。

    下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

     代码如下复制代码

     

    ALTER TABLE students ADD PRIMARY KEY (sid)

    4.  删除索引

    可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

     

     代码如下复制代码

    DROP INDEX index_name ON talbe_name

    ALTER TABLE table_name DROP INDEX index_name

    ALTER TABLE table_name DROP PRIMARY KEY

     

    其中,前两条语句是等价的,删除掉table_name中的索引index_name。

    第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

    如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

     

    5.查看索引

     代码如下复制代码

    mysql> show index from tblname;

    mysql> show keys from tblname;

    · Table

    表的名称。

    · Non_unique

    如果索引不能包括重复词,则为0。如果可以,则为1。

    · Key_name

    索引的名称。

    · Seq_in_index

    索引中的列序列号,从1开始。

    · Column_name

    列名称。

    · Collation

    列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。

    · Cardinality

    索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。

    · Sub_part

    如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。

    · Packed

    指示关键字如何被压缩。如果没有被压缩,则为NULL。

    · Null

    如果列含有NULL,则含有YES。如果没有,则该列含有NO。

    · Index_type

    用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

    · Comment

    一些关于过引的常用语句

     代码如下复制代码

    1.添加PRIMARY KEY(主键索引)

    mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

    2.添加UNIQUE(唯一索引)

    mysql>ALTER TABLE `table_name` ADD UNIQUE (
    `column`
    )
    3.添加INDEX(普通索引)

    mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

    4.添加FULLTEXT(全文索引)

    mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

    5.添加多列索引

    mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )


    展开全文
  • mysql 创建索引

    千次阅读 2018-08-22 15:35:11
    MySQL的Explain关键字查看是否使用索引 explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。简单讲,它的作用就是分析查询性能。  explain关键字的使用...

    MySQL的Explain关键字查看是否使用索引

    explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。简单讲,它的作用就是分析查询性能。

      explain关键字的使用方法很简单,就是把它放在select查询语句的前面。

      mysql查看是否使用索引,简单的看type类型就可以。如果它是all,那说明这条查询语句遍历了所有的行,并没有使用到索引。

     

      比如:explain select * from company_info where cname like '%小%'

    explain select * from company_info where cname like '小%'

     

    1)、id列数字越大越先执行,如果说数字一样大,那么就从上往下依次执行,id列为null的就表是这是一个结果集,不需要使用它来进行查询。
     
    2)、select_type列常见的有:
    A:simple:表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,外层的查询为simple,且只有一个
    B:primary:一个需要union操作或者含有子查询的select,位于最外层的单位查询的select_type即为primary。且只有一个
    C:union:union连接的两个select查询,第一个查询是dervied派生表,除了第一个表外,第二个以后的表select_type都是union
    D:dependent union:与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响
    E:union result:包含union的结果集,在union和union all语句中,因为它不需要参与查询,所以id字段为null
    F:subquery:除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery
    G:dependent subquery:与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
    H:derived:from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select
     
    3)、table
    显示的查询表名,如果查询使用了别名,那么这里显示的是别名,如果不涉及对数据表的操作,那么这显示为null,如果显示为尖括号括起来的<derived N>就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>,与<derived N>类似,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集。
     
    4)、type
    依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL,除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引
    A:system:表中只有一行数据或者是空表,且只能用于myisam和memory表。如果是Innodb引擎表,type列在这个情况通常都是all或者index
    B:const:使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描
    C:eq_ref:出现在要连接过个表的查询计划中,驱动表只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为not null,唯一索引和主键是多列时,只有所有的列都用作比较时才会出现eq_ref
    D:ref:不像eq_ref那样要求连接顺序,也没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见与辅助索引的等值查找。或者多列主键、唯一索引中,使用第一个列之外的列作为等值查找也会出现,总之,返回数据不唯一的等值查找就可能出现。
    E:fulltext:全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引
    F:ref_or_null:与ref方法类似,只是增加了null值的比较。实际用的不多。
    G:unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值
    H:index_subquery:用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
    I:range:索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。
    J:index_merge:表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取所个索引,性能可能大部分时间都不如range
    K:index:索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
    L:all:这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。
     
    5)、possible_keys
    查询可能使用到的索引都会在这里列出来
     
    6)、key
    查询真正使用到的索引,select_type为index_merge时,这里可能出现两个以上的索引,其他的select_type这里只会出现一个。
     
    7)、key_len
    用于处理查询的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去。留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意,mysql的ICP特性使用到的索引不会计入其中。另外,key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。
     
    8)、ref
    如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
     
    9)、rows
    这里是执行计划中估算的扫描行数,不是精确值
     
    10)、extra
    这个列可以显示的信息非常多,有几十种,常用的有
    A:distinct:在select部分使用了distinc关键字
    B:no tables used:不带from字句的查询或者From dual查询
    C:使用not in()形式子查询或not exists运算符的连接查询,这种叫做反连接。即,一般连接查询是先查询内表,再查询外表,反连接就是先查询外表,再查询内表。
    D:using filesort:排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中
    E:using index:查询时不需要回表查询,直接通过索引就可以获取查询的数据。
    F:using join buffer(block nested loop),using join buffer(batched key accss):5.6.x之后的版本优化关联查询的BNL,BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
    G:using sort_union,using_union,using intersect,using sort_intersection:
    using intersect:表示使用and的各个索引的条件时,该信息表示是从处理结果获取交集
    using union:表示使用or连接各个使用索引的条件时,该信息表示从处理结果获取并集
    using sort_union和using sort_intersection:与前面两个对应的类似,只是他们是出现在用and和or查询信息量大时,先查询主键,然后进行排序合并后,才能读取记录并返回。
    H:using temporary:表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表,执行计划中看不出来,需要查看status变量,used_tmp_table,used_tmp_disk_table才能看出来。
    I:using where:表示存储引擎返回的记录并不是所有的都满足查询条件,需要在server层进行过滤。查询条件中分为限制条件和检查条件,5.6之前,存储引擎只能根据限制条件扫描数据并返回,然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性,可以把检查条件也下推到存储引擎层,不符合检查条件和限制条件的数据,直接不读取,这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition
    J:firstmatch(tb_name):5.6.x开始引入的优化子查询的新特性之一,常见于where字句含有in()类型的子查询。如果内表的数据量比较大,就可能出现这个
    K:loosescan(m..n):5.6.x之后引入的优化子查询的新特性之一,在in()类型的子查询中,子查询返回的可能有重复记录时,就可能出现这个
     
    除了这些之外,还有很多查询数据字典库,执行计划过程中就发现不可能存在结果的一些提示信息
     
    11)、filtered
    使用explain extended时会出现这个列,5.7之后的版本默认就有这个字段,不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。

    原文地址:https://www.cnblogs.com/acm-bingzi/p/mysqlExplain.html

    展开全文
  • mysql 创建索引和删除索引

    千次阅读 2018-11-23 11:47:15
    mysql 创建索引和删除索引   索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。 (1)使用ALTER TABLE语句...

    mysql 创建索引和删除索引

     

    索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。

    (1)使用ALTER TABLE语句创建索引。
    语法如下:
    alter table table_name add index index_name (column_list) ;
    alter table table_name add unique (column_list) ;
    alter table table_name add primary key (column_list) ;

    其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式,table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引。
    创建索引的示例如下:
    mysql> alter table table_test add index index_test1(name) ;
    Query OK, 2 rows affected (0.08 sec)
    (2)使用CREATE INDEX语句对表增加索引。
    能够增加普通索引和UNIQUE索引两种。其格式如下:
    create index index_name on table_name (column_list) ;
    create unique index index_name on table_name (column_list) ;

    创建索引的示例如下:

    mysql>create index index_test2 on table_test(age);

    Query OK, 2 rows affected (0.08 sec)

    说明:table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。
    (3)删除索引。
    删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理,其格式如下:
    drop index index_name on table_name ;
    alter table table_name drop index index_name ;
    alter table table_name drop primary key ;

    其中,在前面的两条语句中,都删除了table_name中的索引index_name。而在最后一条语句中,只在删除PRIMARY KEY索引中使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
    如果从表中删除某列,则索引会受影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。
    删除索引的操作,如下面的代码:
    mysql> drop index name on table_test ;
    Query OK, 2 rows affected (0.08 sec)

    展开全文
  • MySQL 创建索引和索引效率验证

    千次阅读 2019-10-27 17:41:44
    MySQL 创建索引和索引效率验证 对于一张存储数据的表来说,通常来说数据量会很大。 项目中总会有一些表数据量非常大,并且在使用过程中,需要频繁地到这些表中查询数据。 数据量大的时候,查询速度会明显变慢,这...
  • MySQL创建索引

    万次阅读 2013-03-20 10:55:22
    创建索引的语法是: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) index_col_name: col_name [(length)] [ASC | DESC] 对于CHAR和VARCHAR列...
  • MySQL 创建索引(Create Index)的方法和语法结构及例子
  • 1、创建索引 索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。 以下命令语句分别展示了如何创建主键索引(PRIMARY KEY)、 唯一索引(UNIQUE)、 全文索引...
  • mysql创建索引的原则

    2019-06-11 11:06:57
    mysql创建索引的原则 在mysql中使用索引的原则有以下几点: 1、 对于查询频率高的字段创建索引; 2、 对排序、分组、联合查询频率高的字段创建索引; 3、 索引的数目不宜太多 原因:a、每创建一个索引...
  • MySQL创建索引 由于数据量的不断增大,在使用MySql查询时速度愈来愈慢,这时候有必要创建合适的索引来优化提升查询的速度。
  • mysql 创建索引语句

    千次阅读 2020-11-19 19:00:03
    一、创建索引 1.PRIMARYKEY(主键索引) mysql > ALTERTABLE`table_name`ADDPRIMARYKEY (`column`) 2.UNIQUE(唯一索引) mysql > ALTERTABLE`table_name`ADDUNIQUE (`column` ) 3.INDEX(普通索引) mysql &...
  • MySQL创建索引/判断索引是否生效

    千次阅读 2019-08-06 12:36:51
    MySQL创建索引: 现有数据表 jingjia_info,共658行数据: SELECT * FROM jingjia_info; 查询是否有索引:EXPLAIN + 查询语句 EXPLAIN SELECT * FROM jingjia_info; EXPLAIN列的解释: table:显示这一行的...
  • Mysql创建索引注意事项

    千次阅读 2020-03-08 08:40:39
    介绍了mysql索引相关的基础知识及索引自身搜索的规则和锁机制,并列了一些创建索引的一些优化策略或小tips...
  • mysql创建索引方式

    千次阅读 2019-06-29 19:11:48
    在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。 1.ALTER TABLE ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。 ALTER TABLE table_name ADD ...
  • Oracle/MySQL创建索引

    2020-05-19 12:42:09
    创建索引 create index 索引名 on 表名(列名); 删除索引 drop index 索引名; 创建表的主键Primary Key 或者unique 约束时,Oracle会同时在约束所在列上创建同名索引。 MySQL -添加primary key(主键索引) ...
  • MySql 创建索引原则

    千次阅读 2015-12-27 15:41:12
    为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。本小节将向读者介绍一些索引的设计原则。   1.选择唯一性索引   唯一性索引的值是唯一的,可以更快速的通过该索...
  • Mysql创建索引、增加索引和删除索引

    千次阅读 2019-01-20 15:12:08
    MySQL中创建表的时候,可以直接创建索引。基本的语法格式如下: CREATE TABLE 表名(字段名 数据类型 [完整性约束条件], [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名](字段名1 [(长度)] [ASC | DESC]) ); ...
  • 在开发中使用数据库mysql少不了创建索引、重建索引、查询索引、删除索引的操作, 本篇文章主要是对MySql索引操作方法做了一下总结。以下所列示例中中 `table_name` 表示数据表名,`index_name` 表示索引名,column ...
  • MySql创建索引时百支持ASC或DESC排序。 下面举例 创建表时同时度创建索知引降序排序(sname 字段上普通道索引降序) create table tbl1 ( id int unique, sname varchar(50), index tbl1_index_sname(sname desc) ); ...
  • mysql创建索引技巧

    2014-11-06 18:43:17
    1,创建MySql索引  对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加  索引的话,那么查找任何哪怕只是一...
  • SQL CREATE INDEX (mysql 创建索引)语句 ** 1.创建普通索引 SQL CREATE INDEX 语法 在表上创建一个简单的索引。允许使用重复的值: CREATE INDEX index_name ON table_name (column_name) 注释:“column_name” ...
  • 在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。 1.ALTER TABLE ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。 ALTER TABLE table_name ADD INDEX ...
  • MYSQL创建索引全过程

    2014-03-28 11:46:37
    代码复制粘贴 改下里面的参数 就可以用 而且参数 也有说明 简单易懂
  • mysql 创建索引(联合索引)

    千次阅读 2014-08-19 17:47:59
    1、创建索引 alter table ssp.ssp_web_report add unique index(date_id,website_id,slot_id); alter ignore table ssp.ssp_web_report add unique index(date_id,website_id,slot_id);
  • MySQL创建索引的方法

    千次阅读 2019-08-10 00:12:02
    创建索引 如果是CHAR,VARCHAR类型,length可以小于字段实际长度 如果是BLOB和TEXT类型,必须指定 length。所以大多数情况下不需要指定length 用ALTER TABLE的最多 一、CREATE INDEX方法 CREATE INDEX <...
  • mysql创建索引三种方式

    千次阅读 2020-12-21 14:56:53
    1. 新建表中添加索引 ① 普通索引 create table t_dept( no int not null primary key, name varchar(20) null, sex varchar(2) null, info varchar(20) null, index index_no(no) ) ② 唯一索引 create ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 265,496
精华内容 106,198
关键字:

mysql建索引

mysql 订阅