什么是索引？

索引：高效获取数据的数据结构，针对某一字段建立索引，可以提高查询效率，减低数据排序成本，但同时也消耗磁盘空间，降低更新表的速度

InnoDB索引数据结构

1. 优化B+树：
   • 所有的数据都会出现在叶子节点
   • 叶子节点形成一个双向链表，利于排序，提高访问性能（B+树为单向链表）
   • 非叶子节点仅仅起到索引数据作用，具体的数据都在叶子节点存放

2. Hash索引：采用一定的hash算法，将键值换算成hash值，映射到对应的槽位上，存储在 hash表中
   • Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
   • 无法利用索引完成排序操作
   • 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引
3. 在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的
4. 为什么InnoDB存储引擎选择使用B+tree索引结构?
   • 相对于二叉树，层级更少，搜索效率高
   • B-tree和B+tree根据页的大小决定存储多少节点，对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
   • 相对Hash索引，B+tree支持范围匹配及排序操作

索引分类

1. 按存储形式分：

• 聚集索引：必须有,而且只有一个，叶子节点下挂的是这一行的数据

​ 聚集索引选取规则:

​ - 如果存在主键，主键索引就是聚集索引

​ - 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引

​ - 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

• 二级索引：叶子节点下挂的是该字段值对应的主键值

由于查询的是*，二级索引只有name和id信息，所有会根据二级索引查到的id回表查询聚集索引，得到想要的信息。

2. 按所用字段分：

• 单列索引：一个字段建立的索引

1

create index idx_user_pro on tb_user(profession);

• 联合索引：多个字段联合建立的索引

1

create index idx_user_pro on tb_user(profession,age...);

• 覆盖索引：所用字段建立的索引满足所要查询的字段，不用回标查询的索引

• 前缀索引：以某个字段部分内容建立的索引（适合字符串类型且内容较长，如文章）

1

create index idx_xxxx on table_name(column(n)) ;

索引语法

1. 创建索引：

   1	CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

2. 查看索引

   1	SHOW INDEX FROM table_name ;

3. 删除所有

   1	Drop Index index_name ON table_name ;

4. SQL提示

   1 2 3 4 5 6

   1)use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进 行评估）。 2)ignore index ： 忽略指定的索引。 3)force index ： 强制使用索引。 例：select * from tb_user use index(idx_user_pro) where profession = '软件工 程';

SQL性能分析工具

1. 查看数据表操作访问频次

   1	SHOW GLOBAL(或者Session) STATUS LIKE 'Com_______';

2. 开启慢查询日志

   MySQL的配置文件中配置如下信息：

   1 2 3 4

   # 开启MySQL慢日志查询开关 slow_query_log=1 # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 long_query_time=2

   慢查询日志存储文件：localhost-slow.log

3. profile详情

   1 2	-- 开启profile设置 SET profiling = 1;

   1 2 3 4 5 6

   -- 查看每一条SQL的耗时基本情况 show profiles; -- 查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; -- 查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id;

4. explain执行计划：直接在sql语句前加explain

索引失效原则

• 最左前缀法则：联合索引最左边的字段必须存在，不存在，则索引失效。最左列存在，中间跳过某一列，该列后面的字段索引将失效

• 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效（尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <）

• 在索引列上进行运算操作， 索引将失效

• 字符串类型字段使用时，不加引号，索引将失效

• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

• 用or分割开的条件，左右两侧字段都有索引时，索引才会生效

• 数据分布情况：如果MySQL评估使用索引比全表更慢，则不使用索引

索引设计原则

• 针对于数据量较大，且查询比较频繁的表建立索引
• 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增 删改的效率
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询

其他优化

1. 插入数据优化

   • 使用批量插入数据

   • 手动控制事务（begin、commit）

   • 主键顺序插入（不顺序插入可能导致页分裂，删除时可能出现页合并）

   • 大数据量插入使用load指令进行插入

     1 2 3 4 5 6 7

     -- 客户端连接服务端时，加上参数 -–local-infile mysql –-local-infile -u root -p -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 set global local_infile = 1; -- 执行load指令将准备好的数据，加载到表结构中 load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

2. 主键优化

   • 满足业务需求的情况下，尽量降低主键的长度
   • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
   • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
   • 业务操作时，避免对主键的修改

3. order by优化

   • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
   • 尽量使用覆盖索引
   • 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
   • 如果不可避免的出现filesort（文件缓冲区排序），大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)

4. group by优化：建立覆盖索引，遵循最左前缀法则

5. limit优化：进行limit分页查询，在查询时，越往后，分页查询效率越低（原因：mysql将分页前之前的所有数据进行排序，仅仅返回需要的数据。如执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大），可以通过覆盖索引加子查询形式进行优化

   1 2	select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

6. count优化：

   • 手动计数（借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了）
   • count(字段) < count(主键 id) < count(1) ≈ count( * )，所以尽量使用 count( * )

7. update优化：查询条件需要建立索引，有索引，行锁，无索引，表锁