mysql 慢查询优化

# ### part1 : sql

语句优化
 
# (1) mysql 执行流程
客户端: 
    发送链接请求,然后发送sql语句
    
服务端:
    1.连接层: 提供和客户端链接的服务
        show processlist;查看所有登录到mysql的用户
    
    2.服务器:
        (1)提供各种用户使用的接口(增删改查)
        (2)提供sql优化器(mysql query optimizer)
        (发现sql语句执行效率非常慢,会经过优化器优化,然后把优化的结果进行执行)
        
    3.存储引擎:
        把得到的数据进行保存,
        innodb : 支持事务处理,支持行锁,支持高并发
        myisam : 支持表锁,不支持并发.
        
        把数据存储在文件或者内存当中
"""
create table ceshi_table(
id int primary key auto_increment,
name varchar(255)
)engine = myisam auto_increment=2 charset = utf8;
"""
    4.文件和日志
        产生日志文件 binlog 二进制文件

# (2) sql 卡顿原因
    硬盘读写数据,io延迟高,sql语句性能低,索引失效,导致sql执行时间长
    编写过程:
        select .. from .. join  on where .. group by .. having .. order by limit
    
    解析过程:
        from .. join on where  group by having select distinct order by limit .. 

# (3) 索引
    # 索引(index)概念:
        是一个树状的数据结构,即(B树结构,分支节点>2)
        相当于字典的目录,功效是加快查询速度;
        常用树: B树(balance-tree) , 二叉树,红黑树,hash树


    # 树节点:
        根节点(最顶级节点)
        分支节点(父节点,子节点)
        叶子节点(最后一层存储数据的节点)
        树的高度(树的层级,理想是三层)

    [b+] : 在相邻的叶子节点上,加入了双向链表(指针),当前叶子节点不但保存当前值,还保存了下一个叶子节点的地址[小范围数据,查询速度很快]
    [b*] : 在相恋的分支节点上,加入了双向链表(指针),当前分支节点不但保存当前值,还保存了下一个分支节点的地址[在大范围里,找数据速度加快]



# (4) innodb 和 myisam 的索引结构
    (1) 聚集索引(聚簇索引) [innodb存储引擎的特点,myisam没有]
        如果有主键,自动以主键作为聚集索引列(字段)
        如果没有主键,选择唯一键
        都没有,自动生成隐藏聚集索引,该字段是6个字节,类型为长整型;
        
        分支节点是存储下一层节点的最小值,用来划分范围,追求的矮胖的数据结构
        在数据量变大的时候,尽量在树层级高度不变的情况下,横向发展,好处:可以减少io次数,提升查询效率
        真实的数据,直接在叶子节点上存储,所以速度快.
        
    (2) 辅助索引(非聚簇索引,二级索引,普通索引)
        对这一列的数据先排序,划分区间,把索引值分布到叶子节点上
        辅助索引存储的是加了索引的字段值和对应映射的主键id(primary key=>pk),没有存储真实的数据
        通过找出这个主键id,再去聚集索引树状结构中查询真实数据;
        
        辅助索引辅助聚集索引找数据的,辅助索引叶子节点重复值过多,会导致回表的次数增多,随机产生的io减慢查询效率
        如果想要解决重复问题,使用联合索引,更加精确找出对应唯一的那个id.
    
    (3) 两者区别:
        myisam 和 innodb 使用的索引数据结构都是B+树,但是在叶子节点上存储的数据不同
        innodb的文件结构中只有.frm 和 .ibd , 直接把数据存在了叶子节点上
        myisam的文件结构中有.frm .myd .myi , 叶子节点上存储的索引列,通过索引列映射对应的地址,在去通过这个地址找到实际的数据
        
        innodb 一个表只有一个聚集索引,和多个辅助索引,排序速度更快
        myisam 只能有多个辅助索引,没有聚集索引
        
    (4) 性能优化:
        利用索引查询时,速度很快,相反,增删改速度会变慢,会改变树状结构;
        追求:让每一个分支节点存储的数据尽量小,减少树状结构纵向值高度上的增加

# ### part2 : 索引
# 1.常用索引种类:
普通索引(index)
    -提高查询的效率

唯一索引:
    -主键索引 primary key : 在创建主键约束的同时,创建索引(不为空,唯一)
    -唯一索引 unique      : 在创建唯一约束的同时,创建索引(允许为空,唯一)

联合索引:
    -primary key() : 联合主键索引
    -unique()      : 联合唯一索引 
    -index()       : 联合普通索引

# 2.常用索引的应用场景
编号int,
姓名varchar(255),
身份证号char(18),
电话char(11),
住址varchar(255),
备注信息: text,
姓:char(10)
名:char(10)

编号int, 主键
姓名varchar,可以使用普通索引
身份证号char, 唯一索引unique
电话char,唯一索引
备注信息:text 全文索引 , 可使用fulltext(全文索引) 多数情况下使用第三方软件Sphinx来运行
姓和名 ,通过来说一起查询,可以使用联合索引

# 3.常用的索引数据结构  hash树  与  B-Tree
hash类型的索引:   数据在内存中,通过键来获取值,查询单条数据最快,一个范围的数据慢 
B-Tree类型的索引: b+数(理想层级三级),三层B树,理论上存放的数据量可以支撑百万条数据;

# 不同的存储引擎支持的索引种类
innodb  : 支持事务,行级锁,            支持 B-Tree  ,fulltext ,不支持hash类型索引结构
myisam  : 支持表级锁,不支持事务      支持 B-Tree , fulltext  ,不支持hash类型索引结构
memory  : 不支持事务,支持表级锁      支持 B-Tree ,hash类型   ,不支持fulltext索引结构

# 4.建立索引
# (1) 方法1,建表的时候,创建索引  index 索引名(索引字段)
create table t1(
    id int primary key,
    name char(100),
    index index_name(name)
);

# (2) 方法2.建表之后,创建索引 create index 索引名 on 表名(索引字段)
create table t2(
    id int primary key,
    name char(100)
);
create index index_name on t2(name);

# (3) 方法3.建表之后,创建索引 alter table 表名 add index 索引名(索引字段)
create table t3(
    id int primary key,
    email char(100)
);
alter table t3 add index index_email(email)

# (4) 删除索引
drop index index_email on t3


# 5.正确使用索引
alter table s1 add index index_id(id) / create index index_id on s1(id)
select count(*) from s1 where id = 5;
# 发现加了索引和不加索引时间差距较大
# 注意加了索引之后,ibd文件会表达

# (1) 把频繁作为搜索条件的字段作为索引,查单条数据,如果查询的是一个范围内的数据,不能命中索引
# 范围小的数据  表达范围的符号: id > <  >= <= != like "xboy" between and in... 

# (2) 选一个字段作为索引,这个列(字段)必须是区分度较高的字段
"""这个字段对应的值,如果出现了大量重复,在通过辅助索引查询的时候,会出现大量的随机id,增加聚集索引中的查询量,影响速度"""
"""区分度高的字段,推荐加上索引 ,必须系统会自动给primary key 和 unique 两个约束自动加索引"""
create index index_name on s1(name);
select count(*) from  s1 where name = "xxxx"
select count(*) from  s1 where name = "xboyww" # 不推荐使用区分度不高的字段加索引

# (3) 条件中,不能让索引字段参与计算,不能命中索引
select * from s1 where id = 1000
select * from s1 where id*3 = 3000

# (4) 条件中含有and , sql语句会经过优化器优化.
# 1.如果有and 相连,找到第一个有索引的并且树的高度最矮的字段进行优化
select count(*) from s1 where email = "xboyww1000@oldboy"
select count(*) from s1 where email = "xboyww1000@oldboy" and id = 1000
select count(*) from s1 where name = "xboyww" and  email = "xboyww1000@oldboy" and id = 1000

# 2.如果有or相连,没有优化,所有语句从左到右执行,索引会失去意义
select count(*) from s1 where id = 1000 or email = "xboyww1000@oldboy";

# (5) 联合索引 最左前缀原则 index(字段1,字段2 .... )
drop index index_id on s1;
drop index index_name on s1;
create index union_index on s1(first_name,last_name);

select count(*) from s1 where first_name="王6" and last_name="文6" # 命中索引
select count(*) from s1 where last_name="文6" and first_name="王6" # 命中索引
# select count(*) from s1 where last_name="文6"; 不会命中索引,被标记的first_name不存在
select count(*) from s1 where first_name="王6" and gender = "man";
select count(*) from s1 where first_name="王6" and gender = "man" and name = "xboyw11w";
# 最左前缀原则: 被标记的MUL这个字段存在,就命中索引
first_name + name + gender + ... (该字段存在即可)

# (6) 其他
    使用了函数不能命中索引
    select count(*) from s1 where reverse(first_name) = "文2";
    类型不匹配不能命中索引
    select count(*) from s1 where first_name = 90;