MySQL 中的索引管理

​ 在 MySQL 中，对索引的查看和删除操作是所有索引类型通用的。

6.1 普通索引

​ 这是最基本的索引，它没有任何限制 MyIASM 中默认的 BTREE 类型的索引，也是我们大多数情况下用到的索引。

6.1.1 创建索引

CREATE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD INDEX index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , INDEX index_name (title(5)))

6.1.2 查看索引

SHOW INDEX FROM [table_name]
SHOW KEYS FROM [table_name] # 只在 MySQL 中可以使用 keys 关键字。

6.1.3 删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

6.2 唯一索引

​ 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似

6.2.1 创建索引

CREATE UNIQUE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD UNIQUE index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY
KEY(id) , UNIQUE index_name (title(length)))

6.3 全文索引（FULLTEXT ）

​ MySQL 从 3.23.23 版开始支持全文索引和全文检索，FULLTEXT 索引仅可用于 MyISAM表 ；他们可以从 CHAR 、 VARCHAR 或 TEXT 列 中作为 CREATE TABLE 语句的一部分被创建，或是随后使用 ALTER TABLE 或 CREATE INDEX 被添加。对于较大的数据集，将你的资料输入一个没有 FULLTEXT 索引的表中，然后创建索引，其速度比把资料输入现有 FULLTEXT 索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

6.3.1 创建索引

CREATE FULLTEXT INDEX index_name ON table_name(column(length))
ALTER TABLE table_name ADD FULLTEXT index_name( column)
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY
KEY(id) , FULLTEXT index_name (title))

6.4 组合索引（ 最左前缀 ）

CREATE TABLE article(id int not null, title varchar(255), time date);

​ 平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。例如上表中针对 title 和 time 建立一个组合索引：ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
–title,time
–title
为什么没有 time 这样的组合索引呢？这是因为 MySQL 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，
如下面的几个 SQL 所示：
1，使用到上面的索引

SELECT * FROM article WHERE title='测试' AND time=1234567890;
SELECT * FROM article WHERE title='测试';

2，不使用上面的索引

SELECT * FROM article WHERE time=1234567890;

参考：https://segmentfault.com/a/1190000008131735#articleHeader5s

6.4.1 创建索引

CREATE INDEX index_name ON table_name (column_list)

7 MySQL 中的索引优化

​ 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE和 DELETE 次数大于查询次数时，放弃索引。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

7.1 索引不会包含有 NULL 值的列

​ 只要列中包含有 NULL 值都将不会被包含在索引中，组合索引中只要有一列含有 NULL值，那么这一列对于此组合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为 NULL。create table table_name(c1 varchar(32) default ‘0’)

7.2 使用短索引

​ 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个 CHAR(255)的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作。

CREATE INDEX index_name ON table_name (column(length))

7.3 索引列排序

​ MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

7.4like 语句操作

​ 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而 like “aaa%”可以使用索引。

7.5 不要在列上进行运算

​ 例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致 索 引 失 效 而 进 行 全 表 扫 描 ， 因 此 我 们 可 以 改 成 ： select * from users where adddate<’2007-01-01′

8 索引总结

​ 最后总结一下，MySQL 只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的 like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建 16 个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。建议：一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的
索引是否有必要。

9 MySQL 中的 SQL 的常见优化策略

9.1 避免全表扫描

​ 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

9.2 避免判断 null 值

​ 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：
select id from t where num=0

9.3 避免不等值判断

应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

9.4 避免使用 or 逻辑

​ 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

9.5 慎用 in 和 not in 逻辑

in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t1 where num in(select id from t2 where id > 10)
此时外层查询会全表扫描，不使用索引。可以修改为：
select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id
此时索引被使用，可以明显提升查询效率。

9.6 注意模糊查询

下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
模糊查询如果是必要条件时，可以使用 select id from t where name like 'abc%'来实现模糊查询，此时索引将被使用。如果头匹配是必要逻辑，建议使用全文搜索引擎（Elastic search、Lucene、Solr 等）。

9.7 避免查询条件中字段计算

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

9.8 避免查询条件中对字段进行函数操作

应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name 以 abc 开头的 id
应改为:
select id from t where name like 'abc%'

9.9WHERE 子句 “= ” 左边注意点

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

9.10 组合索引使用

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

9.11 不要定义无异议的查询

不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)

9.12exists

很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

9.13 索引也可能失效

并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段 sex，male、female 几乎各一半，那么即使在 sex 上建了索引也对查询效率起不了作用。

9.14 表格字段类型选择

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。尽可能的使用 varchar 代替 char ，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

9.15 查询语法中的字段

任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

9.16 索引无关优化

不使用*、尽量不使用 union，union all 等关键字、尽量不使用 or 关键字、尽量使用等值判断。
表连接建议不超过 5 个。如果超过 5 个，则考虑表格的设计。（互联网应用中）表连接方式使用外联优于内联。外连接有基础数据存在。如：A left join B,基础数据是 A。A inner join B，没有基础数据的，先使用笛卡尔积完成全连接，在根据连接条件得到内连接结果集。
大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。
Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)