介绍mysql索引的原理以及优化建议
索引的本质
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。 目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。
索引的分类如下
- 主键索引（Primary Key Index）：
主键索引是用于唯一标识每一行数据的索引，确保每个主键值都是唯一的。 在 InnoDB 存储引擎中，主键索引同时也是数据的物理存储顺序，被称为聚集索引。
- 唯一索引（Unique Index）：
唯一索引确保索引列中的值是唯一的，但可以包含 NULL 值。 一个表可以有多个唯一索引。
- 普通索引（Normal Index）：
普通索引也被称为非聚集索引。 它是最基本的索引类型，用于加速查询。
- 全文索引（Full-Text Index）：
全文索引用于在文本列（如 VARCHAR 或 TEXT 类型）上进行全文搜索。 它支持模糊匹配和自然语言搜索。
- 空间索引（Spatial Index）：
空间索引用于优化空间数据类型（如 GEOMETRY、POINT、LINESTRING）的查询。 它可以加速地理位置数据的空间查询。
优化建议
1.like语句的前导模糊查询不能使用索引
select * from student where name like "%zhang"; --不能使用索引
select * from student where name like "zhang%";--非前导模糊查询，可以使用索引
2.union、in、or 都能够命中索引，建议使用 in
3.负向条件查询不能使用索引
负向条件有：!=、<>、not in、not exists、not like 等。
4.联合索引最左前缀原则
假如titles表的主索引为<emp_no, title, from_date>
情况一：全列匹配。
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
| 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
情况二：最左前缀匹配。
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
当查询条件精确匹配索引的左边连续一个或几个列时，如
情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no, from_date>
情况四：查询条件没有指定索引第一列。
EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
情况五：匹配某列的前缀字符串。
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 56 | NULL | 1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀
情况六：范围查询。
EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
5.不能使用索引中范围条件右边的列（范围列可以用到索引），范围列之后列的索引全失效
范围条件有：<、<=、>、>=、between等。
假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。
select * from titles where emp_no < 10010 and title='Senior Engineer' and from_date between '1986-01-01' and '1986-12-31'
6.不要在索引列上面做任何操作（计算、函数），否则会导致索引失效而转向全表扫描
例如下面的 SQL 语句，即使 date 上建立了索引，也会全表扫描：
select * from student where YEAR(create_time) <= '2023'
7.强制类型转换会全表扫描
字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。
如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。
select * from sys_user where phone=13612346678
可以优化为：
select * from sys_user where phone='13612346678'
8.更新十分频繁、数据区分度不高的列不宜建立索引
更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。
“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。
一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算
9.利用覆盖索引来进行查询操作，避免回表，减少select * 的使用
覆盖索引：查询的列和所建立的索引的列个数相同，字段相同。
例如登录业务需求，SQL语句如下。
select uid, login_time from sys_user where login_name=? and passwd=?
可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。
10.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not null 约束以及默认值。
11.is null, is not null无法使用索引