MYSQL索引分类以及优化
MySQL索引分类以及查询优化
参考文章：
https://www.cnblogs.com/bypp/p/7755307.html
https://www.cnblogs.com/luyucheng/p/6289714.html
一、功能
1、索引的功能加速查找
2、MYSQL中的primary key，unique，联合唯一索引，除了加速查找，还有约束的功能。
二、索引分类
按功能分类：
1、普通索引index：加速查找
2、唯一索引
主键索引：primary key：加速查找+约束（不能为空且唯一）
唯一索引：unique:加速查找+约束（唯一）
3、联合索引（复合索引）
-primary key（id,name）:联合主键索引
-unique(id,name):联合唯一索引
-index（id,name):联合普通索引
4、全文索引 fulltext：用于搜索很长一篇文章的时候，效果最好。
5、空间索引spatial：了解就好，几乎不用
按物理实现方式分类：
聚簇索引：
非聚簇索引：
按作用字段个数分类：
单列索引、联合索引（多列索引）
三、索引的两大分类：
#我们可以在创建上述索引的时候，为其指定索引类型，分两类 hash类型的索引：查询单条快，范围查询慢 btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它） #不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引； NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引； Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
四、创建/删除索引语法
--创建表时创建索引
create table s1( id int not null primary key,#新增字段时可添加唯一主键和唯一索引，唯一索引在not null 后面加unique，id int not null unique。 name varchar(20), email varchar(30) #id int index #不可以这样加索引，因为index只是索引，没有约束一说， #primary key(id),#也可如此加索引。 #index(id)#也可如此加索引。 );
--创建表后创建索引
create index name on s1(name);添加普通索引 create unique age on s1(age); 添加唯一索引 alter table s1 add primary key(id);添加主键索引 create index name on s1(id,name);添加普通联合索引
--删除索引
drop index name on s1;删除普通索引 drop index age on s1;删除唯一索引和普通索引的删除一样，不用再index前面加unique来删，可直接删除 alter table s1 drop primary key;删除主键（添加时时通过alter添加的，删除时也用alter）
五、测试索引
1、准备
新建一张表：
CREATE TABLE `s1` ( `id` int(11) NOT NULL, `name` varchar(50) DEFAULT NULL, `gender` char(6) DEFAULT NULL, `email` varchar(50) DEFAULT NULL )
通过存储过程造上百万数据：
CREATE DEFINER=`root`@`%` PROCEDURE `testindex`()
BEGIN
declare i int default 1;
while i<=3000000 do
insert into s1 values(i,concat('tom',i),if(i%2=0,'男','女'),concat(i,'@qq.com'));
set i=i+1;
end while;
END
2、没有加索引测试查询
mysql> select id from s1 where id=123;
+-----+
| id |
+-----+
| 123 |
+-----+
1 row in set (2.07 sec)
3、加索引
#1. 一定是为搜索条件的字段创建索引，比如select * from t1 where age > 5;就需要为age加上索引 #2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，插入删除更新都很慢，只有查询快 比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。 建完以后，再查询就会很快了 #3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.M
#加索引后执行时间如下：
mysql> select id from s1 where id=123; +-----+ | id | +-----+ | 123 | +-----+ 1 row in set (0.00 sec)
六、正确使用索引
1、覆盖索引
#分析 select * from s1 where id=123; 该sql命中了索引，但未覆盖索引。 利用id=123到索引的数据结构中定位到该id在硬盘中的位置，或者说再数据表中的位置。 但是我们select的字段为*，除了id以外还需要其他字段，这就意味着，我们通过索引结构取到id还不够， 还需要利用该id再去找到该id所在行的其他字段值，这是需要时间的，很明显，如果我们只select id， 就减去了这份苦恼，如下 select id from s1 where id=123; 这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了id在硬盘的地址，速度很快
2、联合索引（也叫复合索引）
(id,name,gender)的复合索引等于(id,name,gender)，(name,gender)、(gender)三个索引，这被称为最佳左前缀特性。
因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
组合索引可以命中： select * from s1 where name='egon' ; select * from s1 where name='egon' and email='adf'; 索引合并可以命中： select * from s1 where name='egon' ; select * from s1 where email='adf'; select * from s1 where name='egon' and email='adf';
组合查询用组合索引比较好，单条查询则不用建立组合索引。
七、添加索引注意事项
哪些列上适合添加索引
1、较频繁的作为查询条件字段应该创建索引
2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from test where sex = '男'
3、更新非常频繁的字段不适合创建索引
4、不会出现在WHERE子句中的字段不该创建索引
5、使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
索引无法命中的情况
- like '%xx'
mysql> EXPLAIN select * from s1 where id LIKE '%123';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2990846 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 使用函数
select * from tb1 where reverse(email) = 'wupeiqi';
- or
当or条件中有未建立索引的列才失效，当or对应列都有索引的情况是走索引的。
- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来,INT类型则不能加引号，不然...
mysql> select * from s1 where id='123';
+-----+--------+--------+------------+
| id | name | gender | email |
+-----+--------+--------+------------+
| 123 | tom123 | 女 | 123@qq.com |
+-----+--------+--------+------------+
1 row in set (0.01 sec)
mysql> select * from s1 where id=123;
+-----+--------+--------+------------+
| id | name | gender | email |
+-----+--------+--------+------------+
| 123 | tom123 | 女 | 123@qq.com |
+-----+--------+--------+------------+
1 row in set (0.00 sec)
普通索引的不等于不会走索引
- !=
select * from tb1 where email != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
- >
select * from tb1 where email > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
#排序条件为索引，则select字段必须也是索引字段，否则无法命中
- order by
select name from s1 order by email desc;
当根据索引排序时候，select查询的字段如果不是索引，则不走索引
select email from s1 order by email desc;
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
- count(1)或count(列)代替count(*)在mysql中没有差别了
- create index xxxx on tb(title(19)) #text类型，必须制定长度
- 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 - 使用连接（JOIN）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值（重复少）不适合建索引，例：性别不适合
1索引包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
.索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
5.不要在列上进行运算
这将导致索引失效而进行全表扫描，例如
SELECT * FROM table_name WHERE YEAR(create_date)<2017;
使用not in和<>操作
