背景:从开始写第一条sql到现在,今天是感觉自己又行了的时刻。对于一个技术人员来讲,虽然某些深层次技术暂时项目上应用不到,但是还是要有所涉猎。为了加强基础性建设,为以后的腾飞打下夯实的基础,今天抽空整理一下MySQL相关的优化纪要。
1. 索引是什么?
索引是一种快速检索数据的数据结构。
2. 索引的意义是什么?
提升数据检索效率,减轻数据处理分担。
3. 索引创建的场景是什么?
随着系统数据的量级提升,在硬件条件达标的情况下,数量达到一定量级后就需要创建。
4. 索引的创建如何进行?
第一种语法:
CREATE INDEX [INDEX_NAME] ON FROM [TABLE_NAME]([COLUMN_NAME1],[COLUMN_NAME2],[...]);
错误说明:应该是: CREATE INDEX index_name ON table_name(column1, column2, columnn);
第二种语法:
ALTER TABLE [TABLE_NAME] ADD INDEX [INDEX_NAME] (COLUMN_NAME1,[COLOMN_NAME2], [...]);
5. 索引如何删除?
第一种常用语法:
DROP INDEX [INDEX_NAME] ON TABLE_NAME;
第二种语法:
ALTER TABLE [TABLE_NAME] DROP INDEX [INDEX_NAME];
6. 如何避免索引失效?
:查询参数尽量遵守最佳左前缀法则与索引列相匹配(或尽量做到全职匹配),减少select *操作。
:尽量不要再索引列上坐操作(如:计算,函数,自动或手动的类型转换),否则会导致索引失效从而转向全表扫描。
:MySQL在使用不等于(!=|<>)的时候会让索引失效导致全表扫描。
:is null, is not null也会使索引失效。
:Like通配符使用时%在前也会使索引失效导致全表扫描。(可以利用覆盖索引解决)
:字符串不加单引号回事索引失效。(Eg: 假设参数列的类型为varchar型,’222’能够用到索引,而222则会使索引失效,因为MySQL收到222后经过校验发现数据库中对应参数列的类型为varchar型,而用户传递的参数确实一个整数型,因此会隐式的做一次类型转换。之前也说过,不要对索引列进行自动或手动的类型转换,否则会使索引失效就是这个道理)
:同时,使用or也会使索引失效。
以上是基础的一些使用,暂时简单的随笔到这里,后续有时间会对具体的高级优化再详尽说明!
-- 索引失效的常见情况
-- 1. 索引列有null的空值数据, 导致count(*)不能走索引(HashSet中不能存空值).
-- 2. 建立了一个包含Y的复合索引, 但是却是select * from table_name; 此时不会走索引, sql优化器会认为你为了提升数据的有效性,一行一行的读取数据, 从而不会走索引.
-- 3. 建立的复合索引在查询中没有按顺序使用, 导致索引出现了断层从而没有走索引.
-- 4. 索引上有了函数, 导致没有走索引, 可以创建一个既定函数的索引, Eg: create index index_name from table_name(right(column_name));
-- 5. !=(<>不等于)也会导致不走索引.
-- 6. not in() , not exist() 不会走索引.(可以改为左连接查询的方式)
-- 7. like %在前不会走索引.