Mysql 架构及优化之-索引优化

摘要：索引基础知识索引帮助高效获取数据的数据结构索引中叫键数据越大越重要索引好比一本书为了找到书中特定的话题查看目录获得页码索引列位于列索引按值查找并且返回任何包含该值的行如果索引了多列数据那么列的顺序非常重要存储引擎说明存储引擎表锁表级锁不支持

索引帮助mysql高效获取数据的数据结构 索引(mysql中叫"键(key)") 数据越大越重要
索引好比一本书,为了找到书中特定的话题,查看目录,获得页码
select fruit_name from fruit where id = 5 索引列位于id列,索引按值查找并且返回任何包含该值的行
如果索引了多列数据,那么列的顺序非常重要

myisam 存储引擎

表锁：myisam 表级锁  
不支持自动恢复数据：断电之后 使用之前检查和执行可能的修复 
不支持事务：不保证单个命令会完成, 多行update 有错误 只有一些行会被更新 
只有索引缓存在内存中：mysiam只缓存进程内部的索引 
紧密存储：行被仅仅保存在一起

Innodb存储引擎

事务性：Innodb支持事务和四种事务隔离级别
外键：Innodb唯一支持外键的存储引擎 create table 命令接受外键
行级锁：锁设定于行一级 有很好的并发性
多版本：多版本并发控制
按照主键聚集：索引按照主键聚集
所有的索引包含主键列：索引按照主键引用行 如果不把主键维持很短 索引就增长很大
优化的缓存：Innodb把数据和内存缓存到缓冲池 自动构建哈希索引
未压缩的索引：索引没有使用前缀压缩
阻塞auto_increment:Innodb使用表级锁产生新的auto_increment
没有缓存的count():myisam 会把行数保存在表中 Innodb中的count()会全表或索引扫描

索引在存储引擎实现的,而不是服务层

B-tree 索引

大多数谈及的索引类型就是B-tree类型, 可以在create table 和其他命令使用它
myisam使用前缀压缩以减小索引,Innodb不会压缩索引 myiam索引按照行存储物理位置引用被索引的行,Innodb按照主键值引用行
B-tree数据存储是有序的,按照顺序保存了索引的列 加速了数据访问,存储引擎不会扫描整个表得到需要的数据

B-tree 索引实例

    create table peple(
        last_name  varchar(50) not null , 
        first_name varchar(50) not null ,
        dob date not null ,
        gender enum("m","f") not null ,
        key(last_name,first_name,dob)  #决定索引顺序
    )

    使用B-tree索引的查询类型,很好用于全键值、键值范围或键前缀查找
    只有在超找使用了索引的最左前缀的时候才有用
    
    匹配全名：全键值匹配和索引中的所有列匹配
             查找叫Tang Kang 出生于 1991-09-23 的人 
             
    匹配最左前缀：B-tree找到姓为tang的人
    
    匹配列前缀： 匹配某列的值的开头部分 查找姓氏以T开头的人         
             
    匹配范围值：索引查找姓大于Tang小于zhu的人
    
    精确匹配一部分并且匹配某个范围的另外一部分：
               查找姓为Tang并且名字以字母K开头的人 精确匹配last_name列并且对
               first_name进行范围查询
    
    只访问索引的查询：B-tree支持只访问索引的查询，不会访问行

B-tree局限性

B-tree局限性：(案例中索引顺序:last_name first_name dob )
     
    如果查找没有送索引列的最左边开始,没有什么用处,即不能查找所有叫Kang 的人 
    也不能找到所有出生在某天的人，因为这些列不再索引最左边,也不能使用该索引超找
    某个姓氏以特定字符结尾的人
    
    不能跳过索引的列,即不能找到所有姓氏为Tang并且出生在某个特定日期的人
    如果不定义first_name列的值,Mysql只能使用索引的第一列
    
    存储引擎不能优化任何在第一个范围条件右边的列,比如查询是where last_name = "Tang"
    AND first_name like "K%" AND dob="1993-09-23" 访问只能使用索引头两列
    
    由此可知 索引列顺序的重要性！

哈希索引

    目前只有Memory存储引擎支持显示的哈希索引 而且Memory引擎对我来说不常用
    所以我们就轻描淡写的过了吧

R-tree(空间索引)

    Myisam支持空间索引 可以使用geometry空间数据类型
    空间索引不会要求where子句使用索引最左前缀可以全方位索引数据
    可以高效使用任何数据组合查找 配合使用mercontains()函数使用

全文索引

    fulltext是Myisam表特殊索引,从文本中找关键字不是直接和索引中的值进行比较
    全文索引可以和B-Tree索引混用 索引价值互不影响
    全文索引用于match against操作 而不是普通的where子句

前缀索引和索引选择性

    通常索引几个字符,而不是全部值,以节约空间并得到好的性能 同时也降低选择性
    索引选择性是不重复的索引值和全部行数的比值
    高选择性的索引有好处,查找匹配过滤更多的行,唯一索引选择率为1 最佳状态
    
    blob列 text列 及很长的varchar列 必须定义前缀索引 mysql 不允许索引他们的全文

前缀索引和索引选择性实例

  造数据
    #复制一份与cs_area表结构
    mysql> create table area like cs_area ;

    #插入1600数据
    mysql> insert into area select * from cs_area limit     1600;

    #模拟真实数据
    mysql> update area set name = (select name from cs_area order by rand() limit 1 );

#表area有name列 需要对name列前缀索引


      #计算得比值接近0.9350就好了


      #分别取 3 4 5位name值计算


      #可知name列添加5位前缀索引就可以了


      #Mysql不能在order by 或 group by查询使用前缀索引 也不能将其用作覆盖索引

聚集索引

    聚集索引不是一种多带带的索引类型 而是一种存储数据的方式
    Innodb 的聚集索引实际上同样的结构保存了B-tree索引和数据行
    
    "聚集" 是指实际的数据行和相关的键值保存在一起 
    每个表只能有一个聚集索引 因此不能一次把行保存在两个地方
    
    (由于聚集索引对我来说 不常用 我们就略过啦~)

覆盖索引

    索引支持高效查找行 mysql也能使用索引来接收列的数据 这样不用读取行数据
    
    当发起一个被索引覆盖的查询 explain解释器的extra列看到 using index 

    #满足条件：#
    # select 查询的字段必须 有索引全覆盖
    select last_name,first_name 其中 last_name 和first_name 必须都有索引
    #不能在索引执行like操作

为排序使用索引扫描

    

    mysql排序结果的方式：使用文件排序 、 扫描有序的索引
    explain中的type列若为 "索引(Index)" 说明mysql扫描索引
    
    单纯扫描索引很快，如果mysql没有使用索引覆盖查询 就不得不查找索引中发现的每一行
    
    mysql 能有为排序和查找行使用同样的索引
    
    如表 user 索引 (uid,birthday )
    
    使用排序索引：
    .... where date = "1993-09-23" order by uid desc  (索引最左前缀)
    .... where date > "1993-09-23" order by date, uid (两列索引最左前缀)
    
    不能使用索引进行排序的查询：
    where date = "1993-09-23" order by uid desc，com_id  
                    (使用了不同排序方向,索引都是升序排列)
    where date = "1993-09-23" order by uid desc，staff_id (引用了不再索引的列)
    where date = "1993-09-23" order by uid (不能形成最左前缀)
    where date > "1993-09-23" order by uid,com_id  
                    (where有范围条件 因此不会使用余下索引)

避免多余和重复索引

重复索引：类型相同,以同样的顺序在同样的列创建索引  比如在表user id列 添加 unique(id)约束 、id not null
primary key 约束   index(id)  其实这些是相同的索引 !
多余索引：如存在(A)索引 应该扩展它 满足 (A,B)索引 
(A,B)索引 <==> (B) 
(A,B)索引 <==> (A) 
(A,B) A最左前缀 (B,A) B最左前缀

索引实例研究

设计user表 字段：country、 state/region 、city 、sex 、age 、eye 、color 
功能：支持组合条件搜索用户 支持用户排序 用户上次在线时间

支持多种过滤条件

不在选择性很差的列添加索引

优化排序

select name,gender from user where sex="M" order by rating limit 10000,10

即时有索引 (sex,rating) 高偏移量话费很多时间扫描被丢掉的数据
select name,gender from user inner join (select id from user where x.sex = "M"
order by rating limit 100000,10) as x using (id) 

基于索引(sex,rating) 提取需要行的主键列, 联接以取得所有需要的列

表维护三个目标：查找和修复损坏、维护精确的索引统计,并减少碎片

查找并修复表损坏

check table 命令 确定表是否损坏 能抓到大部分表和索引错误 repair table 命令修复损坏的表
myisamchk 离线修复工具

更新索引统计

analyze table cs_area 更新索引统计信息 便于优化器优化sql
show index 命令检查索引的基数性

减少索引和数据碎片

myisam引擎 使用 optimize table 清除碎片 Innodb 引擎 使用 alter table .. engine =
.. 重新创建索引

正则化和非正则化

正则化数据库：每个因素只会表达一次   教师表teacher  (id,school_id) 学校表school  
(school_id,school_name)   优点：更新信息只变动一张表 缺点：简单的学校名称查询  需要关联表
非正则化数据库：信息是重复的 或者 保存在多个地方
教师表teacher  (id,school_id,school_name) 学校表school  
(school_id,school_name)
优点：便于直接统计对应学校名称的老师 缺点：更新需要变动的表多一张
正则化和非正则化并用：比如需要统计用户的发帖数 可以在user表添加字段num_message 保存发帖总数 避免高密度查询统计

缓存和汇总表

实例：统计过去24小时发布的信息精确的数量

表周期性创建

周期创建可以得到没有碎片和全排序索引的高效表

注意此法会将数据清除 只是得到一个没有碎片和高效的索引表
计数表：比如缓存用户朋友数量、文件下载次数 通常建立一个多带带的表 以保持快速维护计数器
计划任务定期聚合函数查询 更新对应的字段