MYSQL进阶(2)
删除索引:drop Index indexName on tableName;
B树叶子结点和非叶子节点都存在数据，那么当数据量很大的时候，把索引加载起来就需要很长时间
联合索引:
1)定义:是给一张表上面的多个列增加索引，也就是说给表上面的多个列增加索引，供快速查询使用，当两个列的组合是唯一值时，联合索引是个不错的选择。
用简单例子带你了解联合索引查询原理及生效规则 - 知乎说的很好对于联合索引
Mysql系列-联合索引 - 知乎
2)联合索引和单个索引对比来讲，联合索引的所有索引项都会出现在索引上，存储引擎会先根据第一个索引项排序，如果第一个索引项相同的话才会去看第二个，所有我们在查询的时候，如果头索引不带的话，联合索引就会失效，因为在根节点他就不知道怎么往下走
索引失效:
1)最左前缀法则:如果说我们使用了联合索引，那么要遵循最左前缀法则，最左前缀法则指的是从查询索引的最左列开始，并且不跳过索引中的列，如果说跳过某一列，那么直接会导致后面字段索引失效
1)注意:最左前缀法则指的是最左边的列，是在进行查询的时候，联合索引的最左边字段必须存在和我们前后所写的SQL顺序没有关系
2)就是当你比如说创建了一张表，这张表的所有字段都有索引，那么这个时候最左匹配不遵循，索引也不会失效，因为索引树里面已经包含了有关于查询的全部字段，此时就不需要进行回表查询了
3)况且如果说跳过了某一列，后面的索引字段全部失效
select * from child;//查询表里面所有字段 +----+----------+----------+---------+--------+ | id | username | password | classID | salary | +----+----------+----------+---------+--------+ | 1 | A | 12345 | 1 | 1234 | | 2 | B | 8989 | 2 | 89 | +----+----------+----------+---------+--------+ 2 rows in set (0.00 sec)mysql> create index AllIndex on child(username,password,classID);//针对表中的三列建立索引 Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> select * from child where username="A" and password="12345" and classID=1;//符合最左前缀法则,索引生效 +----+----------+----------+---------+--------+ | id | username | password | classID | salary | +----+----------+----------+---------+--------+ | 1 | A | 12345 | 1 | 1234 | +----+----------+----------+---------+--------+mysql> desc select * from child where username="A" and password="12345" and classID=1; //显示信息 +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | child | NULL | ref | AllIndex | AllIndex | 311 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+mysql> select * from child where password="12345" and classID=1; //不满足最左前缀法则,索引失效 +----+----------+----------+---------+--------+ | id | username | password | classID | salary | +----+----------+----------+---------+--------+ | 1 | A | 12345 | 1 | 1234 | +----+----------+----------+---------+--------+mysql> desc select * from child where password="12345" and classID=1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | child | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+desc select * from child where username="A" and classID=1; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | child | NULL | ref | AllIndex | AllIndex | 153 | const | 1 | 50.00 | Using index condition | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+2)在联合索引中，出现了范围查询，范围查询右侧的列索引失效，所以我们尽量少使用>,<而是使用>=和<=
3)在索引列上面进行运算操作，那么索引列会失效，也包括查询的列使用了函数
4)字符串类型的字段不加上引号，那么索引字段失效，或者说查询的列进行了隐式类型转换
5)如果是like尾部模糊查询，那么索引不会失效，但是如果头部模糊查询，那么索引会失效
6)or连接条件:如果说or前面的列有索引，但是后面的列没有索引，那么涉及到的索引都不会生效，只有当or两侧都有索引之后，索引才会生效
7)MYSQL评估如果说索引比全表扫描的更慢，那么直接会走全表扫描，索引也会失效
总结:
1)我们在实际进行开发的时候，避免使用select *，会特别容易出现回表查询，如果我们创建了联合索引，里面包含这个表中的所有字段，那么就会在这个联合索引里面就已经包含着所有我们需要进行查询的字段，就不需要再进行回表查询了，但是在一般情况下，联合索引不一定包含表中的所有字段，在我们进行查询联合索引的时候，发现着某一个字段在某一个联合索引中不存在，那么只能进行回表查询，在主键索引也就是聚簇索引中去查询完整的数据载荷了，所以我们要进行规避回表查询，性能就会降低
2)所以说在我们的执行计划里面:我们可以看到Extra字段，里面的值如果是Using where;Using index表示查找使用了索引，但是需要的数据都是可以在索引列中找到的，所以不需要进行回表查询
3)但是如果说Extra中出现了Using index condtion:表示使用了索引，但是需要进行回表查询数局
所以说使用联合索引检索效率比单列索引高，况且不容易出现回表查询
前缀索引:当我们需要针对字符串类型来进行创建索引的时候，索引的字符串就会变得很长很长，或者是打文本数据，查询时候会浪费大量的磁盘IO，影响查询效率，所以我们应该只将字符串的一部分前缀作为索引，这样可以大大节省索引空间
1)create index 索引名字 on 表名(哪一个列名(整数前缀))
2)咱们可以根据索引的选择性来进行确定，而选择性是根据不重复的索引值/数据库表中的记录总数的比值，索引选择性越高那么查询效率就越高，性能也是最好的
select count(*) from child;//先进行查询一共有多行 +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec)mysql> select count(distinct password) from child;//查询不重复密码有多少行 +--------------------------+ | count(distinct password) | +--------------------------+ | 2 | +--------------------------+ select count(distinct substring(password,1,3))/count(*) from child;//查询截取后的字段除以总数据长度的值是不是1,如果截取的字段出现了重复,那么值会小于1 +--------------------------------------------------+ | count(distinct substring(password,1,3))/count(*) | +--------------------------------------------------+ | 1.0000 | +--------------------------------------------------+create index passwordIndex on child(password(3));show index from child; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | child | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | child | 1 | AllIndex | 1 | username | A | 2 | NULL | NULL | YES | BTREE | | | | child | 1 | AllIndex | 2 | password | A | 2 | NULL | NULL | YES | BTREE | | | | child | 1 | AllIndex | 3 | classID | A | 2 | NULL | NULL | YES | BTREE | | | | child | 1 | passwordIndex | 1 | password | A | 2 | 3 | NULL | YES | BTREE | | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------最后一列中的Sub_Part就是描述在原来的字符串里面截取了几个字段
上面我们就创建了一个前缀索引:对前五个字符构建前缀索引，并没有针对这个字段建立完整的索引
1)当我们进行查询select * from "17799999"的话，会进行截取前5个字符，根据字符先后顺序，进行比较，也就是根据26个字母的先后顺序，查到主键id，再根据主键id在聚簇索引上面查询到完整的数据载荷了，再从数据载荷里面查询到email的值，和刚才的from后面的值进行对比，相同的话返回完整的记录
2)然后那个前缀索引的叶子结点的指针继续向后走，重复刚才的过程
单列索引和联合索引的对比:
create table User(userID int,username varchar(40),password(40)，classID int);
1)现在咱们在User表中针对username和password分别创建索引，也就是说现在有两个索引
先在进行操作select username,password from User where username=XXX and password=XXX
2)但是现在这样的查询效率是比较低的，但是最终MYSQL只会选择一个索引，所以还是要进行回表查询的
3)但是当我们针对这两个字段创建一个联合索引在继续执行上面的SQL语句，就会效率很高的
所以说在我们进行多条件联合查询的时候，就例如上面的例子，我们针对两个列分别建立了索引，又创建了联合索引，MYSQL就会进行自动评估哪一个字段的索引效率会更高，从而会进行选择合适的，效率高的索引来进行查询
覆盖索引:是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
总结:
1)索引可以分为:主键索引，唯一索引(如果说有某一个列的值不能重复)，常规索引，全文索引，在InnoDB存储引擎里面，索引存储结构分为聚簇索引和费聚簇索引
2)索引使用的设计原则:
1)我们要针对数据量比较大，况且查询比较频繁的列建立索引
2)针对常用作查询条件where order by group by操作的字段进行建立索引
3)尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率就越高
4)如果是针对字符串类型的字段，字段的长度比较长，我们可以针对字段的特点，建立前缀索引
5)尽量使用联合索引，减少单列索引，查询的时候，联合索引可以覆盖索引，节省存储空间，提高查询效率
6)要控制索引数量，索引不是多多益善，索引越多，维护索引结果的代价也会越大，会影响增删改速率
7)如果说索引列不能存储null值，请在建表的时候用not null来进行约束，当优化器知道每列是否包含null值的时候，他就更好的确定使用哪一个索引更有效的进行查询
