MySQl高级——索引

MySQL框架介绍：MySQL是一种关系型数据库，（分层可拔插）：

1. .连接层
2. 服务层
3. 引擎层
4. 存储层
   
   这里的MySQl部署在Linux上，主流的安装方法有二进制源码安装、RPM安装两种。（网上有很多安装教程，这里就不一一赘述了。）

MySQL的数据库：（MySQL中创建的数据库）

MySQL的配置文件：my-huge.cnf（与服务器调优相关）
修改字符集：把mysql的默认字符编码改为utf-8（网上搜教程）

MySQL的配置文件：

5. .二进制文件（log-bin：主从复制）
6. 错误日志（log-error：默认为关闭状态）
7. 查询日志（log：可用来进行慢查询）
8. 数据文件：两系统 1): windows
   2):Linux
   frm文件：存放表结构
   myd文件：存放表数据
   myi文件：存放表索引

MySQl的存储引擎：

MySql支持多种数据存储引擎，MySQl插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。
1.查看：
MySQl已经提供的存储引擎：show engines ;
查看当前默认的存储引擎：show variables like “ % storage_engine%”；

MySQl的索引优化：

sql性能下降原因：性能下降、SQl慢执行、等待时间长

• 查询语句写的烂
• 索引失效
• 关联太多join*（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲和线程池等）

SQl JOIN:

什么是索引（索引分为单值索引和符合索引）：索引是帮助MySQl高效获取数据的数据结构，可以理解为“排好序的快速查找数据结构”，索引用于排序和快速查找，在数据之外，数据库还维护着满足特定查找算法的数据结构。
一般而言，索引本身也很大，不可能全部存储在内存中，由此索引往往以索引文件的形式存储在磁盘上。

索引优势与劣势：

• 优势：类似大学图书馆的书目索引，能够提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低了数据排序的成本，降低了CPU的消耗。
• 劣势：首先，索引也相当于一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。其次索引虽然大大提高了查询速度，但同时，也降低了更新表的速度，比如进行insert、update、和delete，因为在更新表时，MySQl不仅要保存数据，还要保存一下索引文件，没次更新了索引列的字段。最后，索引只是提高效率的一个因素，如果MySQl中存在大数据量的表，就需要花时间建立最优秀的索引，或优化查询。

索引分类及创建索引的命令（通常情况下，一张表的索引最好不超过5个）：

索引分类：

• 单值索引：即一个索引只包含单个列，一张表可以有多个单值索引
• 唯一缩阴：索引列的值必须唯一，但允许有空值
• 复合索引：即一个索引包含多个列
  索引结构域原理：（数据库的衡量单位是段、区、块）
• hash索引
• full-text索引
• R_tree索引
• B+tree索引
  哪些情况下适合建索引：
• 主键自动建立唯一索引
• 频繁作为查询条件的字段应建立索引
• 查询中与其他表关联的字段，外键关系建立索引
• 频繁更新的字段不适合创建索引，因为每次的更新不但要更新记录，也会更新索引
• where条件里用不到的字段不创建索引
• 单键/组合索引的选择问题，who？（在高并发下倾向于创建组合索引）
• 查询中统计或分组字段
  哪些情况下不适合创建索引：
• 表记录太少（MySQl300万条记录后性能开始下降）
• 经常增删改的表
• 数据重复且分布平均的表字段
• 因此应该只为最经常查询和最经常排序的数据列建立索引，（若某个数据列包含许多重复内容，为其建立索引就没有太大的实际效果）

创建索引的语句：

• 单值索引：create index idx_user_name on user(name)在user表中的name列上创建一个叫做idx_user_name的单值索引
• 复合索引：create index idx_user_nameEmail on user(name,Email)在user表中创建包含name列和Email列的叫做idx_user_nameEmail的复合索引

索引优化：

索引失效原因：

• 全值匹配我最爱
• 最佳左前缀原则
• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换）否则会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致），减少select *的使用）
• MySQl在使用不等于（！=/<>）时无法使用索引，会导致全表扫描
• is null或 is not null 也无法使用索引
• like以通配符开头（‘%abc…’）MySQl索引会失效，变成全表扫描的操作、解决“%字符串%”时索引不被引用的方法：覆盖索引
• 字符串不叫单引号会导致索引失效
• 少用or，用它来连接时也会导致索引失效
• 遵循小表驱动大表的原则：即小的数据集驱动大的数据集