mysql---索引（二）

 聚集索引 和 二级索引

聚集索引选取规则 :

如果存在主键，主键索引就是聚集索引。 如果不存在主键，将使用第一个唯一（ UNIQUE ）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索

引

聚集索引的叶子节点下挂的是这一行的数据 。

二级索引的叶子节点下挂的是该字段值对应的主键值。

具体过程如下 :

① . 由于是根据 name 字段进行查询，所以先根据 name='Arm' 到 name 字段的二级索引中进行匹找。但是在二级索引中只能查找到 Arm 对应的主键值 10 。

② . 由于查询返回的数据是 * ，所以时还需要根据主键值10，到聚集索引中查找10对应的记录，最 终找到10 对应的行 row 。

③ . 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取

数据的方式，就称之为回表查询。

索引语法：

创建索引：

create INDEX idx_name on tb_user(name)

-- phone 手机号字段的值，是非空 且为唯一 为该字段创建唯一索引
create UNIQUE index idx_phone on tb_user(phone)

-- 为profession age status 创建联合索引
create UNIQUE index idx_phone on tb_user(phone)

-- 为email建立合适的索引来提升查询顺序
-- 一个字段可以有很多索引

create  UNIQUE index idx_email on tb_user(email)

select * from tb_brand;

SQL性能分析：

可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;

-- global 是查询全局数据 ;

SHOW GLOBAL STATUS LIKE 'Com_______' ;

那么通过查询 SQL 的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。 那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？ 次数我们可以借助于慢查询日志。

慢查询日志：

-- 慢查询日志：
-- 查看是否开启，慢查询
-- 查看是否开启慢查询 -- 只记录超过时间的sql语句
show variables  like '%slow_query_log%';
-- 开启慢查询
set global slow_query_log='ON'

set global slow_query_log='off'
-- 查询超时时间
show variables like 'long_query_time%';

-- 改超时时间
set global long_query_time=0.3;

-- 查询具体的执行时间 只能记录一段时间
show profiles;
-- 查询sql语句每个阶段耗时整个数据库的
show profile for query  16

explain select * from user where username ="张三";

desc  select * from user where username ="张三";

检查这条sql语句的执行情况。

执行情况如下图：

 

其中最重要的   id   type   possible_key   key 来判断性能好坏

索引失效的情况：

select * from tb_user where 
-- 在联合索引时 不能跳过字段 最左前缀 如果是联合索引，必须是严格按照顺序，否则索引（即使只出现一个，但也要严格按照索引，从左开始）会失效
-- 不能出现范围查询（大于等于 小于等于 除外） 否则会失效
-- 内置函数和聚合函数 也会使索引失效
-- 字符串不加引号也会失效 数据库底层会将 数字自动转成字符串，但是索引会失效
-- 模糊查询 尾部模糊查询 不失效  头部模糊查询失效
-- or 前后必须都有索引才能生效
-- 数据评估影响 有where条件查询时，返回值50%以上 就索全表 以下 就是 走索引

索引设计原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。

2). 针对于常作为查询条件（ where ）、排序（ order by ）、分组（ group by ）操作的字段建立索

引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7). 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。