009 学习笔记--索引

1. 索引概述
2. 索引结构
3. 索引分类
4. 索引语法
5. SQL性能分析
6. 索引使用
7. 索引设计原则

索引概述：

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。

在数据库之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，

这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

无索引：效率慢，查询--全表扫描

有索引：高效(例如，二叉树)

索引优劣点：

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列需要占用资源空间
通过索引列对数据进行排序，降低数据的排序成本，降低CPU的消耗	索引大大提高了查询效率，同时也降低更新表的数据，例如，对表进行INSERT、UPDATE、DELETE时，效率降低。

 

 

 

索引结构：

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要分为：

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引)	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-txt(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene,Solr,ES

 

 

 

 

 

 索引分类：

 

注：二级索引，也被称为非聚集索引。

聚集索引选取规则：

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

索引语法：

 

-- 查询指定表的索引信息
show INDEX from tb_users;
-- 新增索引
create index id_user_name on tb_users(name);
-- 查询指定表的索引信息
show INDEX from tb_users;

-- 添加唯一索引
-- create UNION index idx_indexname on tb_users(name);

-- 删除索引
drop index id_user_name on tb_users;

 

 注：在创建联合索引时，需注意字段的顺序

SQL性能分析：

• SQL执行频率
  • Mysql客户端连接成功后，通过show [session|global]status命令可提供服务器状态信息。
  • 通过如下指令，可查看当前数据库的Insert、Update、Delete、Select的访问频次：show global status like 'Com_______'（下划线 7 个）

  • 

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

  • 

  • 慢查询日志状态查询——show VARIABLES like 'slow_query_log'; 如下所示：

  • 

  •  

    配置完之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log 

• profile详情
  • show profile(查看sql执行时长)能够在优化时帮助我们了解时间都耗费到哪里了。通过have_profiling参数，能够看到当前Mysql是否支持profile操作

  • -- 查看是否支持 PROFILE：select @@have_profiling; -- YES支持，如下图：

  • 

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

  • -- 0,表示关闭，1表示打开  select @@profiling;       设置方式：  set @@profiling =1;

  • 

  •  

       

  • 

      

• explain执行计划
  • explain或者desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序
  • 语法：直接在select语句之前添加关键字 explain或desc—— explan/desc select sql语句，如下所示：
    • EXPLAIN SELECT t.* from tb_users t;
    • desc SELECT t.* from tb_users t;

    • desc select id,NAME,age,gender,phone from tb_users t;

  • 

  • EXPLAIN select * from  tb_user_edu where userid in(SELECT userid FROM tb_users where id<4); 

  • 

  •  

     explain执行计划个字段含义解释说明：　　

    • id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序——id相同，执行顺序从上到下；id不同，值越大，越优先执行。
    • select_type：
      • 表示select的类型，常见的取值有slmple——简单表，即不使用表连接或子查询、primary——注查询，即外层的查询、
      • Union——union中的第二个或者后面的查询语句、subquery——select/where之后包含了子查询等
    • type：表示连接类型，性能由好到差的连接类型为——null、system、const、eq_ref、ref、range、index、all。
    • possible_key：显示可能应用在这张表上的索引、一个或多个
    • key：实际使用的索引，如果为null,则没有使用索引
    • key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确度的前提下，长度越短越好
    • rows：mysql任务必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不是精确的
    • filtered：表示返回结果的行数占需读取行数的百分比，filtered值越大越好
    • extra：额外的信息

索引的使用：

索引——用于提升查询效率，索引会影响，即降低insert、update、delete效率。

对表创建索引，就是构建一种数据结构(B+Tree),数据量越大，对表创建耗时越长。

1.  最左前缀法则
   • 如果索引多列--联合索引，要遵守最左前缀法则。
   • 最左前缀法则是指查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，索引将部分实现——后面的字段索引失效。
   • 即，查询时需包含查询最左侧列，否则索引失效
2. 范围查询
   • 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
   • eg：select * from tb_users where id>10 and age=18—— age =18索引失效
   • 注，在业务允许的情况下，联合查询，尽量使用 >= 或 <= 实现范围查询 —— 可规避索引失效
3. 索引列运算
   • 不要对索引列上进行运算操作，否则索引将失效
4. 字符串不加引号
   • 字符串类型字段使用时，不加引号，索引将失效
5. 模糊查询
   • 如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引将失效。
   • eg： name like '软件%' ——索引不失效；  name like '%软件'——失效；  name like '%软%'——失效
   • 注：只要头部添加 % 匹配，索引将失效
6. or连接的条件
   • 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
7. 数据分布影响
   • 如果mysql评估使用索引比全表扫描更慢，则不使用索引
8. SQL 提示
   • 是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。例如：
   • use index：EXPLAIN select * from tb_users use INDEX(idx_user_name) where name='张三';
   • ignore index：EXPLAIN select * from tb_users IGNORE INDEX(idx_user_name) where name='张三';
   • force index：EXPLAIN select * from tb_users FORCE INDEX(idx_user_name) where name='张三';
9. 覆盖索引
   • 尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select * ——用select 字段列表代替* (尽量避免回表查询)
   • 注：
   • using index condition：查找使用了索引，但是需要回表查询数据
   • using where;using index：查找使用了索引，但是需要的数据都在所有列中能找到，所以不需要回表查询数据
   • 回表查询：即查询的字段信息在索引中不存在，则另需要使用id列查询出行数据(对于单表，如果存在联合索引，查询列都是索引列，则会避免回表查询，否则会出现回表查询)
10. 前缀索引——降低索引体积，提高效率
    • 当字段类型为字符串(varchar\text等)时，有时需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，
    • 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
    • 语法：
    • create index idx_indexname on tablename(column(n))——n，表示字段内容，字符串的前 n 个字符串作为索引。
    • 前缀 n  长度：可根据索引的选择性决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性
    • 越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
11. 单例&联合索引
    • 单例索引：即一个索引只包含单个列
    • 联合索引：即一个索引包含多个列
    • 注：在业务场景中，如果存在多个查询条件，考虑针对于查询字典建立索引时，建立联合索引，而非单列索引。
    • 注：多条件联合查询时，mysql优化器会评估那个字段的索引效率更高，会选择该索引完成本次查询。
12. 索引设计原则
    • 针对数据量大，且查询比较频繁的表建立索引
    • 针对于长作为查询条件where、排序order by、分组group by操作的字段建立索引
    • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率就越高
    • 如果是字符串类型的字段，字段的长度较长，可针对于字段的特点，建立前缀索引
    • 尽量使用联合索引，减少单例索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
    • 要控制索引的数量，索引并不是越多越好，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
    • 如果索引列不能存NULL值，请在创建表时使用NOT NULL约束列。当优化器知道每列是否包含NULL值时，它可以更改地确定那个索引最有效地用于查询