mysql 多字段索引 顺序 数据库多字段索引

目录

• 1、索引：
• 2、索引的分类：
• 3、索引的创建和删除的SQL：

• ㈠索引的创建
• ㈡索引的删除

• 4、索引执行过程分析：
• 5、索引的底层原理
• 6、MySQL数据库的存储引擎MyISAM和InNoDB的索引结构

• MyISAM存储引擎（非聚集索引）
• InNoDB存储引擎（聚集索引）

• 7、联合索引
• 8、索引的优化

1、索引：

索引是一种提高查询效率的数据结构（B树或者是哈希结构）；索引是创建在数据库表中，是对数据库表中的一列或者多列的值进行排序的一个结果，好处就是提高查询效率；

一般情况下，一次查询只用一个索引；

2、索引的分类：

• 普通索引：没有任何限制，可以给任意字段创建普通索引；
• 唯一性索引：使用unique修饰的字段，值不能重复的，主键索引就属于唯一性索引；
• 主键索引：使用primary key修饰的字段自动创建主键索引；
• 单列索引：在一个字段上创建索引 ；
• 多列索引：在多个字段上创建索引；
• 全文索引：使用fulltext参数可以设置全文索引，只支持char\varchar\text类型的字段上，常用于数据量较大的字符串类型上；

3、索引的创建和删除的SQL：

㈠索引的创建

在创建表的时候指定索引

create table table_name(
           id int,
           name varchar(20),
           index(id)
       );

在已经创建好的表上添加索引

ALTER table tableName ADD INDEX indexName(columnName)
columnName添加索引的字段
indexName索引别名 可以任意命名

㈡索引的删除

drop index index_name(索引名) on 表名

4、索引执行过程分析：

使用explain关键分析查询SQL

explain select * from Student where Sname like 'zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
 
possible_keys:表示SQL执行可能会命中的索引有哪些;
 key:表示执行过程真正使用的索引名称
 rows:表示查询影响的数据行数
 当前查询possible_keys\key都为null，则未命中索引
  rows=4表示当前查询操作对每一行数据都进行比较
  
 添加索引后，分析执行过程
 explain select * from Student where Sname like 'zhaolei'\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: Student
          type: range
 possible_keys: idx_name
           key: idx_name
       key_len: 27
           ref: NULL
          rows: 1
         Extra: Using index condition
 1 row in set (0.01 sec)
通过explain关键分析查询SQL，可以看出当前查询命中索引idx_name
， rows: 1表示通过一条记录就查询到了结果

5、索引的底层原理

mysql支持两种索引，一种是B树索引，一种是哈希表索引；

问：数据库中常见的慢查询的优化方式是什么？
答：加索引；
问：问什么加索引可以优化查询？
答：因为能减少磁盘IO;
问：怎么减少磁盘IO的？
答：索引是一种优化查询的数据结构，比如在MySQL中用到的B+树，这种数据结构是可以优化查询的，所以我们可以利用索引来快速的查找数据；
问：那你知道哪些数据结构可以提高查询速度吗？
答：红黑树，二叉树，哈希表，B树（B-树），B+树、B*树等；
问：那为什么MySQL使用B+树呢？
答：如下：

二叉搜索树在某些情况下可能会退化成链表，AVL树的旋转太多，红黑树主要是磁盘I/O问题，不适合磁盘存储的数据，也不适合存储大量的数据，因为I/O浪费太大，读取的资源浪费也大。（之所以hashmap可以用，是因为hashmap存储的数据是在内存里面的）；

MYSQL InnoDB存储引擎，基于B-树(实际MYSQL采用的是B+树)的索引结构。B-树是一种m阶平衡树，叶子节点都在同一层，由于每一个节点存储的数据量比较大，所以整个B-树的层数是非常低的，基本上不超过三层；

由于磁盘的读取也是按block块操作的（内存是按page页面操作的），因此B-树的节点大小一般设置为和磁盘块大小一致，这样一个B-树节点，就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来，所以当使用B-树存储索引的时候，磁盘I/O的操作次数是最少的（MySQL的读写效率，主要集中在磁盘I/O上）。

那么MySQL最终为什么要采用B+树存储索引结构呢，那么看看B-树和B+树在存储结构上有什么不同？

• B-树的每一个节点，存了关键字和对应的数据地址，而B+树的非叶子节点只存关键字，不存数据地址。因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的，B+树的叶子节点存放关键字和数据，因此，从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，因此查询会更快一些。
• B-树由于每个节点都存储关键字和数据，因此离根节点近的数据，查询的就快，离根节点远的数据，查询的就慢；B+树所有的数据都存在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。
• 在B-树上如果做区间查找，遍历的节点是非常多的；B+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。

哈希索引当然是由哈希表实现的，哈希表对数据并不排序，因此不适合做区间查找，效率非常低，需要搜索整个哈希表结构。

6、MySQL数据库的存储引擎MyISAM和InNoDB的索引结构

聚集索引和非聚集索引：聚集就是索引和数据存放在一个文件里面，非聚集索引就是索引和数据分别存放在两个文件里面；

MyISAM存储引擎（非聚集索引）

MyISAM引擎使用B+树作为索引结构、叶节点的数据域存放的是数据地址，在MyISAM引擎中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，如果给其他字段创建辅助索引，结构图如下：

根据上面两张图，首先按照B+树搜索算法搜索索引，如果指定的key存在，则取出其数据域的值，然后以数据域的值为地址，读取相应的数据记录；

InNoDB存储引擎（聚集索引）

InNoDB存储引擎的主键索引，叶子节点中，索引关键字和数据是在一起存放的，如图：

可以看到，索引关键字和数据存储在叶子节点中；InNoDB辅助索引，叶子节点存放的是索引关键字和对应的主键（为了一致性和节省存储空间）：

辅助索引的B+树，先根据关键字找到对应的主键，再去主键索引树上找到对应的行记录数据，从索引树上可以看到，InNoDB的索引关键字和数据都是在一起存放的，体现在磁盘存储上，例如创建一个user表，在磁盘上只存储两种结构，user.frm（存储表的结构），user.idb（存储索引和数据）；

7、联合索引

就是先根据第一个键排序，第一个键相同的话，按第二个键排序……

8、索引的优化

索引的优化，主要就是分析索引在哪些情况下会失效的问题：

1. 在where后使用or，导致索引失效（尽量少用or）；
2. 使用like ，like查询是以%开头；
3. 复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用；
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；
5. 使用in导致索引失效；
6. DATE_FORMAT()格式化时间，格式化后的时间再去比较，可能会导致索引失效；
7. 对于order by、group by 、 union、 distinc 中的字段出现在where条件中时，才会利用索引！

创建一个students表：
其中stud_id为主键！

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `stud_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(1) NOT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`stud_id`)
 
)

INSERT INTO `learn_mybatis`.`students` (`stud_id`, `name`, `email`, `phone`, `create_date`)
 VALUES ('1', 'admin', '[e-mail]', '18729902095', '1983-06-25');
INSERT INTO `learn_mybatis`.`students` (`stud_id`, `name`, `email`, `phone`, `create_date`) 
VALUES ('2', 'root', '[e-mail]', '2', '1983-12-25');
INSERT INTO `learn_mybatis`.`students` (`stud_id`, `name`, `email`, `phone`, `create_date`) 
VALUES ('3', '110', '[e-mail]', '3dsad', '2017-04-28');

1. 在where后使用or，导致索引失效（尽量少用or）

简单实例演示：
创建两个普通索引，

使用下面的查询sql：

# 使用了索引
EXPLAIN select * from students where stud_id='1'  or phone='18729902095'
# 使用了索引
EXPLAIN select * from students where stud_id='1'  or email='[e-mail]'

#--------------------------

# 没有使用索引
EXPLAIN select * from students where phone='18729902095' or email='[e-mail]'

# 没有使用索引
EXPLAIN select * from students where stud_id='1'  or phone='222' or email='[e-mail]'

2.使用like ，like查询是以%开头

在1的基础上，还是使用 index_name_email 索引。

使用下面查询sql：

# 使用了index_name_email索引
EXPLAIN select * from students where email like '[e-mail]%'

# 没有使用index_name_email索引，索引失效
EXPLAIN select * from students where email like '[e-mail]'

# 没有使用index_name_email索引，索引失效
EXPLAIN select * from students where email like '[e-mail]%'

3.复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用;

删除1的基础创建的 index_name_email 和 index_name_phone 索引。

重新创建一个复合索引：

create index index_email_phone on students(email,phone);

使用下面查询sql

# 使用了 index_email_phone 索引
EXPLAIN select * from students where email='[e-mail]' and  phone='18729902095'

# 使用了 index_email_phone 索引
EXPLAIN select * from students where phone='18729902095' and  email='[e-mail]'

# 使用了 index_email_phone 索引
EXPLAIN select * from students where email='[e-mail]' and name='admin'

# 没有使用index_email_phone索引，复合索引失效
EXPLAIN select * from students where phone='18729902095' and name='admin'

4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
给name创建一个索引！

CREATE INDEX index_name ON students(name);

# 使用索引
EXPLAIN select * from students where name='110'

# 没有使用索引
EXPLAIN select * from students where name=110

5. 使用in导致索引失效

# 使用索引
EXPLAIN select * from students where name='admin'

# 没有使用索引
EXPLAIN SELECT * from students where name in ('admin')

6. DATE_FORMAT()格式化时间，格式化后的时间再去比较，可能会导致索引失效。
删除 students 上的创建的索引！重新在create_date创建一个索引！

CREATE INDEX index_create_date ON students(create_date);

# 使用索引
EXPLAIN SELECT * from students where create_date >= '2010-05-05'

# 没有使用索引
EXPLAIN SELECT * from students where DATE_FORMAT(create_date,'%Y-%m-%d') >= '2010-05-05'

于是，总结下索引的优化：

• 最左前缀匹配原则；
• 主键外键一定要建索引；
• 对 where,on,group by,order by 中出现的列使用索引；
• 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0；
• 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键；
• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
• 为较长的字符串使用前缀索引；
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可；
• 不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建；
• 对于like查询，”%”不要放在前面。

SELECT * FROMhoudunwangWHEREunameLIKE'后盾%' -- 走索引 
SELECT * FROMhoudunwangWHEREunameLIKE "%后盾%" -- 不走索引

• 查询where条件数据类型不匹配也无法使用索引
  字符串与数字比较不使用索引;

CREATE TABLEa(achar(10)); 
EXPLAIN SELECT * FROMaWHEREa="1" – 走索引 
EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引

正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因；