mysql 联合索引结构与索引匹配原则
最左前缀匹配原则： 在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。
可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。
示例：
表： | 字段| 类型 | 描述 | | --- | --- | --- | |id | int(11) | 主键| |name | varchar(10) | 名称| |age | int(11) | 年龄|
该表中对id列.name列.age列建立了一个联合索引 id_name_age_index，实际上相当于建立了三个索引（id）（id_name）（id_name_age）。
下面介绍下可能会使用到该索引的几种情况：
- 全值匹配查询时
mysql> explain select * from staffs where name = 'shine' and sex = 1 and age = 20; +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_name_age_sex | idx_name_age_sex | 40 | const,const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where age = 20 and name = 'shine' and sex = 1; +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_name_age_sex | idx_name_age_sex | 40 | const,const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where sex = 1 and age = 20 and name = 'shine'; +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_name_age_sex | idx_name_age_sex | 40 | const,const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
通过观察上面的结果图可知，where后面的查询条件，不论是使用（name，age，sex）（age，name，sex）还是（sex，age，name）顺序，在查询时都使用到了联合索引，可能有同学会疑惑，为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配，却也使用到了联合索引？ 这是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划，所以不论以何种顺序都可使用到联合索引
- 匹配最左边列时
mysql> explain select * from staffs where name = 'shine'; +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | staffs | NULL | ref | idx_name_age_sex | idx_name_age_sex | 33 | const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
该搜索是遵循最左匹配原则的，通过key字段也可知，在搜索过程中使用到了联合索引，且使用的是联合索引中的（id）索引
mysql> explain select * from staffs where name = 'shine' and age = 20;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_name_age_sex | idx_name_age_sex | 38 | const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
由于name到age是从左边依次往右边匹配，这两个字段中的值都是有序的，所以也遵循最左匹配原则，通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（name_age）索引
mysql> explain select * from staffs where name = 'shine' and age = 20 and sex = 1;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_name_age_sex | idx_name_age_sex | 40 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
由于上面三个搜索都是从最左边name依次向右开始匹配的，所以都用到了name_age_sex联合索引。
那如果不是依次匹配呢？
mysql> mysql> explain select * from staffs where name = 'shine' and sex = 1;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_name_age_sex | idx_name_age_sex | 33 | const | 1 | 20.00 | Using where; Using index |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（name）索引,因为联合索引树是按照name字段创建的，但sex相对于name来说是无序的，只有name是有序的，所以他只能使用联合索引中的name索引。
mysql> explain select * from staffs where age = 20;
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | index | NULL | idx_name_age_sex | 40 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
通过观察发现上面key字段发现在搜索中也使用了name_age_sex索引，可能许多同学就会疑惑它并没有遵守最左匹配原则，按道理会索引失效，为什么也使用到了联合索引？因为没有从id开始匹配，且name单独来说是无序的，所以它确实不遵循最左匹配原则，然而从type字段可知，它虽然使用了联合索引，但是它是对整个索引树进行了扫描，正好匹配到该索引，与最左匹配原则无关，一般只要是某联合索引的一部分，但又不遵循最左匹配原则时，都可能会采用index类型的方式扫描，但它的效率远不如最做匹配原则的查询效率高，index类型类型的扫描方式是从索引第一个字段一个一个的查找，直到找到符合的某个索引，与all不同的是，index是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描。
mysql> explain select * from staffs where sex = 1;
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | index | NULL | idx_name_age_sex | 40 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from staffs where age = 20 and sex = 1;
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | index | NULL | idx_name_age_sex | 40 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
这两个结果跟上面的是同样的道理，由于它们都没有从最左边开始匹配，所以没有用到联合索引，使用的都是index全索引扫描。
- 匹配列前缀
对于模糊匹配的查询，如果是前缀匹配用的是索引，中坠和后缀用的是全表扫描
mysql> explain select * from staffs where name like "shie%";
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | range | idx_name_age_sex | idx_name_age_sex | 33 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from staffs where name like "%ara%";
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | index | NULL | idx_name_age_sex | 40 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from staffs where name like "%ara";
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | index | NULL | idx_name_age_sex | 40 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql对于索引优先考虑的对象
一般我们在开发项目时对于一些业务难免会写出一些条件+分组/排序的sql语句，而通常也是这些sql会导致mysql的性能变差，这个时候我们想到使用mysql的索引来进行优化，但是mysql的索引对于条件，分组，排序都存在的情况下是如何去选择索引的呢？
下面我们根据上面的问题来进行一些测试以及分析。
条件与分组排序共存的情况下
mysql> explain select sex,age from staffs where name = "shine" group by sex order by age;
+----+-------------+--------+------------+------+--------------------------+------------------+---------+-------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+--------------------------+------------------+---------+-------+------+----------+-----------------------------------------------------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_name_age_sex,idx_sex | idx_name_age_sex | 33 | const | 1 | 100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+------------+------+--------------------------+------------------+---------+-------+------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
当sql中where条件，分组，排序同时存在时，MySQL的优化器会优先选择条件来确定使用的索引，因为where可以减少更多的sql扫描，而排序和分组往往进行的是全表扫描。
### 条件与排序共存
mysql> explain select sex,age from staffs where name = "shine" order by age;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_name_age_sex | idx_name_age_sex | 33 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
原因：所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。
分组排序共存
mysql> explain select sex,age from staffs group by sex order by age;
+----+-------------+--------+------------+-------+--------------------------+---------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+--------------------------+---------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | staffs | NULL | index | idx_name_age_sex,idx_sex | idx_sex | 2 | NULL | 5 | 100.00 | Using temporary; Using filesort |
+----+-------------+--------+------------+-------+--------------------------+---------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
对于分组和排序共存的情况下，mysql会优先根据分组去选择索引，那是因为sql需要先将要查询的数据进行分组，随后才会进行数据的排序。
mysql索引的挑选原则
注：字段一般是推荐重复比较少的字段影响到数据的检索，如果是项目需求（可建立联合索引）
- 唯一字段可以单独建立单索引，非唯一考虑联合索引,推荐尽量使用唯一字段建立索引
- 索引的个数，联合索引的个数 最佳 6个 以内，如果索引因为项目需求：最多 10个
- 索引的使用遵循最左匹配原则其次覆盖索引
- 尽量选择小的字段建立索引 int ，varchar(10)， char(5)
- 避免<，<= ，> ,>= , % ,between 之前的条件。选择索引的字段的范围和模糊之前，因为范围与模糊会引起索引失效，针对于联合索引，就是联合索引的中间尽量不要有范围查询的字段
- 尽量多使用explain分析
- 避免更新频繁的字段 （二叉树会一直变化，导致性能变慢）
- 建立的索引- 优先考虑 建立 联合索引
- 索引字段不要有 null， 不是 ‘’
Mysql查询正在运行的事务以及杀掉它
-
查询 正在执行的事务：
SELECT * FROM information_schema.INNODB_TRX
根据这个事务的线程ID（trx_mysql_thread_id）:可以使用mysql命令：kill 线程id 杀掉线程