mysql 索引-【应用】

在对B树和B+树进行了一定的了解之后，终于耐住性子过来对Mysql索引进行一定的总结。本篇我们暂不提原因，仅仅通过SQL执行来对索引进行一定的总结。
索引到底是什么？其本质其实就是数据结构，能够帮助我们快速的获取数据库中的数据。

导入测试库：

测试数据库：mysql官方Employee数据；也可以在我的共享云盘中下载：链接:https://pan.baidu.com/s/1b-0EtvTxbTQPRNK7HkaQag 密码:64oh
下载完成之后，操作命令，导入到数据库中。在导入的过程中，遇到如下错误：导入不成功
当执行source /Users/huoyajing/kupai-worker/software/mysql/employees_db/employees.sql时，导入错误，提示“Failed to open file ‘load_employees.dump’,error:2”，说明找不到，我们可以编辑employees.sql加入全路径，如下图：
employees.sql
导入成功之后，我们则正是开启索引总结。

唯一索引：

查看唯一索引效率，我们首先要开启性能分析功能：

//查看是否打开了性能分析列表；
select @@profiling;

select @@profiling

// 打开 profiling 功能
set profiling=1;

set profiling

//再无索引情况下执行语句
select * from employees where first_name = 'Chirstian';
//为first_name加上索引
alter table employees add index first_name (first_name);
//加入索引之后再次执行
select * from employees where first_name = 'Chirstian';

如下为性能分析结果：
分析结果
通过红色标记可发现，加入索引之后，性能提高了50倍之多，可见索引的速度之快。

联合索引：

联合索引遵循原则：最左前缀匹配原则
这里说明一下explain几个关键字段的含义

字段 说明
id 这是SELECT的查询序列号
select_type select_type就是select的类型;SIMPLE：简单SELECT(不使用UNION或子查询等)
type 有无使用索引 结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL(若索引在range之外，则证明索引无啥效果，性能很差)
possible_keys 显示MySQL可能决定使用的键（索引）
key 显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
rows 显示MySQL认为它执行查询时必须检查的行数
filtered 显示了通过条件过滤出的行数的百分比估计值

接下来我们对（birth_date, first_name, last_name）创建一个联合索引：

alter table employees add index bd_fn_ln (birth_date, first_name, last_name);

分别执行以下SQL：

  1. select * from employees where birth_date = ‘1954-05-01’ and first_name = ‘Chirstian’ and last_name = ‘Koblick’;
    001
  2. select * from employees where birth_date = ‘1954-05-01’ and first_name = ‘Chirstian’ and last_name = ‘Koblick’;
    002
  3. select * from employees where birth_date = ‘1954-05-01’ and last_name = ‘Koblick’;
    003
  4. select * from employees where last_name = ‘Koblick’ and birth_date = ‘1954-05-01’;
    004
    注意：语句3和语句4其实是完全等效的，因为并不符合最左前缀原则，所以只是birth_date索引生效，last_name索引并不生效。看语句3和语句4，explain执行的rows字段，值为60，其实就是在对birth_date进行插入之后，查询出的条数为60条，然后再从60条内挑选last_name = ‘Koblick’的数据。
    看根据birth_date查询条数：
    0001
  5. select * from employees where first_name = ‘Chirstian’ and last_name = ‘Koblick’;
    10
    看红色标记部分，因为以上我们见了一个firse_name唯一索引，和一个三个字段的联合索引，此语句根据first_name和last_name查询，因为并非是符合最左前缀原则，所以并没有用到联合索引，仅仅是使用了我们建的唯一索引值，rows为226，filtered为10.00证明根据firse_name查询出的结果为226行，通过firse_name查询过滤出的行数占比10%左右。
    11
    同样执行语句5，但是把唯一索引去除，看explain执行结果：
    12
    并无用到索引。效率很慢。

索引使用注意项：

  • 选择区分度高列作为索引，区分度是一个介于0和1之间的小数，越接近1区分度越高，越适合做索引。什么意思呢，就是说比如男女字段，只有男女两个值，并不是适合做索引，但是比如地名，通讯录，从a-z；又或者一个公司中，每个员工有一个工号，用工号做索引，那么区分度就是1，用工号做索引就非常有效。
  • 唯一索引和主键索引区别：unique index值必须是唯一的，但是有且可以有多个NULL值；而primary key，值也必须是唯一的，其唯一区别是primary是不能有NULL值的。对于主键也是可以做联合索引的，其实意思就是本条记录用一个字段作为主键是不能确保唯一的，比如我用A和B都作为主键，两个联合到一起就是所谓的联合主键。
  • 导致索引失效的几种常出现的情况：
    -查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等）都会导致索引失效；
    -如果条件中有or，要想索引失效，则必须让or的所有字段都加上索引；
    -like查询是以%开头（如图是%开头和结果的两种结果）
    111
    -如果列类型是字符串，那一定要在条件中将数据使用引号引用起来；
    -字符型字段为数字时在where条件里不添加引号；
    -not in ,not exist；(唯一索引失效）
    13
    -B+索引中，is null索引失效；is not null索引生效；
    14

索引讲述到此，下篇则主要针对原理进行一下讲解。

posted on 2018-08-08 18:25  huohuoL  阅读(117)  评论(0)  编辑  收藏  举报

导航