seq搜索引擎优化至少包括那几步?(MySQL中常见索引合并索引的分类及处理方法（一）)

　　seq搜索引擎优化至少包括那几步?(MySQL中常见索引合并索引的分类及处理方法（一）)

　　一、MySQL中常见的索引类型

　　普通索引：只加快查询

　　主键索引：加快查询，只有列值一、表中只有一个（不为null）

　　唯一索引：加快查询，列值唯一（可以有null）

　　组合索引：多列值组成一个索引，专门用于组合搜索，比索引合并效率更高

　　索引合并：使用多个单列索引组合搜索。

　　覆盖索引：select的数据列只能从索引中获取，不读取数据行；换句话说，查询列应该被构建的索引覆盖。

　　正常指数

　　--创建表并添加名称字段作为公共索引

　　创建平板电脑（

　　idint not null auto_increment 主键，

　　namechar(32) 不为空，indexidx_name(name)

　　);--为表单独指定一个公共索引

　　创建索引 idx_name ontb(name);--删除索引

　　drop index idx_name ontb;--查看索引

　　显示来自 tb 的索引；

　　查看代码

　　表#表的名称

　　Non_unique #0 如果索引是唯一索引，1 如果可以

　　key_name#索引的名称

　　seq_in_index #索引中的列序号，从1开始

　　Column_name#列名

　　Collat​​ion#列如何存储在索引中。在MySQL中，值有'A'（升序）或NULL（不排序）

　　Cardinality#估计索引中唯一值的个数

　　Sub_part# 如果该列仅被部分索引，则索引的字符数。如果整个列都被索引，则为 NULL

　　Packed# 表示关键字是如何打包的。如果未压缩，则为 NULL

　　如果列收录 NULL，则 Null# 收录 YES。如果不是，则该列收录 NO

　　Index_type#使用的索引方法（BTREE、FULLTEXT、HASH、RTREE）

　　评论#多条评论

　　查看索引 --> 栏目介绍

　　主键索引

　　主键有两个作用：加速查询和唯一约束（不能收录null）

　　注意：一张表最多只能有一个主键索引

　　--创建表并添加id字段作为主键索引--方法1

　　创建平板电脑（

　　idint not null auto_increment 主键，

　　namechar(4) not null);--方法二

　　创建平板电脑（

　　idint 不是 nullauto_increment，

　　namechar(4) 不为空，主键(id)

　　);--为现有表添加主键

　　alter table tb add primary key(id);--删除主键--方法1

　　alter table tb drop primary key;--方法2--如果当前主键是自增主键，不能直接删除，需要先修改自增属性，再删除

　　alter table tb modify id int, drop primary key;

　　查看代码

　　唯一索引

　　唯一索引有两个作用：加速查找和唯一约束（可以收录空值）

　　创建平板电脑（

　　idint not null auto_increment 主键，

　　namechar(4) 不为空，

　　ageint 不为空，唯一 indexidx_age（年龄）

　　);--在现有表上创建唯一索引

　　在 tb(age) 上创建唯一索引 idx_age；

　　查看代码

　　综合指数

　　复合索引是将n列组合成一个索引

　　应用场景：经常使用n列同时查询，如：select * from tb where and id=888；

　　创建平板电脑（

　　idint 不为空，

　　namechar(4) 不为空，

　　ageint 不为空，indexidx_name_age（姓名，年龄）

　　);--为现有表创建复合索引

　　在 tb(name,age) 上创建索引 idx_name_age；

　　查看代码

　　二、聚集索引和非聚集索引（辅助索引）

　　数据库中的B+tree索引可以分为：聚簇索引和非聚簇索引

　　聚集索引：innodb table/index-organized table，即表中的数据按照主键B+树存储，叶子节点直接存储整个数据，每张表只能有一个聚集索引。

　　① 定义主键时，innodb存储引擎将其视为聚集索引；

　　②如果没有定义主键，innodb定位到第一个唯一索引，并且该索引的所有列值都是空的，那么就认为是聚集索引；

　　③如果表没有主键或合适的唯一索引，innodb会生成一个row ID聚集索引，隐藏row ID值为6字节。

　　补充：由于实际的数据页只能按照一棵B+树排序，所以每张表只能有一个聚集索引，这对于主键排序和范围搜索非常有利。

　　非聚集索引（辅助索引）：叶子节点不收录行的所有数据。叶节点除了键值外，还收录一个书签连接，通过书签找到对应的行数据。

　　从innodb存储引擎的辅助索引中获取数据的搜索方式如下：

　　从上图可以看出，二级索引叶子节点存储的是主键值。获取主键值后，从聚集索引中查找整行数据。

　　例如，如果在高度为3的二级索引中搜索数据，首先从二级索引（3个IO）中获取主键值，然后从高度为3的聚集索引中搜索，获取整行数据（3 个 IO），总共需要 6 个 IO。一个表上可以存在多个二级索引。

　　聚集索引和二级索引的区别：

　　相同的是：无论是聚集索引还是辅助索引，其内部都是B+树的形式，即高度平衡，叶子节点存储所有数据。

　　不同的是，聚簇索引的叶子节点存放的是整行的信息，而辅助索引的叶子节点存放的是单个索引字段的信息。

　　何时使用聚集索引或非聚集索引（重要！！！）：

　　三、测试索引

　　1、创建表

　　创建表用户信息（

　　idint 不为空，

　　namevarchar(16) 默认为空，

　　年龄，

　　性别字符（1) 不为空，

　　emailvarchar(32) default null)engine=myisam default charset=utf8;

　　查看代码

　　注意：MYISAM存储引擎不生成引擎事务，数据插入速度极快。为了方便测试数据的快速插入，我们在插入数据后将存储类型改为InnoDB。

　　2、创建存储过程

　　创建过程 insert_userinfo(in num int)begin

　　declare i int default 0;declare n int default 1;--循环插入数据

　　而 n, >=,

　　--= 等号

　　从 id=1000 的用户信息中选择计数（*）；-- 执行索引，索引效率高

　　-->, >=,

　　从用户信息中选择计数（*），其中 id

　　从 id>100 的用户信息中选择 count(*)；--执行索引，区间范围越大，索引效率越低

　　从 userinfo 中选择 count(*)，其中 id 介于 10 和 500000 之间；--执行索引，区间范围越大，索引效率越低

　　--!= 不等于

　　从 id!=1000 的用户信息中选择 count(*)；--指标范围大，指标效率低

　　——比如“%xx%”

　　-- 为名称字段添加索引

　　create index idx_name onuserinfo(name); select count(*) from userinfo where name like "%xxxx%"; --全模糊查询，索引效率低

　　select count(*) from userinfo where name like "%xxxx"; -- 什么是模糊查询结束，索引效率低

　　--Exception: 当like使用what开头时，索引效率高

　　select * from userinfo where name like "xxxx%";--or

　　从 id=1000 或 email="xx" 的用户信息中选择 count(*)；--email 不是索引字段，索引是对该查询的全表扫描

　　--Exception: 当or条件中有未索引的列时，会失效，后面会被索引

　　从 id=1000 的用户信息中选择 count(*) 或；--当id和name都是索引字段时，or条件也会执行索引

　　-- 使用函数

　　从 userinfo 中选择 count(*) where reverse(name)="1knip"; --name 索引字段，函数使用时索引无效

　　--Exception: 索引字段对应的值可以使用函数，我们可以改成如下形式

　　select count(*) from userinfo where name=reverse("1knip");--类型不一致

　　--如果列是字符串类型，传入的条件必须用引号引起来

　　从用户信息中选择计数（*），其中名称=123； - 慢

　　-- 同类型

　　从 userinfo where 中选择 count(*)； - 快速地

　　--按顺序

　　--排序条件是索引，那么select字段也必须是索引字段，否则无法命中

　　按名称 desc 从 userinfo 中选择电子邮件；--无法命中索引

　　按名称 desc 从 userinfo 中选择名称；--命中指数

　　五、组合索引

　　组合索引：是指将表上的多个列组合成一个索引。

　　复合索引的好处有两个主要原因：

　　一个前三名；如果构建了(a, b, c)的组合索引，那么实际上相当于构建了三个索引(a), (a, b), (a, b, c)，因为每增加一个索引，都会增加写操作的开销和磁盘空间的开销。对于具有大量数据的表，这是一个很大的开销！

　　索引列越多，被索引过滤掉的数据就越少。1000W条数据的表有如下sql：select * from table where a=1 and b=2 and c=3，假设每个条件可以过滤掉10%的数据，如果只有单值的话index，然后通过index可以过滤掉1000W*10%=100w条数据，然后回表从100w条数据中找到匹配b=2和c=3的数据，然后排序分页; 如果是复合索引，通过索引*10%*10%*10%=1w过滤掉1000w，然后排序分页，效率更高，一目了然。

　　最左匹配原则：从左到右依次使用。如果不使用中间的索引，断点之前的索引部分会起作用，断点之后的索引将不起作用。

　　select * from tb where a=1 and b=2 and c=3;--abc 这三个索引都用在了where条件中，都起到了作用

　　select * from tb where c=3 and b=2 and a=1;--列出这条语句只是为了说明mysql没有那么笨，where中的条件顺序会在查询前被mysql自动优化，并且效果和上一句一样

　　select * from tb where a=1 and c=3;--a使用索引，b没用，所以c不使用索引效果

　　select * from tb where a=1 and b>2 and c=3;--a用，b也用，c不用，其中b是范围值，也是断点，但它是自身使用索引

　　select * from tb where a>1 and b=2 and c=3;--使用a，不使用b，不使用c

　　select * from tb where a=1 order byb;--a使用索引，b在结果排序中也使用索引的效果

　　select * from tb where a=1 order byc;--a使用索引，但是c没有起到排序的作用，因为中间断点

　　select * from tb where b=2 order bya;--b不使用索引，a在排序中不发挥索引作用

　　六、注意事项（重要）

　　1、避免使用 select *

　　2、在其他数据库中，使用count(1)或者count(column)代替count(*)，而在mysql数据库中，优化count(*)后，效率基本是和前两个一样

　　3、建表时尽量使用char而不是varchar

　　4、表字段顺序固定长度字段优先

　　5、复合索引而不是多个单列索引（当经常使用多个条件查询时）

　　6、使用连接（JOIN）而不是子查询（Sub-Queries）

　　7、表连接不超过 4 个（JOIN）

　　8、优先处理大量减少结果的连接

　　9、连接表时注意条件类型相同

　　10、索引hash值不适合索引，例如：性别不适合

　　七、慢查询日志

　　慢查询日志：将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件中，对这些特殊的SQL语句进行分析改进，以提高数据库性能。

　　慢查询日志参数：

　　long_query_time #设置慢查询阈值，超过设置值的SQL会记录在慢查询日志中，默认值为10s

　　slow_query_log #指定是否开启慢查询日志

　　log_slow_queries #指定是否开启慢查询日志（该参数已被slow_query_log代替，为了兼容性保留）

　　slow_query_log_file #指定慢日志文件的存放位置，可以为空，系统会给一个默认文件host_name-slow.log

　　log_queries_not_using_indexes #如果该值设置为ON，所有不使用索引的查询都会被记录

　　查看MySQL慢日志信息：

　　--查询慢日志配置信息

　　show variables like "%query%";--修改配置信息

　　设置全局 slow_query_log =on;

　　检查不使用索引参数的状态：

　　--显示参数

　　显示变量，如“%log_queries_not_using_indexes”；--打开状态

　　设置全局 log_queries_not_using_indexes =on;

　　查看慢日志是如何显示的：

　　-- 查看慢日志记录

　　show variables like "%log_output%";--设置慢日志同时记录在文件和表中

　　设置全局日志输出=“文件，表”；

　　测试慢查询日志：

　　--查询时间超过10秒会记录在慢查询日志中

　　select sleep(3) fromuserinfo;--查看表中的日志

　　从 mysql.slow_log 中选择 *；

　　八、执行计划

　　解释+查询SQL：用于显示SQL执行信息参数，可以根据参考信息进行SQL优化。

　　解释 select count(*) from userinfo where id=1;

　　执行计划：让mysql估计执行操作（一般正确）。

　　type：查询计划的连接类型，有多个参数，从最好的类型到最差的类型

　　性能：null > system/const > eq_ref > ref > ref_or_null > index_merge > range > index > all 慢：

　　explainselect * from userinfo where email="pink";

　　类型：ALL（全表扫描）

　　特别：select * from userinfo limit 1；

　　快的：

　　explainselect * from userinfo where;

　　类型：ref（去索引）

　　九、大数据量分页优化

　　执行这段代码：

　　从用户信息限制中选择 * 3000000,10；

　　优化：

　　1、简单粗暴，不准查看这么晚的数据。比如百度就是这样。如果你最多翻到76页，它不会让你翻的。这种方式是从业务上解决的。