索引是数据库优化中最常见也 最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。
（Mysql的优化很大一部分都集中在索引的优化，因为现在是信息社会，数据量都不少，所以做索引少不了的，很多学员朋友在面试或者在公司里面其实面面临的一个重要的问题往往是突出的问题是查询过慢或者查询过久导致服务器的cpu消耗过高，那么像这种问题咱们最直接最常用的手段是什么呢？ 索引优化，所以今天咱们来把索引优化好好的给大家讲解一下包括测试，基本上通过索引优化可以帮助开发者解决大部份的SQL性能问题，以前在三一重工的时候他负责的一个CRM系统查询经常好慢，然后他跟我讲，后来我帮他查了一下啊，它的Mysql里面的慢查询日志srow.log里面有大量的慢查询语句，经常查询到十分钟以上啊。怎么解决呢？通过DSC定位看那些地方需要做索引那些地方不需要做索引，咱们一般常见做索引的地方一般是where语句的后面也就是条件语句以及排序Order by 以及group by后面的having,总之这些条件后面的字段或者频繁的字段都会加索引）
慢查询日志查看：
show variables like '%quer%';
其中红框标注的选项是：
-slow_query_log是否记录慢查询。用long_query_time变量的值来确定“慢查询”。
-slow_query_log_file慢日志文件路径
-long_query_time慢日志执行时长（秒），超过设定的时间才会记日志
如何开启慢查询日志查看那功能：
Linux：
在/etc/my.cnf配置文件的[mysqld]选项下增加：
slow_query_log=TRUE
slow_query_log_file=/usr/local/mysql/slow_query_log.txt
long_query_time=3
Windows：
在my.ini配置文件的[mysqld]选项下增加：
slow_query_log=TRUE
slow_query_log_file=c:/slow_query_log.txt
long_query_time=3[图片上传失败...(image-28b5a9-1599919719190)]
索引的存储分类：
MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是一个独一的一个文件;InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面的，但可以有多个文件组成。
MySQl目前不支持函数索引，但是能对列的面前某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大的缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性经行灵活设计。
（其实咱们一般用户的表存储到MyISAM引擎里面的，MyISAM引擎里面的表不管是在linux还是在weindos下面它其实存储分为三个文件，一个是.LOG是日志文件，另外一个是以.MYI结尾的索引文件，以.MYD的即是数据文件,那么InnoDB引擎类型的表默认是共性表空间，而且每个表的表结构会单独的分离开，放到不同的文件里面去，但是咱们的InnoDB索引和数据会存储到同一个表空间，MySQL如果使用InnoDB存储引擎，数据库文件类型就包括.frm、ibdata1、.ibd，存放位置有两个，
.frm文件默认存放位置是C:\Documents and Settings\All Users\ApplicationData\MySQL\MySQL Server 5.1\data，ibdata1、.ibd文件默认存放位置是MySQL安装目录下的data文件夹）
（Mysql的表空间管理远远说不上完善。换句话说，事实上Mysql根本没有真正意义上的表空间管理。Mysql的Innodb包含两种表空间文件模式，默认的共享表空间和每个表分离的独立表空间。只要在my.cnf里面增加innodb_file_per_table=1就可以从共享表空间切换到独立表空间。当然对于已经存在的表，则需要执行alter table MY_TABLE engine=innodb命令迁移数据。
共享表空间方式
由于是默认的方式，就暂且理解为Mysql官方推荐的方式。相对而言所有的数据都在一个（或几个）文件中，比较利于管理，而且在操作的时候只需要open这一个（或几个）文件即可，相对来说代价很低。
但问题是在数据达到以G为单位来计算的时候优劣逆转。一个大小惊人的文件很不利于管理，而且对于一个如此巨大的文件来说，读写它需要耗费的资源一样巨大。更加令人费解的是，MySQL竟然将索引和数据保存于同一个文件中，索引和数据之间尚存在资源争用，不利于性能的提升。你当然可以通过innodb_data_file_path的配置规划多个表空间文件，但MySQL的逻辑是“用满后增加”，仅仅是一个文件的拆分而已，不能从根本上分离数据和索引。
之前曾经遭遇到700G以上的表空间文件，而且更加让人郁闷的是对于如此大的文件还在以每天数G的数量增加。由于无法停机，即便是拷贝一下也要花费差不多一夜，只能眼睁睁看着它继续增大而毫无保守可行的办法。
独立表空间方式
相对而言对立表空间每个表都有独立的多个数据文件，而且做到了索引和数据的分离。多个小文件之间很方便的完成跨数据库甚至跨硬件的数据拷贝和迁移。相对来说灵活性很好。
这样做同样带来另一个方面的问题。当数据库中的表数量达到一定级别时，每次操作所涉及的文件过多，如果按照默认Centos的ulimit -n = 1024的话，仅仅只能保证同时打开256个表以内，这在习惯上“拆库拆表”的MySQL数据结构上很难达到要求。尚且这种数据文件的利用率不算很高，当大量“不高”的文件集中起来，浪费的空间也很惊人，更何况最后可能出现的状况不是“一堆K级别的小文件”而是“一堆G级别的大文件”，有点适得其反的意思。你自然可以联想到分区表，又是一个“仅仅做文件拆分而已”，多个分区文件缺一不可。
之前同样遇到过这个问题，MySQL连接大的状况下大量的timeout，但主机负载还算可以，查了一圈才知道是open files限制的问题，限制一修改，负载变得惊人，但连接数却又提升的不多。
总之，两种方法各有所长，部分互补，但都不是解决问题的终极方案。期待MySQL能够出现真正意义上表空间的概念，更加自由的规划数据文件。）
例子：
create index ind_company_name on company(name(4))
其中company表明
ind_company_name 索引名
看下我们数据库下面有多少数据库啊
show databases;
use test;
键一个表；
CREATE TABLE t1(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(30)
);
//插入数据
INSERT INTO t1(NAME) VALUES("admin1"),("admin2"),("admin3"),("admin4"),("admin5")
比较这三个语句的耗时：
SELECT * FROM t1
SELECT * FROM t1 WHERE id=4;
SELECT * FROM t1 WHERE NAME="admin4";
解析这条语句：
DESC SELECT * FROM t1 WHERE id=4
[图片上传失败...(image-6f6b30-1599919719191)]
解释： 它是一个简单的单表查询SIMPLE，而且查询的表是t1,里面搜索的结果是1行，所以类型是const.possible_keys: PRIMARY可能应用到的索引是主键，实际应用索引key：PRIMARY，影响的行数rows 是1行，
使用索引长度ken_len: 4
执行解释：DESC SELECT * FROM t1 WHERE NAME="admin4";
[图片上传失败...(image-c5ce37-1599919719191)]
解释：单表查询 查询表t1 索引没用到，影响行数是5行 type：all 效率较低
咱们给它加索引吧：
ALTER TABLE t1 ADD INDEX in_name(NAME)
查看t1表下面的字段信息
DESC t1;
[图片上传失败...(image-9f3843-1599919719191)]
打上索引后咱们再来查看一下执行过程：
DESC SELECT * FROM t1 WHERE NAME="admin4";
[图片上传失败...(image-ff4ead-1599919719191)]
最后印象行数rows 变成了1 ，现在是5行 如果生产线上是5000万条数据了，这就是别人效率笔记快5000万倍的原因了。一定要看影响行数啊 这个决定了你查询时间啊，能不能在生产线上使用就看它了
查找了多少行就代表影响了多少行
所以：索引的使用是提高select操作性能的最佳途径啊
下面介绍如何使用索引：
1、 对于创建多列索引，只要查询的条件中用到最左边的列，索引一般会使用，如下创建一个符合索引
create index ind_salescom_onsales (company_id,moneys);
然后按company_id进行查询就使用到了索引，
explain select * from sales2 where company_id =20006
使用moneys进行查询就没有到了索引，
explain select * from sales2 where moneys =10
（ysql 搜索有三种啊 第一种是like 第二种是正则查询，第三种是sphinx(斯芬克斯)搜索加速,中文分词，高能显示，1000万都只要001秒的耗时，很接近百度搜索引擎
2、 使用like查询的时候，如果%符号在第一个位置的时候，索引可能用不上啊
建议按照人的习惯思维走啊 一般你记忆那个人的名字是吧 知道他姓什么 名字可能记得不全是吧，类似于这样
DESC SELECT * FROM t1 WHERE NAME LIKE "admin%";
[图片上传失败...(image-896a27-1599919719191)]
DESC SELECT * FROM t1 WHERE NAME LIKE "%5";
[图片上传失败...(image-a53dd5-1599919719191)]
3、 如果那一列为空null 搜索哪一行为空也将用到索引
下面我们给t1表新插入一条name为空的行
INSERT INTO t1(NAME) VALUES(NULL)
然后执行查询
DESC SELECT * FROM t1 WHERE NAME IS NULL;
[图片上传失败...(image-db31ba-1599919719191)]
查询非空也用到了索引啊
DESC SELECT * FROM t1 WHERE NAME IS NOT NULL;
[图片上传失败...(image-bc857e-1599919719191)]
这些测试是告诉大家 你用了索引 在写查询语句的时候要注意 别搞了半天的索引用的时候又没用上，查询变慢对资源消耗很大啊，别一条sql语句执行了到第二天还没搞完啊，这就尴尬了啊！当然开个玩笑
二：存在索引但不使用索引的介绍
1、 如果Mysql使用索引比全部扫描更慢，则不宜用索引，例如如果key_part1均匀分布在1-100之间，查询时使用索引就不太好。这个是mysql自己估计的。
2、 如果在使用查询时 用到 where or的组合方式 那么 or或者是件列必须都是索引列啊索引才生效
测试：DESC SELECT * FROM t1 WHERE id =1 or name=”admin1
[图片上传失败...(image-8a3ec-1599919719191)]
这里name上面的索引：
ALTER TABLE t1 DROP INDEX in_name;
测试：DESC SELECT * FROM t1 WHERE id =1 or name=”admin1
[图片上传失败...(image-f0a427-1599919719191)]
3、 如果某个列是字符串类型 而你查询的时候将这个列的条件给了一个字符型，那么该列上的索引页用不到，这个在InnoDB和高版本5.5以上将优化
加上索引：ALTER TABLE t1 ADD INDEX in_name(NAME);
DESC t1; 查询有没有索引啊
插入一条数据：INSERT INTO t1(NAME) VALUES("777")
查询语句：DESC SELECT * FROM t1 WHERE id =1 OR NAME="777"
三、查看索引使用情况
如果索引正在工作，Handler_read_key的值将很高，这个值代表一行被索引读到的次数
Handler_read_rnd_next的值高则意味着查询运行效率低，并且应该建立索引补救
查看索引参数使用情况 ：SHOW STATUS LIKE 'Handler_read%'
[图片上传失败...(image-344dda-1599919719190)]
这里就要结合咱们的慢查询 和DSC去建立索引啊
四：常用的表优化手段
建立一个t1表的视图：
CREATE VIEW v_t1 AS SELECT * FROM t1 WHERE id>2 AND id<5
查看视图是否存在：
SELECT * FROM v_t1
我打算把t1 表删除 ，删除之前先复制表啊
CREATE TABLE t2 LIKE t1; 复制表结构
INSERT INTO t2 SELECT * FROM t1; 复制数据
SELECT * FROM t2 查询t2表
删除t1表：
DROP TABLE t1;
SHOW TABLES
检查视图v_t1;
CHECK v_t1;
报错：
查询：CHECK TABLE v_t1;
[图片上传失败...(image-cc90f6-1599919719190)]
这个是对咱们排错是非常实用的，检查一个或者多个表是否有错误,具体语法如下：
CHECK TABLE tbl_name[,tbl_name] ... [option] ...
option= {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
类型意义
QUICK
不扫描行，不检查错误的链接。
FAST
只检查没有被正确关闭的表。
CHANGED
只检查上次检查后被更改的表，和没有被正确关闭的表。
MEDIUM
扫描行，以验证被删除的链接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点。
EXTENDED
对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100％一致的，但是花的时间较长。
接着 咱们在表改回来：
RENAME TABLE t2 TO t1;
查询是否含有t1表：
SHOW TABLES
在检查视图：
CHECK TABLE v_t1;
[图片上传失败...(image-8cc5a4-1599919719190)]
查询视图里面的值：
SELECT * FROM v_t1
[图片上传失败...(image-7365d7-1599919719190)]
所以我们的check 可以检查一个表，但是我们的InnoDB和MYISAM经常中间删除或者修改大量的表，尤其是删除，导致表的大小不会自动收索，另外一个手段，优化表空间，这个重点是把表空间进行整理，把产生的碎片和空洞剔除。
优化表空间：
OPTIMIZE TABLE t1
[图片上传失败...(image-f5a35b-1599919719190)]
表不支持优化,做重建+分析
但是这里提醒一下啊：一个大的表可能在被访问的表不要随随便便的在工作时间来优化啊。
其他数据的导入导出
网友评论