一、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在优化过程中分解语句,执行时甚至不用访问表或索引, 例如走子查询的数据在父查询中有,又或者数据在内存中直接有