关闭

mysql建索引原则及索引区别

标签: mysql
110人阅读 评论(0) 收藏 举报
分类:
索引原则
1、最左前缀匹配
    mysql 会一直向右匹配直到遇到范围查询(<,>,between,like)就停止匹配。
    如:a=1 and b=1 and c>3 and d=4 ,如果建立(a,b,c,d)顺序的索引,d是使用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a b d 可以任意调整顺序。
2、= 和 in 可以乱序。
    比如:a=1 and b =2 and c=3,   建立(a,b,c)索引可以任意顺序,mysql优化器会自动优化。
3、尽量选择区分高度的列作为索引。
    区分度的公式:count(distinct col)/count(*),表示字段不重复比例,比例越大扫描记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0 。
    一般需要join 的字段我们都要求是0.1 以上,即平均1条扫描10条记录
4、索引列不能参与计算

索引使用
1、索引的列是出现在where 子句后面或连接子句中指定的列
2、使用唯一索引,索引的列基数越大越好。(不同的值)
3、使用短索引。如果对字符串列进行索引,应该指定一个长度,只要有可能就应该这样做。例如有一个varchar(200)列,如果在前10或20个字符多数值是唯一的,就不要对整列进行索引。对前10或20个字符进行索引能节省大量的索引空间,使查询更快。较少的索引涉及的磁盘IO较少,较短的值的比较速度更快。主要是:对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySql也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
4、最左前缀。创建N列索引时,实际创建了MySql可以利用的N个索引。多列索引可起几个索引的作用,加为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
5、不要过度索引
6、对于InnoDB 存储引擎的表,记录默认会执照一定的顺序保存,如果有明确的定义主键,则按照主键顺序保存。若没有主键,有唯一索引,就按照唯一索引的顺序保存。如果没有主键也没有唯一索引,表中会自动生成一个内部列,按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的。InnoDB 表的普通索引都会保存主键的键值,所以主键要尽量的选择较短的数据类型。

索引区别
BTREE 索引
    >,<,>=,<=,between,!= ,  <> ,  Like 'pattern'('pattern' 不以通配符开始),使用这些操作符时,都可以使用相关列上的索引
HASH 索引
    只用于= 或 <=> 操作符的等式比较
    优化器不能使用HASH 索引来加速ORDER BY 操作
    MySQL 不能确定在两个值之间大约有多少行。如果将一个MyISAM 表改为HASH 索引MEMORY 表,会影响一些查询的执行效率
    只能使用整个关键字来搜索一行
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:38011次
    • 积分:1179
    • 等级:
    • 排名:千里之外
    • 原创:81篇
    • 转载:8篇
    • 译文:0篇
    • 评论:4条
    文章分类
    最新评论