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 小结
