一、索引使用1.1概述1、定义索引的数据结构，帮助MySQL高效获取数据（根据一定的规则）。2.MySQL的定义与解释数据库系统除了存储数据外，还维护着满足特定搜索算法的数据结构。这些数据结构以某种方式引用（指向）数据，因此可以在这些数据结构上实现高级搜索算法。这个数据结构就是索引。3.优缺点优点提高数据检索效率，降低数据库IO成本。通过索引对数据进行排序，降低数据排序成本和CPU消耗。缺点其实索引也是一张表，里面存放的是主键和索引字段，指向实体类的记录，所以索引列也很占空间。更新表时，MySQL不仅保存了数据，还保存了索引文件。每次更新一个有索引列的字段，都会在更新引起的键值变化后调整索引信息。1.2索引结构（InnoDB）MySQL数据库中默认的存储引擎InnoDB的索引结构是B+树，根据叶子节点的内存存储，索引类型分为主键索引和非主键索引.1.主键索引（聚集索引）主键索引的叶子节点存放整行数据，其结构如下：2.主键当非主键索引的叶子节点的内容(二级索引或辅助索引)代替主键索引存储值的结构如下：1.3索引使用规则不创建索引，执行计划如下创建索引createindexidx_seller_name_status_addressontb_seller(name,状态，卖家）；1。全值匹配，为索引的所有列指定具体值explainselect*fromtb_sellerwherename='小米科技'andstatus='1'andaddress='北京';2.最左前缀的法制违反了最左规则，如果索引不符合最左规则，但是某列跳转，只有最左列索引生效：3.范围查询对右边的列,不能使用索引。根据前两个字段name和status查询，使用了索引，最后一个条件地址没有使用索引。4、对索引列进行操作，索引失败。5、字符串没有加单引号，导致索引失败。因为，在查询中，字符串没有加单引号，MySQL查询优化器会自动进行类型转换。导致索引失效。6.or分隔的条件示例，name字段是索引列，但是createtime不是索引列，中间是or连接不带索引：explainselect*fromtb_sellerwherename='DarkHorseProgrammer'orcreatetime='2088-01-0112:00:00'\G;7.对于以%开头的Like模糊查询，该索引无效。解决方案8.如果MySQL评估使用索引比全表慢，则不要使用索引9.isNULL,isNOTNULL有时索引会失败。10.in，notin，有时索引失效。11、尽量使用覆盖索引，避免select。尝试使用覆盖索引（只访问索引的查询（索引列完全包含查询列））并减少select。如果查询列超过索引列，也会降低性能。usingindex：使用覆盖索引时，会有usingwhere：搜索使用索引的情况下，需要回表查询需要的数据usingindexcondition：搜索使用了索引，但是需要回头到表中使用索引查询数据；usingwhere：搜索使用索引，但是在索引列中可以找到需要的数据，所以不需要回表查询数据使用效率，使用索引更高效。为查询频率高、数据量大的表创建索引。对于索引字段的选择，应该从where子句的条件中提取最佳候选列。如果where子句中有很多组合，那么应该选择过滤效果最好的最常用列的组合。使用唯一索引，区分程度越高，使用索引的效率越高。索引可以有效提高查询数据的效率，但是索引的数量并不总是越多越好。索引越多，维护索引的成本自然会增加。对于插入、更新、删除等DML操作频繁的表，过多的索引会引入相当高的维护成本，降低DML操作的效率，增加相应操作的时间消耗。另外，如果索引过多，MySQL也会遇到选择困难的问题。虽然最后还是会找到一个可用的索引，但无疑会增加选择的成本。对于短索引，索引创建后硬盘也用于存储，因此提高索引访问的I/O效率也可以提高整体的访问效率。如果构成索引的字段总长度比较短，那么在给定大小的存储块中可以存储更多的索引值，相应地，可以有效提高MySQL访问索引的I/O效率。使用最左边的前缀和N列组合而成的复合索引，相当于创建了N个索引。如果在查询的时候where子句中使用了组成索引的前几个字段，那么这个查询SQL可以使用组合索引来提高查询效率。二常见SQL优化2.1数据库准备1。sqlCREATETABLE`emp`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(100)NOTNULL,`age`int(3)NOTNULL,`salary`int(11)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;insertinto`emp`(`id`,`name`,`age`,`salary`)values('1','Tom','25','2300');insertinto`emp`(`id`,`name`,`age`,`salary`)values('2','Jerry','30','3500');insertinto`emp`(`id`,`name`,`age`,`salary`)values('3','Luci','25','2800');插入`emp`(`id`,`name`,`age`,`salary`)values('4','Jay','36','3500');插入'emp'('id','name','age','salary')values('5','Tom2','21','2200');insertinto`emp`(`id`,`name`,`age`,`salary`)values('6','Jerry2','31','3300');insertinto`emp`(`id`,`name`,`age`,`salary`)values('7','Luci2','26','2700');insertinto`emp`(`id`,`name`,`age`,`salary`)values('8','Jay2','33','3500');插入`emp`(`id`,`name`,`age`,`salary`)values('9','Tom3','23','2400');插入`emp`(`id`,`name`,`age`,`salary`)values('10','Jerry3','32','3100');插入`emp`(`id`,`name`,`age`,`salary`)values('11','Luci3','26','2900');insertinto`emp`(`id`,`name`,`age`,`salary`)values('12','Jay3','37','4500');createindexidx_emp_age_salaryonemp(age,salary);2.2orderby优化1.filesort第一种是对返回的数据进行排序，也就是通常所说的filesort排序，所有不直接通过索引返回排序结果的排序都称为FileSort排序2.usingindex第二种是直接返回通过有序索引顺序扫描有序数据，本例是使用索引，不需要额外排序，运行效率高。多字段排序了解了MySQL的排序方式后，优化目标很明确：尽量减少额外排序，直接通过索引返回排序后的数据。where条件和Orderby使用同一个索引，OrderBy的顺序和索引顺序一样，Orderby的字段都是升序或者降序。否则肯定需要额外的操作，所以才会出现FileSort。3、优化以上两种方式可以通过创建合适的索引来减少Filesort的出现，但是在某些情况下，条件约束并不能使Filesort消失，所以需要加快Filesort的排序操作。对于Filesort，MySQL目前采用的是一种扫描算法：一次性取出所有满足条件的字段，然后将排序后的结果集直接输出到sortbuffer中。排序的内存开销较大，但排序效率高于二次扫描算法。MySQL通过比较系统变量max_length_for_sort_data的大小与Query语句提取的字段总大小来判断是否是排序算法。如果max_length_for_sort_data较大，则使用第二种优化算法；否则，使用第一个。可以适当增加sort_buffer_sizemax_length_for_sort_data系统变量来增加排序区域的大小，提高排序效率。2.3groupby优化因为GROUPBY其实也进行了排序操作，而与ORDERBY相比，GROUPBY主要只是排序后多了一些分组操作。当然如果在分组的时候使用了一些其他的聚合函数，那么也需要进行一些聚合函数的计算。因此，在GROUPBY的实现过程中，也可以像ORDERBY一样使用索引。如果查询中包含groupby，但用户又想避免消费排序后的结果，可以执行orderbynull来禁止排序。如下：dropindexidx_emp_age_salaryonemp;explainselectage,count(*)fromempgroupbyage;优化explainselectage,count(*)fromempgroupbyageorderbynull;从上面的例子可以看出，第一个SQL语句需要“filesort”，而第二个SQL由于orderbynull，所以不需要“filesort”，而上面提到的Filesort往往是非常耗时的。创建索引createindexidx_emp_age_salaryonemp(age,salary);2.4limit在优化一般的分页查询时，通过创建覆盖索引可以更好的提升性能??。一个常见且非常麻烦的问题是limit5000000,10。这时MySQL需要对前5000010条记录进行排序，只返回5000000-5000010条记录，丢弃其他记录。查询排序的成本非常高。限制分页操作，越往后性能越低：优化方案select*fromtb_skut,(selectidfromtb_skuorderbyidlimit9000000,1)awhereet.id=a.id;2.5countoptimization在很多业务系统中，需要考虑分页操作，但是当当我们进行分页操作时，需要进行一次计数操作来获取总记录数。如果数据库表的数据量很大，在InnoDB引擎中，执行计数操作的性能是比较低的。需要遍历整张表数据并计数累加。优化方案①。在大数据量的查询中，只查询数据，不显示记录总数；②.一个表的计数是通过缓存redis来维护的，用来记录数据库表的记录总数。插入/删除时，需要动态更新；③.在数据库表中定义大量数据统计表，插入/删除时需要动态更新。2.6批量插入优化1.环境准备CREATETABLE`tb_user`(`id`INT(11)NOTNULLAUTO_INCREMENT,`username`VARCHAR(50)NOTNULL,`password`VARCHAR(50)NOTNULL,`name`VARCHAR(20)NOTNULL,`birthday`DATEDEFAULTNULL,`sex`CHAR(1)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`unique_user_username`(`username`))ENGINE=INNODBDEFAULTCHARSET=utf8;使用load命令导入数据时，适当的设置可以提高导入效率。对于InnoDB表，有以下几种提高导入效率的方法：主键顺序插入由于InnoDB表是按照主键顺序存储的，所以将导入的数据按照主键顺序排列，可以有效提高导入数据的效率。效率。如果InnoDB表没有主键，系统默认会自动创建一个内部列作为主键，所以如果可以为表创建主键，可以借此提高数据导入的效率。脚本文件介绍：sql1.log---->主键有序sql2.log---->主键乱序插入ID序列数据：loaddatalocalinfile'/root/sql1.log'intotable`tb_user`fieldsterminatedby','lineterminatedby'\n';插入ID乱序的数据：关闭唯一性校验，导入数据前执行SETUNIQUE_CHECKS=0，关闭唯一性校验，导入完成后执行SETUNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入效率。手动提交事务如果应用使用了自动提交，建议在导入前执行SETAUTOCOMMIT=0关闭自动提交，导入后再执行SETAUTOCOMMIT=1开启自动提交，这样也可以提高导入效率。
