关于mysql:Mysql索引

32次阅读

共计 5639 个字符,预计需要花费 15 分钟才能阅读完成。

7、索引

概念:索引 index 是帮忙 MYSQL 高效获取数据的数据结构。索引是数据结构(树) MYSQL 里的索引是 B + 树

索引相似于书的目录

SQL 优化的起因:性能低,执行工夫长,等待时间长,sql 语句欠佳(连贯查问)、索引生效、服务器参数设置不周

SQL 编写过程:

select .. from  .. join ... on ..where  ..group by ... having...order by...limit..

解析过程

先解析 from.... on  .. join...where ..group by ...having ..select ..order by  limit

索引的底层原理 B + 树

三层 B + 树,一个关键字对应一个指针,对应一个指数

B+ 树的数据全副寄存在叶节点中,

B+ 树中查问任意的数据次数:n 次(B+ 树的高度)

7.1、索引分类

  1. 单列索引

    • 主键索引(PRIMARY KEY)

      ​ 惟一的标识,主键不可反复

    • 惟一索引 (UNIQUE KEY/index)

      ​ 避免出现反复的列,能够反复,多个列都能够标识位 惟一索引

      人们创立惟一索引的目标往往不是为了进步访问速度,而只是为了 == 防止数据呈现反复 ==。

  2. 惯例索引 (KEY/INDEX)

    • 默认的,index。key 关键字来设置
  3. 全文索引 (FullText)

    • 疾速定位数据
  4. 复合索引

    • 多个列形成的索引(相当于二级目录:z :zhao) 比方 先找 name 列 再找 age,(name,age)不肯定要都查问,只有反复的状况下才须要,复合索引能够有多个列

创立索引

  1. CREATE 索引类型 索引名 on 表名(字段)
--- 创立复合索引

-----【形式一】create index  dept_index on tb(dept,name)
--tb(dept,name) 会主动生成合乎索引


----【形式二】alter table 表名 索引类型 索引名(字段)

alter table tablename  add unique index_name(name)

删除索引

drop index 索引名 on 表名


show index from 表名 \G

7.2、索引应用准则

索引的弊病
  • 索引不是越多越好,不要对常常变动的数据加索引
  • 小数据量的数据不要加索引
  • 很少应用的字段也不倡议加索引
  • 索引尽管能够进步查问的效率,然而会升高增删改的效率
索引的长处
  • 进步查问效率(升高 IO 使用率)
  • 升高 CPU 使用率

7.3、SQL 性能问题

  • 剖析 SQL 的执行打算:explain,能够模仿 SQL 优化器执行 SQL 语句
  • MySQL 查问优化其会烦扰咱们的优化【mysql 有一个查问优化器】

查问执行打算

explain + sql 语句
mysql> explain select * from linelock \G
*************************** 1. row ***************************
           id: 编号
  select_type: 查问类型
        table: 表名
   partitions: NULL
         type: 类型
possible_keys: 预测用到的索引,可能用到索引的列都会列出来
          key: 理论应用的索引
      key_len: 用于解决查问的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查问不                 肯定都能应用到所有的列,具体应用到了多少个列的索引,这里就会计算进去,没有应用到的列,这里                不会计算进去
          ref: 表之间的援用
         rows: 执行打算中估算的扫描行数,不是准确值
     filtered: 100.00 这个字段示意存储引擎返回的数据在 server 层过滤后,剩下多少满足查问的记录数量的比                例,留神是百分比,不是具体记录数。Extra: 额定的信息
1 row in set, 1 warning (0.00 sec)


----type:索引类型、类型
---system>const>eq_ref>ref>range>index>all,
--- 对 type 进行优化的前提:有索引
----system、const 只是现实状况,理论能达到 ref>range

ref: 非位移性索引,对于每个索引建的查问,返回匹配的所有行(0, 多)
range:检索指定范畴的行,where 前面是一个范畴查问(between , > ,< =)

index: 查问全副索引中的数据
all: 查问全副表中的数据


system/const:后果只有一条数据
eq_ref: 后果多条;然而每条数据是惟一的;ref:后果多条;然而每条数据是 0 或多条


SQL

create table course (-> cid int(3),
    -> cname varchar(20),
    -> tid int(3)
    -> );


create table teacher(-> tid int(3),
    -> tname varchar(20),
    -> tcid int(3)
    -> );

create table teacherCard(-> tcid int(3),
    -> tcdesc varchar(200)
    -> );

--- 数据插入
insert into course values(1,'java',1),(2,'html',1),(3,'sql',2),(4,'jin',3);


insert into teacher values(1,'tz',1),(2,'tw',2),(3,'tl',3);

insert into teacherCard values(1,'tzdesc'),(2,'twdesc'),(3,'tldesc');

explain select t.* from teacher t,course c,teacherCard tc where t.tid=c.tid and t.tcid=tc.tcid and (c.cid=2 or tc.tcid=3);
------id 值雷同的状况下
---- 数据小的表优先计算,联立表查问时




----id 值不同的时候
----id 值越大越优先查问


select_type:
PRIMARY: 蕴含子查问 SQL 中的 主查问(最外层)SUBQUERY: 蕴含子查问 SQL 中的子查问(非最外层)
simple:简略查问(不蕴含子查问、union)derived:衍生查问(应用到了长期表)------a、在 from 子查问中只有一张表
 ------b、在 from 子查问中,如果有 table1 union table2,则 table1 就是 derived
 
 
 ---ref:留神与 type 中的 ref 值辨别
 -- 作用:出名以后表所参照的字段
 

 
--- 在 utf- 8 中一个【字符】占【三个字节】---- 如果索引字段能够为 Nul,则会应用一个字节用于标识

{{
----etra 字段
----【using filesort】:性能耗费大;须要额定的一次排序(查问),个别呈现在 order by 中

--- 对于单索引,如果排序和查找的是同一个字段,则不会呈现 using filesort;如果不一样则会呈现
--- 对于合乎索引:不能跨列(最佳左前缀)-----【using temporary】:性能损耗较大,用到了长期表,个别呈现在 group by 中
---- 解决办法:查什么用什么分组


---【using index】:性能晋升,索引笼罩
--- 示意:不读取源文件,只从索引字段开始查问,即不须要回表查问

--- 只有应用到的列,全副在索引中,那么就会有 using index
---using index 会对 possible_key 和 key 造成影响
--1、如果没有 where,则索引只呈现在 key 中
--2、如果有 where,则索引呈现在 key 和 possible_keys 中

---【using where】(须要会表查问)-- 假如 age 是索引列,name 不是
    -- 但查问语句中有 select age,name from。。where age=。。此语句必须回原表查 name
    
---【impossible where】:where 子句永远为 false
}}

7.4、优化案例

单表优化、两表优化、多表优化

7.4.1、单表优化

create table book(bid int(4) primary key,
name varchar(20) not null,
authorid int(4) not null,
publicid int(4) not null,
typeid int(4) not null
);

insert into book values(1,'java',1,1,2),(2,'fc',2,1,2),(3,'gf',3,2,1),(4,'magh',4,2,3);

1、问题:查问 authorid= 1 且 typeid 为 2 或 3 的 bid

select bid from book where typeid in(2,3) and authorid =1 ;
explain select bid from book where typeid in(2,3) and authorid =1 ;   

优化:加索引

alter table book add index idx_bta(bid,typeid,authorid);

依据 sql 理论解析的程序,调整索引的程序

一旦进行索引降级优化,先前的索引应该删除,避免烦扰

drop index idx_bta on book

索引降级

alter table book add index idx_tab(typeid,authorid,bid);
--- 尽管能够回表查问 bid,然而能够将 bid 放在索引中,进步查找效率

索引再次优化

------ 思路:因为范畴查问 in 有时会实现,因而替换索引的程序,将 typeid
alter index idx_tab on book;
alter table book add index idx_atb(authorid,typeid,bid);

explain select bid from book where  authorid =1 and typeid in(2,3) order by typeid desc;

小结:

  • 索引不能跨列使(最佳左前缀),放弃索引的定义和应用的程序一致性
  • 索引须要逐渐优化
  • 将含 in 的范畴查问放到最初,避免索引生效
  • using where 须要回原表查问,using index 不须要回原表查问

7.5、防止索引生效的准则

  • 复合索引,不要跨列或者无序应用(最佳左前缀)
  • 尽量应用全索引匹配

    • 建了 (a,b,c) 三个索引,那么查问的时候尽量全用上
  • 不要在索引上进行任何操作(计算、函数、类型转换),否则索引生效

    • 例如:select。。。where A.x =。。。;假如 A.x 是索引,那么不要进行
    • select。。。where A.x*3 =。。。会索引生效
  • 对于复合索引(a,b,c) a 生效了,b,c 均生效
  • 合乎索引不能应用不等于 (!= <>) 或 is null (is not null),否则本身以及右侧所有全副生效

SQL优化是一种概率层面的优化。至于是否理论应用了咱们的优化,须要通过 explain 进行揣测

体验概率状况:起因是服务层两头有一层sql 优化器,可能会影响咱们的优化

  • like 尽量以“常量”结尾,不要以’%‘结尾,否则索引生效

    select * from use where name ='%x%';---name 索引生效
    
    -- 如果必须应用 '%x%' 进行含糊查问
    -- 则应用索引笼罩,能够解救一部分
    select  tname from ta where tname like '%x%';
  • 尽量不要应用类型转换(显示、隐式),不然会索引生效

  • 尽量不要应用 or,否则索引生效

7.5.2、一些其余的优化办法(刨除索引)

  • exist 和 in

    如果主查问的数据集大,则应用 In

    如果子查问的数据集大,则应用 exist

    exist 语法:

    select tname from teacher where exist(sleect * from teacher)

    --- 将主查问的后果,放到子查问中进行条件测验【是否有数据】,如果合乎校验,则保留数据
  • order by 优化

    • using filesort 有两种算法:双路排序、单路排序(依据 IO 的次数)
    • MySQL4.1 之前默认应用双路排序;双路:扫描两次磁盘(1:从磁盘读取排序字段,进行排序(buffer 中执行)2:扫描其余字段)
    • MySQL4.1 之后默认应用单路排序:只读取一次全副的字段,在 buffer 中进行排序。然而单路排序会有肯定的隐患(不肯定的真的是一次。)如果数据很大,能够思考调大 buffer 容量的大小:set max_length_for_sort_data
    • 如果 max_length_for_sort_data 太低,mysql 会主动切换到双路。
    • 防止应用 select * …..

7.6、SQL 排序 - 慢查问

mysql 提供的一种日志记录,用于记录 MySQL 中相应工夫超过阈值的 SQL 语句(long_query_time, 默认 10s)

慢查问日志默认敞开,开发调优是能够开启,如果最终部署时候要进行敞开。

查看是否开启了慢查问日志

show variables like '%slow_query_log%';

开启慢查问日志

  • 长期开启

    set global slow_query_log =1;   ---- 在内存中开启
  • 永恒开启
/etc/my.cnf 中追加
  • 慢查问阈值:
show variables like '%long_query_time%'
  • 长期设置阈值:

    set globale long_query_time=5   --- 要重新启动 mysql 失效
  • 查问超过阈值的 SQL:

    show global status like '%slow_queries%';
    
    -- 慢查问的 sql 被记录在了日志中,能够通过日志查看具体的慢 SQL
    -- 也能够通过 mysql 工具 查看【mysqldumpslow】-- 通过 mysqldumpslow --help 来查看
  • 慢 sql 工具查看

    -- 获取返回记录组最多的 3 个 SQL
    mysqldumpslow -s r -t 3  日志文件的门路
    

7.7、全局日志查问

全局查问日志:记录开启后的全副 sql 语句

show variables like '%general_log';

set global general_log=1; -- 开启全局日志
set global log_output='table';

-- 开启全局日志后,所有的记录都会被存储在 mysql.general_log 中

正文完
 0