kingbase 部分索引 mysql部分索引

索引概述：

• 索引是帮助MySQL高效获取数据的数据结构（有序）

索引结构：

• MySQL的索引是在存储引擎层实现的，不同的存储引擎由不同的结构，主要包含这几种

• B+tree 索引

• 最常见的索引类型，大部分引擎都支持B+树索引

• Hash 索引

• 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询

• R-tree （空间索引）

• 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常比较少

• Full-text （全文索引）

• 是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES

• 索引
• B+tree：支持、支持、支持
• Hash：不支持、不支持、支持
• R-tree：不支持、支持、不支持
• Full-text：5.6版本后支持、支持、不支持
• 二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢
• 红黑树：大数据量情况下，层级较深，检索速度慢
• B+Tree相比于B-Tree区别

• 所有的数据都会出现在叶子节点
• 叶子节点形成一个单向链表

• 在MySQL里进行了优化，形成了带有顺序指针的B+Tree

• Hash

• 哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，任何储存到hash表中，如果两个（或多个）键值，映射到一个相同的槽位上，就形成了hash冲突/碰撞，可以通过链表来解决
• 特点：

• Hash索引只能用于对等比较（=，in），不支持范围查询
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索就可以了，效率通常高于B+Tree索引

补充：数据结构可视化网站Data Structure Visualization，可以去插入数据玩玩（需右键翻译）

 

 索引分类：

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

• 聚焦索引（Clustered Index）

• 将数据存储与所有放到了一块，索引结构的叶子节点保存了行数据

• 必须有，而且只有一个

• 二级索引（Secondary Index）

• 将数据与所有分开存储，所有结构的叶子节点关联的是对应的主键

• 可以存在多个

• 聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在索引，将第一个唯一索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

索引语法

• 创建索引

• create [unique|fulltext] index 索引名 on 表名(索引列...);

• 查看索引

• show index from 表名;

• 删除索引

• drop index 索引名 on 表名;

SQL性能分析

• SQL执行频率

• MySQL客服端连接成功后，通过show [session|global] status 命令可以提供服务器状态信息。
• 通过该指令可以查看当前数据库INSERT、UPDATE、DELETE、SELECT访问频次

• show global status like 'Com_______';

• 模糊匹配，七个_

• 慢查询日志

• 慢查询日志记录了索引执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
• MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置信息：

• 开启MySQL慢查询日志开关

• slow_query_log=1;

• 设置慢查询日志的时间为2秒

• long_query_time=2;

• 配置完毕后，通过指令重启MySQL服务器进行测试，查看慢查询日志文件中记录的信息/var/lib/mysql/localhost-slow.log
• 查看慢查询日志是否开启

• show variables like 'slow_query_log';

• profile详情

• show profiles 能够在做SQL优化时帮助我们了解时间的耗费在哪里。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

• select @@have_profiling

• 或者show variables like 'have_pro%';

• 默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

• set profiling=1;

• 执行一系列SQL的操作，然后通过这些指令查看指令的执行耗时：

• 查看每一条SQL的耗时基本情况

• show profiles;

• 查看指定query_id的SQL语句各个阶段的耗时情况

• show profile for query query_id;

• 查看指定query_id的SQL语句CPU的使用情况

• show profile cpu for query query_id;

• explain执行计划

• explain或者desc命令获取MySQL如何执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序
• 语法：

• 直接在select语句之前加上关键字explain/desc

• explain select 字段列表 from 表名 where 条件;

• explain执行计划各字段含义：

• id

• select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）

• select_type

• 表示select的类型，常见的取值有SIMPLE（simple 简单表，即不使用表连接或子查询)、PRIMARY（primary 主查询，即外层的查询）、UNION（union中的第二个或者后面的查询语句）、SUBQUERY（select/where之后包含了子查询）等

• type

• 表示连接类型，性能由好到差的连接类型为NULL、system、const、en_ref、ref、range、index、all

• possible_key

• 显示可能应用在这张表上的索引，一个或多个

• Key

• 实际使用的索引，如果为NULL，则没有使用索引

• Key_len

• 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确度的前提下，长度越短越好

• rows

• MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的

• filerted

• 表示返回的行数占需要读取行数的百分比，filtered的值越大越好

索引使用

• 最左前缀法则

• 如果索引了多列（联合索引），要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并不能跳过索引中的列，如果跳过某一列，索引将部分失效（后面的字段索引失效）

• 范围查询

• 联合索引中，如果出现范围查询(>,<)，范围查询右侧的列索引失效

• 但用>=,<=时不会

• 索引列计算

• 不要在索引列上进行运算操作，索引将失效

• 如explain select * from tu_user where substring(phone,10,2)='15';

• 字符串不加引号

• 字符串类型字段使用时，不加引号，索引将失效

• 模糊查询

• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

• or连接的条件

• 用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到

• 数据发布影响

• 如果MySQL评估使用索引比全表更慢，则不使用索引

• SQL提示

• SQL提示，是优化数据库的一个重要手段，在SQL语句中加入一些人为的提示来达到优化操作的目的
• use index

• explain select & from tb_user use index(idx_user_pro) where profession='软件工程';

• 只是建议，MySQL还会评估后选择是否使用

• ignore index

• explain select & from tb_user ignore index(idx_user_pro) where profession='软件工程';

• force index

• explain select & from tb_user force index(idx_user_pro) where profession='软件工程';

• 覆盖索引

• 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到，不用回表查询，减少select
• using index condition：查找使用了索引，但是需要回表查询数据
• using where;using index：查询使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

• 前缀索引

• 当字符类型为字符串时，有时候需要索引很长的字符串，会使索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率
• 语法：

• create index idx_xxxx on table_name(column(n));

• 前缀长度

• 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，索引选择性最好，性能最好
• select count(distinct email)/count(*) from tb_user;
• select count(distinct substring(email,1,5))/count(*) from tb_user;

索引设计原则原文更加详细

• 1．选择唯一性索引
• 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
• 2．为经常需要排序、分组和联合操作的字段建立索引
• 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
• 3．为常作为查询条件的字段建立索引
• 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
• 4．限制索引的数目
• 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
• 5．尽量使用数据量少的索引
• 如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
• 6．尽量使用前缀来索引
• 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
• 7．删除不再使用或者很少使用的索引
• 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
• 8 . 最左前缀匹配原则，非常重要的原则。
• mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
• 9 .=和in可以乱序。
• 比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
• 10 . 尽量选择区分度高的列作为索引。
• 区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录
• 11 .索引列不能参与计算，保持列“干净”。
• 比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
• 12 .尽量的扩展索引，不要新建索引。
• 比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
• 13、当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率
• 注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

完整思维导图