锐客网

  1. 首页 > 睿知 > it技术 > >

mysql索引怎么选择 mysql索引字段的选择

IT技术索引

mysql建索引的几大原则1．选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录 。例如，学生表中学号是具有唯一性的字段 。为该字段建立唯一性索引可以很快的确定某个学生的信息 。如果使用姓名的话 ， 可能存在同名现象，从而降低查询速度 。
2．为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间 。如果为其建立索引，可以有效地避免排序操作 。
3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度 。因此 ， 为这样的字段建立索引，可以提高整个表的查询速度 。
4．限制索引的数目
索引的数目不是越多越好 。每个索引都需要占用磁盘空间，索引越多 ， 需要的磁盘空间就越大 。修改表时，对索引的重构和更新很麻烦 。越多的索引 ， 会使更新表变得很浪费时间 。
5．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响 。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多 。
6．尽量使用前缀来索引
如果索引字段的值很长 ， 最好使用值的前缀来索引 。例如 ， TEXT和BLOG类型的字段，进行全文检索会很浪费时间 。如果只检索字段的前面的若干个字符 ， 这样可以提高检索速度 。
7．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要 。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响 。
8 . 最左前缀匹配原则，非常重要的原则 。
mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“ 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整 。
9 .=和in可以乱序 。
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
。
10 . 尽量选择区分度高的列作为索引 。
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少 ， 唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同 ， 这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录
11 .索引列不能参与计算，保持列“干净” 。
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大 。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
12 .尽量的扩展索引，不要新建索引 。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
注意：选择索引的最终目的是为了使查询的速度变快 。上面给出的原则是最基本的准则，但不能拘泥于上面的准则 。读者要在以后的学习和工作中进行不断的实践 。根据应用的实际情况进行分析和判断 ， 选择最合适的索引方式 。
Mysql建立索引经验在实际开发中使用数据库时，难免会遇到一些大表数据，对这些数据进行查询时，有时候SQL会查询得特别慢，这时候，有经验的老师傅会告诉你 ， 你看一下哪几个字段查的多，加一个索引就好了 。
那么，怎么合理地建立索引呢？这里分享一下我的一些经验，如有不妥之处，欢迎批评指正 。
1、不要盲目建立索引 ，先分析再创建
索引虽然能大幅度提升我们的查询性能 ， 但也要知道，在你进行增删改时，索引树也要同样地进行维护 。所以，索引不是越多越好，而是按需建立 。最好是在一整块模块开发完成后，分析一下，去针对大多数的查询，建立联合索引 。
2、使用联合索引尽量覆盖多的条件
这是说在一个慢sql里假如有五个where ， 一个 order by ，那么我们的联合索引尽量覆盖到这五个查询条件，如果有必要，order by 也覆盖上。
3、小基数字段不需要索引
这个意思是 ， 如果一张表里某个字段的值只有那么几个，那么你针对这个字段建立的索引其实没什么意义 ， 比如说，一个性别字段就两种结果 ， 你建了索引，排序也没什么意思（也就是索引里把男女给分开了）
所以说，索引尽量选择基数大的数据去建立，能最大化地利用索引
4、长字符串可以使用前缀索引
我们建立索引的字段尽量选择字段类型较小的，比如一个varchar(20)和varchar(256)的，我们在20的上面建立的索引和在256上就有明显的差距（字符串那么长排序也不好排呀，唉） 。
当然，如果一定是要对varchar(256)建立索引,我们可以选择里面的前20个字符放在索引树里（这里的20不绝对 ， 选择能尽量分辨数据的最小字符字段设计），类似这样KEY index(name(20),age,job) ，索引只会对name的前20个字符进行搜索，但前缀索引无法适用于order by 和 group by 。
5、对排序字段设计索引的优先级低
如果一个SQL里我们出现了范围查找，后边又跟着一个排序字段，那么我们优先给范围查找的字段设置索引，而不是优先排序 。
6、如果出现慢SQL ， 可以设计一个只针对该条SQL的联合索引 。
不过慢SQL的优化 ， 需要一步步去进行分析，可以先用explain查看SQL语句的分析结果 ， 再针对结果去做相应的改进 。explain的东西我们下次再讲 。
PS：在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记 ， 执行查询会返回执行计划的信息，而不是 执行这条SQL 。
mysql 复合 索引 怎么选择利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引 。复合索引的结构与电话簿类似，人名由姓和名构成 ， 电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序 。如果您知 道姓，电话簿将非常有用；如果您知道姓和名 ， 电话簿则更为有用 ， 但如果您只知道名不姓 ， 电话簿将没有用处 。
所以说创建复合索引时，应该仔细考虑列的顺序 。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处 。
如：建立 姓名、年龄、性别的复合索引 。
create table test（
a int,
b int,
c int,
KEY a（a,b,c）
）；
优： select * from test where a=10 a
MySQL前缀索引 前缀索引顾名思义，定义字符串的一部分当做索引，而不是把整个字符串当做索引 。默认地 ， 如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串 。
假设一张表有 id,name,email 2个字段
1.创建email列的普通索引应该是： alter table T add index idx_email1( email )
2.前缀索引的创建规则为： alter table table T add index idx_email2( email(6) )
当然第一索引包含是的整个字符串，第二个是该字段前6个字节（注意是字节）
对于这2中索引，B 树怎么存储呢？
INSERT INTO T (email) VALUES ('瞎子','zhangsh1234@163.com'), ('剑圣','lisi1998883@163.com'), ('露娜','zhangssxyz@163.com'), ('李白','zhangsy1998@163.com'), ('韩信','zhaq5481993@163.com'), ('百里玄策','hhaq5481993@163.com');
【谁还不是个野王啊】
普通索引存储为：
是的你没看错，前缀索引那颗树上的存储的是email的前6位字节，也就是你创建前缀索引时指定的前缀字节长度 。2种树相比，前缀索引存储了更少的数据，那么他所耗费的空间也就相比较少，这正是他的一个优点 。同样的也就相对的增加了扫描行数 。
什么增加了扫描行数？？？？ 这是为什么呢？
那么小朋友咱们一起来看下吧 。
假设SQL如此这般： select id,name,email from T where email = 'zhangsh1234@163.com'
那么这2个SQL，应该怎么操作呢 。
idx_email1:
2.到主键上查到主键为ID1的，判断email值是否正确【为什么判断呢，其实我理解是为了二次判断保证数据一致性吧，比较官方的解释尚未找到】，正确放入结果集
3.取 idx_email1 索引树上刚刚查到的位置的下一条记录 ， 如此往复 。
循环过程中，需要回主键取1次数据，所以系统可以认为只扫描了一行【1次是数第一棵树数出来的】
idx_email2:
1.从 索引数上找到满足索引值为 'zhangs'的该记录，取得 ID1的值
2.到主键上查到主键值是 ID1 的行，判断出 email 的值是’ zhangsh1234@xxx.com ’，这行记录放入结果集【不是要的值 ， 丢弃 ， 进行下一步】
3.取 idx_email2 上刚刚查到的位置的下一条记录,重复以上步骤
在这个过程中，要回主键索引取 3 次数据，也就是扫描了 3 行 。通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多 。
但是，对于这个查询语句来说 ， 如果你定义的 idx_email2 不是 email(6) 而是 email(8），也就是说取 email 字段的前 8 个字节来构建索引的话，即满足前缀’zhangsh’的记录只有一个，也能够直接查到 ID1，只扫描一行就结束了 。也就是说使用前缀索引 ， 定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本 。
那么问题来了，到底定义多长才算是合理呢？
一般的定义原则是 count(distinct(columnName))/count(*) ,当前缀索引【count(distinct(columnName(length)))，length是你想要创建列的前缀字节长度】越接近此值越好 ， 当有多个前缀字节都一样且都等于这个值时怎么选择呢，当然是 字节越少越好了哈，字节越少越省空间 。索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低 。
count(distinct(columnName(length)))翻译到SQL 为： count(dictinct(left(colunmName, length)))
前面我们说了使用前缀索引可能会增加扫描行数，这会影响到性能 。其实，前缀索引的影响不止如此，我们再看一下另外一个场景 。
来呀，上SQL： select id,email from T where email='zhangsh1234@163.com'
如果按照email全字段索引，那么此SQL 是不需要回表的【为什么不需要回表？兄嘚，这个相当于覆盖索引了哈】
那么如果按照前缀索引是否需要回表呢？答案是的 。
因为当判断前6个字节相等后 ， 需要拿到id 回表拿到email的全部内容进行比较，如果不相同 ， 丢弃这行 ， 否则加入结果集 。
那么有人会问了，我把长度放大点，包含所有字节不就好了吗？
那么此时会有如下问题 。
1.当你此时的长度是囊括了全字段，但是系统是不知道的，他还是需要回表再次判断的，去确定前缀索引的定义是否截断了完整信息 。
2.此时长度是够了，那么能肯定因为业务日后不会增加长度吗？
3.尽可能的加长长度，还不如直接建立全字段索引呢
综上，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素 。
前面说到的是，可以根据字段前面几个字节进行查询的，那么对于身份证这种，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的 。
或许你会说，多弄几个字节不就好吗？那么请问下自己为什么使用前缀索引呢 ， 不就是为了节省空间吗？
那么这么做合适吗？ 不合适对吗？乖~，快去反省下吧
那么采用前缀索引显示是不行的 ， 那么如果用前缀索引怎么办呢，聪明的你应该已经猜到了，采用倒叙存储，然后建立前缀索引 。
放到SQL 中就应该是这样的： select field_list from t where id_card = reverse('id_card_string');
当然了，这种逻辑建议放到业务逻辑中实现，而不是放到SQL 中 。
按照上述第4节的内容 ， 有人或许会有另一个想法，还倒叙建立前缀索引复杂不，hash索引或者hash字段不香吗？
有人会问了 ， 为什么要在创建一个值来存储hash值呢，如果不存储你知道原值是什么吗？ 同时hash算法是有一定重复可能的（hash值碰撞）
【可以了解下partition算法哦:[】 。如果重复了，不存储原值，你是无法判断出正确数据的 。
注：【hash字段不代表hash索引 ， hash索引原理正在快马加鞭】，简单说下hash索引，hash索引不需要创建一个值来存储hash值，而是有hasn表来存储【hash值碰撞时，由一个链表来搞定了】 ， 存储的内容为 hash值和每行的行指针。
说回来?。芴饬?
查询时： select field_list from t where id_card_crc=crc32('id_card_string') and id_card='id_card_string'
不过有个问题相信你也想到了，不管是hash存储值还是hash索引都是不支持范围查询的 。
来总结下这2个优缺点吧
1.从占用空间来看呢，倒叙索引不需要额外开辟存储空间，而hash字段需要额外的一个字段，所以从这点上看倒叙索引更胜一筹，NO!并不准确,如果前缀长度过长 ， 那么这2个情况额外的空间也就相差无几了
【mysql索引怎么选择 mysql索引字段的选择】 3.从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些 。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1 。而倒序存储方式毕竟还是用的前缀索引的方式 ， 也就是说还是会增加扫描行数
1.全字段完整索引比较占空间 ， 但是而走覆盖索引
2.前缀索引，节省空间，但会增加扫描 次数并且不能使用覆盖索引【每次都需回表校验】
3.倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题 。【倒叙方法建立放到业务逻辑中】
4.hash字段索引，相比前缀索引性能较为稳定，但是有额外的存储空间和计算消耗，同时也 不 支持范围查询
Mysql索引建立索引，要使用离散度（选择度）更高的字段 。
我们先来看一个重要的属性列的 离散度，
count(distinct(column_name)) : count(*) -- 列的全部不同值个数：所有数据行行数
数据行数相同的情况下，分子越大，列的离散度就越高 。简单来说 ， 如果列的重复值越多，离散度就越低，重复值越少，离散度就越高 。
当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢 。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引 。
创建一张商户表，因为地址字段比较长 ， 在地址字段上建立前缀索引
create table shop(address varchar(120) not null);
alter table shop add key(address(12));// 截取12个字符作为前缀索引是最优的吗？
问题是 ， 截取多少呢？截取得多了，达不到节省索引存储空间的目的，截取得少了，重复内容太多，字段的散列度（选择性）会降低 。怎么计算不同的长度的选择性呢？
先看一下字段在全部数据中的选择度计算公式：
select count(distinct address) / count(*) from shop;
select count(distinct left(address, n)) / count(*) as subn from shop;
count(distinct left(address,n)) / count(*) 的结果是会随着 n 的变大而变大 。举个例子，现在有两个address（东大街长兴小区，东大街福乐小区），那么 distinct(address，2)distinct(address，3）
==所以，截取的长度越长就会越接近字段在全部数据中的选择度
==所以，我们要权衡索引大小和查询速度 。
举个例子，通过不同长度去计算 ， 与全表的选择性对比：
SELECTCOUNT(DISTINCT(address))/COUNT(*) sub,-- 字段在全部数据中的选择度
COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,-- 截取前5个字符的选择度
COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,
COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,
COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,-- 截取前10个字符的选择度
COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,
COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,
COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,
COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15
FROM shop;
-------- -------- -------- -------- -------- -------- -------- -------- --------
| sub| sub5| sub7| sub9| sub10| sub11| sub12| sub13| sub15|
-------- -------- -------- -------- -------- -------- -------- -------- --------
| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |
-------- -------- -------- -------- -------- -------- -------- -------- --------
可以看到在截取 11 个字段时 sub11(0.9993) 就已经很接近字段在全部数据中的选择度 sub(0.9958)了 ， 而且长度也相较后面更短一些 ， 综合考虑比较合适 。
ALTER TABLE shop ADD KEY (address(11));
1.索引的个数不要过多（浪费空间，更新变慢）
2.在用于 where 判断 order 排序和 join 的（on）字段上创建索引
3.区分度低的字段，例如性别，不要建索引（离散度太低 ， 导致扫描行数过多）
4.更新频繁的值，不要作为主键或者索引（页分裂）
5.不建议用无序的值作为索引 ， 例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）
6.若在多个字段都要创建索引的情况下，联合索引优于单值索引
7.联合索引把散列性高（区分度高）的值放在前面
Mysql前缀索引长度确定方法添加语法： ALTER TABLE table_name ADD KEY(column_name(prefix_length));
在MySQL中mysql索引怎么选择，前缀长度最大值为255字节 。对于存储引擎为MyISAM或InnoDBmysql索引怎么选择的数据表，前缀最长为1000字节 。必须注意的是，在MySQL中，对于TEXT和BLOB这种大数据类型的字段 ， 必须给出前缀长度(length)才能成功创建索引 。
如何确定前缀索引长度mysql索引怎么选择？
可以通过计算选择性来确定前缀索引的选择性 ， 计算方法如下
全列选择性：
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
某一长度前缀的选择性
SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;
当前缀的选择性越接近全列选择性的时候，索引效果越好 。
参考文章：
MySQL索引操作命令详解
MySQL 前缀索引
mysql索引怎么选择的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql索引字段的选择、mysql索引怎么选择的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：如何查询服务器的登录IP地址？ 怎么查服务器登录ip

    下一篇：hbase范围删除，hbase删除namespace