十年网站开发经验 + 多家企业客户 + 靠谱的建站团队
量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决
1.添加PRIMARY KEY(主键索引):
成都创新互联公司是一家专注于成都做网站、网站设计与策划设计,凌海网站建设哪家好?成都创新互联公司做网站,专注于网站建设10余年,网设计领域的专业建站公司;建站业务涵盖:凌海等地区。凌海做网站价格咨询:13518219792
2.添加UNIQUE(唯一索引) :
3.添加INDEX(普通索引) :
4.添加FULLTEXT(全文索引) :
5.添加多列索引:
在实际开发中使用数据库时,难免会遇到一些大表数据,对这些数据进行查询时,有时候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。
建立索引,要使用离散度(选择度)更高的字段。
我们先来看一个重要的属性列的 离散度,
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)
==所以,截取的长度越长就会越接近字段在全部数据中的选择度
==所以,我们要权衡索引大小和查询速度。
举个例子,通过不同长度去计算,与全表的选择性对比:
SELECT COUNT(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.联合索引把散列性高(区分度高)的值放在前面