3.2.4 UPDATE、DELETE 的 WHERE 条件列
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。
如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
mysql> UPDATE student_info SET student_id = 10002 -> WHERE NAME = '462eed7ac6e791292a79'; Query OK, 0 rows affected (1.51 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> ALTER TABLE student_info -> ADD INDEX idx_name(NAME); Query OK, 0 rows affected (4.87 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> UPDATE student_info SET student_id = 10003 -> WHERE NAME = '462eed7ac6e791292a79'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
3.2.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`; • 1
运行结果（600637 条记录，运行时间 0.010s ）：
你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照 递增的顺序 进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
3.2.6 多表 JOIN 连接操作的WHERE条件
多表 JOIN 连接操作时， 连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快(n ,n^2 , n^3…)，严重影响查询的效率。
多表 JOIN 连接操作时， 推荐对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
🔊注意：对用于连接的字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。否则在查询时，虽然也会帮我们进行隐式的类型转换，转换时会使用函数，索引就失效了。索引失效情况在后续文章中还会给大家详细介绍，敬请期待。
举个例子，如果我们只对student_id创建索引，执行 SQL 语句：
SELECT course_id, name, student_info.student_id, course_name FROM student_info JOIN course ON student_info.course_id = course.course_id WHERE name = '462eed7ac6e791292a79';
运行结果（1 条数据，运行时间 0.189s ）：
这里我们对 name 创建索引，再执行上面的 SQL 语句，运行时间为 0.002s。
3.2.7 优先使用类型小的列创建索引
这里的类型指的是我们在建表时显示给表中字段指定的类型，比如TINYINT,INT,BIGINT,能够存储整型数据的字节依次递增，类型依次变大。但是其在存储空间中占用的空间也会依次变多，另外也会影响到索引的使用。比如主键会建立聚簇索引，如果主键越短，目录页能够存储的目录项记录就会越多，B+树就会越扁平，查询时I/O次数就会越少，查询的速度就会越快。另外，即使是二级索引，其非叶子节点还存放主键。因此，这条建议对于表的主键特别适用。
3.2.8使用字符串前缀创建索引
假设一个字段是字符串，字符串很长，我们考虑使用字符串前缀创建索引，而不是整个字符串哟。这样不仅构建索引的B+树索引存储空间更大，存储的内容会更少点，使B+树更加矮胖，同时，在查询时对该字段进行比较效率也会更高点。
这里大家可能有个问题，如果说字符串截取了一段做索引，但是两个不同字符串截取的前缀相同，那么他们比较的结果不就相同了么？不用担心，如果相同，我们还在索引中存储了主键呀，我们再根据主键去回表，就可以知道到底哪个数据是我们需要的了。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引
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(*) • 1
例如
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;
计算出来选择度越接近于1越好哟，因为这说明它重复的数值越少。
🎯 拓展：Alibaba《Java开发手册》
【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
引申另一个问题：索引列前缀对排序的影响
比如我们索引就取了address字段的前12个字符，下面的查询就有点尴尬了
SELECT * FROM shop ORDER BY address LIMIT 12;
因为索引不包含完整的字符，因此无法对于address中前12个字符相同但是后面字符不同的数据进行索引前缀排序了，只能使用文件排序。
3.2.9 区分度高(散列性高)的列适合作为索引
使用如下公式可以计算列的区分度，一般区分度越高越好，当区分度达到33%就算是比较高效的索引了
select count(distinct address) / count(*) from t;
数据相似性大的就不适合建立索引，如：男女性别
3.2.10 使用最频繁的列放到联合索引的左侧
这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。
3.2.11 在多个字段都要创建索引的情况下，联合索引优于单值索引
索引建立的多，维护的成本也高。
多个字段进行联合查询时，其实只使用到一个索引。比如
mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;
- 在建立联合索引的相关字段做查询时，联合索引都能生效，使用频率比较高。足够优化sql执行的速度了。
4 限制索引的数目
建议单张表的索引数目不要超过6个
- 索引越多，占用的磁盘空间越大
- 索引会影响数据的增删改的性能
- 表中创建的索引过多，优化器在
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)
5.不适合创建索引的7种情况
5.1 在where中使用不到的字段，不要设置索引
5.2 数据量小的表最好不要使用索引
在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。不仅浪费存储空间。而且在查找时性能可能还会更慢，因为可能还需要回表操作，不如直接查找就行。
5.3 有大量重复数据的列上不要建立索引
如，男女性别，当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引。
但也不是绝对的哟，比如一个学校有10万人，但是男生只有10人，如果想要筛选出这10个男生，那你就可以对性别这个字段建立索引哟。
5.4 避免对经常更新的表创建过多的索引
第一层含义：对于频繁更新的字段不需要创建索引。否则每次更新字段索引都需要对应维护。
第二层含义：对于经常更新的表，不要创建过多的索引，也是因为维护成本。
5.5 不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。所以我们通常也推荐使用自增的主键。
5.6 删除不再使用或者很少使用的索引
5.7 不要定义冗余或重复的索引
(1)冗余索引
举例：建表语句如下
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 列的索引就算是一个 冗余索引 ，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。
(2)重复索引
另一种情况，我们可能会对某个列 重复建立索引，比方说这样
CREATE TABLE repeat_index_demo ( col1 INT PRIMARY KEY, col2 INT, UNIQUE uk_idx_c1 (col1), INDEX idx_c1 (col1)
col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。
