说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的。
某个字段在SELECT语句的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。尤其是在
数据量大的情况下,创建普通索引就可以大幅提升数据查询的效率。
比如student_info数据表(含100万条数据),假设我们想要查询 student_id=123110 的用户信息。
索引就是让数据按照某种顺序进行存储或检索,因此当我们使用 GROUP BY 对数据进行分组查询,或者
使用 ORDER BY 对数据进行排序的时候,就需要 对分组或者排序的字段进行索引 。如果待排序的列有多
个,那么可以在这些列上建立 组合索引 。
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的 *** 作,如果对 WHERE 字段创建了索引,就
能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录,然后再对它进行更新或
删除。如果进行更新的时候,更新的字段是非索引字段,提升的效率会更明显,这是因为非索引字段更
新不需要对索引进行维护。
有时候我们需要对某个字段进行去重,使用 DISTINCT,那么对这个字段创建索引,也会提升查询效率。
比如,我们想要查询课程表中不同的 student_id 都有哪些,如果我们没有对 student_id 创建索引,执行
SQL 语句:
运行结果(600637 条记录,运行时间 0.683s ):
如果我们对 student_id 创建索引,再执行 SQL 语句:
运行结果(600637 条记录,运行时间 0.010s ):
你能看到 SQL 查询效率有了提升,同时显示出来的 student_id 还是按照 递增的顺序 进行展示的。这是因
为索引会对数据按照某种顺序进行排序,所以在去重的时候也会快很多。
首先, 连接表的数量尽量不要超过 3 张 ,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增
长会非常快,严重影响查询的效率。
其次, 对 WHERE 条件创建索引 ,因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下,
没有 WHERE 条件过滤是非常可怕的。
最后, 对用于连接的字段创建索引 ,并且该字段在多张表中的 类型必须一致 。比如 course_id 在
student_info 表和 course 表中都为 int(11) 类型,而不能一个为 int 另一个为 varchar 类型。
举个例子,如果我们只对 student_id 创建索引,执行 SQL 语句:
运行结果(1 条数据,运行时间 0.189s ):
这里我们对 name 创建索引,再执行上面的 SQL 语句,运行时间为 0.002s 。
创建一张商户表,因为地址字段比较长,在地址字段上建立前缀索引
问题是,截取多少呢?截取得多了,达不到节省索引存储空间的目的;截取得少了,重复内容太多,字
段的散列度(选择性)会降低。 怎么计算不同的长度的选择性呢?
先看一下字段在全部数据中的选择度:
通过不同长度去计算,与全表的选择性对比:
公式:
例如:
引申另一个问题:索引列前缀对排序的影响
拓展:Alibaba《Java开发手册》
【 强制 】在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本
区分度决定索引长度。
说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会 高达
90% 以上 ,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
这样也可以较少的建立一些索引。同时,由于"最左前缀原则",可以增加联合索引的使用率。
结论:在数据表中的数据行数比较少的情况下,比如不到 1000 行,是不需要创建索引的。
举例1:要在 100 万行数据中查找其中的 50 万行(比如性别为男的数据),一旦创建了索引,你需要先
访问 50 万次索引,然后再访问 50 万次数据表,这样加起来的开销比不使用索引可能还要大。
举例2:假设有一个学生表,学生总数为 100 万人,男性只有 10 个人,也就是占总人口的 10 万分之 1。
学生表 student_gender 结构如下。其中数据表中的 student_gender 字段取值为 0 或 1,0 代表女性,1 代
表男性。
如果我们要筛选出这个学生表中的男性,可以使用:
运行结果(10 条数据,运行时间 0.696s ):
结论:当数据重复度大,比如 高于 10% 的时候,也不需要对这个字段使用索引。
例如身份z、UUID(在索引比较时需要转为ASCII,并且插入时可能造成页分裂)、MD5、HASH、无序长字
符串等。
① 冗余索引
举例:建表语句如下
我们知道,通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索,再创建一
个专门针对 name 列的索引就算是一个 冗余索引 ,维护这个索引只会增加维护的成本,并不会对搜索有
什么好处。
② 重复索引
另一种情况,我们可能会对某个列 重复建立索引 ,比方说这样:
我们看到,col1 既是主键、又给它定义为一个唯一索引,还给它定义了一个普通索引,可是主键本身就
会生成聚簇索引,所以定义的唯一索引和普通索引是重复的,这种情况要避免。
欢迎共同进步:
QQ群:1007576722
https://huchao.blog.csdn.net/article/details/124220802?spm=1001.2014.3001.5502
假设index(a,b,c)最左前缀匹配:模糊查询时,使用%匹配时:’a%‘会使用索引,’%a‘不会使用索引
条件中有or,索引不会生效
a and c,a生效,c不生效
b and c,都不生效
a and b >5 and c,a和b生效,c不生效。
MySQL索引类型包括:一、普通索引
这是最基本的索引,它没有任何限制。有以下几种创建方式:
1.创建索引
代码如下:
CREATE INDEX indexName ON mytable(username(length))
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。
2.修改表结构
代码如下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定。
CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) )
-- 删除索引的语法:
DROP INDEX [indexName] ON mytable
二、唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
代码如下:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) )
三、主键索引
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:
代码如下:
CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) )
当然也可以用 ALTER 命令。记住:一个表只能有一个主键。
四、组合索引
为了形象地对比单列索引和组合索引,为表添加多个字段:
代码如下:
CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL )
为了进一步榨取MySQL的效率,就要考虑建立组合索引。
二:使用索引的注意事项
使用索引时,有以下一些技巧和注意事项:
1.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2.使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O *** 作。
3.索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序 *** 作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4.like语句 *** 作
一般情况下不鼓励使用like *** 作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算
select * from users where YEAR(adddate)<2007
将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:
select * from users where adddate<‘2007-01-01'
6.不使用NOT IN和<> *** 作。
三:sql优化原则
常见的简化规则如下:
1.不要有超过5个以上的表连接(JOIN)
2.考虑使用临时表或表变量存放中间结果。
3.少用子查询
4.视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
5.连接的表越多,其编译的时间和连接的开销也越大,性能越不好控制。
6.最好是把连接拆开成较小的几个部分逐个顺序执行。
7.优先执行那些能够大量减少结果的连接。
8.拆分的好处不仅仅是减少SQL Server优化的时间,更使得SQL语句能够以你可以预测的方式和顺序执行。
如果一定需要连接很多表才能得到数据,那么很可能意味着设计上的缺陷。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)