-
索引概述：
-
索引是帮助MySQL高效获取数据的数据结构（有序）
-
-
索引结构：
-
MySQL的索引是在存储引擎层实现的，不同的存储引擎由不同的结构，主要包含这几种
-
B+tree 索引
-
最常见的索引类型，大部分引擎都支持B+树索引
-
-
Hash 索引
-
底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
-
-
R-tree （空间索引）
-
空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常比较少
-
-
Full-text （全文索引）
-
是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES
-
-
-
索引
存储引擎：InnoDB、MyISAM、Memory -
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;
-
-
-
-
索引设计原则（来源：mysql建立索引的几大原则（仅学习） - 知乎）原文更加详细
-
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、当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率
-
注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。
-
完整思维导图
503
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
