目录
1.索引类型
1、主键索引
2、普通索引
3、唯一性索引
4、单列索引
5、组合索引
6、全文索引
MySQL中的全文索引(Full-text Index)是一种特殊类型的索引,专门用于支持全文搜索功能。这种索引使得在大量的文本数据中进行关键词搜索变得更加高效。
2.索引命名规范
1、主键索引
pk_xxx
2、唯一性索引
uk_xxx
3、组合索引
idx_xxx_xxx
4、全文索引
ftx_xxx
3.创建索引的方式
1、创建表的时候指定
CREATE DATABASE dbtest2;
USE dbtest2;
CREATE TABLE dept (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);
SHOW INDEX FROM dept;
2、ALTER TABLE……方式创建
DROP TABLE IF EXISTS book5;
CREATE TABLE book5 (
book_id INT ,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100) ,
`comment` VARCHAR(100),
year_publication YEAR
);
ALTER TABLE book5 ADD INDEX idx_cmt(`comment`);
ALTER TABLE book5 ADD UNIQUE INDEX uk_idx_bname(book_name);
ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id, book_name, info);
SHOW INDEX FROM book5;
3、CREATE INDEX……ON……方式
DROP TABLE IF EXISTS book6;
CREATE TABLE book6 (
book_id INT ,
book_name VARCHAR(100),
`authors` VARCHAR(100),
info VARCHAR(100) ,
`comment` VARCHAR(100),
year_publication YEAR
);
CREATE INDEX idx_cmt ON book6(`comment`);
CREATE UNIQUE INDEX uk_idx_bname ON book6(book_name);
CREATE INDEX mul_bid_bname_info ON book6(book_id, book_name, info);
SHOW INDEX FROM book6;
4.删除索引的方式
1、AFTER TABLE
ALTER TABLE table_name DROP INDEX idx_name
2、DROP INDEX
DROP INDEX idx_name ON table_name;
5.限制索引数目原则
在生活中凡是都有一个“度”,超过这个“度”就会引发一些糟糕的事件。在数据库索引设计时也是要遵循这个“度”的,索引的数目不是越多越好,我们需要有意识地限制每张表的索引数据,建议单张表索引数据不超过6个,原因如下:
1、索引就是提高搜索性能的文件集,也会带有磁盘容量的消耗,索引越多消耗的磁盘空间就越大。
2、索引会影响insert、delete、update等更新数据的使用,应该表数据变更时,需要实时维护索引。
3、MySQL在执行select语句时,会经过执行优化器,其会进行查询的优化,选择最优的索引进行查询,评估并生成出一个最好的执行计划,如果索引过多,会导致评估量增加,增加MySQL优化器生成执行计划的时间,进而减低查询效率。
6.不适合创建索引的场景
1、数据量小的表最好不要使用索引
在数据表中的数据行数比较少的情况下,比如不到 1000 行,数据库优化器是不会选择走索引的,所以创建索引不是必要。
2、有大量重复数据的列上不要创建索引
性别、店铺ID这些字段,数据区分度不高的列,创建索引对搜索性能提升微乎其微,但是缺降低了表更新数据的性能【需维护相关索引】,根据产出比,最好选择不要创建索引。
3、避免对经常更新的表创建过多的索引
第一层含义:频繁更新的字段不一定要创建索引。因为更新数据的时候,也需要更新索引,如果索引太多,在更新索引的时候也会造成负担,从而影响效率。
第二层含义:避免对经常更新的表创建过多的索引,并且索引中的列尽可能少。此时,虽然提高了查询速度,同时却会降低更新表的速度。
4、不建议使用无序的值作为索引
例如身份证、 UUID( 在索引比较时需要转为 ASCII ,并且插入时可能造成页分裂 ) 、 MD5 、 HASH 、无序长字 符串等。
5、删除不在使用或者很少的索引
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要,或者随着业务的调整,某个字段不再称为查询的条件,而为该字段创建了的索引。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。
6、不要定义冗余或重复的索引
CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
KEY idx_name (name(10))
);
我们知道,通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索【最左前缀原则】,再创建一个专门针对 name 列的索引就算是一个余索引,维护这个索引只会增加维护的成本,并不会对搜索有什么好处。
CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
);
我们看到,col1既是主键、又给它定义为一个唯一索引l,还给它定义了一个普通索引l,可是主键本身就会生成聚簇索引l,所以定义的唯一索引和普通索引是重复的,这种情况要避免。
7.适合创建索引的场景
一,推荐创建索引表的字段
1、字段的数值有唯一性的限制
业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。(来源: Alibaba )
说明:拥有唯一特性的字段创建唯一性索引,可以大大提高查询效率。不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的。
2、频繁作为where查询条件的字段
某个字段在 SELECT 语句的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。尤其是在 数据量大的情况下,创建普通索引就可以大幅提升数据查询的效率。 比如 student_info 数据表(含 100 万条数据),假设我们想要查询 student_id=123110 的用户信息。
下面的sql代码首先是查看student_info表中都有哪些索引?然后在不使用索引的情况下查询效率如何,对where查询条件的字段student_id添加索引之后查询效率又如何? 最后是执行耗时。
SHOW INDEX FROM student_info;
SELECT course_id, class_id, NAME, create_time, student_id
FROM student_info
WHERE student_id = 123110; #240ms
ALTER TABLE student_info ADD INDEX idx_sid(student_id);
SELECT course_id, class_id, NAME, create_time, student_id
FROM student_info
WHERE student_id = 123110; #17ms
3、经常group by和order by的列
索引就是让数据按照某种顺序进行存储或检索,因此当我们使用 GROUP BY 对数据进行分组查询,或者使用 ORDER BY 对数据进行排序的时候,就需要对分组或者排序的字段进行索引 。如果待排序的列有多个,那么可以在这些列上建立组合索引 。
如果SQL同时进行GROUP和ORDER,可以用GROUP和ORDER相关字段创建组合索引。
这里先将GROUP BY中用到的student_id字段相关的索引删除,看下查询效率。之后再对这个字段加索引,再看查询效率。
ALTER TABLE student_info DROP INDEX idx_sid;
SHOW INDEX FROM student_info;
SELECT student_id, COUNT(*) AS num
FROM student_info
GROUP BY student_id LIMIT 100; #514ms
ALTER TABLE student_info ADD INDEX idx_sid(student_id);
SELECT student_id, COUNT(*) AS num
FROM student_info
GROUP BY student_id LIMIT 100; #17ms
4、update、delete的where条件列
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作,如果对 WHERE 字段创建了索引,就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录,然后再对它进行更新或 删除。 如果进行更新的时候,更新的字段是非索引字段,提升的效率会更明显,这是因为非索引字段更 新不需要对索引进行维护。
SHOW INDEX FROM student_info;
UPDATE student_info
SET student_id = 10002
WHERE NAME = '462eed7ac6e791292a79'; #466ms
ALTER TABLE student_info ADD INDEX idx_name(NAME);
UPDATE student_info
SET student_id = 10003
WHERE NAME = '462eed7ac6e791292a79'; #16ms
5、distinct字段需要创建索引
有时候我们需要对某个字段进行去重,使用 DISTINCT ,那么对这个字段创建索引,也会提升查询效率。 比如,我们想要查询课程表中不同的 student_id 都有哪些,如果我们没有对 student_id 创建索引,执行:
SELECT DISTINCT(student_id)
FROM student_info; #531ms
ALTER TABLE student_info ADD INDEX idx_sid(student_id);
SHOW INDEX FROM student_info;
SELECT DISTINCT(student_id)
FROM student_info; #369ms
二,提升索引性能小技巧
1、多表join连接操作时,创建索引注意事项
首先, 连接表的数量尽量不要超过 3 张 ,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快,严重影响查询的效率。
其次, 对 WHERE 条件创建索引 ,因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下, 没有 WHERE 条件过滤是非常可怕的。
最后, 对用于连接的字段创建索引 ,并且该字段在多张表中的类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型,而不能一个为 int 另一个为 varchar 类型。
SELECT s.course_id, NAME, s.student_id, c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #16ms
DROP INDEX idx_name ON student_info;
SELECT s.course_id, NAME, s.student_id, c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #189ms
2、尽量使用列数据量小的字段创建索引
我们这里所说的类型大小指的就是该类型表示的数据范围的大小。我们在定义表结构的时候要显式的指定列的类型,以整数类型为例,有TINYINT、MEDIUMINT、INT、BIGINT等,它们占用的存储空间依次递增,能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话,在表示的整数范围允许的情况下,尽量让索引列使用较小的类型,比如我们能使用INT就不要使用BIGINT,能使用MEDIUMINT就不要使用INT。
这是因为:
1、数据类型越小,在查询时进行的比较操作越快;
2、数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以放下更多的记录,从而减少磁盘1/0带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率。
这个建议对于表的主键来说更加适用,因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键使用更小的数据类型,也就意味着节省更多的存储空间和更好的搜索性能。
3、使用字符串前缀创建索引
假设我们的字符中很长,那存储一个字符中就需要占用很大的存储空间。在我们需要为这个字符中列建立索引时。那就意味着在对应的B+树中有这么两个问题:
1、B+树索引中的记录需要把该列的完整字符串存储起来,更费时。而且字符串越长,在索引中占用的存储空间越大。
2、如果B+树索引中索引例存储的字符串很长,那在做字符串比较时会占用史多的时间。
我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引。这样在查找记录时虽然不能情确的定位到记录的位置,但是能定位到相应前缀所在的位置,然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间,又减少了字符串的比较时间,还大体能解决排序的问题。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255)
);
-- 创建前缀索引,使用email字段的前10个字符
CREATE INDEX idx_email_prefix ON users (email(10));
4、区分度高【散列性高】的列作为索引
列的是数指的是某一列中不重复数据的个数,比方说某个列包含值2,5,8,2,5,8,2,5,8,虽然有9条记录,但该列的基数却是3。也就是说,在记录行数一定的情况下,列的基数越大,该列中的值越分散:列的基数越小,该列中的值越集中。这个列的基数指标非常重要,直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引,为基数太小列的建立索引效果可能不好。
可以使用公式 select count(distinct a)/count(*)from table 计算区分度,越接近1越好,一般超过33%就算是比较高效的索引了。
计算列区分度:
SELECT count(distinct page_type)/count(*) FROM `t_product_recommend`
通过计算,下面列只有25%左右的区分度,区分度较低,作为索引列性价比不高。
拓展:联合索引把区分度高(散列性高的列放在前面)
5、组合索引的列顺序原则
1、越经常用到的列,越放到最左边
2、数据区分度越高的列越放到最左边
3、数据量越小的列,越放到最左边
提升组合索引性能原则就是尽量提高左则列的过滤效果
感谢观看!不妨帮咯咯点个赞再走 爱你~~~