mysql基础教程05、索引和事务

01、建立索引

--查看索引：
	--show index from 表名;
	--show create table 表名;  这里查出的带有KEY的字段都是带索引的
	
--添加索引：
	--alter table 表名 add index 索引名(字段名)
		--如果不指定索引名，则字段名和索引名保持一致
	--添加以后查看：
		--show index from 表名;

--删除索引：
	--alter table drop index 索引名;
	--删完再查看一下：show index from 表名;

02、测试索引是否真的可以提高查询效率

--创建一个测试表：
		create table test_index(title varchar(40));

--插入10万条数据：
		from pymysql import connect

		def main():
		    # 创建Connection连接
		    conn = connect(host='localhost',port=3306,database='python',user='root',password='mysql',charset='utf8')
		    # 获得Cursor对象
		    cursor = conn.cursor()
		    # 插入10万次数据
		    for i in range(100000):
		        cursor.execute("insert into test_index values('ha-%d')" % i)
		    # 提交数据
		    conn.commit()
		
		if __name__ == "__main__":
		    main()

--怎么查看查询时间
	--set profiling=1;  运行时间检测
	--select * from test_index where title='ha-99999';  执行查询语句
	--show profiles;  查看执行的时间
--结果：
	--未建立索引之前：0.0338s
	--建立索引 100000 条数据：0.34s
	--索引建立之后查询时间：0.00547s  显著降低查询时间

03、联合索引

--联合索引[复合索引]：即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候
	--减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销

--创建teacher表：
	--create table teacher
		(
    		id int not null primary key auto_increment,
    		name varchar(10),
   		    age int
		);
--创建联合索引：
	--alter table 表名 add index 索引名(字段名1, 字段名2, ...);
		--如果不指定索引名则默认是第一个字段名
	--查看索引：show index from 表名;

--删除联合索引“
	--alter table 表名 drop index 索引名;

--联合索引最左原则：
	--就是联合索引建立以后，查询时的条件中必须带上联合索引使用字段中最左边的值。否则索引不生效
	--示例：
		--建立联合索引：
			ALTER TABLE teacher ADD INDEX (`name`, age);  建立name age的联合索引
		--查询：
			select * from teacher where name = "liming";  联合索引生效
			select * from teacher where name = "liming" and age = 18;  联合索引生效
			select * from teacher where age = 18;  联合索引不生效，因为name字段没有用在查询条件中

04、索引使用的优缺点

--优点：
	--加快数据的查询速度
--缺点：
	--创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加

--使用原则：
	--通过优缺点对比，不是索引越多越好，而是需要自己合理的使用。
	
	--对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引。
		--比如 name password可以创建索引
		
	--数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
	
	--在一字段上相同值比较多不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。