您的位置:新葡亰496net > 网络数据库 > 目录及查询优化总计,MySQL索引及查询优化总括

目录及查询优化总计,MySQL索引及查询优化总括

发布时间:2019-06-16 03:54编辑:网络数据库浏览(138)

    1、B 树基本概念

    MySQL 索引及查询优化总结

    文章《MySQL查询分析》讲述了使用MySQL慢查询和explain命令来定位mysql性能瓶颈的方法,定位出性能瓶颈的sql语句后,则需要对低效的sql语句进行优化。本文主要讨论MySQL索引原理及常用的sql查询优化。

    本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方

    本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方

    欢迎大家关注腾讯云技术社区-博客园官方主页,我们将持续在博客园为大家推荐技术精品文章哦~

      B 树的语言定义比较复杂,简单的说是为磁盘存取设计的平衡二叉树

    一个简单的对比测试

    前面的案例中,c2c_zwdb.t_file_count表只有一个自增id,FFileName字段未加索引的sql执行情况如下:

    新葡亰496net 1

    image

    在上图中,type=all,key=null,rows=33777。该sql未使用索引,是一个效率非常低的全表扫描。如果加上联合查询和其他一些约束条件,数据库会疯狂的消耗内存,并且会影响前端程序的执行。

    这时给FFileName字段添加一个索引:

    alter table c2c_zwdb.t_file_count add index index_title(FFileName);

    再次执行上述查询语句,其对比很明显:

    新葡亰496net 2

    image

    在该图中,type=ref,key=索引名(index_title),rows=1。该sql使用了索引index_title,且是一个常数扫描,根据索引只扫描了一行。

    比起未加索引的情况,加了索引后,查询效率对比非常明显。

    首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname='css',如果要拿这条数据的话需要些的sql是 SELECT * FROM award WHERE nickname = 'css'

    首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname='css',如果要拿这条数据的话需要些的sql是 SELECT * FROM award WHERE nickname = 'css'

    作者:谢庆玲

    新葡亰496net 3

    MySQL索引

    通过上面的对比测试可以看出,索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降。如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找。

    下面介绍几种常见的MySQL索引类型。

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

    一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在nickname上建立索引,那么mysql只需要扫描一行数据及为我们找到这条nickname='css'的数据,是不是感觉性能提升了好多咧....

    一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在nickname上建立索引,那么mysql只需要扫描一行数据及为我们找到这条nickname='css'的数据,是不是感觉性能提升了好多咧....

     文章《MySQL查询分析》讲述了使用MySQL慢查询和explain命令来定位mysql性能瓶颈的方法,定位出性能瓶颈的sql语句后,则需要对低效的sql语句进行优化。本文主要讨论MySQL索引原理及常用的sql查询优化。

      网上经典图,黄色p1 p2 p3代表指针,蓝色的代表磁盘,里面包含数据项,第一层17,35,p1就代表小于17的,p2就代表17-35之间的,p3就代表大于35的,可是需要注意的是,第三层才是真实的数据,17、35都不是真实数据,只是用来划分数据的!

    1、MySQL索引类型

    (1) 主键索引 PRIMARY KEY

    它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。

    新葡亰496net 4

    image

    当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

    (2) 唯一索引 UNIQUE

    唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。可以在创建表的时候指定,也可以修改表结构,如:

    ALTER TABLE table_name ADD UNIQUE (column)

    (3) 普通索引 INDEX

    这是最基本的索引,它没有任何限制。可以在创建表的时候指定,也可以修改表结构,如:

    ALTER TABLE table_name ADD INDEX index_name (column)

    (4) 组合索引 INDEX

    组合索引,即一个索引包含多个列。可以在创建表的时候指定,也可以修改表结构,如:

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

    (5) 全文索引 FULLTEXT

    全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

    可以在创建表的时候指定,也可以修改表结构,如:

    ALTER TABLE table_name ADD FULLTEXT (column)

    mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.

    mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.

    一个简单的对比测试

    前面的案例中,c2c_zwdb.t_file_count表只有一个自增id,FFileName字段未加索引的sql执行情况如下:

    新葡亰496net 5

    在上图中,type=all,key=null,rows=33777。该sql未使用索引,是一个效率非常低的全表扫描。如果加上联合查询和其他一些约束条件,数据库会疯狂的消耗内存,并且会影响前端程序的执行。

    这时给FFileName字段添加一个索引:

    alter table c2c_zwdb.t_file_count add index index_title(FFileName);
    

    再次执行上述查询语句,其对比很明显:

    新葡亰496net 6

    在该图中,type=ref,key=索引名(index_title),rows=1。该sql使用了索引index_title,且是一个常数扫描,根据索引只扫描了一行。

    比起未加索引的情况,加了索引后,查询效率对比非常明显。

    2、为什么使用B 树

    2、索引结构及原理

    mysql中普遍使用B Tree做索引,但在实现上又根据聚簇索引和非聚簇索引而不同,本文暂不讨论这点。

    b 树介绍

    下面这张b 树的图片在很多地方可以看到,之所以在这里也选取这张,是因为觉得这张图片可以很好的诠释索引的查找过程。

    新葡亰496net 7

    image

    如上图,是一颗b 树。浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。

    真实的数据存在于叶子节点,即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

    查找过程

    在上图中,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b 树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

    性质

    (1) 索引字段要尽量的小。

    通过上面b 树的查找过程,或者通过真实的数据存在于叶子节点这个事实可知,IO次数取决于b 数的高度h。

    假设当前数据表的数据量为N,每个磁盘块的数据项的数量是m,则树高h=㏒(m 1)N,当数据量N一定的情况下,m越大,h越小;

    而m = 磁盘块的大小/数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的;如果数据项占的空间越小,数据项的数量m越多,树的高度h越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。

    (2) 索引的最左匹配特性。

    当b 树的数据项是复合的数据结构,比如(name,age,sex)的时候,b 数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b 树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b 树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b 树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

    建索引的几大原则

    (1) 最左前缀匹配原则

    对于多列索引,总是从索引的最前面字段开始,接着往后,中间不能跳过。比如创建了多列索引(name,age,sex),会先匹配name字段,再匹配age字段,再匹配sex字段的,中间不能跳过。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

    一般,在创建多列索引时,where子句中使用最频繁的一列放在最左边。

    看一个补符合最左前缀匹配原则和符合该原则的对比例子。

    实例:表c2c_db.t_credit_detail建有索引(Flistid,Fbank_listid)

    新葡亰496net 8

    image

    不符合最左前缀匹配原则的sql语句:

    select * from t_credit_detail where Fbank_listid='201108010000199'G

    该sql直接用了第二个索引字段Fbank_listid,跳过了第一个索引字段Flistid,不符合最左前缀匹配原则。用explain命令查看sql语句的执行计划,如下图:

    新葡亰496net 9

    image

    从上图可以看出,该sql未使用索引,是一个低效的全表扫描。

    符合最左前缀匹配原则的sql语句:

    select * from t_credit_detail where Flistid='2000000608201108010831508721' and Fbank_listid='201108010000199'G

    该sql先使用了索引的第一个字段Flistid,再使用索引的第二个字段Fbank_listid,中间没有跳过,符合最左前缀匹配原则。用explain命令查看sql语句的执行计划,如下图:

    新葡亰496net 10

    image

    从上图可以看出,该sql使用了索引,仅扫描了一行。

    对比可知,符合最左前缀匹配原则的sql语句比不符合该原则的sql语句效率有极大提高,从全表扫描上升到了常数扫描。

    (2) 尽量选择区分度高的列作为索引。
    比如,我们会选择学号做索引,而不会选择性别来做索引。

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

    (4) 索引列不能参与计算,保持列“干净”
    比如:Flistid 1>‘2000000608201108010831508721‘。原因很简单,假如索引列参与计算的话,那每次检索时,都会先将索引计算一次,再做比较,显然成本太大。

    (5) 尽量的扩展索引,不要新建索引。
    比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

    索引的不足
    虽然索引可以提高查询效率,但索引也有自己的不足之处。

    索引的额外开销:
    (1) 空间:索引需要占用空间;
    (2) 时间:查询索引需要时间;
    (3) 维护:索引须要维护(数据变更时);

    不建议使用索引的情况:
    (1) 数据量很小的表
    (2) 空间紧张

    单列索引:一个索引只包含一个列,一个表可以有多个单列索引.

    单列索引:一个索引只包含一个列,一个表可以有多个单列索引.

    MySQL索引

    通过上面的对比测试可以看出,索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降。如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找。

    下面介绍几种常见的MySQL索引类型。

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

      B 树有什么好处我们非要使用它呢?那就先要来看看mysql的索引

    常用优化总结

    优化语句很多,需要注意的也很多,针对平时的情况总结一下几点:

    组合索引:一个组合索引包含两个或两个以上的列,

    组合索引:一个组合索引包含两个或两个以上的列,

    1、MySQL索引类型

    (1) 主键索引 PRIMARY KEY

    它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。

    新葡亰496net 11

    当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

    (2) 唯一索引 UNIQUE

    唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。可以在创建表的时候指定,也可以修改表结构,如:

    ALTER TABLE table_name ADD UNIQUE (column)
    

    (3) 普通索引 INDEX

    这是最基本的索引,它没有任何限制。可以在创建表的时候指定,也可以修改表结构,如:

    ALTER TABLE table_name ADD INDEX index_name (column)
    

    (4) 组合索引 INDEX

    组合索引,即一个索引包含多个列。可以在创建表的时候指定,也可以修改表结构,如:

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

    (5) 全文索引 FULLTEXT

    全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

    可以在创建表的时候指定,也可以修改表结构,如:

    ALTER TABLE table_name ADD FULLTEXT (column)
    

     

    1、有索引但未被用到的情况(不建议)

    (1) Like的参数以通配符开头时

    尽量避免Like的参数以通配符开头,否则数据库引擎会放弃使用索引而进行全表扫描。

    以通配符开头的sql语句,例如:select * from t_credit_detail where Flistid like '%0'G

    新葡亰496net 12

    image

    这是全表扫描,没有使用到索引,不建议使用。

    不以通配符开头的sql语句,例如:select * from t_credit_detail where Flistid like '2%'G

    新葡亰496net 13

    image

    很明显,这使用到了索引,是有范围的查找了,比以通配符开头的sql语句效率提高不少。

    (2) where条件不符合最左前缀原则时

    例子已在最左前缀匹配原则的内容中有举例。

    (3) 使用!= 或 <> 操作符时

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

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

    新葡亰496net 14

    image

    (4) 索引列参与计算

    应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。

    select * from t_credit_detail where Flistid 1 > '2000000608201108010831508722'G

    新葡亰496net 15

    image

    (5) 对字段进行null值判断

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

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

    (6) 使用or来连接条件

    应尽量避免在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';

    新葡亰496net 16

    image

    本文使用的案例的表

    本文使用的案例的表

    2、索引结构及原理

    mysql中普遍使用B Tree做索引,但在实现上又根据聚簇索引和非聚簇索引而不同,本文暂不讨论这点。

    b 树介绍

    下面这张b 树的图片在很多地方可以看到,之所以在这里也选取这张,是因为觉得这张图片可以很好的诠释索引的查找过程。

    新葡亰496net 17

    如上图,是一颗b 树。浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。

    真实的数据存在于叶子节点,即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

    查找过程

    在上图中,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b 树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

    性质

    (1) 索引字段要尽量的小。

    通过上面b 树的查找过程,或者通过真实的数据存在于叶子节点这个事实可知,IO次数取决于b 数的高度h。

    假设当前数据表的数据量为N,每个磁盘块的数据项的数量是m,则树高h=㏒(m 1)N,当数据量N一定的情况下,m越大,h越小;

    而m = 磁盘块的大小/数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的;如果数据项占的空间越小,数据项的数量m越多,树的高度h越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。

    (2) 索引的最左匹配特性。

    当b 树的数据项是复合的数据结构,比如(name,age,sex)的时候,b 数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b 树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b 树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b 树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

    建索引的几大原则

    (1) 最左前缀匹配原则

    对于多列索引,总是从索引的最前面字段开始,接着往后,中间不能跳过。比如创建了多列索引(name,age,sex),会先匹配name字段,再匹配age字段,再匹配sex字段的,中间不能跳过。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

    一般,在创建多列索引时,where子句中使用最频繁的一列放在最左边。

    看一个补符合最左前缀匹配原则和符合该原则的对比例子。

    实例:表c2c_db.t_credit_detail建有索引(Flistid,Fbank_listid)

    新葡亰496net 18

    不符合最左前缀匹配原则的sql语句:

    select * from t_credit_detail where Fbank_listid='201108010000199'G
    

    该sql直接用了第二个索引字段Fbank_listid,跳过了第一个索引字段Flistid,不符合最左前缀匹配原则。用explain命令查看sql语句的执行计划,如下图:

    新葡亰496net 19

    从上图可以看出,该sql未使用索引,是一个低效的全表扫描。

    符合最左前缀匹配原则的sql语句:

    select * from t_credit_detail where Flistid='2000000608201108010831508721' and Fbank_listid='201108010000199'G
    

    该sql先使用了索引的第一个字段Flistid,再使用索引的第二个字段Fbank_listid,中间没有跳过,符合最左前缀匹配原则。用explain命令查看sql语句的执行计划,如下图:

    新葡亰496net 20

    从上图可以看出,该sql使用了索引,仅扫描了一行。

    对比可知,符合最左前缀匹配原则的sql语句比不符合该原则的sql语句效率有极大提高,从全表扫描上升到了常数扫描。

    (2) 尽量选择区分度高的列作为索引。

    比如,我们会选择学号做索引,而不会选择性别来做索引。

    (3) =和in可以乱序

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

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

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

    (5) 尽量的扩展索引,不要新建索引。

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

    索引的不足

    虽然索引可以提高查询效率,但索引也有自己的不足之处。

    索引的额外开销:

    (1) 空间:索引需要占用空间;

    (2) 时间:查询索引需要时间;

    (3) 维护:索引须要维护(数据变更时);

    不建议使用索引的情况:

    (1) 数据量很小的表

    (2) 空间紧张

      2.1mysql索引

    2、避免select *

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

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

    新葡亰496net 21

    CREATE TABLE `award` (
       `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
       `aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
       `nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
       `is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
       `award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
       `account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
       `password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
       `message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
       `created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
       `updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';
    

    常用优化总结

    优化语句很多,需要注意的也很多,针对平时的情况总结一下几点:

        试想一下在mysql中有200万条数据,在没有建立索引的情况下,会全部进行扫描读取,这个时间消耗是非常恐怖的,而对于大型一点的网站来说,达到这个数据量很容易,不可能这样去设计

    3、order by 语句优化

    任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

    方法:
    1.重写order by语句以使用索引;
    2.为所使用的列建立另外一个索引
    3.绝对避免在order by子句中使用表达式。

    CREATE TABLE `award` (
       `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
       `aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
       `nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
       `is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
       `award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
       `account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
       `password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
       `message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
       `created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
       `updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';
    

    (一)索引的创建

    1、有索引但未被用到的情况(不建议)

    (1) Like的参数以通配符开头时

    尽量避免Like的参数以通配符开头,否则数据库引擎会放弃使用索引而进行全表扫描。

    以通配符开头的sql语句,例如:select * from t_credit_detail where Flistid like '%0'G

    新葡亰496net 22

    这是全表扫描,没有使用到索引,不建议使用。

    不以通配符开头的sql语句,例如:select * from t_credit_detail where Flistid like '2%'G

    新葡亰496net 23

    很明显,这使用到了索引,是有范围的查找了,比以通配符开头的sql语句效率提高不少。

    (2) where条件不符合最左前缀原则时

    例子已在最左前缀匹配原则的内容中有举例。

    (3) 使用!= 或 <> 操作符时

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

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

    新葡亰496net 24

    (4) 索引列参与计算

    应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。

    select * from t_credit_detail where Flistid  1 > '2000000608201108010831508722'G
    

    新葡亰496net 25

    (5) 对字段进行null值判断

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

    select * from t_credit_detail where Flistid is null ;
    

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

    select * from t_credit_detail where Flistid =0;
    

    (6) 使用or来连接条件

    应尽量避免在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';
    

    新葡亰496net 26

        在我们创建数据库表的时候,大家都知道一个东西叫做主键,一般来讲数据库会自动在主键上创建索引,这叫做主键索引,来看看索引的分类吧

    4、GROUP BY语句优化

    提高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

    新葡亰496net 27

    1.单列索引

    2、避免select *

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

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

        a.主键索引:int优于varchar

    5、用 exists 代替 in

    很多时候用 exists 代替 in 是一个好的选择: select num from a where num in(select num from b) 用下面的语句替换: select num from a where exists(select 1 from b where num=a.num)

    (一)索引的创建

    1-1)    普通索引,这个是最基本的索引,

    3、order by 语句优化

    任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

    方法:1.重写order by语句以使用索引;

      2.为所使用的列建立另外一个索引
    
      3.绝对避免在order by子句中使用表达式。
    

        b.普通索引(INDEX):最基本的索引,没有限制,加速查找

    6、使用 varchar/nvarchar 代替 char/nchar

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

    1.单列索引

    其sql格式是 CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))

    4、GROUP BY语句优化

    提高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
    

        c.唯一索引(UNUQUE):听名字就知道,要求所有类的值是唯一的,但是允许有空值

    7、能用DISTINCT的就不用GROUP BY

    SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

    可改为:

    SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

    1-1)    普通索引,这个是最基本的索引,

    第一种方式 :

    5、用 exists 代替 in

    很多时候用 exists 代替 in 是一个好的选择:

    select num from a where num in(select num from b)
    

    用下面的语句替换:

    select num from a where exists(select 1 from b where num=a.num)
    

        d.组合索引:

    8、能用UNION ALL就不要用UNION

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

    其sql格式是 CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))

      CREATE INDEX account_Index ON `award`(`account`);
    

    6、使用 varchar/nvarchar 代替 char/nchar

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

    1 CREATE INDEX name_age_address_Index ON `student`(`name`, `age`, `address`);
    

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

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

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

    第一种方式 :

    第二种方式: 

    7、能用DISTINCT的就不用GROUP BY

    SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
    

    可改为:

    SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
    

        在这里实际上包含三个索引,说到组合索引,一定要讲最左前缀原则

      CREATE INDEX account_Index ON `award`(`account`);
    
    ALTER TABLE award ADD INDEX account_Index(`account`)
    

    8、能用UNION ALL就不要用UNION

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

     

    第二种方式: 

     

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

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

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

     

    欢迎加入QQ群:374933367,与腾云阁原创作者们一起交流,更有机会参与技术大咖的在线分享!


    ALTER TABLE award ADD INDEX account_Index(`account`)
    

     

    相关阅读

    5 步优化 MongoDB 以及其它数据库
    埋在MYSQL数据库应用中的17个关键问题!
    当谈 SQL 优化时谈些什么?


    此文已由作者授权腾讯云技术社区发布,转载请注明文章出处
    原文链接:
    获取更多腾讯海量技术实践干货,欢迎大家前往腾讯云技术社区

        最左前缀原则:

     

     

          我们现在创建了索引x,y,z,Index:(x,y,z),只会走x,xy,xyz的查询,例如:

     

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

    1 select * from table where x='1'
    2 select * from table where x='1' and b='1'
    3 select * from table where x='1' and b='1' and c='1'
    

     

    1-2)    唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,

          如果是x,z,就只会走x,注意一种特殊情况,select * from table where x='1' and y>'1' and z='1',这里只会走xy,因为在经历xy的筛选后,z不能保证是有序的,可索引是有序的,因此不会走z

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

    其sql格式是 CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length)); 或者 ALTER TABLE TableName ADD UNIQUE (column_list)  


    1-2)    唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,

    CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);
    

     

    其sql格式是 CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length)); 或者 ALTER TABLE TableName ADD UNIQUE (column_list)  

    1-3)    主键索引,不允许有空值,(在B TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

        e.全文索引(FULLTEXT):用于搜索内容很长的文章之类的很好用,如果创建普通的索引,在遇到 like='%xxx%'这种情况索引会失效

    CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);
    

    主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的

    1 ALTER TABLE tablename ADD FULLTEXT(col1, col2)
    2 SLECT * FROM tablename WHERE MATCH(col1, col2) AGAINST(‘x′, ‘y′, ‘z′)
    

    1-3)    主键索引,不允许有空值,(在B TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

     

        这样就可以将col1和col2里面包含x,y,z的记录全部取出来了

    主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的

    2.组合索引

        

     

    一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引是:包含多个字段但是只有索引名称

        索引的删除:DORP INDEX IndexName ON `TableName`

    2.组合索引

    其sql格式是 CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);

      

    一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引是:包含多个字段但是只有索引名称

     CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);
    

        索引的优缺点:

    其sql格式是 CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);

     

          1、在数据量特别庞大的时候,建立索引有助于我们提高查询效率

     CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);
    

    新葡亰496net 28

          2、在操作表的时候,维护索引会增加额外开销

     

    如果你建立了 组合索引(nickname_account_createdTime_Index) 那么他实际包含的是3个索引 (nickname) (nickname,account)(nickname,account,created_time)

          3、不泛滥使用索引,创建多了索引文件会膨胀很快

    新葡亰496net 29

    在使用查询的时候遵循mysql组合索引的"最左前缀",下面我们来分析一下 什么是最左前缀:及索引where时的条件要按照建立索引的时候字段的排序方式

     

    如果你建立了 组合索引(nickname_account_createdTime_Index) 那么他实际包含的是3个索引 (nickname) (nickname,account)(nickname,account,created_time)

    1、不按索引最左列开始查询(多列索引) 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where `c2` = `aaa` and `c3`=`sss` 不能使用索引

      2.2B 树的优点

    在使用查询的时候遵循mysql组合索引的"最左前缀",下面我们来分析一下 什么是最左前缀:及索引where时的条件要按照建立索引的时候字段的排序方式

    2、查询中某个列有范围查询,则其右边的所有列都无法使用查询(多列查询)

        了解上面的模型后,试想一下,200W条数据,假如没有建立索引,会全部进行扫描,B 树仅仅用三层结构可以表示上百万的数据,只需要三次I/O!这提升是真的巨大啊!

    1、不按索引最左列开始查询(多列索引) 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where `c2` = `aaa` and `c3`=`sss` 不能使用索引

    Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询

        因为B 树是平衡二叉树,在不断的增加数据的时候,为了保持平衡可能需要做大量的拆分操作,因此提供了旋转的功能,不知道旋转建议去补一下树的基础知识

    2、查询中某个列有范围查询,则其右边的所有列都无法使用查询(多列查询)

    3、不能跳过某个字段来进行查询,这样利用不到索引,比如我的sql 是 

        B 树插入动画(来自

    Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询

    explain select * from `award` where nickname > 'rSUQFzpkDz3R' and account = 'DYxJoqZq2rd7' and created_time = 1449567822; 那么这时候他使用不到其组合索引.

    新葡亰496net 30

    3、不能跳过某个字段来进行查询,这样利用不到索引,比如我的sql 是 

    因为我的索引是 (nickname, account, created_time),如果第一个字段出现 范围符号的查找,那么将不会用到索引,如果我是第二个或者第三个字段使用范围符号的查找,那么他会利用索引,利用的索引是(nickname),

    3、索引优化

    explain select * from `award` where nickname > 'rSUQFzpkDz3R' and account = 'DYxJoqZq2rd7' and created_time = 1449567822; 那么这时候他使用不到其组合索引.

    因为上面说了建立组合索引(nickname, account, created_time), 会出现三个索引

      1、最佳左前缀原则

    因为我的索引是 (nickname, account, created_time),如果第一个字段出现 范围符号的查找,那么将不会用到索引,如果我是第二个或者第三个字段使用范围符号的查找,那么他会利用索引,利用的索引是(nickname),

    新葡亰496net 31

      2、不要在索引的列上做操作

    因为上面说了建立组合索引(nickname, account, created_time), 会出现三个索引

     新葡亰496net 32

      3、like会使索引失效变成全表扫描

    新葡亰496net 33

    (3)全文索引

      4、字符串不加单引号会导致索引失败

     新葡亰496net 34

    文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.

      5、减少使用select *

    (3)全文索引

    如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是 where column lick '%xxxx%' 这样做会让索引失效

    新葡亰496net 35

    文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.

    .这个时候全文索引就祈祷了作用了

      参照这里,写的很好   

    如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是 where column lick '%xxxx%' 这样做会让索引失效

    ALTER TABLE tablename ADD FULLTEXT(column1, column2)
    

     

    目录及查询优化总计,MySQL索引及查询优化总括。.这个时候全文索引就祈祷了作用了

    有了全文索引,就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。

    总结:

    ALTER TABLE tablename ADD FULLTEXT(column1, column2)
    
    ELECT * FROM tablename
    WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)
    

      sql语句怎么用,没有规定必须怎么查,对于数据量小,有时候不需要新建立索引,根据一定的实际情况来考虑

    有了全文索引,就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。

    这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

        

    ELECT * FROM tablename
    WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)
    

     

     

    这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

    (二)索引的删除

     

    删除索引的mysql格式 :DORP INDEX IndexName ON `TableName`

    (二)索引的删除

     

    删除索引的mysql格式 :DORP INDEX IndexName ON `TableName`

    (三)使用索引的优点

     

    1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
    2.建立索引可以大大提高检索的数据,以及减少表的检索行数
    3.在表连接的连接条件 可以加速表与表直接的相连
    4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
    5.建立索引,在查询中使用索引 可以提高性能

    (三)使用索引的优点

     

    1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
    2.建立索引可以大大提高检索的数据,以及减少表的检索行数
    3.在表连接的连接条件 可以加速表与表直接的相连 
    4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
    5.建立索引,在查询中使用索引 可以提高性能

    (四)使用索引的缺点

     

    1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
    2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
    新葡亰496net,3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快)。

    (四)使用索引的缺点

    (五)使用索引需要注意的地方

    1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
    2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
    3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快)。

    在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的.
    一般来说,
    1.在经常需要搜索的列上,可以加快索引的速度
    2.主键列上可以确保列的唯一性
    3.在表与表的而连接条件上加上索引,可以加快连接查询的速度
    4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间,  (单独order by 用不了索引,索引考虑加where 或加limit)
    5.在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)

    (五)使用索引需要注意的地方

    6.like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick '

    本文由新葡亰496net发布于网络数据库,转载请注明出处:目录及查询优化总计,MySQL索引及查询优化总括

    关键词: