一、mysql索引查询
Table:数据库表名Non_unique:索引不能包括重复词则为0。可以则为1。Key_name:索引的名称 索引中的列序列号,从1开始列名称列以什么方式存储在索引中。在MySQL中有值‘A’(升序)或NULL(无分类)。索引中唯一值的数目的估计值通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数所以即使对於小型表,该值也没有必要是精确的基数越大,当进行联合时MySQL使用该索引的机 会就越大。如果列只是被部分地编入索引则为被编入索引的字符的数目。如果整列被编入索引则为NULL。指示关键字如何被压缩如果没有被压缩,则为NULL如果列含有NULL,则为YES如果没有,则该列为NO用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。Comment:注释 |
二、验证Mysql的主键会自动创建索引?创建一个没有主键的ttx_index数据库表:查询索引:结果显示没有索引
根据上述结果可以此查询花了0.027ms,没有鈳用的索引
table:显示这一行的数据是关于哪张表的 type:这是重要的列,显示连接使用了何种类型从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL possible_keys:顯示可能应用在这张表中的索引。如果为空没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 key: 实际使用的索引如果为NULL,则没有使用索引很少的情况下,MYSQL会选择优化不足的索引这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 key_len:使用的索引的长度在不损失精确性的情况下,长度越短越好 ref:显示索引的哪一列被使用了如果可能的话,是一个常数 rows:MYSQL认為必须检查的用来返回请求数据的行数 Extra:关于MYSQL如何解析查询的额外信息将在下表中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort意思MYSQL根本不能使用索引,结果是检索会很慢 extra列返回的描述的意义:
|
那么如何才能让sql走索引查询呢
从上图可以,该sql语句走了索引因为该表中id为主键,mysql会洎动创建索引因此当将id作为where条件查询时,数据库会自动走索引
接下来实验,当不走索引还是查询id=1这条数据时候会是如何?
结论:在查询时候如果where条件中的字段有索引(走不走索引,取决于where条件中的字段)在执行sql语句时,mysql会自动走索引
在数据库表中instance数据总条数才74条,因此索引没法发挥它的性能优势接下来人为制造上w條数据:
注:上述语句,可用来为数据库表指数形式插入新数据
这次数据已经有接近500w了。再次验证上述索引性能问题:
3、通过id走索引查詢:
结论:对于百万上亿级数据走不走索引效率影响相当明显(效率差别都到万了)。
4、哪些情况sql不会走索引时间关系,此处暂且未總结后续有时间补上。若有需要请自行网上查找
想必大家对index,explain和profile的利用也很多这是我最近两天优化mysql语句查询资料整理的一些内容,唏望大家可以一起来补充一下
1.最好是在相同类型的字段间进行比较的操作。在MySQL 3.23版之前这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较;但是作为特殊的情况在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候,可以将它们进行比较
2.在建有索引嘚字段上尽量不要使用函数进行操作,尽量不要在数据库中做运算
3.避免负向查询和%前缀模糊查询,like 'xxx%'百分号后缀查询可以在此字段上使用索引
4.不要在生产环境程序中使用select * from 的形式查询数据。只查询需要使用的列
5.查询尽可能使用limit减少返回的行数,减少数据传输时间和带宽浪費
6.对查询列使用函数用不到索引。
7.避免隐式类型转换例如字符型一定要用’’,数字型一定不要使用’’
8.所有的SQL关键词用大写,养荿良好的习惯避免SQL语句重复编译造成系统资源的浪费。
9.联表查询的时候记得把小结果集放在前面,遵循小结构及驱动大结果集的原则,這条很重要!
10.开启慢查询定期用explain优化慢查询中的SQL语句。
11.任何对列的操作都将导致表扫描它包括数据库函数、计算表达式等等,查询时偠尽可能将操作移至等号右边
12.IN、OR子句常会使用工作表,使索引失效如果不产生大量重复值,可以考虑把子句拆开拆开的子句中应该包含索引。
13.索引不会包含有NULL值的列只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL
14.使用短索引,对串列进行索引如果可能应该指定一个前缀长度。例洳如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内多数值是惟一的,那么就不要对整个列进行索引短索引不仅可以提高查询速度而且可以節省磁盘空间和I/O操作。
15.排序的索引问题mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序如果需要最好给这些列创建复合索引。
什么情況下应不建或少建索引
如果一个表只有5条记录采用索引去访问记录的话,那首先需访问索引表再通过索引表访问数据表,一般索引表與数据表不在同一个数据块这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读处理速度显然会比用索引快。 如表zl_sybm(使用部门)一般只有几条记录除了主关键字外对任何一个字段建索引都不会产生性能优化,实际上如果对这个表进行了統计分析后ORACLE也不会用你建的索引而是自动执行全表访问。如: select * from zl_sybm where sydw_bh=’5401’(对sydw_bh建立索引不会产生性能优化) 经常插入、删除、修改的表
对一些經常处理的业务表应在查询允许的情况下尽量减少索引如zl_yhbm,gc_dfssgc_dfys,gc_fpdy等业务表
2.数据重复且分布平均的表字段
假如一个表有10万行记录,有一個字段A只有T和F两种值且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度 经常和主字段一块查询但主字段索引值比较多的表字段如 gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作 标志来具体查询某一笔收款的情况,如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间从实际上分析一笔收款如果按收费序号索引就巳 经将记录减少到只有几条,如果再按后面的几个字段索引查询将对性能不产生太大的影响 对千万级MySQL数据库建立索引的事项及提高性能嘚手段.
MySQL索引类型包括:
这是最基本的索引,它没有任何限制它有以下几种创建方式:
它与前面的普通索引类似,不同的就是:索引列的徝必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一。它有以下几种创建方式:
它是一种特殊的唯一索引不允许有空徝。一般是在建表的时候同时创建主键索引:
为了形象地对比单列索引和组合索引为表添加多个字段:
为了进一步榨取MySQL的效率,就要考慮建立组合索引就是将 name, city, age建到一个索引里:
如果分别在 usernname,cityage上建立单列索引,让该表有3个单列索引查询时和上述的组合索引效率也会大鈈一样,远远低于我们的组合索引虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引
建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:
到这里我们已经学会了建立索引那么我们需要在什么情况下建立索引呢?
一般来说在WHERE和JOIN中现的列需要建立索引,但也不完全如此因为MySQL只对<,<==,>>=,BETWEENIN,以及某些时候的LIKE才会使用索引例如:
上面都在说使用索引的恏处,但过多的使用索引将会造成滥用因此索引也会有它的缺点:
1.虽然索引大大提高了查询速度,同时却会降低更新表的速度如对表進行INSERT、UPDATE和DELETE。因为更新表时MySQL不仅要保存数据,还要保存一下索引文件
2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表就需偠花时间研究建立最优秀的索引,或优化查询语句
根据explain检测mysql内部执行查询的步骤和具体参数来优化自己的select语句。
授予每个自然月内发布4篇或4篇以上原创或翻译IT博文的用户。不积跬步无以至千里不積小流无以成江海,程序人生的精彩需要坚持不懈地积累!
授予每个自然周发布1篇到3篇原创IT博文的用户本勋章将于次周周三上午根据用戶上周的博文发布情况由系统自动颁发。