一、mysql索引类型 BTREE:B+树索引 （常用） HASH：HASH索引 FULLTEXT：全文索引 RTREE：R树索引 二、索引分类 主键索引 普通索引 唯一索引 三、索引基本管理 创建和删除： alter table stu add index idx_name(stu_name); # 创建索引 alter table stu drop index idx_name; # 删除索引 或者 create index inx_name on stu(stu_name); drop index inx_name on stu; 查询索引设置 desc stu; 1.2.3.4.5.6.7.8.9.10.11.12. 四、MySQL中的约束索引 4.1 主键索引： 只能有一个主键。 索引:列的内容是唯一值,高中学号. 建的时候至少要有一个主键索引，最好和业务无关 走主键索引的查询效率是最高的，我们尽量每个表有一个主键，并且将来查询的时候计量以主键为条件查询 建表时创建推荐 CREATE TABLE `test` ( `id` INT(4) NOT NULL AUTO_INCREMENT, `name` CHAR(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=UTF8; 建立表后增加 CREATE TABLE `test1` ( `id` INT(4) NOT NULL, `name` CHAR(20) NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=UTF8; # 简历一个test1表 ALTER TABLE test1 CHANGE id id INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT; # 增加自增主键 4.2 普通索引 加快查询速度，工作中优化数据库的关键。 在合适的列上建立索引，让数据查询更高效。 create index index_name on test(name); alter table test add index index_name(name); 用了索引，查一堆内容。 在where条件关键字后面的列建立索引才会加快查询速度. select id,name from test where state=1 order by id group by name; 前缀索引：根据字段的前N个字符建立索引 create index index_name on test(name(8)); 联合索引：多个字段建立一个索引 特点：前缀生效特性。 原则：把最常用来作为条件查询的列放在前面。 alter table stu add index minx(gender,age); # 查询时优先匹配gender 前缀加联合索引 create index index_name on test(name(8),sex(2)); 唯一性索引 create unique index index_name on test(name); 五、企业sql优化思路 企业SQL优化思路： 1、把一个大的不使用索引的SQL语句按照功能进行拆分 2、长的SQL语句无法使用索引，能不能变成2条短的SQL语句让它分别使用上索引。 3、对SQL语句功能的拆分和修改 4、减少“烂”SQL 由运维（DBA）和开发交流（确认），共同确定如何改，最终由DBA执行 5、制定开发流程 不适合走索引的场景： 1、唯一值少的列上不适合建立索引或者建立索引效率低。例如：性别列 查看表的唯一值数量： select count(distinct user) from mysql.user; # user列不重复的有几行 select count(distinct user,host) from mysql.user; #user，host不重复的有几行 SELECT COUNT(*) FROM mysql.user; #一共有多少行 2、小表可以不建立索引，100条记录。 3、对于数据仓库，大量全表扫描的情况，建索引反而会慢 一、数据库索引的设计原则： 为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。 那么索引设计原则又是怎样的? 1．选择唯一性索引 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。 例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。 如果使用姓名的话，可能存在同名现象，从而降低查询速度。 主键索引和唯一键索引，在查询中使用是效率最高的。 2．为经常需要排序、分组和联合操作的字段建立索引 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。 如果为其建立索引，可以有效地避免排序操作。 3．为常作为查询条件的字段建立索引 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此， 为这样的字段建立索引，可以提高整个表的查询速度。 select count(DISTINCT population ) from city; select count(*) from city; 4．尽量使用前缀来索引 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索 会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。 explain（执行计划），使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈 mysql> explain select name,gender,age from test where gender='F' and age 或<查找东西时发生的情况 11、index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比 ALL 更好，因为索引一般小于表数据）。 12、ALL : 这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免 13、Not exists : MySQL 优化了LEFT JOIN，一旦它找到了匹配 LEFT JOIN 标准的行，就不再搜索了。 出现Using temporary,Using filesort,Using join buffer一般是排序 order by ,group by ,distinct,条件上没有索引例如： explain select * from city where countrycode=‘CHN’ order by population; 或者在join 的条件列上没有建立索引 type : 表示MySQL在表中找到所需行的方式，又称“访问类型”， 常见类型如下:ALL,index, range, ref, eq_ref, const, system, NULL从左到右，性能从最差到最好 ALL： Full Table Scan， MySQL将遍历全表以找到匹配的行 如果显示ALL，说明： 查询没有走索引：说明一语句本身的问题、二、索引的问题，没建立索引 例子 explain select count(*) from stu ; index index： Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的） 例子： range range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。 显 而易见的索引范围扫描是带有between或者where子句里带有查询。 where 条件中有范围查询或模糊查询时如： > = <= between and in () or like 'xx%' 当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。 ref 对于普通索引这就是最高的级别了 ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行比如： where stu_name='xiaoming' explain select * from stu where stu_name='aa'; 5、 eq_ref eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说， 就是多表连接中使用primary key或者 unique key作为关联条件 join条件使用的是primary key或者 unique key 6、 const,system const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。 如将主键置于where条件的条件，MySQL就能将该查询转换为一个常量 explain select * from city where id=1; null NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引， 例如走子查询的数据在父查询中有，又或者数据在内存中直接有
#yyds干货盘点#---MySQL 索引管理及执行计划
