mysql索引的使用

索引：

索引语法:

创建：create [unique/fulltext] index idx_xxx_xxx on tbl_xxx(字段)

查询：show index from tbl_xxx

删除：drop index idx_xxx on tbl_xxx

unique为唯一索引、fulltext为全文索引、当创建索引语句中有多个字段时创建联合索引。

索引失效情况：

1）在联合索引中，创建索引的顺序是有讲究的(最左前缀法则)，举例：

create idx idx_name_age_sex on tbl_user(name,age,sex);

name即为最左,当查询语句的where条件中没有出现name不会使用联合索引即索引失效。当查询语句的where条件中有name和sex时索引长度会减少即只使用了name的索引，age的索引跳过那么sex的所以会失效。如果where条件中有name和age则这两个索引都会被使用。

2）范围查询()：where条件中出现范围查询时，右边列的索引会失效，举例：

select * from tbl_user where name=’张三’and age>30 and sex=‘男’;

其中sex的索引会失效。

3）在索引上进行运算操作时索引会失效，举例(phone字段有单例索引)：

select * from tbl_user where substring(phone,10,2);

这条语句会走全表扫描而不走索引。

4）查询条件的字段是字符串类型时不加引号，索引会失效。

5）模糊查询中，头部模糊匹配索引会失效，举例：

select * from tbl_user where name like ’%三’;

6)查询条件使用or时两侧的列必须都有索引，一侧有一侧没有索引会失效。

SQL提示：

查询时MySQL会自动评估，是走全表扫描快还是走索引快，智能选择。

建议使用：MySQL有自己的想法使用下列语法给它提建议，用不用它说了算。举例：select * from tbl_user use index(索引名) where name=’张三’;

忽略使用：不让MySQL使用规定的索引。

举例：select * from tbl_user ignore index(索引名) where name=’张三’;

强制使用：不让MySQL有想法，就得给我用规定的索引！

举例：select * from tbl_user force index(索引名) where name=’张三’;

覆盖索引:查询中使用了索引并且返回值是索引的列，在索引中可以全部找到

不要使用select *。简单来说就是让索引走一次就能找到所有数据mssql 索引，不要进行回表查询.举例：

select id,name,age from tbl_user where name=’xx’;

在这条语句中，name字段有索引，那么在查询时直接走二级索引找到符合条件的叶子节点拿到的数据就是对应id，都可以显示出来不用再去聚集索引中查询。

select id,name,phone from tbl_user where name=’xx’;

在这条语句中，根据同上的过程，name和id都能找到，phone找不到，那么会拿着从二级索引中找到的id回到聚集索引中拿到id的一整行数据，再从这一行数据中提取phone的数据。这个过程扫描了二级索引和聚集索引，称为回表查询。查询性能相对降低。

使用select *很容易造成回表查询，除非建立一个全表字段的联合索引。

举例面试题：对下列sql进行优化，怎么提升查询性能

select id,username,password from tbl_user where username=’zhangsan’;

答案：建立username和password的联合索引。

附：

MySQL高级：

InnoDB和MyIASM的区别：

InnoDB支持事务，支持行锁，支持外键；

MyIASM不支持事务，支持表锁，不支持外键。

Memory引擎数据存储在内存中，受硬件和断电问题影响所以表只能当做临时表或缓存使用。由于数据存在内存中所以访问速度快。

B树和B+树的区别:

B+树所有的数据都会出现在叶子节点，并且形成一个双向链表；

Select * from user where name=”xx”,假设这张表的有id(主键)字段和name字段。（聚集索引和二级索引的索引结构都是B+树）执行这条sql的过程为：where后的条件是name，所以要到二级索引(辅助索引)中查询xx，经过对比找到叶子节点上name=xx的数据，二级索引的叶子节点上存储的是name对应的id即主键，聚集索引的叶子节点存储的是id对应的证行数据。用id到聚集索引中对比找到对应的叶子节点即可获取到符合where条件的数据。这个过程有个术语——回表查询。

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!