关于mysql:Mysql索引

概念：索引 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+ 树的高度）

单列索引
- 主键索引（PRIMARY KEY)
  惟一的标识，主键不可反复
- 惟一索引 (UNIQUE KEY/index)
  避免出现反复的列，能够反复，多个列都能够标识位惟一索引
  
  人们创立惟一索引的目标往往不是为了进步访问速度，而只是为了 == 防止数据呈现反复 ==。
惯例索引 (KEY/INDEX)
- 默认的，index。key 关键字来设置
全文索引 (FullText)
- 疾速定位数据
复合索引
- 多个列形成的索引（相当于二级目录：z :zhao) 比方先找 name 列再找 age，（name，age）不肯定要都查问，只有反复的状况下才须要，复合索引能够有多个列

创立索引

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

索引的弊病

索引不是越多越好，不要对常常变动的数据加索引
小数据量的数据不要加索引
很少应用的字段也不倡议加索引
索引尽管能够进步查问的效率，然而会升高增删改的效率

索引的长处

进步查问效率（升高 IO 使用率）
升高 CPU 使用率

剖析 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
}}

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

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 不须要回原表查问

复合索引，不要跨列或者无序应用（最佳左前缀）
尽量应用全索引匹配
- 建了 (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，否则索引生效

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 * …..

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  日志文件的门路

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

show variables like '%general_log';

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

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

关于mysql:Mysql索引

7、索引

7.1、索引分类

7.2、索引应用准则

索引的弊病

索引的长处

7.3、SQL 性能问题

7.4、优化案例

7.4.1、单表优化

7.5、防止索引生效的准则

7.5.2、一些其余的优化办法（刨除索引）

7.6、SQL 排序 - 慢查问

7.7、全局日志查问