4、UPDATE、DELETE 的 WHERE 条件列
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作,如果对 WHERE 字段创建了索引,就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录,然后再对它进行更新或删除。 如果进行更新的时候,更新的字段是非索引字段,提升的效率会更明显,这是因为非索引字段更新不需要对索引进行维护
mysql> UPDATE student_info SET student_id = 10002 -> WHERE NAME = '462eed7ac6e791292a79';# 550ms Query OK, 0 rows affected (0.55 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> ALTER TABLE student_info -> ADD INDEX idx_name(NAME); Query OK, 0 rows affected (2.26 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> UPDATE student_info SET student_id = 10002 -> WHERE NAME = '462eed7ac6e791292a79';# 1ms Query OK, 0 rows affected (0.001 sec) Rows matched: 0 Changed: 0 Warnings: 0
5、DISTINCT 字段需要创建索引
有时候我们需要对某个字段进行去重,使用 DISTINCT
,那么对这个字段创建索引,也会提升查询效率。
比如,我们想要查询课程表中不同的 student_id 都有哪些,如果我们没有对 student_id 创建索引,执行 SQL 语句:
SELECT DISTINCT(student_id) FROM `student_info`;
运行结果(600637 条记录,运行时间 0.683s )
如果我们对 student_id 创建索引,再执行 SQL 语句:
SELECT DISTINCT(student_id) FROM `student_info`;
运行结果(600637 条记录,运行时间0.010s
)
你能看到 SQL 查询效率有了提升,同时显示出来的 student_id 还是按照 递增的顺序
进行展示的。这是因为索引会对数据按照某种顺序进行排序,所以在去重的时候也会快很多。
6、多表 JOIN 连接操作时,创建索引注意事项
首先,连接表的数量尽量不要超过 3 张
,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快(n ,n^2 , n^3…),严重影响查询的效率。
其次,对 WHERE 条件创建索引,因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下,没有 WHERE 条件过滤是非常可怕的。
🔊注意:对于用连接的字段创建索引,这些字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型,而不能一个为 int 另一个为 varchar 类型。否则在查询时,虽然也会帮我们进行隐式的类型转换,转换时会使用函数,但会导致索引失效。索引失效情况在后续文章中还会给大家详细介绍,敬请期待。
举个例子,如果我们只对 student_id 创建索引,执行 SQL 语句,耗时0.21s
mysql> SELECT c.course_id, NAME, s.student_id, course_name -> FROM student_info s JOIN course c -> ON s.course_id = c.course_id -> WHERE NAME = 'WloNYD'; +-----------+--------+------------+-------------+ | course_id | NAME | student_id | course_name | +-----------+--------+------------+-------------+ | 10077 | WloNYD | 95666 | JfydVs | | 10077 | WloNYD | 95666 | nZkayq | | 10077 | WloNYD | 95666 | mTHDYg | | 10085 | wLonyD | 98444 | pZdpsR | +-----------+--------+------------+-------------+ 4 rows in set (0.21 sec)
这时,我们对 name 创建索引,再执行上面的 SQL 语句,耗时 0.00s
mysql> ALTER TABLE student_info -> ADD INDEX idx_name(NAME);# 为name创建索引 Query OK, 0 rows affected (2.52 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT c.course_id, name, s.student_id, course_name -> FROM student_info s JOIN course c -> ON s.course_id = c.course_id -> WHERE name = 'WloNYD'; +-----------+--------+------------+-------------+ | course_id | name | student_id | course_name | +-----------+--------+------------+-------------+ | 10077 | WloNYD | 95666 | mTHDYg | | 10077 | WloNYD | 95666 | nZkayq | | 10085 | wLonyD | 98444 | pZdpsR | | 10077 | WloNYD | 95666 | JfydVs | +-----------+--------+------------+-------------+ 4 rows in set (0.00 sec)
7、使用列的类型小的创建索引
8、使用字符串前缀创建索引
创建一张商户表,因为地址字段比较长,在地址字段上建立前缀索引
create table shop(address varchar(120) not null); alter table shop add index(address(12));
问题是,截取多少呢?截取得多了,达不到节省索引存储空间的目的;截取得少了,重复内容太多,字段的散列度(选择性)会降低。 怎么计算不同的长度的选择性呢?
先看一下字段在全部数据中的选择度:
select count(distinct address) / count(*) from shop;
通过不同长度去计算,与全表的选择性对比:
公式:
count(distinct left(列名, 索引长度))/count(*)
例如:
select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度 count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度 count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度 count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度 from shop;
🎯 拓展:Alibaba《Java开发手册》
【 强制 】在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度。
说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会 高达90% 以上 ,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
9、区分度高(散列性高)的列适合作为索引
10、使用最频繁的列放到联合索引的左侧
这样也可以较少的建立一些索引。同时,由于"最左前缀原则",可以增加联合索引的使用率。
11、在多个字段都要创建索引的情况下,联合索引优于单值索引
索引建立的多,维护的成本也高。
多个字段进行联合查询时,其实只使用到一个索引。如下,只用到了idx_sid索引
mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;
- 在建立联合索引的相关字段做查询时,联合索引都能生效,使用频率比较高。足够优化sql执行的速度了
3.3 限制索引的数目
在实际工作中,我们也需要注意平衡,索引的数目不是越多越好。我们需要限制每张表上的索引数量,建议单张表索引数量不超过6个。原因:
①每个索索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
②索引会影响INSERT、DELETE、 UPDATE等语句的性能,因为表中的数据更改的同时,索引也会进行调整和更新,会造成负担。
③优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,会增加MySQL优化器生成执行计划时间,降低查询性能。
解释:表中创建的索引过多,优化器在possible_keys中选择合适的key 时需要的成本也会更多。比如下面查询中possible_keys有两个,实际使用的key只有一个,这其实优化器判断的哟。
mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100; +----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+ | 1 | SIMPLE | student_info | NULL | index | idx_sid,idx_cre_time_sid | idx_sid | 4 | NULL | 997449 | 100.00 | Using temporary; Using filesort | +----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
3.4 哪些情况不适合创建索引
1、在 where 中使用不到的字段,不要设置索引
2、数据量小的表最好不要使用索引
结论:在数据表中的数据行数比较少的情况下,比如不到 1000 行,是不需要创建索引的。
3、有大量重复数据的列上不要建立索引
4、避免对经常更新的表创建过多的索引
5、不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为 ASCII,并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
6、删除不再使用或者很少使用的索引
7、不要定义冗余或重复的索引
冗余索引
举例:建表语句如下
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 既是主键、又给它定义为一个唯一索引,还给它定义了一个普通索引,可是主键本身就会生成聚簇索引,所以定义的唯一索引和普通索引是重复的,这种情况要避免。
3.5 小结